Why learn SQL?

🌙 ↑

SQL from Basics to Advanced Functions

Published on poligon-srv.pl

Table of Contents

Why Learn SQL?

SQL (Structured Query Language) is the lingua franca of relational databases. Whether you're managing servers, building backends in PHP/Node/.NET, working with WordPress, or performing ETL in Power BI, sooner or later, you'll need to interact with a database. This article is a comprehensive guide, covering everything from the basics to advanced window functions and data transformations.

SQL Command Categories

DDL (Data Definition Language) – Defines and modifies database structure. Commands include: CREATE, ALTER, DROP, TRUNCATE.

DML (Data Manipulation Language) – Manipulates data in tables. Commands include: INSERT, UPDATE, DELETE.

DQL (Data Query Language) – Retrieves data from the database. The main command is SELECT (sometimes categorized under DML but often treated separately due to its specificity and frequent use).

TCL (Transaction Control Language) – Manages transactions to ensure data integrity. Commands include: COMMIT, ROLLBACK, SAVEPOINT.

CRUD in Practice

-- C (Create): INSERT – Adding new records INSERT INTO users (login, email) VALUES ('yaro', 'yaro@poligon-srv.pl'); INSERT INTO products (name, price, category_id) VALUES ('Laptop', 3500.00, 101); -- R (Read): SELECT – Retrieving data SELECT id, login, email FROM users WHERE login = 'yaro'; SELECT product_name, price FROM products WHERE price > 1000 ORDER BY price DESC; -- U (Update): UPDATE – Modifying existing records UPDATE users SET email = 'admin@poligon-srv.pl' WHERE id = 42; UPDATE products SET stock_quantity = 50 WHERE product_id = 123; -- D (Delete): DELETE – Removing records DELETE FROM users WHERE last_login < '2023-01-01'; DELETE FROM temporary_logs WHERE created_at < CURRENT_DATE - INTERVAL '30 days'; Copy 💡 Tip: DELETE removes rows one by one (logging them, allowing rollback within a transaction), while TRUNCATE clears the entire table faster, deleting all data without logging individual records and often without the possibility of ROLLBACK (depending on the engine and mode).

Creating and Modifying Structures

-- New database and table CREATE DATABASE poligon; USE poligon; -- MySQL/MariaDB – selects the active database CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, login VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Example for PostgreSQL with serial: CREATE TABLE products ( product_id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) NOT NULL DEFAULT 0.00, category VARCHAR(100), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); -- Modifying a table – adding, altering, and dropping columns ALTER TABLE users ADD COLUMN is_active BIT DEFAULT 1; ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL; -- MySQL ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 4); -- PostgreSQL - changing type ALTER TABLE users DROP COLUMN is_active; -- Dropping a column -- Deleting a table or its data TRUNCATE TABLE users_logs; -- Clears table contents but retains structure DROP TABLE old_backup; -- Drops the table entirely (structure and data) Copy

Indexes

Indexes are critical for optimizing SELECT query performance, but excessive use can slow down INSERT, UPDATE, and DELETE operations due to the need to update them.

-- Creating an index on the email column in the users table CREATE INDEX idx_users_email ON users(email); -- Creating a composite (multi-column) index CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); -- Dropping an index (syntax varies by engine) -- MySQL: DROP INDEX idx_users_email ON users; -- SQL Server: DROP INDEX idx_users_email ON users; -- PostgreSQL: DROP INDEX idx_users_email; Copy

Relationships and Constraints

PRIMARY KEY – A unique identifier for each row in a table. Ensures uniqueness and fast lookup.

FOREIGN KEY – A key that references a primary key in another table, ensuring referential integrity.

NOT NULL – Restricts a column from accepting NULL values.

UNIQUE – Ensures all values in a column (or set of columns) are unique.

CHECK – Validates data to meet specific conditions (e.g., age >= 0).

DEFAULT – Assigns a default value to a column if no value is provided during insertion.

CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT NOT NULL, total DECIMAL(10,2) CHECK (total >= 0), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE -- Specifies what happens when a record in the 'users' table is deleted ); Copy

JOIN

The JOIN statement combines rows from two or more tables based on a related column.

INNER JOIN – Returns only rows with matching values in both tables.

LEFT JOIN (or LEFT OUTER JOIN) – Returns all rows from the left table and matching rows from the right table. If there's no match, returns NULL for right table columns.

RIGHT JOIN (or RIGHT OUTER JOIN) – Similar to LEFT JOIN, but returns all rows from the right table and matching rows from the left table.

