Day 2: Querying Data with SELECT

SELECT Statements	Operators	Wildcards (with LIKE)
SELECT * (All columns) SELECT Col1, Col2 (Specific) DISTINCT (Unique only) WHERE (Unique only) ORDER BY ASC/DESC (Sorting) TOP n (Limit records) TOP n PERCENT (Limit by %)	Math: =, <>, <, >, <=, >= Logic: AND, OR, NOT Range: BETWEEN (Inclusive range) List: IN (Matches multiple values) Pattern: LIKE (Used with wildcards)	% : Zero, one, or multiple characters _ : Exactly one single character [] : Any single character within the brackets [^] : Any single character NOT within the brackets

1. The Basics of Data Retrieval

The SELECT statement is the most frequently used command in SQL Server. It allows you to retrieve data from one or more tables.

Selecting All Columns (*)

To retrieve every column from a table, use the asterisk (*) wildcard.

SELECT * FROM Employees;

Selecting Specific Columns

For better performance and clarity, it is best practice to specify exactly which columns you need, separated by commas.

SELECT FirstName, LastName, Department FROM Employees;

2. Removing Duplicates (DISTINCT)

If your table contains duplicate values and you only want to see unique entries, use the DISTINCT keyword immediately after SELECT.

SELECT DISTINCT Department FROM Employees;

3. Filtering Data (The WHERE Clause & Operators)

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

Comparison Operators

• = : Equal to
• <> or != : Not equal to
• > : Greater than
• < : Less than
• >= : Greater than or equal to
• <= : Less than or equal to

SELECT * FROM Products WHERE Price >= 100;

Logical Operators (Multiple Conditions)

You can combine multiple conditions using logical operators.

• AND : True if ALL conditions separated by AND are true.
• OR : True if ANY of the conditions separated by OR is true.
• NOT : Reverses the boolean value of a condition.

-- Using AND
SELECT * FROM Employees WHERE Department = 'IT' AND Salary > 60000;

-- Using OR
SELECT * FROM Customers WHERE City = 'New York' OR City = 'London';

-- Using NOT
SELECT * FROM Employees WHERE NOT Department = 'HR';

Special Range & List Operators

• IN : Allows you to specify multiple possible values for a column (a cleaner alternative to multiple ORs).
• BETWEEN : Selects values within a given range (inclusive of both end points).

-- Using IN
SELECT * FROM Customers WHERE Country IN ('USA', 'Canada', 'UK');

-- Using BETWEEN
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

4. Pattern Matching (LIKE & Wildcards)

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. SQL Server provides specific wildcards to help build these patterns.

• % : Represents zero, one, or multiple characters.
• _ : Represents exactly one single character.
• [] : Represents any single character within the brackets.
• [^] : Represents any single character NOT within the brackets.

Examples of Wildcards:

-- Starts with 'A'
SELECT * FROM Employees WHERE FirstName LIKE 'A%';

-- Ends with 'son'
SELECT * FROM Employees WHERE LastName LIKE '%son';

-- Contains 'tech' anywhere in the string
SELECT * FROM Companies WHERE CompanyName LIKE '%tech%';

-- Exactly 4 characters, starting with 'J' and ending with 'hn' (e.g., John)
SELECT * FROM Employees WHERE FirstName LIKE 'J_hn';

-- Starts with 'A', 'B', or 'C'
SELECT * FROM Customers WHERE City LIKE '[ABC]%';

-- Starts with any letter EXCEPT 'A', 'B', or 'C'
SELECT * FROM Customers WHERE City LIKE '[^ABC]%';

5. Sorting and Limiting Results

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending (ASC) or descending (DESC) order. By default, it sorts in ascending order.

-- Sort descending by Salary
SELECT * FROM Employees ORDER BY Salary DESC;

-- Sort by Department alphabetically, then by Salary descending
SELECT * FROM Employees ORDER BY Department ASC, Salary DESC;

TOP n Records

In SQL Server, the TOP clause limits the number of rows returned in the result set. This is incredibly useful for large tables. Always use TOP with an ORDER BY clause to ensure consistent results!

-- Get the 5 highest paid employees
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;

TOP n PERCENT

Instead of a fixed number, you can request a percentage of the total records.

-- Get the top 10% most expensive products
SELECT TOP 10 PERCENT * FROM Products ORDER BY Price DESC;