SQL

Part 1

1
SQL

• SQL (“seekwel”) stands for Structured Query Language

• Made up of 2 parts:
• Data Definition Language (DDL)
– used to create and modify a relational schema
• Data Manipulation Language (DML)
– used to retrieve or modify data in a schema
– We call DML SQL statements queries

• Before SQL, there was SEQUEL which stands for Structured English
Query Language

2
Agenda

• So Far
• Basics of the Relational Model
• SQL DDL Statements
– CREATE TABLE Command
– Attribute Domains
– Key Constraints
– Foreign Key Constraints
– Check Constrains
– Other DDL Statements
• SQL DML Statements
– Queries

3
Question

• Does every table need to have a primary key?

4
Relational Model –
Do the values of different rows have to be distinct?

Sailors
sid name rating age
1 Dusty 7 45
Valid
2 Rusty 10 35

Sailors
sid name rating age Not Valid
1 Dusty 7 45
1 Dusty 7 45
2 Rusty 10 15

5
SQL Standard –
Do the values of different rows have to be distinct?

Sailors
sid name rating age
1 Dusty 7 45
Valid
2 Rusty 10 35

Sailors
sid name rating age
Valid, if no primary key is
1 Dusty 7 45
1 Dusty 7 45
defined
2 Rusty 10 15

6
Keys – Theory vs Practice

• Does every table need to have a primary key?

• Theory (key)
– Relational database theory dictates that you must have a candidate
key for every relation
– At least one key
– The key ensures uniqueness
– The key must be minimal
• Practice (primary key)
– The ISO SQL Standard does not require a primary key
– RDBMS products typically do not require a primary key
– At most one primary key
– The primary key ensures uniqueness
– The primary key need not be minimal

7
Primary Keys: Good to have them or not?

• Even though a primary key is not required, it is bad practice not to have
one

8
Foreign Key Constraints

• A foreign key defines a (directed) link between tuples in different

relations
• Foreign keys enforce referential integrity: a requirement that a value in
an attribute or attributes of a tuple in one relation must appear as a
value in another relation

9
Foreign Keys: Example

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

[Link] must refer to an existing

student, i.e., match [Link] for some
tuple in Student

10
 Declaring Foreign Keys

• A foreign key in a table must reference (point to) a candidate key in

another table
• That is, the target column(s) are either a primary key or are designated
UNIQUE
• Some relational systems limit this further: a foreign key must point to
the primary key. For efficiency reasons, this is usually a better choice.

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

candidate keys: sid, login, (name, dob)

11
 Declaring Foreign Keys

• A foreign key in a table must reference (point to) a candidate key in

another table
• That is, the target column(s) are either a primary key or are designated
UNIQUE
• Some relational systems limit this further: a foreign key must point to
the primary key. For efficiency reasons, this is usually a better choice.

create table Students ( create table Enrollment (

sid integer unique, sid integer,
login varchar(128) primary key, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

candidate keys: sid, login, (name, dob)

12
Declaring Foreign Keys

• You can have more than one foreign key in a table referencing the same
candidate key of another table

create table Students ( create table Sibling_Pairs (

sid integer unique, thing_one integer,
login varchar(128) primary key, thing_two. integer,
name varchar(128), primary key (thing_one, thing_two),
dob date, foreign key (thing_one) references Students(sid),
gpa decimal, foreign key (thing_two) references Students(sid)
unique (name, dob) );
);

13
 Enforcing Referential Integrity - Insertion

• What happens if an Enrollment record with a non-existent sid is

inserted?

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

789 210 4

insert rejected by the DBMS

14
 Enforcing Referential Integrity - Update

• What happens if an Enrollment record is updated to a non-existent sid?

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 789 210 4

update rejected by the DBMS

15
 Enforcing Referential Integrity - Deletion

• What happens if an Enrollment record is deleted?

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

Deleting this record is not a

problem! 16
 Enforcing Referential Integrity - Deletion

• What should be done to Enrollment if a Students record is deleted?

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

if this record is deleted this record would violate

referential integrity
17
 Enforcing Referential Integrity – Deletion - Option 1

• What should be done to Enrollment if a Students record is deleted?

• Option 1: Disallow the deletion (default behavior)

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

if the user attempts to the DBMS rejects the

delete this record delete
18
 Enforcing Referential Integrity – Deletion – Option 2

• What should be done to Enrollment if a Students record is deleted?

• Option 2: Specifically disallow the deletion

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid)
unique (name, dob) on delete restrict,
); foreign key (cid) references Courses(cid)
);
Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

