SQL Tutorial 2024: Essential Database Commands

This quick reference tutorial covers the essential SQL commands with concise explanations and practical examples.

SQL (MySQL, Oracle SQL, Microsoft SQL Server, and others) provides hundreds of functions and commands—topics already well documented in numerous books and online resources. Last updated 2026.

SQL (Structured Query Language) is the standard language for managing databases. Here’s a quick guide to the most important SQL commands for web applications:

We provide a fast overview of each command.

This SQL tutorial is updated regularly:

Current SQL topics include:

• A comprehensive overview of essential SQL commands and concepts for web development
• SELECT statements
• WHERE clauses, ORDER BY, GROUP BY, HAVING
• Aggregate functions like COUNT, SUM, AVG
• JOINs for combining tables

SQL Tutorial for IRC-Coding.de

SELECT

The SELECT command retrieves data from a database table.

SELECT column1, column2 FROM table;

Here’s a quick reference for the SQL commands and concepts covered:

WHERE

Filters records based on a condition.

SELECT * FROM customers WHERE city = 'Bochum';

The WHERE clause in SQL restricts query results based on specific conditions. Here are important considerations and best practices when using WHERE:

Syntax and case sensitivity:

SQL keywords are case-insensitive, though it’s standard 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 treated as different values, unless your database is configured for case-insensitive matching.

Quotation marks:

String values in SQL are typically wrapped in single quotes (’ ’).

If a string contains a single quote, escape it by doubling it—for example, ‘O”Brien’ for the name “O’Brien”.

NULL values:

The query “WHERE city = ‘Bochum’” will not return rows where the city column is NULL. To include NULL values, use IS NULL or IS NOT NULL explicitly.

Performance optimization:

Indexes can significantly improve query speed.

If the city column is frequently used in WHERE clauses, adding an index on that column will boost performance.

Applying functions to columns in the WHERE clause can disable indexes.

For example, “WHERE UPPER(city) = ‘BOCHUM’” prevents the index on the city column from being used.

Multiple conditions:

Combine multiple conditions with AND and OR. Use parentheses in complex queries to clarify precedence—for example, “WHERE city = ‘Bochum’ AND (age > 30 OR job = ‘Engineer’)”.

Wildcard search:

Use LIKE for pattern matching. The underscore (_) represents a single character, and the percent sign (%) represents any number of characters—for example, “WHERE city LIKE ‘Boc%’”.

Security:

Avoid SQL injection attacks by using parameterized queries, especially with user input. This is critical for dynamic SQL statements.

A secure and optimized query might look like this:

-- Example of a secure query with parameterized binding
SELECT * 
FROM customers 
WHERE city = ?;

Here, a placeholder (?) is used and later replaced safely via a method like Prepared Statements.

ORDER BY

Sorts results by one or more columns.

SELECT * FROM products ORDER BY price DESC;

You can use column positions in the SELECT list for sorting:

SELECT name, age FROM customers ORDER BY 2 DESC;

This sorts by the second column (age) in descending order. This approach reduces readability and is often discouraged.

NULL values:

NULL values are handled specially in sort order. In some SQL databases they appear first, in others last, depending on whether you’re sorting ascending or descending.

Many SQL dialects offer explicit options to control NULL placement, such as ORDER BY column ASC NULLS LAST.

Performance optimization:

Sorting can significantly impact performance, especially with large datasets. Indexes on sort columns improve speed.

Avoid applying functions to columns in the ORDER BY clause, as this disables indexes—for example, avoid ORDER BY UPPER(name).

Combining ORDER BY with other clauses:

ORDER BY works with LIMIT or FETCH FIRST to return paginated results:

SELECT * FROM customers ORDER BY name LIMIT 10 OFFSET 20;

Important: An ORDER BY clause typically appears at the end of your query, after WHERE, GROUP BY, and HAVING.

Sorting by computed columns:

You can sort by expressions defined in the SELECT list:

SELECT name, (salary * 1.1) AS new_salary FROM employees ORDER BY new_salary DESC;

Collation:

Sort order is influenced by collation, which determines how text values are ordered. Different collations can produce different sort orders for strings.

Sorting with JOINs:

When your query uses JOINs, you can sort by columns from any of the joined tables:

SELECT c.name, o.order_date 
FROM customers c JOIN orders o 
ON c.customer_id = o.customer_id ORDER BY o.order_date;

Example query with ORDER BY:

SELECT name, city, age 
FROM customers 
ORDER BY city ASC, age DESC;

This query sorts results first by city in ascending order, then by age in descending order within each city.

GROUP BY

Groups rows that share common values in a column.

SELECT category, SUM(sale_price) FROM orders GROUP BY category;

HAVING

Filters groups similar to WHERE, but applies to aggregated values.

SELECT category, SUM(sale_price) FROM orders

The HAVING clause in SQL sets conditions on grouped results produced by the GROUP BY clause.

Here are important considerations and best practices when using HAVING:

Difference between WHERE and HAVING:

The WHERE clause filters rows before grouping, while HAVING filters groups after aggregation.

Example: WHERE is used to filter rows based on individual row values before any aggregation occurs. HAVING is used to filter aggregated values.

Using with aggregate functions:

HAVING is typically combined with aggregate functions such as COUNT, SUM, AVG, MAX, and MIN.

Example:

SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 5;

This query counts customers in each city and displays only cities with more than 5 customers.

Syntax:

The HAVING clause always follows GROUP BY in your query.

The correct clause order is:

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

Multiple conditions:

You can combine multiple conditions in HAVING using AND and OR, just like in WHERE.

Example:

SELECT city, AVG(age) AS average_age
FROM customers
GROUP BY city
HAVING AVG(age) > 30 AND COUNT(*) > 10;

This displays only cities where the average customer age exceeds 30 and there are more than 10 customers.

Performance optimization:

Like WHERE, HAVING can affect performance, especially with large datasets.

Efficient use of indexes and avoiding unnecessary calculations improve speed.

It’s often more efficient to place conditions that apply to individual rows in the WHERE clause and reserve HAVING only for aggregate-based conditions.

Example query with HAVING:

SELECT city, COUNT(*) AS customer_count, AVG(age) AS average_age
FROM customers
WHERE city IS NOT NULL
GROUP BY city
HAVING COUNT(*) > 5 AND AVG(age) > 30;

This query displays cities with more than 5 customers and an average age over 30.

Summary:

WHERE filters before aggregation. HAVING filters after aggregation. HAVING is primarily used with aggregate functions. Correct placement of conditions between WHERE and HAVING is crucial for query efficiency.

GROUP BY category

HAVING SUM(sale_price) > 1000;

Aggregate Functions

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

SELECT COUNT(*) AS CustomerCount FROM Customers;

SELECT AVG(Price) AS AveragePrice FROM Products;

Aggregate functions are special SQL operations that perform calculations across multiple rows in a table or filtered result set. They condense the values from a specified column into a single result. The main aggregate functions are:

COUNT

Counts the number of rows in a group.

SELECT COUNT(*) AS CustomerCount 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 LowestPrice FROM Products;

Aggregate functions are often combined with the GROUP BY clause to perform calculations on groups of rows that share the same value in one or more columns. They’re useful for extracting summary information from your data—such as total revenue, averages, and highest and lowest values.

JOIN

Combines 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 core commands for filtering, grouping, aggregating, and joining 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. The commands INSERT, UPDATE, and DELETE let you add, modify, and remove records in a database.

Here’s a quick tutorial:

INSERT INTO – Add a New Record

The INSERT INTO command adds a new record to a database table.

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

INSERT INTO SQL Example:

INSERT INTO Customers (Name, Address, City)
VALUES ('John Smith', 'Main Street 1', 'Springfield');

UPDATE – Modify a Record

With UPDATE, you can change one or more records based on a condition.

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

SQL Update Example:

UPDATE Customers
SET City = 'Berlin'
WHERE CustomerID = 22;

DELETE FROM – Delete a Record

The DELETE FROM command removes one or more records from a table.

DELETE FROM TableName
WHERE Condition;

