Introduction
In the previous blog article we've explored together a comprehensive guide on: 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.
```sql -- 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.
```sql -- 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.
```sql -- 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.
```sql -- 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.
```sql -- 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.
```sql -- 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.
```sql -- 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.
```sql -- 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.
```sql -- 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.
```sql -- 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
```sql -- 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.
```sql -- 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.