Mastering SQL: Comprehensive Guide To Structured Query Language

Introduction

In the previous blog article we've explored together a comprehensive guide on:

Mastering Relational Database Design: A Comprehensive Guide

Louai Boumediene ・ May 6

#webdev #learning #database #backend

an in this article, we will discover in a high level the syntax and core features of the language that is actually used to bring the database schema design to existence... SQL.

So... Let's dive right into it!

What is SQL

SQL, or Structured Query Language, is the facade language used to talk to all RDMSs, however for the most time as backend engineers (we're not database engineers) we don't use SQL directly, and we rather use Object Relational Mappers ORMs which provides a better more reliable interface on top of raw SQL to communicate with database in our preferred programming language, however we still need a basic knowledge of SQL for quick and simple manipulation and interactions with the database.

Basic SQL Queries

Basic SQL queries involve selecting specific data from a database table using commands like SELECT, DISTINCT, WHERE, LIMIT, and OFFSET.

-- Selecting all columns from a table
SELECT * FROM employees;

-- Selecting specific columns
SELECT first_name, last_name FROM employees;

-- Using DISTINCT to get unique values
SELECT DISTINCT department_id FROM employees;

-- Using WHERE to filter results
SELECT * FROM employees WHERE department_id = 10;

-- Using LIMIT to limit the number of results
SELECT * FROM employees LIMIT 5;

-- Using OFFSET to skip certain rows
SELECT * FROM employees OFFSET 5;

Filtering Data

Filtering data in SQL allows you to retrieve specific records from a table based on certain conditions, such as using comparison operators like >, <, =, and logical operators like AND, OR, and NOT.

-- Using comparison operators
SELECT * FROM employees WHERE salary > 50000;

-- Using logical operators
SELECT * FROM employees WHERE department_id = 10 AND salary > 50000;

-- Using IN and NOT IN
SELECT * FROM employees WHERE department_id IN (10, 20);

-- Using BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

-- Using LIKE for pattern matching
SELECT * FROM employees WHERE last_name LIKE 'S%';

Sorting Data

Sorting data in SQL arranges the retrieved records in either ascending or descending order based on specified columns using the ORDER BY clause.

-- Sorting data in ascending order
SELECT * FROM employees ORDER BY salary;

-- Sorting data in descending order
SELECT * FROM employees ORDER BY salary DESC;

-- Sorting by multiple columns
SELECT * FROM employees ORDER BY department_id, salary DESC;

Aggregate Functions

Aggregate functions in SQL perform calculations on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

-- Counting the number of rows
SELECT COUNT(*) FROM employees;

-- Calculating total salary
SELECT SUM(salary) FROM employees;

-- Finding average salary
SELECT AVG(salary) FROM employees;

-- Finding minimum salary
SELECT MIN(salary) FROM employees;

-- Finding maximum salary
SELECT MAX(salary) FROM employees;

Grouping Data

Grouping data in SQL allows you to group rows that have the same values in specified columns using the GROUP BY clause, often used in conjunction with aggregate functions.

-- Grouping data by department
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

-- Using HAVING to filter grouped data
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

Joins

Joins in SQL combine data from multiple tables based on related columns to retrieve data that spans across those tables.

-- Inner Join
SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;

-- Left Join
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;

-- Right Join
SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;

-- Full Outer Join
SELECT * FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

Subqueries

Subqueries in SQL are nested queries within another query, used to retrieve data that depends on the result of another query.

-- Subquery example
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- Correlated subquery example
SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Views

Views in SQL are virtual tables generated from the result of a query, providing a way to simplify complex queries and restrict access to certain data.

-- Creating a view
CREATE VIEW high_paid_employees AS SELECT * FROM employees WHERE salary > 80000;

-- Updating a view
CREATE OR REPLACE VIEW high_paid_employees AS SELECT * FROM employees WHERE salary > 90000;

-- Dropping a view
DROP VIEW IF EXISTS high_paid_employees;

Indexing

Indexing in SQL improves the performance of queries by creating indexes on columns, allowing for faster data retrieval.

-- Creating an index
CREATE INDEX idx_lastname ON employees(last_name);

-- Dropping an index
DROP INDEX idx_lastname;

Transactions

Transactions in SQL ensure data integrity by grouping SQL statements into atomic units, ensuring that either all statements are successfully executed or none of them are.

-- Beginning a transaction
BEGIN TRANSACTION;

-- Committing a transaction
COMMIT;

-- Rolling back a transaction
ROLLBACK;

Stored Procedures

Stored procedures in SQL are precompiled SQL code stored in the database that can be executed with a single command, often used to encapsulate frequently executed tasks

-- Creating a stored procedure
CREATE PROCEDURE get_employee (IN employee_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = employee_id;
END;

-- Executing a stored procedure
CALL get_employee(100);

-- Modifying a stored procedure
ALTER PROCEDURE get_employee (IN employee_id INT)
BEGIN
    SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = employee_id;
END;

-- Dropping a stored procedure
DROP PROCEDURE IF EXISTS get_employee;

Backup and Recovery

Backup and recovery in SQL involves creating backups of databases to protect against data loss and restoring them in case of database failure or corruption.

-- Creating a full backup
BACKUP DATABASE dbname TO disk = 'path_to_backup';

-- Creating a differential backup
BACKUP DATABASE dbname TO disk = 'path_to_backup' WITH DIFFERENTIAL;

-- Creating a transaction log backup
BACKUP LOG dbname TO disk = 'path_to_backup';

-- Restoring from a backup
RESTORE DATABASE dbname FROM disk = 'path_to_backup';

SQL Dialects and Vendor-Specific Extensions

While SQL is a standardized language, different database vendors have implemented their own extensions and dialects, leading to slight variations in how SQL is written and executed across different RDBMSs. These variations can impact the syntax, functions, and features available to developers working with specific database systems.

For example, here are a few examples of vendor-specific SQL dialects and extensions:

Oracle SQL:

• Oracle SQL includes proprietary extensions like hierarchical queries, analytic functions, and the MODEL clause for data modeling.
• Example: SELECT CUBE(product, region) FROM sales; (CUBE is an Oracle-specific extension for generating subtotals)

SQL Server (Microsoft):

• SQL Server includes T-SQL (Transact-SQL), which extends standard SQL with features like control-of-flow language constructs, error handling, and procedural programming constructs.
• Example: SELECT ISNULL(column_name, 'default_value') FROM table_name; (ISNULL is a T-SQL function)

MySQL:

• MySQL includes extensions like spatial data types and functions, window functions, and the REGEX operator for regular expression matching.
• Example: SELECT column_name REGEXP '^pattern' FROM table_name; (REGEXP is a MySQL-specific operator)

PostgreSQL:

• PostgreSQL includes extensions like arrays, JSON support, full-text search, and range data types.
• Example: SELECT column_name || 'suffix' FROM table_name; (|| is a PostgreSQL-specific string concatenation operator)

So the most logical way to go about is:

1. The Basics: Know the basics of Relational Dabase Design: Entitnes, keys, Relationships, …. .
2. SQL: Learn the fundamentals SQL as a standalone lanugage.
3. Pick RDBMs: Pick one RDBMS that sweets you the most, and simply use it adhearing to all its special syntax and conspets.

Conclusion

SQL is a vast field in itself, encompassing a multitude of concepts beyond what we've touched upon. Transactions, backups, and file management are just a few examples of the broader scope. However, for backend engineers like us, the fundamentals we've covered suffice as a foundational overview of SQL.