CHAPTER 6
Basic SQL
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Categories of Data Models
ER Model Relational
EER Model Model
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 2
Basic SQL
◼ SQL language
◼ Considered one of the major reasons for the
commercial success of relational databases
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
SQL Data Definition
◼ Terminology:
◼ Table, row, and column used for relational model
terms relation, tuple, and attribute
◼ CREATE statement
◼ Main SQL command for data definition
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Schema and Catalog Concepts in
SQL (cont’d.)
CREATE SCHEMA
schemaname
[AUTHORIZATION
ownername];
◼ CREATE SCHEMA
COMPANY
AUTHORIZATION
‘Jsmith’;
◼ System schemas
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
The CREATE TABLE Command in SQL
◼ Specifying a new relation
◼ Provide name of table
◼ Specify attributes, their types and initial
constraints
◼ Can optionally specify schema:
◼ CREATE TABLE [Link] ...
OR
◼ CREATE TABLE EMPLOYEE ...
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Attribute Data Types and Domains in
SQL
◼ Basic data types
◼ Numeric data types
◼ Integer numbers: INTEGER, INT, and SMALLINT
◼ Floating-point (real) numbers: FLOAT or REAL, and
DOUBLE PRECISION
◼ Character-string data types
◼ Fixed length: CHAR(n), CHARACTER(n)
◼ Varying length: VARCHAR(n), CHAR
VARYING(n), CHARACTER VARYING(n)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 17
COMPANY relational database
schema (model)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 11
SQL CREATE TABLE data definition
statements for defining EMPLOYEE table
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 9
SQL CREATE TABLE data definition statements
for defining the COMPANY schema from Figure
5.7 (Fig. 6.1)
continued on next slide
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 14
SQL CREATE TABLE data definition
statements for defining the COMPANY
schema from Figure 5.7 (Fig. 6.1)-continued
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 15
Specifying Key and Referential
Integrity Constraints
◼ PRIMARY KEY clause
◼ Specifies one or more attributes that make up the
primary key of a relation
◼ Dnumber INT PRIMARY KEY;
◼ UNIQUE clause
◼ Specifies alternate (secondary) keys
(CANDIDATE keys).
◼ Dname VARCHAR(15) UNIQUE;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 12
Specifying Key and Referential
Integrity Constraints (cont’d.)
◼ 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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 13
The SELECT-FROM-WHERE
Structure of Basic SQL Queries
◼ Basic form of the SELECT statement:
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic Retrieval Queries
Output:
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic Retrieval Queries
Output:
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic Retrieval Queries
Fname Lname Address
John Smith 731 Fondres, Houston, TX
Franklin Wong 638 Voss, Houston, TX
Output:
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyce English 5631 Rice, Houston, TX
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic Retrieval Queries
Fname Lname Address
John Smith 731 Fondres, Houston, TX
Franklin Wong 638 Voss, Houston, TX
Output:
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyce English 5631 Rice, Houston, TX
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic Retrieval Queries (Contd.)
Output:
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic Retrieval Queries (Contd.)
Output:
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Aliasing, and Renaming
◼ Aliases or tuple variables
◼ 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 [Link], [Link], [Link], [Link]
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=[Link];
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Substring Pattern Matching and
Arithmetic Operators
◼ LIKE comparison operator
◼ 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
WHERE(Salary BETWEEN 30000 AND 40000) AND Dno = 5;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic Retrieval Queries
◼ Query: Show the resulting salaries if every employee working on the
‘ProductX’ project is given a 10 percent raise.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 23
Basic Retrieval Queries
◼ Query: Show the resulting salaries if every employee working on the
‘ProductX’ project is given a 10 percent raise.
SELECT [Link], [Link], 1.1 * [Link] AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE [Link]=[Link] AND [Link]=[Link] AND
[Link]=‘ProductX’;
Output
Fname Lname Increased_sal
John Smith 33000
Joyce English 27500
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 24
Basic SQL Retrieval Query Block
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 43
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 44
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.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 46
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.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 49
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 51
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 30
End of CHAPTER 6
Basic SQL
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe