计算机代写 BETWEEN 30000 AND 40000)

Slide 6- *

The CREATE TABLE Command in SQL
Specifying a new relation
Provide name of table
Specify attributes, their types and initial constraints
Can optionally specify schema:
CREATE TABLE COMPANY.EMPLOYEE …

CREATE TABLE EMPLOYEE …

Slide 6- 9

COMPANY relational database schema (Fig. 5.7)
Slide 6- 11

One possible database state for the COMPANY relational database schema (Fig. 5.6)
Slide 6- 12

One possible database state for the COMPANY relational database schema – continued (Fig. 5.6)
Slide 6- 13

SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure 5.7 (Fig. 6.1)
continued on next slide
Slide 6- 14

SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure 5.7 (Fig. 6.1)-continued
Slide 6- 15

Character data types that are either fixed-length, char, or variable-length, varchar.
Ex; in SQL Server 2017, char [ ( n ) ] Fixed-length string data. n defines the string length in bytes and must be a value from 1 through 8,000.

varchar [ ( n | max ) ] Variable-length string data. n defines the string length in bytes and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

Attribute Data Types and Domains in SQL
Basic data types
Numeric data types
Integer numbers: INTEGER, INT
Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION
Character-string data types
Fixed length: CHAR(n), CHARACTER(n)
Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

Slide 6- 17

Attribute Data Types and Domains in SQL (cont’d.)
Bit-string data types
Fixed length: BIT(n)
Varying length: BIT VARYING(n)
Boolean data type
Values of TRUE or FALSE or NULL
DATE data type
Ten positions
Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD
Multiple mapping functions available in RDBMSs to change date formats

Slide 6- 18

Attribute Data Types and Domains in SQL (cont’d.)
Additional data types
Timestamp data type

Includes the DATE and TIME fields
Plus a minimum of six positions for decimal fractions of seconds
Optional WITH TIME ZONE qualifier
INTERVAL data type
Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp
DATE, TIME, Timestamp, INTERVAL data types can be cast or converted to string formats for comparison.

Slide 6- 19

Attribute Data Types and Domains in SQL (cont’d.)
Name used with the attribute specification
Makes it easier to change the data type for a domain that is used by numerous attributes
Improves schema readability
CREATE DOMAIN SSN_TYPE AS CHAR(9);
User Defined Types (UDTs) are supported for object-oriented applications. (See Ch.12) Uses the command: CREATE TYPE

Slide 6- *

Basic Retrieval Queries in SQL
SELECT statement
One basic statement for retrieving information from a database
SQL allows a table to have two or more tuples that are identical in all their attribute values
Tuple-id may be used as a key

Slide 6- *

Hence, in general, an SQL table is not a set of tuples, because a set does not
allow two identical members; rather, it is a multiset (sometimes called a bag) of

The SELECT-FROM-WHERE Structure of Basic SQL Queries
Basic form of the SELECT statement:

Slide 6- *

The SELECT-FROM-WHERE Structure of Basic SQL Queries (cont’d.)
Logical comparison operators
=, <, <=, >, >=, and <>
Projection attributes
Attributes whose values are to be retrieved
Selection condition
Boolean condition that must be true for any retrieved tuple. Selection conditions include join conditions when multiple relations are involved.

Slide 6- 30

Basic Retrieval Queries
Slide 6- 31

Aliasing, Renaming and Tuple Variables (contd.)
The attribute names can also be renamed

EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno)
Note that the relation EMPLOYEE now has a variable name E which corresponds to a tuple variable

Slide 6- *

Unspecified WHERE Clause
and Use of the Asterisk (cont’d.)
Specify an asterisk (*)
Retrieve all the attribute values of the selected tuples
The * can be prefixed by the relation name; e.g., EMPLOYEE *

Slide 6- 37

Tables as Sets in SQL (cont’d.)
Set operations
UNION, EXCEPT (difference), INTERSECT
Corresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL
Type compatibility is needed for these operations to be valid

Slide 6- 39

Their results are multisets
(duplicates are not eliminated) The behavior of these operations is illustrated by
the examples in Figure 6.5. Basically, each tuple—whether it is a duplicate or not—
is considered as a different tuple when applying these operations.

Copyright © 2016 and Shamkant B. Pattern Matching and Arithmetic Operators
LIKE comparison operator
Used for string pattern matching
% replaces an arbitrary number of zero or more characters
underscore (_) replaces a single character
Examples: WHERE Address LIKE ‘%Houston,TX%’;
WHERE Ssn LIKE ‘_ _ 1_ _ 8901’;
BETWEEN comparison operator

E.g., in Q14 :
WHERE(Salary BETWEEN 30000 AND 40000)
AND Dno = 5;
Slide 6- 40

Arithmetic Operations
Standard arithmetic operators:
Addition (+), subtraction (–), multiplication (*), and division (/) may be included as a part of SELECT

Query 13. Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.

SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND P.Pname=‘ProductX’;
Slide 6- *

Ordering of Query Results
Use ORDER BY clause
Keyword DESC to see result in a descending order of values
Keyword ASC to specify ascending order explicitly
Typically placed at the end of the query

ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC
Slide 6- *

Basic SQL Retrieval Query Block
Slide 6- 43

INSERT, DELETE, and UPDATE Statements in SQL
Three commands used to modify the database:
INSERT, DELETE, and UPDATE
INSERT typically inserts a tuple (row) in a relation (table)
UPDATE may update a number of tuples (rows) in a relation (table) that satisfy the condition
DELETE may also update a number of tuples (rows) in a relation (table) that satisfy the condition

Slide 6- 44

The INSERT Command
Specify the relation name and a list of values for the tuple. All values including nulls are supplied.

The variation below inserts multiple tuples where a new table is loaded values from the result of a query.

Slide 6- 46

The DELETE Command
Removes tuples from a relation
Includes a WHERE clause to select the tuples to be deleted. The number of tuples deleted will vary.

Slide 6- 49

Used to modify attribute values of one or more selected tuples
A WHERE-clause selects the tuples to be modified

Slide 6- 50

UPDATE (contd.)
Example: Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively

U5: UPDATE PROJECT
SET PLOCATION = ‘Bellaire’, DNUM = 5
WHERE PNUMBER=10

Slide 6- 51

程序代写 CS代考加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts