Description

Instructions:
• Submit a text file or a pdf file with your answers. Name your file as lastnameFirstnameW4.fileExtension (e.g. chaturvediRituW4.sql or chaturvediRituW4.txt). Hand-written answers will not be accepted.
• Use the same SP database used in lcasss and in lab2.
Questions
1. Identify the errors in the following, describe them (in afew words) and fix them.
1.1: Find all suppliers and the total number of parts they supply.
SELECT sno, COUNT(*) FROM SP;
Your Answer:

1.2: Find sname and the total number of parts they supply.
SELECT sname, COUNT(*)
FROM S, SP GROUP BY sno; Your Answer:

1.3. Find names of suppliers that supply the maximum quantity.
SELECT sname FROM SP, S WHERE S. Sno = SP. Sno
AND qty = MAX (qty);

1.4. Find sno of suppliers who supply more than 2 parts
SELECT sno, COUNT(pno)
FROM SP
WHERE COUNT (pno) > 2
GROUP BY sno;

A non-correlated subquery is one that uses an IN or NOT In to test if the values returned in the subquery are members of a set or not. Typically an inner query is first preocessed by the DBMS, then the results of the inner query are used by the outer query. A correlated subquery is one in which the inner query relies on the outer query before it can be processed.
2a. SELECT SNAME
FROM S
WHERE NOT EXISTS (SELECT *
FROM SP
WHERE S.SNO=SP.SNO AND SP.PNO = ‘P2’) Your Answer:

2b. SELECT sname
FROM S
WHERE sno IN (’S2’, ’S3’); Your Answer:

2c. SELECT sname
FROM S
WHERE sno IN (SELECT sno FROM SP
WHERE pno IN (’P2’, ’P3’)); Your Answer:

2d. SELECT sname
FROM S
WHERE NOT EXISTS (SELECT *
FROM SP SP1
WHERE SNO = ’S2’
AND NOT EXISTS (SELECT *
FROM SP SP2
WHERE SP1.PNO = SP2.PNO Your Answer:

3. Answer the following questions in context with the create_sp.sql and insert_sp.sql scripts used in lab2. These scripts allow you to create and populate the tables S, P and SP. After downloading and running the scripts on postgres,
3a. Write the command in postgres to find the tables that you just created?
(similar to ls on linux)
3b. Which schema did you create the tables in?
3c. Write command (s) in postgres to display the structure of tables S, P and SP.
3d. Write command (s) in postgres to see the rows of tables S, P and SP. 3e. Write command to use a linux command on postgres, such as clear.
Your Answer: