You are required to create the following tables in a database named SICKLEAVE: EMPLOYEES EMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEY EMPLOYEE_NAME VARCHAR(30) NOT NULL EMPLOYEE_SURNAME VARCHAR(30) NOT NULL DATE_OF_BIRTH DATE NOT NULL DOCTORS DOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEY DOCTOR_NAME VARCHAR(30) NOT NULL EMPLOYEE_SICKLEAVE EMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES EMPLOYEES(EMPLOYEE_ID) START_DATE DATE NOT NULL PRIMARY KEY DOCTOR_ID VARCHAR(5) NOT NULL NUMBER_OF_DAYS SMALLINT NOT NULL (20) Q.1.2 Populate the tables created in Q.1.1 with the following data: EMPLOYEES EMPLOYEE_ ID EMPLOYEE_NAME EMPLOYEE_ SURNAME DATE_OF_BIRTH I0001 Dominique Woolridge 1993-04-19 I0002 Nico Baird 1991-11-19 I0003 Derek Moore 1992-06-24 I0004 Neo Petlele 1993-12-29 I0005 Andrew Crouch 1994-01-30 DOCTORS (10) Question 2 (Marks: 20) Q.2.1 Write a query that will display doctors' names that have not issued any doctor’s certificates to employees yet. Sample Results: DOCTOR_NAME Julia Robbins (5) DOCTOR_ID DOCTOR_NAME D0001 Thabo Ntlali D0002 Deon Coetzee D0003 Kwezi Mbete D0004 Trevor January D0005 Julia Robins EMPLOYEE_SICKLEAVE EMPLOYEE_ID DOCTOR_ID START_DATE NUMBER_OF_DAYS I0001 D0004 2019-01-25 2 I0002 D0001 2019-05-14 1 I0003 D0003 2019-06-07 5 I0003 D0002 2019-06-29 15 I0004 D0001 2019-08-01 3 I0005 D0004 2019-10-22 9 I0005 D0001 2019-12-28 4
You are required to create the following tables in a
EMPLOYEES
EMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEY
EMPLOYEE_NAME VARCHAR(30) NOT NULL
EMPLOYEE_SURNAME VARCHAR(30) NOT NULL
DATE_OF_BIRTH DATE NOT NULL
DOCTORS
DOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEY
DOCTOR_NAME VARCHAR(30) NOT NULL
EMPLOYEE_SICKLEAVE
EMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES
EMPLOYEES(EMPLOYEE_ID)
START_DATE DATE NOT NULL PRIMARY KEY
DOCTOR_ID VARCHAR(5) NOT NULL
NUMBER_OF_DAYS SMALLINT NOT NULL
(20)
Q.1.2 Populate the tables created in Q.1.1 with the following data:
EMPLOYEES
EMPLOYEE_ ID EMPLOYEE_NAME EMPLOYEE_
SURNAME
DATE_OF_BIRTH
I0001 Dominique Woolridge 1993-04-19
I0002 Nico Baird 1991-11-19
I0003 Derek Moore 1992-06-24
I0004 Neo Petlele 1993-12-29
I0005 Andrew Crouch 1994-01-30
DOCTORS
(10)
Question 2 (Marks: 20)
Q.2.1 Write a query that will display doctors' names that have not issued any doctor’s
certificates to employees yet.
Sample Results:
DOCTOR_NAME
Julia Robbins
(5)
DOCTOR_ID DOCTOR_NAME
D0001 Thabo Ntlali
D0002 Deon Coetzee
D0003 Kwezi Mbete
D0004 Trevor January
D0005 Julia Robins
EMPLOYEE_SICKLEAVE
EMPLOYEE_ID DOCTOR_ID START_DATE NUMBER_OF_DAYS
I0001 D0004 2019-01-25 2
I0002 D0001 2019-05-14 1
I0003 D0003 2019-06-07 5
I0003 D0002 2019-06-29 15
I0004 D0001 2019-08-01 3
I0005 D0004 2019-10-22 9
I0005 D0001 2019-12-28 4
Q.1.3 Alter the EMPLOYEES table to add a column as specified below:
EMPLOYEES
AGE SMALLINT
(5)
Q.1.4 Update the contents of the EMPLOYEES table to populate the new AGE field that was added to the
table in Q.1.3. The age should be calculated based on the current date and the date of birth.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 4 images
