Use SQL to write the following queries: a) Find the last names of students born after January 1, 2000 b) Find the student last names, dates and grades of exams they passed, where a grade was greater than or equal to 8 c) Find the first and last names of students who took to at least one exam from "Computer Science" programme d) List professor IDs of top 5 professors who teach the most courses e) Calculate the average credits for courses in each programme f) Find the surnames of students who have never taken an exam from “Mathematics” programme a) SELECT last_name FROM Student WHERE date_of_birth < '2000-01-01'; (I assume that the type of date of birth in the table student is DATE) b)SELECT Student.last_name, Exam.exam_date, Exam.grade FROM Student JOIN Exam ON Student.student_id = Exam.student_id WHERE Exam.grade >= 8; c)SELECT Student.first_name, Student.last_name FROM Student JOIN Exam ON Student.student_id = Exam.student_id JOIN Course ON Exam.course_id = Course.course_id WHERE Course.programme = 'Computer Science'; d)SELECT professor_id, COUNT(course_id) AS course_count FROM Course GROUP BY professor_id ORDER BY course_count DESC LIMIT 5; e)SELECT programme, SUM(credits) / COUNT(course_id) FROM Course GROUP BY programme; f)SELECT Student.last_name FROM Student LEFT JOIN Exam ON Student.student_id = Exam.student_id LEFT JOIN Course ON Exam.course_id = Course.course_id AND Course.programme = 'Mathematics'
Use SQL to write the following queries:
a) Find the last names of students born after January 1, 2000
b) Find the student last names, dates and grades of exams they passed, where a grade was
greater than or equal to 8
c) Find the first and last names of students who took to at least one exam from "Computer
Science" programme
d) List professor IDs of top 5 professors who teach the most courses
e) Calculate the average credits for courses in each programme
f) Find the surnames of students who have never taken an exam from “Mathematics”
programme
a) SELECT last_name
FROM Student
WHERE date_of_birth < '2000-01-01'; (I assume that the type of date of birth in the table student is DATE)
b)SELECT Student.last_name, Exam.exam_date, Exam.grade
FROM Student
JOIN Exam ON Student.student_id = Exam.student_id
WHERE Exam.grade >= 8;
c)SELECT Student.first_name, Student.last_name
FROM Student
JOIN Exam ON Student.student_id = Exam.student_id
JOIN Course ON Exam.course_id = Course.course_id
WHERE Course.programme = 'Computer Science';
d)SELECT professor_id, COUNT(course_id) AS course_count
FROM Course
GROUP BY professor_id
ORDER BY course_count DESC
LIMIT 5;
e)SELECT programme, SUM(credits) / COUNT(course_id)
FROM Course
GROUP BY programme;
f)SELECT Student.last_name
FROM Student
LEFT JOIN Exam ON Student.student_id = Exam.student_id
LEFT JOIN Course ON Exam.course_id = Course.course_id
AND Course.programme = 'Mathematics'
WHERE Exam.exam_date IS NULL AND Exam.grade IS NULL; are these answers correct?
Step by step
Solved in 3 steps
