Consider the following relations MobileMaster & MobileStock:- Display the Mobile company, Mobile name

Question

Consider the following relations MobileMaster & MobileStock:-

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;

(viii) SELECT AVG(M_Price) FROM MobileMaster;

Answer 1

(i) SELECT M_Compnay, M_Name, M_Price FROM MobileMaster ORDER BY M_Mf_Date DESC;

(ii) SELECT * FROM MobileMaster WHERE M_Name LIKE 'S%';

(iii) SELECT M_Supplier, M_Qty FROM MobileStock WHERE M_Id <> 'MB003';

(iv) SELECT M_Company FROM MobileMaster WHERE M_Price BETWEEN 3000 AND 5000;

(v)

(vi)

(vii)

M_Id	M_Name	M_Qty	M_Supplier
MB004	Unite3	450	New_Vision
MB001	Galaxy	300	Classic Mobile Store

(viii) 5450