Consider a toy-store database has the following schema: Product(pid: integer, name: varchar(20), min_age: integer) Manufacturer(mid: integer, name: varchar(20), address: varchar(50)) Supplier(sid: integer, name: varchar(20), address: varchar(50)) Inventory(pid:integer, stock: integer) Manufactures(mid:integer, pid: integer) Supplies(sid: integer, pid: integer)

Write and run the following SQL queries on the tables:

a) Find all the product_id’s and names whose manufacturer is LEO company.

ans. select pid, name from product where pid in(select pid from manufactures where mid in(select mid from manufacturer where name=’leo’) );

b) Find all the Supplier details who supplies police_car toy.

Ans. select name from supplier where sid in(select sid from supplies where pid=(select pid from product where nae=’police car’));

c) Write a SQL statement to insert a new product with pid=-1, name='my product', and min_age=3 into       the Product table.

ans. insert into product values(1,’my product’,3);

d) List the ids and names of all products whose inventory is below 10.

ans. select pid , name from product where pid in(select pid from inventory where stock<30);

e) List the ids and names of all suppliers for products manufactured by "TRIKA". The id and name of each supplier should appear only once.

ans. select distinct(sid),distinct (name) from from supplier where sid in(select sid from supplies where pid in(select pid from manufactures where mid in(select mid from manufacturer where name=’trika’)));

f) 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.

Ans. selct pid,name,stock from product ,inventory where product.pid=inventory.pid order by stock desc;

g) List the ids and names of all products for whom there is only one supplier.

Ans. Select pid , name from product where pid in(select pid from supplies where sid in(select distinct (sid)from supplier);

h) Find the ids and names of the products with the lowest inventory. Do NOT assume these are always products with an inventory of zero.

Ans. Select pid, name from product and supplier where product.pid=supplier.pid having min(stock);

i) List the id and name of each supplier along with the total number of products it supplies.

Ans. Select id ,name, count(pid) from supplier ,product;

j) Find the id and name of the manufacturer who produces toys on average for the youngest children.

Ans. Select mid,name,  from manufacturer where mid=(select mid from manufacturer where pid =(select pid from product having avg(min_age)));