;;; IMPORTANT: This solution is not to be shared with other students.
;;; It is for your use only.
;;; Sharing this solution with other students is a violation
;;; of college academic honesty policies.
-- Problem 1
drop view if exists home;
create view home as
select empid, phonenumber as home
from Phones
where phonetype='home';
drop view if exists cell;
create view cell as
select empid, phonenumber as cell
from Phones
where phonetype='cell';
select Employees.empid, Employees.firstname, Employees.lastname, home.home, cell.cell
from Employees
left outer join home using (empid)
left outer join cell using (empid);
-- Problem 2
drop view if exists promotionsales cascade;
create view promotionsales as
select promo,salesperson,sum(amount) as amount
from Sales
join Promotions on saledate >= startdate and saledate <= enddate
group by promo,salesperson;
drop view if exists maxamount cascade;
create view maxamount as
select promo,max(amount) as amount
from promotionsales
group by promo;
drop view if exists bestsales cascade;
create view bestsales as
select promo, salesperson, amount
from promotionsales
join maxamount using (promo,amount);
select promo, salesperson, amount
from Promotions
left outer join bestsales using (promo)
order by promo;