Scribd
Upload
2 views2 pages

(IC) Topic02 BasicSQLQueries

The document outlines an in-class assignment for the CS486 Introduction to Databases course, led by Dr. Lê Thị Nhàn and supported by TAs and lab instructors. It includes tasks to create a University database and execute various SQL queries related to student and instructor data, course enrollment, and performance metrics. The assignment emphasizes practical application of SQL skills using SQL Management Studio or Azure Data Studio.

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
2 views2 pages

(IC) Topic02 BasicSQLQueries

The document outlines an in-class assignment for the CS486 Introduction to Databases course, led by Dr. Lê Thị Nhàn and supported by TAs and lab instructors. It includes tasks to create a University database and execute various SQL queries related to student and instructor data, course enrollment, and performance metrics. The assignment emphasizes practical application of SQL skills using SQL Management Studio or Azure Data Studio.

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:
-​ Run the given UniversityDB_script file to create the University database and import its data.
Task 2:
Utilize the SQL editor of SQL Management Studio or Azure Data Studio to write the following
queries:
1.​ Find the Students who were born in the 2000s.
2.​ Find the Students who were born between 2000s and 2005s.
3.​ Find the students enrolled in the course ‘CS07’ offered in the Fall semester of 2022.
4.​ Extract the names and salary (after a 10% increase) of the instructors teaching the course
CS07. (Note: Students insert appropriate sample data to test the query)
5.​ Retrieve the (course_id, course_Name, prerequisite name) for all courses in the 'IS'
department.
6.​ Retrieve the instructors of department ‘Computer Science’ whose salary is between $2000
and $3000.
7.​ Retrieve the instructors and their teaching courses with the role "lecturer". The result should
be sorted first by department name and then alphabetically by instructor name.
8.​ Extract a list of (course id, course name) where instructor ‘I002’ has not participated in
teaching.
9.​ Count the number of instructors in the "IS" department.
10.​Count the number of instructors in the "IS" department.
11.​Provide a list of instructors (instructor_id) who have participated in teaching in both the role
of 'lecturer' and the role of 'TA'.
12.​Count the number of courses opened in each year.
13.​Count the number of courses opened in each semester of each year.
14.​Retrieve a list of (student_id, student_name) who failed the course CS03 in the Fall
semester of 2022
15.​Retrieve a list of (student_id, student_name) who passed the course CS03 but failed the
course CS04in the Fall semester of 2022.

You might also like