SQL Tutorial 2026 Essential Commands for Database Programming

This Nutshell tutorial or workshop will briefly mention all commands and provide a small example. SQL (mysql, Oracle SQL, Microsoft SQL, etc.) offers hundreds of functions and commands, about which many good books and online resources have already been written. Updated 2026

SQL (Structured Query Language) is the standard language for database management. Here is a quick tutorial with the most important SQL commands relevant for websites:

We provide a quick overview of the respective commands.

This German SQL tutorial is regularly expanded: Current SQL topic overview

A more comprehensive overview of the most important SQL commands and topics for websites:

SELECT

Querying Data

WHERE, ORDER BY, GROUP BY, HAVING Functions like COUNT, SUM, AVG JOIN for connecting tables

SQL Tutorial for IRC-Coding.de

SELECT

The SELECT command is used to query data from a database table.

SELECT column1, column2 FROM table;

Here is a short tutorial on the specified SQL commands and concepts:

WHERE

Filters records based on a condition.

SELECT * FROM Customers WHERE City = 'Bochum';

A WHERE clause in SQL serves to restrict the result set of a query based on specific conditions. Here are some special features and best practices to consider when using a WHERE clause:

Syntax and case sensitivity:

SQL keywords are not case-sensitive, but it’s common to write them in uppercase to distinguish them from table and column names. String comparisons are case-sensitive in most SQL databases. That means ‘Bochum’ and ‘bochum’ are considered different unless the database is configured to be case-insensitive.

Quotation marks: String values in SQL are usually enclosed in single quotes (’ ’). If a string contains a single quote, it must be doubled or escaped with another quote, e.g., ‘O”Brien’ for the name “O’Brien”.

NULL values:

The query “WHERE City = ‘Bochum’” returns no rows where the value of the ‘City’ column is NULL. If NULL values should be considered, this must be explicitly specified with “IS NULL” or “IS NOT NULL”.

Performance optimization:

Indexes can significantly improve query performance. If the ‘City’ column is frequently used in WHERE clauses, an index on this column can increase query speed. Using functions on columns in the WHERE clause can override indexes. For example: “WHERE UPPER(City) = ‘BOCHUM’” prevents the use of an index on the ‘City’ column.

Multiple conditions: You can combine multiple conditions with AND and OR. The order is important and should be clarified with parentheses for complex queries, e.g., “WHERE City = ‘Bochum’ AND (Age > 30 OR Profession = ‘Engineer’)”.

Wildcard search: With LIKE you can perform pattern matching. The underscore (_) stands for a single character and the percent sign (%) for any number of characters, e.g., “WHERE City LIKE ‘Boc%’”.

Security: Avoid SQL injection attacks by using parameter binding, especially for user input. This is especially important for dynamic SQL queries. Example of a secure and optimized query could look like this:

— Example of a secure query with parameter binding

SELECT * 
FROM Customers 
WHERE City = ?;

Here a placeholder (?) is used, which is later replaced by the actual value through a secure method (e.g., Prepared Statements).

ORDER BY

Sorts the results by one or more columns.

SELECT * FROM Products ORDER BY Price DESC;

It’s possible to use the position of columns in the SELECT list for sorting:

SELECT Name, Age FROM Customers ORDER BY 2 DESC; 

sorts by the second column (Age) in descending order. This can reduce readability and is often not recommended.

NULL values:

NULL values are handled specially in the sort order. In some SQL databases they appear first, in others at the end, depending on whether sorting is ascending or descending.

Many SQL dialects offer options to explicitly sort NULL values, e.g., ORDER BY Column ASC NULLS LAST.

Performance optimization:

Sorting can have a significant impact on performance, especially with large amounts of data. Indexes on the sort columns can improve performance. Avoid using functions on columns in the ORDER BY clause, as this can override indexes, e.g., ORDER BY UPPER(Name).

Combination of ORDER BY and other clauses:

ORDER BY can be combined with LIMIT or FETCH FIRST to deliver paginated results:

SELECT * FROM Customers ORDER BY Name LIMIT 10 OFFSET 20;

It’s important to note that an ORDER BY clause is usually at the end of the SQL query, after WHERE, GROUP BY and HAVING. Sorting by calculated columns:

You can also sort by calculated columns defined in the SELECT list: SELECT Name, (Salary * 1.1) AS NewSalary FROM Employees ORDER BY NewSalary DESC. Collation:

The sort order can be influenced by the collation, which determines how text values are sorted. Different collations can have different sort orders for strings. Sorting in combination with JOINs:

For queries that use JOINs, you can sort by columns from any participating table:

SELECT c.Name, o.OrderDate 
FROM Customers c JOIN Orders o 
ON c.CustomerID = o.CustomerID ORDER BY o.OrderDate;

Example of a query with ORDER BY:

SELECT Name, City, Age 
FROM Customers 
ORDER BY City ASC, Age DESC;

This query sorts the results first by city in ascending order and within each city by age in descending order.

GROUP BY

Groups rows with common values in a column.

SELECT Category, SUM(SalesPrice) FROM Orders GROUP BY Category;

HAVING

Filters groups similar to WHERE, but for aggregated values.

SELECT Category, SUM(SalesPrice) FROM Orders 

The HAVING clause in SQL is used to set conditions for group results that are generated by the GROUP BY clause. Here are some special features and best practices to consider when using the HAVING clause:

Difference between WHERE and HAVING:

The WHERE clause filters rows before grouping, while the HAVING clause filters after grouping. Example: WHERE is used to filter rows based on individual row values before aggregation takes place. HAVING is used to filter aggregated values.

Use with aggregate functions:

HAVING is often used in conjunction with aggregate functions like COUNT, SUM, AVG, MAX and MIN. Example:

SELECT City, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY City
HAVING COUNT(*) > 5;

This query counts the number of customers in each city and shows only cities that have more than 5 customers. Syntax:

The HAVING clause always follows the GROUP BY clause and comes after it in the query. The correct order of clauses in a query is:

SELECT, FROM, [WHERE], GROUP BY, HAVING, [ORDER BY].

Multiple conditions:

You can use multiple conditions in a HAVING clause by combining AND and OR, similar to the WHERE clause. Example:

SELECT City, AVG(Age) AS AverageAge
FROM Customers
GROUP BY City
HAVING AVG(Age) > 30 AND COUNT(*) > 10;

This query shows only cities where the average age of customers is over 30 years and the number of customers is more than 10. Performance optimization:

Like the WHERE clause, the use of HAVING can affect performance, especially with large amounts of data. Efficient use of indexes and avoiding unnecessary calculations can improve performance. It’s often more efficient to place conditions based on individual rows in the WHERE clause and only conditions based on aggregates in the HAVING clause.

Example query with HAVING:

SELECT City, COUNT(*) AS CustomerCount, AVG(Age) AS AverageAge
FROM Customers
WHERE City IS NOT NULL
GROUP BY City
HAVING COUNT(*) > 5 AND AVG(Age) > 30;

In this query, cities with more than 5 customers and an average age over 30 are displayed. Summary:

WHERE filters before aggregation. HAVING filters after aggregation. HAVING is mainly used in conjunction with aggregate functions. The correct placement of conditions between WHERE and HAVING is crucial for query efficiency.

GROUP BY Category

HAVING SUM(SalesPrice) > 1000;

Aggregate Functions

COUNT: Counts rows SUM: Sum of values AVG: Average of values

SELECT COUNT(*) AS NumberOfCustomers FROM Customers;

SELECT AVG(Price) AS AveragePrice FROM Products;

Aggregate functions are special functions in SQL that perform calculations over multiple rows of a table or filtered result set. They summarize the values of the specified column into a single value. The most important aggregate functions are:

COUNT Counts the number of rows in a group.

SELECT COUNT(*) AS NumberOfCustomers FROM Customers;

SUM Calculates the sum of values in a specified column.

SELECT SUM(Price) AS TotalRevenue FROM Orders;

AVG Calculates the average value of values in a column.

SELECT AVG(Age) AS AverageAge FROM Users;

MAX Returns the largest value in a column.

SELECT MAX(Salary) AS HighestSalary FROM Employees;

MIN Returns the smallest value in a column.

SELECT MIN(Price) AS CheapestProduct FROM Products;

Aggregate functions are often used in combination with the GROUP BY clause to perform calculations for groups of rows that have the same value in one or more columns. They are useful for extracting summarized information from data, e.g., total revenues, average values, highest and lowest values.

JOIN

Links rows from two tables based on matching values.

SELECT Orders.OrderNumber, Customers.Name, Products.Description
FROM Orders 
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
JOIN Products ON Orders.ProductID = Products.ProductID;

These are the most important commands for filtering, grouping, aggregating, and linking data in SQL for use on websites.

INSERT, UPDATE, DELETE

Adding, modifying, and deleting records

CREATE, ALTER, DROP

Tutorial: INSERT, UPDATE and DELETE in SQL

SQL (Structured Query Language) is the standard language for database administration. With the commands INSERT, UPDATE and DELETE you can add, modify, and delete records in a database.

Here is a short tutorial about it:

INSERT INTO - Add new record

The INSERT INTO command inserts a new record into a database table.

INSERT INTO TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);

INSERT INTO SQL - Example:

INSERT INTO Customers (Name, Address, City)
VALUES ('Max Mustermann', 'Musterstraße 1', 'Musterstadt');

UPDATE - Modify record With UPDATE you can modify one or more records based on a condition.

UPDATE TableName
SET Column1 = Value1, Column2 = Value2, ...
WHERE Condition;

UPDATE SQL - Example:

