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)
;