CMPS 342   Labs

Goal :  Given following relational schemes, express the queries listed
below in relational algebra, tuple and domain relational calculus.

Database :  Relational database (relation instances)
		S (snum, sname, status, city)
		P (pnum, pname, color, weight, city)
		SP(snum, pnum, qty)

where table S contains suppliers' information, P contains parts
information and SP tells who supplies which part by how many.


Queries:

1. Get all supplier names, quantity who have shipments for part with part
number equal to 'p2', and sort them by quantity and sname of supplement
(use descending order).

2. Print all supplier's names with the status who have status larger than
or equal to the status of supplier with snum = 's2', excluding s2.

3. Find all part's names with red color and heavier than part whose part
number is 'p2'. The heading of the output must be "Hvy_Red_Part".

4. Find parts whose colors begin with either "R" or "B", and if the color
begins with "B", the third letter must be "u".

5. Get supplier names who supply at least one part which is heavier than all red parts.

6. Find all supplier names who have shipments for all blue parts.

7.Get all pairs of supplier names such that two suppliers are located in the
same city; no pair should have the same names.

8 Use aggregate function MAX find the parts with the heaviest weight.

9. Find the parts with the lightest weight, and the output must be
"The lightest Part is PARTNAME with weight = actual_wright". Note that the
part name is in capital case.

10. Find supplier(s) who supply all blue parts.

11. Find supplier(s) who supply only blue parts.

11. Find supplier(s) who supply all blue parts but no other colored parts.

12. Find the suppliers with the second largest status.

13. Add a tuple (s1, p2, 500) into relation sp. Show you result after the insertion.

14. Change the status of s2 to 100. Show your result.

15. Delete the tuple added in 11. Change status of s2 to 10.