SQL Tutorial

SQL is a standard language for storing, manipulating and retrieving data in databases.

SELECT * FROM Customers;

SQL Introduction

SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.

SQL Syntax

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

SELECT column1, column2, ...
FROM table_name;

SQL SELECT Statement

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

SELECT CustomerName, City FROM Customers;

SQL SELECT DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Country FROM Customers;

SQL WHERE Clause

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

SELECT * FROM Customers
WHERE Country='Mexico';

SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

SQL ORDER BY Keyword

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

SELECT * FROM Customers
ORDER BY Country DESC;

SQL INSERT INTO Statement

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

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

SQL UPDATE Statement

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

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

SQL NULL Values

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

SELECT MIN(Price) AS SmallestPrice
FROM Products;

SELECT MAX(Price) AS LargestPrice
FROM Products;

SQL COUNT(), AVG() and SUM()

The COUNT() function returns the number of rows that matches a specified criterion.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

SELECT COUNT(ProductID) FROM Products;
SELECT AVG(Price) FROM Products;
SELECT SUM(Quantity) FROM OrderDetails;

SQL LIKE Operator

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

There are two wildcards often used in conjunction with the LIKE operator:

• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single, character

SELECT * FROM Customers WHERE CustomerName LIKE 'a%'; -- Starts with "a"
SELECT * FROM Customers WHERE CustomerName LIKE '%a'; -- Ends with "a"
SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; -- "or" in any position

SQL Wildcards

Wildcard characters are used to substitute one or more characters in a string.

SELECT * FROM Customers
WHERE City LIKE '[bsp]%'; -- Starts with "b", "s", or "p"

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SQL BETWEEN Operator

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

The BETWEEN operator is inclusive: begin and end values are included.

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SQL Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable. An alias only exists for the duration of that query.

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

SQL Joins

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

Here are the different types of the Joins in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) 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

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

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

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

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

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

SQL HAVING Clause

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

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

SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

CREATE DATABASE myTestDB;

SQL CREATE TABLE Statement

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

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

SQL Constraints

SQL 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: Uniquely identifies a row/record in another table
• CHECK: Ensures that all values in a column satisfies a specific condition
• DEFAULT: Sets a default value for a column when no value is specified

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL ALTER TABLE Statement

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

It is also used to add and drop various constraints on an existing table.

ALTER TABLE Customers
ADD Email varchar(255);

SQL AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

CREATE TABLE Persons (
    PersonID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    PRIMARY KEY (PersonID)
);

SQL CREATE VIEW Statement

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

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

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

SQL Injection

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution.

SQL Data Types

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

SQL developers have to decide what type of data will be stored inside each and every table column.

• String: CHAR, VARCHAR, BINARY, TEXT
• Numeric: INT, FLOAT, DECIMAL, BIT
• Date and Time: DATE, DATETIME, TIMESTAMP, YEAR

SQL Quiz

Test your SQL skills with our Quiz!

SQL Exercises

We have gathered a variety of SQL exercises (with answers) for each SQL Chapter.