Comp-3150: Database Management Systems

• Ramez Elmasri, Shamkant B. Navathe(2016) Fundamentals of Database Systems

(7th Edition), Pearson, isbn 10: 0-13-397077-9; isbn-13:978-0-13-397077-7.

• Chapter 6:
BASIC SQL

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 1

materials from Elmasri & Navathe, 7th
Chapter 6: BASIC SQL:
Outline
• 1. SQL Data Definition and Data Types
• 2. Specifying Constraints in SQL
• 3. Basic Retrieval Queries in SQL
• 4. INSERT, DELETE, and UPDATE Statements in SQL

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 2

materials from Elmasri & Navathe, 7th
1. SQL Data Definition and Data Types
 SQL language
 Considered one of the major reasons for the commercial success of
relational databases
 SQL stands for “structured Query Language” although it
originally came from the word SEQUEL in its introduction.
 SQL is one of the major reasons for the success of the
commercial relational model as it makes DB application
migration easy with most DBMS’s using it.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 3

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
SQL Standards
 SQL has gone through many standards: starting with SQL-86 or
SQL 1. SQL-92 is referred to as SQL-2.
 Later standards (from SQL-1999) are divided into core specification
and specialized extensions. The extensions are implemented for
different applications – such as data mining, data warehousing,
multimedia etc.
 SQL-2006 added XML features; In 2008 they added Object-oriented
features.
 SQL-3 is the current standard which started with SQL-1999. It is not
fully implemented in any RDBMS

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 4

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
SQL Standards
 Terminology:
 Table, row, and column are used for the relational model terms
relation, tuple, and attribute respectively.
 SQL language has features for :
 1. Data definition (called DDL or data definition language),
 2. Data Manipulation (called DML or data mainipulation language),
 3. Transaction control (Transact-SQL), (4) Indexing , (5) Security
specification (Grant and Revoke), etc. (called data control lang. or DCL)
 CREATE statement
 Main SQL command for data definition is the CREATE statement

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 5

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
The CREATE SCHEMA command in SQL
 We cover the basic standard SQL syntax – there are variations
in existing RDBMS systems
 1. SQL schema (that is the database):
 1. Identified by a schema name, and
 2. Includes an authorization identifier (eg ‘Jsmith’) to indicate the
owner of the schema and descriptors (eg. Grant select) for each
element
 Schema elements include
 Tables, constraints, views, domains, and other constructs
 Not all users are authorized to create schemas and schema
elements.
 Each statement in SQL ends with a semicolon
Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 6
materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
The CREATE SCHEMA command in SQL
• For example, to create a database schema called COMPANY, use:
 CREATE SCHEMA statement

 CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;

 The above does not list the schema elements and those
can be defined later.
 Catalog
 Named collection of schemas in an SQL environment
 A catalog contains a special schema called
INFORMATION_SCHEMA for providing information on all
schemas and element descriptors in these schemas.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 7

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
The CREATE TABLE Command in SQL
 2. The CREATE TABLE Command in SQL:
 Is used to specify a new relation and it:
 Provides name of the table
 Specifies attributes, their types and initial constraints
 Can optionally specify schema as:
 CREATE TABLE COMPANY.EMPLOYEE ...
or
 CREATE TABLE EMPLOYEE ...
 Note that the Oracle DBMS SQL implementation on
our CS server has data types VARCHAR2 (not
VARCHAR), NUMBER (not DECIMAL or INT).

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 8

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
The CREATE TABLE Command in SQL
• CREATE TABLE EMPLOYEE
(Fname VARCHAR2(15) NOT NULL,
Minit CHAR,
Lname VARCHAR2(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR2(15),
Sex CHAR,
Salary DECIMAL(10, 2),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY(Ssn));

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 9

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
The CREATE TABLE Command in SQL
 Base tables (base relations)
 Relation and its tuples are actually created and stored as a file by
the DBMS
 Virtual relations (views)
 Created through the CREATE VIEW statement. Do not
correspond to any physical file.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 10

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
COMPANY relational database schema (Fig. 5.7)

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 11

materials from Elmasri & Navathe, 7th Ed
One possible database state for the COMPANY relational
database schema (Fig. 5.6)

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 12

materials from Elmasri & Navathe, 7th Ed
One possible database state for the COMPANY relational
database schema – continued (Fig. 5.6)

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 13

materials from Elmasri & Navathe, 7th Ed
SQL CREATE TABLE data definition statements for defining
the COMPANY schema from Figure 5.7 (Fig. 6.1)

);

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 14

