Updating Student GPA in MySQL Database

Introduction

In this project, you will learn how to update a student's GPA (Grade Point Average) in a database using the UPDATE statement in SQL.

👀 Preview

MySQL [edusys]> SELECT * FROM student WHERE name = "Snow";
+-------+------+-----------+----------+
| ID    | name | dept_name | tot_cred |
+-------+------+-----------+----------+
| 70557 | Snow | Physics   |       61 |
+-------+------+-----------+----------+
1 row 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 a database script into the MySQL database
• How to use the UPDATE statement to modify data in a table
• How to verify the updated data in the database

🏆 Achievements

After completing this project, you will be able to:

• Understand the basic SQL UPDATE statement and how to use it to modify data in a database
• Gain experience in working with MySQL, including starting the server, logging in, and executing SQL scripts
• Develop skills in database management and data manipulation

Start MySQL and Import the Database

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

1. Open a terminal on your system.

2. Run the following command to start the MySQL service:

   sudo /etc/init.d/mysql start
   

3. Log into the MySQL terminal:

   mysql -uroot
   

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

   SOURCE ~/project/edusys.sql
   

Update the Student's GPA

In this step, you will learn how to use the UPDATE statement to modify the tot_cred of the student with the name snow in the student table.

1. Open a new file named updateInformation.sql in the ~/project directory.

2. Add the following code to the updateInformation.sql file:

   USE `edusys`
   UPDATE student
   SET tot_cred = 61
   WHERE name = "Snow";
   

   This code will update the tot_cred column of the student with the name snow to 61.

3. Save the updateInformation.sql file.

Run the Update Script

In this step, you will learn how to run the updateInformation.sql script in the MySQL terminal.

1. In the MySQL terminal, run the following command to execute the updateInformation.sql script:

   SOURCE ~/project/updateInformation.sql
   

2. Verify the update by running the following query:

   SELECT * FROM student WHERE name = "Snow";
   

   You should see the updated tot_cred value of 61 for the student with the name snow.

   +-------+------+-----------+----------+
   | ID    | name | dept_name | tot_cred |
   +-------+------+-----------+----------+
   | 70557 | Snow | Physics   |       61 |
   +-------+------+-----------+----------+
   1 row in set (0.000 sec)
   

Congratulations! You have successfully updated the student's GPA in the database using the UPDATE statement.

Summary

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