UPDATE Customers
SET City = 'Berlin'
WHERE Name = 'Max Mustermann';

DELETE - Delete record With DELETE you can delete one or more records based on a condition.

DELETE FROM TableName
WHERE Condition;

DELETE SQL - Example:

DELETE FROM Customers
WHERE City = 'Musterstadt';

Important notes:

• Always be careful with DELETE and UPDATE commands
• Use WHERE clause to specify which records to modify/delete
• Test queries before executing them on production data

Data Definition Language (DDL)

CREATE TABLE - Create new table

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE - Modify existing table

ALTER TABLE Customers ADD COLUMN Phone VARCHAR(20);
ALTER TABLE Customers MODIFY COLUMN Name VARCHAR(150);
ALTER TABLE Customers DROP COLUMN Phone;

DROP TABLE - Delete table

DROP TABLE Customers;

Advanced SQL Concepts

Subqueries

A query nested inside another query.

SELECT Name FROM Employees 
WHERE DepartmentID IN (
    SELECT DepartmentID FROM Departments 
    WHERE Location = 'Berlin'
);

EXISTS

Checks for the existence of rows in a subquery.

SELECT Name FROM Customers 
WHERE EXISTS (
    SELECT 1 FROM Orders 
    WHERE Orders.CustomerID = Customers.CustomerID
);

UNION and UNION ALL

Combines result sets from multiple queries.

SELECT Name FROM Employees 
UNION
SELECT Name FROM Managers;

SELECT Name FROM Employees 
UNION ALL
SELECT Name FROM Managers;

CASE Statements

Conditional logic in SQL.

SELECT 
    Name,
    CASE 
        WHEN Salary > 80000 THEN 'High'
        WHEN Salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;

Window Functions

Performs calculations across a set of table rows.

SELECT 
    Name,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank,
    AVG(Salary) OVER (PARTITION BY Department) AS DeptAvg
FROM Employees;

Transactions

Transaction Control

Ensures data integrity.

START TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;

COMMIT;
-- or ROLLBACK;

ACID Properties

• Atomicity: All operations succeed or none
• Consistency: Database remains in valid state
• Isolation: Concurrent transactions don’t interfere
• Durability: Changes persist after commit

Indexes

Creating Indexes

Improves query performance.

CREATE INDEX idx_customer_email ON Customers(Email);
CREATE INDEX idx_employee_department ON Employees(Department, Salary);

Types of Indexes

• B-Tree Index: Default for most databases
• Hash Index: Good for equality comparisons
• Full-Text Index: For text searching
• Spatial Index: For geographic data

Views

Creating Views

Virtual tables based on query results.

CREATE VIEW CustomerOrders AS
SELECT 
    c.Name,
    c.Email,
    COUNT(o.OrderID) AS OrderCount,
    SUM(o.TotalAmount) AS TotalSpent
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID;

Using Views

SELECT * FROM CustomerOrders WHERE OrderCount > 5;

Stored Procedures

Creating Procedures

Reusable SQL code blocks.

CREATE PROCEDURE GetCustomerOrders(IN CustomerID INT)
BEGIN
    SELECT 
        o.OrderID,
        o.OrderDate,
        o.TotalAmount
    FROM Orders o
    WHERE o.CustomerID = CustomerID
    ORDER BY o.OrderDate DESC;
END;

Calling Procedures

CALL GetCustomerOrders(123);

Triggers

Creating Triggers

Automatic actions on data changes.

CREATE TRIGGER update_product_stock
AFTER INSERT ON OrderItems
FOR EACH ROW
BEGIN
    UPDATE Products 
    SET StockQuantity = StockQuantity - NEW.Quantity 
    WHERE ProductID = NEW.ProductID;
END;

Best Practices

Performance Optimization

1. Use appropriate indexes for frequently queried columns
2. **Avoid SELECT *** in production code
3. Use parameterized queries to prevent SQL injection
4. Optimize JOIN operations with proper indexing
5. Use EXPLAIN to analyze query performance

Security

1. Use parameterized queries or prepared statements
2. Implement proper user permissions
3. Validate input data
4. Use encryption for sensitive data
5. Regular security audits

Code Quality

1. Use consistent naming conventions
2. Add comments for complex logic
3. Format SQL properly for readability
4. Use transactions for related operations
5. Handle errors appropriately

Common SQL Patterns

Pagination

SELECT * FROM Products 
ORDER BY ProductID 
LIMIT 10 OFFSET 20;

Hierarchical Data

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, Name, ManagerID, 1 as Level
    FROM Employees 
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy ORDER BY Level, Name;

Date/Time Operations

SELECT 
    CURRENT_DATE,
    CURRENT_TIME,
    CURRENT_TIMESTAMP,
    DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY),
    DATEDIFF('2026-12-31', CURRENT_DATE);

---

Recommended Literature: Databases