Consider the following schema: Parts ( pid : string , pname : string, descriptio
ID: 3610041 • Letter: C
Question
Consider the following schema:
Parts(pid: string,pname: string, description: string, color:string)
Departments(did: string,dname:string, address: string)
Suppliers(sid:string,sname:string,address:string)
Orders(did:string,sid: string, pid:string,time:string, quantity:real,price:real)
The key fields are underlined, and the domain of each field islisted after the field name. Thus pid is the key forPart, did is the key forDepartment, sid is the key forSupplier, and did, sid,and pidtogether form the key for Order. Write thefollowing queries in SQL.
"Find the names of department."
"Find the names and addresses of suppliers who supply redpart."
"Find the pids of parts which are ordered by IT dept."Note IT is department name.
" Find the department names who have ordered parts from SupplierDELL". Note DELL is supplier name.
"Find the dids of departments which have ordered bothpart A01 and A02". Note A01 and A02 are part ids.
"Find the dids of department which have ordered atleast one part".
"Find the names of department which have ordered atleast two parts".
"Find the names of department which have ordered allparts."
"Find the did of department which have not ordered anyproduct from Supplier S001", Note S001 is supplier ID.
"Find the pids of parts that are supplied by at leasttwo different suppliers."
Explanation / Answer
"Find the names of department."
select dname from Departments;
"Find the names and addresses of suppliers who supply redpart."
select sname, address fromSuppliers where sid in( select sid from Orders where pid in( selectpid from Parts where color = ‘red’));
"Find the pids of parts which are ordered by IT dept." Note ITis department name.
select pid from Parts wherepid in(select pid from Orders where did in ( select did fromDepartments where dname= ‘IT dept’));
" Find the department names who have ordered parts from SupplierDELL". Note DELL is supplier name.
select dname fromDepartments where did in( select did from Orders where sid in(select sid from Suppliers where sname = ‘DELL’));
"Find the dids of departments which have ordered both part A01and A02". Note A01 and A02 are part ids.
select did from Departmentswhere did in( select did from Orders where pid = ‘A01’& ‘A02’);
"Find the dids of department which have ordered at least onepart".
select did from Departmentswhere did in( select did from Orders where count(pid) >=1);
"Find the names of department which have ordered at least twoparts".
select dname fromDepartments where did in( select did from Orders where count(pid)>=2);
"Find the pids of parts that are supplied by at least twodifferent suppliers."