SQL Tutorial 2024: Essential Database Commands

This tutorial covers the essential SQL commands in a concise format with practical examples. SQL (MySQL, Oracle SQL, Microsoft SQL, and others) offers hundreds of functions and commands—many excellent books and online resources already document these extensively. Updated 2026

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

We provide a fast overview of the relevant commands.

**This SQL tutorial is updated regularly: **Current SQL topics overview

A comprehensive reference of the most important SQL commands and topics for web development:

SELECT

Querying Data

WHERE, ORDER BY, GROUP BY, HAVING Functions like COUNT, SUM, AVG JOIN to combine tables

SQL Tutorial for IRC-Coding.de

SELECT

The SELECT command retrieves data from a database table.

SELECT spalte1, spalte2 FROM tabelle;

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

WHERE

Filters records based on a condition.

SELECT * FROM Kunden WHERE Stadt = 'Bochum';

A WHERE clause in SQL restricts the result set of a query based on specific conditions. Here are some key considerations and best practices when using a WHERE clause:

Syntax and case sensitivity:

SQL keywords are case-insensitive, but it’s conventional to write them in uppercase to distinguish them from table and column names. String comparisons are case-sensitive in most SQL databases. This means ‘Bochum’ and ‘bochum’ are treated as different unless the database is configured for case-insensitive matching.

Quotation marks:

String values in SQL are typically enclosed in single quotes (’ ’). If a string contains a single quote, it must be escaped by doubling it or using an escape mechanism, for example, ‘O”Brien’ for the name “O’Brien”.

NULL values:

The query “WHERE Stadt = ‘Bochum’” returns no rows where the ‘Stadt’ column contains NULL. To include NULL values explicitly, use “IS NULL” or “IS NOT NULL”.

Performance optimization:

Indexes can significantly improve query performance. If the ‘Stadt’ column is used frequently in WHERE clauses, an index on that column can speed up queries. Applying functions to columns in the WHERE clause can disable indexes.

For example, “WHERE UPPER(Stadt) = ‘BOCHUM’” prevents the use of an index on the ‘Stadt’ column.

Multiple conditions:

You can combine multiple conditions with AND and OR. Order matters, and complex queries should use parentheses for clarity, for example, “WHERE Stadt = ‘Bochum’ AND (Alter > 30 OR Beruf = ‘Ingenieur’)”.

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 Stadt LIKE ‘Boc%’”.

Security:

Prevent 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 parameter binding
SELECT * 
FROM Kunden 
WHERE Stadt = ?;

Here, a placeholder (?) is used and later replaced safely—for example, with Prepared Statements—by the actual value.

ORDER BY

Sorts results by one or more columns.

SELECT * FROM Produkte ORDER BY Preis DESC;

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

SELECT Name, Alter FROM Kunden ORDER BY 2 DESC;

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

NULL values:

NULL values are handled specially in 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 control NULL placement, for example, ORDER BY Spalte ASC NULLS LAST.

Performance optimization:

Sorting can have significant performance implications, especially with large datasets. Indexes on sort columns can improve performance. Avoid applying functions to columns in the ORDER BY clause, as this can disable indexes, for example, ORDER BY UPPER(Name).

Combining ORDER BY with other clauses:

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

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

Note that an ORDER BY clause normally appears at the end of the SQL query, after WHERE, GROUP BY, and HAVING.

Sorting by computed columns:

You can sort by computed columns defined in the SELECT list:

SELECT Name, (Gehalt * 1.1) AS NeuesGehalt FROM Mitarbeiter ORDER BY NeuesGehalt DESC;

Collation:

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

Sorting with JOINs:

In queries using JOINs, you can sort by columns from any of the involved tables:

SELECT k.Name, o.Bestelldatum 
FROM Kunden k JOIN Bestellungen o 
ON k.KundenID = o.KundenID ORDER BY o.Bestelldatum;

Example query with ORDER BY:

SELECT Name, Stadt, Alter 
FROM Kunden 
ORDER BY Stadt ASC, Alter DESC;

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

GROUP BY

Groups rows with common values in a column.

SELECT Kategorie, SUM(Verkaufspreis) FROM Bestellungen GROUP BY Kategorie;

HAVING

Filters groups similarly to WHERE, but for aggregated values.

SELECT Kategorie, SUM(Verkaufspreis) FROM Bestellungen

The HAVING clause in SQL sets conditions on grouped results produced by the GROUP BY clause. Here are some key considerations and best practices for using HAVING:

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 occurs. HAVING is used to filter aggregated values.

Use with aggregate functions:

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

SELECT Stadt, COUNT(*) AS Kundenanzahl
FROM Kunden
GROUP BY Stadt
HAVING COUNT(*) > 5;

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

Syntax:

The HAVING clause always follows the GROUP BY clause. The correct clause order 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 WHERE. Example:

SELECT Stadt, AVG(Alter) AS Durchschnittsalter
FROM Kunden
GROUP BY Stadt
HAVING AVG(Alter) > 30 AND COUNT(*) > 10;

This query shows only cities where customer average age exceeds 30 and the customer count is greater than 10.

Performance optimization:

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

Example query with HAVING:

SELECT Stadt, COUNT(*) AS Kundenanzahl, AVG(Alter) AS Durchschnittsalter
FROM Kunden
WHERE Stadt IS NOT NULL
GROUP BY Stadt
HAVING COUNT(*) > 5 AND AVG(Alter) > 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 essential for query efficiency.

GROUP BY Kategorie

HAVING SUM(Verkaufspreis) > 1000;

Aggregate Functions

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

SELECT COUNT(*) AS AnzahlKunden FROM Kunden;

SELECT AVG(Preis) AS DurchschnittlichenPreis FROM Produkte;

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

COUNT Counts the number of rows in a group.

SELECT COUNT(*) AS AnzahlKunden FROM Kunden;

SUM Calculates the total of values in a specified column.

SELECT SUM(Preis) AS GesамtUmsatz FROM Bestellungen;

AVG Calculates the average value across a column.

SELECT AVG(Alter) AS DurchschnittsAlter FROM Nutzer;

MAX Returns the largest value in a column.

SELECT MAX(Gehalt) AS HoechstesGehalt FROM Mitarbeiter;

MIN Returns the smallest value in a column.

SELECT MIN(Preis) AS BilligstenProdukt FROM Produkte;

Aggregate functions are commonly combined with the GROUP BY clause to perform calculations on groups of rows sharing the same value in one or more columns. They’re useful for extracting summary information from data—total revenue, averages, maximum and minimum values, and more.

JOIN

Combines rows from two tables based on matching values.

SELECT Bestellungen.BestellNr, Kunden.Name, Produkte.Bezeichnung
FROM Bestellungen

JOIN Kunden ON Bestellungen.KundenID = Kunden.KundenID

JOIN Produkte ON Bestellungen.ProduktID = Produkte.ProduktID;

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 INSERT, UPDATE, and DELETE commands let you add, modify, and remove records from 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 TabellenName (Spalte1, Spalte2, ...)
VALUES (Wert1, Wert2, ...);

INSERT INTO Example:

INSERT INTO Kunden (Name, Adresse, Stadt)
VALUES ('Max Mustermann', 'Musterstraße 1', 'Musterstadt');

UPDATE – Modify a Record

Use UPDATE to change one or more records based on a condition.

UPDATE TabellenName
SET Spalte1 = Wert1, Spalte2 = Wert2, ...
WHERE Bedingung;

UPDATE Example:

UPDATE Kunden
SET Stadt = 'Berlin'
WHERE KundenID = 22;

DELETE FROM – Delete a Record

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

DELETE FROM TabellenName
WHERE Bedingung;

DELETE FROM Example:

DELETE FROM Bestellungen
WHERE BestellDatum < '2022-01-01';

With these three SQL commands, you maintain complete control over records in your database tables—adding, updating, or deleting data as needed for your website.

Creating, Modifying, and Dropping Tables

Data Types, Constraints, and Indexes

Here’s a SQL tutorial on creating, modifying, and dropping tables, along with data types, constraints, and indexes:

CREATE TABLE – Create a Table

CREATE TABLE TabellenName (
   Spalte1 Datentyp Constraint,
   Spalte2 Datentyp,
   Spalte3 Datentyp,
   ...
   CONSTRAINT Constraint_Name Constraint_Typ (Spalte)
);

Data types define what kind of information a column stores—INT for whole numbers, VARCHAR for text, and so on. Constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL enforce data integrity.

Let me break down the SQL CONSTRAINT command in straightforward terms: a constraint is a rule you apply to a column or table in your database. These rules ensure that only specific, approved data can be entered into the table.

There are several types of constraints:

PRIMARY KEY – Ensures each record in the column has a unique value, such as a customer ID. No two customers can share the same number.

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

NOT NULL – Requires the column to always contain a value; it cannot be empty.

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

CHECK – Sets a range of allowed values, such as requiring age to be between 18 and 99.

Constraints act as a quality gate, preventing incorrect, conflicting, or duplicate data from entering your database. They maintain data reliability and consistency.

ALTER TABLE – Modify a Table

