Database SQL Relational language SQL n SQL or

Database SQL

Relational language SQL n SQL or Structured Query Language n SQL is an interactive Query language and database programming Language

SQL History Originally (mid-’ 70 s) defined as Sequel in System R project at IBM San Jose Research Center n Name change to SQL, multiple iterations of standards (’ 86, ’ 87, ’ 89, ’ 92, …) n Has usual good and bad aspects of muchused, much-extended languages n n Think of C++ or Fortran-90…

SQL n From American National Institue ANSI, it was SQL-86 (ANSI 1986) and SQL-2 (SQL-92) n SQL Language is divided into n DDL : Data Definition Language n DML : Data Manipulation Language n DCL : Data Control Language

DDL n It is commands used for: n n n Creates databases, tables, indices Create views Specify integrity constraints n DDL commands are n CREATE n ALTER n DROP

DML n For values in the database n n Perform queries Perform updates n DML commands: n SELECT n UPDATE n DELETE n INSERT

DCL n For controlling the access of data n DCL commands are n GRANT n REVOKE

SQL - DDL n It used to define and manage the structure of tables in the database n Its statements as n CREATE TABLE n ALTER TABLE n DROP TABLE CREATE INDEX DROP INDEX

Creating tables create table name (attr 1 domain 1, attr 2 domain 2, …) Can also have integrity constraints n not null n unique n primary key (attr 1, attr 2, …) n check (predicate) n To be a primary key, must be non-null and unique n Different SQLs differ about whether not null is implied by primary key