if the user attempts to the DBMS rejects the

delete this record delete
19
 Enforcing Referential Integrity – Deletion - Option 3

• What should be done to Enrollment if a Students record is deleted?

• Option 3: cascade the delete

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid)
unique (name, dob) on delete cascade
); foreign key (cid) references Courses(cid)
);
Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

if the user deletes this record the DBMS deletes this record

20
 Enforcing Referential Integrity – Deletion - Option 4

• What should be done to Enrollment if a Students record is deleted?

• Option 4: set default value (does not always make sense)

create table Students ( create table Enrollment (

sid integer primary key, sid integer default 123,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid)
unique (name, dob) on delete set default
); foreign key (cid) references Courses(cid)
);
Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 123 210 4

if the user deletes this record the DBMS updates the FK with
its default value (or reject if
default also violates integrity)
21
 Enforcing Referential Integrity – Deletion - Option 5

• What should be done to Enrollment if a Students record is deleted?

• Option 3: set NULL (does not always make sense)

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid)
unique (name, dob) on delete set null
); foreign key (cid) references Courses(cid)
);
Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 NULL 210 4

if the user deletes this record the DBMS updates the FK with
NULL (allowed only if FK not
part of primary key)
22
 Enforcing Referential Integrity - Update

• What should be done if a Students record is updated?

create table Students ( create table Enrollment (

sid integer primary key, sid integer,
login varchar(128) unique, cid integer,
name varchar(128), grade decimal,
dob date, primary key (sid, cid),
gpa decimal, foreign key (sid) references Students(sid),
unique (name, dob) foreign key (cid) references Courses(cid)
); );

Students Enrollment
sid login name dob gpa sid cid grade

123 Jane Jane S. 1/1/90 4 123 210 4

456 Ann Ann M. 5/2/92 3.8 456 210 4

789

if the user updates the sid in The DBMS follows the

this record same rules as for
deletion of the student
record 23
Enforcing Referential Integrity (Summary)

If the key that is referenced by a foreign key is deleted/updated, the DBMS

1. Disallows the deletion/update

2. Cascades the delete - deletes all relevant records with the same FK
3. Sets the FK to its default value (does not always make sense)
4. Sets the FK to NULL (allowed if FK not part of the primary key)

24
NULLs in Foreign Keys

• Can the attribute designated as a FK be NULL?

25
 Nulls in Foreign Keys
Employees
create table Employees (
ssn char(12) primary key, ssn name title
name varchar(128) not null, ‘111-11-1111’ Ann ‘CEO’
title varchar(128) ’222-22-2222’ Bob ‘clerk’
);

1. an employee has at most one manager, who is also an employee

2. not every employee has a manager

Managers
create table Managers ( emp_ssn mgr_ssn
emp_ssn char(12) primary key,
‘222-22-2222’ ‘111-11-1111’
mgr_ssn char(12),
foreign key (emp_ssn) references Employees(ssn), ‘111-11-1111’ NULL
foreign key (mgr_ssn) references Employees(ssn)
);

26
Foreign Key Examples

27
Foreign Key Examples (Solution 1)

28
Foreign Key Examples (Solution 2)

29
Integrity Constraints continued

• So far, we talked about enforcing

• domain constraints, by specifying data types for attributes

• not null constraints, by specifying not null for attributes
• key constraints, by defining primary keys and designating attributes /
combinations of attributes as UNIQUE
• referential integrity constraints, by defining foreign keys

• Another useful type of a constraint is a CHECK constraint

30
Specifying CHECK Constraints

• Used to restrict the values in a column

• We already saw an example: not null constraints
• Here is another example:

create table Employees (

ssn char(12) primary key,
name varchar(128) not null,
title varchar(128),
age integer not null,
country varchar(128),
check (age > 0),
check (country in (select name from Country))
);

Not always supported

ERROR: cannot use subquery in check constraint

31
Naming Constraints

• Q: Why do we name constraints?

• A: For readability
• A: Also, so that we can refer to them by name, so as to drop them.

create table Employees (

ssn char(12) primary key,
name varchar(128) not null,
title varchar(128),
age integer not null,
country varchar(128),
constraint Age_Constraint check (age > 0),
constraint Country_Constraint check (country in (select name from Country))
);

32
Modifying Constraints

create table Employees (

ssn char(12) primary key,
name varchar(128) not null,
title varchar(128),
age integer not null,
country varchar(128),
constraint Age_Constraint check (age > 0),
constraint Country_Constraint check (country in (select name from Country))
);

