Summary -

In this topic, we described about the below sections -

How to create Database?

In SQL, database is a systematic collection of data and it can easily modify, read, and write. The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data.

In terms of security, the database is more secure by providing limited access to the predefined users. In terms of compatibility, we can access the database from anywhere and SQL database plays a vital role in online transactions.

To access the database, it should be already existing, or we need to create it.

CREATE DATABASE statement is used to create database. The syntax for database creation is -

Create database

Element Description Restrictions
database-name Specifies the new database name. Must be unique name among the names of the databases in the server.
dbspace Specifies the space for storing the database.
Default is root.		 Dbspace should exist.

If the optional IF NOT EXISTS keywords includes, the database server takes no action if a database the specified name exists.

When you create a database and you do not have admin privileges or you are not DBA, it is a local database and you alone can access it.

When you create a database and you have admin privileges or you are DBA, you should run GRANT statement to make the database available for all other users.


Example -

Consider we are going to create a separate database for employee's named emptdb.The code for creating the database is

CREATE DATABSE A emptdb;

After executing the above query, gets created. As we discussed earlier, we must have admin privileges to create the database.

There are some important points that should remember while naming a new database -

  • Database name is a combination of alphanumeric letters. i.e. a to z and 0 to 9.
  • The database name should be as descriptive as possible. It should specify the nature of data it contains e.g., Customers, Vendors, etc.
  • The database name should be unique in a database server. Some database vendors have a restriction on the length of the database name. For e.g., Microsoft SQL Server supports the database name can be up to 128 characters.
  • In some platforms (UNIX and LINUX), the name of the database is case sensitive. So, we should use a consistent database name. For example – to refer the database created in the above example, we should always use the database name as emptdb. But not like emptdb or EMPTDB or something else.
  • Delimited identifiers should not use for a database name.
  • The maximum length of the database name refers the number of bytes, not the number of characters.

Note! In real-time project, as a developer we do not have access to create the database. There is separate team called database admin team to perform all DDL transactions.

Verify the Database

Once the database is successfully created, we can verify the database created or not by using below statement -

SHOW DATABASES [LIKE pattern];
  • pattern - Specifies the partial string of the database name.

Example -

Consider above example, we can verify the database creation by using the below query – 

SHOW DATABASES LIKE "emp%";

Once the above query executed, it displays the results like below -

+--------------------+
|         Database   |
+--------------------+
| emptdb             |
+--------------------+
1 rows in set (0.00 sec)

Note! Before going to create the database, it is always suggestable to verify the database is existing or not.

What happen if try to create a database with the name that already existing?

When try to create a new database with the name that already exists ,system throws the Error: "A database with the specified name already exists. Please specify a unique name."