SQL SELECT Query

The SQL SELECT query is one of the most frequently used commands to retrieve data from a database. It allows users to access and extract specific records based on defined conditions, making it an essential tool for data management and analysis. In this article, we will learn about SQL SELECT statement with query examples and explore advanced techniques for efficient data manipulation and analysis.

Understanding Data Retrieval with SQL SELECT

SQL SELECT is used to fetch or retrieve data from a database. It can either pull all the data from a table or return specific results based on specified conditions. The data returned is stored in a result table. In the SELECT statement, we define the columns we want to display in the query result, making it flexible for different data retrieval needs.

The SELECT clause is the first and one of the last components evaluated in the SQL query process. Before determining the final result set, the system needs to know all possible columns that might be included.

Syntax:

SELECT column1,column2.... FROM table_name ;

Examples of SELECT Statement

To better understand the SQL SELECT statement, let’s start by creating a sample table that we will use for our examples. We will also insert some sample data to make the demonstration more practical.

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

Example 1: Select Specific Columns

In this example, we will demonstrate how to retrieve specific columns from the Customer table. Here we will fetch only CustomerName and LastName for each record.

Query:

SELECT CustomerName, LastName 
FROM Customer;

Output

Example 2: Select All Columns

In this example, we will fetch all the fields from the table Customer:

Query:

 SELECT * FROM Customer;

Output

Example 3: SELECT Statement with WHERE Clause

Suppose we want to see table values with specific conditions then WHERE Clause is used with select statement. In this example, filter customers who are 21 years old.

Query:

SELECT CustomerName 
FROM Customer 
where Age = '21'; 

Output:

Example 4: SELECT with GROUP BY Clause

In this example, we will use SELECT statement with GROUP BY Clause to Group rows and perform aggregation. Here, Count orders per customer.

Query:

SELECT Customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY Customer_id;

Output:

Example 5: SELECT Statement with HAVING Clause

Use HAVING to filter results after grouping. Consider the following database for fetching departments with total salary above 50,000. Use WHERE for row-level filtering, HAVING for group-level filtering.

Query:

SELECT Department, sum(Salary) as Salary
FROM employee
GROUP BY department
HAVING SUM(Salary) >= 50000;  

Output:

Example 6: SELECT Statement with ORDER BY clause in SQL

In this example, we will use SELECT Statement with ORDER BY clause. Here, Sort results by Age in descending order.

Query:

SELECT * FROM Customer ORDER BY Age DESC;  

Output:

Tips to Master SELECT

Conclusion

The SQL SELECT statement is the most fundamental query to retrieve and view the data from relational databases. Whether fetching individual columns or applying the complex clauses such as WHERE, GROUP BY, and ORDER BY, the SELECT query offers the freedom of retrieving data with effectiveness. With a grasp of how to utilize the SELECT statement and integrating it with different clauses, we can utilize it to filter, aggregate, and sort the data effectively to suit our requirement.