MySQL View Creation | Data Manipulation | Database Management

Introduction

In this project, you will learn how to create a view based on the teaches table and delete the related data from the view.

👀 Preview

MySQL [edusys]> SELECT * FROM teaches_view;
+-------+----------+----------+------+
| id    | courseId | semester | year |
+-------+----------+----------+------+
| 76766 | BIO-101  | Summer   | 2017 |
| 10101 | CS-101   | Fall     | 2017 |
| 83821 | CS-190   | Spring   | 2017 |
| 83821 | CS-190   | Spring   | 2017 |
| 10101 | CS-347   | Fall     | 2017 |
| 98345 | EE-181   | Spring   | 2017 |
| 22222 | PHY-101  | Fall     | 2017 |
+-------+----------+----------+------+
7 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to create a view with specific fields from the teaches table
• How to delete data from the created view based on a certain condition

🏆 Achievements

After completing this project, you will be able to:

• Understand the concept of a view and how to create one
• Manipulate data in a view by deleting records that match a specific condition
• Apply these skills to manage data in a database more efficiently

Create a View

In this step, you will learn how to create a view based on the teaches table. Follow the steps below to complete this step:

1. Open a terminal and start the MySQL service:

   sudo /etc/init.d/mysql start

1. Log into the MySQL terminal.

   mysql -uroot

1. Import the edusys.sql data into the MySQL database.

   SOURCE ~/project/edusys.sql;

1. Create a new file named teachesDump.sql in the ~/project directory.
2. Add code to the file to create a view named teaches_view that contains the following fields:

| teaches | teaches_view |
| --------- | ------------ |
| ID | id |
| course_id | courseId |
| semester | semester |
| year | year |

The code should look like the following:

   CREATE VIEW teaches_view AS
   SELECT ID AS id, course_id AS courseId, semester, year
   FROM teaches;

Delete View Data

In this step, you will learn how to delete the data from the teaches_view view where the year is 2018. Follow the steps below to complete this step:

1. Open the teachesDump.sql file and add code to the file to delete the data from the teaches_view view where the year is 2018.

   DELETE FROM teaches_view
   WHERE year = 2018;

1. The final code is as follows:

   CREATE VIEW teaches_view AS
   SELECT ID AS id, course_id AS courseId, semester, year
   FROM teaches;

   DELETE FROM teaches_view
   WHERE year = 2018;

Now, you have completed the project of creating a view and deleting the related data in the view. You can run the teachesDump.sql script in the MySQL terminal to execute the SQL statements.

SOURCE ~/project/teachesDump.sql;

After executing the SQL statements, you can view the data in the teaches_view view as follows:

MySQL [edusys]> SELECT * FROM teaches_view;
+-------+----------+----------+------+
| id    | courseId | semester | year |
+-------+----------+----------+------+
| 76766 | BIO-101  | Summer   | 2017 |
| 10101 | CS-101   | Fall     | 2017 |
| 83821 | CS-190   | Spring   | 2017 |
| 83821 | CS-190   | Spring   | 2017 |
| 10101 | CS-347   | Fall     | 2017 |
| 98345 | EE-181   | Spring   | 2017 |
| 22222 | PHY-101  | Fall     | 2017 |
+-------+----------+----------+------+
7 rows in set (0.000 sec)

Summary

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

🚀 Practice Now: Delete Course Schedule View Data

Want to Learn More?

• 🌳 Learn the latest MySQL Skill Trees
• 📖 Read More MySQL Tutorials
• 💬 Join our Discord or tweet us @WeAreLabEx