CS计算机代考程序代写 database SQL CS3402

CS3402
1
CS3402: Chapter 5
SQL: Structured Query Language

SQL: Structured Query Language
 Data Definition Language(DDL): define the structures in a database, i.e. create, modify, and remove database objects such as tables, indexes, and users. Commands include CREATE, ALTER, and DROP.
 Data Manipulation Language (DML): deal with the manipulation of data present in database. Commands include : INSERT, UPDATE , and DELETE.
 Data Query Language (DQL): make queries for data in databases. Commands include : SELECT.
Note:
 Each statement in SQL ends with a semicolon (;)
 SQL statements are case insensitive
CS3402 2

CS3402
3
DDL: Define database

CREATE Table
 Create Database:
CREATE SCHEMA database_name AUTHORIZATION user-name;
 Create Table: CREATE TABLE table_name (column_name1 data_type(size),
column_name2 data_type(size),….);
 Delete Table: DROP TABLE table-name;
 Update Table:
ALTER TABLE table-name
ADD Aj, Dj
(to add new attribute Aj with domain Dj to an existing table)
CS3402
4

SQL CREATE TABLE data definition statements for defining the COMPANY schema
Standard data types
CS3402
5

Data Types and Domains: Numeric
 Numeric data types include integer numbers of various sizes (INTEGER or INT, and SMALLINT) and floating-point numbers of various precision (FLOAT or REAL, and DOUBLE PRECISION).
 Formatted numbers can be declared by using DECIMAL(i, j) , where I (i.e., the precision) is the total number of decimal digits and j (i.e., the scale) is the number of digits after the decimal point. The default for scale is zero, and the default for precision is implementation-defined.
4
CS3402 6

Data Types and Domains: Character string
 Character string data types are either fixed length CHAR(n) or CHARACTER (n), where n is the number of characters or varying length VARCHAR (n) or CHAR VARYING(n) or CHARACTER VARYING(n), where n is the maximum number of characters.
 For fixed length strings , a shorter string is padded with blank characters to the right. For example, if the value ‘Smith’ is for an attribute of type CHAR(10), it is padded with five blank characters to become ‘Smith’ if needed. Padded blanks are generally ignored when strings are compared.
 When specifying a string value, it is placed between single quotation marks (apostrophes), and it is case sensitive ( a distinction is made between uppercase and lowercase)
CS3402 7

Data Types and Domains: Date and Time
 The DATE data type has ten positions, and its components are YEAR, MONTH, and DAY in the form YYYY-MM-DD.
 The TIME data type has at least eight positions, with the components HOUR, MINUTE, and SECOND in the form HH:MM:SS.
 Literal values are represented by single-quoted strings preceded by the keyword DATE or TIME; for example, DATE ‘2014-09-27’ or TIME ‘09:12:47’.
 Only valid dates and times should be allowed by the SQL implementation.
CS3402 8

Data Types and Domains: Boolean
 A Boolean data type is defined as Boolean and has the traditional values of TRUE or FALSE. In SQL, because of the presence of NULL values, a three-valued logic is used, so a third possible value for a Boolean data type is UNKNOWN.
Not
T
F
U
U
F
T
AND
T
U
F
T
T
U
F
U
F
U
U
F
F
F
F
OR
T
U
F
T
T
T
T
U
T
U
U
F
T
U
F
3-valued logic:
And
● Only T-T returns T
● And-ing F with anything results
with F
● The rest is UNKNOWN CS3402
Or
● Only F-F returns F
● Or-ing T with anything results
with T
● The rest is UNKNOWN
9

SQL CREATE TABLE data definition statements for defining the COMPANY schema
Indicate this column does not accept NULL value.
Define primary key
ensures that all values in a column are different
Define foreign key and its reference
CS3402
10

Attribute Constraints
 Because SQL allows NULLs as attribute values, a constraint NOT NULL may be specified if NULL is not permitted for a particular attribute. This is always implicitly specified for the attributes that are part of the primary key, but it can be specified for any other attributes whose values are required not to be NULL
 The PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation.
 The UNIQUE clause specifies alternate (unique) keys, also known as candidate keys.
 Referential integrity is specified via the FOREIGN KEY clause.
CS3402 11

SQL CREATE TABLE data definition statements for defining the COMPANY schema
CS3402 12

One possible database state for the COMPANY relational database schema
CS3402 13

One possible database state for the COMPANY relational database schema
CS3402 Slide 6- 13 14

CS3402
15
DQL: make query of data

