Database Management with SQL

Introduction

In this project, you will learn how to delete a specific table in the world database and empty another table without dropping its structure. This project is designed to help you understand database management and SQL commands.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import data from a SQL file into MySQL
• How to delete a table from a database
• How to empty a table without dropping its structure

🏆 Achievements

After completing this project, you will be able to:

• Manage database tables effectively
• Perform basic SQL operations such as deleting and truncating tables
• Understand the importance of database maintenance and optimization

Access MySQL and Import the 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/world.sql into MySQL.

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

3. Import the data from /home/labex/project/world.sql into MySQL and switch to the world database:

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

Delete the City Table

In this step, you will learn how to delete the city table from the world database.

1. Delete the city table:

DROP TABLE IF EXISTS city;

Empty the Countrylanguage Table

In this step, you will learn how to empty the countrylanguage table without dropping the table structure.

1. Empty the countrylanguage table:

TRUNCATE TABLE countrylanguage;

Save the Script

In this final step, you will save the script you have created in the previous steps.

1. Create a new file named dropCity.sql in the /home/labex/project directory.

2. Copy the following code into the dropCity.sql file:

-- Delete the city table
DROP TABLE IF EXISTS city;

-- Empty the countrylanguage table
TRUNCATE TABLE countrylanguage;

3. Run the dropCity.sql script:

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

You should see the following output:

MariaDB [world]> SOURCE /home/labex/project/dropCity.sql;
Query OK, 0 rows affected, 1 warning (0.002 sec)

Query OK, 0 rows affected (0.002 sec)

Congratulations! You have successfully completed the project. You can now run the dropCity.sql script in MySQL to delete the city table and empty the countrylanguage table.

Summary

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