Database Management System[CS403] Assignment No. 4 i) City_Climate ( City , Clim
ID: 3608038 • Letter: D
Question
Database Management System[CS403]
Assignment No. 4
i) City_Climate (City, Climate, Week)
[This relation shows the climate of the various cities in thefirst, second, third or fourth week of a month]
City
Climate
Week
Lahore
Sunny
first
Karachi
Hazy
second
Islamabad
Rainy
second
ii) Shop_Sales (Shop_num, Shop, Sales,Week)
[This relation shows the Sales of a particular shop in a soppingplaza]
Shop_num
Shop
Sales (in Rs.)
Week
62
Handicrafts
100000
first
41
Furniture
400000
third
32
Handicrafts
150000
fourth
27
Crafts
150000
third
26
Cutlery
50000
second
Write the SQL queries for the following:
a) Set all the values in theClimate column of City_Climate table to ‘Hazy’
b) Insert a City‘Faisalabad’ with ‘Moisty’ climate in thethird week
c) Change the climate ofIslamabad city in the City_Climate table, to Hazy
d) Displays the type(s) ofshop (handicrafts, cutlery, Furniture etc) from the Shop_Saleswhose sum of sales is greater than 20,000
e) Display the number ofdistinct entries in the Shop_Sales with respect to the column‘Shop’
f) Display the namesof the shops which contains the string 'craft', in Shop_Sales
City
Climate
Week
Lahore
Sunny
first
Karachi
Hazy
second
Islamabad
Rainy
second
Explanation / Answer
Dear, a) SQL>Update city_climate Setclimate='Hazy'; b) SQL>Insert into city_climatevalues(‘Faisalabad’,‘Moisty’,'thirdweek'); c) SQL> Update city_climate Set climate='Hazy' where city='Islamabad'; d) SQL>Select distinct shop Fromshop_sales Wheresales>20,000; e) SQL>Select distinct count(Shop) from Shop_Sales; f) SQL> Select distinct shop from Shop_Sales
whereShop like '%Craft%'; I hope this will helpfulfor you..............
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.