drop table branch;
create table branch(Bno char(15),Street char(15),Area char(15),City char(15),Pcode char(15),Tel_No char(15),Fax_No char(15));
insert into branch values ('B5','22 Deer Rd','Sidcup','London','SW1 4EH','0171-886-1212','0171-886-1214');
insert into branch values ('B7','16ArgyllSt','Dyce','Aberdeen','AB2 3SU','01224-67125','01224-67111');
insert into branch values ('B3','163 Main St','Partick','Glasgow','G11 9QX','0141-339-2178','0141-339-4439');
insert into branch values ('B4','32 Manse Rd','Leigh','Bristol','BS99 1NZ','0117-916-1170','0117-776-1114');
insert into branch values ('B2','56 Colver Dr','','London','NW10 6EU','0181-963-1030','0181-453-7992');
drop table staff;
create table staff(Sno char(15),FName char(15),LName char(15),Address char(40),Tel_No char(15),Position char(15),Sex char(15),DOB char(15),Salary decimal(8,2),NIN char(15),Bno char(15));
insert into staff values ('SL21','John','White','19 Taylor St,Cranford,London','0171-884-5112','Manager','M','1-Oct-45','30000','WK442011B','B5');
insert into staff values ('SG37','Ann','Beech','81 George St,Glasgow PA1 2JR','0141-848-3345','Snr Asst','F','10-Nov-60','12000','WL432514C','B3');
insert into staff values ('SG14','David','Ford','63 Ashby St,Partick,Glasgow G11','0141-339-2177','Deputy','M','24-Mar-58','18000','WL220658D','B3');
insert into staff values ('SA9','Mary','Howe','2 Elm P1,Aberdeen AB2 3SU','','Assistant','F','19-Feb-70','9000','WM532187D','B7');
insert into staff values ('SG5','Susan','Brand','5 Gt Western Rd,Glasgow G12','0141-334-2001','Manager','F','3-Jun-40','24000','WK588932E','B3');
insert into staff values ('SL41','Julie','Lee','28 Malvern St,Kilburn NW2','0181-554-3541','Assistant','F','13-Jun-65','9000','WA290573K','B5');
drop table property_for_rent;
create table property_for_rent(Pno char(15),Street char(15),Area char(15),City char(15),Pcode char(15),Type char(15),Rooms char(15),Rent char(15),Ono char(15),Sno char(15),Bno char(15));
insert into property_for_rent values ('PA14','16 Holhead','Dee','Aberdeen','AB7 5SU','House','6','650','CO46','SA9','B7');
insert into property_for_rent values ('PL94','6 Argyll St','Kilburn','London','NW2','Flat','4','400','CO87','SL41','B5');
insert into property_for_rent values ('PG4','6 Lawrence St','Partick','Glasgow','G11 9QX','Flat','3','350','CO40','SG14','B3');
insert into property_for_rent values ('PG36','2 Manor Rd','','Glasgow','G32 4QX','Flat','3','375','CO93','SG37','B3');
insert into property_for_rent values ('PG21','18 Dale Rd','Hyndland','Glasgow','G12','House','5','600','CO87','SG37','B3');
insert into property_for_rent values ('PG16','5 Novar Dr','Hyndland','Glasgow','G12 9AX','Flat','4','450','CO93','SG14','B3');
drop table renter;
create table renter(Rno char(15),FName char(15),LName char(15),Address char(40),Tel_No char(15),Pref_Type char(15),Max_Rent char(15),Bno char(15));
insert into renter values ('CR76','John','Kay','56 High St,Putney,London SW1 4EH','0171-774-5632','Flat','425','B5');
insert into renter values ('CR56','Aline','Stewart','64 Fern Dr,Pollock, Glasgow G42 OBL','0141-848-1825','Flat','350','B3');
insert into renter values ('CR74','Mike','Ritchie','18 Tain St,Gourock PA1G 1YQ','01475-392178','House','750','B3');
insert into renter values ('CR62','Mary','Tregear','5 Tarbot Rd,Kildary,Aberdeen AB9 3ST','01224-196720','Flat','600','B7');
drop table owner;
create table owner(Ono char(15),FName char(15),LName char(15),Address char(40),Tel_No char(15));
insert into owner values ('CO46','Joe','Keogh','2 Fergus Dr,Banchory,Aberdeen AB2 7SX','01224-861212');
insert into owner values ('CO87','Carol','Farrel','6 Achray St,Glasgow G32 9DX','0141-357-7419');
insert into owner values ('CO40','Tina','Murphy','63 Well St,Shawlands,Glasgow G42','0141-943-1728');
insert into owner values ('CO93','Tony','Shaw','12 Park Pl,Hillhead,Glasgow G4 0QR','0141-225-7025');
drop table viewing;
create table viewing(Rno char(15),Pno char(15),Pdate char(15),Pcomment char(15));
insert into viewing values ('CR56','PA14','24-May-98','too small');
insert into viewing values ('CR76','PG4','20-Apr-98','too remote');
insert into viewing values ('CR56','PG4','26-May-98','-');
insert into viewing values ('CR62','PA14','14-May-98','no dining room');
insert into viewing values ('CR56','PG36','28-Apr-98','-');
show columns * from branch;
select * from branch;
show columns * from staff;
select * from staff;
show columns * from property_for_rent;
select * from property_for_rent;
show columns * from renter;
select * from renter;
show columns * from owner;
select * from owner;
show columns * from viewing;
select * from viewing;
select Sno,FName,LName,Salary from staff;
select distinct Pno from viewing;
select Sno, FName, LName, Salary/12 AS montly_salary from staff;
select Sno,FName,LName,Salary from staff where Salary>10000;
select Bno,Street,Area,City from branch where City='London' or City='Glasgow';
select Sno,FName,LName,Salary from staff where Salary>=20000 and Salary <=30000;
select Sno,FName,LName,Position from staff where Position IN ('Manager','Deputy');
select Sno,FName,LName,Address,Salary from staff where Address like '%Glasgow%';
select Rno,Pdate from viewing where Pno='PG4' and Pcomment is null;
select Sno,FName,LName,Salary from staff order by salary desc;
select Pno,Type,Rooms,Rent from property_for_rent order by Type,Rent desc;
select count(*) as count from property_for_rent where Rent>350;
select count(distinct Pno) as count from viewing where Pdate between '1-May-98' and '31-May-98';
select count(Sno) as count, sum(Salary) as sum from staff where Position='Manager';
select min(Salary) as min, max(Salary) as max, avg(Salary) as avg from staff;
select Bno, count(Sno) as count, sum(Salary) as sum from staff group by Bno order by Bno;
select Bno, count(Sno) as count, sum(Salary) as sum from staff group by Bno having count(Sno)>1 order by Bno;
select sno,FName,LName,position from staff where bno=(select bno from branch where street = '163 Main St');
select Sno,FName,LName,Position,Salary-(select avg(Salary) from staff) as sal_diff from staff where Salary > (select avg(Salary) from staff);
select Pno,Street,Area,City,Pcode,Type,Rooms,Rent from property_for_rent where Sno in (select Sno from staff where Bno in (select Bno from branch where Street = '163 Main St'));
select Sno,FName,LName,Position,Salary from staff where Salary > some (select Salary from staff where Bno = 'B3');
select Sno,FName,LName,Position,Salary from staff where Salary > all (select Salary from staff where Bno = 'B3');
select r.Rno,FName,LName,Pno,Pcomment from renter r, viewing v where r.rno = v.rno;
select S.Bno,s.Sno,FName,LName,Pno from staff s, property_for_rent p where s.sno = p.Sno order by s.Bno,s.Sno,Pno;
select b.Bno,b.City,s.Sno,FName,LName,Pno from branch b, staff s, property_for_rent p where b.Bno = s.Bno and s.Sno=p.Sno order by b.Bno,s.Sno,Pno;
select S.bno,s.Sno,count(*) as count from staff s, property_for_rent p where s.sno=p.sno group by s.Bno,s.Sno order by s.Bno,s.Sno;
select b.*, p.* from branch b left join property_for_rent p on b.City =p.City;
select b.*, p.* from branch b right join property_for_rent p on b.City =p.City;
select b.*, p.* from branch b full join property_for_rent p on b.City =p.City;
select Sno,FName,LName,Position from staff s where exists (select * from branch b where s.Bno=b.Bno and City='london');
(select Area from branch where Area is not null) union (select Area from property_for_rent where Area is not null);
(select Area from branch where Area is not null) intersect (select Area from property_for_rent where Area is not null);
(select Area from branch where Area is not null) except (select Area from property_for_rent where Area is not null);