Question

You will need to refer to the University database to answer this question. Write SQL queries to perform the following operations. each case show the SQL statement you used and the output from MySQL. You are required to include screenshots showing the actu output of your queries. The tables of University database are as follows: region (region_number, address, telephone, email _address) staff (staff number, name, address, email_address, telephone,

Answer 1

If you have any doubts, please give me comment...

-- e)

SELECT student_id, course_code

FROM examination E

WHERE NOT EXISTS(

SELECT student_id

FROM assignment A

WHERE E.student_id=A.student_id AND E.course_code = A.course_code

);

-- f)

SELECT S.student_id, name, mark

FROM student S, assignment A

WHERE S.student_id = A.student_id AND A.mark = (

SELECT MAX(mark)

FROM assignment

);

-- g)

SELECT R.region_number, telephone, COUNT(*) AS no_of_students

FROM region R, student S

WHERE R.region_number = S.region_number

GROUP BY region_number, telephone

HAVING COUNT(*)<5;

-- h)

CREATE TABLE Faculty(

Staff_number CHAR(4) NOT NULL PRIMARY KEY,

Faculty_name VARCHAR(40),

Address VARCHAR(120),

Email_address VARCHAR(40),

Telephone VARCHAR(15)

);