alter table Employees drop constraint Country_Constraint;

alter table Employees add constraint SSN_Constraint

check ssn like ‘%-%-%’;

33
Modifying Tables

• To delete a table (removes both schema and data)

drop table Students;

• To change the schema of a table

alter table Students add class char(4);

alter table Students add class char(4) default 2020;
alter table Students drop column gpa;

34
Inserting Records

• To insert a record into an existing table, use the insert command.

insert into Students(sid, login, name, dob, gpa)

values (1, ‘jm1’, ‘John Smith’, 2001-11-11, 3.8);

• The order of columns does not matter

insert into Students(sid, name, login, gpa, dob)

values (1, ‘John Smith’, ‘jm1’, 3.8, 2001-11-11);

35
Inserting Records

• The list of columns is optional, the order of values must be as defined

insert into Students

values (1, ‘jm1’, ‘John Smith’, 2001-11-11, 3.8);

• You can insert into some columns only, filling with NULL

insert into Students(sid, name, login)

values (1, ‘John Smith’, ‘jm1’);

36
Updating Records

• The update statement is used to modify the existing records in a table.

update Students
set name = ‘Pepper’, gpa = 4.0
where sid=1;

• Without a condition, all records are updated

update Students
set gpa = 4.0;

37
Deleting Records

• The delete statement is used to delete existing records in a table.

delete from Students
where sid=1;

• Without a condition, all records are deleted

delete from Students;

38
SQL Queries

39
SQL Queries – SFW Queries

select Columns
from Table
where Condition

• Which table are we interested in?

• Which records are we interested in?
• Which columns are we interested in?

40
Operations on a single table – The SFW Query

Given a table T, return a new table T’ that contains a

subset of the rows / columns from T

select * select *
from Table from Table
where Condition

select Column select Columns

from Table from Table
where Condition where Condition

41
SWF Queries

select Columns
from Table
where Condition

Which records are we interested in?

• where gpa=3.5
• where gpa=3.5 and name=‘Pepper’
• where (gpa=3.5 or gpa=4.0) and name=‘Pepper’

42
SWF Queries

select Columns
from Table
where Condition

Which columns are we interested in?

• select * means all columns
• select gpa
• select gpa * 10
• select gpa * 10 as student_gpa, name

43
SQL Queries – SFW Queries

select Columns
from Table
where Condition

• is the same as

select all Columns

from Table
where Condition

44
Examples

Sailors (sid,name,rating,age) Boats (bid, name,color) Reserves (sid, bid,day)

bid name color sid bid day
sid name rating age
101 Interlake blue 1 101 10/10/12
1 Dusty 7 45
102 Interlake red 1 102 10/10/12
2 Rusty 10 35
103 Clipper green 1 101 10/7/12
3 Horatio 5 35
104 Marine red 2 102 11/9/12
4 Zorba 8 18
5 Julius 25 2 102 7/11/12
3 101 7/11/12
3 102 7/8/12
create table Sailors (
sid integer primary key, 4 103 19/9/12
name varchar(128) unique,
rating integer,
age integer create table Boats (
); bid integer primary key,
name varchar(128) unique,
color varchar(128)
);

create table Reserves (

sid integer,
bid integer,
day date,
primary key (sid, bid, day),
foreign key sid references [Link],
foreign key bid references [Link]
);
45
 Selection in SQL

Sailors (sid,name,rating,age) create table Sailors (

sid integer primary key,
sid name rating age name varchar(128) unique,
1 Dusty 7 45 rating integer,
age integer
2 Rusty 10 35
);
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25

Find all sailors sid name rating age

1 Dusty 7 45
2 Rusty 10 35
select * 3 Horatio 5 35
4 Zorba 8 18
from Sailors;
5 Julius 25

46
 Selection in SQL

Sailors (sid,name,rating,age) create table Sailors (

sid integer primary key,
sid name rating age name varchar(128) unique,
1 Dusty 7 45 rating integer,
age integer
2 Rusty 10 35
);
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25

Find all sailors whose age is 35.

select * sid name rating age

2 Rusty 10 35
from Sailors 3 Horatio 5 35

where age = 35;

47
 Selection in SQL

Sailors (sid,name,rating,age) create table Sailors (

sid integer primary key,
name varchar(128) unique,
sid name rating age rating integer,
age integer
1 Dusty 7 45 );
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25

What are the sailors whose age is 35 and whose rating is over 7?

sid name rating age

