Question A. (50 marks) A database schema consisting of three relations STUDENT, COURSE, and STAFF is created as follows:

[answerhappygod]

Question A 50 Marks A Database Schema Consisting Of Three Relations Student Course And Staff Is Created As Follows 1 (58.3 KiB) Viewed 39 times

Question A 50 Marks A Database Schema Consisting Of Three Relations Student Course And Staff Is Created As Follows 2 (62.36 KiB) Viewed 39 times

Question A. (50 marks) A database schema consisting of three relations STUDENT, COURSE, and STAFF is created as follows: CREATE TABLE STUDENT ( STU_ID CHAR(4), STUDENT_NAME CHAR(20). ADDRESS CHAR(20), BIRTHDATE DATE, GENDER CHAR(6), PRIMARY KEY (STU_ID)); CREATE TABLE STAFF ( STAFF_ID CHAR(3), STAFF NAME CHAR(20), GENDER CHAR(6), DEPARTMENT CHAR(20), BOSS_ID CHAR(3), SALARY NUMBER(8,2), PRIMARY KEY (STAFF_ID), FOREIGN KEY (BOSS_ID) REFERENCES STAFF (STAFF_ID)); CREATE TABLE COURSE ( COURSE_ID CHAR(6), COURSE_TITLE CHAR(20), STAFF_ID CHAR(3), PRIMARY KEY (COURSE_ID), FOREIGN KEY (STAFF_ID) REFERENCES STAFF(STAFF_ID)); CREATE TABLE TAKEN ( COURSE_ID CHAR(6), STU_ID CHAR(4), PRIMARY KEY (STU_ID, COURSE_ID). FOREIGN KEY (STU_ID) REFERENCES STUDENT(STU_ID), FOREIGN KEY (COURSE_ID) REFERENCES COURSE (COURSE_ID));

Write down a SQL statement for each query below: (1) List the names of all female students who were born before 01-10-1995. [5 marks] (2) List the names of all students whose name is at least 8 characters long and order the results alphabetically. [5 marks) (3) List the names of all courses that are taught by the staff members BEAN or SAM. [5 marks] (4) Retrieve the number of courses taught by each staff member. Your output should be in ascending order of staff id. [5 marks] (5) Find the name of every staff member whose salary is higher than his/her boss. [5 marks] (6) List the name of every staff members whose salary is higher than the average salary of all staff. [5 marks) (7) Find the titles of the courses which have been not taken by any students. [5 marks] (8) Find the names of the female staff members who are the boss of some staff member in the 'Engineering' department. [7.5 marks] (9) Find the ID and name of the students who have taken all courses taught by the staff member with ID '001'. [7.5 marks)