Modify Student Table Attributes in MySQL Database

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/alter_table("`Table Modification`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("`Database Selection`") sql/BasicSQLCommandsGroup -.-> sql/alter_table("`ALTER TABLE statements`") sql/DataDefinitionandIntegrityGroup -.-> sql/data_types("`Data Types`") sql/DataDefinitionandIntegrityGroup -.-> sql/constraints("`Constraints`") subgraph Lab Skills mysql/source -.-> lab-301360{{"`Modify Student Table Attributes`"}} sql/in -.-> lab-301360{{"`Modify Student Table Attributes`"}} mysql/alter_table -.-> lab-301360{{"`Modify Student Table Attributes`"}} mysql/use_database -.-> lab-301360{{"`Modify Student Table Attributes`"}} sql/alter_table -.-> lab-301360{{"`Modify Student Table Attributes`"}} sql/data_types -.-> lab-301360{{"`Modify Student Table Attributes`"}} sql/constraints -.-> lab-301360{{"`Modify Student Table Attributes`"}} end

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.

Open a terminal and start the MySQL service:

sudo service mysql start

Access MySQL using the sudo command without any password:

sudo mysql

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

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

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.

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

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

Save and close the file.
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.

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.