Complete SQL Tutorial

Master SQL from basics to advanced concepts with practical examples

1. Introduction to SQL

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It's used to store, retrieve, and manipulate data in database management systems like MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986
  • SQL became a standard of the International Organization for Standardization (ISO) in 1987

What Can SQL Do?

  • Execute queries against a database
  • Retrieve data from a database
  • Insert records in a database
  • Update records in a database
  • Delete records from a database
  • Create new databases
  • Create new tables in a database
  • Create stored procedures in a database
  • Create views in a database
  • Set permissions on tables, procedures, and views

Database Structure

A database contains one or more tables. Each table is identified by a name and contains records (rows) with data. Each record consists of multiple fields (columns).

CustomerID CustomerName Country City
1 John Doe USA New York
2 Jane Smith UK London
3 Bob Johnson Canada Toronto

2. SQL Syntax

SQL statements are composed of keywords, identifiers, and clauses. SQL is not case-sensitive, but it's common practice to write SQL keywords in uppercase.

Basic Syntax Rules:

-- SQL statements end with a semicolon SELECT * FROM Customers; -- SQL is not case-sensitive select * from customers; SELECT * FROM CUSTOMERS; -- Comments start with -- /* This is a multi-line comment */
Note: Some database systems require a semicolon at the end of each SQL statement for execution.

3. SELECT Statement

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

Basic SELECT Syntax

-- Select all columns from a table SELECT * FROM table_name; -- Select specific columns SELECT column1, column2 FROM table_name;

SELECT Examples

Select all customers:

SELECT * FROM Customers;

Select specific columns:

SELECT CustomerName, City FROM Customers;

SELECT DISTINCT (unique values only):

SELECT DISTINCT Country FROM Customers;

Column Aliases

SELECT CustomerName AS Name, City AS Location FROM Customers;

4. WHERE Clause

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

WHERE Syntax

SELECT column1, column2 FROM table_name WHERE condition;

Comparison Operators

Operator Description Example
= Equal WHERE City = 'London'
!= Not equal WHERE City != 'London'
> Greater than WHERE Price > 20
<< /td> Less than WHERE Price < 20
>= Greater than or equal WHERE Price >= 20
<=< /td> Less than or equal WHERE Price <= 20

Logical Operators

AND Operator:

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

OR Operator:

SELECT * FROM Customers WHERE City = 'Berlin' OR City = 'London';

NOT Operator:

SELECT * FROM Customers WHERE NOT Country = 'Germany';

Pattern Matching

LIKE Operator:

-- Names starting with 'A' SELECT * FROM Customers WHERE CustomerName LIKE 'A%'; -- Names ending with 'a' SELECT * FROM Customers WHERE CustomerName LIKE '%a'; -- Names containing 'or' SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; -- Names with 'a' as second character SELECT * FROM Customers WHERE CustomerName LIKE '_a%';

IN Operator:

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

BETWEEN Operator:

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

5. ORDER BY Clause

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

ORDER BY Syntax

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

ORDER BY Examples

Sort by one column (ascending by default):

SELECT * FROM Customers ORDER BY Country;

Sort in descending order:

SELECT * FROM Customers ORDER BY Country DESC;

Sort by multiple columns:

SELECT * FROM Customers ORDER BY Country, CustomerName;

Mixed sorting:

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

6. INSERT Statement

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

INSERT Syntax

-- Insert values for all columns INSERT INTO table_name VALUES (value1, value2, value3, ...); -- Insert values for specific columns INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

INSERT Examples

Insert a complete record:

INSERT INTO Customers VALUES (1, 'John Doe', 'john@example.com', 'USA', 'New York');

Insert specific columns:

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Jane Smith', 'London', 'UK');

Insert multiple records:

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Bob Johnson', 'Toronto', 'Canada'), ('Alice Brown', 'Paris', 'France'), ('Charlie Davis', 'Sydney', 'Australia');

7. UPDATE Statement

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

UPDATE Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Warning: Be careful when updating records! If you omit the WHERE clause, ALL records will be updated!

UPDATE Examples

Update a single column:

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

Update multiple columns:

UPDATE Customers SET ContactName = 'Juan Carlos', City = 'Madrid' WHERE CustomerID = 2;

Update multiple records:

UPDATE Customers SET Country = 'United States' WHERE Country = 'USA';

8. DELETE Statement

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

DELETE Syntax

DELETE FROM table_name WHERE condition;
Warning: Be careful when deleting records! If you omit the WHERE clause, ALL records will be deleted!

DELETE Examples

Delete a specific record:

DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste';

Delete all records (but keep table structure):

DELETE FROM Customers;

Drop entire table:

DROP TABLE Customers;

9. SQL JOINS

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

Types of JOINS

  • INNER JOIN: Returns records that have matching values in both tables
  • LEFT JOIN: Returns all records from the left table, and matched records from the right table
  • RIGHT JOIN: Returns all records from the right table, and matched records from the left table
  • FULL OUTER JOIN: Returns all records when there is a match in either left or right table

JOIN Examples

INNER JOIN:

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

LEFT JOIN:

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

RIGHT JOIN:

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

FULL OUTER JOIN:

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

Self JOIN

-- Find customers from the same city 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 ORDER BY A.City;

10. GROUP BY Clause

The GROUP BY statement groups rows that have the same values into summary rows. It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG).

GROUP BY Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

GROUP BY Examples

Count customers by country:

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

Average price by category:

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

Total sales by employee:

SELECT SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales, Orders.EmployeeID FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID GROUP BY Orders.EmployeeID;

11. HAVING Clause

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

HAVING Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

HAVING Examples

Countries with more than 5 customers:

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

Categories with average price > 20:

SELECT AVG(Price), CategoryID FROM Products GROUP BY CategoryID HAVING AVG(Price) > 20;

12. SQL Functions

SQL has many built-in functions for performing calculations on data.

Aggregate Functions

Common Aggregate Functions:

-- Count total records SELECT COUNT(*) FROM Customers; -- Count non-null values SELECT COUNT(CustomerName) FROM Customers; -- Sum of all values SELECT SUM(Price) FROM Products; -- Average value SELECT AVG(Price) FROM Products; -- Maximum value SELECT MAX(Price) FROM Products; -- Minimum value SELECT MIN(Price) FROM Products;

String Functions

String Manipulation:

-- Convert to uppercase SELECT UPPER(CustomerName) FROM Customers; -- Convert to lowercase SELECT LOWER(CustomerName) FROM Customers; -- Get string length SELECT LEN(CustomerName) FROM Customers; -- Extract substring SELECT SUBSTRING(CustomerName, 1, 5) FROM Customers; -- Concatenate strings SELECT CONCAT(CustomerName, ' - ', Country) FROM Customers; -- Replace text SELECT REPLACE(CustomerName, 'a', 'X') FROM Customers;

Date Functions

Date Operations:

-- Current date and time SELECT NOW(); -- Current date SELECT CURDATE(); -- Extract year SELECT YEAR(OrderDate) FROM Orders; -- Extract month SELECT MONTH(OrderDate) FROM Orders; -- Extract day SELECT DAY(OrderDate) FROM Orders; -- Add days to date SELECT DATE_ADD(OrderDate, INTERVAL 30 DAY) FROM Orders; -- Date difference SELECT DATEDIFF(NOW(), OrderDate) FROM Orders;

Mathematical Functions

Math Operations:

-- Round to 2 decimal places SELECT ROUND(Price, 2) FROM Products; -- Ceiling (round up) SELECT CEIL(Price) FROM Products; -- Floor (round down) SELECT FLOOR(Price) FROM Products; -- Absolute value SELECT ABS(-15); -- Square root SELECT SQRT(16); -- Power SELECT POWER(2, 3);

13. SQL Constraints

Constraints are used to specify rules for data in a table. They ensure the accuracy and reliability of the data.

Types of Constraints

  • NOT NULL: Ensures a column cannot have NULL value
  • UNIQUE: Ensures all values in a column are different
  • PRIMARY KEY: Uniquely identifies each record in a table
  • FOREIGN KEY: Links two tables together
  • CHECK: Ensures values satisfy a specific condition
  • DEFAULT: Sets a default value for a column

Constraint Examples

Create table with constraints:

CREATE TABLE Employees ( EmployeeID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age >= 18), Email varchar(255) UNIQUE, Salary decimal(10,2) DEFAULT 0, DepartmentID int, FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) );

Add constraints to existing table:

-- Add NOT NULL constraint ALTER TABLE Employees MODIFY COLUMN FirstName varchar(255) NOT NULL; -- Add UNIQUE constraint ALTER TABLE Employees ADD CONSTRAINT UC_Employee_Email UNIQUE (Email); -- Add CHECK constraint ALTER TABLE Employees ADD CONSTRAINT CHK_Employee_Age CHECK (Age >= 18 AND Age <= 65); -- Add FOREIGN KEY constraint ALTER TABLE Employees ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

14. SQL Indexes

