SQL Tutorial - T-SQL.co.uk

T-SQL Training Tutorial

Section 1. Introduction to SQL

What is SQL

SQL (Structured Query Language) is a standard language for managing and manipulating databases. Some popular SQL dialects include:

T-SQL: Transact-SQL, used in Microsoft SQL Server.
PL/SQL: Procedural Language/SQL, used in Oracle.
MySQL: An open-source relational database management system.

SQL Syntax

The basic syntax of SQL includes commands such as SELECT, INSERT, UPDATE, and DELETE. Here’s a simple example:


SELECT column1, column2
FROM table_name
WHERE condition;

Section 2. Querying Data

SELECT

The SELECT statement retrieves data from a table.


SELECT column1, column2
FROM table_name;

Section 3. Sorting Rows

ORDER BY Clause

The ORDER BY clause sorts the rows in a result set.


SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;

Section 4. Filtering Rows

DISTINCT

The DISTINCT keyword retrieves unique values from a result set.


SELECT DISTINCT column1
FROM table_name;

LIMIT

The LIMIT keyword limits the number of rows a query returns.


SELECT column1, column2
FROM table_name
LIMIT 10;

FETCH

The FETCH keyword skips N rows before starting to return any rows.


SELECT column1, column2
FROM table_name
ORDER BY column1
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

WHERE Clause

The WHERE clause filters rows based on a condition.


SELECT column1, column2
FROM table_name
WHERE condition;

AND Operator

Combines two Boolean expressions using the AND logical operator.


SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

OR Operator

Combines two Boolean expressions using the OR logical operator.


SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

BETWEEN Operator

Selects data within a range of values.


SELECT column1, column2
FROM table_name
WHERE column1 BETWEEN value1 AND value2;

IN Operator

Returns true if a value is in a list of values.


SELECT column1, column2
FROM table_name
WHERE column1 IN (value1, value2, value3);

LIKE Operator

Queries data based on a specified pattern.


SELECT column1, column2
FROM table_name
WHERE column1 LIKE 'pattern';

IS NULL Operator

Checks whether a value is NULL.


SELECT column1, column2
FROM table_name
WHERE column1 IS NULL;

NOT Operator

Negates a Boolean expression using the NOT operator.


SELECT column1, column2
FROM table_name
WHERE NOT condition;

Section 5. Joining Multiple Tables

INNER JOIN

Merges rows from two tables based on a condition and returns only the matching rows.


SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table; if there are no matching rows, uses NULL for columns of the right table.


SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;

RIGHT JOIN

Returns all rows from the right table and matching rows from the left table; if there are no matching rows, uses NULL for columns of the left table.


SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;

FULL OUTER JOIN

Joins multiple tables by including rows from both tables whether or not the rows have matching rows.


SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_column = b.common_column;

CROSS JOIN

Combines every row from the first table with every row from the second table.


SELECT a.column1, b.column2
FROM table1 a
CROSS JOIN table2 b;

SELF JOIN

Combines rows within the same table based on a related column.


SELECT a.column1, b.column2
FROM table_name a, table_name b
WHERE a.common_column = b.common_column;

Section 6. Grouping Rows

GROUP BY

Arranges rows into groups and applies an aggregate function to each group.


SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

HAVING

Filters groups of rows based on a condition.


SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 1;

GROUPING SETS

Defines multiple groupings in a single query.


SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY GROUPING SETS ((column1), (column2));

ROLLUP

Generates multiple grouping sets considering the hierarchy of the input columns.


SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY ROLLUP (column1, column2);

CUBE

Generates subtotals and grand totals for a set of columns in a query.


SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY CUBE (column1, column2);

Section 7. SET Operators

UNION

Combines result sets of two or more queries into a single result set.


SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM

Section 8. Working with Tables

CREATE TABLE

Creates a new table in the database.


CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL
);

PRIMARY KEY

Defines a primary key for a table.


CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype
);

NOT NULL

Ensures that values inserted or updated into a column are not NULL.


CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype
);

DROP TABLE

Drops a table from the database.


DROP TABLE table_name;

Add a New Column to a Table

Adds a new column to an existing table.


ALTER TABLE table_name
ADD new_column datatype;

Drop a Column

Removes a column from a table.


ALTER TABLE table_name
DROP COLUMN column_name;

FOREIGN KEY

Defines foreign key constraints.


CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    CONSTRAINT fk_name FOREIGN KEY (column2) REFERENCES another_table(column2)
);

UNIQUE

Ensures the uniqueness of values in a column or a set of columns.


CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype
);

CHECK

Validates data before storing it in one or more columns based on a Boolean expression.


CREATE TABLE table_name (
    column1 datatype,
    column2 datatype CHECK (column2 > 0)
);

ALTER TABLE

Modifies the structure of an existing table.


ALTER TABLE table_name
ADD new_column datatype;

Section 9. Modifying Data

INSERT

Inserts one or more rows into a table.


INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

UPDATE

Updates existing data in a table.


UPDATE table_name
SET column1 = value1
WHERE condition;

DELETE

Deletes data from a table permanently.


DELETE FROM table_name
WHERE condition;

TRUNCATE TABLE

Deletes all data in a big table quickly and efficiently.


TRUNCATE TABLE table_name;

Section 10. Views & Triggers

Views

Creates a view in the database.


CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Triggers

Creates a trigger associated with a table that automatically responds to an event.


CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;

Section 11. Conditional Expressions & Functions

CASE Expression

Adds if-else logic to SQL statements.


SELECT column1,
       CASE
           WHEN condition1 THEN 'Result1'
           WHEN condition2 THEN 'Result2'
           ELSE 'Result3'
       END AS new_column
FROM table_name;

COALESCE

Returns the first non-null value in a set of values.


SELECT COALESCE(column1, column2, 'default_value') AS result
FROM table_name;

NULLIF

Compares two arguments and returns NULL if they are equal or the first argument otherwise.


SELECT NULLIF(column1, column2) AS result
FROM table_name;

Section 12. Aggregate Functions

Aggregate Functions

Returns a value such as min, max, average, sum, and count for a set of values.

AVG

Returns the average value of a set.


SELECT AVG(column1) AS average_value
FROM table_name;

COUNT

Returns the number of items in a set.


SELECT COUNT(*) AS total_count
FROM table_name;

SUM

Returns the sum of all or distinct items of a set.


SELECT SUM(column1) AS total_sum
FROM table_name;

MAX

Returns the maximum value in a set.


SELECT MAX(column1) AS max_value
FROM table_name;

MIN

Returns the minimum value in a set.


SELECT MIN(column1) AS min_value
FROM table_name;

Section 13. Subquery

Subquery

Creates a query within another query, providing result sets to the outer query.


SELECT column1
FROM table_name
WHERE column2 IN (SELECT column2 FROM another_table WHERE condition);

Correlated Subquery

Constructs a subquery that uses values from the outer query.


SELECT column1
FROM table_name a
WHERE column2 > (SELECT AVG(column2) FROM table_name b WHERE a.common_column = b.common_column);

EXISTS

Returns true if the subquery contains any rows.


SELECT column1
FROM table_name
WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);

ALL

Compares a value with a set of values and returns true if the comparison is true for every value in the set.


SELECT column1
FROM table_name
WHERE column1 > ALL (SELECT column2 FROM another_table WHERE condition);

ANY

Compares a value with a set of values and returns true if the comparison is true for at least one value in the set.


SELECT column1
FROM table_name
WHERE column1 = ANY (SELECT column2 FROM another_table WHERE condition);