Copyright © 2016 Ramez Elmasri and Shamkant B.
Navathe
CHAPTER 6
Basic SQL
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 2
Chapter 6 Outline
SQL Data Definition and Data Types
Specifying Constraints in SQL
Basic Retrieval Queries in SQL
INSERT, DELETE, and UPDATE Statements in
SQL
Additional Features of SQL
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 3
Basic SQL
SQL language
Considered one of the major reasons for the commercial
success of relational databases
SQL
The origin of SQL is relational predicate calculus called tuple
calculus (see Ch.8) which was proposed initially as the language
SQUARE.
SQL Actually comes from the word “SEQUEL” which was the original term
used in the paper: “SEQUEL TO SQUARE” by Chamberlin and Boyce. IBM
could not copyright that term, so they abbreviated to SQL and copyrighted the
term SQL.
Now popularly known as “Structured Query language”.
SQL is an informal or practical rendering of the relational data
model with syntax
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 4
SQL Data Definition, Data Types,
Standards
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 Slide 6- 5
Schema and Catalog Concepts in
SQL
We cover the basic standard SQL syntax – there
are variations in existing RDBMS systems
SQL schema
Identified by a schema name
Includes an authorization identifier and descriptors
for each element
Schema elements include
Tables, constraints, views, domains, and other
constructs
Each statement in SQL ends with a semicolon
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 6
Schema and Catalog Concepts in
SQL (cont’d.)
CREATE SCHEMA statement
CREATE SCHEMA COMPANY AUTHORIZATION
‘Jsmith’;
Catalog
Named collection of schemas in an SQL
environment
SQL also has the concept of a cluster of catalogs.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 8
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 Slide 6- 9
The CREATE TABLE Command in
SQL (cont’d.)
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.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 10
COMPANY relational database
schema (Fig. 5.7)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 11
One possible database state for the
COMPANY relational database schema
(Fig. 5.6)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 12
One possible database state for the
COMPANY relational database schema –
continued (Fig. 5.6)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 13
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
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
Attribute Data Types and Domains in
SQL (cont’d.)
Bit-string data types
Fixed length: BIT(n)
Varying length: BIT VARYING(n)
Boolean data type
Values of TRUE or FALSE or NULL
DATE data type
Ten positions
Components are YEAR, MONTH, and DAY in the form
YYYY-MM-DD
Multiple mapping functions available in RDBMSs to
change date formats
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 18
Specifying Constraints in SQL
Basic constraints:
Relational Model has 3 basic constraint types that
are supported in SQL:
Key constraint: A primary key value cannot be
duplicated
Entity Integrity Constraint: A primary key value
cannot be null
Referential integrity constraints : The “foreign
key “ must have a value that is already present as
a primary key, or may be null.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 18
Specifying Attribute Constraints
Other Restrictions on attribute domains:
Default value of an attribute
DEFAULT <value>
NULL is not permitted for a particular attribute
(NOT NULL)
CHECK clause
Dnumber INT NOT NULL CHECK (Dnumber >
0 AND Dnumber < 21);
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 22
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 (called
CANDIDATE keys in the relational model).
Dname VARCHAR(15) UNIQUE;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 20
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- 21
Giving Names to Constraints
Using the Keyword CONSTRAINT
Name a constraint
Useful for later altering
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 22
Default attribute values and referential
integrity triggered action specification (Fig.
6.2)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 23
Specifying Constraints on Tuples
Using CHECK
Additional Constraints on individual tuples within a
relation are also possible using CHECK
CHECK clauses at the end of a CREATE TABLE
statement
Apply to each tuple individually
CHECK (Dept_create_date <=
Mgr_start_date);
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 24
Basic Retrieval Queries in SQL
SELECT statement
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 is strictly
set-theory based)
Multiset or bag behavior
Tuple-id may be used as a key
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 25
The SELECT-FROM-WHERE
Structure of Basic SQL Queries
Basic form of the SELECT statement:
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 26
The SELECT-FROM-WHERE Structure
of Basic SQL Queries (cont’d.)
Logical comparison operators
=, <, <=, >, >=, and <>
Projection attributes
Attributes whose values are to be retrieved
Selection condition
Boolean condition that must be true for any
retrieved tuple. Selection conditions include join
conditions (see Ch.8) when multiple relations are
involved.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 30
Basic Retrieval Queries
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 31
Basic Retrieval Queries (Contd.)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 32
Ambiguous Attribute Names
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 33
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];
Recommended practice to abbreviate names and to
prefix same or similar attribute from multiple tables.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 34
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 32
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 36
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 *
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 37
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 38
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 39
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
E.g., in Q14 :
WHERE(Salary BETWEEN 30000 AND 40000)
AND Dno = 5;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 40
Arithmetic Operations
Standard arithmetic operators:
Addition (+), subtraction (–), multiplication (*), and
division (/) may be included as a part of SELECT
Query 13. 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’;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 38
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 [Link] DESC, [Link] ASC,
[Link] ASC
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 39
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
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 45
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
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 [Link]=5)
WITH DATA;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Slide 6- 44
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 48
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
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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 50
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- 49
Summary
SQL
A Comprehensive language for relational database
management
Data definition, queries, updates, constraint
specification, and view definition
Covered :
Data definition commands for creating tables
Commands for constraint specification
Simple retrieval queries
Database update commands
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 55