Delete Expired Course Information

Introduction

In this project, you will learn how to manage and maintain a database of course information. The main task is to delete expired course information from the database using SQL commands.

👀 Preview

MySQL [edusys]> SELECT * FROM course;
+-----------+----------------------------+------------+---------+
| course_id | title                      | dept_name  | credits |
+-----------+----------------------------+------------+---------+
| BIO-101   | Intro. to Biology          | Biology    |       4 |
| BIO-399   | Computational Biology      | Biology    |       3 |
| CS-101    | Intro. to Computer Science | Comp. Sci. |       4 |
| CS-190    | Game Design                | Comp. Sci. |       4 |
| CS-315    | Robotics                   | Comp. Sci. |       3 |
| CS-319    | Image Processing           | Comp. Sci. |       3 |
| CS-347    | Database System Concepts   | Comp. Sci. |       3 |
| EE-181    | Intro. to Digital Systems  | Elec. Eng. |       3 |
| FIN-201   | Investment Banking         | Finance    |       3 |
| HIS-351   | World History              | History    |       3 |
| MU-199    | Music Video Production     | Music      |       3 |
| PHY-101   | Physical Principles        | Physics    |       4 |
+-----------+----------------------------+------------+---------+
12 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and log into the MySQL terminal
• How to import data from an SQL script into the database
• How to use the DELETE statement to remove specific records from a table

🏆 Achievements

After completing this project, you will be able to:

• Understand the basic operations of a relational database management system (RDBMS) like MySQL
• Write SQL queries to manipulate data in a database
• Develop scripts to automate database management tasks

Skills Graph

Start MySQL and Import Data

In this step, you will learn how to start the MySQL server and import the necessary data for the project.

1. Open a terminal and start the MySQL service using the following command:

   sudo /etc/init.d/mysql start

2. Log into the MySQL terminal:

   mysql -uroot

3. Import the edusys.sql script into the MySQL database:

   SOURCE ~/project/edusys.sql

This will create the necessary database and tables for the project.

Delete Expired Course Information

In this step, you will learn how to delete the course with course_id as BIO-301 from the course table.

1. Create the deleteCourse.sql file in the ~/project directory:

2. Open the deleteCourse.sql file in a text editor and add the following code:

   use edusys;
   DELETE FROM course WHERE course_id = 'BIO-301';

   This SQL statement will delete the course with course_id as BIO-301 from the course table.

3. Save the deleteCourse.sql file.

4. Run the deleteCourse.sql script in the MySQL terminal:

   SOURCE ~/project/deleteCourse.sql

   This will execute the DELETE statement and remove the expired course information from the database.

You can verify the changes by running the following SQL query:

SELECT * FROM course;

This should show the updated list of courses, with the BIO-301 course removed.

+-----------+----------------------------+------------+---------+
| course_id | title                      | dept_name  | credits |
+-----------+----------------------------+------------+---------+
| BIO-101   | Intro. to Biology          | Biology    |       4 |
| BIO-399   | Computational Biology      | Biology    |       3 |
| CS-101    | Intro. to Computer Science | Comp. Sci. |       4 |
| CS-190    | Game Design                | Comp. Sci. |       4 |
| CS-315    | Robotics                   | Comp. Sci. |       3 |
| CS-319    | Image Processing           | Comp. Sci. |       3 |
| CS-347    | Database System Concepts   | Comp. Sci. |       3 |
| EE-181    | Intro. to Digital Systems  | Elec. Eng. |       3 |
| FIN-201   | Investment Banking         | Finance    |       3 |
| HIS-351   | World History              | History    |       3 |
| MU-199    | Music Video Production     | Music      |       3 |
| PHY-101   | Physical Principles        | Physics    |       4 |
+-----------+----------------------------+------------+---------+
12 rows in set (0.000 sec)

Summary

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