materials from Elmasri & Navathe, 7th Ed
SQL CREATE TABLE data definition statements for defining
the COMPANY schema from Figure 5.7 (Fig. 6.1)

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 15

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
The CREATE TABLE Command
 Some foreign keys may cause errors
 Specified either via:
 Circular references
 Or because they refer to a table that has not yet been created
 Example circular reference is foreign key Super_ssn in
EMPLOYEE table which refers to Ssn in the EMPLOYEE table.
 The foreign key Dno in EMPLOYEE table refers to the DEPARTMENT
table not yet created.
 One solution to these problems is to leave the constraints out during
the initial creation of the table and use the ALTER TABLE statement to
change them when they can no longer cause a violation.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 16

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
Attribute Data Types and Domains in SQL
• 3. Attribute Data Types and Domains in SQL
 Basic data types (Six main types)

 1. Numeric data types

 i. Integer numbers: INTEGER, INT, and SMALLINT
 ii. Floating-point (real) numbers: FLOAT or REAL, and DOUBLE
PRECISION
 2. Character-string data types
 i. Fixed length: CHAR(n), CHARACTER(n)
 ii. Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER
VARYING(n)
 Varying large text: CHARACTER LARGE OBJECT or CLOB.
Eg. CLOB(20M) specifies a maximum of 20MB of text.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 17

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
Attribute Data Types and Domains in SQL
 3. Bit-string data types
 i. Fixed length: BIT(n), e.g., B’10101’
 ii. Varying length: BIT VARYING(n)
 iii.BLOB,e.g. BLOB(30G) for large binary values like
images.
 4. Boolean data type
 Values of TRUE or FALSE or NULL
 5. DATE data type
 hasTen positions with Components as YEAR, MONTH, and DAY in the
form YYYY-MM-DD
 Multiple mapping functions available in RDBMSs to change date formats
 Note that the date format on our CS system is dd-mon-yy. For example,
12-aug-55 for 12th August, 1955.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 18

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
Attribute Data Types and Domains in SQL
 6. Additional data types
 i. Timestamp data type which Includes the DATE and TIME fields
 Plus a minimum of six positions for decimal fractions of seconds
 Optional WITH TIME ZONE qualifier
 Eg. TIMESTAMP ‘2014-09-27 09:12:47.648302’
 ii. 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.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 19

materials from Elmasri & Navathe, 7th Ed
1. SQL Data Definition and Data Types:
Attribute Data Types and Domains in SQL
 Create Domain statement
 can be used to declare the data type of an attribute as
SSN_TYPE
 Example:
 CREATE DOMAIN SSN_TYPE AS CHAR(9);
 It makes it easier to change the data type for a domain that is
used by numerous attributes
 And use of domain improves schema readability
• Create TYPE command can be used to create User Defined Types
(UDTs).

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 20

materials from Elmasri & Navathe, 7th Ed
2. Specifying Constraints in SQL
Basic constraints:
 Relational Model has 3 basic constraint types that are
supported in SQL:
 1. Key constraint: A primary key value cannot be duplicated
 2. Entity Integrity Constraint: A primary key value cannot be null
 3. Referential integrity constraints : The “foreign key “ must
have a value that is already present as a primary key, or may be
null.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 21

materials from Elmasri & Navathe, 7th Ed
2. Specifying Constraints in SQL
Other 3 Restrictions on attribute domains are:
 4. Default value of an attribute
 DEFAULT <value>
 5. NULL is not permitted for a particular attribute (NOT NULL)
 6. CHECK clause for restricting attribute or domain values at
declaration of attribute
 Dnumber INT NOT NULL CHECK (Dnumber > 0 AND
Dnumber < 21);
• An example CHECK clauses at the end of a CREATE TABLE statement
• CHECK (Dept_create_date <= Mgr_start_date);

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 22

