SELECT Statement

The SELECT statement is used to query the database and retrieve records that match specified criteria. It is the most fundamental statement in SQL for data retrieval.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Explanation:

• SELECT: Specifies the columns you want to retrieve.
• FROM: Specifies the table from which to retrieve data.
• WHERE (Optional): Filters records based on a condition.

Retrieving All Columns

To retrieve all columns from a table, you can use the asterisk (*) wildcard:

SELECT *
FROM Customers;

Retrieving Specific Columns

You can list the specific columns you want to retrieve, separated by commas:

SELECT CustomerName, City, Country
FROM Customers;

Filtering Records with WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT ProductName, Price
FROM Products
WHERE Price > 50;

Common Operators in WHERE Clause:

Using LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcards for LIKE:

• % - Represents zero, one, or multiple characters.
• _ - Represents a single character.

-- Find customers starting with 'A'
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'A%';

-- Find customers ending with 'son'
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '%son';

-- Find customers with 'an' in their name
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '%an%';

-- Find customers where the second letter is 'o'
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '_o%';

Using IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.

SELECT ProductName, Price
FROM Products
WHERE Price IN (10, 20, 30);

-- Equivalent to:
-- SELECT ProductName, Price
-- FROM Products
-- WHERE Price = 10 OR Price = 20 OR Price = 30;

Using BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 50 AND 100;

-- Equivalent to:
-- SELECT ProductName, Price
-- FROM Products
-- WHERE Price >= 50 AND Price <= 100;

Sorting Query Results with ORDER BY

The ORDER BY clause is used to sort the result-set in ascending or descending order.

• ASC: Ascending order (default).
• DESC: Descending order.

-- Sort by ProductName in ascending order
SELECT ProductName, Price
FROM Products
ORDER BY ProductName ASC;

-- Sort by Price in descending order
SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

-- Sort by Country ascending, then City descending
SELECT CustomerName, Country, City
FROM Customers
ORDER BY Country ASC, City DESC;

SQL Aliases

SQL aliases are used to give a table, or a column, a temporary name. Aliases are created using the AS keyword.

-- Alias for a column
SELECT CustomerName AS AliasName
FROM Customers;

-- Alias for a table
SELECT C.CustomerName, C.City
FROM Customers AS C
WHERE C.Country = 'USA';

SELECT TOP 5 ProductName, Price
FROM Products
WHERE Price IS NOT NULL
ORDER BY Price DESC;