Lab 5: View in MySQL - Exercise

Dr Hung Tran and Dr Tran Duc Minh

DATCOM Lab, Faculty of Data Science and Artificial Intelligence

College of Technology
National Economics University, Vietnam
Email: hung.tran@neu.edu.vn
Mobile: 086-646-4048

1
 DATCOM Lab, Faculty of Data Science and Artificial Intelligence, NEU

Introduction
You are tasked with creating and using VIEWs to solve various complex queries in a student management
system. The system contains the following tables:

• Students: contains information about students.

– student id: student ID (primary key)
– name: student name
– birthdate: date of birth
– major: student’s major
– enrollment date: date of enrollment
• Courses: contains information about courses.
– course id: course ID (primary key)
– course name: name of the course
– credits: number of credits for the course
• Enrollments: contains information about students enrolling in courses.
– enrollment id: enrollment ID (primary key)
– student id: student ID (foreign key)
– course id: course ID (foreign key)
– enrollment date: date of enrollment
• Grades: contains student grades for the courses they are enrolled in.
– grade id: grade ID (primary key)
– student id: student ID (foreign key)
– course id: course ID (foreign key)
– grade: student’s grade in the course (0-10 scale)

Requirements
1. Create a VIEW for student grades based on the major
Create a VIEW that displays a list of students, including student id, name, major, course name,
and grade. This VIEW should include only students in the “Computer Science” major with an
average grade of 7.0 or higher.

• Question 1: Write an SQL statement to create the VIEW as described above.

• Question 2: Write an SQL query using the VIEW to return a list of students who have an
average grade ≥ 7.0 in all the courses they have enrolled in.

2. Create a VIEW to summarize the number of students by major

Create a VIEW that displays the major and the number of students enrolled in each major. This
VIEW should only include majors with at least 20 students.

• Question 3: Write an SQL statement to create the VIEW summarizing the number of
students by major.

3. Create a VIEW to track graduation progress

Create a VIEW to display the student’s name, the number of credits they have completed, and
the minimum number of credits they need to graduate. Assume that students need to complete
at least 120 credits to graduate and only courses where they scored a grade of 5.0 or higher count
toward the completed credits.

• Question 4: Write an SQL statement to create the VIEW described above.

@DATCOM Lab 2
 DATCOM Lab, Faculty of Data Science and Artificial Intelligence, NEU

4. Optimize VIEW performance

Some VIEWs may become slow when handling large datasets. Propose solutions to optimize
the performance when using VIEWs, particularly when processing data for many students and
courses. Explain the methods and strategies you would apply to enhance efficiency.

• Question 5: Provide 3 solutions to optimize VIEW performance in the student management

system, and explain in detail how to apply them.

Sample Data
Use the following sample data for testing:

• Students
student id name birthdate major enrollment date
1 Nguyn Văn A 2000-05-20 Computer Science 2019-08-15
2 Trn Th B 2001-03-12 Computer Science 2020-08-15
3 Lê Văn C 2000-11-30 Mechanical Eng. 2019-08-15
4 Phm Th D 2001-07-05 Computer Science 2020-08-15
5 Hoàng Văn E 2000-09-22 Business Admin. 2019-08-15
6 Th F 2001-01-19 Computer Science 2020-08-15
• Courses
course id course name credits
1 Database Systems 4
2 Machine Learning 3
3 Thermodynamics 4
4 Marketing Management 3
5 Data Structures 4
6 Financial Accounting 3
• Enrollments
enrollment id student id course id enrollment date
1 1 1 2019-09-01
2 1 2 2019-09-01
3 2 1 2020-09-01
4 2 5 2020-09-01
5 3 3 2019-09-01
6 3 5 2019-09-01
7 4 1 2020-09-01
8 4 2 2020-09-01
9 5 4 2019-09-01
10 5 6 2019-09-01
• Grades
grade id student id course id grade
1 1 1 8.5
2 1 2 7.0
3 2 1 6.5
4 2 5 7.5
5 3 3 5.5
6 3 5 8.0
7 4 1 9.0
8 4 2 7.5
9 5 4 6.0
10 5 6 7.0

@DATCOM Lab 3
 DATCOM Lab, Faculty of Data Science and Artificial Intelligence, NEU

Submission
Students must submit:
• An SQL file containing the commands to create the VIEWs and related queries. The file name
should follow the format ID Fullname VIEW.sql.

• A report (in PDF or Word format) explaining the approach and execution of the SQL commands
and the optimization strategies.
If you need further clarification or assistance, please contact your instructor.

@DATCOM Lab 4