Create Database in SQL
- Create database in SQL is one of the basic database management operations.
- Database creating allows you to store, manage, and query data in a standarized way.
- SQL (Structured Query Language) is the standard language used to interact with the relational databases.
- This tutorial is designed to provide a step-by-step guide on how the database, tables, and relationship are created and to explain some of the key concepts related to the create database in SQL using the example.
1. Introduction to Create Database in SQL
- A series of tables constitutes a relational database where rows and columns are used for storing data.
- SQL consists of a series of functions to interact with the database as well, which are, create databases, find the table structure and insert data to the table.
- CREATE DATABASE: This command is used to make a database.
- CREATE TABLE: This is a command that used to create a table in the database.
- ALTER TABLE: This is one of the commands used to edit a table, such as insert or delete columns.
- DROP DATABASE: This command deletes a whole database.
- DROP TABLE: This command removes a specific table from the database.
2. Creating a Database
- This is how you can create a database using a SQL “CREATE DATABASE” statement.
CREATE DATABASE EmployeeDB;
- This SQL command will allow you to add a new database under a name called EmployeeDB. You can mention the database name with CREATE DATABASE and the database will be created in SQL.
Checking Database Creation
- The SHOW DATABASES command is used to check the list of all databases that have been created after the database was made.
SHOW DATABASES;
- All the databases that are in the system, including the one you just created, will be displayed after this command.
3. Using the Database
- Once the database is created, you must change it to create tables or impose data.
- You need to be able to use the USE command:
USE EmployeeDB;
- At this point, EmployeeDB is the active database, and from now on, all commands will refer to it only.
4. Creating Tables
- Tables are the place where the information that you want to write will be stored.
- A table includes rows and columns.
- A column in each table signifies the type of data like name, ID, or date, and each row signifies a record.
- The CREATE TABLE command should be provided to built tables as the whole in the database.
- Here’s a sample code snippet creating a table for storing employee information:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
HireDate DATE,
Salary DECIMAL(10, 2)
);
Explanation of the Table Definition:
- EmployeeID INT PRIMARY KEY: The EmployeeID column having the data type INT (integer).
- FirstName VARCHAR(50): The column holds labels for the first name of the employee, containing an available VARCHAR(50) character with up to 50 items.
- LastName VARCHAR(50): Moreover, this forms the column for the employee’s last name.
- DateOfBirth DATE: The column supplies the employee’s birthday information.
- HireDate DATE: This column documents the hiring date of the employee.
- Salary DECIMAL(10, 2): This column records the highest employee salary, having 10 digits in total, and 2 of them being a reference for cents (cents).
5. Inserting Data into Tables
- After creating a table, you can put data into it by using the INSERT INTO instruction. Here is an example:
INSERT INTO Employees (EmployeeID, FirstName, LastName, DateOfBirth, HireDate, Salary)
VALUES (1, 'John', 'Doe', '1985-07-12', '2020-01-15', 60000.00);
- This command is used to create a new employee record in the table Employees. Each value is linked to a column in the table.
6. Querying Data
- Once the information is stored, one can get it back through the use of the SELECT instruction. As an instance:
SELECT*FROM Employees;
- This will fetch all columns (*) for all records that exist in the Employee table.
- if you want to filter the results;, you can use the WHERE clause. For example, to get the details of employees with a salary greater than 50,000:
SELECT*FROM Employees
WHERE Salary >50000;
7. Modifying Tables
- If data changes over time, you may need to restructure the table.
- For an instance,
- if you want to add another column to the Employees table, you can use the ALTER TABLE command:
ALTER TABLE Employees
ADD Email VARCHAR(100);
- Adding this command will create a new column Email to the Employees table.
8. Constraints in Table Creation
Constraints are defined as the rules that are applied to the columns of a table in order to guarantee the precision as well as the reliability of the information. Some common types of constraints are:
- PRIMARY KEY: Uniquely identifies each record/table in the table.
- FOREIGN KEY: Ensures referential integrity by linking columns in one table to the primary key of another table.
- NOT NULL: This one is the restriction which forces the user to select the value, other than NULL, the NULL is the default value of the field, and every record must contain a value and is always unique in the database.
- UNIQUE: Ensures that all values in a column are unique.
Let us consider the case of the following:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL,
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);
For the above statement:
- DepartmentID is a primary key.
- DepartmentName is a NOT NULL field.
- ManagerID is a foreign key that refers to the EmployeeID in the Employees table, thus establishing the Parents-Employees relationship.
9. Deleting Databases and Tables
- Should there be a time when you want to remove a database or a table, you can do so by issuing the DROP command.
- To delete a table:
DROPTABLE Employees;
To delete a database:
DROP DATABASE EmployeeDB;
- The removal of a table might delete not only the specified table but also the child tables and records that are related to the parent table. Therefore, avoid it.