# MCS-023 SOLVED ASSIGNMENT 2018-19

Question 4: FREE IGNOU SOLUTION MCS-023 SOLVED ASSIGNMENT 2018-19 (15X1=15 Marks)
Consider the following relations:
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
Answer the following simple queries in SQL.
a) Find name of supplier for city = “MUMBAI”.
c) Find all suppliers whose status is 10, 20 or 30.
d) Find total number of city of all suppliers.
e) Find s# of supplier who supplies ‘BLUE’ part.
f) Count number of supplier who supplies ‘BLUE’ part.
g) Sort the supplier table by sname.
h) Delete records in supplier table whose status is 40.
i) Find name of parts whose color is ‘red’
j) Find parts name whose weight less than 10 kg.
k) Find all parts whose weight from 10 to 20 kg.
l) Find average weight of all parts.
m) Find S# of supplier who supply part ‘p2’
n) Find name of supplier who supply maximum parts.
o) Sort the parts table by pname.

Question 5: FREE IGNOU SOLUTION MCS-023 SOLVED ASSIGNMENT 2018-19 (10 Marks)
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.
b) Find all the Supplier details who supplies police_car toy.
c) Write a SQL statement to insert a new product with pid=-1, name=’my
product’, and min_age=3 into the Product table.
d) List the ids and names of all products whose inventory is below 10.
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.
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.
g) List the ids and names of all products for whom there is only one 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.
i) List the id and name of each supplier along with the total number of products
it supplies.
j) Find the id and name of the manufacturer who produces toys on average for
the youngest children.

### 1 Response

1. Akash Kumar says:

Assignment MCS-023 2018, ANSWER to question 4 and 5 are missing.
Make it available as soon as possible.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

error: Content is protected !!