COMP2400 2007 Lab4 Solutions
-- Part 1

-- Ex 1

select pno, sum(hours)
from works_on
group by pno;

OR

select pno, sum(hours)
from project, works_on
where pnumber = pno
group by pno;

-- Ex 2

select dname, avg(salary)
from employee, department
where dno = dnumber
group by dname;

-- Ex 3
select max(project_hours)
from (
  select sum(hours) as project_hours
  from works_on
  group by pno
) A;

  
-- Ex 4
-- Answer = 1000

select dno
from employee
group by dno
having count(*) = (
    select max(emp_count) from (
      select count(*) as emp_count
      from employee
      group by dno) A
  );

-- Ex 5

-- Version 1

-- Not a correlated subquery.

select fname, lname 
from employee, 
  (
    select dnumber, count(*) as loc_count
    from dept_location
    group by dnumber 
  ) A
where dnumber = dno
and loc_count > 1;

-- Version 2 -- still not a correlated subquery

select fname, lname 
from employee
where dno in (
    select dnumber
    from dept_location
    group by dnumber
    having count(*) > 1
  );

--
-- Better - thie *is* a correlated subquery
--
select fname, lname from employee
where (select count(*) 
       from dept_location 
       where dnumber = dno) > 1;

-- Ex 6

--
-- As a join
--
select E.fname, E.lname 
from employee E, employee S
where E.super_eno = S.enumber
and E.salary > S.salary;

--
-- As a correlated subquery! (This is what I'm after)
--
select E.fname, E.lname 
from employee E
where salary > (select S.salary 
                from employee S 
                where S.enumber = E.super_eno) 

-- Part 2

-- Ex 7

select count(*) from (
  select distinct order_date
  from wine_order 
  where customer_name = 'Fred Bloggs'
) A;

-- Ex 8

select C.customer_name
from (
  select distinct customer_name, amount_paid, order_date
  from wine_order) C
group by C.customer_name
having sum(C.amount_paid) = (select max(B.total) from (
  select sum(A.amount_paid) as total, A.customer_name
  from (
    select distinct customer_name, amount_paid, order_date
    from wine_order) A
  group by customer_name) B
);


-- Ex 9

-- One possible query

select customer_name, order_date from
(select case when quantity <= quantity_in_stock then 1 else 0 end
as order_satisfied, customer_name, order_date
from wine_order, wine
where wine_order.wine_name = wine.wine_name) A
group by customer_name, order_date
having min(order_satisfied) = 1;

--
-- Robin's original solution 
--
select required.customer_name,required.order_date from
(select customer_name,order_date,sum(quantity) as qty 
   from wine_order where shipped_date is null) 
 as required,
(select customer_name,order_date,sum(quantity) as qty 
   from wine_order o natural join wine w 
   where quantity <= quantity_in_stock) 
 as available
where required.customer_name = available.customer_name
and   required.order_date = available.order_date
and   required.qty = available.qty;

--
-- Nice solution courtesy Daniel Frampton
--
select customer_name,order_date 
from wine_order natural join wine
where amount_paid >= total_price
and shipped_date is null
group by customer_name,order_date
having min(quantity_in_stock - quantity) > 0;

10.

11. The 'natural' design would have 3 classes, Customer, Wine and Order,
    plus an association class for the wine quantity.