select *
2 Rusty 10 35
from Sailors
where age = 35
and rating > 7;

48
 Projection in SQL

Sailors (sid,name,rating,age) create table Sailors (

sid integer primary key,
name varchar(128) unique,
sid name rating age rating integer,
age integer
1 Dusty 7 45 );
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25

What are the ages of sailors?

age
select age
45
from Sailors; 35 SQL queries compute
35 bags (not sets!)
18
25

49
 Projection in SQL – Use DISTINCT to eliminate duplicates

Sailors (sid,name,rating,age) create table Sailors (

sid integer primary key,
name varchar(128) unique,
sid name rating age rating integer,
age integer
1 Dusty 7 45 );
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25

What are the different ages of sailors?

age
select distinct age
45
from Sailors; 35 use distinct to remove
18 duplicates
25

50
SQL queries compute bags (not sets)

• A bag (aka a multi-set) is an unordered collection with duplicates

• This is unlike a set, which is also unordered, but there are no duplicates
• Relational model, relational algebra work with sets
• SQL works with bags
• Why bags: efficiency of implementation is the main reason

We use distinct to covert from a bag to a set

51
 SQL – Combining selection and projection

Sailors (sid,name,rating,age) create table Sailors (

sid integer primary key,
name varchar(128) unique,
sid name rating age rating integer,
age integer
1 Dusty 7 45 );
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25

What are the names of sailors whose age is 35?

name
select name
Rusty
from Sailors Horatio

where age = 35

52
Examples

Sailors (sid,name,rating,age) Boats (bid, name,color) Reserves (sid, bid,day)

sid name rating age bid name color sid bid day
1 Dusty 7 45 101 Interlake blue 1 101 10/10/12
2 Rusty 10 35 102 Interlake red 1 102 10/10/12
3 Horatio 5 35 103 Clipper green 1 101 10/7/12
4 Zorba 8 18 104 Marine red
2 102 11/9/12
5 Julius 25 2 102 7/11/12
3 101 7/11/12
(a) List names of boats.
3 102 7/8/12
4 103 19/9/12
(b) List ratings and ages sailors.

(c) List names of sailors who are over 21 years old.

(d) List names of red boats.

(e) List ids of boats that have ever been reserved.

53
SQL Operators

• Arithmetic: + - * / %
• Bitwise: ^ & |
• Comparison: = <> > < .. The SQL standard defines the
“not equal” operator as <>
• Compound: +=, -=, .. but != is also used

• Logical: AND, OR, EXISTS, …

54
String expressions

Sailors (sid, name,rating,age) Boats (bid,name,color) Reserves (sid, bid,day)

Find name, age, 2*rating of all sailors whose name starts with A or
contains ‘ust’ as a substring starting from position 2

select name, age, rating * 2 as twice_the_rating

Q1 from Sailors
where name like 'A%' or name like '_ust%';

as is a way to name a field in theresult

like is used for string matching

% denotes 0 or more arbitrarycharacters

_ denotes exactly 1 arbitrarycharacter

There is also support for regular expressions: [list], [^list], [l-t], …

55
Expressions and strings: examples

Sailors (sid, name,rating,age) Boats (bid,name,color) Reserves (sid, bid,day)

Find names, sids of sailors whose rating * 5 is less than their age
Q2
select name, sid
from Sailors
where rating * 5 < age

Find names, sids of sailors whose names contain the

letter u
Q3
select name, sid
from Sailors
where name like '%u%'

56
Null values

• null stands for a missing / unknown / inapplicable value

• SQL provides special comparison operators for null

(cannot use = < > !=)
select *
Q4 from Sailors
where rating is not null;

Sailors (sid,name,rating,age) sid name rating age

sid name rating age 1 Dusty 7 45

1 Dusty 7 45 2 Rusty 10 35

2 Rusty 10 35 3 Horatio 5 35

3 Horatio 5 35 4 Zorba 8 18

4 Zorba 8 18

5 Julius null 25
Q5 select *
from Sailors
where rating is null;
sid name rating age

5 Julius null 25

57
Null Values: Cannot use =, !=, < or > with NULL

select ..
from ..
where rating is null
Valid

select ..
from ..
where rating is not null
Valid

select ..
from ..
where rating = null Not Valid

select ..
from ..
where rating <> null Not Valid
58
Null values (II)

• null stands for a missing value (unknown / inapplicable) value

• SQL provides special comparison operators for null

(cannot use = < > !=)

Sailors (sid,name,rating,age) Q6 select sid, rating * 0

