Chapter 4

BASIC SQL
SQL
 The name SQL is presently expanded as Structured
Query Language. Originally, SQL was called
SEQUEL (Structured English QUEry Language) and
was designed and implemented at IBM Research as
the interface for an experimental relational database
system called SYSTEM R.
 SQL is a comprehensive database language: It has
statements for data definitions, queries, and updates.
Hence, it is both a DDL and a DML.
 In addition, it has facilities for defining views, for
specifying security and authorization for defining
integrity constraints.
Outline
 SQL Data Definition and Data Types
 Specify Constraints in SQL
 Basic Retrieval in SQL
 INSERT, DELETE and UPDATE
Statement
Data Definition, Constraints,
and Schema Changes
 Used to CREATE, DROP, and ALTER the
descriptions of the tables (relations) of
a database

Slide 8-4
CREATE SCHEMA
 Specifies a new database schema by
giving it a name

 CREATE SCHEMA COMPANY

AUTHORIZATION ‘Jsmith’;
CREATE TABLE
 Specifies a new base relation by giving it a
name, and specifying each of its attributes
and their data types (INTEGER, FLOAT,
DECIMAL(i,j), CHAR(n), VARCHAR(n))
 A constraint NOT NULL may be specified on
an attribute
CREATE TABLE DEPARTMENT
( DNAME VARCHAR(10)NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9) );
CREATE TABLE
 In SQL, can use the CREATE TABLE command for
specifying the primary key attributes, secondary
keys, and referential integrity constraints (foreign
keys).
 Key attributes can be specified via the PRIMARY KEY
and UNIQUE phrases

CREATE TABLE DEPT

( DNAME VARCHAR(10)NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP );
Attribute Types
 Numeric data types include integer
numbers of various sizes (INTEGER or
INT, and SMALLINT) and floating-point
(real).
 Character-string data types are either
fixed length—CHAR(n) or
CHARACTER(n), VARCHAR(n) where n
is the maximum number of characters.
Attribute Types (2)
 Bit-string data types are either of fixed
length n—BIT(n)—or varying length—BIT
VARYING(n), where n is the maximum
number of bits.
 A Boolean data type 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. We
Attribute Types (3)
 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.
Attribute Types (4)
 A timestamp data type (TIMESTAMP)
includes the DATE and TIME fields, plus a
minimum of six positions for decimal fractions
of seconds and an optional WITH TIME ZONE
qualifier. Literal values are represented by
single quoted strings preceded by the
keyword TIMESTAMP, with a blank space
between data and time; for example,
TIMESTAMP ‘2008-09-27 09:12:47.648302’.
DROP TABLE
 Used to remove a relation (base table)
and its definition
 The relation can no longer be used in
queries, updates, or any other
commands since its description no
longer exists
 Example:

DROP TABLE DEPENDENT;

ALTER TABLE
 Used to add an attribute to one of the base relations
 The new attribute will have NULLs in all the tuples of
the relation right after the command is executed;
hence, the NOT NULL constraint is not allowed for
such an attribute
 Example:
ALTER TABLE EMPLOYEE ADD JOB
VARCHAR(12);

 The database users must still enter a value for the

new attribute JOB for each EMPLOYEE tuple. This
can be done using the UPDATE command.
REFERENTIAL INTEGRITY
OPTIONS
 We can specify RESTRICT, CASCADE, SET NULL or
SET DEFAULT on referential integrity constraints
(foreign keys)

CREATE TABLE DEPT

( DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSNCHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
ON DELETE SET DEFAULT ON UPDATE
CASCADE ); Slide 8-14
REFERENTIAL INTEGRITY
OPTIONS (continued)
CREATE TABLE EMP
( ENAMEVARCHAR(30)NOT NULL,
ESSN CHAR(9),
BDATE DATE,
DNO INTEGER DEFAULT 1,
SUPERSSN CHAR(9),
PRIMARY KEY (ESSN),
FOREIGN KEY (DNO) REFERENCES DEPT
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (SUPERSSN) REFERENCES EMP
ON DELETE SET NULL ON UPDATE CASCADE );
Outline
 SQL Data Definition and Data Types
 Specify Constraints in SQL
 Basic Retrieval in SQL
 INSERT, DELETE and UPDATE
