Lecture 13 ------------- Option 1: Query -> use in from Statement like a relation (anonymous relations) from (---- query ----) as x where x. Option 2: Query that returns a single value -> use anywhere instead of a number (Scalar queries) select a,b,c, (---- scalar query---) ... where 5 = (---- scalar query --- ) Option 2: Query that returns a set/bag of tuples Check values against the set value in (=== query ---) value not in (=== query ---) value >=ALL (=== query ---) (>=, >, =, <, <=, <>) value >=ANY (=== query ---) (>=, >, =, <, <=, <>) exists (---- query ----) - true if the result of the query is a non-empty set/bag not exists (---- query ----) - true if the result of the query is an empty set/bag ------------ Find episodes in which no one has been eliminated. select id from episodes except select episodeid from results where result='eliminated'; select id from episodes e left join results r on e.id=r.episodeid and r.result='eliminated' where r.episodeid is null ; select id from episodes where id not in (set of episodeids in which someone was eliminated) ; select id from episodes where id not in (select episodeid from results where result='eliminated') ; Find episodes with the highest number of eliminations. select episodeid from results where result='eliminated' group by episodeid having count(*) = (max number of eliminations in any episode) ; -- This query returns number of eliminations per episode select count(*) from results where result='eliminated' group by episodeid ; select episodeid from results where result='eliminated' group by episodeid having count(*) >= ALL (select count(*) from results where result='eliminated' group by episodeid ) ; Find bakers who ranked 1 in technicals the highest number of times. -- solve in the same way as the query above! Find bakers who got a rank 1 in technicals in an episode and then never won star baker after that episode. select t.baker from technicals t where t. rank = 1 and t.episodeid > ALL (all episodes this person got a star baker) ; select DISTINCT t.baker from technicals t where t. rank = 1 and NOT EXISTS (SELECT r.episodeid FROM results r WHERE r.result = 'star baker' and t.baker = r.baker and t.episodeid < r.episodeid) ; Episodes in which no one is eliminated: select e.id from episodes e where not exists (select r.episodeid from results r where r.result='eliminated' and r.episodeid = e.id) ; Return for each contestant the last episode in which they won rank 1 in technicals. select t.baker , t.episodeid from technicals t where t.rank = 1 and not exists (select 1 from technicals t2 where t2.rank = 1 and t2.baker = t.baker and t2.episodeid > t.episodeid); TRANSACTIONS --------------- A sequence of operations that change data begin ; -- sql operations that query or change data ; end ; Atomicity: a transaction either executes fully or not at all. If xact succeeds: commit -> make changes permanent! If xact fails: rollback -> all changes made by the transaction are erased! ----------------- DROP TABLE IF EXISTS tmp ; CREATE TABLE tmp ( id INT PRIMARY KEY , name VARCHAR(10) NOT NULL , val VARCHAR(10) ) ; INSERT INTO tmp VALUES(2, 'ABC', 'val1') ; INSERT INTO tmp(id,name) VALUES(3, 'DEF') ; ---------- Insert the results of a query into another relation ; INSERT INTO tmp SELECT .... FROM ... WHERE ...; create table stats (baker varchar(20), numwins int) ; insert into stats select r.baker, count(distinct t.episodeid) + count(distinct r.episodeid) as numwins from results r full join technicals t on r.baker = t.baker and t.rank = 1 and r.result = 'star baker' where r.baker is not null group by r.baker ; DELETE delete from relation where condition ; Deletes all tuples that satisfy the condition in the WHERE clause; delete from stats where numwins = 1; delete from stats ; -- deletes all the tuples Delete all tuples from stats for bakers who never won star baker delete from stats where baker not in (select baker from results where result = 'star baker') ; UPDATE -------- update relation set attribute = X, attribute2 = Y where condition ; For those tuples that satisfy the WHERE condition change the value of attributes in SET UPDATE stats SET numwins = numwins + 1 WHERE baker like 'R%' ; Update stats, add 1 to numwins for people who got rank 1 in technicals more than once. UPDATE stats SET numwins = numwins + 1 WHERE baker IN ( SELECT baker FROM technicals WHERE rank = 1 GROUP BY baker HAVING count(*)> 1) ; DELETE FROM stats ; INSERT INTO stats SELECT baker, 0 FROM bakers ; UPDATE stats SET numwins = (SELECT count(*) FROM results r WHERE r.result = 'star baker' and r.baker = stats.baker) ;