Scribd
Upload
7 views2 pages

Database Queries for Student Analysis

The document outlines an in-class assignment for CS486 - Introduction to Databases, led by Dr. Lê Thị Nhàn and supported by TAs and lab instructors. It includes a series of SQL queries to be executed on a University database, focusing on student participation, course management, and performance metrics. The tasks require retrieving various data points such as student codes, course details, and departmental statistics using SQL commands.

Uploaded by

Nguyễn Đức Duy
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
7 views2 pages

Database Queries for Student Analysis

The document outlines an in-class assignment for CS486 - Introduction to Databases, led by Dr. Lê Thị Nhàn and supported by TAs and lab instructors. It includes a series of SQL queries to be executed on a University database, focusing on student participation, course management, and performance metrics. The tasks require retrieving various data points such as student codes, course details, and departmental statistics using SQL commands.

Uploaded by

Nguyễn Đức Duy
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