View for Course Information

Introduction

In this project, you will learn how to create and use database views in MySQL. Database views are virtual tables that provide a customized perspective of the underlying data, allowing you to hide certain data processes from users and simplify data access.

👀 Preview

MariaDB [edusys]> SELECT * FROM comp_spring_2018_taylor;
+-----------+-------------+
| course_id | room_number |
+-----------+-------------+
| CS-319    | 3128        |
+-----------+-------------+
1 row in set (0.00 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and import a database
• How to define a view that displays the room numbers of all major courses offered in a specific semester, location, and department
• How to define a view that filters the results of the previous view to only include courses located in a specific building

🏆 Achievements

After completing this project, you will be able to:

• Understand the concept and benefits of database views
• Create custom views to simplify data access and hide unnecessary complexity
• Apply your knowledge of SQL queries to define and use views in a real-world scenario

Start MySQL and Import the Database

In this step, you will learn how to start the MySQL server and import the edusys.sql database.

1. Start the MySQL server using the following command:

sudo service mysql start

2. Access the MySQL command-line interface using the following command:

sudo mysql

3. Import the edusys.sql database using the following command:

source /home/labex/project/edusys.sql;

This will import the edusys database into your MySQL server.

Define the comp_spring_2018 View

In this step, you will learn how to define the comp_spring_2018 view.

1. Create the comp_spring_2018_taylor.sql file in the ~/project directory.
2. Add code to the file to create the comp_spring_2018 view using the following SQL query:

CREATE VIEW comp_spring_2018 AS
SELECT course.course_id, building, room_number
FROM section, course
WHERE course.course_id = section.course_id AND semester = 'Spring' AND year = 2018 AND dept_name = 'Comp. Sci.';

This view will display the course_id, building, and room_number of all courses offered by the "Comp. Sci." department in the spring semester of 2018.

Define the comp_spring_2018_taylor View

In this step, you will learn how to define the comp_spring_2018_taylor view.

1. Open the comp_spring_2018_taylor.sql file.
2. Create the comp_spring_2018_taylor view using the following SQL query after the code added in the previous step:

CREATE VIEW comp_spring_2018_taylor AS
SELECT course_id, room_number
FROM comp_spring_2018
WHERE building = 'Taylor';

This view will display the course_id and room_number of all courses offered by the "Comp. Sci." department in the spring semester of 2018 and located in the "Taylor" building.

Verify the Solution

To verify the solution, you can run the following SQL query:

source /home/labex/project/comp_spring_2018_taylor.sql;
SELECT * FROM comp_spring_2018_taylor;

This should display the following output:

+-----------+-------------+
| course_id | room_number |
+-----------+-------------+
| CS-319    | 3128        |
+-----------+-------------+
1 row in set (0.00 sec)

Congratulations! You have successfully completed the project.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.