ALTER TABLE TabellenName
  ADD Spalte Datentyp,
  DROP COLUMN Spalte,
  ALTER COLUMN Spalte Datentyp;

Use ALTER TABLE to add, remove, or change columns.

DROP TABLE – Delete a Table

DROP TABLE TabellenName;

Deletes the entire table including all its data.

SQL Constraints

PRIMARY KEY (Spalte) - Unique key per row
FOREIGN KEY (Spalte) REFERENCES andereTabelle(PrimärSchlüssel) - Links tables
UNIQUE (Spalte) - Unique values in column
NOT NULL - No NULL values allowed in column
CHECK (Bedingung) - Restricts value range

Constraints guarantee data integrity and consistency.

Indexes

CREATE INDEX IndexName ON TabellenName (Spalte);

Indexes speed up searches on indexed columns. With these commands, 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. Normalization optimizes the logical structure to avoid redundancy and integrity issues. 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, maintain consistency, and enable efficient queries. Well-normalized databases minimize anomalies during insert, update, and delete operations.

Relationships Between SQL Tables

Normalization creates separate tables linked by defined relationships. Depending on cardinality, we distinguish:

One-to-One (1:1)

A row in Table A corresponds to exactly one row in Table B. Example: A person has exactly one passport.

One-to-Many (1:N)

A row in Table A corresponds to many rows in Table B. Example: A customer has multiple orders.

Many-to-Many (N:M)

Multiple rows in A correspond to multiple rows in B. Requires a junction table. Example: Students enroll in multiple courses; courses have multiple students.

These relationships allow data to be linked and queried across table boundaries without creating redundancy.

Transactions

COMMIT and ROLLBACK

Isolation levels for consistency

Here’s a tutorial on 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 database reverts to its original state before START TRANSACTION.

Isolation Levels

They determine how concurrent transactions can view 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 increased 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 tutorial on security aspects in SQL - users, roles, permissions, and preventing SQL injections:

Users and Permissions

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

Create a new user

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

Grant read permission on a table

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

Grant write/update permissions on a table

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

All privileges on a database

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

SQL Database Roles

Instead of assigning permissions individually per user, you can define roles and add users to them:

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 inserted through unvalidated user input. This can compromise an entire database.

To prevent this:

• Never insert unvalidated user input directly into SQL commands
• Use prepared statements or query parameterization
• Grant minimum permissions to applications
• Validate and sanitize input (escaping, length limits)
• Keep database system patches up to date

Through restrictive user, role, and permission management combined 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 tutorial shows you how to secure your applications against SQL injections. We’ll walk through the process step by step, covering both fundamentals and advanced techniques.

Table of Contents - Preventing SQL Injection

What is SQL Injection?

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

1. What is SQL Injection?

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 altering 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 predefined routines 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 validated and sanitized. This includes checking for 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 Privileges

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 access:

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 detect SQL injections and other vulnerabilities.
• ESAPI: An OWASP API that provides protections against common security vulnerabilities.

5. Best Practices and Further Resources

Code Reviews

Conduct regular code reviews to identify security gaps.

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 speed up data retrieval in database tables. Think of them like an index in a book—they significantly accelerate lookup operations.

Create an index on a single column

CREATE INDEX index_name ON tabelle (spalte);

Composite index across multiple columns

CREATE INDEX index_name ON tabelle (spalte1, spalte2);

Index the columns you query frequently in WHERE, JOIN, and ORDER BY clauses. Keep in mind that too many indexes can hurt write performance.

Query Optimization

Optimizing SQL queries can dramatically improve 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 letters: 'name%' instead of '%name%'
• LIMIT and pagination: Never select all rows for large datasets

Specific techniques:

• Partitioning: For very large tables, partition by date ranges and 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 replicas to offload read queries
• Sharding: Distribute the database across multiple servers for very large datasets

Regular monitoring and optimization of slow queries and hotspots are key to maintaining a high-performing 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 datenbank1 datenbank2 > backup.sql

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

Incremental backup

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

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

Replication

Replication distributes data across multiple systems for high availability and load distribution.

On the master:

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

On the slave:

SLAVE START;

Clustering

Multiple servers form a cluster to distribute load and processing. 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

Configure data and management nodes

Import/Export

Import data from a backup file

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

Import data from a CSV file

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

Export data to CSV

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

These commands and processes are essential for managing, securing, distributing, and exchanging database data. Careful administration is crucial 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 every aspect of working with databases.

SQL and Security

• SQL Injection: Security Risks and Safeguards - Comprehensive security guide with practical examples
• SQL vs NoSQL Comparison - Compare relational and document-oriented databases