Lecture 13ish - Anonymous Queries and DDL

Lecture 13ish - Anonymous Queries and DDL#

Announcements#

  • Docker container for Parks DB is not working yet, so all your code has been run on my own computer. Grading is ongoing.

  • Homework #4 is due to friday. Expect Homework #5 soon after that.

  • Due to problems with VPN over the break, Lecture exercise 12 deadline is extended to today (10/20) at midnight.

  • Lecture Exercise 13 will be posted at 2pm today, due on Thursday 10/23 at midnight. It is on insert/update/delete operations.

  • Lecture Exercise Optional 1 will be posted today at 2pm, due on Sunday 10/26 midnight.

    • This is one of the optional lecture exercises, meaning I will drop at least one of the lowest lecture exercise grades. It contains a number of queries with varying complexity. It is a great way to sharpen your SQL skills.

  • Personal databases are created on the db server, named

    db_<username>

for each of you. Use these to test out DB creation and other functionality.

Today’s lecture#

  • Advanced SQL: Anonymous queries and the WITH statement

  • Data Definition and Manipulation in SQL (create/insert/update/delete)

  • Foreign keys, constraint checking

  • Introduction to procedural SQL if we have time.

load_ext sql

%config SqlMagic.displaylimit = None
%sql --section baking
displaylimit: Value None will be treated as 0 (no limit)
Connecting to 'baking'

Advanced SQL concepts

  • Scalar query: A query that returns a single number

  • Set query: A query that returns a single attribute/column

  • Anonymous query: A query that returns multiple attributes and used in the query as another relation

    • Use them in FROM, not fully recommended

    • Use them in WITH, recommended

%%sql
select baker
from bakers
where age = (select max(age) from bakers);
Running query in 'baking'
1 rows affected.
baker
Karen 
%%sql
select
baker 
from bakers
where age >= all (select age from bakers);
Running query in 'baking'
1 rows affected.
baker
Karen 
%%sql

select
    b.baker
    , count(f.episodeid) as numfav
from 
    bakers b left join favorites f
    on b.baker = f.baker
group by
    b.baker ;
Running query in 'baking'
12 rows affected.
baker numfav
Rahul 4
Imelda 0
Jon 2
Karen 0
Ruby 1
Luke 0
Dan 2
Antony 0
Manon 0
Briony 2
Terry 0
Kim-Joy 1 
%%sql

-- For each baker, find if they have been a star baker
-- more times than they have been a favorite

select
    r.baker
    , count(*)
from 
    results r
where 
    r.result = 'star baker'
group by
    r.baker
having 
    count(*) > (select count(*) from favorites f
                where f.baker = r.baker) ;
Running query in 'baking'
3 rows affected.
baker count
Kim-Joy 2
Manon 1
Ruby 2 
%%sql

-- For each baker, find if they have been a star baker
-- more times than they have been a favorite

select
    r.baker
    , count(*)
from 
    results r
    , (select b.baker, count(f.episodeid) as numfav
       from bakers b left join favorites f on b.baker = f.baker
       group by b.baker) favs
where 
    r.result = 'star baker'
    and r.baker = favs.baker
group by
    r.baker, favs.numfav
having 
    count(*) > favs.numfav ;
Running query in 'baking'
3 rows affected.
baker count
Kim-Joy 2
Manon 1
Ruby 2 
%%sql
WITH favs as (select b.baker, count(f.episodeid) as numfav
       from bakers b left join favorites f on b.baker = f.baker
       group by b.baker
  )
SELECT
    r.baker
    , count(*)
FROM
   results r,
    favs f
WHERE
    r.result = 'star baker'
    and r.baker = f.baker
GROUP BY
    r.baker, f.numfav
HAVING 
    count(*) > f.numfav;
Running query in 'baking'
3 rows affected.
baker count
Kim-Joy 2
Manon 1
Ruby 2 
%%sql
WITH favs as (select b.baker, count(f.episodeid) as numfav
       from bakers b left join favorites f on b.baker = f.baker
       group by b.baker
  )
