Page 45 - C++

(1 Mark for correct definition of DDL & DML commands)
(½ Mark each for correct identification of commands)

(b) Consider the following relations MobileMaster & MobileStock:- 6
MobileMaster

M_Id M_Company M_Name M_Price M_Mf_Date

MB001 Samsung Galaxy 4500 2013-02-12

MB003 Nokia N1100 2250 2011-04-15

MB004 Micromax Unite3 4500 2016-10-17
MB005 Sony XperiaM 7500 2017-11-20

MB006 Oppo SelfieEx 8500 2010-08-21

MobileStock

S_Id M_Id M_Qty M_Supplier

S001 MB004 450 New Vision

S002 MB003 250 Praveen Gallery

S003 MB001 300 Classic Mobile Store
S004 MB006 150 A-one Mobiles

S005 MB003 150 The Mobile

S006 MB006 50 Mobile Centre

Write the SQL query for questions from (i) to (iv) & write the output of SQL command

for questions from (v) to (viii) given below:-
(i) Display the Mobile company, Mobile name & price in descending order of

their manufacturing date.

(ii) List the details of mobile whose name starts with „S‟.
(iii) Display the Mobile supplier & quantity of all mobiles except „MB003‟.

(iv) To display the name of mobile company having price between 3000 & 5000.
(v) SELECT M_Id, SUM(M_Qty) FROM MobileStock GROUP BY M_Id;

(vi) SELECT MAX(M_Mf_Date), MIN(M_Mf_Date) FROM MobileMaster;
(vii) SELECT M1.M_Id, M1.M_Name, M2.M_Qty, M2.M_Supplier FROM

MobileMaster M1, MobileStock M2 WHERE M1.M_Id=M2.M_Id AND

M2.M_Qty>=300;

Page No. 28