Questions on SQL

Question 1 : Consider the following schema:
             Suppliers(sid: integer, sname: string, address: string)
             Parts(pid: integer, pname: string, color: string)
             Catalog(sid: integer, pid: integer, cost: real)
             The Catalog relation lists the prices charged for parts by
             Suppliers. Write the following queries in SQL:
1. Find the pnames of parts for which there is some supplier.
Solution :
          SELECT p.pname FROM Parts AS p
          WHERE EXISTS (
          SELECT * FROM Catalog AS c
          WHERE c.pid = p.pid)
2. Find the snames of suppliers who supply every red part.
Solution : SELECT s.sid, s.sname 
           FROM Suppliers AS s, Catalog AS c, Parts AS p
           WHERE s.sid = c.sid
           AND p.pid = c.pid AND p.color = ‘red’ 
           GROUP BY s.sid, s.sname 
           HAVING COUNT(*) = (SELECT COUNT(*) FROM Parts AS p1
           WHERE p1.color=‘red’)
3. Find the snames of suppliers who supply every part.
Solution : SELECT S.sname FROM Suppliers S
           WHERE NOT EXISTS (( SELECT P.pid FROM Parts P )
           EXCEPT 
           ( SELECT C.pid FROM Catalog C
           WHERE C.sid = S.sid ))
4. Find the pnames of parts supplied by Acme Widget Suppliers and
   no one else.
Solution : SELECT p.pname FROM Parts AS p 
           WHERE p.pid IN (
           (SELECT c1.pid AS pid FROM Catalog AS c1, Suppliers s1
           WHERE c1.pid = s1.sid 
           AND s1.sname = ‘Acme Widget Suppliers’)
           EXCEPT 
           (SELECT DISTINCT c2.pid AS pid
           FROM Catalog AS c2, Suppliers s2 
           WHERE c2.pid = s2.sid 
           AND s2.sname != ‘Acme Widget Suppliers’))