CREATE Commands n Create the table S (s#, Sname, Status, City) CREATE TABLE S (s# CHAR(5), Sname CHAR(20), Status INT, City CHAR(15), PRIMARY KEY (s#)).

Data Definition Language n Every attribute has a domain n Some common ones: n char(n): character string of fixed length n n varchar(n): character string of length up to n n int: integer, exact size machine-dependent n smallint: another (usually smaller than int) integer

More common domains n numeric(p, d): fixed point number with p total digits, d of them to the right of the decimal point n real, double precision: floating-point numbers n float(n): floating-point with precision at least n n date: a calendar date (day, month, year) n time: hours, minutes, seconds n datetime: a time on a date

Example: Default Values CREATE TABLE Students( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT ‘ 123 Sesame St. ’, phone CHAR(16) ); 13

Other Keys CREATE TABLE Product ( product. ID CHAR(10), name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (product. ID), UNIQUE (name, category)) There is at most one PRIMARY KEY; there can be many UNIQUE 14

Foreign Key Constraints CREATE TABLE Purchase ( prod. Name CHAR(30), category VARCHAR(20), date DATETIME, FOREIGN KEY (prod. Name, category) REFERENCES Product(name, category) n (name, category) must be a PRIMARY KEY 15

Example Create a database which can use in Seaport The database consists of 3 tables: Sailors (sid integer, sname char, rating integer, age real) 2. Reverses (sid, bid, day) 3. Boats (bid integer, bname char, color char) n n 1.

DROP command n DROP TABLE BOATS n DROP TABLE REVERSES n DROP TABLE SAILORS

ALTER COMMAND n ALTER TABLE Sailors ADD address CHAR(30) n ALTER TABLE Boats DROP colors

INDEX n CREATE INDEX S ON Sailors (sid) n CREATE INDEX R ON REVERSES (sid, bid, day) n CREATE INDEX B ON Boats (bid) n DROP INDEX R

Example 1 Since Professor Status Works. In Department CREATE TABLE Works. In ( Since DATE, -- attribute Status CHAR (10), -- attribute Prof. Id INTEGER, -- role (key of Professor) Dept. Id CHAR (4), -- role (key of Department) PRIMARY KEY (Prof. Id), -- since a professor works in at most one department FOREIGN KEY (Prof. Id) REFERENCES Professor (Id), FOREIGN KEY (Dept. Id) REFERENCES Department ) 20

Example 2 Date Project Price Sold Part Supplier CREATE TABLE Sold ( Price INTEGER, -- attribute Date DATE, -- attribute Proj. Id INTEGER, -- role Supplier. Id INTEGER, -- role Part. Number INTEGER, -- role PRIMARY KEY (Proj. Id, Supplier. Id, Part. Number, Date), //WHY DATE? FOREIGN KEY (Proj. Id) REFERENCES Project, FOREIGN KEY (Supplier. Id) REFERENCES Supplier (Id), FOREIGN KEY (Part. Number) REFERENCES Part (Number) ) 21

DML SELECT n The basic format for the SELECT command is SELECT FROM WHERE <attribute list> <table list> <condition>

Simple SQL Query Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works SELECT * FROM Product WHERE category=‘Gadgets’ “selection”

Examples n Retrieve the birthdate and address of employee whose name is “John B. Smith” n SELECT Bdate, Address FROM Employee WHERE Name = ‘John B. Smith’

Example n Retrieve all Sailors name whose reverses boat id 103 n SELECT sname FROM Sailors , Reverses WHERE Sailors. sid = Reverses. sid AND Reverses. bid=103

ORDER BY n The Select command has sentential ORDER BY n Select S. sname FROM Sailors S, Reverses R WHERE S. sid = R. sid AND R. bid = 103 ORDER BY age DESC

The LIKE operator n n s LIKE p: pattern matching on strings p may contain two special symbols: n n % = any sequence of characters _ = any single character Product(PName, Price, Category, Manufacturer) Find all products whose name mentions ‘gizmo’: SELECT * FROM Products WHERE PName LIKE ‘%gizmo%’ 27

Eliminating Duplicates Category SELECT DISTINCT category FROM Product Gadgets Photography Household Compare to: Category Gadgets SELECT category FROM Product Gadgets Photography Household

Joins in SQL n Connect two or more tables: Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi Company What is the connection between them ? Cname Stock. Price Country Gizmo. Works 25 USA Canon 65 Japan Hitachi 15 Japan

Joins Product (pname, price, category, manufacturer) Company (cname, stock. Price, country) Find all countries that manufacture some product in the ‘Gadgets’ category. SELECT country FROM Product, Company WHERE manufacturer=cname AND category=‘Gadgets’

Joins Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(persname, phone. Number, city) Find names of people living in Seattle that bought some product in the ‘Gadgets’ category, and the names of the stores they bought such product from SELECT DISTINCT persname, store FROM Person, Purchase, Product WHERE persname=buyer AND product = pname AND city=‘Seattle’ AND category=‘Gadgets’

Complex Correlated Query Product ( pname, price, category, maker, year) n Find products (and their manufacturers) that are more expensive than all products made by the same manufacturer before 1972 SELECT DISTINCT pname, maker FROM Product AS x WHERE price > ALL (SELECT price FROM Product AS y WHERE x. maker = y. maker AND y. year < 1972); Powerful, but much harder to optimize !

Aggregate Operators n COUNT (*) n COUNT ( [DISTINCT] A) n n n A is a column SUM ( [DISTINCT] A) AVG ( [DISTINCT] A) MAX (A) MIN (A) Count the number of sailors SELECT COUNT (*) FROM Sailors S 33

Find name and age of the oldest sailor(s) SELECT S. sname, MAX (S. age) FROM Sailors S n This is illegal, but why? n Cannot combine a column with a value SELECT S. sname, S. age FROM Sailors S WHERE S. age = (SELECT MAX (S 2. age) FROM Sailors S 2) 34

Examples n What is the average distance between plazas? select avg(distfromprev) from plazas n How many events are there? select count(*) from events 29 n How many distinct times are there? select count(distinct occurredat) from events 24 n How do we find the duplicates?

Aggregation: Count SELECT Count(*) FROM Product WHERE year > 1995 Except COUNT, all aggregations apply to a single attribute

Aggregation: Count COUNT applies to duplicates, unless otherwise stated: SELECT Count(category) FROM Product WHERE year > 1995 same as Count(*) Better: SELECT Count(DISTINCT category) FROM Product WHERE year > 1995

Aggregate functions n GROUP BY & HAVING n GROUP BY for grouping the Query results n Having to provide a condition on the group

Example SELECT Pnumber, Pname, COUNT(*) FROM PROJECT, WORKS_ON WHERE Pnumber = PNO GROUP BY Pnumber, Pname HAVING COUNT(*) >2

Grouping and Aggregation Usually, we want aggregations on certain parts of the relation. Purchase(product, date, price, quantity) Example 2: find total sales after 10/1 per product. SELECT FROM WHERE GROUPBY product, Sum(price*quantity) AS Total. Sales Purchase date > “ 10/1” product Let’s see what this means…

Grouping and Aggregation 1. Compute the FROM and WHERE clauses. 2. Group by the attributes in the GROUPBY 3. Select one tuple for every group (and apply aggregation) SELECT can have (1) grouped attributes or (2) aggregates.

HAVING Clause Same query, except that we consider only products that had at least 100 buyers. SELECT product, Sum(price * quantity) FROM Purchase WHERE date > “ 9/1” GROUP BY product HAVING Sum(quantity) > 30 HAVING clause contains conditions on aggregates.

n Find all authors who wrote at least 10 documents: n Attempt 1: with nested queries This is SQL by a novice SELECT DISTINCT Author. name FROM Author WHERE count(SELECT Wrote. url FROM Wrote WHERE Author. login=Wrote. login) > 10

n Find all authors who wrote at least 10 documents: n Attempt 2: SQL style (with GROUP BY) SELECT Author. name FROM Author, Wrote WHERE Author. login=Wrote. login GROUP BY Author. name HAVING count(wrote. url) > 10 This is SQL by an expert No need for DISTINCT: automatically from GROUP BY

SQL n Using the following tables Sailors Sid sname rating age 22 Dustin 7 45 29 Brutus 1 33 31 Lubber 8 55. 5 32 Andy 8 25. 5 58 Rusty 10 35 64 Horatio 7 35 71 Zorba 16 74 Horatio 9 40 85 Art 3 25. 5 95 bob 3 63. 5 10

Example Sid bid Day 22 101 10/10/98 22 10/10/98 Bid Banem Color 22 103 10/8/98 22 104 10/7/98 101 Interlake Blue 31 102 11/10/98 102 Interlake Red 31 103 10/6/98 103 Clipper Green 31 104 10/12/98 104 Marine Red 64 101 9/5/98 64 102 9/8/98 74 103 9/8/98 Boats Reverses

Queries n Find the names and ages of all sailors n Find all sailors with rating above 7 n Find the name of sailors who have reserved boat number 104

48

Quiz n Write an SQL query for following, and show the result on the example tables: Find all course names that were not taught in 1997 n List the professor names along with all the departments ids where a professor has taught n Find the names of students who took courses in all the departments that offered a course in Fall of 1995 or 1997. n 49

Exercises Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Company (cname, stock price, country) Person(per-name, phone number, city) Ex #1: Find people who bought telephony products. Ex #2: Find names of people who bought American products Ex #3: Find names of people who bought American products and they live in Seattle. Ex #4: Find people who have both bought and sold something. Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.

Mutating (non-read-only) queries n Deletion n Deletes whole tuples n Insertion n Inserts whole tuples n Update n Changes values of attributes n Can generally do these to tables but not to views or other derived entities n n Note that renaming is not in itself derivation So a renamed relation can be mutated like the original

Update update name set assignment where P n where clause is optional (selects tuples to be updated) n Give Jane Swift a $20 balance update subscribers set balance = 20 where firstname=“Jane” and lastname=“Swift” n Give every subscriber a $3 rebate update subscribers set balance = balance + 3

Update examples UPDATE P SET Color = ‘Yellow’ Weight = Weight + 5 City WHERE P# = 2 = NULL

Deletion delete from name where P n Delete all events from transponder 72 delete from events where tid=72 n Delete all events relating to George Bush delete from events where tid in select from transponders as t, subscribers as s where t. tid = s. tid and s. firstname=“George” and s. lastname=“Bush”

Delete example DELETE FROM S WHERE S# =1

Insertions General form: INSERT INTO R(A 1, …. , An) VALUES (v 1, …. , vn) Example: Insert a new purchase to the database: INSERT INTO Purchase(buyer, seller, product, store) VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’) Missing attribute NULL. May drop attribute names if give them in order. 56

Insert example INSERT INTO VALUES P(P#, City , Weight) (7 , ‘athens’, 24 )

Grant & revoke operations n the format are GRANT operation ON table TO user REVOKE operation FROM user

Examples GRANT INSERT, DELETE ON TABLE reserves TO ahmed GRANT ALL ON TABLE saliros TO hassan REVOKE SELECT ON TABLE boats FROM manal

Grant Example GRANT SELECT ON TABLE S TO u 3 WITH GRANT OPTION REVOKE DELETE ON TABLE S FROM U 2

Starwars Exercises char(name, race, homeworld, affiliation) planets(name, type, affiliation) timetable(cname, pname, movie, arrival, departure) n Which planet does Princess Leia go to in movie 3? SELECT distinct pname FROM timetable WHERE cname ='Princess Leia' and movie=3; 61

Starwars Exercises char(name, race, homeworld, affiliation) planets(name, type, affiliation) timetable(cname, pname, movie, arrival, departure) n How many humans stay on Dagobah in movie 3? SELECT count(*) FROM timetable, characters WHERE movie=3 and pname =‘Dagobah’ and timetable. cname=characters. name and characters. race=‘Human’; 62

Starwars Exercises char(name, race, homeworld, affiliation) planets(name, type, affiliation) timetable(cname, pname, movie, arrival, departure) n Who has been to his/her homeworld in movie 2? SELECT distinct c. name FROM characters c, timetable t WHERE c. name=t. cname and t. pname=c. homeworld and movie=2; 63

Starwars Exercises char(name, race, homeworld, affiliation) planets(name, type, affiliation) timetable(cname, pname, movie, arrival, departure) n Find distinct names of the planets visited by those of race “droid”. SELECT distinct t. pname FROM char c, timetable t WHERE c. name=t. cname and c. race=‘droid’; 64

Starwars Exercises char(name, race, homeworld, affiliation) planets(name, type, affiliation) timetable(cname, pname, movie, arrival, departure) n For each character and for each neutral planet, how much time total did the character spend on the planet? SELECT c. name, p. name, SUM(t. departure-t. arrival) as amount FROM characters c, timetable t, planets p WHERE t. cname=c. name and t. pname=p. name and p. affiliation='neutral' 65 GROUP BY c. name, p. name;

EXERCISE 1: Queries 1. First and last name of employees who have no supervisor. 2. First and last name of employees supervised by Franklin Wong. 3. Last name of employees who have dependents. 4. Last name of employees who have daughters. 5. Last name of employees in department 5 who work more than 10 hours/week on Product. X. 6. Last name of supervisors of employees in department 5 who work more than 10 hours/week on Product. X. 7. First and last names of all department managers. 8. Salaries of all employees who have worked on the Reorganization project. 9. SSN of all employees who have worked on a project that is controlled by a department different than the department that they are assigned to. 10. Last name of all employees who are not married.

EXERCISE 1: Schema

EXERCISE 1: Instance

EXERCISE 2: Queries 1. List all airplane types that can land at any airport in San Francisco. 2. List the ids and number of seats for all airplanes that can land at any airport in Chicago. 3. List the name and phone number of all customers with a seat reserved on a flight that leaves Chicago O’Hara airport (ORD) on October 31, 2008. 4. List all airlines that have seats available for flights leaving Los Angeles (LAX) on September 25, 2008. 5. List all airlines that operate at San Jose International Airport (SJC).

Exercise 2: Schema

EXERCISE 3: Queries 1. Count the number of overdue books. 2. How many books by author Harry Crews are in the database? 3. Determine the number of library cards assigned to each borrower phone number. 4. Find names of all borrowers who do not have any book loans. 5. Do any library branches have every book?

EXERCISE 3: Schema