Chapter 7 CS.docx - Chapter 7 8 Assignments 42 select...

This preview shows page 1 - 3 out of 5 pages.

Chapter 7 & 8 Assignments 42. select customer_t.customerid from customer_t where customer_t.customerid not in (select customerid from order_t); 43. select s.employeename, count(e.employeeid) as headcount from employee_t s, employee_t e where s.employeeid = e.employeesupervisor group by s.employeename having headcount >2; 44. select e1.employeename, e1.employeebirthdate, e2.employeename as manager, e2.employeebirthdate as managerbirth from employee_t e1, employee_t e2 where e1.employeesupervisor = e2.employeeid and e1.employeebirthdate < e2.employeebirthdate; 45. select order_t.orderid, order_t.customerid, order_t.orderdate, orderline_t.productid, product_t.productdescription, orderline_t.orderedquantity from orderline_t, order_t, product_t where order_t.orderid = orderline_t.orderid and orderline_t.productid = product_t.productid and order_t.customerid = 4; 46. select orderline_t.productid, product_t.productstandardprice, sum(orderline_t.orderedquantity)* product_t.productstandardprice as totalprice from product_t, orderline_t where product_t.productid = orderline_t.productid group by orderline_t.productid, product_t.productstandardprice, orderline_t.orderid having orderline_t.orderid =1; 47. create view fourmigos as select orderline_t.productid, product_t.productstandardprice, sum(orderline_t.orderedquantity)* product_t.productstandardprice as totalprice from product_t, orderline_t where product_t.productid = orderline_t.productid group by orderline_t.productid, product_t.productstandardprice, orderline_t.orderid having orderline_t.orderid = 1001; select sum(fourmigos.totalprice) as totalcost from fourmigos; 48. select p.productid, productdescription, p.productstandardprice, sum(quantityrequired*r.materialstandardprice) as totcost from product_t as p, uses_t as u, rawmaterial_t as r where p.productid = u.productid and u.materialid = r.materialid group by p.productid, productdescription, p.productstandardprice; 49. select orderline_t.orderid, sum(orderedquantity*productstandardprice) as totaldue, paymentamount from orderline_t, product_t, payment_t where orderline_t.productid = product_t.productid and orderline_t.orderid = payment_t.orderid group by orderline_t.orderid, paymentamount order by sum(orderedquantity*productstandardprice) - paymentamount desc; 50. select customer_t.customerid, customername, sum(orderedquantity) as unitsbought from orderline_t, order_t, product_t, customer_t where productdescription LIKE '%computer desk%' and order_t.orderid = orderline_t.orderid and product_t.productid = orderline_t.productid and customer_t.customerid = order_t.customerid group by customer_t.customerid, customername; order_t customer_t product_t orderline_t
Image of page 1

Subscribe to view the full document.

51. select distinct customerid from order_t where orderid in (select distinct orderid from orderline_t where
Image of page 2
Image of page 3
  • Fall '17
  • Barson
  • SELECT CustomerID, VBScript, T S

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern