CS3402: Chapter 5

SQL: Structured Query Language

CS3402 1
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 commands are case insensitive
 Despite the existence of the standard, SQL code is not completely
portable among different database systems without adjustments.
CS3402 2
 DDL: Define database

CS3402 3
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 ALTER TABLE table-name
ADD Aj, Dj; DROP Aj;
(to add new attribute Aj with (to delete attribute Aj from
domain Dj to an existing table) 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 AND T U F OR T U F
3-valued
T F T T U F T T T T
logic:
U U U U U F U T U U
F T F F F F F T U F

And Or
● Only T-T returns T ● Only F-F returns F
● And-ing F with anything results ● Or-ing T with anything results
with F with T
● The rest is UNKNOWN ● The rest is UNKNOWN
CS3402 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
Adding Constrains after creating
table

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

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

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

Slide 6- 13
CS3402 15
 DQL: make query of data

CS3402 16
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

Satisfy the Title Year Length Type

Star War 1977 124 Color
conditions Mighty Duck 1991 104 Color
Wayne’s World 1992 95 Color
CS3402 17
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. A AND B OR NOT C equals to (A AND B) OR (NOT C )

CS3402 18
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 19
Retrieval from two tables:Join
Operation
C D E F
A B XYZ
AAAA
ABC DEF X BBBB
JOIN CCCC

A B C D E F
ABC DEF XYZ
ABC DEF AAAA
ABC DEF BBBB
ABC DEF CCCCC

CS3402 20
Retrieval from three tables

Select-project-join
query

Project joins Department joins

Department Employee
CS3402 21
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;
(or balance IS NOT NULL;)
CS3402 22
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 23
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 EMPLOYEE
WHERE Fname LIKE ‘%mm%’;
(Note: if we use ‘ _ _ mm%’, then it becomes a special case)
3rd character (case sensitive)
CS3402 24
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.,
 Find the first name and address of employee who belongs to
the Research department.

Dnumber
CS3402 25
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 26
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 27
 Unspecified WHERE Clause

 Missing WHERE clause

 Indicates no condition on tuple selection (select ALL)

All possible
Values of all combinations
tuples

CS3402 28
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 29
 Ordering Tuples

 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

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

• 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 30
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

ALL: Distinct:

 Specifying SELECT with neither ALL nor DISTINCT is equivalent to

SELECT ALL.
CS3402 31
Tables as Sets in SQL
 Set operations
 UNION, INTERSECT, MINUS/EXCEPT 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

The projects with

CS3402 “Smith” as the worker
32
Tables as Sets in SQL
 The relations resulting from these set operations UNION,
INTERSECT, MINUS/EXCEPT are sets of tuples; that is, duplicate
tuples are eliminated from the result.

 If we do not want to eliminate the duplicate tuples, we should use

multisets operations UNION ALL, INTERSECT ALL,
MINUS/EXCEPT ALL.

CS3402 33
 DML: Manipulate data

CS3402 34
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, ….

 To avoid such problems and to simplify implementation, most SQL-

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
Summary of SQL Syntax

CS3402 42
Summary of SQL Syntax

CS3402 43
References

 6e
● Ch. 4. p. 83 – 107
● Ch. 5, p. 111 – 126

CS3402 44