---
Lecture 2: Data Models & SQL
- Standard types: INT, FLOAT, DECIMAL(p,s), CHAR(n), VARCHAR(n), BOOLEAN, DATE,
TIME, TIMESTAMP.
- Statements:
• create table (…);
+ add primary key: name VARCHAR(20) PRIMARY KEY or PRIMARY KEY (name,
country)
+ add secondary key: UNIQUE (name, country)
+ add foreign key: manufacturer VARCHAR(20) REFERENCES Company(name) or
FOREIGN KEY (manu_name, manu_co) REFERENCES Company(name, country))
+ PRIMARY KEY adds implicitly “NOT NULL” constraint while UNIQUE does not –
you would have to add this explicitly for UNIQUE.
• drop table ...;
• alter table ... add/remove ...;
• insert into ... values (...);
• delete from ... where ...;
• update ... set (do sth)... where (conditions) ...;
• select … from … where;
• remove duplicates using DISTINCT
• ORDER BY price ASC, pname DESC
---
Lecture 3: SQL Joins
- Interpreting Joins: A JOIN B produces one row for every pair of rows
- Types of Joins:
+ join predicate
+ cross join (join without predicate)
+ natural join (pairs of columns with same name)
+ inner joins:
SELECT DISTINCT cname
FROM Product, Company
WHERE country = ‘USA’ AND category = ‘gadget’AND manufacturer = cname
Alternative syntax:
SELECT DISTINCT cname
FROM Product JOIN Company ON country = ‘USA’ AND category = ‘gadget’AND
manufacturer = cname
+ self-joins: When a relation occurs twice in the FROM (example:
SELECT DISTINCT z.cname
FROM Product x, Product y, Company z -> x and y is identical.
WHERE z.country = ‘USA’
AND x.category = ‘gadget’
AND y.category = ‘photo’
AND x.manufacturer = cname
AND y.manufacturer = cname;)
+ outer joins: if u want to list an attribute which value NULL.
• Left outer join: Include the left tuple even if there’s no match.
• Right outer join: Include the right tuple even if there’s no match.
• Full outer join: Include both left and right tuples even if there’s
no match.
---
Lecture 4: SQL Aggregation - Grouping
- Simple Aggregations:
+ select count(*) from Purchase: counts the total number of records.
-- NULL is counted in count(*)
-- NULL is ignored in count(quantity)
+ select sum(quantity) from Purchase: returns the total sum of a numeric
column, disallow NULLs unless you need to handle them.
+ select avg(price) from Purchase: returns the average value of a numeric
column, NULL values are ignored.
+ select max(quantity) from Purchase
+ select min(quantity) from Purchase
- GROUP BY: groups rows that have the same values into summary rows, ORDER BY.
SELECT product, Sum(quantity)
FROM Purchase
WHERE price > 1
GROUP BY product
ORDER BY sum(price*quantity) DESC
- HAVING clause: contains conditions on groups.
SELECT product, sum(price*quantity)
FROM Purchase
WHERE price > 1
GROUP BY product
HAVING sum(quantity) > 30
- Aggregate + Join Example:
SELECT manufacturer, month, count(*)
FROM Product, Purchase
WHERE pname = product
GROUP BY manufacturer, month
- What if there are no purchases for a manufacturer: use Outer Join
SELECT manufacturer, count(quantity)
FROM Product LEFT OUTER JOIN Purchase
ON pname = product
GROUP BY manufacturer
---
Lecture 5: Subqueries
- Subqueries in SELECT, FROM.
- Subqueries in WHERE
Example: Find all companies that make some products with price < 100
+ Existential quantifiers:
SELECT DISTINCT C.cname
FROM Company C
WHERE EXISTS (SELECT * FROM Product P WHERE C.cid = P.cid and P.price < 100)
-> EASY
Example: Find all companies that make only products with price < 100
+ Universal quantifiers:
SELECT DISTINCT C.cname
FROM Company C
WHERE C.cid NOT IN (SELECT P.cid FROM Product P WHERE P.price >= 100)
SELECT DISTINCT C.cname
FROM Company C
WHERE NOT EXISTS (SELECT * FROM Product P WHERE P.cid = C.cid and P.price >= 100)
SELECT DISTINCT C.cname
FROM Company C
WHERE 100 >=ALL (SELECT price FROM Product P
WHERE P.cid = C.cid)
-> HARD
