Sheet 5 - Problem 5
BME B59 🫡itsLu
sql
a year ago
1.8 kB
33
Indexable
--Creating tables: CREATE TABLE company_mast (COM_ID INT PRIMARY KEY, COM_NAME VARCHAR(30) NOT NULL); CREATE TABLE item_mast (PRO_ID INT PRIMARY KEY, PRO_NAME VARCHAR(30) NOT NULL, PRO_PRICE MONEY NOT NULL, pro_com INT FOREIGN KEY REFERENCES company_mast(COM_ID)); --Populating company_mast table: INSERT INTO company_mast VALUES (11, 'Samsung'); INSERT INTO company_mast VALUES (12, 'iBall'); INSERT INTO company_mast VALUES (13, 'Epsion'); INSERT INTO company_mast VALUES (14, 'Zebronics'); INSERT INTO company_mast VALUES (15, 'Asus'); INSERT INTO company_mast VALUES (16, 'Frontech'); --Populating item_mast table: INSERT INTO item_mast VALUES (101, 'Mother board', 3200, 15); INSERT INTO item_mast VALUES (102, 'Key board', 450, 16); INSERT INTO item_mast VALUES (103, 'ZIP drive', 250, 14); INSERT INTO item_mast VALUES (104, 'Speaker', 550, 16); INSERT INTO item_mast VALUES (105, 'Monitor', 5000, 11); INSERT INTO item_mast VALUES (106, 'DVD drive', 900, 12); INSERT INTO item_mast VALUES (107, 'CD drive', 800, 12); INSERT INTO item_mast VALUES (108, 'Printer', 2600, 13); INSERT INTO item_mast VALUES (109, 'Refill cartidge', 350, 13); INSERT INTO item_mast VALUES (110, 'Mouse', 250, 12); --Query a) Write a SQL query to calculate the average price of all products of the manufacturer which code is 16: SELECT AVG(PRO_PRICE) 'Frontech Average Sales' FROM item_mast WHERE pro_com = 16; --Query b) Write a SQL query to display the name and price of all the items with a price is equal or more than Rs. 250 and the list contain the larger price first and then by name in ascending order: SELECT PRO_NAME 'Product Name', PRO_PRICE 'Product Price' FROM item_mast WHERE PRO_PRICE >= 250 ORDER BY PRO_PRICE DESC, PRO_NAME ASC;
Editor is loading...
Leave a Comment