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