SQL Tutorial

Introduction to SQL

1. What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational database management systems (RDBMS). It lets you:

Define database schemas
Insert, query, update, and delete data
Control access and transactions


Most major databases—Oracle, MySQL, PostgreSQL, SQL Server—use SQL (with slight dialect differences).

2. A Brief History

1970: Edgar F. Codd publishes his paper on the relational model.
1974–78: IBM develops SEQUEL (later SQL) at San Jose Research Lab.
1986: ANSI adopts SQL as the first industry standard.
1987: ISO adopts the ANSI SQL standard.
Since then: Multiple revisions (SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008, SQL:2011, SQL:2016, SQL:2023).

3. The Relational Model

At its core, SQL implements Codd’s relational model:

Table (Relation): 2D grid of rows and columns.
Row (Tuple): Single record in a table.
Column (Attribute): Field that holds data of a specific type.
Key: Column (or set) that uniquely identifies rows (e.g., primary key).
Foreign Key: Column referencing a key in another table, enforcing referential integrity.

4. SQL Standard Categories

Category Purpose Examples
DDL (Data Definition) Define or alter schema CREATE, ALTER, DROP
DML (Data Manipulation) Query or modify data SELECT, INSERT, UPDATE, DELETE
DCL (Data Control) Control access permissions GRANT, REVOKE
TCL (Transaction Control) Manage transactions BEGIN, COMMIT, ROLLBACK

5. Basic DDL Examples

-- Create a new table
CREATE TABLE employees (
  employee_id   INT         PRIMARY KEY,
  first_name    VARCHAR(50),
  last_name     VARCHAR(50),
  hire_date     DATE        NOT NULL,
  salary        DECIMAL(10,2)
);

-- Alter table to add a column
ALTER TABLE employees
  ADD department VARCHAR(30);

-- Drop a table
DROP TABLE employees;

6. Basic DML Examples

6.1 INSERT

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary, department)
VALUES (1001, 'Alice', 'Wong', '2023-02-15', 75000.00, 'Engineering');

6.2 SELECT

-- Retrieve all columns from all rows
SELECT * FROM employees;

-- Retrieve specific columns with a condition
SELECT first_name, last_name, salary
  FROM employees
 WHERE department = 'Engineering'
   AND salary > 70000
 ORDER BY salary DESC;

6.3 UPDATE

-- Give everyone in Marketing a 10% raise
UPDATE employees
   SET salary = salary * 1.10
 WHERE department = 'Marketing';

6.4 DELETE

-- Remove employees hired before 2010
DELETE FROM employees
 WHERE hire_date < '2010-01-01';

7. Transactions (TCL)

BEGIN;         -- start a transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;        -- save changes

-- If something goes wrong, revert:
ROLLBACK;

8. Further Topics to Explore

Joins (INNER, LEFT, RIGHT, FULL)
Subqueries and CTEs (WITH clauses)
Indexes for performance
Views and Stored Procedures
Window Functions (e.g., ROW_NUMBER(), RANK())
Security: roles, permissions, encryption
Advanced SQL: pivoting, advanced analytic

Practice by creating your own tables and querying sample data—hands-on experience is key!

Next: SQL Tables

>