Summary -

In this topic, we described about the Select Statement with detailed example.

SELECT statement is used to retrieve data from existing one or more tables in database. The retrieved data is stored in the form of result table. It is also known as result-set. The mostly used command in SQL was SELECT statement. SELECT statement is used with various clauses.

Syntax -

SELECT Expressions
FROM Table_name
[WHERE clause ]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]; 
  • Expressions – Specifies Column names and Aggregate functions on columns which are going to retrieve.
  • Table_name – Specifies name of the table in database where user want to fetch data.
  • WHERE clause - It is optional in SELECT statement. WHERE clause specifies the condition that is used to select the data from table.
  • GROUP BY clause - It is optional in SELECT statement and used to group the columns with similar kind of data. Used with aggregate functions in SELECT statement.
  • HAVING clause - It is optional in SELECT statement and used to select the rows that are matching with the specified condition in HAVING clause. It is used with aggregate functions in SELECT Statement.
  • ORDER BY clause - It is optional in SELECT statement and used to sort the data in result-set either ascending or descending order.

Examples

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetching all rows from table.

Requirement - Fetching all rows from employee_details table. The query was as follows -

SELECT * FROM employee_details; 

By executing above query, we can get all rows present from the employee_details table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetching specified columns from table.

Requirement - Fetching emp_id, emp_name, designation columns data from employee_details table. The query was as follows –

 SELECT emp_id, emp_name, designation FROM employee_details; 

By executing above query, we can get all rows data for the emp_id, emp_name, designation columns from the employee_details table. The output was as follows -

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst
006 Employee6 Analyst
007 Employee7 Clerk
008 Employee8 Salesman
009 Employee9 Salesman

Scenario – Fetching rows from table by using WHERE clause.

Requirement– Fetch the employee details who are "Analyst". The query was as follows –

SELECT * FROM employee_details WHERE designation = 'Analyst'; 

By executing above query, we can get all the details of employees who are "Analyst" from employee_details table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000

Scenario – Fetching data from table by using GROUP BY clause.

Requirement – Get employees count depending on the department from employee_details table. The query was as follows –

SELECT COUNT(emp_id) AS 'Total employees', dept_id
FROM employee_details GROUP BY dept_id; 

By executing above query, we can get the details of total number of employees in different departments from employee_details table. The output was as follows –

Total employees dept_id
5 1000
4 2000

Scenario – Fetching data from table by using HAVING clause.

Requirement – Get total number of employees that are more than one based on designation. The query was as follows –

SELECT COUNT(emp_id) AS 'Total employees', designation
FROM employee_details GROUP BY designation HAVING COUNT(emp_id) > 1; 

By executing above query, we can get the details of total number of employees based on different designations from employee_details table. The output was as follows –

Total employees designation
2 Analyst
2 Director
2 Manager
2 Salesman

Scenario – Fetching data from table by using ORDER BY clause.

Requirement– Get the employee details of department 1000 and order by salary descending. The query was as follows –

SELECT * FROM employee_details 
WHERE dept_id = 1000 
ORDER BY salary DESC; 

By executing above query, we can get all the details of employees whose dept_id is 1000 and descending order of salary from employee_details table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000