The SQL 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.
SQL SELECT Query: The SELECT statement in SQL is used to fetch or retrieve data from a database. It allows users to access the data and retrieve specific data based on specific conditions.
Syntax of SQL SELECT Statement:
The basic syntax of the SELECT Query is as follows −
SELECT column1, column2, …
FROM table_name;
Here, column1, column2, … are the field names of the table you want to select data from.
The table_name represents the name of the table you want to select data from.
Select ALL columns:
If you want to return all columns, without specifying every column name, you can use the SELECT * syntax:
SELECT * FROM table_name;
SELECT Statement Example:
Let’s look at some examples of the SQL SELECT statement, to understand it better.
Let’s create a table which will be used in examples:
CREATE TABLE:
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT(2),
Phone INT(10),
Budget DECIMAL (10,2),
Bought_Items INT
);
— Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, ‘James’, ‘Flake’, ‘India’,’23’,’xxxxxxxxxx’, 50000, 33),
(2, ‘Son’, ‘Linda’, ‘Austria’,’21’,’xxxxxxxxxx’, 45000, 15),
(3, ‘Marks’, ‘Mary’, ‘America’,’24’,’xxxxxxxxxx’, 65000, 45),
(4, ‘Jennifer’, ‘P’, ‘Austria’,’21’,’xxxxxxxxxx’, 75000.10, 55),
(5, ‘Sundar’, ‘Lary’, ‘America’,’22’,’xxxxxxxxxx’, 85000.35, 67);
Retrieve Data using SELECT Query:
In this example, we will fetch CustomerName, LastName from the table Customer:
Query:
SELECT CustomerName, LastName, Budget FROM Customer;
Output:
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.
Query:
SELECT CustomerName FROM Customer where Age = ’24’;
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
Query:
SELECT COUNT(Bought_Items) FROM Customer GROUP BY Country;
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 condition;
Example of SELECT Statement with HAVING clause
Query:
SELECT SUM(Budget) as Total_Budget
FROM Customer
GROUP BY Country
HAVING SUM(Budget) >= 50000;
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:
Query:
SELECT * FROM Customer ORDER BY Age DESC;
