SQL Create Database | SQL Drop & Select Database
SQL(Structured Query Language) is used for managing the relational DBMS. SQL provides us many functionalities using queries and commands like Create, Drop, and Select database query.
We have various types of queries available to us. Create and Drop belong to the DDL(Data Definition Language) type of queries, and Select belongs to DML(Data Manipulation Language) type of queries.
Create query helps us create a database, while drop deletes the data stored in the table, and Select query helps us get a glimpse of the data stored and provides a view with various applied conditions.
Stay updated with latest technology trends
Join DataFlair on Telegram!!
What is a Database?
We have large amounts of data being generated every day and to utilize the generated data, we need to store the data properly.
We use databases to store the data being produced. In other words, we can say that a database is a collection of large amounts of data.
A database is made up of many tables and data inserted into those tables.
Rules for creating a Database
While creating a database, we need to follow some rules:
1. The database that we are creating should have a unique name so that it can be identified easily.
2. The name of the database can be 128 characters long, not more than that.
3. Our create query runs in the auto-commit mode.
Why do we need to Create, Drop, and Select Query?
Data is increasing day by day, and billions of new data are created and thus we need to have control to continuously capture the data in the database.
With the SQL queries, we are able to do that.
With the Create query, we create multiple tables that are then used to store the data and perform various studies on it.
In today’s time, almost all the major sectors use data for exploratory analysis to predict the growth of services being provided by them.
Some of the significant examples being :
- Health Care Sector
- E-commerce Sector
- Financial Firms
This dependency on data requires efficient management and handling of data being produced, and here, SQL queries come in handy.
Similarly, we use Select query to get a glimpse of the data stored in our database, and Drop query is used to drop the data stored in the table with the specified conditions.
SQL Create Query
Create query comes under the DDL(Data Definition Language) in SQL.
DDL queries help in defining the database structure or the database schema. It is also known as Data Storage and Definition Language.
Create helps us define the implementation details of the schema which are hidden from the user and are accessible only to the database administrator.
Create Query is used to create a table in the database.
Syntax of Create Query:
CREATE Table tableName ( columnName1 datatype, columnName2 datatype, columnName3 datatype, columnName4 datatype, columnName5 datatype, …... );
Let us consider an example:
Let us create a table called DataFlair_Employee, which holds the details of all the current employees working in DataFlair. The details must include :
- Name of the employee
- Post of the Employee
- Email of the Employee
- Age of the Employee
- Contact Number of the Employee
Thus, we need to create five columns in our database, i.e. :
- name_emp – Name of the employee
- post_emp – Post of the Employee
- email – Email of the Employee
- age – Age of the Employee
- co_num – Contact Number of the Employee
Let us now write the query to create our table DataFlair_Employee, we have decided the column number and names in advance (stated in bold):
CREATE TABLE DataFlair_Employee ( name_emp varchar(50), post_emp varchar(50), email varchar(50), age int, co_num varchar(10) );
Populating the database :
Insert into DataFlair_Employee (name_emp , post_emp , email , age , co_num) Values ('Ram' , "Intern", 'firstname.lastname@example.org', 21 , '7415823691' ), ('Shyam', "Manager", 'email@example.com' , 25 , '7586941235'), ('Ria', "Analyst" , 'firstname.lastname@example.org', 23 , '7415823691'), ('Kavya', "Senior Analyst" , 'email@example.com', 31 , '9874586321'), ('Aman', "Database Operator",' firstname.lastname@example.org' , 26 , '9864752431') ;
To view the created table :
Select * from DataFlair_Employee;
This is how we create a table using the create statement.
SELECT Query in SQL
Select query comes under the DML (Data Manipulation Language) query of SQL.
DML queries help us to manipulate and edit data stored in a database. Select query is used to take a peek into the data stored in the database and to get a glimpse of the type of data stored in the database.
Select query is required to access the data stored in our database and is used to check data beforehand using any operation on the database for data cleaning and analysis.
Syntax of SELECT Query :
SELECT * FROM tableName ;
SELECT column1 , column2 , ….. FROM tableName ;
SELECT column1 , column2 , …… FROM tableName WHERE condition;
Let us view the table DataFlair_Employee we created above.
1. First, let us view all the data stored in the table DataFlair_Employee.
SELECT * FROM DataFlair_Employee ;
2. Query to view only a few units of data stored in the table.
We can restrict the number of rows we want to see in the data, i.e. we can restrict our output table using the LIMIT keyword.
SELECT name_emp , age FROM DataFlair_Employee LIMIT 2 ;
Here we get the two rows in our output as we have set the LIMIT to 2, and only the column’s passed in the query are returned in the output.
3. Query to get data according to specified conditions
Consider we want the data of employees who are above the age of 22, then we can do so by the following query.
SELECT * FROM DataFlair_Employee Where age > 22;
Here, we get the details of all the employees whose age is more than 22 years.
DROP Query in SQL
DROP query is used to delete the table on which the drop command is executed. It deletes the table definition and all its column definitions and restrictions.
DROP TABLE tableName ;
Let us try deleting our existing table, i.e. DataFlair_Employee table. The query for the same will be as follows:
Drop Table DataFlair_Employee;
We should be very careful when we drop a table because once a table is dropped, the table can’t be recovered back.
We have seen how we can create a database using the Create query and view the database using the Select query, in multiple formats using the Limit and the Where clause.
The Limit clause is used to view chunks of the database and where clause is used to get data according to the conditions provided by the user.
At last, we see how to drop a table, but we should be very cautious when we drop a table as once this action is performed, we can’t reverse it.