INT 203 - DB 1

db1.sql

-- 19 / SEP / 2018
select p.* , o.fname||' '||o.lname as name
from Propertyforrent p inner join Privateowner o on p.ownerno = o.ownerno;

select s.staffno, s.fname, s.lname as name, b.city
from staff s inner join branch b on s.branchno = b.branchno;

select c.fname, c.lname
from client c join viewing v on c.clientno = v.clientno
group by c.clientno, c.fname, c.lname
having count(v.viewdate) > 1;

-- 
select c.clientno, c.fname, v.propertyno, v.viewdate
from client c left outer join viewing v on c.clientno = v.clientno;

select s.fname, p.propertyno
from staff s left outer join propertyforrent p on s.staffno = p.staffno;

select p.propertyno, v.viewdate
from propertyforrent p left join viewing v on p.propertyno = v.propertyno
     join staff s on s.staffno = p.staffno
     join branch b on s.branchno = b.branchno 
where v.viewdate is null and b.street like '%22 Deer%';

--
select staffno
from staff
minus 
select staffno
from Propertyforrent;

--
select fname, lname
from staff
union
select fname, lname
from client
union
select fname, lname
from privateowner;

--
select propertyno
from propertyforrent
minus
select propertyno
from viewing;

--
(select city 
from branch
union
select city
from propertyforrent)

minus

(select city 
from branch
intersect
select city
from propertyforrent);

--
select s2.fname, s2.position, s2.salary
from staff s1 cross join staff s2
where s1.fname = 'Julie' and s1.salary = s2.salary;

select fname, position, salary
from staff
where salary = (select salary from staff where fname = 'Julie');

--
select s2.fname, s2.position, s2.salary
from staff s1 cross join staff s2
where s1.fname = 'Julie' and s1.salary < s2.salary;

--
select s.fname, b.city
from staff s join branch b on s.branchno = b.branchno
where b.city = 'London';

--
select fname
from staff
where branchno in 
(select branchno
from branch 
where city = 'London');
--

select s.fname, p.rooms
from staff s join propertyforrent p on s.staffno = p.staffno
where p.rooms = 3;

select fname
from staff
where staffno in
                (select staffno
                from propertyforrent
                where rooms = 3);

--

select p.propertyno
from propertyforrent p join privateowner o on p.ownerno = o.ownerno
where o.telno like '012%';

select propertyno
from propertyforrent
where ownerno in
                (select ownerno
                from privateowner
                where telno like '012%');
                
                
--
select b.branchno, s.staffno
from branch b left join staff s on b.branchno = s.branchno
where s.staffno is null;

select branchno from branch
minus
select branchno from staff;

select branchno from branch
where branchno not in (
select branchno from staff);

--
select s.fname, s.salary
from staff s join branch b on s.branchno = b.branchno
where salary < (select avg(salary) from staff) 
      and b.city = 'Glasgow'; 
--
select s.fname, s.branchno, s.salary, avgSalary.av
from staff s join (select branchno, 
                   avg(salary) av 
                   from staff group by branchno) avgSalary on s.branchno = avgSalary.branchno
where s.salary > avgSalary.av;