from Sailors;
sid rating
sid name rating age
1 0
1 Dusty 7 45
2 Rusty 10 35 2 0

3 Horatio 5 35 3 0

4 Zorba 8 18 4 0
5 Julius null 25 5 null

The value of rating * 0 for sid = 5 is null, not 0!

59
Question: What does this query compute?

name age gpa

Moe 20 4
Larry 18 NULL
Joe 21 3.8

select gpa*100
from students;

60
Question: What does this query compute?

name age gpa

Moe 20 4
Larry 18 NULL
Joe 21 3.8

select gpa*100
from students;

gpa
400
NULL
380

61
Question: What does this query compute?

name age gpa

Moe 20 4
Larry 18 NULL
Joe 21 3.8

select name
from students
where gpa > 3.5;

62
Question: What does this query compute?

name age gpa

Moe 20 4
Larry 18 NULL
Joe 21 3.8

select name
from students
where gpa > 3.5;

name
Moe
Joe

63
Question: What does this query compute?

name age gpa

Moe 20 4
Larry 18 NULL
Joe 21 3.8

select name
from students
where age > 15 or gpa > 3.5;

64
Question: What does this query compute?

name age gpa

Moe 20 4
Larry 18 NULL
Joe 21 3.8

select name
from students
where age > 15 or gpa > 3.5;

name
Moe
Larry
Joe

65
Question?

• Does this query find all the records?

select *
from Sailors
where rating > 7 or rating <= 7;

66
Question?

• Does this query find all the records?

select *
from Sailors
where rating > 7 or rating <= 7;

• Nope! null > 7 is false and null <=7 is also false

Input Output
sid name rating age sid name rating age
1 Dusty 7 45 1 Dusty 7 45
2 Rusty 10 35 2 Rusty 10 35
3 Horatio 5 35 3 Horatio 5 35
4 Zorba 8 18 4 Zorba 8 18
5 Julius null 25

67
Renaming attributes
select sid as sailor_id, rating / 10 as normalized_rating
from Sailors;

the as keyword is optional

Q7 select sid sailor_id, rating / 10 normalized_rating

from Sailors;

sailor_id normalized_rating
1 0.7

2 1

3 0.5

4 0.8

5 null

68
Renaming relations

Q8 select [Link], [Link] / 10 as normalized_rating

from Sailors S;

sid normalized_rating
1 0.7

2 1

3 0.5

4 0.8

5 null

69
Sorting results

• The output of a SQL query can be ordered

• Use the order by keyword
• To order by ascending order (from lower to higher value) specify ASC
– This is the default, you can skip the keyword ASC
• To order by descending order (from higher to lower value) specify DESC

select Columns
from Table
where Condition
order by col_1, col_2 asc, col_3 desc

ascending descending
order order

70
Sorting Results - Example

Retrieve all reservations sorted by sid, Retrieve all reservations sorted by

with ties broken by bid sid (descending), with ties broken
by bid (ascending)
select * select *
Q9 from Reserves Q10 from Reserves
order by sid, bid order by sid desc, bid

sid bid day sid bid day

1 101 10/10/12 4 103 19/9/12
1 101 10/7/12 3 101 7/11/12
1 102 10/10/12 3 102 7/8/12
2 102 11/9/12 2 102 11/9/12
2 102 7/11/12 2 102 7/11/12
3 101 7/11/12 1 101 10/10/12
3 102 7/8/12 1 101 10/7/12
4 103 19/9/12 1 102 10/10/12

71
Examples

Sailors (sid,name,rating,age) Boats (bid, name,color) Reserves (sid, bid,day)

bid name color sid bid day
sid name rating age
101 Interlake blue 1 101 10/10/12
1 Dusty 7 45
102 Interlake red 1 102 10/10/12
2 Rusty 10 35
103 Clipper green 1 101 10/7/12
3 Horatio 5 35
104 Marine red 2 102 11/9/12
4 Zorba 8 18
5 Julius 25 2 102 7/11/12
3 101 7/11/12
(a) Find the color of a boat named clipper. 3 102 7/8/12
4 103 19/9/12
(b) Find all sailors who are 35 or older, sort them by name.

(c) List the names of red boats, sorted by boat id.

(d) Find all boats that are either red or called Interlake.

72
Acknowledgements

Some slides in this course are inspired or copied from

• [Link] by Julia Stoyanovich, Drexel U,

Spring 2018
• [Link] by Jiannan Wang - Simon Fraser, Fall
2018

73