FULL JOIN (or FULL OUTER JOIN) – Returns all rows when there's a match in either the left or right table. If there's no match, returns NULL for columns from the other table. (Some engines, like MySQL, don't natively support FULL JOIN).

SELECT u.login, o.id AS order_id, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id ORDER BY u.login; Copy

FULL OUTER JOIN (where supported, e.g., PostgreSQL, SQL Server)

SELECT a.id, a.val AS a_val, b.val AS b_val FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id; Copy i MySQL does not natively support FULL JOIN – achieve the same effect by combining LEFT JOIN and RIGHT JOIN via UNION.

UNION / UNION ALL

The UNION and UNION ALL statements combine the results of two or more SELECT queries into a single result set.

UNION – Combines results and automatically removes duplicates.

UNION ALL – Combines results without removing duplicates, typically faster than UNION as it avoids the sorting and deduplication step.

-- Without duplicates SELECT id, name FROM products_2023 UNION SELECT id, name FROM products_2024; -- With duplicates SELECT id, name FROM products_2023 UNION ALL SELECT id, name FROM products_2024; Copy

Important: For UNION or UNION ALL, all SELECT queries must have the same number of columns, with compatible data types in the same order.

EXISTS

The EXISTS operator is used in subqueries to check for the existence of rows. It returns TRUE if the subquery returns at least one row, and FALSE otherwise. It is highly efficient as it stops scanning once a matching row is found.

SELECT u.* FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 0 ); Copy

The above query selects all users who have placed at least one order with a total greater than 0.

SELECT INTO

The SELECT INTO statement creates a new table and inserts the results of a SELECT query into it. It's a quick way to copy data into a new or temporary table.

In SQL Server (and partially in PostgreSQL):

SELECT * INTO users_backup_2024 FROM users WHERE created_at >= '2024-01-01'; Copy

In PostgreSQL, CREATE TABLE AS is more commonly used:

CREATE TABLE users_backup_2024 AS SELECT * FROM users WHERE created_at >= '2024-01-01'; Copy

Aggregations and Grouping

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) perform calculations on a set of rows and return a single value. The GROUP BY clause groups rows with the same values in specified columns, enabling aggregate functions to be applied to each group. The HAVING clause filters groups after aggregation.

-- Number of orders and total sum per user SELECT u.login, COUNT(o.id) AS order_count, SUM(o.total) AS total_orders, AVG(o.total) AS avg_order_value FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.login HAVING SUM(o.total) > 1000; -- Filters groups with total orders exceeding 1000 Copy

ROUND – Rounding Numeric Values

The ROUND() function rounds numeric values to a specified number of decimal places.

SELECT total, ROUND(total, 2) AS total_2_decimals FROM orders; Copy

DISTINCT is useful to avoid duplicates:

SELECT DISTINCT email FROM users; SELECT DISTINCT category FROM products; -- List of unique product categories Copy

Sorting and Pagination

Sorting (ORDER BY) organizes query results. Pagination (LIMIT/OFFSET or OFFSET/FETCH NEXT) divides large result sets into smaller, manageable pages, crucial for web applications.

-- MySQL / PostgreSQL SELECT FROM logs ORDER BY created_at DESC LIMIT 50 OFFSET 100; -- SQL Server (since SQL Server 2012) SELECT FROM logs ORDER BY created_at DESC OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY; -- SQL Server (older style – retrieving only TOP N records) SELECT TOP 50 * FROM logs ORDER BY created_at DESC; Copy

Filters and Logical Operators

