SQL Tutorial

SQL (Structured Query Language) is the standard language for dealing with Relational Databases. It can be used to insert, search, update, and delete database records.

Get Started

Welcome to the SQL Tutorial! Navigate through the chapters on the left to master SQL from the ground up.

RDBMS Basics

RDBMS stands for Relational Database Management System. It is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Table: The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
Row: A row (or record) represents a single, implicitly structured data item in a table.
Column: A column is a set of data values of a particular simple type, one value for each row of the database.

SQL Data Types

Each column in a database table is required to have a name and a data type.

Data Type	Description
`INT`	A normal-sized integer that can be signed or unsigned.
`VARCHAR(size)`	A variable-length string. The maximum size is specified in parentheses.
`TEXT`	Holds a string with a maximum length of 65,535 bytes.
`DATE`	A date format YYYY-MM-DD.
`DECIMAL(size, d)`	An exact fixed-point number. Size is total digits, d is number of decimals.

CREATE Database & Table

The CREATE statement is used to create a new database or table.

CREATE DATABASE

CREATE DATABASE myDB;

CREATE TABLE

To create a table, you specify the table name along with the columns and their data types.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    Salary DECIMAL(10, 2)
);

ALTER Table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Add a Column

ALTER TABLE Employees
ADD Email VARCHAR(100);

Drop a Column

ALTER TABLE Employees
DROP COLUMN BirthDate;

DROP & TRUNCATE

Used to delete entire tables or just the data inside them.

DROP TABLE

Deletes the table definition and all data, indexes, triggers, constraints, and permission specifications for that table.

DROP TABLE Employees;

TRUNCATE TABLE

Deletes the data inside a table, but not the table itself.

TRUNCATE TABLE Employees;

INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

Syntax specifying columns:

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe');

Syntax without specifying columns (values must match table order):

INSERT INTO Employees
VALUES (2, 'Jane', 'Smith', 'jane@example.com');

UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

Warning! Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Update a Single Record

UPDATE Employees
SET LastName = 'Johnson', Email = 'johnson@example.com'
WHERE EmployeeID = 1;

DELETE Statement

The DELETE statement is used to delete existing records in a table.

Danger! If you omit the WHERE clause, all records in the table will be deleted.

Delete a specific record

DELETE FROM Employees
WHERE EmployeeID = 1;

SELECT & DISTINCT

The SELECT statement is used to select data from a database.

Select All Columns

SELECT * FROM Employees;

Select Specific Columns

SELECT FirstName, LastName FROM Employees;

SELECT DISTINCT

Returns only distinct (different) values.

SELECT DISTINCT Department FROM Employees;

WHERE Clause

The WHERE clause is used to filter records.

Filtering Numeric Data

SELECT * FROM Employees
WHERE Salary > 50000;

Filtering Text Data

SQL requires single quotes around text values.

SELECT * FROM Employees
WHERE LastName = 'Smith';

AND, OR, NOT, IN, LIKE

Combine or enhance conditions using logical operators.

AND / OR / NOT

SELECT * FROM Employees
WHERE Department = 'Sales' AND Salary > 40000;

IN Operator

Allows you to specify multiple values in a WHERE clause.

SELECT * FROM Employees
WHERE Department IN ('HR', 'IT', 'Sales');

LIKE Operator

Used to search for a specified pattern in a column.

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

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

ORDER BY Clause

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

Ascending Sort (Default)

SELECT * FROM Employees
ORDER BY LastName ASC;

Descending Sort

SELECT * FROM Employees
ORDER BY Salary DESC;

Aggregate Functions

Aggregate functions perform a calculation on a set of values, and return a single value.

MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
AVG() - returns the average value of a numeric column
SUM() - returns the total sum of a numeric column

SELECT COUNT(EmployeeID) AS TotalEmployees,
       AVG(Salary) AS AverageSalary
FROM Employees;

GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows.

It is often used with aggregate functions to group the result-set by one or more columns.

SELECT Department, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY Department;

HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT Department, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(EmployeeID) > 5;

INNER JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

INNER JOIN

Returns records that have matching values in both tables.

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

LEFT, RIGHT & FULL JOIN

Outer joins include rows even if they do not have related records in the joined table.

LEFT JOIN

Returns all records from the left table, and the matched records from the right table.

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

RIGHT JOIN

Returns all records from the right table, and the matched records from the left table.

FULL OUTER JOIN

Returns all records when there is a match in either left or right table.

SELF JOIN & UNION

Advanced ways to combine data.

SELF JOIN

A regular join, but the table is joined with itself.

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City;

UNION Operator

Used to combine the result-set of two or more SELECT statements.

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

Subqueries

A subquery is a query nested inside another query.

Example: Subquery in WHERE

Find employees earning more than the average salary.

SELECT FirstName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

SQL Constraints

Constraints are used to specify rules for the data in a table.

NOT NULL - Ensures that a column cannot have a NULL value.
UNIQUE - Ensures that all values in a column are different.
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
FOREIGN KEY - Prevents actions that would destroy links between tables.
CHECK - Ensures that the values in a column satisfies a specific condition.
DEFAULT - Sets a default value for a column if no value is specified.

Views & Indexes

Concepts for optimizing and organizing database access.

CREATE VIEW

A view is a virtual table based on the result-set of an SQL statement.

CREATE VIEW BrazilCustomers AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';

CREATE INDEX

Indexes are used to retrieve data from the database more quickly than otherwise.

CREATE INDEX idx_lastname
ON Employees (LastName);