CREATE DATABASE and DROP DATABASE in SQL

SQL

The CREATE DATABASE statement is used to create a new database. A database is a structured collection of data that is organized and stored for easy retrieval and management.

Syntax

The basic syntax for creating a database is as follows:

CREATE DATABASE database_name;
  • CREATE DATABASE: The SQL command to create a new database.
  • database_name: The name you want to give to your new database. Make sure to choose a meaningful and descriptive name.

Creating a Simple Database

Let’s start by creating a simple database. For the purpose of this tutorial, we’ll work with a basic structure:

Database Name: StudentDB

Tables

  1. Students Table:
    • Columns:
      • StudentID (INT)
      • FirstName (VARCHAR)
      • LastName (VARCHAR)
      • Age (INT)
  2. Courses Table:
    • Columns:
      • CourseID (INT)
      • CourseName (VARCHAR)
      • Instructor (VARCHAR)
      • Credits (INT)

Examples and Code

Example 1: Creating the Database

CREATE DATABASE StudentDB;

Output:

Query OK, 1 row affected

How to see DATABASE?

In SQL, we can use the SHOW DATABASES command or the SHOW SCHEMAS command, depending on the specific database management system (DBMS).

MySQL and MariaDB:

SHOW DATABASES;

This command will display a list of all the databases on the MySQL or MariaDB server.

PostgreSQL:

\l

This command shows a list of all databases in PostgreSQL.

Microsoft SQL Server:

SELECT name FROM sys.databases;

This SQL query retrieves the names of all databases in Microsoft SQL Server.

SQLite:

ATTACH DATABASE 'path/to/database/file' AS dbname;

This command in SQLite allows you to attach a database file, and you can see the attached databases using the following:

PRAGMA database_list;

DROP Database in SQL

Dropping a database means permanently deleting it from the database management system (DBMS). We have to be extremely careful when using the DROP DATABASE command, as it irreversibly removes all data and objects associated with the specified database.

MySQL and MariaDB:

DROP DATABASE [IF EXISTS] database_name;
  • IF EXISTS: Optional clause to avoid an error if the database does not exist.
  • database_name: The name of the database you want to drop.

Example:

DROP DATABASE IF EXISTS SchoolDB;

PostgreSQL:

DROP DATABASE [IF EXISTS] database_name;

Example:

DROP DATABASE IF EXISTS SchoolDB;

Microsoft SQL Server:

USE master;
DROP DATABASE [IF EXISTS] database_name;

Example:

USE master;
DROP DATABASE IF EXISTS SchoolDB;

SQLite:

SQLite does not have a direct DROP DATABASE command. Instead, you can delete the database file from the file system.

-- In SQLite, you can also use the following pragma to detach a database:
ATTACH DATABASE 'path/to/database/file' AS dbname;
-- Then, you can drop tables or manipulate the database as needed.
-- After that, you can detach the database:
DETACH DATABASE dbname;