Database Management | SQL Fundamentals | Table Deletion

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`") sql/BasicSQLCommandsGroup -.-> sql/delete("`DELETE statements`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/delete("`Data Deletion`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_table("`Table Removal`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("`Database Selection`") sql/BasicSQLCommandsGroup -.-> sql/drop_table("`DROP TABLE statements`") sql/BasicSQLCommandsGroup -.-> sql/truncate_table("`TRUNCATE TABLE statements`") sql/DataManipulationandQueryingGroup -.-> sql/exists("`EXISTS condition`") subgraph Lab Skills mysql/source -.-> lab-301328{{"`Database Management With SQL`"}} sql/select -.-> lab-301328{{"`Database Management With SQL`"}} sql/delete -.-> lab-301328{{"`Database Management With SQL`"}} sql/in -.-> lab-301328{{"`Database Management With SQL`"}} mysql/select -.-> lab-301328{{"`Database Management With SQL`"}} mysql/delete -.-> lab-301328{{"`Database Management With SQL`"}} mysql/drop_table -.-> lab-301328{{"`Database Management With SQL`"}} mysql/use_database -.-> lab-301328{{"`Database Management With SQL`"}} sql/drop_table -.-> lab-301328{{"`Database Management With SQL`"}} sql/truncate_table -.-> lab-301328{{"`Database Management With SQL`"}} sql/exists -.-> lab-301328{{"`Database Management With SQL`"}} end

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.

Start the MySQL service:

sudo service mysql start

Access MySQL using the sudo command:

sudo mysql

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.

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.

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.

Create a new file named dropCity.sql in the /home/labex/project directory.
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;

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.