materials from Elmasri & Navathe, 7th Ed
2. Specifying Constraints in SQL
 1. Specifying Key and Referential Integrity Constraints
 i. PRIMARY KEY clause
 Specifies one or more attributes that make up the primary key of
a relation, e.g.,
 Dnumber INT PRIMARY KEY;
 ii. UNIQUE clause
 Specifies alternate (secondary) keys (called CANDIDATE keys in
the relational model), e.g.,.
 Dname VARCHAR(15) UNIQUE;

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 23

materials from Elmasri & Navathe, 7th Ed
2. Specifying Constraints in SQL
 2. ENTITY integrity is enforced from PRIMARY
KEY clause although the NOT NULL can still be
specified.
 3. FOREIGN KEY clause
 Default operation: reject update on violation
 Attach referential triggered action clause
 Options include SET NULL, CASCADE, and SET DEFAULT
 Action taken by the DBMS for SET NULL or SET DEFAULT is the
same for both ON DELETE and ON UPDATE
 CASCADE option suitable for “relationship” relations

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 24

materials from Elmasri & Navathe, 7th Ed
2. Specifying Constraints in SQL
• Giving Names to Constraints
 Using the Keyword CONSTRAINT
 a constraint can be named for easy later reference and use
 Fig 6.1 and 6.2 given next show some example
uses of these constraints while creating
tables.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 25

materials from Elmasri & Navathe, 7th Ed
2. Specifying Constraints in SQL: Figure 6.1 (continued) SQL
CREATE TABLE data definition statements for defining the
COMPANY schema from Figure 5.7.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 26

materials from Elmasri & Navathe, 7th Ed
2. Specifying Constraints in SQL: Figure 6.2 Example illustrating
how default attribute values and referential integrity triggered
actions are specified in SQL.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 27

materials from Elmasri & Navathe, 7th Ed
3. Basic Retrieval Queries in SQL

 SELECT statement
 Is 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
 Unlike relational model (relational model which is strictly set-
theory based and a set does not allow duplicate elements)
 In SQL Multiset or bag behavior is possible
 Tuple-id may be used as a key to enforce tuple uniqueness
although this cannot be guaranteed in retrieved results.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 28

materials from Elmasri & Navathe, 7th Ed
3. Basic Retrieval Queries in SQL: The SELECT-FROM-
WHERE Structure of Basic SQL Queries
 Basic form of the SELECT statement:

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 29

materials from Elmasri & Navathe, 7th Ed
3. Basic Retrieval Queries in SQL: The SELECT-FROM-
WHERE Structure of Basic SQL Queries
 Logical comparison operators are:
 =, <, <=, >, >=, and <> (not equal)
 Projection attributes are:
 Attributes whose values are to be retrieved
 Selection condition is:
 Boolean condition that must be true for any retrieved tuple.
Selection conditions include join conditions when multiple
relations are involved.
 A Boolean condition (expression) can be a literal value, an
attribute value, or an expression or a conjunction (AND),
disjunction (OR), or negation (NOT) of an expression.
Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 30
materials from Elmasri & Navathe, 7th Ed
Basic Retrieval Queries

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 31

materials from Elmasri & Navathe, 7th Ed
Basic Retrieval Queries (Contd.)

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 32

materials from Elmasri & Navathe, 7th Ed
Ambiguous Attribute Names
 A query that involves only selection and join conditions plus
projection of attributes is called a select-project-join (spj) query.
 Same name can be used for two (or more) attributes in
different relations
 As long as the attributes are in different relations
 Must qualify the attribute name with the relation name to prevent
ambiguity as in Q1A below.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 33

materials from Elmasri & Navathe, 7th Ed
Aliasing, and Renaming
 Aliases or tuple variables can be used to:
 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;
 Recommended practice is to abbreviate names and to prefix same or
similar attribute from multiple tables.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 34

materials from Elmasri & Navathe, 7th Ed
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
 The “AS” may be dropped in most SQL implementations

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 35

materials from Elmasri & Navathe, 7th Ed
Unspecified WHERE Clause
and Use of the Asterisk
 Missing WHERE clause
 Indicates no condition on tuple selection
 Effect is a CROSS PRODUCT
 Result is all possible tuple combinations (or the Algebra
operation of Cartesian Product– see Ch.8) result

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 36

