T-SQL Queries - SQL Guides

SELECT Statement

The SELECT statement is used to query the database and retrieve data from one or more tables. It's the most fundamental command for data retrieval.

Basic SELECT

Retrieve all columns and rows from a table

SELECT * FROM Customers;

This query retrieves all columns (*) and all rows from the Customers table.

Retrieve specific columns

SELECT CustomerName, ContactName, City FROM Customers;

This query retrieves only the CustomerName, ContactName, and City columns from the Customers table.

Using Aliases

SELECT CustomerName AS Name, City AS Location FROM Customers;

Aliases (AS) provide temporary, more readable names for columns in the result set.

Filtering with WHERE Clause

Filter rows based on a condition

SELECT CustomerName, City FROM Customers WHERE Country = 'Germany';

The WHERE clause filters records, returning only those where the Country is 'Germany'.

Using multiple conditions (AND, OR)

                    SELECT CustomerName, City FROM Customers WHERE Country = 'USA' AND City = 'New York';
                

The AND operator requires both conditions to be true.

                    SELECT CustomerName, City FROM Customers WHERE Country = 'USA' OR Country = 'Canada';
                

The OR operator requires at least one condition to be true.

Using IN operator

                    SELECT CustomerName, Country FROM Customers WHERE Country IN ('Germany', 'France', 'Spain');
                

The IN operator allows you to specify a list of values for a condition.

Using BETWEEN operator

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

The BETWEEN operator selects values within a given range (inclusive).

Sorting with ORDER BY

Sort results in ascending order

SELECT CustomerName, City FROM Customers ORDER BY City ASC;

ASC (ascending) is the default, so it can be omitted.

Sort results in descending order

SELECT ProductName, Price FROM Products ORDER BY Price DESC;

DESC specifies descending order.

Multi-column sorting

                    SELECT CustomerName, Country, City FROM Customers ORDER BY Country ASC, City DESC;
                

Sorts first by Country (ascending), then by City (descending) for customers in the same country.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause.

COUNT()

SELECT COUNT(CustomerID) AS NumberOfCustomers FROM Customers;

Counts the number of rows in the Customers table.

SUM()

SELECT SUM(Quantity) AS TotalQuantity FROM OrderDetails;

Calculates the sum of values in the Quantity column.

AVG()

SELECT AVG(Price) AS AveragePrice FROM Products;

Calculates the average value of the Price column.

MIN() and MAX()

SELECT MIN(Price) AS LowestPrice, MAX(Price) AS HighestPrice FROM Products;

Finds the minimum and maximum values in the Price column.

GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It's typically used with aggregate functions.

Count customers by country

                    SELECT Country, COUNT(CustomerID) AS NumberOfCustomers FROM Customers GROUP BY Country;
                

This query groups customers by their country and counts how many customers are in each country.

Average price by category

                    SELECT CategoryID, AVG(Price) AS AverageProductPrice FROM Products GROUP BY CategoryID;
                

Calculates the average price of products within each CategoryID.

HAVING Clause

The HAVING clause is used to filter groups based on a specified condition, similar to how WHERE filters individual rows.

Countries with more than 5 customers

                    SELECT Country, COUNT(CustomerID) AS NumberOfCustomers FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
                

This query returns only those countries that have more than 5 customers.

Categories with average product price above 75

                    SELECT CategoryID, AVG(Price) AS AverageProductPrice FROM Products GROUP BY CategoryID HAVING AVG(Price) > 75;
                

Shows categories where the average product price exceeds 75.

JOIN Operations

JOIN clauses are used to combine rows from two or more tables based on a related column between them.

INNER JOIN

Get customer names and their orders

                    SELECT C.CustomerName, O.OrderID FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID = O.CustomerID;
                

Returns rows when there is a match in both tables. Links Customers and Orders tables using CustomerID.

LEFT JOIN

Get all customers and their orders (if any)

                    SELECT C.CustomerName, O.OrderID FROM Customers AS C LEFT JOIN Orders AS O ON C.CustomerID = O.CustomerID;
                

Returns all rows from the left table (Customers) and the matched rows from the right table (Orders). If no match, NULL is returned for columns from the right table.

RIGHT JOIN

Get all orders and their corresponding customer names (if any)

                    SELECT C.CustomerName, O.OrderID FROM Customers AS C RIGHT JOIN Orders AS O ON C.CustomerID = O.CustomerID;
                

Returns all rows from the right table (Orders) and the matched rows from the left table (Customers).

FULL OUTER JOIN

Get all customers and all orders, matching where possible

                    SELECT C.CustomerName, O.OrderID FROM Customers AS C FULL OUTER JOIN Orders AS O ON C.CustomerID = O.CustomerID;
                

Returns all rows when there is a match in either the left or the right table. Includes rows that do not have matches in either table.

Subqueries

A subquery (or inner query) is a query nested inside another SQL query. They can be used in the WHERE, FROM, or SELECT clauses.

Find customers who have placed orders

                    SELECT CustomerName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);
                

The subquery finds all CustomerIDs that exist in the Orders table, and the outer query selects customers whose CustomerID is in that list.

Find products more expensive than the average price

                    SELECT ProductName, Price FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
                

This query compares each product's price to the overall average price calculated by the subquery.

T-SQL Querying Fundamentals