BASIC SQL
CHAPTER 4 (6/E)
CHAPTER 8 (5/E)
1
LECTURE OUTLINE
SQL Data Definition and Data Types
Specifying Constraints in SQL
Basic Retrieval Queries in SQL
Set Operations in SQL
2
BASIC SQL
Structured Query Language
Considered one of the major reasons for the commercial success of relational databases
Statements for data definitions, queries, and updates
• it is both a DDL and DML
• Can be used for defining views on the database, for specifying
security and authorization, for defining integrity constraints, and for
specifying transaction controls.
• It also has rules for embedding SQL statements into a general-
purpose programming language such as Java, COBOL, or C/C++.1
• The later SQL standards (starting with SQL:1999) are divided into a
Core specification plus specialized extensions
• Core specification: is implemented by all RDBMS vendors that are SQL compliant.
• The extensions : are optional modules to be purchased independently for specific database
applications such as data mining, spatial data, temporal data, data warehousing, online
analytical processing (OLAP), multimedia data, and so on.
3
BASIC SQL
Terminology:
Relational Model SQL
relation table
tuple row
attribute column
Syntax notes:
• Some interfaces require each statement to end with a semicolon.
• SQL is not case-sensitive.
4
DATA DEFINITION LANGUAGE (DDL)
5
CREATE TABLE COMMAND
To create a new relation :
• Provide name of relation
• Specify attributes and initial constraints and data types
• Base tables (base relations)
• Relation and its tuples are physically stored and managed by DBMS
Include information for each column (attribute) plus constraints
• Column name
• Column type (domain)
• Key, uniqueness, and null constraints
6
7
8
DATA TYPE EXAMPLES
Attempting to assign a value containing
more characters than the defined length
results in the truncation of the character
string to the defined length. If any of the
truncated characters are not blank, an error
is raised.
9
EXAMPLES
Integers are stored in 32 bits, as signed numbers
- where i, the precision, is the total number of
decimal digits
- and j, the scale, is the number of digits after
the decimal point.
- NOTE: If you exceed the number of digits expected
to the left of the decimal point, an error is thrown. If
you exceed the number of expected digits to the
right of the decimal point, the extra digits are
truncated.
10
11
INTEGRITY CONSTRAINTS IN CREATE TABLE
Columns can be declared to be NOT NULL
Columns can be declared to have a default value
• Assigned to column in any tuple for which a value is not specified
Example
CREATE TABLE EMPLOYEE (
…
NICKNAME VARCHAR(20) DEFAULT NULL,
…
Province CHAR(2) NOT NULL DEFAULT 'ON',
…
);
12
);
);
13
SPECIFYING KEY CONSTRAINTS
PRIMARY KEY clause
• Specifies one or more attributes that make up the primary key of a
relation
Dnumber INT NOT NULL PRIMARY KEY,
• Primary key attributes must be declared NOT NULL
UNIQUE clause
• Specifies alternate (candidate) keys
Dname VARCHAR(15) UNIQUE;
• May or may not allow null values, depending on declaration
If no key constraints, two or more tuples may be identical in all columns.
• SQL deviates from pure relational model!
Unique constrain means that no two tuples in the relation can be
equal for this attribute
Note:
14
EXAMPLES
create table m(
create table m( p char(6) not null,
p char(6) not null primary key, pname char(20) default 'x',
pname char(20) default 'x', Same as color char(10) default NULL ,
color char(10) default NULL, weigth smallint,
weigth smallint, city char(15),
city char(15) primary key(p));
);
To define a primary key of both attribute p
and color
create table mm(
p char(6) not null,
pname char(20) default 'x',
color char(10) default NULL ,
weigth smallint,
city char(15),
15
primary key(p,color));
REFERENTIAL CONSTRAINTS
FOREIGN KEY clause
FOREIGN KEY (Dept) REFERENCES DEPARTMENT (Dnum),
• Default operation: reject update on violation
• Attach referential triggered action clause in case referenced
tuple is deleted
• Options include SET NULL, CASCADE, and SET DEFAULT
Foreign key declaration must refer to a table already created
-In general, the action taken by the DBMS for SET NULL or SET DEFAULT is the same
for both ON DELETE and ON UPDATE:
The value of the affected referencing attributes is changed to NULL for SET
NULL and to the specified default value of the referencing attribute for SET DEFAULT.
-The action for CASCADE ON DELETE is to delete all the referencing tuples, whereas the
action for CASCADE ON UPDATE is to change the value of the referencing foreign key
attribute(s) to the updated (new) primary
key value for all the referencing tuples.
16
SPECIFYING TUPLE CONSTRAINTS
Some constraints involve several columns
CHECK clause at the end of a CREATE TABLE statement
• Apply to each tuple individually
Example
• CHECK (Dept_create_date <= Mgr_start_date)
• Example2:
create table e11(ename varchar2(10),empno
number(6) constraint ch check(empno>100));
Note : the above statement can also be written
as :
create table e11(empno number(6), ename
varchar2(10), constraint ch check(empno>100));
17
EXAMPLE
Recall Employee example:
18
Note: ON UPDATE is not
supported in Oracle Database.
Use trigger to implement it
19
20
EXAMPLE1
Persons Orders
CREATE TABLE Persons ( CREATE TABLE Orders (
PersonID int Primary Key Not Null, OrderID int NOT NULL,
LastName varchar(255), OrderNumber int NOT NULL,
FirstName varchar(255), PersonID int,
Address varchar(255), PRIMARY KEY (OrderID),
City varchar(255) FOREIGN KEY (PersonID) REFERENCES
); Persons(PersonID) ON delete CASCADE);
After deleting the records from table persons where
personid=1 the table orders will become
DELETE FROM persons WHERE personid=1;
21
EXAMPLE2
Persons Orders
CREATE TABLE Persons ( CREATE TABLE Orders (
PersonID int Primary Key Not Null, OrderID int NOT NULL ,
LastName varchar(255), OrderNumber int NOT NULL,
FirstName varchar(255), PersonID int,
Address varchar(255), PRIMARY KEY (OrderID),
City varchar(255) FOREIGN KEY (PersonID) REFERENCES
); Persons(PersonID) ON DELETE set NULL
);
After deleting the records from table persons where
personid=1 the table orders will become
DELETE FROM persons WHERE personid=1;
22
NOTES
1. Oracle does not support cascading updates. It also does not allow you to
set the value to the default when the parent row is deleted. Your two
options for an on delete behavior are cascade or set null.
2. The default constraint should come before the NOT NULL constrain as
shown below
CREATE TABLE Orders (
OrderID int NOT NULL ,
OrderNumber int NOT NULL,
PersonID int DEFAULT 9 NOT NULL ,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES
Persons(PersonID) ON DELETE set default
);
23
BASIC SQL RETRIEVAL QUERIES
All retrievals use SELECT statement:
SELECT <return list>
FROM <table list>
[ WHERE <condition> ] ;
where
<return list> is a list of expressions or column names whose
values are to be retrieved by the query
<table list> is a list of relation names required to process the
query
<condition> is a Boolean expression that identifies the tuples
to be retrieved by the query
Example
SELECT title, year, genre
FROM Film
WHERE director = 'Steven Spielberg' AND year > 1990;
Omitting WHERE clause implies all tuples selected.
24
SEMANTICS FOR 1 RELATION
1. Start with the relation named in the FROM clause
2. Consider each tuple one after the other, eliminating those that do
not satisfy the WHERE clause.
• Boolean condition that must be true for any retrieved tuple
• Logical comparison operators
=, <, <=, >, >=, and <>
3. For each remaining tuple, create a return tuple with columns for
each expression (column name) in the SELECT clause.
• Use SELECT * to select all columns.
25
SELECT-FROM-WHERE SEMANTICS
What if there are several relations in the FROM clause?
1. Start with cross-product of all relation(s) listed in the FROM clause.
• Every tuple in R1 paired up with every tuple in R2 paired up with …
2. Consider each tuple one after the other, eliminating those that do
not satisfy the WHERE clause.
3. For each remaining tuple, create a return tuple with columns for
each expression (column name) in the SELECT clause.
Steps 2 and 3 are just the same as before.
SELECT actor, birth, movie
FROM Role, Person
WHERE actor = name and birth > 1940;
Role
Person ()معلومات الممثلين
Actor()اسم الممثل movie Persona()الدور
Name(اسم
Ben Affleck Argo Tony Mendez
))الممثل birth city
Alan Arkin Argo Lester Siegel
Ben Affleck 1972 Berkeley
Ben Affleck The Company Men Bobby Walker
Alan Arkin 1934 New York
Tommy Lee Jones The Company Men Gene McClary
26
Tommy Lee Jones 1946 San Saba
SELECT actor, birth, movie FROM Role, Person
WHERE actor = name and birth > 1940;
Role X Person
Result
27
AMBIGUOUS COLUMN NAMES
Same name may be used for two (or more) columns (in different
relations)
• Must qualify the column name with the relation name to prevent
ambiguity
Customer Sale LineItem
custid name address phone saleid date custid saleid product quantity price
SELECT name, date, product, quantity
FROM Customer, Sale, LineItem
WHERE price > 100 AND [Link] = [Link] AND
[Link] = [Link];
Note
• If SELECT clause includes custid, it must specify whether to use
[Link] or [Link] even though the values are
guaranteed to be identical.
28
2-RELATION SELECT-FROM-WHERE
-retrieve the information award, actor, persona for the actor that was
rewarded
SELECT award, actor, persona, [Link]
FROM Honours, Role
WHERE category = 'actor' AND winner = actor
AND [Link] = [Link]
Honours Role
movie award category winner actor movie persona
Lincoln Critic's Choice actor Daniel Day-Lewis Ben Affleck Argo Tony Mendez
Argo Critic's Choice director Ben Affleck Tommy Lee Jones Lincoln Thaddeus Stevens
Lincoln SAG supporting actor Tommy Lee Jones Daniel Day-Lewis The Boxer Danny Flynn
Lincoln Critic's Choice screenplay Tony Kushner Daniel Day-Lewis Lincoln Abraham Lincoln
War Horse BMI Flim music John Williams
[Link] award category winner actor [Link] persona
Lincoln Critic's Choice actor Daniel Day-Lewis Ben Affleck Argo Tony Mendez
Lincoln Critic's Choice actor Daniel Day-Lewis Tommy Lee Jones Lincoln Thaddeus Stevens
Lincoln Critic's Choice actor Daniel Day-Lewis Daniel Day-Lewis The Boxer Danny Flynn
Lincoln Critic's Choice actor Daniel Day-Lewis Daniel Day-Lewis Lincoln Abraham Lincoln
Argo Critic's Choice director Ben Affleck Ben Affleck Argo Tony Mendez
Argo Critic's Choice director Ben Affleck Tommy Lee Jones Lincoln Thaddeus Stevens
Argo Critic's Choice director Ben Affleck Daniel Day-Lewis The Boxer Danny Flynn
Argo Critic's Choice director Ben Affleck Daniel Day-Lewis Lincoln Abraham Lincoln
Lincoln SAG supporting actor Tommy Lee Jones Ben Affleck Argo Tony Mendez
Lincoln SAG supporting actor Tommy Lee Jones Tommy Lee Jones Lincoln Thaddeus Stevens
Lincoln SAG supporting actor Tommy Lee Jones Daniel Day-Lewis The Boxer Danny Flynn
29
…
RECALL SAMPLE TABLES
30
31
32
33
TABLES AS SETS IN SQL
Duplicate tuples may appear in query results
• From duplicates in base tables
• From projecting out distinguishing columns
Keyword DISTINCT in the SELECT clause eliminates duplicates
34
OTHER OPERATORS
Standard arithmetic operators:
• Addition (+), subtraction (–), multiplication (*), and division (/)
• Can be applied to numeric values or attributes with numeric domain
[NOT] LIKE comparison operator
• Used for string pattern matching
• Percent sign (%) matches zero or more characters
• Underscore (_) matches a single character
e.g., to also match Tommy Lee Jones as supporting actor:
SELECT award, actor, persona, [Link]
FROM Honours, Role
WHERE category LIKE '%actor' AND winner = actor AND
[Link] = [Link];
[NOT] BETWEEN comparison operator
WHERE year BETWEEN 1990 AND 2010
equivalent to WHERE year >= 1990 AND YEAR <= 2010
35
36
THE INSERT COMMAND
INSERT is used to add a single tuple to a relation. We must specify
the relation name and a list of values for the tuple.
The values should be listed in the same order in which the
corresponding attributes were specified in the CREATE TABLE
command
example, to add a new tuple to the EMPLOYEE relation
n
Recal the create statement for table employee is
37
THE INSERT COMMAND
A second form of the INSERT statement allows the user to specify
attribute
names that correspond to the values provided in the INSERT
command.
This is useful if a relation has many attributes but only a few of
those attributes are assigned values in the new tuple.
Attributes with NULL allowed or DEFAULT values are the ones that
can be left out.
Attributes with NOT NULL specification and no default value should be
specified
For example, to enter a tuple for a new EMPLOYEE for whom we
know only the Fname, Lname, Dno, and Ssn attributes, we can write
the following
38
INSERT INTO SELECT STATEMENT
This statement copies data from one table and inserts it into another
table.
The INSERT INTO SELECT statement requires that the data types
in source and target tables match.
39
EXAMPLE
40
THE DELETE COMMAND
The DELETE statement is used to delete existing records in a table.
Examples:
commands in U4A will delete zero tuples
commands in U4B will delete one tuples
commands in U4C will delete four tuples
commands in U4D will delete all tuples
41
THE UPDATE STATEMENT
is used to modify attribute values of one or more selected tuples.
The WHERE clause in the UPDATE command selects the tuples to be
modified from a single relation
If you omit the WHERE clause, all records in the table will be updated
updating a primary key value may propagate to the foreign key values of
tuples in other relations if specified in the referential integrity constraints
Examples
42
SUMMARY
43
OPERATOR PRECEDENCE
AND has higher precedence than OR.
Example:
Select *
from employee
where fname=‘Denis’ and sex= ‘F’ or sex=‘M’;
- In the above example the expression
where fname=‘Denis’ and sex= ‘F’ or sex=‘M’ is equivalent to
where (fname=‘Denis’ and sex= ‘F’ ) or (sex=‘M’);