DELETE FROM Example:

DELETE FROM Orders
WHERE OrderDate < '2022-01-01';

With these three SQL commands, you maintain full control over the records in your database tables—whether inserting, modifying, or deleting data for your website.

Creating, Modifying, and Deleting Tables

Data Types, Constraints, Indexes

Here’s a SQL tutorial on creating, modifying, and deleting tables, plus working with data types, constraints, and indexes:

CREATE TABLE – Create a Table

CREATE TABLE TableName (
   Column1 DataType Constraint,
   Column2 DataType,
   Column3 DataType,
   ...
   CONSTRAINT ConstraintName ConstraintType (Column)
);

Data types define the kind and format of data stored in a column—for example, INT for whole numbers or VARCHAR for text. Constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL enforce data integrity.

I’ll explain the SQL CONSTRAINT command for tables as simply as possible: a constraint is a rule you set for a column or table in your database. These rules ensure that only specific, permitted data can be entered into the table.

Several types of constraints exist:

PRIMARY KEY – Specifies that each record in the column must have a unique value, such as a customer ID. No two customers can have the same number.

FOREIGN KEY – Links data between two tables, such as connecting a customer’s orders to their contact information.

NOT NULL – States that the column must always contain a value and cannot be empty.

UNIQUE – Similar to PRIMARY KEY, but values in the column cannot be duplicated.

CHECK – You set a value range, for example, age must be between 18 and 99.

Constraints help prevent incorrect, contradictory, or duplicate data from entering your database. They serve as quality control for your data.

ALTER TABLE – Modify a Table

ALTER TABLE TableName
  ADD Column DataType,
  DROP COLUMN Column,
  ALTER COLUMN Column DataType;

With ALTER TABLE, you can add, remove, or modify columns.

DROP TABLE – Delete a Table

DROP TABLE TableName;

Deletes the entire SQL table, including all its data.

SQL Constraints

PRIMARY KEY (Column) – Unique key per row
FOREIGN KEY (Column) REFERENCES OtherTable(PrimaryKey) – Link between tables
UNIQUE (Column) – Unique values in column
NOT NULL – No NULL values in column
CHECK (Condition) – Restrict value range

Constraints ensure data integrity and consistency.

Indexes

CREATE INDEX IndexName ON TableName (Column);

Indexes speed up searches for records based on the indexed column(s).

With these statements, you can manage tables, define data types and constraints, and create indexes for better performance.

Database Model

Normalization for Data Integrity

Relationships: One-to-One, One-to-Many, Many-to-Many

Here’s an explanation and tutorial on database models, normalization, and relationships:

Database Model and Normalization

A database model describes how data is structured and organized in a database. Through normalization, the logical database structure is optimized to avoid redundancy and integrity problems. Normalization is achieved by applying rules (first, second, third normal form, and so on) to your tables:

Each table cell must contain exactly one value. Each column must have a unique name. Different types of data must be stored in separate tables.

The goal is to store data without redundancy, consistently, and efficiently. Well-normalized databases minimize anomalies during insert, update, and delete operations.

Relationships Between SQL Tables

Normalization creates separate tables that are linked through defined relationships. Depending on cardinality, we distinguish:

One-to-One (1:1)

One row from Table A is assigned to exactly one row from Table B.

Example: A person has exactly one passport.

One-to-Many (1:N)

One row from Table A is assigned to multiple rows from Table B.

Example: A customer has many orders.

Many-to-Many (N:M)

Multiple rows from A are assigned to multiple rows from B. Requires a junction table.

Example: Students enroll in multiple courses; courses have multiple students.

Through these relationships, data can be linked and queried across table boundaries without creating redundancy.

Transactions

COMMIT and ROLLBACK

Isolation levels for consistency

Here’s a guide to transactions in SQL - COMMIT, ROLLBACK and isolation levels:

Transactions

A transaction is a sequence of SQL statements treated as a single logical unit. Either all statements execute successfully, or none do. This ensures data atomicity and consistency.

START TRANSACTION;
SQL statement 1;
SQL statement 2;
...
COMMIT

