Lecture 15 - Procedural SQL

Lecture 15 - Procedural SQL#

Announcements#

  • No new lecture exercises today

  • Expect a new homework by monday the latest

  • I posted on Bulletin Board for Hw#4 about how to time yourselves (if you wish) and my run time using this method

Today’s lecture#

  • Transactions and constraint checking

  • Procedural programming

DDL#

  • Insert/update/delete

load_ext sql

drop table if exists tmp ;

create table tmp (
    tmpid    varchar(50)
    , name   varchar(100)
    , primary key (tmpid)
) ;
 
insert into tmp
select parkid, fullname
from parks
where lower(fullname) like '%pa%';


create table tmp2 as 
select parkid, fullname, parkcode
from parks
where lower(fullname) like '%his%';

alter table tmp2 add primary key (parkid) ;

-- insert into tmp tuples from tmp2 that are not yet in tmp!

insert into tmp
select parkid, fullname from tmp2 except select tmpid, name from tmp;

delete from tmp2 ;
insert into tmp2 select parkid, fullname from parks;

-- delete from tmp2 all tuples that are already in tmp

delete from tmp2
where not exists(select * from tmp t where t.tmpid = tmp2.parkid);

delete from tmp2
where parkid not in (select tmpid from tmp);


----------------------

drop table if exists c ;
drop table if exists b ;
drop table if exists a ;

create table a (
   aid  int  primary key
   , aname varchar(10)
) ;

create table b (
   bid  int  primary key
   , aid  int  not null
   , bname  varchar(10)
   , foreign key (aid) references a(aid)
         on delete cascade on update cascade
);


create table c (
   cid  int 
   , aid  int
   , bid  int  
   , primary key (cid, aid)
   , foreign key (aid) references a(aid)
           on delete restrict on update cascade
   , foreign key (bid) references b(bid)
           on delete set null on update set null
);

insert into a values (1,'abc');
insert into a values (2,'def'); 
insert into a values (3,'ghi');
insert into a values (4,'dfe');


insert into b values (11,1, 'aabc');
insert into b values (12,1,'ddef'); 
insert into b values (13,3,'gfhi');
insert into b values (14,1,'gfhi');

insert into c(cid,aid,bid) values (101,2,11);
insert into c(cid,aid,bid) values (102,3,11);
insert into c(cid,aid,bid) values (103,3,12);
insert into c(cid,aid,bid) values (102,2,13);

import psycopg2 as dbapi2

db = dbapi2.connect (database="sibeladali", \
                     user="sibeladali", \
                     password="sibeladali")
cur = db.cursor()

cur.execute ("SELECT * from b;");
rows = cur.fetchall()
for i, row in enumerate(rows):
    ##print ("Row", i, "value = ", row)
    print(row[0], row[1], row[2])

12 1 ddef
14 1 gfhi
13 8 gfhi
21 1 aabc
Contents