CS Database Lab Assignment-5 Solution

Description

Create a database name SP. In this database create the following three tables which capture the information related to supplier and parts database.

9. 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‐

9. 1. • phone numbers are 10 digits. E.g. 9898989897

9. 1. • values of sno start with char ‘s’. e.g. ‘s123’,’s534’, etc.

9. 1. • values of pno start with char ‘p’. e.g. ‘p10’,’p23’, etc.

Now write MySQL query to perform each of the followings. You may have to add required data to test your queries.

Marks: (2.5 x 10)=25

1. Display only the numbers from sno.

2. Display the sname with exactly two ‘a’

3. Show sno and pno combination as followings‐ if sno is ‘s123’ and pno is ‘p10’ then display ‘sp12310’

4. Display the sno where the numerical part is a palindrome

5. Display the sno of a given supplier as follows‐ if sno is ‘s123’ then display it as ‘suppl123’

6. Display the phone in xxxxx‐xxxxx format

7. For each sno, generate a key which starts with the last digit of sno, 5th and 8th digits of its phone number and ends with a random number between 0 to 99.

8. Assume that the weight unit in parts table is in ‘gm’. Now display the weight unit in ‘kg’ by rounding off 2 digits.

9. Retrieve the domain name of the email of the suppliers. If the email is abc@gmail.com then retrieve only ‘gmail’.

10. Display a chart that will show the pno and its weight with asterisks (*). For

example: if the weight is any value in {0,1,2,…,9} then use ‘*’, if the weight is any value in {10,11,12,…,19} use ‘**’, if the weight is any value in {20,21,22,…,29 use ‘***’ and so on.

Write all the above MySQL queries (include commands for database creation, database use, table creation, key creation if not done during table creation, tuple insertion).