Delete Course Schedule View Data

MySQLMySQLBeginner
Practice Now

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
  2. Log into the MySQL terminal.

    mysql -uroot
  3. Import the edusys.sql data into the MySQL database.

    SOURCE ~/project/edusys.sql;
  4. Create a new file named teachesDump.sql in the ~/project directory.

  5. 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;
  2. 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)
âœĻ Check Solution and Practice

Summary

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

Other MySQL Tutorials you may like