Modify Student Table Attributes

Introduction

In this project, you will learn how to modify the attributes of the student table in the edusys database. You will access MySQL, import data, change the name attribute to student_name, and output the modified table structure.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without any password
• How to import data from a SQL file into the edusys database
• How to change the name attribute in the student table to student_name
• How to output the modified structure of the student table to the command line

🏆 Achievements

After completing this project, you will be able to:

• Manage and modify database tables using SQL commands
• Understand the process of importing and manipulating data in a database
• Apply SQL statements to alter table attributes and structure
• Retrieve and display the updated table information

Access MySQL and Import Data

In this step, you will learn how to access MySQL using the sudo command without any password, and import the data from /home/labex/project/edusys.sql into the edusys database.

1. Open a terminal and start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command without any password:

sudo mysql

3. Import the data from /home/labex/project/edusys.sql into the edusys database:

SOURCE /home/labex/project/edusys.sql;

4. Verify that the data has been imported by checking the edusys database:

SHOW DATABASES;
USE edusys;
SHOW TABLES;

Modify the Student Table Attributes

In this step, you will learn how to change the name attribute in the student table to student_name.

1. Add the following SQL statement to the file alterStudent.sql:

ALTER TABLE student CHANGE COLUMN name student_name varchar(20);

2. Save and close the file.

3. Run the alterStudent.sql script in the edusys database:

SOURCE /home/labex/project/alterStudent.sql;

Output the Modified Structure of the Student Table

In this step, you will learn how to output the modified structure of the student table to the command line.

1. Use the DESC command to output the structure of the student table:

DESC student;

The output should look similar to the following:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ID           | varchar(5)   | NO   | PRI | NULL    |       |
| student_name | varchar(20)  | YES  |     | NULL    |       |
| dept_name    | varchar(20)  | YES  | MUL | NULL    |       |
| tot_cred     | decimal(3,0) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

This completes the project. You have successfully modified the attributes of the student table in the edusys database.

Summary

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