SQL Database Question:

Question 1

Based on the following tables, STAFF, BRANCH, PROPERTY_FOR_SALE and OWNER; write SQL statements to produce the desired results.

Table 1: Table STAFF

StaffNo	StaffName	Position	Gender	DateOfBirth	Salary	BranchNo
S1001	Aileen Tan	Manager	Female	5-Sep-55	8000	B001
S1002	Ahmad Ashraff	Sales Assistant	Male	30-Jan-70	1500	B003
S1010	Siti Aishah	Supervisor	Female	23-Dec-68	2500	B003
S1015	Nur Aleeya	Sales Assistant	Female	7-May-80	1200	B002
S1020	Muthusamy	Manager	Male	23-Aug-50	10000	B003
S1030	Edleen	Sales Assistant	Female	9-Jan-75	1350	B001
S1060	Janice Tan	Sales Assistant	Female	11-Oct-79	2000	B005

Table 2: Table BRANCH

BranchNo	Street	City	Pcode	State
B001	Jalan Reko	Kajang	43000	Selangor
B002	Jalan Larkin	Johor Bahru	80350	Johor
B003	Jalan Putra Square 2	Kuantan	25200	Pahang
B004	Jalan Wong Ah Fook	Johor Bahru	80000	Johor
B005	Jalan SS 6/12	Petaling Jaya	47301	Selangor

Table 3: Table PROPERTY_FOR_SALE

PropertyNo	Street	City	Pcode	Type	Rooms	Price	OwnerNo	StaffID	BranchNo
P001	7,BDK 1/15	Kuantan	25200	House	6	400,000	O100	S1015	B003
P002	6,LP 3/2	Johor Bahru	80300	Apartment	4	550,000	O101	S1030	B002
P003	89,Jln Petri	Johor Bahru	80100	House	3	750,000	O102	S1060	B002
P004	15,Jln 2/32	Kuantan	25100	Flat	3	150,000	O103	S1002	B003
P005	12,Jln 5/5	Kajang	43650	House	4	490,000	O101	S1002	B001
P006	37,Jln 40	Kelana Jaya	47300	Flat	3	200,000	O103	S1060	B005

Table 4: Table OWNER

OwnerNo	OwnerName	TelNo	Email
O100	Abu Bakar	012-2213579	A.bakar@outlook.com
O101	Annisa	016-9753111	annisa@outlook.com
O102	Mei Lin	013-7894561	LinM@outlook.com
O103	Meera Kaur	017-3214569	Meera@outlook.com

(A) Give all Sales Assistants a 5% pay increase.

(B) List all managers and supervisors. Sort the query result in descending order by Branch No.

(C) Find the number of Property for each owner.

(D) Display Property No, Type and Price for Annisa.

(E) List Owner Name, Property No, City and Price for property price greater than 500,000.

(F) Display the Name, Position and Salary of staff who earn more than 8,000.