materials from Elmasri & Navathe, 7th Ed
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 . *

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 37

materials from Elmasri & Navathe, 7th Ed
Tables as Sets in SQL
 SQL does not automatically eliminate duplicate tuples in query results
 For aggregate operations (See sec 7.1.7) duplicates must be accounted
for
 Use the keyword DISTINCT in the SELECT clause
 Only distinct tuples should remain in the result

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 38

materials from Elmasri & Navathe, 7th Ed
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

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 39

materials from Elmasri & Navathe, 7th Ed
Substring Pattern Matching and Arithmetic Operators
 LIKE comparison operator is
 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 : Retrieve all employees in dept 5 whose salary is between
$30,000 and $40,000.
SELECT * FROM EMPLOYEE
WHERE(Salary BETWEEN 30000 AND 40000)
AND Dno = 5;

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 40

materials from Elmasri & Navathe, 7th Ed
Arithmetic Operations
 Standard arithmetic operators:
 Addition (+), subtraction (–), multiplication (*), and division (/) may
be included as a part of SELECT clause

 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’;

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 41

materials from Elmasri & Navathe, 7th Ed
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

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 42

materials from Elmasri & Navathe, 7th Ed
Basic SQL Retrieval Query Block

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 43

materials from Elmasri & Navathe, 7th Ed
4. 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

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 44

materials from Elmasri & Navathe, 7th Ed
INSERT
 In its simplest form, it is used to add one or more tuples to a
relation
 Attribute values should be listed in the same order as the
attributes were specified in the CREATE TABLE command
 Constraints on data types are observed automatically
 Any integrity constraints as a part of the DDL specification are
enforced

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 45

materials from Elmasri & Navathe, 7th Ed
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.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 46

materials from Elmasri & Navathe, 7th Ed
BULK LOADING OF TABLES
 Another variation of INSERT is used for bulk-loading of several
tuples into tables
 A new table TNEW can be created with the same attributes as T
and using LIKE and DATA in the syntax, it can be loaded with entire
data.
 EXAMPLE
CREATE TABLE D5EMPS LIKE EMPLOYEE
(SELECT E.*
FROM EMPLOYEE AS E
WHERE E.Dno=5)
WITH DATA;

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 47

materials from Elmasri & Navathe, 7th Ed
DELETE
 Removes tuples from a relation
 Includes a WHERE-clause to select the tuples to be deleted

 Referential integrity should be enforced

 Tuples are deleted from only one table at a time (unless CASCADE is

specified on a referential integrity constraint)

 A missing WHERE-clause specifies that all tuples in the relation are to

be deleted; the table then becomes an empty table

 The number of tuples deleted depends on the number of tuples in the

relation that satisfy the WHERE-clause

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 48

materials from Elmasri & Navathe, 7th Ed
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.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 49

materials from Elmasri & Navathe, 7th Ed
UPDATE
 Used to modify attribute values of one or more selected tuples
 A WHERE-clause selects the tuples to be modified
 An additional SET-clause specifies the attributes to be modified
and their new values
 Each command modifies tuples in the same relation
 Referential integrity specified as part of DDL specification is
enforced

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 50

materials from Elmasri & Navathe, 7th Ed
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

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 51

materials from Elmasri & Navathe, 7th Ed
UPDATE (contd.)
 Example: Give all employees in the 'Research' department a 10% raise in
salary.
U6:UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research')

 In this request, the modified SALARY value depends on the original

SALARY value in each tuple

 The reference to the SALARY attribute on the right of = refers to the

old SALARY value before modification
 The reference to the SALARY attribute on the left of = refers to the
new SALARY value after modification

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 52

materials from Elmasri & Navathe, 7th Ed
Fig. 6.4: Results of Queries Q11, Q11A, Q16 and Q18

Figure 6.4 Results of additional SQL queries when applied to the

COMPANY database state shown in Figure 5.6. (a) Q11. (b) Q11A. (c)
Q16. (d) Q18.

Comp-3150 Dr. C. I. Ezeife (2023) with Figures and some 53

materials from Elmasri & Navathe, 7th Ed