You are requested to write the SQL commands to perform the following operations

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

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

Explanation / Answer

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))
;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.