Description
Create a database name SP. In this database create the following three tables which capture the information related to supplier and parts database.
-
supplier<sno,sname,city,phone,email> /*sno is the primary key*/ II. parts<pno,pname,weight,color> /*pno is the primary key*/
III. sp<sno,pno ,qty> /*sno, pno combination is the primary key. Also, sno is a foreign key which refers to supplier.sno. Again, pno is also a foreign key which refers to parts.pno.*/
Make the following assumptions while creating the tables‐
-
phone numbers are 10 digits. E.g. 9898989897
-
values of sno start with char ‘s’. e.g. ‘s123’,’s534’, etc.
-
values of pno start with char ‘p’. e.g. ‘p10’,’p23’, etc.
In your sp database add one dos (date of supply) attribute in the sp table, add one (date of manufacture) in parts table and dob (date of birth) attribute in supplier table. Thus the new schema will be as follows‐
I. supplier<sno, sname, city, phone, email, dob>
-
parts<pno, pname, weight, color, dom> III. sp<sno, pno, qty, dos>
Now write MySQL query to perform each of the followings. You may have to add required data to test your queries.
1) Find the name of the youngest supplier.
2) Find the details of the suppliers who are more than 30 years old.
3) Show the average age of the suppliers from each city.
4) Find the name of the suppliers who have supplied parts in the year 2012.
5) Find the name of the suppliers who have supplied parts after 1st Feb‐2015.
6) Find the name of the most recently supplied parts.
7) Find the name of the suppliers who have supplied atleast two parts and one part is supplied within 30 days of supply of previous part.
8) If the warranty period is 3 months from the date of supply then show the warranty expiry date of each items supplied.
9) If the warranty period is 3 months from the date of supply then show the part no. (supplied by ‘s123’) that are still under warranty.
10) Show the name of the suppliers who have supplied more than 6 months old items.
