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.<\/p>\n
What is a Database?<\/h3>\n
We have large amounts of data being generated every day and to utilize the generated data, we need to store the data properly.<\/p>\n
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.<\/p>\n
A database is made up of many tables and data inserted into those tables.<\/p>\n
Rules for creating a Database<\/h3>\n
While creating a database, we need to follow some rules:<\/p>\n
1. The database that we are creating should have a unique name so that it can be identified easily.
\n2. The name of the database can be 128 characters long, not more than that.
\n3. Our create query runs in the auto-commit mode.<\/p>\n
Why do we need to Create, Drop, and Select Query?<\/h3>\n
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.<\/p>\n
With the SQL queries, we are able to do that.<\/p>\n
With the Create query, we create multiple tables that are then used to store the data and perform various studies on it.<\/p>\n
In today\u2019s time, almost all the major sectors use data for exploratory analysis to predict the growth of services being provided by them.<\/p>\n
Some of the significant examples being :<\/p>\n
- \n
- Health Care Sector<\/li>\n
- E-commerce Sector<\/li>\n
- Financial Firms<\/li>\n<\/ul>\n
This dependency on data requires efficient management and handling of data being produced, and here, SQL queries come in handy.<\/p>\n
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.<\/p>\n
SQL Create Query<\/h3>\n
Create query comes under the DDL(Data Definition Language) in SQL.<\/p>\n
DDL queries help in defining the database structure or the database schema. It is also known as Data Storage and Definition Language.<\/p>\n
Create helps us define the implementation details of the schema which are hidden from the user and are accessible only to the database administrator.<\/p>\n
Create Query is used to create a table in the database.<\/p>\n
Syntax of Create Query:<\/strong><\/p>\n
CREATE Table tableName (\r\ncolumnName1 datatype,\r\ncolumnName2 datatype,\r\ncolumnName3 datatype,\r\ncolumnName4 datatype,\r\ncolumnName5 datatype,\r\n\u2026...\r\n);\r\n<\/pre>\n
Let us consider an example:<\/p>\n
Let us create a table called DataFlair_Employee,<\/strong> which holds the details of all the current employees working in DataFlair. The details must include :<\/p>\n
- \n
- Name of the employee<\/li>\n
- Post of the Employee<\/li>\n
- Email of the Employee<\/li>\n
- Age of the Employee<\/li>\n
- Contact Number of the Employee<\/li>\n<\/ul>\n
Thus, we need to create five columns in our database, i.e. :<\/p>\n
- \n
- name_emp –<\/strong> Name of the employee<\/li>\n
- post_emp –<\/strong> Post of the Employee<\/li>\n
- email –<\/strong> Email of the Employee<\/li>\n
- age –<\/strong> Age of the Employee<\/li>\n
- co_num –<\/strong> Contact Number of the Employee<\/li>\n<\/ul>\n
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):<\/p>\n
CREATE TABLE DataFlair_Employee (\r\nname_emp varchar(50),\r\npost_emp varchar(50),\r\nemail varchar(50),\r\nage int,\r\nco_num varchar(10)\r\n);\r\n<\/pre>\n
![\"SQL](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Create-table.png\")
<\/a><\/p>\n![\"View](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/view-the-created-table.png\")
<\/a><\/p>\nPopulating the database :<\/strong><\/p>\n
Insert into DataFlair_Employee (name_emp , post_emp , email , age , co_num)\r\nValues ('Ram' , \"Intern\", 'ram@dataflair.com', 21 , '7415823691' ),\r\n('Shyam', \"Manager\", 'shyam@dataflair.com' , 25 , '7586941235'),\r\n('Ria', \"Analyst\" , 'ram@dataflair.com', 23 , '7415823691'),\r\n('Kavya', \"Senior Analyst\" , 'kavya@dataflair.com', 31 , '9874586321'),\r\n('Aman', \"Database Operator\",' rish@dataflair.com' , 26 , '9864752431') ;\r\n<\/pre>\nTo view the created table :<\/strong>
\nSelect * from DataFlair_Employee;<\/p>\n![\"view](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/view-inserted-data.png\")
<\/a><\/p>\nThis is how we create a table using the create statement.<\/p>\n
SELECT Query in SQL<\/h3>\n
Select query comes under the DML (Data Manipulation Language) query of SQL.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Syntax of SELECT Query :<\/strong><\/p>\n
SELECT * FROM tableName ;<\/pre>\n
SELECT column1 , column2 , \u2026.. FROM tableName ;\r\n<\/pre>\n
SELECT column1 , column2 , \u2026\u2026 FROM tableName WHERE condition;<\/pre>\n
Example :<\/strong>
\nLet us view the table DataFlair_Employee we created above.<\/p>\n1. First, let us view all the data stored in the table DataFlair_Employee.<\/strong><\/p>\n
SELECT * FROM DataFlair_Employee ;<\/pre>\n
![\"select](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/select-query-to-view-all-data.png\")
<\/a><\/p>\n2. Query to view only a few units of data stored in the table.<\/strong><\/p>\n
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.<\/p>\n
SELECT name_emp , age FROM DataFlair_Employee LIMIT 2 ;<\/pre>\n
<\/p>\n
![\"select](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/select-query-with-limit.png\")
<\/a><\/p>\nHere we get the two rows in our output as we have set the LIMIT to 2, and only the column\u2019s passed in the query are returned in the output.<\/p>\n
3. Query to get data according to specified conditions<\/strong><\/p>\n
Consider we want the data of employees who are above the age of 22, then we can do so by the following query.<\/p>\n
SELECT * FROM DataFlair_Employee Where age > 22;<\/pre>\n
- post_emp –<\/strong> Post of the Employee<\/li>\n
- name_emp –<\/strong> Name of the employee<\/li>\n