SQL SELECT Statement
The SELECT statement is the most commonly used command in Structured Query Language. It is used to access the records from one or more database tables and views. It also retrieves the selected data that follow the conditions we want.
By using this command, we can also access the particular record from the particular column of the table. The table which stores the record returned by the SELECT statement is called a result-set table.
Syntax of SELECT Statement in SQL
In this SELECT syntax, Column_Name_1, Column_Name_2, ….., Column_Name_N are the name of those columns in the table whose data we want to read.
If you want to access all rows from all fields of the table, use the following SQL SELECT syntax with * asterisk sign:
Examples of SELECT Statement in SQL
Here, we took the following two different SQL examples which will help you to execute the SELECT statement for retrieving the records:
Example 1:
Firstly, we have to create the new table and then insert some dummy records into it.
Use the following query to create the Student_Records table in SQL:
The following query inserts the record of intelligent students into the Student_Records table:
The following SQL query displays all the values of each column from the above Student_records table:
The output of the above query is:
| Student_ID | First_Name | Address | Age | Percentage | Grade |
|---|---|---|---|---|---|
| 201 | Akash | Delhi | 18 | 89 | A2 |
| 202 | Bhavesh | Kanpur | 19 | 93 | A1 |
| 203 | Yash | Delhi | 20 | 89 | A2 |
| 204 | Bhavna | Delhi | 19 | 78 | B1 |
| 205 | Yatin | Lucknow | 20 | 75 | B1 |
| 206 | Ishika | Ghaziabad | 19 | 91 | C1 |
| 207 | Vivek | Goa | 20 | 80 | B2 |
Example 2:
The following query displays the values of particular column from the above Student_Record table:
| Student_ID | Age | Percentage | Grade |
|---|---|---|---|
| 201 | 18 | 89 | A2 |
| 202 | 19 | 93 | A1 |
| 203 | 20 | 89 | A2 |
| 204 | 19 | 78 | B1 |
| 205 | 20 | 75 | B1 |
| 206 | 19 | 91 | C1 |
| 207 | 20 | 80 | B2 |
SELECT Statement with WHERE clause
The WHERE clause is used with SELECT statement to return only those rows from the table, which satisfy the specified condition in the query.
In SQL, the WHERE clause is not only used with SELECT, but it is also used with other SQL statements such as UPDATE, ALTER, and DELETE statements.
Syntax of SELECT Statement with WHERE clause
In the syntax, we specify the condition in the WHERE clause using SQL logical or comparison operators.
Example of SELECT Statement with WHERE clause
Firstly, we have to create the new table and then insert some dummy records into it.
Use the following query to create the Employee_Details table in SQL:
The following INSERT query inserts the record of employees into the Employee_Details table:
The following SELECT query shows the data of the Employee_Details table:
| Employee_Id | Emp_Name | Emp_City | Emp_Salary | Emp_Panelty |
|---|---|---|---|---|
| 101 | Anuj | Ghaziabad | 25000 | 500 |
| 102 | Tushar | Lucknow | 29000 | 1000 |
| 103 | Vivek | Kolkata | 35000 | 500 |
| 104 | Shivam | Goa | 22000 | 500 |
The following query shows the record of those employees from the above table whose Emp_Panelty is 500:
This SELECT query displays the following table in result:
| Employee_Id | Emp_Name | Emp_City | Emp_Salary | Emp_Panelty |
|---|---|---|---|---|
| 101 | Anuj | Ghaziabad | 25000 | 500 |
| 103 | Vivek | Kolkata | 35000 | 500 |
| 104 | Shivam | Goa | 22000 | 500 |
SQL SELECT Statement with GROUP BY clause
The GROUP BY clause is used with the SELECT statement to show the common data of the column from the table:
Syntax of SELECT Statement with GROUP BY clause
Example of SELECT Statement with GROUP BY clause
Use the following query to create the Cars_Details table:
The following INSERT query inserts the record of cars into the Cars_Details table:
The following SELECT query displays the values in the output:
| Car_Number | Car_Name | Car_Amount | Car_Price |
|---|---|---|---|
| 2578 | Creta | 3 | 1000000 |
| 9258 | Audi | 2 | 900000 |
| 8233 | Venue | 6 | 900000 |
| 6214 | Nexon | 7 | 1000000 |
The following SELECT with GROUP BY query lists the number of cars of the same price:
The output of above GROUP BY query is shown below:
Output:
| Count (Car_Name) | Car_Price |
|---|---|
| 2 | 1000000 |
| 2 | 900000 |
SQL SELECT Statement with HAVING clause
The HAVING clause in the SELECT statement creates a selection in those groups which are defined by the GROUP BY clause.
Syntax of SELECT Statement with HAVING clause
Example of SELECT Statement with HAVING clause
Let’s create the Employee_Having table in SQL using the below CREATE command:
The following INSERT query inserts the record of employees into the Employee_Having table:
The following SELECT query shows the values of Employee_Having table in the output:
| Employee_Id | Employee_Name | Employee_Salary | Employee_City |
|---|---|---|---|
| 201 | Jone | 20000 | Goa |
| 202 | Basant | 40000 | Delhi |
| 203 | Rashet | 80000 | Jaipur |
| 204 | Anuj | 20000 | Goa |
| 205 | Sumit | 50000 | Delhi |
The following query shows the total salary of those employees having more than 5000 from the above Employee_Having table:
This HAVING query with SELECT statement shows the following table:
Output:
| SUM (Employee_Salary) | Employee_City |
|---|---|
| 90000 | Delhi |
| 80000 | Jaipur |
SELECT Statement with ORDER BY clause
The ORDER BY clause with the SQL SELECT statement shows the records or rows in a sorted manner.
The ORDER BY clause arranges the values in both ascending and descending order. Few database systems arrange the values of column in ascending order by default.
Syntax of SELECT Statement with ORDER BY clause
Example of SELECT Statement with ORDER BY clause in SQL
The following INSERT query inserts the record of employees into the Employee_Having table:
The following SELECT query shows the values of the table in the output:
| Id | FirstName | Salary | City |
|---|---|---|---|
| 201 | Jone | 20000 | Goa |
| 202 | Basant | 15000 | Delhi |
| 203 | Rashet | 80000 | Jaipur |
| 204 | Anuj | 90000 | Goa |
| 205 | Sumit | 50000 | Delhi |
The following query sorts the salary of employees in descending order from the above Employee_Order table:
This SQL query shows the following table in result:
Output:
| Emp_Id | Emp_Name | Emp_Salary | Emp_City |
|---|---|---|---|
| 204 | Anuj | 90000 | Goa |
| 203 | Rashet | 80000 | Jaipur |
| 205 | Sumit | 50000 | Delhi |
| 201 | Jone | 20000 | Goa |
| 202 | Basant | 15000 | Delhi |