SQL (Structured Query Language)

Description

SQL (Structured Query Language) is a domain-specific language used in programming and data management to communicate with relational databases. It is the standard language for retrieving, inserting, updating, and deleting data in relational database management systems (RDBMS) such as MySQL, PostgreSQL, SQL Server, and Oracle.

SQL allows developers, data analysts, and database administrators to define, manipulate, and control data structures through declarative commands. Unlike procedural languages that specify how to perform a task, SQL focuses on what data is required, leaving the how to the underlying database engine.

Key Features of SQL

Declarative syntax for data querying and manipulation
Support for data definition (DDL) and data manipulation (DML)
Set-based operations and filtering
Support for joins, subqueries, aggregations
Platform-independent syntax (with slight variations among engines)
Integration with programming languages and business intelligence tools

SQL Categories

1. Data Definition Language (DDL)

Used to define and modify database structures.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10, 2)
);

ALTER TABLE employees ADD COLUMN hire_date DATE;
DROP TABLE employees;

2. Data Manipulation Language (DML)

Used to interact with the data inside the structures.

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 60000);
UPDATE employees SET salary = 65000 WHERE id = 1;
DELETE FROM employees WHERE id = 1;

3. Data Query Language (DQL)

Used for retrieving data.

SELECT name, salary FROM employees WHERE salary > 50000;

4. Data Control Language (DCL)

Used to manage permissions.

GRANT SELECT ON employees TO analyst;
REVOKE SELECT ON employees FROM analyst;

5. Transaction Control Language (TCL)

Manages transactions in databases.

BEGIN;
UPDATE employees SET salary = 70000 WHERE id = 1;
COMMIT;
-- or ROLLBACK;

Core SQL Concepts

Tables and Rows

Data in relational databases is stored in tables composed of rows and columns. Each table has a schema defining data types and constraints.

Primary Keys and Foreign Keys

Primary Key: Uniquely identifies each record.
Foreign Key: Establishes a relationship between two tables.

Joins

Combines rows from multiple tables:

SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Types:

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN
FULL JOIN

Aggregation

Use functions like SUM, AVG, COUNT, MAX, MIN.

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Subqueries

Queries inside queries:

SELECT name FROM employees WHERE salary > (
  SELECT AVG(salary) FROM employees
);

Indexes

Speed up read operations by creating data pointers.

CREATE INDEX idx_salary ON employees(salary);

Normalization and Data Integrity

Normalization is the process of structuring a relational database to reduce redundancy and dependency. Normal forms include:

1NF (First Normal Form): Atomic column values
2NF: No partial dependency on composite key
3NF: No transitive dependency

Constraints ensure data integrity:

NOT NULL
UNIQUE
CHECK
DEFAULT
FOREIGN KEY

Stored Procedures and Triggers

Stored Procedure: Reusable blocks of SQL logic
Trigger: Executes automatically when a specific event occurs

CREATE PROCEDURE give_raise AS
BEGIN
  UPDATE employees SET salary = salary * 1.05;
END;

CREATE TRIGGER log_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (description) VALUES ('New hire added');
END;

SQL vs NoSQL

Feature	SQL (Relational DB)	NoSQL (e.g., MongoDB)
Schema	Fixed schema	Dynamic schema
Relations	Strong, foreign keys	Weak or none
Transactions	ACID-compliant	BASE, eventual consistency
Query Language	SQL	JSON-like or proprietary
Use Case	Structured, consistent	Unstructured, high velocity

SQL in Modern Context

Business Intelligence: Tools like Tableau, Power BI connect via SQL
Data Warehousing: Systems like Amazon Redshift, Google BigQuery support SQL
Data Science: Analysts use SQL to preprocess data
Web Development: SQL is the backbone of full-stack applications
Cloud: SQL databases offered as PaaS (e.g., Azure SQL, AWS RDS)

Common SQL Engines

Engine	Notes
MySQL	Open source, fast, widely used
PostgreSQL	Feature-rich, highly standards-compliant
SQLite	Embedded database, serverless
Microsoft SQL Server	Enterprise-focused, powerful analytics
Oracle Database	High performance, secure, complex systems

Each engine supports standard SQL with proprietary extensions (e.g., T-SQL for SQL Server, PL/pgSQL for PostgreSQL).

SQL Optimization Tips

Use EXPLAIN to view query plans
Avoid SELECT *
Create indexes on filtered/joined columns
Normalize, but denormalize selectively for performance
Use LIMIT to avoid large result sets
Avoid unnecessary subqueries
Consider using stored procedures for repeated logic

Example SQL Query (Full Use Case)

-- Show top 5 departments with highest average salary
SELECT d.name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
ORDER BY avg_salary DESC
LIMIT 5;