5. Find the sids of suppliers who charge more for some part than the 
   average cost of that part (averaged over all the suppliers who
   supply that part.
Solution : SELECT DISTINCT c.sid FROM Catalog AS c,
           (SELECT c1.pid AS avg pid, AVG(c1.cost) AS avg cost
           FROM Catalog AS c1 GROUP BY c1.pid) AS TEMP 
           WHERE c.pid = TEMP.avg pid AND c.cost > TEMP.avg cost
6. Find the sids of suppliers who supply a red part and a green part.
Solution : (SELECT DISTINCT c1.sid AS SID 
           FROM Parts AS p1, Catalog AS c1 
           WHERE p1.pid = c1.pid AND p1.color = ‘green’) 
           INTERSECT (
           SELECT DISTINCT c2.sid AS SID 
           FROM Parts AS p2, Catalog AS c2 
           WHERE p2.pid = c2.pid AND p2.color = ‘red’)
         or
           SELECT DISTINCT c.sid
           FROM Catalog c, Parts p
           WHERE c.pid = p.pid and p.color = 'red'
           INTERSECT
           SELECT DISTINCT c.sid
           FROM Catalog c, Parts p
           WHERE c.pid = p.pid and p.color = 'green'
7. For every supplier who supplies at least 1 red part, print the
   name of the supplier and the total number of red parts that he
   or she supplies.
Solution : SELECT S.sname, COUNT(*) as PartCount
           FROM Suppliers S, Catalog C, Parts P
           WHERE C.sid = S.sid and P.pid = C.pid and P.color = 'Red'
           GROUP BY S.sname, S.sid
8. For each part, find the sname of the supplier who charges the most
   for that part.
Solution : SELECT P.pid, S.sname
           FROM Parts P, Suppliers S, Catalog C
           WHERE C.pid = P.pid
           AND C.sid = S.sid
           AND C.cost = (SELECT MAX (C1.cost)
           FROM Catalog C1
           WHERE C1.pid = P.pid)
9. Find the sids of suppliers who supply only red parts.
Solution : SELECT DISTINCT C.sid
           FROM Catalog C
           WHERE NOT EXISTS ( SELECT *
           FROM Parts P
           WHERE P.pid = C.pid AND P.color <> ‘Red’ )
10. Find the sids of suppliers who supply a red part or a green part.
Solution : SELECT DISTINCT C.sid
           FROM Catalog C, Parts P
           WHERE C.pid = P.pid AND P.color = ‘Red’
           UNION
           SELECT DISTINCT C1.sid
           FROM Catalog C1, Parts P1
           WHERE C1.pid = P1.pid AND P1.color = ‘Green’
11. For every supplier that supplies a green part and a red part,
    print the name and price of the most expensive part that she
    supplies.
Solution : SELECT S.sname, MAX(C.cost) as MaxCost
           FROM Suppliers S, Parts P, Catalog C
           WHERE P.pid = C.pid AND C.sid = S.sid
           GROUP BY S.sname, S.sid
           HAVING ANY ( P.color=’green’ ) AND ANY ( P.color = ’red’ )
12. Find the distinct pnames of all parts.
Solution : SELECT DISTINCT pname
           FROM Parts;
13. Find the distinct pnames of parts for which there is some
    supplier.
Solution : SELECT DISTINCT P.pname
           FROM Parts P, Catalog C
           WHERE P.pid = C.pid;
14. Find the distinct pnames of all parts sold (by some supplier) for
    less than 5.50
Solution : SELECT DISTINCT P.pname
           FROM Parts P, Catalog C
           WHERE P.pid = C.pid
           AND C.cost<5.50;
15. Find the price of the least expensive red part.
Solution : SELECT MIN(C.cost)
           FROM Catalog C, Parts P
           WHERE C.pid = P.pid
           AND P.color = 'Red';
16. For every supplier, print the name of the supplier and the total
    number of parts that he or she supplies.
Solution : SELECT S.sname, COUNT(*) as PartCount
           FROM Suppliers S, Catalog C
           WHERE C.sid = S.sid
           GROUP BY S.sname, S.sid
17. Find the pids of the most expensive parts supplied by suppliers
    named Yosemite Sham.
Solution : SELECT C.pid
           FROM Catalog C, Suppliers S
           WHERE S.sname = ‘Yosemite Sham’ AND 
                             C.sid = S.sid AND
                  C.cost ALL (SELECT C2.cost
                  FROM Catalog C2, Suppliers S2
                  WHERE S2.sname = ‘Yosemite Sham’ AND
                                    C2.sid = S2.sid)
18. For every supplier, print the name and price of the most
    expensive part that she supplies.
Solution : SELECT S.sname, MAX(C.cost) as MaxCost
           FROM Suppliers S, Parts P, Catalog C
           WHERE P.pid = C.pid AND C.sid = S.sid
           GROUP BY S.sname, S.sid

Previous Home Next
The CREATE VIEW Command Questions On SQL – Part 2

     

Incoming search terms:

  • sql queries supplier parts database
  • specify the following query in sql based on given schema supplier (sid sname city ) part(pid pname colour) sp( sid pid quantity)with solution
  • Q2 Consider the following schema Give an expression in the SQL to express each of the following queries: Suppliers(sid sname address) Parts(pid pname color) Catalog(sid pid cost)
  • find names of dealer who supply all parts
  • sql commond for relation suppliers parts catalog
  • find did of supplier who supplieds every parts relational algebra dbms
  • find the sids of suppliers who supply every red or green part
  • suppliers database queries
  • list the name of supplier to whom p0 is given for mouse query
  • my sql database setupConsider the following schema: Suppliers( sid: integer sname: string address: string) Parts(pid: integer pname: string color: string) Catalog( sid: integer pid: integer cost: real) The Catalog relation lists the prices charged for par

Leave a Reply