How to Create a Database in SQL? A Detailed Procedure

In this ever-evolving digital world, Database Management is crucial. They help store, organise, and retrieve information effectively. Structured Query Language (SQL) is a globally used programming language for managing Relational Databases. If you are just starting out on SQL, then delve into this blog to learn How to Create a Database in SQL.  

The first step in storing the structured data in a Database is the 'CREATE DATABASE' statement. Database engineers and developers utilise this statement to create new Databases on their systems, with a name specified in the statement.    

According to the State of Developer Ecosystem Report 2023 by HackerEarth, more than 60 per cent of developers are familiar with SQL. This statistic validates the demand for professionals experienced with the query language. If you want to pursue a career in Database Management, you should be familiar with How to Create a Database in SQL.

Table of Contents 

1) A brief look at a Database in SQL    

2) Creating a Database in SQL  

3) How to use an SQL Database? 

4) How to delete or drop an SQL Database? 

5) Conclusion  

A brief look at a Database in SQL  

A Database in SQL serves as a structured repository for storing, managing, and retrieving data. It acts as a digital ledger, organising information into tables, each comprising rows and columns. SQL, or Structured Query Language, is the standard language for interacting with these databases. Databases store diverse data types, from text and numbers to images and more, facilitating efficient data organisation. 

SQL operations, such as SELECT for retrieval and INSERT for addition, allow seamless interaction with the database. Key database concepts include tables, relationships, and indexes, collectively ensuring data integrity and accessibility. Understanding the basics of SQL Databases is fundamental for anyone involved in Data Management, providing a foundation for effective data manipulation and retrieval within a structured and organised framework.

Master the advanced art of SQL with our expert-led Advanced SQL Training – join now! 

Types of Databases in SQL

Users can generally create two types of Databases using the SQL Server:  

1) System Databases: SQL Server will automatically create system Databases for users after installation. These Databases are utilised by SQL Server Management Studio (SSMS), Application Programming Interfaces (APIs) and other tools. Users are advised to avoid a manual modification of system Databases. Here is a list of various system Databases:   

a) Master: The master Database stores the system-level information for each instance of the SQL Server Database. It comprises logon accounts, linked servers, endpoints and system configuration settings.    

b) Model: The model Database is utilised as a template for creating all Databases on the instance of the SQL Server.    

c) MSDB: This Database is used by the SQL Server Agent service for alert schedules and other features like the SQL Server Management Studio, Database Mail and Service Broker.    

d) Tempdb: The Tempdb Database holds intermediate data result sets, temporary objects, and internal objects the SQL engine creates.   

2) User-defined Databases: As the name suggests, users create these Databases using Transact-SQL (T-SQL) or SSMS for the application data. An instance of an SQL Server can contain a maximum of 32,767 Databases. 

Creating a Database in SQL 

The two most important SQL commands that users can learn to execute are 'CREATE DATABASE' and 'CREATE TABLE' Users must learn to get familiar with these basic structure of SQL queries before they work with any further operations on data. Alternatively, they can experiment with these two statements if they have any template databases.   

Users can create Databases in SQL using the following two methods: 

Create a Database using T-SQL

A user can execute an SQL script in the query editor, using the ‘Master’ Database, in the following way: 

USE master; 

CREATE  

The user can then execute the following script to create an ‘HR’ Database: 

USE master; 

CREATE Database ‘HR’; 

The following script can then be executed to create the HR Database with log and data files: 

USE master; 

CREATE Database [HR] 

ON (Name = N’HR’, FILENAME = N’’, SIZE = 2048MB, FILEGROWTH = 500MB) 

LOG ON (NAME = N'HR_log', FILENAME = N'', SIZE = 1048MB, FILEGROWTH = 256MB) 
 
The user must now ensure that the file paths for the data and log file exist before they execute the SQL Script written above. They can then open the SSMS service and refresh the Database folder to view the newly created HR Database in the list. 

Dive deep into our industry-leading  SQL courses! From fundamentals to advanced techniques, we've got you covered.

Create a Database using SQL Server Management Studio 

A user can Create a Database in SQL Studio using the following ways:  

1) The user can open the SSMS and connect to a new instance of the SQL Server in the Object Explorer.   

2) They can then right-click on the Databases folder and click the 'New Database....' option.  

3) After the new Database dialogue opens, the user must enter a name for their new Database (for example, 'HR').  

4) The user can then alter the default values for their Database and the log files under the 'Database files' grid.   

5) An SQL Server has at least two operating system files, which are:   

a) Data files: These files have data and objects such as tables, stored procedures, views, and indexes.    

b) Log files: These files have the information necessary to recover all transactions within the Database. There must be a minimum of one log file for every Database.    

6) The user can also alter the options under the 'Options' page of the 'New Database' window:   

a) Collation: This model specifies the patterns of the bits representing each character in a dataset. The server supports storing objects with different collations in the same Database.    

b) Recovery Model: This model is a Database property that controls how the transactions are logged. A user is given three options under this model: Simple, Full and Bulk-logged. A Database typically utilises the full recovery model.   

c) Compatibility Level: This model lists selected versions of the SQL Server, namely 2008, 2012, 2014, 2016, 2017, and 2019.    

d) Containment Type: This model comprises two options: None and Partial. The default option is 'None'.    

7) The user can then create a new Database, which will be listed in the Database folder. The Database is created containing the following folders:   

a) Database Diagrams: These diagrams depict the structure of the new Database. A user can create new diagrams with the 'Create New Diagram' option after right-clicking on the folder.    

b) Views: The system and user-defined views are available to the users in this Database folder.    

c) Tables: The system- and user-defined tables are contained within this folder. The system views are those that have internal information related to the Database, and the user-defined ones are those that are created with the user's specifications.   

d) External Resources: Any service, file share or computer not part of the SQL Server installation is stored as an external resource. This resource comprises two folders: ' External Data Sources' and 'External File Formats'.    

e) Programmability: This folder displays all the functions, stored procedures, assemblies, rules, defaults, Database triggers, etc., of the Database.   

f) Storage: The storage folder maintains the information on the Partition schemes and functions and full-text catalogues.    

g) Security: This folder comprises the roles, schemas, Database users, security keys (asymmetric and symmetric), certificates, and security policies. 

Unlock the gateway to data mastery with our Relational Databases & Data Modelling Training – Sign up today! 

How to use a SQL Database?

To employ a SQL Database, utilise the following syntax for selecting and using a specific database:

USE databasename;

In this syntax, "USE" is the keyword, and "databasename" represents the chosen database, selected from existing database names. Ensure that you conclude the statement with a semicolon. Once a database is selected, it remains the default until another "USE" statement with a different database is executed. To verify the current database in use, hover your mouse pointer over the query execution file.
 

How to delete or drop a SQL Database?

To delete a Database in SQL, employ the following syntax:

DROP DATABASE databasename;

In this syntax, "DROP DATABASE" is the designated keyword, and "databasename" represents the specific database intended for deletion. Conclude the statement with a semicolon. Exercise caution when using this command, as it permanently removes the specified database and its associated data.

Conclusion 

We hope you enjoyed reading this blog on How to Create a Database in SQL. In this blog took a brief look at the Database in SQL and how you can create one. This blog can be a good starting point if you are just starting your career in Database Management in SQL. 

Revolutionise your Database skills with our Introduction to Database Training - Sign up now!