MySQL DBMS: Show a screenshot when completed For each of the following English d

Question

MySQL DBMS: Show a screenshot when completed

For each of the following English descriptions, provide a valid SQL statement and show the evaluated result (command line or screen capture). Views are not allowed and the SQL should be a single statement. (a) Get project numbers and names for projects supplied by supplier S2. (b) Get the full details for parts supplied by a supplier in London. (c) Get all shipments where the quantity is in the range 300 to 750 inclusive. (d) Get part numbers for parts supplied by a supplier in London to a project in London. (e) Get all supplier-number/part-number/project number triples such that the indicated 1. supplier, part and project are collocated (f) Get the triples in (e) that are not collocated. (g) For each part being supplied to a project, get the part number, project number and the total quantity. (h) Get part numbers for those parts supplied to all projects in London. Given the English description: For parts that are supplied to some projects in average quantity of more than 350, show part numbers and part names (popular bulk parts) (a) Write a valid SQL statement (b) Define a view that completes this description and show the result: For parts that are 2. supplied to some projects in average quantity of less than 400. (c) Prove that the view has been created in the system catalog (hint: SQL on information_schema again) 3. Do the following and prove that it was successful: (a) Create an index with UNIQUE option on JNAME of the PROJECT table. (b) Create an index with UNIQUE pltion on PNAME of the PART table. (c) Insert in the SPJ table this tuple: (S5, P6, J9, NULL)

Explanation / Answer

Answer is as follows :

As per Chegg Guidelines, We are allowed to answer only first four question of Part A, So please repost others.

Part A :

a)

SELECT JNO, JNAME FROM Project JOIN SPJ ON Project.JNO = SPJ.JNO WHERE SNO = "S2" ;

Join is used to add two tables project and SPJ and where clause select the supplier S2

b)

SELECT PNO,Pname, Color, Weight, City FROM Parts JOIN SPJ ON Parts.PNO = SPJ.PNO JOIN Supplier ON SPJ.SNO = Supplier.SNO WHERE Supplier.City = "London" ;

Here we need to join three tables i.e. Parts, SPJ and Supplier and Where clasue is used to select london city from supplier table

c)

SELECT * FROM SPJ WHERE QTY BETWEEN 300 AND 750 ;

the query select all the shiplment details from SPJ table and between is used to find qty from 300 to 750.

d)

SELECT PNO FROM Parts JOIN SPJ ON Parts.PNO = SPJ.PNO JOIN Supplier ON SPJ.SNO = Supplier.SNO JOIN Project ON SPJ.JNO = Project.JNO WHERE Supplier.City = "London" AND Project.City = "London" ;

Here we need to join four tables i.e. Parts, SPJ ,Project and Supplier and Where clasue is used to select london city from supplier table and project table.

if there is any query please ask in comments...

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.