Filters in the WHERE clause allow precise row selection based on conditions. They use comparison operators (=, !=, >, =,

SELECT * FROM products WHERE price BETWEEN 100 AND 500 AND category IN ('GPU','CPU') AND name LIKE '%Ryzen%' AND discontinued IS NOT NULL AND NOT (stock = 0 AND is_virtual = 0); Copy

CTE and Complex Queries

Common Table Expression (CTE)

A CTE (WITH clause) is a temporary, named result set that can be referenced within a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve the readability and modularity of complex queries.

WITH monthly_sales AS ( SELECT DATE_FORMAT(created_at, '%Y-%m') AS sale_month, -- MySQL/MariaDB -- DATE_TRUNC('month', created_at) AS sale_month, -- PostgreSQL -- FORMAT(created_at, 'yyyy-MM') AS sale_month, -- SQL Server SUM(total) AS total_sales FROM orders GROUP BY sale_month ) SELECT sale_month, total_sales FROM monthly_sales WHERE total_sales > 5000 ORDER BY sale_month; Copy

CASE / IIF

The CASE statement defines conditional results in a SELECT query, useful for categorizing data, creating flags, or dynamic formatting. IIF is a shorthand version of CASE available in some engines (e.g., SQL Server).

SELECT id, total, CASE WHEN total = 0 THEN 'free' WHEN total < 100 THEN 'cheap' ELSE 'expensive' END AS price_tag, CASE total -- Shorter syntax for equality comparisons WHEN 0 THEN 'Zero Value' WHEN 100 THEN 'One Hundred' ELSE 'Other Value' END AS specific_price_tag FROM orders; -- In SQL Server: IIF(condition, true_value, false_value) SELECT IIF(total > 1000, 'VIP', 'STD') AS tier FROM orders; Copy

Window Functions

Window functions perform calculations across a set of rows (a window) related to the current row. Unlike aggregate functions, they don't group results and return a value for each row, preserving data granularity.

SELECT user_id, total, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn, -- Numbers rows within each user_id group LAG(total, 1, 0) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total, -- Retrieves 'total' from the previous row in the group LEAD(total, 1, 0) OVER (PARTITION BY user_id ORDER BY created_at) AS next_total, -- Retrieves 'total' from the next row in the group SUM(total) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum, -- Running sum of orders for each user AVG(total) OVER (PARTITION BY user_id) AS avg_user_total -- Average order value per user FROM orders; Copy

PARTITION BY: Divides the result set into partitions (groups) for the window function. ORDER BY: Specifies the row order within each partition. ROWS BETWEEN ... AND ...: Defines the window for the function (e.g., UNBOUNDED PRECEDING means from the partition's start, CURRENT ROW is the current row).

Date and Time Operations

SQL provides many functions for manipulating dates and times, varying by database engine.

-- Current time SELECT CURRENT_TIMESTAMP AS now_with_time; -- MySQL, PostgreSQL, SQL Server SELECT CURRENT_DATE AS today; -- MySQL, PostgreSQL, SQL Server SELECT CURRENT_TIME AS now; -- MySQL, PostgreSQL, SQL Server -- Adding/subtracting periods -- SQL Server: SELECT DATEADD(day, 7, CURRENT_TIMESTAMP) AS next_week; SELECT DATEADD(month, -1, CURRENT_DATE) AS last_month; -- MySQL: SELECT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AS next_week; SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH) AS last_month; -- PostgreSQL: SELECT CURRENT_TIMESTAMP + INTERVAL '7 days' AS next_week; SELECT CURRENT_DATE - INTERVAL '1 month' AS last_month; -- Date difference -- SQL Server: SELECT DATEDIFF(day, created_at, CURRENT_TIMESTAMP) AS days_passed FROM orders; -- MySQL: SELECT DATEDIFF(CURRENT_TIMESTAMP, created_at) AS days_passed FROM orders; -- PostgreSQL: SELECT (CURRENT_TIMESTAMP - created_at) AS time_passed FROM orders; -- Returns 'interval' type SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - created_at)) / 86400 AS days_passed FROM orders; -- Days as a number Copy

String Operations

String functions are essential for manipulating text data, such as names, emails, or comments.

SELECT CONCAT(first_name, ' ', last_name) AS full_name, -- Concatenates strings (MySQL, PostgreSQL, SQL Server since 2012) first_name || ' ' || last_name AS full_name_pg, -- PostgreSQL alternative SUBSTRING(email, 1, CHARINDEX('@', email) - 1) AS login, -- Extracts substring (SQL Server, MySQL, PostgreSQL use SUBSTRING(string FROM start FOR length)) LEFT(email, INSTR(email, '@') - 1) AS login_mysql, -- MySQL alternative REPLACE(phone, '-', '') AS phone_clean, -- Replaces substrings LEN(comment) AS comment_len, -- String length (SQL Server), LENGTH in MySQL/PostgreSQL UPPER(color) AS country_up, -- Converts to uppercase TRIM(' ' FROM note) AS note_trimmed, -- Removes whitespace (in MySQL 8: TRIM(note)) LPAD(id, 5, '0') AS padded_id -- Pads with characters on the left to reach a specified length (PostgreSQL, MySQL) FROM users; Copy

Handling NULLs

The NULL value represents missing data. Proper handling of NULLs is crucial to avoid errors and ensure correct business logic.

COALESCE(expr1, expr2, …) – Returns the first non-NULL value from a list of expressions, useful for providing default or fallback values.

NULLIF(a, b) – Returns NULL if a equals b. Otherwise, returns a. Often used to prevent division by zero or to convert specific values to NULL.

