Question 1) What is the length of values defined for city column. Modify the length of “city” in the table “suppliers”.
Question 2) Are there any constraints defined for the above tables (table suppliers (S), supplier-part (SP), parts? (P) In order to determine, try to search through the USER_CONSTRAINTS table.
Question 3) Add the following constraints for the tables:
i) s# is the primary key for table “S”
ii) s#, p# is the primary key for the table “SP”
iii) p# in table “P” should be unique
iv) the primary key of the “S” table is posted as foreign key in the tables “SP”
v) SNAME in table “S” may not be NULL
v) the columns “qty” in table “SP”, “weight” in table “P” should be larger than 0.
Question 4) Write fitting SQL for the following English queries. Use SPJ datasheet giuven below:
1. Find the names of parts whose weight is between 10 and 15
2. Which supplier supplied what parts to the 'Sorter' job?
3. Which jobs require red parts?
4. Find the names of the suppliers who never supplied anything in 1999
5. Which supplier supplied the most parts ever?
6. What is the total quantity of parts ordered for the 'Sorter' job?
7. What are the parts that 'Adam' ever supplied?
8. What is the most recently shipped job?
9. What is the total weight of a 'Terminal'?
10. Name the supplier which has the lowest status?
11. What is the total weight of all the parts?
12. Name the jobs which had all the parts from the same city?
13. Find the names of the parts required to finish 'Tape' job.
14. Which city had the maximum number of parts originating from it?
