Introduction to SQL

Structured Query Language (SQL) is a standard programming language used to manage and manipulate relational databases. SQL is essential for tasks such as querying data, updating records, managing access control, and defining database structures.

SQL operates on structured data stored in tables, consisting of rows and columns. It provides various commands to interact with the database, ensuring efficient data retrieval and management.

SQL was first developed in the 1970s at IBM for their System R project, based on Edgar F. Codd's relational model. Over time, SQL has evolved into an ANSI and ISO standard, with popular implementations like MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.

Key milestones in SQL evolution:

SQL-86: The first standardized version.

SQL-92: Added better support for constraints and joins.

SQL:1999: Introduced features like triggers, recursive queries, and procedural elements.

SQL:2003: Included XML-related features.

SQL:2011 and later: Added temporal data support and performance improvements.

SQL commands are categorized into five main types:

DDL (Data Definition Language) – Defines database structures

CREATE TABLE – Creates a new table

ALTER TABLE – Modifies an existing table

DROP TABLE – Deletes a table

Example:

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

Example: Modifying a Table

ALTER TABLE employees ADD COLUMN hire_date DATE;

DML (Data Manipulation Language) – Manipulates data

INSERT – Adds records

UPDATE – Modifies records

DELETE – Removes records

Example:

INSERT INTO employees (id, name, department, salary) VALUES (1, 'John Doe', 'IT', 60000);

Example: Updating a Record

UPDATE employees SET salary = 70000 WHERE id = 1;

DCL (Data Control Language) – Manages access control

GRANT – Assigns privileges

REVOKE – Removes privileges

Example:

GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';

TCL (Transaction Control Language) – Manages transactions

COMMIT – Saves changes

ROLLBACK – Undoes changes

Example:

BEGIN TRANSACTION;
UPDATE employees SET salary = 70000 WHERE id = 1;
ROLLBACK;

DQL (Data Query Language) – Retrieves data

SELECT – Queries data

Example:

SELECT * FROM employees WHERE department = 'IT';