Question
SQL Database Question: Based on the following tables, STAFF, BRANCH , PROPERTY_FOR_SALE and OWNER ; write SQL statements to produce the desired results. Table 1:
SQL Database Question:
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 | |
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started