You are requested to write the SQL commands to perform the following operations
ID: 3792834 • Letter: Y
Question
You are requested to write the SQL commands to perform the following operations on the tables in the CONSTRUCTION database.
TABLES IN THE CONSTRUCTION DATABASE
WORKER Table
Primary key: WORKER_ID
Foreign keys: none
WORKER_ID
WORKER_NAME
HRLY_RATE
SKILL_TYPE
1235
Faraday
12.50
Electric
1412
Nemo
13.75
Plumbing
2920
Garret
10.00
Roofing
3231
Mason
17.40
Framing
- etc.-
ASSIGNMENT Table
Primary key: WK_ID + BLDG_ID + START_DATE
Foreign keys: WK_ID (references the WORKER table)
BLDG_ID (references the BUILDING table)
WK_ID
BLDG_ID
START_DATE
NUM_DAYS
1235
321
2016-10-10
5
1412
321
2016-10-01
10
1235
515
2016-10-17
22
2920
460
2016-12-09
18
- etc.-
BUILDING Table
Primary key: BLDG_ID
Foreign keys: none
BLDG_ID
BLDG_ADDRESS
TYPE
QLTY_LEVEL
STATUS
321
123 Elm
Office
2
2
435
456 Maple
Retail
1
1
515
789 Oak
Residence
3
1
460
1011 Birch
Office
2
3
- etc.-
Which buildings have the most workers assigned for each type of building? List the building address and the safety level.
WORKER_ID
WORKER_NAME
HRLY_RATE
SKILL_TYPE
1235
Faraday
12.50
Electric
1412
Nemo
13.75
Plumbing
2920
Garret
10.00
Roofing
3231
Mason
17.40
Framing
- etc.-
Explanation / Answer
SQL Query : SELECT BUILDING.BLDGID,BUILDING.TYPE,BUILDING.BLDG_ADDRESS,BUILDING.QLTY_LEVEL,count(ASSIGNMENT.BLDG_ID) as number_of_workers from BUILDING left join ASSIGNMENT on (BUILDING.BLDG_ID = ASSIGNMENT.BLDG_ID) group by BUILDING.BLDG_ID
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.