IS NULL / IS NOT NULL – Operators to check if a value is (or is not) NULL.

SELECT COALESCE(nick, login, 'anon') AS display_name FROM users; -- Returns nick if present, else login, else 'anon' SELECT NULLIF(status, 'UNKNOWN') AS status_or_null FROM devices; -- Returns NULL if status is 'UNKNOWN', else status SELECT * FROM products WHERE description IS NULL; -- Selects products without a description Copy

Table Transformations

Transformations like PIVOT and UNPIVOT change table structure, turning rows into columns or columns into rows, useful for reporting and analysis.

UNPIVOT converts columns into rows, useful for denormalizing reports.

-- SQL Server SELECT product_id, metric, value FROM sales UNPIVOT (value FOR metric IN (q1, q2, q3, q4)) AS u; Copy i In many engines (e.g., MySQL, PostgreSQL), use UNION ALL or JSON functions instead of native UNPIVOT to achieve a similar effect.

Example UNPIVOT with UNION ALL (for MySQL/PostgreSQL): If you have a sales table with columns product_id, q1 (quarter 1 sales), q2, etc.:

SELECT product_id, 'q1' AS metric, q1 AS value FROM sales UNION ALL SELECT product_id, 'q2' AS metric, q2 AS value FROM sales UNION ALL SELECT product_id, 'q3' AS metric, q3 AS value FROM sales UNION ALL SELECT product_id, 'q4' AS metric, q4 AS value FROM sales; Copy

When defining foreign key relationships (FOREIGN KEY), you can specify how the database handles deletion or updates of a parent record (in the table with the primary key).

ON DELETE CASCADE: If a record in the parent table is deleted, all related records in the child table are automatically deleted. Use with caution.

ON DELETE SET NULL: If a record in the parent table is deleted, the foreign key value in related child table records is set to NULL. Requires the foreign key column to allow NULL.

ON DELETE SET DEFAULT: (Rarely supported) If a record in the parent table is deleted, the foreign key value in related child table records is set to the default value.

ON DELETE NO ACTION / ON DELETE RESTRICT: Prevents deletion of a parent table record if related child table records exist. RESTRICT is typically the default behavior if unspecified.

Similar options (ON UPDATE CASCADE, ON UPDATE SET NULL, etc.) exist for update operations.

Checklists, Best Practices, and Pitfalls

Working with SQL requires not only syntax knowledge but also adherence to best practices for performance, security, and reliability.

Always include a WHERE clause in UPDATE/DELETE unless you intentionally want to affect all records. Omitting WHERE can result in deleting or modifying all table data!

Indexes: Create indexes on columns used for filtering, joining (JOIN), or sorting (ORDER BY). Avoid over-indexing, as indexes consume space and slow down INSERT/UPDATE/DELETE operations.

NULL vs Empty String: Decide in your project what "missing value" means and consistently use either NULL or empty strings, but don’t mix them for the same concept.

Use Transactions for larger operations (especially DML) to ensure atomicity and allow rollback if needed. Always end transactions with COMMIT or ROLLBACK. BEGIN TRANSACTION; -- or START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; -- If something went wrong: -- ROLLBACK; -- Otherwise: COMMIT;

Comment complex queries and store them in a version control system (e.g., Git), not just in SQL client history.

Test Performance: Regularly analyze query execution plans to identify bottlenecks. EXPLAIN (MySQL, PostgreSQL) or EXPLAIN ANALYZE (PostgreSQL)

SET STATISTICS IO ON (SQL Server)

Security: Avoid SQL Injection! Always use parameterized queries or prepared statements (PreparedStatement in Java, SqlCommand in .NET, etc.) instead of concatenating strings with user input.

! Warning: Never run UPDATE or DELETE without a WHERE clause in production without a backup and full awareness of the consequences!

Cheat Sheet

A quick reference for key commands and concepts to help you find what you need fast.

Summary

Congratulations! You've gone through a comprehensive SQL guide, from foundational principles and command types to advanced techniques like window functions and data transformations. You've learned how to create, modify, and manage database structures, manipulate data, join information across tables, and optimize queries.

This guide provides nearly a complete list of the commands and functions covered, along with context, examples, and best practices. Now, open your favorite SQL client and start crafting queries.

If you're interested, future posts could cover:

Query optimization (composite indexes, execution plans),

Practical PIVOT/UNPIVOT,

Schema migrations (Liquibase/Flyway),

Automating backups and disaster recovery for MySQL/PostgreSQL/SQL Server.

Partnerzy strategiczni

Polecane artykuły

wpisz help

● ● ● Terminal $