Question

1) Add to a relational table DEPARTMENT information about the total number of employees in each department.

Note, that if a department has not employee then for such a department the total number of employees must be set to zero (0).

The total number of employees must be a positive number no more than 999. Use SELECT statement to list the contents of an extended relational table DEPARTMENT in the descending order of the total number of employees. Finally, remove the new column that added in this subtask.

CREATE TABLE LPOSITION( position VARCHAR(20) NOT NULL, /* Position occupied */ salary_level CHAR NOT NULL, /* Salary level */ CONSTRAINT LPosition_PKey PRIMARY KEY (position) ); /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE DEPARTMENT ( name VARCHAR(50) NOT NULL, /* Department name */ code CHAR(8) NOT NULL, /* Code of department */ head DECIMAL(7), /* Head of department */ budget DECIMAL(10,2), /* Budget of department */ CONSTRAINT DEPARTMENT_PKey PRIMARY KEY (name) ); /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE EMPLOYEE( eno DECIMAL(7) NOT NULL, /* Employee number */ first_name VARCHAR(30) NOT NULL, /* First name */ last_name VARCHAR(30) NOT NULL, /* Last name */ email VARCHAR(50) NOT NULL, /* Email address */ bldg_no DECIMAL(5) NOT NULL, /* Building number of office */ room_no VARCHAR(5) NOT NULL, /* Office room number+suffix */ phone DECIMAL(4) NOT NULL, /* Office phone extension number */ dname VARCHAR(50) NOT NULL, /* Department name */ position VARCHAR(20) NOT NULL, /* Position occupied */ CONSTRAINT EMPLOYEE_PKey PRIMARY KEY(eno), CONSTRAINT EMPLOYEE_CKey1 UNIQUE(email), CONSTRAINT EMPLOYEE_CKey2 UNIQUE(phone), CONSTRAINT EMPLOYEE_FKey1 FOREIGN KEY (dname) REFERENCES DEPARTMENT(name), CONSTRAINT EMPLOYEE_FKey2 FOREIGN KEY (position) REFERENCES LPOSITION(position) ); /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE SUBJECT( code CHAR(7) NOT NULL, /* Subject code */ title VARCHAR(200) NOT NULL, /* Subject title */ credits DECIMAL(2) NOT NULL, /* Credit points */ description VARCHAR(2000) NOT NULL, /* Subject description */ CONSTRAINT SUBJECT_PKey PRIMARY KEY(code) ); /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ /* Association: RUNNING SUBJECT Is-instance-of SUBJECT */ CREATE TABLE RUNNINGSUBJECT( code CHAR(7) NOT NULL, /* Subject code */ rsession VARCHAR(7) NOT NULL, /* Session running */ ryear DECIMAL(4) NOT NULL, /* Year running */ enrolment DECIMAL(3) NOT NULL, /* Total enrolment */ CONSTRAINT RUNNINGSUBJECT_PKey PRIMARY KEY(code, rsession, ryear), CONSTRAINT RUNNINGSUBJECT_FKey1 FOREIGN KEY (code) REFERENCES SUBJECT(code) ); /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE TEACHES( code CHAR(7) NOT NULL, /* Subject code */ rsession VARCHAR(7) NOT NULL, /* Session running */ ryear DECIMAL(4) NOT NULL, /* Year running */ lecturer DECIMAL(7) NOT NULL, /* Lecturer */ CONSTRAINT TEACHES_PKey PRIMARY KEY(code, rsession, ryear, lecturer), CONSTRAINT TEACHES_FKey1 FOREIGN KEY (code, rsession, ryear) REFERENCES RUNNINGSUBJECT(code, rsession, ryear), CONSTRAINT TEACHES_FKey2 FOREIGN KEY (lecturer) REFERENCES EMPLOYEE(eno) );

Answer 1

Query to Add to a relational table DEPARTMENT information about the total number of employees in each department

Solution: ALTER TABLE DEPARTMENT ADD total_number_of_employees(DECIMAL(7)) CHECK (total_number_of_employees>=0 AND total_number_of_empolyees<=999);

Query to list the contents of an extended relational table DEPARTMENT in the descending order of the total number of employees

SELECT * FROM DEPAERTMENT

ORDER BY total_number_of_empolyees DESC;

Query to remove the added column total_number_of_empolyees

ALTER TABLE DEPARTMENT DROP COLUMN total_number_of_employees ;