Sheet 4 - Problem 2

--Creating tables:
CREATE TABLE company_mast (COM_ID INT PRIMARY KEY NOT NULL, COM_NAME VARCHAR(30));
CREATE TABLE item_mast (PRO_ID INT PRIMARY KEY NOT NULL, PRO_NAME VARCHAR(30), PRO_PRICE MONEY, pro_com INT FOREIGN KEY REFERENCES company_mast(COM_ID));

--Populating company_mast table:
INSERT INTO company_mast VALUES (11, 'Samsung'), 
								(12, 'iBall'), 
								(13, 'Epsion'),
								(14, 'Zebronics'),
								(15, 'Asus'),
								(16, 'Frontech');

--Populating item_mast table:
INSERT INTO item_mast VALUES (101, 'Mother board', 3200, 15),
							(102, 'Key board', 450, 16),
							(103, 'ZIP drive', 250, 14),
							(104, 'Speaker', 550, 16),
							(105, 'Monitor', 5000, 11),
							(106, 'DVD drive', 900, 12),
							(107, 'CD drive', 800, 12),
							(108, 'Printer', 2600, 13),
							(109, 'Refil cartidge', 350, 13),
							(110, 'Mouse', 250, 12);

--Query a) 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, PRO_PRICE FROM item_mast WHERE PRO_PRICE >= 250 ORDER BY PRO_PRICE DESC, PRO_NAME ASC;