You are requested to write the SQL commands to perform the f

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

Solution

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