Question

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 SQL statement for each query below:

1) Find out the information of staff members who are female and earn either below $5,000 or above $30,000. [5 marks] 2) List all staff members who are not in the Accounting nor the History department. [5 marks] 3) List all students whose name contains the substring "JONES". List the females before the males in chronological order (by birthdate). [6 marks] 4) List all the courses taught by Raymond J. JOHNSON. [6 marks] 5) Find the names of all staff members who earn more than their bosses. [8 marks] 6) List all staff members who are either in the same department as Amy Dancer or Jack Nelson. [8 marks] 7) Find the names of the staff members who make more money than every member of the Accounting department. [7 marks] 8) Find the average salary for each department with more than one staff member. [5 marks]

Answer 1

Question 1:

SELECT * FROM STAFF WHERE GENDER="Female" AND (SALARY NUMBER<5000 OR SALARY NUMBER>30000).

Here, assuming the salary number entries are in dollars and gender entries are either Male or Female, the query is as above. If the entries are different you can modify it as per the entries. But the basic structure is the same.

Question 2:

SELECT STAFF_NAME FROM STAFF WHERE NOT DEPARTMENT="Accounting" AND NOT DEPARTMENT="History"

Assuming to print the staff names, the query is as stated above. If you want the complete details of the staff please use * instead of staff_name.

Question 3:

SELECT STUDENT_NAME FROM STUDENT WHERE STUDENT_NAME LIKE '%JONES%' GROUP BY GENDER ORDER BY BIRTHDATE

This query prints all the student names having jones as a substring and females first and males next by ordering the birthdays in ascending order or chronological order.

Question 4:

SELECT COURSE_TITLE FROM COURSE WHERE STAFF_ID IN (SELECT STAFF_ID FROM STAFF WHERE STAFF_NAME="Raymond J.JOHNSON")

Question 5:

Information is not sufficient. Boss salary is not obtained from the above 3 tables.

Question 6:

SELECT STAFF_NAME FROM STAFF WHERE DEPARTMENT IN (SELECT DEPARTMENT FROM STAFF WHERE STAFF_NAME="Amy Dancer" OR STAFF_NAME = "Jack Nelson")

Question 7:

SELECT STAFF_NAME FROM STAFF WHERE SALARY NUMBER > ALL(SELECT SALARY NUMBER FROM STAFF WHERE DEPARTMENT="Accounting")

Question 8:

SELECT DEPARTMENT, avg(SALARY NUMBER) as AverageSalary FROM STAFF GROUP BY DEPARTMENT HAVING count(STAFF_ID)>1

Please feel free to comment in the comment section for any clarifications.