SQL Select Statement

What Is SQL SELECT Statement?

The SQL SELECT statement is the cornerstone of querying databases. It allows you to extract specific data from one or more database tables or views with precision. Whether you're a budding data analyst or a seasoned database administrator, mastering the SELECT statement is essential.

Syntax of SQL SELECT Statement

Let's start with the fundamental syntax:

SELECT Column_Name_1, Column_Name_2, ..., Column_Name_N
FROM Table_Name;

Here, Column_Name_1, Column_Name_2, and so on represent the names of the columns in the table from which you want to retrieve data. This syntax serves as the backbone of countless SQL queries.

But what if you need all the rows and fields from a table? You can use the following simplified syntax:

SELECT * FROM table_name;

This nifty wildcard (*) retrieves all available data, sparing you from listing each column individually.

Examples of SQL SELECT Statement

Let's put theory into practice with some real-world examples.

Example 1: Retrieving Student Records

Imagine you're managing student data. You can create a table like this:

CREATE TABLE Student   
(  
Id INT PRIMARY KEY,    
FirstName VARCHAR(20),     
Age INT NOT NULL,  
Marks INT NOT NULL
);

To populate it, use this query:

INSERT INTO Student VALUES
(01, 'Amal',18, 89),   
(02, 'Kamal',19, 93),  
(03, 'Sunil',20, 89),    
(04, 'Nimal',19, 78),  
(05, 'Amantha',20, 75),  
(06, 'Sachin',19, 51),  
(07, 'Virat',20, 62);

Now, let's retrieve all the values:

SELECT * FROM Student_Records;

You'll get a result set with student details.

Example 2: Selecting Specific Columns

Suppose you only need specific columns:

SELECT Id, FirstName, Marks FROM Student;

This query fetches only the desired columns, simplifying your data extraction process.

SQL SELECT Statement with WHERE Clause

The WHERE clause pairs perfectly with the SELECT statement when you need to filter rows based on specific conditions.

Syntax of SQL SELECT with WHERE Clause

SELECT * FROM Name_of_Table WHERE [condition];

Example: Filtering Employee Details

Let's create an Employee table and insert some data:

CREATE TABLE Employee 
(  
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,  
Employee_Name VARCHAR(50),  
Employee_Salary INT NOT NULL,   
Employee_Penalty INT NOT NULL  
);

Populate it:

INSERT INTO Employee (Employee_ID, Employee_Name, Employee_Salary) VALUES
(101, 'Amal',25000),  
(102, 'Kamal',35000),   
(103, 'Nimal',35000),  
(104, 'Sunil',22000);

Now, let's retrieve employees with a penalty:

SELECT * FROM Employee WHERE Employee_Salary = 35000;

Using this query we can retrieve employees whose salary equal to 35000.

SQL SELECT Statement with GROUP BY Clause

The GROUP BY clause enhances your SELECT statement by aggregating data based on specific columns.

Syntax of SQL SELECT 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;

You can summarize data using aggregate functions like COUNT, SUM, AVG, etc.

Example: Counting Buses

Let's create a Bus_Details table and insert some bus data:

CREATE TABLE Bus_Details  
(  
Bus_Number INT PRIMARY KEY,  
Bus_Name VARCHAR(50),  
Bus_Price INT NOT NULL
);

Add Buses to the table:

INSERT INTO Bus_Details (Bus_Number, Bus_Name, Bus_Price)   
VALUES
(2578, 'Tata', 1500000),  
(9258, 'Ashok Layland', 3000000),   
(8233, 'Ashok Layland', 900000),  
(6214, 'Mahendra', 1000000);

Now, let's find out how many Buses share the same price:

SELECT COUNT(Bus_Name), Bus_Price FROM Bus_Details GROUP BY Bus_Price;

This query groups Buses by price, giving you valuable insights into pricing trends.

SQL SELECT Statement with HAVING Clause

The HAVING clause complements GROUP BY by filtering aggregated results.

Syntax of SQL SELECT 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 condition;

It allows you to set conditions for aggregated data.

Example: Employee Salaries

Let's create an EmpHaving table and insert some employee data:

CREATE TABLE EmpHaving  
(  
EmployeeId INT PRIMARY KEY,  
EmployeeName VARCHAR(50),  
EmployeeSalary INT NOT NULL,  
EmployeeCity VARCHAR(50) 
);

Populate it with employee records:

INSERT INTO EmpHaving (EmployeeId, Employee_Name, EmployeeSalary, EmployeeCity)   
VALUES
(1000, 'Amal', 20000, 'Galle'),  
(1001, 'Kamal', 40000, 'Matara'),   
(1002, 'Sunil', 80000, 'Hambantota'),  
(1003, 'Amali', 20000, 'Colombo'),  
(1004, 'Kelum', 50000, 'Jaffna');

Now, let's find out the total salary of employees earning more than 5000 in each city:

SELECT SUM(EmployeeSalary), EmployeeCity FROM EmpHaving GROUP BY EmployeeCity HAVING SUM(EmployeeSalary) > 5000;

This query helps identify cities where employees earn well above a certain threshold.

SQL SELECT Statement with ORDER BY Clause

The ORDER BY clause allows you to sort query results.

Syntax of SQL SELECT 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];

You can arrange data in ascending (ASC) or descending (DESC) order.

Example: Sorting Car Prices

Consider an Car_Detail table:

CREATE TABLE Car_Detail  
(  
Id INT NOT NULL,  
CarName VARCHAR(50),  
Price INT
);

Insert Car_Detail records:

INSERT INTO Car_Detail (Id, CarName, Price)   
VALUES
(201, 'AQUA', 20000),  
(202, 'FIT', 15000),   
(203, 'PREMIO', 80000),  
(204, 'CHR', 90000),  
(205, 'PRADO', 50000);

To sort car prices in descending order:

SELECT * FROM Employee_Order ORDER BY Salary DESC;