After the successful installation of the SQLite database, to launch the SQLite terminal. For this, use the “Sqlite3” keyword in the Ubuntu 20.04 shell screen. The Sqlite3 console will be opened for your use to add database queries to it. To see all the already built tables within our sqlite3 database, we will be using the “.tables” instruction in the sqlite3 console. The output for this command is showing that we have no tables yet to be found in the database as presented below.
To drop a table from the Sqlite3 database, we must have some table in it. So, we will be creating a new table named “Employee” with the CREATE TABLE instruction. We have been creating a column “ID” of integer type which will be the primary key for this table and must not be NULL. The Name column of text type and the AGE column of integer type will be created and both must not be NULL. After executing this instruction, our table will be successfully generated in the SQLite database. Using the “.tables” instruction, we have listed the tables of our database i.e. only an “Employee” table has been found so far.
To fetch all the records of the table “Employee”, we have to use the SELECT query followed by the asterisk “*” character and the FROM option followed by the table name. On execution, we have found that the table is empty as it returns nothing. As this table is empty, we can simply delete it.
To delete this newly generated empty table from our Sqlite3 database of Ubuntu 20.04, we need to utilize the DROP TABLE instruction at the sqlite3 shell. The command must contain the name of a table at its end to specify the table to be removed from the database system. We have used the name “Employee” after the DROP TABLE instruction keyword. This command returns nothing as the query execution was successful.
After using the “.tables” instruction at the shell, we have nothing in return as an output (i.e. showing that the table has been removed and there is no table left in the database.) To confirm the deletion of an “Employee” table, we need to use the SELECT instruction followed by the asterisk “*” character once more followed by the option FROM and the table named “Employee”. After the SELECT instruction execution on the SQLite console, we encountered the error stating that there is no such table as “Employee” in the database. This error message demonstrates that the table has been successfully dropped from our database.
Let’s take a look at another example to create and drop a table from the SQLite database of the Ubuntu 20.0.4 system. This time we will not be dropping a table until it has some values. So, we have been creating a new table “Test” with the use of CREATE TABLE instruction in the SQLite console. This table will include the ID column of integer type and it will also be considered to be a primary key. The “Name” column will be of text type and the “Salary” column must be of integer type while all of these three columns must not be empty.
After executing this instruction, we have tried the “.tables” instruction to see all the already created tables in our database. We have found the newly created “Test” table only in this table list. Now, it’s time to insert some records within the Test table. For this, we need to use the INSERT INTO instruction followed by the name of a table, “Test”. Its consecutive column names. The option VALUES would be followed by the values to be added to the columns mentioned. Using this instruction, we have added a total of 5 records for the ID, Name, and Salary column of the “Test” table.
After adding 5 records within the newly generated Test table, we have to fetch and display these 5 records in the column and row forms within the SQLite console screen. For this, we will be using the SELECT instruction followed by the asterisk “*”, the “FROM” option, and the name of a table “Test”. After running this query, we have the 5 records displayed for the three columns ID, Name, and Salary of a table “Test”.
After that, we tried the DROP TABLE instruction to delete the table “Test” from our database. The “.tables” query is here to display the list of tables and no table has been found so far. Using the SELECT instruction once more, we have found an error showing that the Test table is already deleted.
Now, we have created 3 tables i.e. Test, Student, and Department. The Department table uses the Column “SID” of the Student table as its foreign key.
We have tried the DROP TABLE command to delete the Test table first. After this, we tried to use the same instruction to delete the Department and Student tables at the same time. It caused an error as displayed.
To avoid this error, we must use the DROP TABLE instruction along with each table name.
Conclusion:
This article was all about the use of DROP TABLE instructions to drop or delete the table from the database. We have used the help of CREATE TABLE, “.tables”, and the SELECT instructions to create, search and drop the tables. That’s all for today and we hope you will like it.
