Consider the following relational schema, SQL in oracle sqlplus This is killing

Question

Consider the following relational schema, SQL in oracle sqlplus

This is killing me completely, I have been able to complete the last two assignments with 3 tables where one of them was a join between the other two. But this time I think this is asking me to join across 3-5 tables and I have no idea how to do this. Please help

Consider the following relational schema orders NUMERIC ordendate MESTAMP(6) WTHOUT TIME ZONE customers ordemumber CHARACTER VARYING(10) orderitems customerid NUMERIC NUMERIC NUMERIC totalamount NUMERIC(12,2) firstname CHARACTER VARYING(40) orderid NUMERIC lastname CHARACTER VARYING(40) productid NUMERIC city CHARACTER VARYING(40) unit price NUMERIC (12.2 country CHARACTER VARYING(40) quantity NUMERIC phone CHARACTER VARYING(20) products NUMERIC suppliers productname CHARACTER VARYING (50) supplierid UMERIC NUMERIC NUMERIC(12,2) company name CHARACTER VARYING(40) nitprice package CHARACTER VARYING (30) contactname ARACTER VARYING(50) sdiscontinued NUMERIC contactite CHARACTER VARYING (40) CHARACTER VARYING (40) country CHARACTER VARYING(40) phone CHARACTER VARYING(30) CHARACTER VARYING(30 1) (10pts) Retrieve the first names of customers who have ordered all products supplied by Leka Trading' company 2) (10pts) For every company, retrieve their name and the number of products they supply 3) (10pts) Count the number of customers who have ordered different products on the same date 4) (10pts) Retrieve the price of the most expensive product ordered by Simon Crowther 5) (10pts) List the ids of customers who have ordered exactly six different products (without considering quantity) on the same date

Explanation / Answer

Answer 1

select c.firstname from customer c inner join orderitems ot on ot.id = c.id inner join supplier s where companyname = 'Leka Trading';

Answer 2

select count(p.id), s.companyname from products P join suppliers s on p.supplierid = s.id group by s.companyname;

Answer 3

select count(customerid),orderdate from orders group by orderdate;

Answer 4

select max(o.unitprice),c.firstname,c.lastname from orderitems o join customer c on c.id = o.id where firstname = 'Simon' and lastname = 'Crowther';

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.