Creating and Updating Database Views

Introduction

In this project, you will learn how to create a student view in a MySQL database and update the data in the view.

👀 Preview

MySQL [edusys]> SELECT * FROM student WHERE ID = '70557';
+-------+---------+------+
| id    | name    | cred |
+-------+---------+------+
| 70557 | History |    0 |
+-------+---------+------+
1 row in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to create a view based on an existing table
• How to update the data in a view

🏆 Achievements

After completing this project, you will be able to:

• Understand the concept of a database view and how to create one
• Modify the data in a view using SQL update statements
• Apply these skills to manage and maintain data in a database

Creating the Student View

In this step, you will learn how to create a student view based on the student table.

1. Start MySQL:

   sudo /etc/init.d/mysql start
   

2. Log into the MySQL terminal:

   mysql -uroot
   

3. Import the data from the edusys.sql script into MySQL:

   SOURCE ~/project/edusys.sql
   

4. Create a new file named updateView.sql in the ~/project directory.

5. Add code to the file to create a view named teaches_view that contains the following fields:

   student	student_view
   ID	id
   name	name
   tot_cred	cred

   The code should look like the following:

   USE edusys
   CREATE VIEW IF NOT EXISTS student_view AS
   SELECT ID AS id, name AS name, tot_cred AS cred
   FROM student;
   

   This creates a view named student_view that selects the ID, name, and tot_cred columns from the student table and renames them to id, name, and cred respectively.

Updating the Student View

In this step, you will learn how to update the data in the student_view view.

1. Open the updateView.sql file and add code to the file to update the name field with the value 'History' for the entry with id '70557' in the student_view view:

   UPDATE student_view
   SET name = 'History'
   WHERE id = '70557';
   

   This updates the name field to 'History' for the row in the student_view view where the id is '70557'.

2. The final code is as follows:

   USE edusys
   -- Create a student_view table with the corresponding fields as shown in the table
   CREATE VIEW IF NOT EXISTS student_view AS
   SELECT ID AS id, name AS name, tot_cred AS cred
   FROM student;
   
   -- Update the name field with the value History for the entry with id 70557 in the student_view view
   UPDATE student_view
   SET name = 'History'
   WHERE id = '70557';
   

3. Run the SQL script in the MySQL terminal.

   SOURCE ~/project/updateView.sql
   

4. Verify the update by selecting the data from the student table where the ID is '70557':

   SELECT * FROM student WHERE ID = '70557';
   

   The output should show the updated name field:

   +-------+---------+------+
   | id    | name    | cred |
   +-------+---------+------+
   | 70557 | History |    0 |
   +-------+---------+------+
   1 row in set (0.001 sec)
   

Congratulations! You have successfully created a student view and updated the data in the view.

Summary

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