World's most popular travel blog for travel bloggers.

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)

, , 5 comments

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)));

5 comments:

  1. זה תענוג גדול לקרוא את הפוסט שלך. זה מלא במידע. אנו מספקים חנות צעצועים באופן מקוון. למידע נוסף בקר באתר האינטרנט שלנו צעצועים לילדים קטנים למכירה אונליין בישראל

    ReplyDelete
  2. You 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.

    ReplyDelete
  3. It 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

    ReplyDelete
  4. I 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

    ReplyDelete

Let us know your responses and feedback