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.-
Quality Level is a major concern in building construction. List the number of buildings that have a quality level of 2 or below and the names of employees working at these buildings.
| 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
CREATE TABLE WORKER
(
WORKER_ID NUMBER(5) NOT NULL,
WORKER_NAME VARCHAR2(100),
HRLY_RATE NUMBER(4,2),
SKILL_TYPE VARCHAR2(50),
CONSTRAINT WORKER_PK PRIMARY KEY (WORKER_ID)
);
CREATE TABLE BUILDING
(
BLDG_ID NUMBER(4),
BLDG_ADDRESS VARCHAR2(200),
TYPE VARCHAR2(50),
QLTY_LEVEL INT,
STATUS INT,
CONSTRAINT BUILDING_PK PRIMARY KEY (BLDG_ID)
);
CREATE TABLE ASSIGNMENT
(
WK_ID NUMBER(5),
BLDG_ID NUMBER(4),
START_DATE DATE,
NUM_DAYS INT,
CONSTRAINT ASSIGNMENT_PK PRIMARY KEY (WK_ID,BLDG_ID,START_DATE),
CONSTRAINT FK_WORKER
FOREIGN KEY (WK_ID)
REFERENCES WORKER(WORKER_ID),
CONSTRAINT FK_BUILDING
FOREIGN KEY (BLDG_ID)
REFERENCES BUILDING(BLDG_ID)
);
SELECT COUNT(B.BLDG_ID), W.WORKER_NAME
FROM
BUILDING B, WORKER W
WHERE WORKER.WORKER_ID IN (SELECT WK_ID FROM ASSIGNMENT WHERE B.BLDG_ID IN
(SELECT BLDG_ID FROM BUILDING WHERE QLTY_LEVEL < 3))
;
