Dark Mode
Image

SQL Tutorial

SQL Database

SQL Injection

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

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

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:

NSERT 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

Comment / Reply From