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)));
זה תענוג גדול לקרוא את הפוסט שלך. זה מלא במידע. אנו מספקים חנות צעצועים באופן מקוון. למידע נוסף בקר באתר האינטרנט שלנו צעצועים לילדים קטנים למכירה אונליין בישראל
ReplyDeleteYou have done good work by publishing this article here.education toys for sale singapore I found this article too much informative, and also it is beneficial to enhance our knowledge. Grateful to you for sharing an article like this.
ReplyDeleteIt is what I was searching for is really informative. It is a significant and useful article for us. Thankful to you for sharing an article like this.Commercial Indoor Playground Equipment
ReplyDeleteI basically need to say this is an especially educated article which you have shared here about hoodies. It is an interfacing with and beneficial article for us. Continue to give such an information, As a result of you. Kids Toys Online
ReplyDeleteAppreciate yyou blogging this
ReplyDelete