Lecture 14
----------------

SQL DDL

Please use ONLY your own database on the server for
these: database named db_<username>

SELECT FROM WHERE --> query

transaction: operations that change data


BEGIN transaction ;
operations
....
operations
END transaction ;


Commit a transaction -> complete it fully successfully
Abort a transaction -> transaction did not complete
    Rollback

Atomicity: all or nothing
   The transaction should either completely fully or
    should not cause any change in the database

Isolation: write programs as if it is the only only
   transaction operating on the db


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

	 , class   char(4)
	 , notes   text
	 , price   numeric(5,2)
	 , PRIMARY KEY (id)


DROP TABLE abc ;
DROP TABLE def ;

CREATE TABLE abc (
     aid        int
     , name    varchar(255)
     , PRIMARY KEY (aid)
) ;


CREATE TABLE def (
    bid   int
    , name varchar(255)
    , aid int
    , PRIMARY KEY (bid)
) ;       


INSERT INTO abc VALUES(1,'Leaf') ;
INSERT INTO abc VALUES(2,'River') ;
INSERT INTO abc VALUES(3,'Hill') ;
INSERT INTO abc VALUES(4) ;


INSERT INTO def VALUES(11,'Moon',1) ;
INSERT INTO def VALUES(12,'Sun',1) ;
INSERT INTO def VALUES(13,'Jupiter',3) ;

INSERT INTO def(bid, aid) VALUES(14,3) ;


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

CREATE TABLE rty AS 
SELECT a.aid, a.name as aname, d.bid, d.name as dname
FROM  abc a, def d
WHERE a.aid = d.aid;

INSERT INTO rty(aid, aname)
SELECT
    bid
    , 'Saturn'
FROM
    def
WHERE
    name is null ;


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

Delete statement:

DELETE FROM <table> WHERE <conditions> ;

DELETE FROM rty WHERE aid = 3 ;

DELETE FROM rty ;


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

Create a table summary data for bakers.

Attributes: baker, fullname, age

numwins, numfavories

drop table summary ;
create table summary as
select
   b.baker
   , b.fullname
   , b.age
   , count(distinct r.episodeid) as numwins
   , count(distinct f.episodeid) as numfavorites
from
   bakers b
   left join results r
   on b.baker = r.baker and r.result = 'star baker'
   left join favorites f
   on b.baker = f.baker
group by
   b.baker
   , b.fullname
   , b.age;


delete from summary s where s.numwins = 0 ;

Delete from table all bakers who never won a technical challenge


select
   *
from
   bakers b
where
   b.baker not in (select baker from technicals where rank =1 ) ;



delete from summary
where baker not in (select baker from technicals where rank =1 ) ;

update <table> set <attr = value>,<attr=value> where <cond> ;



update summary set age = age+4 ;
--- updates all tuples


update summary set numwins = numwins+1 where baker = 'Rahul' ;
--- update tuples that satisfy the WHERE clause

update numwins for all bakers who competed in 10 episodes

update summary set numwins = numwins+1
where (select count(*) from showstoppers s
       where s.baker = summary.baker) = 10 ;



Update the numtechnicals attribute

alter table summary add (numtechnicals int) ;


update summary
set numtechnicals = (select count(*)
                     from technicals t
		     where t.baker = summary.baker and t.rank=1)
;

-- BEWARE: update does not allow an alias  for the main relation being updated

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

Integrity constraints:


DROP TABLE abc ;
DROP TABLE def ;

CREATE TABLE abc (
     aid        int
     , name    varchar(255)  NOT NULL
     , class   char(2)  check (class in ('FR','SO','JR','SR'))
     , notes   text
     , price   numeric(5,2)
     , PRIMARY KEY (aid) ----
     , CHECK (class is not null or price is not null)
) ;

INSERT INTO abc(aid, name, class) VALUES(1,'Leaf','SO') ;
INSERT INTO abc(aid, name, price) VALUES(2,'River',500) ;
INSERT INTO abc(aid, name, class) VALUES(3,'Hill','FR') ;
INSERT INTO abc(aid, name, class) VALUES(4,'Snwo','SO') ;


CREATE TABLE def (
    bid   int
    , name varchar(255)
    , aid int
    , PRIMARY KEY (bid)
    , FOREIGN KEY (aid) REFERENCES abc(aid)
         ON DELETE SET NULL
	 ON UPDATE CASCADE
) ;       

insert into def(bid, name) values(11, 'Sun') ;
insert into def(bid, name, aid) values(12, 'Moon', 2) ;
insert into def(bid, name, aid) values(13, 'Jupiter', 3) ;
insert into def(bid, name, aid) values(14, 'Saturn', 3) ;

-------------------------------
def(aid) references abc(aid)

What happens if I delete an abc tuple that is referenced in def?

1. Restrict: delete fails 
2. Cascade: delete all tuples in def that reference the tuple being deleted
3. Set null