Homework SourseYou 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.-
1.List the number of workers assigned to each building.
| 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
The Idea is to use a group by clause to solve the problem.
GROUP BY Clause: The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns.
So for our problem to list the number of workers in a building, we can apply group by clause on BLDG_ID of ASSIGNMENT TABLE. The SQL Query for the answer is
SELECT BLDG_ID, COUNT(WK_ID) FROM ASSIGNMENT GROUP BY(BLDG_ID);
Below are the queries for SQL table creation,insertion
CREATE TABLE WORKER(WORKER_ID int,
WORKER_NAME VARCHAR(20),
PRIMARY KEY(WORKER_ID));
CREATE TABLE BUILDING(BLDG_ID int,
BLDG_ADDRESS VARCHAR(20),
PRIMARY KEY(BLDG_ID));
CREATE TABLE ASSIGNMENT(BLDG_ID int,
WK_ID int,
CONSTRAINT fk_1 FOREIGN KEY(BLDG_ID)
REFERENCES BUILDING(BLDG_ID),
CONSTRAINT fk_2 FOREIGN KEY(WK_ID)
REFERENCES WORKER(WORKER_ID)
);
INSERT INTO BUILDING VALUES(321,\'BTP\');
INSERT INTO BUILDING VALUES(435,\'WTC\');
INSERT INTO BUILDING VALUES(515,\'ETV\');
INSERT INTO BUILDING VALUES(460,\'PQR\');
INSERT INTO WORKER VALUES(1235,\'Tom\');
INSERT INTO WORKER VALUES(1412,\'Lucy\');
INSERT INTO WORKER VALUES(2920,\'Frank\');
INSERT INTO WORKER VALUES(3231,\'Jane\');
INSERT INTO WORKER VALUES(1431,\'Robert\');
INSERT INTO ASSIGNMENT VALUES(321,1235);
INSERT INTO ASSIGNMENT VALUES(321,1412);
INSERT INTO ASSIGNMENT VALUES(515,1235);
INSERT INTO ASSIGNMENT VALUES(460,2920);