SELECT
    r.baker
    , count(*)
FROM
   results r
WHERE
    r.result = 'star baker'
GROUP BY
    r.baker
HAVING 
    count(*) > (SELECT numfav from favs where baker = r.baker);
Running query in 'baking'
3 rows affected.
baker count
Kim-Joy 2
Manon 1
Ruby 2 
%%sql
SELECT
    r.baker
    , r.episodeid
    , f.episodeid
FROM
   results r
   left join favorites f on r.baker = f.baker
WHERE
    r.result = 'star baker';
Running query in 'baking'
17 rows affected.
baker episodeid episodeid_1
Briony 6 1
Dan 4 2
Dan 4 3
Rahul 3 4
Rahul 2 4
Rahul 3 5
Rahul 2 5
Rahul 3 6
Rahul 2 6
Ruby 9 6
Ruby 8 6
Rahul 3 7
Rahul 2 7
Briony 6 8
Kim-Joy 7 9
Kim-Joy 5 9
Manon 1 None 
%%sql
SELECT
    r.baker
    , count(distinct r.episodeid)
FROM
   results r
   left join favorites f on r.baker = f.baker
WHERE
    r.result = 'star baker'
GROUP BY
    r.baker
HAVING
    count(distinct r.episodeid) > count(distinct f.episodeid)
    ;
Running query in 'baking'
3 rows affected.
baker count
Kim-Joy 2
Manon 1
Ruby 2 
%%sql

-- Find bakers who have been the favorite most times!

WITH favs AS (select baker, count(*) as num from favorites
              group by baker)
SELECT
     baker, num
FROM 
     favs
WHERE
     num = (select max(num) from favs) ;
Running query in 'baking'
1 rows affected.
baker num
Rahul 4 
%%sql

-- Find bakers who have been the favorite top 2 times!

WITH favs AS (select baker, count(*) as num from favorites
              group by baker)
SELECT
     baker, num
FROM 
     favs
WHERE
     num in (select distinct num from favs order by num desc limit 2) ;
Running query in 'baking'
4 rows affected.
baker num
Rahul 4
Dan 2
Briony 2
Jon 2

TRANSACTIONS (XACT)#

  • A transaction is a series of operations that change the database.

  • A transaction has a starting and ending point.

  • A transaction may succeed, then all its changes are made final.

    • Called a commit

  • A transaction may fail, then it should not make any changes to the database.

    • Called a rollback or abort

  • A transaction is atomic, either it will fully succeed or it will not have any effect.

create table abc (
id int primary key
, name varchar(100) not null
, cdate date
, val float
);

insert into abc values(1,‘joe’,‘10/24/2025’::date, 3.5);

insert into abc(id,name) values(2, ‘jane’);

insert into abc(id,cdate) values(3, ‘10/23/2025’::date);
– fails because name cannot be null

begin ;
insert into abc(id,name) values(4, ‘river’);
insert into abc(id,name) values(2, ‘forest’);
commit ;

– fails because one statement fails!

insert into abc(id,name)
select episodeid, baker from results
where result = ‘eliminated’ and episodeid> 5 ;

insert into abc(id,name,cdate)
select episodeid, baker, now()::date from results
where result = ‘eliminated’ and episodeid> 5 ;

update
set =
where ;

create table bresults (
baker varchar(10)
, val int
);

insert into bresults(baker) select baker from bakers ;

– update bresults such that set all values to zero

update bresults set val=0 ;

– update bresults such that for all bakers eliminated – in the first 3 episodes, value should be -1.

update bresults
set val = -1
where baker in (select baker from results
where result=‘eliminated’ and episodeid < 4);

– update bresults by setting any one with a value not -1 – the value should be the number of times they were a favorite

update bresults
set val = (select count(*) from favorites f
where f.baker = bresults.baker)
where val <> -1;

– delete from where ;

delete from abc ; — deletes all tuples!

delete from bresults
where val = -1 ;

– delete from bresults bakers who have never won star baker

delete from bresults
where baker not in (select baker from results
where result = ‘star baker’);

Contents