Notes

PART 1

1.
create table CANDIDATES(CCOE varchar(3),CNAME varchar(30),primary key(CCODE));
create table VOTES(PCODE varchar(2), CCODE varchar(3), VOTE integer(8), primary key(PCODE,CCODE));
create table PRECINCTS(PCODE varchar(2), PNAME varchar(8), primary key(PCODE));

2.
insert into CANDIDATES values('VIL','MAnny Villar'),('EST','Erap Estrada'),('AQU','Noynoy Aquino');
insert into VOTES values('R1','EST',500),('R1','AQU',550),('R1','VIL',600),('R2','EST',450),('R2','AQU',600),('R2','VIL',400),('R3','EST',450),('R3','AQU',700),('R3','VIL',300);
INSERT INTO PRECINTS VALUES('R1','REGION 1'),('R2','REGION 2'),('R3','REGION 3');

3.
UPDATE VOTES
SET VOTE=450
WHERE PCODE='R2'AND CCODE='VIL'

4.

A.
SELECT A.PNAME,B.VOTE
FROM VOTES A, PRECINCTS B
WHERE CCODE='AQU'AND A.CCODE=B.CCODE;

B.
SELECT A.PNAME, SUM(B.VOTE)
FROM VOTES B, PRECINCTS A
WHERE B.PCODE = A.PCODE;

C.
SELECT A.CNAME, SUM(B.VOTE)
FROM CANDIDATES A, VOTES B
WHERE A.CCODE = B.CCODE
ORDER BY SUM(b.VOTES) DESC;


PART 2

1.
select ac.No Of Copies
from BOOK COPIES ac, LIBRARY_BRANCH a, BOOK c
where a.BranchName='Sharpstown' and c.Title='The Lost Tribe' and a.BranchId=ac.BranchId and ac.BookId=c.BookId;

2.
select a.BranchName,ac.No Of Copies
from BOOK COPIES ac, LIBRARY_BRANCH a, BOOK c
where c.Title='The Lost Tribe' and a.BranchId=ac.BranchId and ac.BookId=c.BookId;

3.
select a.Name
from BORROWER A, BOOK LOANS B
where a.CardNo=b.Cardno and DateOut is NULL;

4.
select b.Name, b.Address, c.Title
from BOOKLOANS abc, LIBRARY_BRANCH a, BORROWER b, BOOK c
where abc.Duedate=Now() and b.BranchName='Sharpstown' and abc.CardNo=b.CardNo. and b.BranchId=abcBranchId and abc.BookId=c.BookId;

5.
select b.BranchName, sum(bc.No Of Copies)
from LIBRARY_BRANCH b, BOOK cOPIES bc, BOOK c, BOOK LOANS abc
where abc.DueDate is not NULL and abc.BranchId=b.BranchId and abc.BookId=c.BookId and bc.BranchId=b.BranchId and bc.BookId=abc.BookId;

6.
select b.Name, b. Address, sum(bc.No Of Copies)
from BORROWER b, BOOK cOPIES bc, BOOK c, BOOK LOANS abc
where abc.DueDate is not NULL and abc.CarNo=b.CardNo and abc.BookId=c.BookId and bc.BookId=abc.BookId and sum(bc.No Of Copies)>5;

7.
select b.Title, bc.No Of Copies
from LIBRARY_BRANCH a, Book b, BOOK COPIES bc, BOOK AUTHORS d
WHERE d.AuthorName='Stephen King' and d.Name=b.PublisherName and a.BranchName='Central' and a.BranchId=bc.BranchId and b.BookId=bc.BookId;