SQL Assignment PDF

Preview

Summary

SQL Assignment...

Description

SQL Assignment Consider the relational schema having following tables: i. ii. iii. iv. v. vi.

Product Manufacturer Supplier Inventory Manufactures Supplies

Design their attributes and write the queries in SQL.

Values inserted in Product table

Values inserted in Supplier table

Values inserted in Manufacturer table

Values inserted in Inventory table

Values inserted in Supplies table

Values inserted in Manufactures table

Describing Tables

1. List the ids and names of all products whose inventory is below 5.

Syntax:- select table1.attribute1,attribute2 from table1,table2 where table1.attribute = table2.attribute and table2.attribute < 5; Query:- select product.p_ID,name from product,inventory where product.p_ID = inventory.p_ID and inventory.quant < 5;

2. List the ids and names of all suppliers for products manufactured by “202”. The id and name of each supplier should appear only once. Syntax:- select distinct(table1.attribute1), table1.attribute2 from table1,table2,table3 where table2.attribute= ‘ value’ and table2.attribute = table3.attribute and table1.attribute = table2.attribute; Query:- select distinct(supplier.s_ID), supplier.name from supplier,supplies,manufactures where manufactures.m_ID=202 and supplies.p_ID = manufactures.p_ID and supplier.s_ID = supplies.s_ID;

3. List the ids, names and number in stock of all products in inventory. Order the list by decreasing number in stock and decreasing product ids. Syntax:- select table1_name.attribute1,attribute2,attribute3 from table1 as alias1_name, table2_name as alias2_name where alias1_name.attribute=alias2_name.attribute order by alias1_name.attribute desc, alias2_name.atrribute desc; Query:- select p.p_ID,name,quant from product as p, inventory as I where p.p_ID=i.p_ID order by p.p_ID desc, i.quant desc;

4. List the ids and names of all products for whom there is only one supplier. Syntax:- select distinct(table1.attribute1), table1.attribute2 from table1,table2 where table1.attribute=table2.attribute group by table1.attribute having count(table1.attribute) = 1; Query:- select distinct(product.p_ID), product.name from supplies,product where supplies.p_ID = product.p_ID group by product.p_ID having count(supplies.p_ID) = 1;

5. Find the ids and names of the products with the lowest inventory. DO NOT assume these are always products with an inventory of zero. Syntax:- select table1.attribute1,attribute2 from table1,table2 where table1.attribute = table2.attribute and table2.attribute = (select min(attribute) from table2); Query:- select product.p_ID,name from product,inventory where product.p_ID = inventory.p_ID and inventory.quant= (select min(quant) from inventory);

6. List the id and name of each supplier along with the total number of products it supplies. Syntax:- select table1.attribute1, table1.attribute2, count(table1.attribute) from table1,table2 where table1.attribute= table2.attribute group by table1.attribute;

Query:- select supplier.s_ID, supplier.name, count(supplies.p_ID) from supplier, supplies where supplier.s_ID = supplies.s_ID group by supplier.s_ID;

7. Find the id and name of the manufacturer who produces toys on average for the youngest children. Syntax:- select distinct(table1.attribute1), table1.attribute2, table2.attribute1, table2.attribute2 from table1,table2,table3 where table3.attribute = table2.attribute and table2.attribute = table1.attribute and table3.attribute1= (select min(attribute1) from table3); Query:- select distinct(manufacturer.m_ID), manufacturer.M_name, product.name,product.p_ID from manufacturer, manufactures,product where product.p_ID = manufactures.p_ID and manufactures.m_ID = manufacturer.m_ID and product.min_age= (select min(min_age) from product);...