Sheet 5 - Problem 5

--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;