Answer the following queries using SQL for the database created in Question I and Question 2

Question

Answer the following queries using SQL for the database created in Question I and Question 2

List the names of all the students of the Programme whose programme code is CMAD.

(i) List the Programmes, which has no student. (i) Find the list of students who have been admitted to more than one Programme during the

current academic year. (A student must have a unique ID)

(iv) List the user id of the student whose unique ID is "A001"

(v) List the students who have not passed all the courses of their programme. (vi) Find the student who has secured the highest total marks in the BCA programme

Answer 1

1. SELECT name FROM Student WHERE programme_code = 'CMAD';

2. SELECT Programme.name FROM Programme LEFT JOIN Student ON Programme.code = Student.programme_code WHERE Student.unique_id IS NULL;

3. SELECT unique_id, COUNT(programme_code) FROM Student GROUP BY unique_id HAVING COUNT(programme_code) > 1;

4. SELECT user_id FROM Student WHERE unique_id = 'A001';

5. SELECT Student.name FROM Student LEFT JOIN Enrolment ON Student.unique_id = Enrolment.unique_id WHERE Enrolment.grade < 50;

6. SELECT Student.name FROM Student JOIN Enrolment ON Student.unique_id = Enrolment.unique_id WHERE Enrolment.programme_code = 'BCA' GROUP BY Student.unique_id ORDER BY SUM(Enrolment.grade) DESC LIMIT 1;