Where did SQL come from?
IBM's Sequel language, developed as part of the System R project at the IBM San Jose Research Laboratory, renamed Structured Query Language (SQL).
What does the SQL DDL allow you to specify?
The schema for each relation; the domain of each attribute; integrity constraints; and also indices, security and authorization, and the physical storage structure.
char(n) vs varchar(n)
char(n): fixed length string of length n. varchar(n): variable length string with maximum length n.
numeric(p,d)
Fixed point number: precision of p digits, d of them to the right of the decimal point — numeric(3,1) stores 44.5 but not 444.5 or 0.32.
BOOLEAN and DATE in SQL
BOOLEAN has three values: TRUE, FALSE and UNKNOWN. DATE has ten positions: yyyy-mm-dd.
The three integrity constraints in create table
not null; primary key (A1, …, An); foreign key (Am, …, An) references r.
Does a primary key attribute need "not null"?
No — a primary key declaration on an attribute automatically ensures not null.
alter table r add A D: what value do existing tuples get?
null for the new attribute.
Why drop sec_id from the primary key of takes?
To ensure a student cannot be registered for two sections of the same course in the same semester.
select / from / where ↔ relational algebra
select ↔ projection (Π); from ↔ Cartesian product (×); where ↔ selection predicate (σ).
distinct vs all in select
distinct forces the elimination of duplicates; all specifies that duplicates should not be removed.
select 'A' from instructor returns…
A table with one column and N rows (one per instructor tuple), each with value "A".
Are SQL names case sensitive?
No — SQL names are case insensitive (Name ≡ NAME ≡ name).
Is the keyword "as" required for renaming?
No — as is optional: instructor as T ≡ instructor T.
% and _ in like patterns
% matches any substring; _ matches any (single) character.
'_ _ _' vs '_ _ _ %'
'_ _ _' matches any string of exactly three characters; '_ _ _ %' matches any string of at least three characters.
Are like patterns case sensitive?
Yes — patterns are case sensitive.
Default order in order by
Ascending (asc); use desc for descending.
Tuple occurs m times in r, n times in s: union all / intersect all / except all
m + n; min(m, n); max(0, m − n).
Do union, intersect and except keep duplicates?
No — they automatically eliminate duplicates; use union all, intersect all, except all to retain them.
5 + null = ?
null — the result of any arithmetic expression involving null is null.
Any comparison with null returns…
unknown (e.g., 5 < null, null <> null, null = null).
unknown or true; false and unknown; not unknown
true; false; unknown.
How is a where predicate that evaluates to unknown treated?
As false.
Rule for attributes in select with group by
Attributes in the select clause outside of aggregate functions must appear in the group by list.
where vs having
where predicates are applied before forming groups; having predicates are applied after the formation of groups.
Aggregates and null
All aggregates except count(*) ignore null values; with only null values, count returns 0 and the others return null.
Where can a subquery appear in select … from … where …?
In select: a subquery generating a single value; in from: any valid subquery; in where: B <operation> (subquery).
F <comp> some r
True if there exists at least one t in r such that F <comp> t. (= some) ≡ in, but (≠ some) ≢ not in.
F <comp> all r
True if F <comp> t for every t in r. (≠ all) ≡ not in, but (= all) ≢ in.
exists r / not exists r
exists r ⇔ r ≠ Ø (the subquery is nonempty); not exists r ⇔ r = Ø.
Correlated subquery and correlation name
A correlated subquery is an inner query that uses a variable of the outer query; that variable (e.g., S) is the correlation name.
The unique construct
Tests whether a subquery has any duplicate tuples; true if the subquery contains no duplicates.
The with clause
Defines a temporary relation whose definition is available only to the query in which the with clause occurs.
Scalar subquery: what is it, and what if it returns several tuples?
A subquery used where a single value is expected; a runtime error occurs if it returns more than one result tuple.
delete … where salary < (select avg(salary) …): how does SQL handle the changing average?
It first computes avg(salary) and finds all tuples to delete, then deletes them without recomputing avg or retesting the tuples.
insert into … select …: when are results inserted?
The select-from-where statement is evaluated fully before any of its results are inserted.
Raise salaries ≤ 100000 by 5% and others by 3% in one statement
update instructor set salary = case when salary <= 100000 then salary * 1.05 else salary * 1.03 end