COMMIT persists the changes made by SQL statements within a transaction to the database.

COMMIT;

All changes since START TRANSACTION are written. After that, the transaction is complete.

ROLLBACK

ROLLBACK cancels all changes within a transaction.

ROLLBACK;

The data is restored to its original state before START TRANSACTION.

Isolation Levels

They determine how concurrent transactions can see and affect data:

• READ UNCOMMITTED - Dirty reads possible, no consistency
• READ COMMITTED - Only committed data readable, no phantom reads
• REPEATABLE READ - Read locks, no lost updates
• SERIALIZABLE - Write locks, complete transaction isolation

The higher the level, the better the data consistency and isolation, but with more overhead.

Transactions with COMMIT/ROLLBACK and defined isolation levels form the foundation for reliable and correct database operations. They prevent damage from competing access.

Security

Users, Roles, Permissions

Preventing SQL injection

Here’s a guide to SQL security aspects - users, roles, permissions, and SQL injection prevention:

Users and Permissions

In SQL databases, separate user accounts can be created. Each user is assigned only the necessary permissions (need-to-know principle):

Create a new user

CREATE User 'schutzgeist'@'localhost' IDENTIFIED BY 'passwort123';

Grant read permission to a table

GRANT SELECT ON mitarbeiter TO 'schutzgeist'@'localhost';

Grant write/update permissions to a table

GRANT INSERT, UPDATE ON bestellungen TO 'schutzgeist'@'localhost';

Grant all rights to a database

GRANT ALL PRIVILEGES ON firma.* TO 'schutzgeist'@'localhost';

SQL Database Roles

Instead of assigning permissions individually per user, roles can be defined and users added to those roles:

Create a new role

CREATE ROLE 'ServicesAdmin';

Grant read permission to the role

GRANT SELECT ON finanzen.* TO 'ServicesAdmin';

Add user to the role

GRANT 'Servicesadmin' TO 'schutzgeist'@'localhost';

Preventing SQL Injections

SQL injections are attacks where malicious SQL code is injected through unvalidated user input. This can compromise the entire database.

To prevent this:

• Never insert unvalidated user input directly into SQL commands
• Use prepared statements or query parameterization
• Grant minimal permissions to applications
• Validate and sanitize inputs (escaping, length limits)
• Install the latest patches for your database system

Through restrictive user, role, and permission management along with SQL injection prevention, you can effectively protect database security and integrity.

SQL injections rank among the most common and dangerous security vulnerabilities in web applications. This guide shows you how to secure your applications against SQL injections. We take a step-by-step approach, covering both fundamentals and advanced techniques.

Table of Contents - Preventing SQL Injection

What is a SQL injection?

• How do SQL injections work?
• Preventive measures against SQL injections
• Prepared statements
• Using stored procedures
• Input validation and sanitization
• Minimizing database user permissions
• Using ORM (Object-Relational Mapping)
• Security tools and libraries
• Best practices and further resources

1. What is a SQL injection?

A SQL injection is a vulnerability where attackers insert malicious SQL code into a query sent to a database. This can lead to unauthorized data access, data loss, or data manipulation.

2. How do SQL injections work?

SQL injections occur when user input is directly embedded in SQL queries without proper validation or sanitization. A simple example:

SELECT * FROM users WHERE username = 'user' AND password = 'pass';

An attacker could use the following input:

Username: ’ OR ‘1’=‘1 Password: ” OR ‘1’=‘1

This would result in a query that is always true:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1';

3. Preventive Measures Against SQL Injections

Prepared Statements

Prepared statements are SQL queries where the query structure is compiled in advance and user input is passed as parameters. This prevents input from modifying the SQL code.

Example in PHP with PDO:

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

Using Stored Procedures

Stored procedures are saved procedures in the database that can be called. They isolate SQL code and prevent direct manipulation through user input.

Example in MySQL:

DELIMITER //
CREATE PROCEDURE GetUser(IN username VARCHAR(50), IN password VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = username AND password = password;
END //
DELIMITER ;

Input Validation and Sanitization

All input should be checked for validity and cleaned. This includes verifying expected data types, length restrictions, and special characters.

Example in PHP:

$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
$password = filter_input(INPUT_POST, 'password', FILTER_SANITIZE_STRING);

Minimizing Database User Permissions

Grant only the minimum necessary permissions to database users. This prevents attackers from causing widespread damage, even if they gain access.

Example:

Create a user with read-only permissions:

CREATE User 'readonly'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON mydatabase.* TO 'readonly'@'localhost';

Using ORM (Object-Relational Mapping)

ORM libraries abstract database access and provide built-in protection against SQL injections. Examples include Hibernate for Java and Entity Framework for .NET.

Example in Python with SQLAlchemy:

user = session.query(User).filter_by(username='user', password='pass').first()

4. Security Tools and Libraries

• SQLMap: An open-source tool for detecting and exploiting SQL injections.
• OWASP ZAP: A security scanner that can identify SQL injections and other vulnerabilities.
• ESAPI: An OWASP API that provides protection mechanisms against common security flaws.

5. Best Practices and Further Resources

Code Reviews

Perform regular code reviews to identify security vulnerabilities.

Automated Testing

Implement tests specifically designed to detect SQL injections.

Performance

Indexing

Query Optimization

Here’s a tutorial on performance considerations in SQL databases—indexing and query optimization:

Indexing

Indexes enable faster data retrieval from database tables. They work like an index in a book, speeding up search operations significantly.

Create an index on a single column

CREATE INDEX index_name ON table (column);

Create a composite index across multiple columns

CREATE INDEX index_name ON table (column1, column2);

Index columns that appear frequently in WHERE, JOIN, or ORDER BY clauses. However, too many indexes can hurt write performance.

Query Optimization

Optimizing SQL queries significantly improves database application performance.

General tips:

• Indexing: Use indexes for frequently queried columns
• EXPLAIN: Analyze execution plans with EXPLAIN
• Avoid nested queries: Use JOINs instead of multiple subqueries
• LIKE patterns starting with a letter: 'name%' instead of '%name%'
• LIMIT and pagination: Never select all rows for large datasets

Specific techniques:

• Partitioning: Partition very large tables by date ranges or similar criteria
• Indexed views: Query from a view instead of complex queries on tables
• Caching: Cache query results, for example in Redis
• Replication: Use read replica servers to offload read queries
• Sharding: Distribute the database across multiple servers for very large datasets

Regular monitoring and optimization of slow queries and hotspots is key to a performant database application.

Database Administration

Backups, Replication, Clustering

Import/Export

Here’s a tutorial on database administration covering backups, replication, clustering, and import/export operations:

Backups

Regular backups protect against data loss and enable recovery in case of failure.

Full backup

mysqldump --user=root --password --databases database1 database2 > backup.sql

Creates a backup file backup.sql containing the complete contents of all databases.

Incremental backup

mysqldump --user=root --password --databases database1 --single-transaction > incrbackup.sql

Backs up only data that has changed since the last backup.

Replication

Distributes data across multiple systems for high availability and load balancing.

On master:

CHANGE MASTER TO MASTER_LOG_FILE='log_file', MASTER_LOG_POS=log_position;

On slave:

SLAVE START;

Clustering

Multiple servers form a cluster for performance and load distribution. This combines master and slave into a synchronized high-availability setup.

NDB Cluster (MySQL) SQL node: Routes queries

mysqld --ndb-cluster --ndb-connectstring=192.168.0.1

Set up data and management nodes

Import/Export

Import data from a backup file

mysql --user=root --password database < backup.sql

Import data from a CSV file

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Export data to CSV

SELECT *
FROM table
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

These commands and processes are central to managing, backing up, distributing, and exchanging database data. Careful administration is critical for performance, availability, and data protection.

---

Recommended Reading: Databases

More Database Articles

Databases and SQL are fundamental technologies in software development. The following articles help you understand and master all aspects of databases.

SQL and Security

• SQL Injection: Security Risk and Protection Measures - Comprehensive security guide with practical examples
• SQL vs NoSQL Comparison - Comparing relational and document-oriented databases