schema and simplest query

kuntilanak
kuntilanak used Ask the Experts™
on
given the following schema

how would you write the simplest query possible using SQL to represent:

What are the supplier of PS3 sold in a department managed by Tom?
Sale (saleno, saleqty, itemno, dname)
Supplier (splno, splname)
Item (itemno, itemname, itemtype, itemcolor)
Department (deptname, deptfloor, deptphone, empno)
Delivery (delno, delqty, itemnum, dptname, splno)
Employee (empno, empfname, empsalary, departname, bossno)
Using

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
Hello kuntilanak,

I Try :


SELECT * FROM Sale s, Supplier u, Item i, Department d, Delivery e, Employee m WHERE s.itemno = i.itemno AND u.splno = e.splno AND d.empno = m.empo

Open in new window

Author

Commented:
you did not even mention PS3 or Tom in the query, so I know directly that it is wrong
leakim971Multitechnician
Top Expert 2014

Commented:
Sorry :


SELECT * FROM Sale s, Supplier u, Item i, Department d, Delivery e, Employee m WHERE s.itemno = i.itemno AND u.splno = e.splno AND d.empno = m.empo AND u.splname = 'PS3' AND m.empfname = 'Tom'

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
and shouldn't it be select splname? instead of select *

Author

Commented:
and PS3 should be itemname not supplier name, the supplier name is what we're querying for
leakim971Multitechnician
Top Expert 2014

Commented:
I understand :
SELECT splname FROM Sale s, Supplier u, Item i, Department d, Delivery e, Employee m WHERE s.itemno = i.itemno AND u.splno = e.splno AND d.empno = m.empo AND u.itemname = 'PS3' AND m.empfname = 'Tom'

Open in new window

Commented:

Hi kuntilanak,

Good question .But without provide any sample datas u are giving cool comments on each post.

please do provide sample data and u will get the query exactly u ae looking for.

Author

Commented:
if you can make the following query more simple, that's what I want
select distinct splname from (select * from x.item where itemname = 'Pith_helmet') u, (select * from (select * from x.employee where empfname = 'Andrew') t, x.department where (t.empno = department.empno)) v, x.delivery, x.supplier where (u.itemno = delivery.itemnum) and (v.deptname = delivery.dptname) and (delivery.splno = supplier.splno);

Open in new window

Data Engineer
Commented:
can you try this?
select distinct splname
  from x.item i,
       x.employee e,
       x.department d,
       x.delivery de,
       x.supplier s
 where e.empno = d.empno
   and i.itemno = de.itemnum
   and d.deptname = de.dptname
   and de.splno = s.splno
   and i.itemname = 'Pith_helmet'
   and e.empfname = 'Andrew';

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial