Example for the SQL Basics lab: docker exec -it lab-sqlbasics-db psql -U lab -d kirana. You land at a prompt like kirana=>. Every SQL command ends with a semicolon. The commands that start with a backslash are psql's own, and need no semicolon.
psql command
What it does
\dt
List tables.
\d tablename
Show the columns, keys and indexes of a table.
\l
List databases.
\c dbname
Connect to another database.
\x
Toggle expanded output (one column per line, easier to read for wide rows).
\timing
Show how long each query takes.
\q
Quit.
01 /Reading data
SELECT * FROM products LIMIT 3; -- first three rows, all columns
SELECT name, price FROM products
WHERE price > 200 AND stock > 0 -- filter
ORDER BY price DESC -- sort, highest first
LIMIT 5; -- at most five rows
SELECT DISTINCT city FROM customers; -- each city once
Word
Meaning
WHERE
Keep only rows that match.
ORDER BY col DESC
Sort. ASC is the default.
LIMIT n
Stop after n rows.
IS NULL, IS NOT NULL
The only correct way to test for "unknown". = NULL never matches.
LIKE 'A%'
Text starting with A.
IN (1, 2, 3)
Value is one of these.
02 /Counting and grouping
SELECT COUNT(*) FROM customers;
SELECT city, COUNT(*) FROM customers GROUP BY city ORDER BY COUNT(*) DESC;
SELECT SUM(qty * price) FROM order_items JOIN products ON ...;
COUNT, SUM, AVG, MIN, MAX collapse many rows into one number. GROUP BY does it once for each group.
03 /Joining tables
SELECT c.name, o.ordered_on
FROM customers c
JOIN orders o ON o.customer_id = c.id; -- only customers who have orders
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id -- every customer, even with no orders
GROUP BY c.name;
Join
Returns
JOIN (inner)
Only rows that have a match on both sides.
LEFT JOIN
Every row from the left table, with NULLs where there is no match.
04 /Changing data
INSERT INTO customers (name, city, phone) VALUES ('Neha Rao', 'Pune', '9810000007');
UPDATE products SET stock = stock - 1 WHERE id = 4; -- ALWAYS with a WHERE
DELETE FROM orders WHERE id = 99; -- ALWAYS with a WHERE
The missing WHERE
UPDATE or DELETE without WHERE changes every row. Habit that saves careers: type the WHERE first, run a SELECT with the same WHERE to see what will change, then change it.
05 /Transactions
BEGIN; -- start a group of changes
UPDATE wallets SET balance = balance - 800 WHERE wallet_id = 101 AND balance >= 800;
COMMIT; -- keep them all
-- or: ROLLBACK; -- throw them all away
Level
Set it with
READ COMMITTED (default)
BEGIN;
REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SERIALIZABLE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Lock rows you read
SELECT ... FROM t WHERE ... FOR UPDATE;
06 /Rules the table enforces
Rule
Written as
Stops
Primary key
id SERIAL PRIMARY KEY
Two rows with the same id
Not null
name TEXT NOT NULL
A missing value
Unique
phone TEXT UNIQUE
Two rows with the same phone
Check
price NUMERIC CHECK (price > 0)
A value that breaks the rule
Foreign key
customer_id INT REFERENCES customers (id)
An order for a customer who does not exist
07 /Speed: indexes and EXPLAIN
EXPLAIN (ANALYZE) SELECT * FROM scan_demo WHERE phone = '9877648064'; -- how will it run, and how long did it take?
CREATE INDEX idx_phone ON scan_demo (phone); -- a sorted shortcut
You see in the plan
It means
Seq Scan
Reads every row of the table.
Index Scan
Jumps straight to the matching rows using an index.
Rows Removed by Filter: 199999
The database read that many rows and threw them away.