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
SELECT Column_Name_1, Column_Name_2, ....., Column_Name_N FROM Table_Name; 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:
SELECT * FROM table_name; 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:
CREATE TABLE Student_Records
(
Student_Id Int PRIMARY KEY,
First_Name VARCHAR (20),
Address VARCHAR (20),
Age Int NOT NULL,
Percentage Int NOT NULL,
Grade VARCHAR (10)
) ;The following query inserts the record of intelligent students into the Student_Records table:
INSERT INTO Student VALUES (201, Akash, Delhi, 18, 89, A2),
(202, Bhavesh, Kanpur, 19, 93, A1),
(203, Yash, Delhi, 20, 89, A2),
(204, Bhavna, Delhi, 19, 78, B1),
(05, Yatin, Lucknow, 20, 75, B1),
(206, Ishika, Ghaziabad, 19, 51, C1),
(207, Vivek, Goa, 20, 62, B2);The following SQL query displays all the values of each column from the above Student_records table:
SELECT * FROM Student_Records; 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:
SELECT Student_Id, Age, Percentage, Grade FROM Employee; | 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
SELECT * FROM Name_of_Table WHERE [condition]; 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:
CREATE TABLE Employee_Details
(
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT NULL,
Emp_Panelty INT NOT NULL
) ;The following INSERT query inserts the record of employees into the Employee_Details table:
INSERT INTO Employee_Details (Employee_ID, Emp_Name, Emp_City, Emp_Salary, Emp_Panelty) VALUES (101, Anuj, Ghaziabad, 25000, 500),
(102, Tushar, Lucknow, 29000, 1000),
(103, Vivek, Kolkata, 35000, 500),
(104, Shivam, Goa, 22000, 500); The following SELECT query shows the data of the Employee_Details table:
SELECT * FROM Employee_Details; | 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:
SELECT * FROM Employee_Details WHERE Emp_Panelty = 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
SELECT column_Name_1, column_Name_2, ....., column_Name_N aggregate_function_name(column_Name2) FROM table_name GROUP BY column_Name1; Example of SELECT Statement with GROUP BY clause
Use the following query to create the Cars_Details table:
CREATE TABLE Cars_Details
(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;The following INSERT query inserts the record of cars into the Cars_Details table:
INSERT INTO Cars_Details (Car_Number, Car_Name, Car_Amount, Car_Price)
VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000); The following SELECT query displays the values in the output:
SELECT * FROM Cars_Details; | 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:
SELECT COUNT (Car_Name), Car_Price FROM Cars_Details GROUP BY Car_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
SELECT column_Name_1, column_Name_2, ....., column_Name_N aggregate_function_name(column_Name_2) FROM table_name GROUP BY column_Name1 HAVING ; Example of SELECT Statement with HAVING clause
Let’s create the Employee_Having table in SQL using the below CREATE command:
CREATE TABLE Employee_Having
(
Employee_Id INT PRIMARY KEY,
Employee_Name VARCHAR (50),
Employee_Salary INT NOT NULL,
Employee_City VARCHAR (50)
) ; The following INSERT query inserts the record of employees into the Employee_Having table:
INSERT INTO Employee_Having (Employee_Id, Employee_Name, Employee_Salary, Employee_City)
VALUES (201, Jone, 20000, Goa),
(202, Basant, 40000, Delhi),
(203, Rashet, 80000,Jaipur),
(204, Aunj, 20000, Goa),
(205, Sumit, 50000, Delhi);The following SELECT query shows the values of Employee_Having table in the output:
SELECT * FROM Employee_Having; | 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:
SELECT SUM (Employee_Salary), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM(Employee_Salary)>5000; 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
SELECT Column_Name_1, Column_Name_2, ....., column_Name_N FROM table_name WHERE [Condition] ORDER BY[column_Name_1, column_Name_2, ....., column_Name_N asc | desc ]; Example of SELECT Statement with ORDER BY clause in SQL
CREATE TABLE Employee_Order
(
Id INT NOT NULL,
FirstName VARCHAR (50),
Salary INT,
City VARCHAR (50)
) ;The following INSERT query inserts the record of employees into the Employee_Having table:
INSERT INTO Employee_Order (Id, FirstName, Salary, City)
VALUES (201, Jone, 20000, Goa),
(202, Basant, 15000, Delhi),
(203, Rashet, 80000,Jaipur),
(204, Aunj, 90000, Goa),
(205, Sumit, 50000, Delhi);The following SELECT query shows the values of the table in the output:
SELECT * FROM Employee_Order; | 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:
SELECT * FROM Employee_Order ORDER BY Emp_Salary DESC; 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 |
Leave a Reply