The SELECT-FROM-WHERE Structure of Basic SQL Queries
 
Queries in SQL can be very complex. We will start with simple queries.
The basic form of the SELECT statement formed of the three clauses SELECT, FROM, and WHERE and has the following form:
Specified attributes
Title
Year
Length
Type
Star War
1977
124
Color
Mighty Duck
1991
104
Color
Wayne’s World
1992
95
Color
Satisfy the conditions
CS3402
16

Retrieval from a single table
Projection attributes
Selection conditions
 In SQL, the basic logical comparison operators for comparing attribute are =, <, <=, >, >=, and <>.
 Logic operator (AND, OR, NOT) can be used to connect multiple conditions. Priority is : NOT, AND, OR.
E.g. AANDBORNOTC equalsto(AANDB)OR(NOTC)
CS3402 17

Retrieval from two tables
Two tables
Join conditions
• The condition Dname=‘Research’ is a selection condition that chooses the particular tuple of interest in the DEPARTMENT table, because Dname is an attribute of DEPARTMENT.
• The condition Dnumber=Dnois called a join condition, because it combines two
tuples: one from DEPARTMENT and one from EMPLOYEE, whenever the value
of Dnumberin DEPARTMENT is equal to the value of Dnoin EMPLOYEE.
CS3402 18

Retrieval from two tables:Join Operation
AB
CDEF
XYZ
AAAA
BBBB
CCCC
X JOIN
ABC
DEF
ABCDEF
ABC
DEF
XYZ
ABC
DEF
AAAA
ABC
DEF
BBBB
ABC
DEF
CCCCC
CS3402
19

Retrieval from three tables
CS3402
20
Project joins
Department
Select-project-join query
Department joins Employee

NULL Value Comparisons
 When a record with NULL in one of its attributes is involved in a comparison operation, the result is considered to be UNKNOWN (it
may be TRUE or it may be FALSE).
SELECT Essn, FROM WORKS_ON WHERE HOURS > 0
(UNKNOWN result will not return!)
 In most SQL-based DBMSs, the special keyword NULL may be used to test for a NULL value.
 E.g., SELECT c-name FROM Deposit
WHERE balance IS NULL;
CS3402 (or balance IS NOT NULL;) 21

NULL Values Comparisons
Condition
Value of a
Evaluation
a IS NULL
10
FALSE
a IS NOT NULL
10
TRUE
a IS NULL
NULL
TRUE
a IS NOT NULL
NULL
FALSE
a = NULL
10
UNKNOWN
a = !NULL
10
UNKNOWN
a = NULL
NULL
UNKNOWN
a = !NULL
NULL
UNKNOWN
a = 10
NULL
UNKNOWN
a = 10
NULL
UNKNOWN
CS3402 22

Substring Pattern Matching


The LIKE comparison allows comparison conditions on only parts of a character string, using operator. This can be used for string pattern matching.
Partial strings are specified using two reserved characters: percentage (%) replaces an arbitrary number of zero or more characters, and the underscore (_) replaces a single character.
 Find the names of all employees whose first name has the substring ‘mm’ included
SELECT Fname, Minit, Lname FROM EMPOYEE
WHERE Fname LIKE “%mm%”;
(Note: if we use “ _ _ mm%”, then it becomes a special case) 3rd character (case sensitive)
CS3402
23

Ambiguous Attribute Names
 Same name can be used for two (or more) attributes in different relations, which may cause ambiguity.
 Must qualify the attribute name with the relation name to prevent ambiguity
 This is done by prefixing the relation name to the attribute name and separating the two by a period, e.g.,
Dnumber
CS3402
24

Aliasing, and Renaming
 The ambiguity of attribute names also arises in the case of queries that refer to the same relation twice.
 Must declare alternative relation, called aliases or tuple variables using AS
 E.g. Declare alternative relation names E and S to refer to the EMPLOYEE relation twice in a query:
Query 8. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.
SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn;
CS3402
25

Aliasing, and Renaming
 We can use this mechanism to rename any table in the WHERE clause, whether or not the same relation needs to be referenced more than once. In fact, this practice is recommended since it results in queries that are easier to comprehend.
 The attribute names can also be renamed
EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex,
Sal, Sssn, Dno)
 The “AS” may be dropped in most SQL implementations
EMPLOYEE E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex,
Sal, Sssn, Dno)
CS3402
26

Unspecified WHERE Clause
 Missing WHERE clause
 Indicates no condition on tuple selection (select ALL)
Values of all tuples
All possible combinations
CS3402
27

