Question A. 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)); CREATE TABLE COURSE (COURSE_ID CHAR(6), COURSE_TITLE CHAR(20), STAFF_ID CHAR(3), SECTION NUMBER(2)); 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)); Write down a SQL statement for each query below: 1) List the names of all male students who were born before 01-01-1995. [5 marks] 2) List the names of all students whose name is at least 6 characters long and whose birth date falls within 01-01-1995 and 01-01-2000. Order the results alphabetically. [5 marks] 3) List the names of all courses that are neither taught by SMITH nor by JONES. [6 marks] 4) Retrieve the number of courses for each section number. Your output should be in ascending order of section numbers. [6 marks] 5) Find the name of every staff member who teaches the exact same number of courses as his/her boss. [7 marks] 6) List all staff members whose salary is higher than the average salary. [7 marks] 7) Find the names of the staff members who are the boss of some staff member in the Accounting department. [7 marks] 8) Find the average number of courses taught per staff member. [7 marks]
No comments:
Post a Comment