This content originally appeared on DEV Community and was authored by Labby
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
teachestable - 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:
- Open a terminal and start the MySQL service:
sudo /etc/init.d/mysql start
- Log into the MySQL terminal.
mysql -uroot
- Import the
edusys.sqldata into the MySQL database.
SOURCE ~/project/edusys.sql;
- Create a new file named
teachesDump.sqlin the~/projectdirectory. - Add code to the file to create a view named
teaches_viewthat 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:
- Open the
teachesDump.sqlfile and add code to the file to delete the data from theteaches_viewview where theyearis 2018.
DELETE FROM teaches_view
WHERE year = 2018;
- 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
This content originally appeared on DEV Community and was authored by Labby
Labby | Sciencx (2024-09-19T22:28:48+00:00) MySQL View Creation | Data Manipulation | Database Management. Retrieved from https://www.scien.cx/2024/09/19/mysql-view-creation-data-manipulation-database-management/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.