🟢 Introduction to SQL

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows you to create, read, update, and delete data.

1

What is SQL?

SQL lets you communicate with databases. It's used by nearly every application that stores data — from websites to mobile apps to enterprise systems.

2

How this editor works

This editor runs PostgreSQL directly in your browser using PGlite — no server needed! Write SQL in the left panel and click Execute or press Ctrl+Enter.

3

Pre-loaded data

An employees table is pre-loaded with 8 rows. Try: Hello SQL →

📋 SELECT — Reading Data

1

SELECT all columns

SELECT * FROM employees;

The * means "all columns". This returns every row and column from the table.

2

SELECT specific columns

SELECT name, salary FROM employees;

List only the columns you need — better for performance and readability.

3

Column aliases

SELECT name AS employee_name, salary AS annual_pay FROM employees;

Use AS to rename columns in the output.

💡 Try it: SELECT Example →

🔍 WHERE — Filtering Rows

1

Basic comparison

SELECT * FROM employees WHERE salary > 80000;

Operators: =, !=, <, >, <=, >=

2

AND / OR / NOT

SELECT * FROM employees WHERE department = 'Engineering' AND salary > 100000;

Combine conditions with logical operators.

3

IN, BETWEEN, LIKE

SELECT * FROM employees WHERE department IN ('Engineering', 'Sales');

IN matches a list. BETWEEN checks ranges. LIKE does pattern matching with % wildcard.

💡 Try it: WHERE Example → · LIKE & Pattern →

↕️ ORDER BY & DISTINCT

1

Sorting results

SELECT * FROM employees ORDER BY salary DESC;

ASC = ascending (default), DESC = descending.

2

LIMIT & OFFSET

SELECT * FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 2;

Get a "page" of results — great for pagination.

3

DISTINCT

SELECT DISTINCT department FROM employees;

Remove duplicate values from results.

💡 Try it: ORDER BY → · DISTINCT →

📊 Aggregates & GROUP BY

1

Aggregate functions

SELECT COUNT(*), AVG(salary), MIN(salary), MAX(salary), SUM(salary) FROM employees;

Compute summary statistics across rows.

2

GROUP BY

SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department;

Group rows by a column and aggregate within each group.

3

HAVING — Filter groups

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 80000;

HAVING filters groups (like WHERE for aggregates).

💡 Try it: Aggregates → · GROUP BY →

🔗 JOINs — Combining Tables

1

INNER JOIN

SELECT e.name, d.name AS dept FROM employees e INNER JOIN departments d ON e.department = d.name;

Returns only matching rows from both tables.

2

LEFT JOIN

SELECT e.name, d.budget FROM employees e LEFT JOIN departments d ON e.department = d.name;

Returns all rows from left table + matching rows from right (NULL if no match).

3

Other JOINs

RIGHT JOIN — all from right table. FULL JOIN — all from both. CROSS JOIN — cartesian product.

💡 Try it: JOINs Example →

✏️ INSERT, UPDATE, DELETE

1

INSERT — Add rows

INSERT INTO employees (name, department, salary) VALUES ('New Person', 'HR', 75000);

2

UPDATE — Modify rows

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';

Always use WHERE — without it, ALL rows get updated!

3

DELETE — Remove rows

DELETE FROM employees WHERE name = 'New Person';

Use WHERE carefully — DELETE FROM table removes ALL rows!

💡 Try it: INSERT → · UPDATE → · DELETE →

🏗️ CREATE, ALTER, DROP

1

CREATE TABLE

CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(100), price NUMERIC(10,2));

Define columns with data types and constraints.

2

ALTER TABLE

ALTER TABLE products ADD COLUMN category VARCHAR(50);

Add/remove/modify columns on existing tables.

3

DROP TABLE

DROP TABLE IF EXISTS products;

Permanently removes a table and all its data. Use IF EXISTS to avoid errors.

💡 Try it: CREATE TABLE → · ALTER TABLE →

🧩 Subqueries & CTEs

1

Subquery in WHERE

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

A query inside another query — computes a value to filter by.

2

CTE (Common Table Expression)

WITH high_earners AS (SELECT * FROM employees WHERE salary > 90000) SELECT * FROM high_earners;

Named temporary result set — more readable than nested subqueries.

3

Recursive CTE

Build hierarchical data (org charts, trees). The CTE references itself to traverse levels.

💡 Try it: Subqueries → · CTEs → · Recursive CTE →

⚡ Advanced SQL

1

Window Functions

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

Compute across rows without collapsing them — ROW_NUMBER(), RANK(), LAG(), LEAD()

2

CASE expressions

SELECT name, CASE WHEN salary > 100000 THEN 'High' WHEN salary > 70000 THEN 'Mid' ELSE 'Low' END AS tier FROM employees;

SQL's if-then-else — add conditional logic to queries.

3

UNION, INTERSECT, EXCEPT

Combine result sets from multiple queries. UNION merges, INTERSECT finds common, EXCEPT finds differences.

4

JSON support (PostgreSQL)

SELECT data->>'price' FROM products_json;

PostgreSQL can store and query JSON natively with JSONB.

💡 Try it: Window Funcs → · CASE → · UNION → · JSON →

📎 Quick Reference

StatementPurpose

SELECTRead data

INSERT INTOAdd rows

UPDATEModify rows

DELETE FROMRemove rows

CREATE TABLENew table

ALTER TABLEModify table

DROP TABLEDelete table

CREATE INDEXSpeed up queries

CREATE VIEWSaved query

BEGIN / COMMITTransactions

Data TypeDescription

INTEGER / INTWhole numbers

SERIALAuto-increment

NUMERIC(p,s)Exact decimals

VARCHAR(n)Variable text

TEXTUnlimited text

BOOLEANtrue / false

DATEDate only

TIMESTAMPDate + time

JSONBJSON data

📘 SQL Tutorial