Questions on Relational Algebra (Part 1)

Consider the relations:

  1. Suppliers(SID, Sname, Rating)
  2. Parts(PID, Pname, Color)
  3. Catalog( SID, PID, Cost)
Query 1: Retrieve SIDs of Suppliers whose rating > 10.
Solution: 
         Retrieve SIDs of Suppliers whose rating > 10
Query 2: Retrieve SIDs of Suppliers who supplied red parts.
Solution: 
          Retrieve SIDs of Suppliers who supplied red parts
Query 3: Retrieve Sname of Suppliers who supplied red parts.
Solution:
           Retrieve Sname of Suppliers who supplied red parts

From optimization point of view 2) & 3) are same, only no. of 
fields(columns) are reduced but rows are same.
The input output cost of 2) & 3) is also same but of 1) is far more.
Query 4: Retrieve SIDs of Suppliers to supply some red part or some
         green part.
Solution: 
         Retrieve SIDs of Suppliers to supply some red part or some green part
Query 5: Retrieve SIDs of Suppliers to supply some red part and some
         green part.
Solution:
         Retrieve SIDs of Suppliers to supply some red part and some green part
Query 6: Retrieve SIDs of Suppliers who supplied atleast 1 part.
Solution:
          Retrieve SIDs of Suppliers who supplied atleast 1 part
Query 7: Retrieve SIDs of Suppliers who supplied atleast two parts.
Solution: Let us consider the Catalog(SID,PID,Cost) relation as :
SID PID Cost
S1 P1
S1 P2
S2 P1
Retrieve SIDs of Suppliers who supplied atleast two parts
Query 8: Retrieve SIDs of Suppliers who supplied atleast three parts.
Solution:
         Retrieve SIDs of Suppliers who supplied atleast three parts
Query 9: Retrieve SIDs of Suppliers who supplied exactly two parts.
Solution:
          Retrieve SIDs of Suppliers who supplied exactly two parts
Query 10: Retrieve SIDs of Suppliers who supplied atmost two parts.
Solution:
          Retrieve SIDs of Suppliers who supplied atmost two parts
Query 11: Retrieve SIDs of Suppliers whose rating >10 and who
          supplied some red part.
Solution:
         Retrieve SIDs of Suppliers whose rating >10 and who supplied some red part.
Query 12: Retrieve SIDs of Suppliers who don't supply any part.
Solution:
         Retrieve SIDs of Suppliers who don't supply any part
Query 13: Retrieve SIDs of Suppliers who supply every part.
Solution:
         Retrieve SIDs of Suppliers who supply every part.
Query 14: Retrieve SIDs of Suppliers who supplied every red part.
Solution:
           Retrieve SIDs of Suppliers who supplied every red part.
Query 15: Retrieve SIDs of Suppliers who supplied every red part or
          green part.
Solution:
          Retrieve SIDs of Suppliers who supplied every red part or green part.
Query 16: Retrieve SIDs of Suppliers who supplied every red part or
          supply every green part.
Solution:
          Retrieve SIDs of Suppliers who supplied every red part or supply every green part.
Query 17: Find the pairs of sids such that the supplier with the
          first sid charges more for some part than the supplier
          with the second sid.
Solution : Let the Catalog(SID,PID,Cost) will be
SID PID Cost
S1 P1 30
S1 P2 20
S2 P1 10

Clearly, in the question, we have

  • SID’s should be different.
  • PID’s should be same.
  • And Cost should be different.
    Find the pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid.
Query 18: Find the pids of parts that are supplied by at least two different suppliers.
Solution: 
         Find the pids of parts that are supplied by at least two different suppliers.
Query 19: Retrieve SIDs of Suppliers who supplied most and least
          expensive part.
Solution : Let the Catalog(SID,PID,Cost) relation will be
SID PID Cost
S1 P1 10
S1 P2 20
S2 P1 30

Steps to Retrieve SID’s of suppliers who supplied most and least expensive part :

  • Retrieve Disqualified sets Firstly :
    1. For Most Expensive Part : SID’s of suppliers who don’t supplied most expensive part.
    2. For Least Expensive Part : SID’s of suppliers who don’t supplied least expensive part.
  • From all the SID’s
    Retrieve SIDs of Suppliers who supplied most and least expensive part.
Retrieve SIDs of Suppliers who supplied most and least expensive part.

Retrieve SIDs of Suppliers who supplied most and least expensive part.
Query 20: Retrieve SIDs of Suppliers who supplied Second most
          expensive part.
Solution :
          Retrieve SIDs of Suppliers who supplied Second most expensive part.

Previous Home Next
Relational Algebra Questions Based on Relational Algebra (Gate Questions Included)

     

Incoming search terms:

  • find sid of suppliers who supply at least two part
  • suppliers parts catalog relattional algebra

Leave a Reply