How to create a database?

In SQL you have to create a database before creating a table, we will see how to create a database in these parts. In SQL the create database statement is a first step for storing the structured data in the database.

The database developers and the users use this statement for creating the New database in the database system then it creates the database with the name which has been specified in the create database statement we are using.

 

Syntax for CREATE DATABASE

        
          CREATE DATABASE database_name;
        
      

In the above syntax database name represents the name of the database we want to Create. Instead of database name, write the name of the database you want to create.

There are some points to keep in mind while creating a database:

• The name we give to the database should be simple and unique and easily identifiable.
• The database name should not exceed one twenty characters. A short name makes it easy to identify.
• The database name is case sensitive.

In this article, we have taken some examples which will help you to perform and run database queries in SQL.

The given example creates a student database. To create a student database we need to use the following syntax.

 

Example for CREATE DATABASE

        
          CREATE DATABASE Student;
        
      

when this query is executed successfully then it will show the following output in SQL.

 

Output

   
  Command created successfully
 

Follow the next steps to create a database on SQL Server:

1. Open Microsoft Server Management Studio .
2. Click on New Query.
3. Type the CREATE DATABASE syntax.
4. Click on Execute.
5. Then it will show you whether you have successfully created a database or not.
6. Click on Refresh in the Object Explorer box on the left side.

 If you try to recreate a database named Student, the following message will show you.

 

Output

        
          Database 'Student' already exists. Choose a different database name.
        
      

List of Databases or Show Database.

You can use the following syntax to view the created database or list of databases in the system . 

Syntax for SHOW DATABASE

    
SHOW DATABASES;
    
  

Tip: Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;

You can also use  following query to show database list:

Syntax for SHOW DATABASE

        
          SELECT NAME FROM sys.databases;
        
      

 SQL Select Database

If a user wants to perform an operation on a table ,view or indexes on a specific existing database in SQL Server, they have to use select the database to run query on it.

Any database user or administrator can easily select the particular database from current database server by using the USE statement.

Syntax of USE statement in SQL

        
          USE database_name;
        
      

 In this syntax you need to define the name of the database after the USE keyword.

 In this article we have taken one example which will help you to understand USE statement in SQL

Example

        
          USE Students;
        
      

In the above syntax student is the name of database.

SQL Rename database

 Rename database statement is used to change the database name. when users or administrators needs to change the name of the database in some situations due to some technical reasons or to identify the database easily they use Rename statement.

Here is the syntax for rename the database;

In the above syntax use old database name after alter database and use New database name after modify name.

Syntax of Rename Database

        
          ALTER DATABASE old_database_name MODIFY NAME = new_database_name;
        
      

When you execute the query successfully it will show you following result.

Output

        
          The database name 'Student_Name' has been set.
        
      

Let's take one example to understand how to perform and run rename statement in SQL.

Example of Rename Database

        
          ALTER DATABASE Students MODIFY NAME = Student_Name;
        
      

In the above table students is the old name of database and student name is new name of database.

SQL Drop Database

SQL drop statement permanently deletes the existing database from the database system. This statement deletes the stored data in the database system so be careful while using it. 

Care to be taken while using Drop statement :

• This statement deletes the complete data from the database system so it is very important to have a backup of that data when the time comes to reuse it.
• Another most important point is that you cannot delete that database from the system which is currently in use by another database user or administrator. If you try to do so it will show you following result:

Output

        
          Cannot drop database "Student_Name" because it is currently in use.
        
      

Here is the syntax of drop database statement in SQL:

Syntax of Drop Database

    
DROP DATABASE Database_Name; 
  

In this syntax we have to specify the name of the database which we want to delete permanently from the database system, so replace the database name before using.

Example of Drop Database

    
DROP DATABASE Student;
  

Above syntax is only for deleting one database from the system but if you want to delete multiple database then here is the  database syntax for deleting multiple database system by using single Drop Statement.

We can also delete multiple databases easily by using the single DROP syntax:

Syntax of Drop Database

        
          DROP DATABASE Database_Name1, [ Database_Name2, ......., Database_NameN ] ;
        
      

Using this statement we have no need to write multiple statement for deleting multiple database we can specify all the databases by using a single statement as shown in the above syntax.