SQL - Create Database and Drop Database: Definition, Syntax and Examples

Vijay Kashyap Algo Trading Expert
Create Database and Drop Database

Create Database: 

For creating a database there are two ways: 

  • By using CREATE DATABASE statement 
  • Using SQL Server Management Studio (Manually) 

We will create database using both method. 

By using CREATE DATABASE statement: 

You can use the below syntax for creating a database.  


CREATE DATABASE DataBase_Name;

Let’s create a database named “Demo_DB”. 

Syntax:


 CREATE DATABASE Demo_DB;

Creating database Using SQL Server Management Studio (Manually): 
First, right-click on the “Databases” folder in the Object Explorer pane, and select “New Database…” 

Now, in the “New Database” dialog box, specify the name for your new database in the “Database name” field. 


Click on the “Options” tab to specify additional settings for the database, such as recovery model, backup options, and compatibility level. 

At the end, Click “OK” to create the new database. 
 

After creating a database in SQL Server, you can view it in SQL Server Management Studio by expanding the “Databases” folder. The new database should appear in the list of databases. 

As you can see in the below image:  


Alternatively, you can use the following T-SQL command to view a list of all databases on the server: 

SELECT name FROM sys.databases;
Result: 


Above query will display a list of all databases on the server, including the one you just created.  


You can also filter the results by using a WHERE clause to search for a specific database name, by using the following syntax.
 SELECT name FROM sys.databases WHERE name = ‘Demo_DB’;
 


How to use a database? 

There are two ways to use a database: 

  1.  Use a database using a syntax – 

You need to follow these steps: 

  • Connect to the SQL Server instance where the database is located. 
  • Open a new query window in SQL Server Management Studio. 
  • Switch to the desired database context by using the USE statement. The syntax is as follows: 

USE database_name;

We have created a database, named “Demo_DB”. Let’s use this database using SQL Statement- 
USE Demo_DB;

  1. Manually from SQL Server Management Studio – 

We can manually choose the database which we want to use. As shown in below image:



Delete/ Drop a database: 
To drop a database in SQL Server, you can use the following query: 



 DROP DATABASE Demo_DB;

Note: Be very careful when dropping a database, as it will permanently delete all data and cannot be undone. 

Before dropping a database, it is important to keep the following things in mind: 

  • Make sure that you have a backup of the database that you want to drop. 
  • Make sure that you have detached the database before dropping it. 
  • Ensure that you have the necessary permissions to drop the database. 
  • Check that there are no active connections to the database that you want to drop. 
  • Dropping a database will permanently delete all data and objects associated with it, so be sure to double-check before executing the drop command. 
  • Dropping a database is an irreversible operation, so make sure you have a backup of the database if you need to recover the data. 
  1. What is the syntax for creating a database in SQL Server? 
    Answer: CREATE DATABASE database_name 
  1. What are the different options available when creating a database in SQL Server? 
    Answer: The different options available when creating a database in SQL Server include specifying the database name, file locations, size, growth rate, collation, and other options related to the database configuration. 
  1. What is the purpose of the “model” database in SQL Server?  
    Answer:  The purpose of the “model” database in SQL Server is to serve as a template for creating new databases. When a new database is created, it is based on the “model” database, and it inherits the database settings and objects defined in the “model” database. 
  1. How can you check if a database already exists before creating it?  
    Answer: You can check if a database already exists before creating it by using the “IF NOT EXISTS” clause in the “CREATE DATABASE” statement. The syntax for this is: 
     IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = ‘database_name’) 

          CREATE DATABASE database_name; 

  1. What are the precautions you need to take before dropping a database?
    Answer: Some precautions you need to take before dropping a database are taking a backup of the database, ensuring that no other users are connected to the database, and ensuring that you have the necessary permissions to drop the database.