Scribd
Upload
6 views2 pages

(IC) Topic03 ComplexSQLQueries

The document outlines an in-class assignment for the CS486 Introduction to Databases course, led by Lê Thị Nhàn, PhD, with assistance from TAs and lab instructors. Students are required to create a University database and write various SQL queries to retrieve specific data about students, courses, and instructors. The tasks include retrieving student codes, course participation, and top-performing students based on grades across different criteria.

Uploaded by

trantrongnguyen.mx
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views2 pages

(IC) Topic03 ComplexSQLQueries

The document outlines an in-class assignment for the CS486 Introduction to Databases course, led by Lê Thị Nhàn, PhD, with assistance from TAs and lab instructors. Students are required to create a University database and write various SQL queries to retrieve specific data about students, courses, and instructors. The tasks include retrieving student codes, course participation, and top-performing students based on grades across different criteria.

Uploaded by

trantrongnguyen.mx
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CS486 – Introduction to Databases

​ Lecturer: ​ Lê Thị Nhàn, PhD.


​ TA: ​ Phan Thị Phương Uyên, MSc.
​ Lab instructor:​ Nguyễn Ngọc Toàn, MSc.
​ ​ Nguyễn Ngọc Minh Châu, MSc.
​ ​
In-class assignment
Task 1: Use University database (run UniversityDB_script file on moodle to create the database).
Utilize the SQL editor of SQL Management Studio or Azure Data Studio to write the following
queries:
1.​ Retrieve the codes of all students, along with the number of courses they have participated
in.
2.​ Retrieve the codes and names of students who have passed more than two courses.
3.​ Retrieve the school years and semesters, along with the number of courses held in each
semester of every year.
4.​ Retrieve the codes and names of courses with the highest number of instructors who have
taught those courses.
5.​ Retrieve the codes and names of instructors who have only taught courses managed by
their own department.
6.​ Retrieve the codes and names of students who have participated in at least one course
managed by a department they do not belong to, or who have the highest grade in the
course ‘Databases’ during the Spring semester of 2022.
7.​ Retrieve the code and name of ALL students, along with the count of courses managed by
each department that they have participated in.
8.​ Find the top scoring student in each section. Print all top students with their IDs if their
scores are tied.
9.​ Find the names of students who took a course from a department different from their own.
10.​Using CTE, for each department, find all the top 2 students based on the average grade.
Include all the students that tied for the top 2.

You might also like