Indexes are used to retrieve data from the database more quickly. They create shortcuts to data, making queries faster.

Index Types

  • Clustered Index: Sorts and stores data rows in the table based on key values
  • Non-Clustered Index: Contains pointers to data rows
  • Unique Index: Ensures no duplicate values
  • Composite Index: Created on multiple columns

Index Examples

Create indexes:

-- Create index on single column CREATE INDEX idx_lastname ON Employees (LastName); -- Create unique index CREATE UNIQUE INDEX idx_email ON Employees (Email); -- Create composite index CREATE INDEX idx_name ON Employees (LastName, FirstName); -- Create index with sorting CREATE INDEX idx_salary_desc ON Employees (Salary DESC);

Drop indexes:

-- Drop index DROP INDEX idx_lastname ON Employees; -- Drop index (SQL Server syntax) DROP INDEX Employees.idx_lastname;
Note: While indexes speed up SELECT operations, they slow down INSERT, UPDATE, and DELETE operations as the indexes also need to be updated.

15. SQL Views

A view is a virtual table based on the result-set of an SQL statement. Views contain rows and columns just like a real table.

View Benefits

  • Security: Hide sensitive data from users
  • Simplicity: Simplify complex queries
  • Consistency: Ensure consistent data access
  • Logic: Centralize business logic

View Examples

Create views:

-- Simple view CREATE VIEW CustomerView AS SELECT CustomerName, ContactName, Country FROM Customers WHERE Country = 'Germany'; -- Complex view with JOIN CREATE VIEW OrderSummary AS SELECT c.CustomerName, COUNT(o.OrderID) as TotalOrders FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName; -- View with calculations CREATE VIEW ProductPricing AS SELECT ProductName, Price, Price * 1.1 as PriceWithTax, CASE WHEN Price < 20 THEN 'Budget' WHEN Price < 50 THEN 'Standard' ELSE 'Premium' END as PriceCategory FROM Products;

Use views:

-- Query a view like a table SELECT * FROM CustomerView; -- Update view (if updatable) UPDATE CustomerView SET ContactName = 'New Name' WHERE CustomerName = 'Alfreds Futterkiste';

Drop views:

DROP VIEW CustomerView;

16. Stored Procedures

A stored procedure is a prepared SQL code that you can save and reuse. It can accept parameters and return results.

Procedure Benefits

  • Performance: Compiled once, executed many times
  • Security: Prevent SQL injection attacks
  • Maintainability: Centralize business logic
  • Network Traffic: Reduce data transfer

Stored Procedure Examples

Simple stored procedure:

-- MySQL/SQL Server syntax DELIMITER // CREATE PROCEDURE GetCustomersByCountry(IN country_name VARCHAR(255)) BEGIN SELECT CustomerName, City FROM Customers WHERE Country = country_name; END // DELIMITER ; -- Execute procedure CALL GetCustomersByCountry('Germany');

Procedure with multiple parameters:

DELIMITER // CREATE PROCEDURE GetCustomersByCountryAndCity( IN country_name VARCHAR(255), IN city_name VARCHAR(255) ) BEGIN SELECT CustomerName, ContactName FROM Customers WHERE Country = country_name AND City = city_name; END // DELIMITER ; -- Execute with parameters CALL GetCustomersByCountryAndCity('Germany', 'Berlin');

Procedure with output parameter:

DELIMITER // CREATE PROCEDURE GetCustomerCount( IN country_name VARCHAR(255), OUT customer_count INT ) BEGIN SELECT COUNT(*) INTO customer_count FROM Customers WHERE Country = country_name; END // DELIMITER ; -- Execute and get output CALL GetCustomerCount('Germany', @count); SELECT @count;

17. SQL Data Types

Data types specify what kind of data can be stored in a column. Different database systems have different data types.

Numeric Data Types

Data Type Description Storage
TINYINT Very small integer (-128 to 127) 1 byte
SMALLINT Small integer (-32,768 to 32,767) 2 bytes
INT Standard integer (-2B to 2B) 4 bytes
BIGINT Large integer 8 bytes
DECIMAL(p,s) Fixed precision and scale Variable
FLOAT Floating point number 4 bytes
DOUBLE Double precision float 8 bytes

String Data Types

Data Type Description Max Length
CHAR(n) Fixed length string 255 characters
VARCHAR(n) Variable length string 65,535 characters
TEXT Large text data 65,535 characters
LONGTEXT Very large text data 4GB
BINARY(n) Fixed length binary 255 bytes
VARBINARY(n) Variable length binary 65,535 bytes

Date and Time Data Types