Use of the Asterisk
 Specify an asterisk (*)
 Retrieve all the attribute values of the selected tuples
EMPLOYEE: 100 tuples DEPARTMENT: 10 tuples
Finally, 1000 tuples and all attributes
CS3402
28


SQL allows the user to order the tuples in the result of a query by the values of one or more of the attributes that appear in the query result, by using the ORDER BY clause.
List first name of all employees in alphabetic order

Ordering Tuples
SELECT Fname FROM EMPOYEE ORDER BY Fname;
By default, in ascending order.

List the employee names in descending order of last name, and if several employees have the same last name, order them in ascending order by the first name
SELECT Fname, Minit, Lname
FROM EMPLOYEE
ORDER BY Lname DESC, Fname ASC;
CS3402
29

Tables as Sets in SQL
 SELECT does not automatically eliminate duplicate tuples (the attributes of two tuples have same values) in query results (NOT a set)
 Use the keyword DISTINCT in the SELECT clause  Only distinct tuples should remain in the result
 Specifying SELECT with neither ALL nor DISTINCT is equivalent to SELECT ALL.
CS3402 30
ALL:
Distinct:

Tables as Sets in SQL
 Set operations
UNION,INTERSECT, MINUS/EXCEPT(difference)
 These set operations apply only to type compatible relations , so we must make sure that the two relations on which we apply the operation have the same attributes and that the attributes appear in the same order in both relations.
Same attribute
The projects in the dept with “Smith” as manager
CS3402
31

Tables as Sets in SQL
 Set operations
UNION,INTERSECT, MINUS/EXCEPT(difference)
 These set operations apply only to type compatible relations , so we must make sure that the two relations on which we apply the operation have the same attributes and that the attributes appear in the same order in both relations.
Same attribute
The projects in the dept with “Smith” as manager
CS3402
32

Tables as Sets in SQL
 The relations resulting from these set operations UNION, INTERSECT, MINUS/EXCEPTaresetsoftuples;thatis,duplicate tuples are eliminated from the result.
 If we do not want to eliminate the duplicate tuples, we should use multisetsoperationsUNION ALL, INTERSECT ALL, MINUS/EXCEPT ALL.
CS3402 33

CS3402
34
DML: Manipulate data

INSERT Command
 INSERT is used to add a single tuple (row) to a relation (table ). We must specify the relation name and a list of values for the tuple.
The values for the attributes are obtained from the results of the SELECT statement
CS3402
35

DELETE Command
 DELETE command removes tuples from a relation. DELETE FROM table-name;
 (Note: this operation only deletes all tuples from the table and the table is still there)
 Includes a WHERE clause to select the tuples to be deleted
CS3402 36

UPDATE Command
 UPDATE command is used to modify attribute values of one or more selected tuples.
 Additional SET clause in the UPDATE command Specifies attributes to be modified and new values
CS3402 37

Views (Virtual Tables)
 Base table (base relation)
 Relation and its tuples are actually (physically) created and
stored as a file by the DBMS
 The tables are stored in the secondary storage in the specified
format
 Virtual table (view)
 Single table derived from other base tables temporarily.
 A view does not necessarily exist in physical form; it is just presented to the user through reconstruction (view) of base tables.
CS3402
38

Views (Virtual Tables)
 CREATE VIEW command
 In V1, attributes retain the names from base tables. In V2,
attributes are assigned new names
New attribute names
 We can think of a view as a way of specifying a table that we need to reference frequently, even though it may not exist physically.
CS3402 39

Views (Virtual Tables)
 Once a View is defined, SQL queries can use the View relation in the FROM clause. Views can be regarded and retrieved as ordinary tables.
E.g.,
SELECT Fname
FROM WORKS_ON1 WHERE HOURS > 5.0;
 View is always up-to-date
 Responsibility of the DBMS and not the user
 Change in base table will be reflected in the views
 DROP VIEW command
 DROP VIEW WORKS_ON1;
CS3402 40


Views (Virtual Tables)
View has limits on data modification operations.
e.g. Suppose we insert a tuple (“Mary”, “Black”, “ProjectX”, 10.0) into WORKS_ON1, it will cause other attribute has NULL value in the base table:( “Mary”, NULL, “Black”, NULL,… )in EMPLOYEE, ….
 Toavoidsuchproblemsandtosimplifyimplementation,mostSQL- based DBMSs restrict the following condition:
“A modification is permitted through a view ONLY IF the view is defined in terms of ONE base relation.”
CS3402
41