Learn SQL | Database Management | Student Information

Introduction

In this project, you will learn how to add student personal information to a database. You will start by learning how to set up the MySQL server and import the necessary database. Then, you will create a SQL script to insert new student records into the student table.

👀 Preview

MariaDB [edusys]> SELECT * FROM student WHERE ID = 93521 OR ID = 89212;
+-------+------+-----------+----------+
| ID    | name | dept_name | tot_cred |
+-------+------+-----------+----------+
| 89212 | Tang | Music     |       60 |
| 93521 | Rong | History   |       68 |
+-------+------+-----------+----------+
2 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

How to start the MySQL server
How to import a database using SQL scripts
How to use SQL INSERT statements to add new data to a table

🏆 Achievements

After completing this project, you will be able to:

Set up a MySQL server and import a database
Write SQL scripts to insert new data into a database table
Verify the inserted data using SQL queries

Start MySQL and Import the Database

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

Open a terminal on the server.
Start the MySQL server:
```
sudo /etc/init.d/mysql start
```
Log into the MySQL terminal:
```
mysql -uroot
```
Import the edusys.sql script into the MySQL database:
```
SOURCE ~/project/edusys.sql
```

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

Add Student Personal Information

In this step, you will learn how to add student personal information to the student table.

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

Open the file and add the following SQL statements:

INSERT INTO edusys.student (ID, name, dept_name, tot_cred)
VALUES (93521, 'Rong', 'History', 68),
       (89212, 'Tang', 'Music', 60);

This will insert two new student records into the student table.

Save the file.
Run the SQL script in the MySQL terminal:
```
SOURCE ~/project/insertInformation.sql
```

You can verify the new student records by running the following SQL query:

SELECT * FROM student WHERE ID = 93521 OR ID = 89212;

This should display the two new student records that you just added.

MariaDB [edusys]> SELECT * FROM student WHERE ID = 93521 OR ID = 89212;
+-------+------+-----------+----------+
| ID    | name | dept_name | tot_cred |
+-------+------+-----------+----------+
| 89212 | Tang | Music     |       60 |
| 93521 | Rong | History   |       68 |
+-------+------+-----------+----------+
2 rows in set (0.000 sec)

Summary

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