SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL Create Database

This tutorial will help us understand how to CREATE a Database in SQL with a few examples.

The first step for storing structured records in the database is creating the databases.

We create a database to store data in the database. We also create a database to create tables, indexes, and views into the database.

We use the CREATE DATABASE query to create a database followed by the database name. The database name must be attractive, easy to remember, and, most importantly, unique. The database with the same name cannot be created on the server.

The database user must know which database name already exists in the database server.

Use the SHOW DATABASES query to find out the already residing database name in the SQL Server.

Before creating a new database, let's check which database already present in my SQL Server

SHOW DATABASES;

The output of the above query is as follows:

Database
cricket
demo
ids
information_schema
mysql
performance_schema
phpadmin
registration
student
student_1
WordPress
SQL Create Database

Before creating a new database, we must learn about the syntax of the create database query.

The syntax of the CREATE DATABASE is as follows:

CREATE DATABASE Database_Name;

Now, we are ready to create new databases in the SQL Server.

Example 1: Write a query to create a new database named javatpoint

CREATE DATABASE javatpoint;

In the above query, we created a new database named javatpoint.

We will execute the SHOW DATABASES query to check whether the javatpoint database is successfully created or not is as follows:

SHOW DATABASES;

The output of the above query is as follows:

Database
cricket
demo
ids
information_schema
javatpoint
mysql
performance_schema
phpadmin
registration
student
student_1
WordPress
SQL Create Database

Example 2: Write a query to create a new database named Mumbai

CREATE DATABASE Mumbai;

In the above query, we created a new database named Mumbai.

We will execute the SHOW DATABASES query to check whether the Mumbai database is successfully created or not is as follows:

SHOW DATABASES;

The output of the above query is as follows:

Database
cricket
demo
demodb
ids
information_schema
javatpoint
Mumbai
mysql
performance_schema
phpadmin
registration
Student
student_1
WordPress
SQL Create Database

CREATE SCHEMA

CREATE SCHEMA is an alternative option or command to create the database. Both query task is the same to create a database in the SQL Server.

The syntax of the CREATE SCHEMA is as follows:

CREATE SCHEMA Schema_Name;

Example 1: Write a query to create a new schema named testDB

CREATE DATABASE testDB;

In the above query, we created a new database named testDB.

We will execute the SHOW DATABASES query to check whether the testDB database is successfully created or not is as follows:

SHOW DATABASES;

The output of the above query is as follows:

Database
Cricket
Demo
Demodb
Ids
information_schema
Javatpoint
Mumbai
mysql
performance_schema
phpadmin
registration
student
student_1
testdb
WordPress
SQL Create Database

Example 2: Write a query to create a new schema named Employee

CREATE DATABASE Employee;

In the above query, we created a new database named Employee.

We will execute the SHOW DATABASES query to check whether the Employee database is successfully created or not is as follows:

SHOW DATABASES;

The output of the above query is as follows:

Database
Cricket
Demo
Demodb
Employee
Ids
information_schema
Javatpoint
Mumbai
mysql
performance_schema
phpadmin
registration
student
student_1
testdb
WordPress
SQL Create Database

SHOW DATABASES

The SHOW DATABASES is the one way to see the already existing database. Another way is to SHOW CREATE DATABASE command. But, in the SHOW DATABASES, all the database's name is listed, and in the SHOW CREATE DATABASE query, only the database that wants to look is displayed with the following character set of the database name.

Example 1: Execute a query to show the employee database.

SHOW CREATE DATABASE Employee;

In the above query, we show the employee database with the character set information of the employee database.

The output of the above query is as follows:

DatabaseCreate Database
EmployeeCREATE DATABASE `employee` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
SQL Create Database

Since when we created the database employee, we didn't specify the character set with the database. So, the default character set is displayed in the employee database.

We created a database without adding arguments or parameters in the above examples.

IF NOT EXISTS

IF NOT EXISTS is one of the parameters which is used while creating the database in the SQL Server. Suppose there are several databases in the SQL Server, and we don't know whether the database is creating resides in the SQL Server. The purpose of the IF NOT EXISTS parameter is to check whether the database we are going to create is already residing or not in the SQL Server.

The syntax of the CREATE DATABASE with the IF NOT EXISTS parameter is as follows:

CREATE DATABASE IF NOT EXISTS DataBase_Name;

Example 1: Write a query to create a new database with the IF NOT EXISTS parameter.

CREATE DATABASE IF NOT EXISTS World;

In the above query, we execute the create database query with the IF NOT EXISTS parameter database name World. If the database is already residing on the server, then the warning message will be displayed or will create the database successfully.

The output of the above query is as follows:

Database
Cricket
Demo
Demodb
Employee
Ids
information_schema
Javatpoint
Mumbai
mysql
performance_schema
phpadmin
registration
student
student_1
testdb
WordPress
world
SQL Create Database

There was no database named world on the server. So, created the Database name World successfully. Now, in the last example, we will create a database which already resides in the database and check for the result.

Example 2: Write a query to create a new database with the IF NOT EXISTS parameter.

CREATE DATABASE IF NOT EXISTS Employee;

In the above query, we execute the create database query with the IF NOT EXISTS parameter database name Employee. If the database is already residing on the server, a warning message will display and create the database successfully.

The output of the above query is as follows:

SQL Create Database

As we can see in the output 1 warning is shown, this is because database name employee is already residing in the server.



ADVERTISEMENT
ADVERTISEMENT