Statement
Constraints in SQL
 It is possible to apply DEFAULT value or
apply CHECK on domain of attribute.

 Tuple based constraints can also be put

at the end of definition of table,
Outline
 SQL Data Definition and Data Types
 Specify Constraints in SQL
 Basic Retrieval in SQL
 INSERT, DELETE and UPDATE
Statement
SELECT-FROM-WHERE Clause
 The basic form of the SELECT
statement, sometimes called a
mapping or a select-from-where
block, is formed of the three clauses
SELECT, FROM, and WHERE and has
the following form:
 SELECT <attribute list>
 FROM <table list>
 WHERE <condition>;
SELECT FROM WHERE
SELECT FROM WHERE
SELECT FROM WHERE
 In Q1, thhe 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 = Dno is called a
join condition, because it combines two
tuples
SELECT FROM WHERE
Ambiguous Attribute Names, Aliasing,
Renaming, and Tuple Variables
Ambiguous Attribute Names, Aliasing,
Renaming, and Tuple Variables
Unspecified WHERE clause
Use of Asterisk
Use of DISTINT and ALL
UNION Operation
Set operations in SQL
 SQL has directly incorporated some of
the set operations from mathematical
set theory, which are also part of
relational algebra (Chapter 6).
 There are set union (UNION), set
difference (EXCEPT), and set
intersection (INTERSECT) operations.
Set operations in SQL
 SQL also has corresponding multiset
operations, which are followed by the
keyword ALL (UNION ALL, EXCEPT ALL,
INTERSECT ALL). Their results are
multi-sets.
Set operations in SQL
Pattern Matching (Use of
LIKE)
Pattern Matching (Use of
LIKE)
Use of Arithmetic Operators
Use of BETWEEN
Use of ORDER BY
ORDER BY
 The default order is in ascending order
of values. We can specify the keyword
DESC if we want to see the result in a
descending order of values.
 The keyword ASC can be used to
specify ascending order explicitly.
 ORDER BY D.Dname DESC, E.Lname
ASC, E.Fname ASC
THE EXISTS FUNCTION (cont.)
 Query 12: Retrieve the name of each employee
who has a dependent with the same first name
as the employee.

Q12B: SELECT FNAME, LNAME

FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN AND
FNAME=DEPENDENT_NAME)

Slide 8-39
THE EXISTS FUNCTION (cont.)
 Query 6: Retrieve the names of employees who have
no dependents.

Q6: SELECT FNAME, LNAME

FROMEMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)

 In Q6, the correlated nested query retrieves all DEPENDENT

tuples related to an EMPLOYEE tuple. If none exist , the
EMPLOYEE tuple is selected
 EXISTS is necessary for the expressive power of SQL

Slide 8-40
EXPLICIT SETS
 It is also possible to use an explicit (enumerated)
set of values in the WHERE-clause rather than a
nested query
 Query 13: Retrieve the social security numbers of all
employees who work on project number 1, 2, or 3.

Q13:SELECT DISTINCT ESSN

FROMWORKS_ON
WHERE PNO IN (1, 2, 3)

Slide 8-41
NULLS IN SQL QUERIES
 SQL allows queries that check if a value is NULL
(missing or undefined or not applicable)
 SQL uses IS or IS NOT to compare NULLs because
it considers each NULL value distinct from other
NULL values, so equality comparison is not
appropriate .
 Query 14: Retrieve the names of all employees who
do not have supervisors.
Q14: SELECT FNAME, LNAME
FROMEMPLOYEE
WHERE SUPERSSN IS NULL
Note: If a join condition is specified, tuples withSlide 8-42
Outline
 SQL Data Definition and Data Types
 Specify Constraints in SQL
 Basic Retrieval in SQL
 INSERT, DELETE and UPDATE
Statement
Modification in Database
 In SQL, three commands can be used
to modify the database: INSERT,
DELETE, and UPDATE.
Use of INSERT
Use of DELETE
Use of UPDATE