CREATE, DROP, and USE Databases

In this lesson, we will look at the syntax for creating and deleting a database.

We'll cover the following

CREATE DATABASE

The SQL CREATE DATABASE statement is used to create a new SQL database.

Syntax

The basic syntax of this CREATE DATABASE statement is as follows:

CREATE DATABASE DatabaseName;

The database name should always be unique within the RDBMS.

Keep in mind that SQL keywords are NOT case sensitive: create is the same as CREATE.

Also, some database systems require a semicolon at the end of each SQL statement. A semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Example

If you want to create a new database, for example, testDB1, then the CREATE DATABASE statement would be as shown below:

CREATE DATABASE testDB1;

Now let’s create two databases in the code below:

CREATE DATABASE testDB1;
CREATE DATABASE testDB2;
SHOW DATABASES;

The SHOW DATABASE command in line 3 is used to display the list of databases present.

DROP DATABASE

The SQL DROP DATABASE statement is used to drop an existing database in SQL schema.

Syntax

The basic syntax of the DROP DATABASE statement is as follows:

DROP DATABASE DatabaseName;

Example

If you want to delete an existing database, for example testDB1, then the DROP DATABASE statement would be as shown below:

DROP DATABASE testDB1;

Let’s test this command in the code below:

CREATE DATABASE testDB1;
SHOW DATABASES;
DROP DATABASE testDB1;
SHOW DATABASES;

Line 4 in the above code is used to delete/drop the testDB1 database.

Be careful when using this operation because deleting an existing database would result in a complete loss of information stored in the database.

USE database

When you have multiple databases in your SQL schema before starting your operation, you need to select the database where all the operations will be performed.

The SQL USE DATABASE statement is used to select any existing database in the SQL schema.

Syntax

The basic syntax of the USE statement is as shown below:

USE DatabaseName;

Example

Now, if you want to work with a database, for example testDB1, then you can execute the following SQL command and start working with it:

USE testDB1;

CREATE DATABASE testDB1;
CREATE DATABASE testDB2;
SHOW DATABASES;
USE testDB1;

If you want to work with a database in a separate file, the USE statement can be used to select the required database in the second file.

main.sql
temp.sql
CREATE DATABASE testDB1;
CREATE DATABASE testDB2;

Small quiz!

Q

Does the following query creates a new database called COMPANY and then uses it?

CREATE DATABASE COMPANY
USE COMPANY
A)

True

B)

False

In the next lesson, we will learn to create and delete tables (relations) in a database.