SQL DDL
As we discussed earlier, Data Definition Language is used to define and create a new database or tables. Let's discuss them in detail in this section.
Title | Explanation |
|---|---|
Create | Create Command is use to create a new table or database for the first time. |
Alter | Alter is use to change and modify the existing table or column with new features. |
Drop | Drop is use to drop and delete an entire table or database along with his structure and you can't rollback to previous state once you drop the table. It is slower than Truncate. |
Rename | Rename is use to rename the table, objects or database. |
Truncate | Truncate works similar to drop command with only difference is that the structure of the table is not deleted only the values are deleted and it is faster than drop statement. |
Instead of learning all the commands separately, we will take one real time problem and will solve it along with learning the sql commands.
Super Market Sales
-
Suppose a supermarket owner come to us and tell us that it's getting really hard for him to store the records of all products and keep track of the sales. He asked us for the suggestions.
-
He showed us the way he is storing his product sales data.
-
As we can see, the way he stores data is not he optimal way. Redundancy is increased and the things are bit complicated.
-
So to optimize the things we discuss and come with some solutions.
-
The best suggestion could be to store the data in the RDBMS like MySQL.
-
But for that we first need the schema or structure of how our database is going to look, so we are going to hire a professional sql database designer who is going to get the schema and relationship between the tables.
-
Hiring a professional database designer is very important because it can help us save lot of time in future.
-
He provides us the optimal solutions for data storage.
-
Best way to link the tables and get some good connections.
As we can see, this is more optimize way to store the data. At first instance, it might seem complicated but once we break down the database and understand the relationships between the tables. How RDBMS helps and makes our life easier compare to the traditional way of storing a data.
Let's start our journey of creating a database and implement all the things which we had discussed in our RDBMS.
Creating a Database
Our first step is use to create a database.
Syntax
CREATE DATABASE database_name;
Database name should be unique, it should not be lengthy and give relevant names.
EXAMPLE
CREATE DATABASE SuperMarketSales;
Creating a Table
Our next step is use to create a table.
Syntax
CREATE TABLE table_name
(
column_name_1 datatype constraints,
column_name_2 datatype constraints,
---
---
column_name_n datatype constraints
);
While creating a table we have to face the name of the table, name of columns along with the data types and constraints.
EXAMPLE
CREATE TABLE Customers
(
customer_id int primary key,
first_name char(20),
last_name char(20),
phone_number int unique,
number_of_products int
);
Alter a Table
Tables can be altered and modify if required with the help of a alter command.
Syntax
ALTER TABLE table_name
ADD column_name datatype;
Instead of adding a new column, we can drop, rename and modify the existing column using the above syntax.
EXAMPLE
ALTER TABLE Customers
ADD Date_of_birth DATE;
Drop a Table
Drop command is use to drop a table or a whole database.
Syntax
DROP TABLE table_name;
Drop is slower compared to truncate and the structure of the table is dropped when we use the drop statement and we cannot roll back once the table is dropped.
EXAMPLE
DROP TABLE Customers;
Rename a Table
We can rename a table or database to a new name using a rename command
Syntax
RENAME TABLE old_table_name to new_table_name;
rename is also possible with the help of alter command.
EXAMPLE
RENAME TABLE Customers to Consumers;
Truncate a Table
Truncate is similar to drop but the difference is that it is faster and the structure of the table is mantained.
Syntax
TRUNCATE TABLE table_name;
Logic of Truncate works similar to the delete command without where clause. which we are going to see in the DML Commands