Data Type Description Format
DATE Date only YYYY-MM-DD
TIME Time only HH:MM:SS
DATETIME Date and time YYYY-MM-DD HH:MM:SS
TIMESTAMP Unix timestamp YYYY-MM-DD HH:MM:SS
YEAR Year only YYYY

Data Type Examples

Table creation with data types:

CREATE TABLE Users ( UserID INT AUTO_INCREMENT PRIMARY KEY, Username VARCHAR(50) NOT NULL UNIQUE, Email VARCHAR(100) NOT NULL, Password CHAR(64), Age TINYINT UNSIGNED, Salary DECIMAL(10,2), IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ProfilePicture LONGBLOB, Bio TEXT );

18. Advanced SQL Concepts

Window Functions

ROW_NUMBER, RANK, DENSE_RANK:

-- Row number within partition SELECT CustomerName, Country, ROW_NUMBER() OVER (PARTITION BY Country ORDER BY CustomerName) as RowNum, RANK() OVER (PARTITION BY Country ORDER BY CustomerName) as Rank, DENSE_RANK() OVER (PARTITION BY Country ORDER BY CustomerName) as DenseRank FROM Customers; -- Running totals SELECT OrderID, OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate) as RunningTotal FROM Orders;

Common Table Expressions (CTE)

-- Simple CTE WITH CustomerOrders AS ( SELECT CustomerID, COUNT(*) as OrderCount FROM Orders GROUP BY CustomerID ) SELECT c.CustomerName, co.OrderCount FROM Customers c JOIN CustomerOrders co ON c.CustomerID = co.CustomerID; -- Recursive CTE (Employee hierarchy) WITH EmployeeHierarchy AS ( -- Anchor member SELECT EmployeeID, ManagerID, FirstName, 0 as Level FROM Employees WHERE ManagerID IS NULL UNION ALL -- Recursive member SELECT e.EmployeeID, e.ManagerID, e.FirstName, eh.Level + 1 FROM Employees e JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchy;

CASE Statements

-- Simple CASE SELECT ProductName, Price, CASE WHEN Price < 20 THEN 'Cheap' WHEN Price < 50 THEN 'Moderate' ELSE 'Expensive' END as PriceCategory FROM Products; -- CASE in WHERE clause SELECT * FROM Orders WHERE CASE WHEN YEAR(OrderDate)=2023 THEN ShipCountry='USA' ELSE ShipCountry IN ('UK', 'Germany' ) END;

Subqueries

-- Scalar subquery SELECT CustomerName FROM Customers WHERE CustomerID = ( SELECT CustomerID FROM Orders WHERE OrderID = 10248 ); -- EXISTS subquery SELECT CustomerName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID ); -- Correlated subquery SELECT CustomerName, (SELECT COUNT(*) FROM Orders o WHERE o.CustomerID = c.CustomerID) as OrderCount FROM Customers c;

19. SQL Best Practices

  • Use meaningful names: Choose descriptive table and column names
  • Normalize your database: Avoid data redundancy
  • Use primary keys: Every table should have a primary key
  • Index appropriately: Create indexes on frequently queried columns
  • Use constraints: Enforce data integrity with constraints
  • Avoid SELECT *: Specify only needed columns
  • Use parameterized queries: Prevent SQL injection
  • Comment your code: Document complex queries
  • Test with small data first: Validate queries before running on large datasets
  • Regular backups: Always backup your database

Good vs Bad SQL Examples:

-- Bad: SELECT * SELECT * FROM Customers WHERE Country = 'Germany'; -- Good: Specify columns SELECT CustomerName, City, Email FROM Customers WHERE Country = 'Germany'; -- Bad: No indexes on WHERE clause columns -- CREATE TABLE without indexes on frequently searched columns -- Good: Create appropriate indexes CREATE INDEX idx_country ON Customers(Country); CREATE INDEX idx_order_date ON Orders(OrderDate);

20. Summary

You've now learned the fundamentals of SQL and some advanced concepts. SQL is a powerful language for managing relational databases. Key takeaways:

  • Master the basic CRUD operations (CREATE, READ, UPDATE, DELETE)
  • Understand how to filter and sort data effectively
  • Learn to join tables to combine data from multiple sources
  • Use aggregate functions and GROUP BY for data analysis
  • Implement proper database design with constraints and indexes
  • Utilize advanced features like views, stored procedures, and window functions
  • Always follow best practices for security and performance
Next Steps: Practice with real databases, learn specific database system features (MySQL, PostgreSQL, SQL Server), and explore database administration topics.