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

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

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

Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 4 images

Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education