SQL Fundamentals
Quick reference guide for SQL (Structured Query Language) fundamentals.
What is SQL?
SQL is a standard language for managing and manipulating relational databases:
Basic SELECT Queries
Retrieve data from tables:
-- ========== Select All Columns ==========
SELECT * FROM users;
-- ========== Select Specific Columns ==========
SELECT id, name, email FROM users;
-- ========== Select with Alias ==========
SELECT
name AS full_name,
email AS email_address
FROM users;
-- ========== Select Distinct Values ==========
SELECT DISTINCT country FROM users;
-- ========== Select with Calculation ==========
SELECT
product_name,
price,
price * 1.1 AS price_with_tax
FROM products;
WHERE Clause (Filtering)
Filter records based on conditions:
-- ========== Basic Filtering ==========
SELECT * FROM users
WHERE country = 'Indonesia';
-- ========== Numeric Comparison ==========
SELECT * FROM products
WHERE price > 100;
-- ========== Multiple Conditions (AND) ==========
SELECT * FROM users
WHERE country = 'Indonesia'
AND age >= 18;
-- ========== Multiple Conditions (OR) ==========
SELECT * FROM users
WHERE country = 'Indonesia'
OR country = 'Singapore';
-- ========== IN Operator ==========
SELECT * FROM users
WHERE country IN ('Indonesia', 'Singapore', 'Malaysia');
-- ========== BETWEEN Operator ==========
SELECT * FROM products
WHERE price BETWEEN 50 AND 200;
-- ========== LIKE Operator (Pattern Matching) ==========
SELECT * FROM users
WHERE name LIKE 'John%'; -- Starts with 'John'
SELECT * FROM users
WHERE email LIKE '%@gmail.com'; -- Ends with '@gmail.com'
-- ========== IS NULL / IS NOT NULL ==========
SELECT * FROM users
WHERE phone IS NULL;
SELECT * FROM users
WHERE phone IS NOT NULL;
ORDER BY (Sorting)
Sort query results:
-- ========== Ascending Order (Default) ==========
SELECT * FROM products
ORDER BY price ASC;
-- ========== Descending Order ==========
SELECT * FROM products
ORDER BY price DESC;
-- ========== Multiple Columns ==========
SELECT * FROM users
ORDER BY country ASC, name ASC;
-- ========== Order by Calculated Column ==========
SELECT
product_name,
price,
stock,
price * stock AS total_value
FROM products
ORDER BY total_value DESC;
LIMIT / TOP
Limit the number of results:
-- ========== MySQL / PostgreSQL ==========
SELECT * FROM users
LIMIT 10;
-- ========== SQL Server ==========
SELECT TOP 10 * FROM users;
-- ========== With OFFSET (Pagination) ==========
SELECT * FROM users
LIMIT 10 OFFSET 20; -- Skip first 20, get next 10
Aggregate Functions
Perform calculations on data:
-- ========== COUNT ==========
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;
-- ========== SUM ==========
SELECT SUM(price) FROM products;
-- ========== AVG ==========
SELECT AVG(price) FROM products;
-- ========== MIN / MAX ==========
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;
-- ========== Multiple Aggregates ==========
SELECT
COUNT(*) AS total_products,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price,
SUM(stock) AS total_stock
FROM products;
GROUP BY
Group rows and apply aggregate functions:
-- ========== Basic Grouping ==========
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;
-- ========== Multiple Columns ==========
SELECT country, city, COUNT(*) AS user_count
FROM users
GROUP BY country, city;
-- ========== With Aggregate Functions ==========
SELECT
category,
COUNT(*) AS product_count,
AVG(price) AS avg_price,
SUM(stock) AS total_stock
FROM products
GROUP BY category;
-- ========== HAVING (Filter After Grouping) ==========
SELECT
category,
COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
JOIN Operations
Combine data from multiple tables:
-- ========== INNER JOIN ==========
-- Returns only matching rows from both tables
SELECT
orders.id,
users.name,
orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id;
-- ========== LEFT JOIN ==========
-- Returns all rows from left table, matching rows from right
SELECT
users.name,
orders.id AS order_id,
orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- ========== RIGHT JOIN ==========
-- Returns all rows from right table, matching rows from left
SELECT
users.name,
orders.id AS order_id,
orders.total
FROM orders
RIGHT JOIN users ON orders.user_id = users.id;
-- ========== Multiple Joins ==========
SELECT
orders.id,
users.name,
products.product_name,
order_items.quantity,
order_items.price
FROM orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;
INSERT Data
Add new records to tables:
-- ========== Insert Single Row ==========
INSERT INTO users (name, email, country)
VALUES ('Yudi Nugraha', 'yudi@example.com', 'Indonesia');
-- ========== Insert Multiple Rows ==========
INSERT INTO users (name, email, country)
VALUES
('Alice', 'alice@example.com', 'Singapore'),
('Bob', 'bob@example.com', 'Malaysia'),
('Charlie', 'charlie@example.com', 'Indonesia');
-- ========== Insert from SELECT ==========
INSERT INTO archived_users (name, email, country)
SELECT name, email, country
FROM users
WHERE created_at < '2020-01-01';
UPDATE Data
Modify existing records:
-- ========== Update Single Column ==========
UPDATE users
SET country = 'Indonesia'
WHERE id = 1;
-- ========== Update Multiple Columns ==========
UPDATE users
SET
email = 'newemail@example.com',
updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
-- ========== Update with Calculation ==========
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';
-- ========== Update Multiple Rows ==========
UPDATE users
SET status = 'inactive'
WHERE last_login < '2023-01-01';
DELETE Data
Remove records from tables:
-- ========== Delete Specific Rows ==========
DELETE FROM users
WHERE id = 1;
-- ========== Delete with Condition ==========
DELETE FROM orders
WHERE status = 'cancelled'
AND created_at < '2023-01-01';
-- ========== Delete All Rows (Use with Caution!) ==========
DELETE FROM temp_table;
-- ========== TRUNCATE (Faster, removes all rows) ==========
TRUNCATE TABLE temp_table;
CREATE TABLE
Define database structures:
-- ========== Basic Table ==========
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT,
country VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- ========== Table with Foreign Key ==========
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
total DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- ========== Table with Constraints ==========
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
stock INT DEFAULT 0 CHECK (stock >= 0),
category VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_category (category)
);
ALTER TABLE
Modify existing table structures:
-- ========== Add Column ==========
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
-- ========== Modify Column ==========
ALTER TABLE users
MODIFY COLUMN name VARCHAR(150) NOT NULL;
-- ========== Drop Column ==========
ALTER TABLE users
DROP COLUMN phone;
-- ========== Add Index ==========
ALTER TABLE users
ADD INDEX idx_email (email);
-- ========== Add Foreign Key ==========
ALTER TABLE orders
ADD FOREIGN KEY (user_id) REFERENCES users(id);
Subqueries
Use queries inside other queries:
-- ========== Subquery in WHERE ==========
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- ========== Subquery in SELECT ==========
SELECT
name,
(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;
-- ========== Subquery with IN ==========
SELECT * FROM products
WHERE category IN (
SELECT DISTINCT category
FROM products
WHERE price > 100
);
-- ========== Subquery in FROM ==========
SELECT category, avg_price
FROM (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS category_avg
WHERE avg_price > 50;
Common Functions
Useful SQL functions:
-- ========== String Functions ==========
SELECT UPPER(name) FROM users;
SELECT LOWER(email) FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT SUBSTRING(name, 1, 3) FROM users;
SELECT LENGTH(name) FROM users;
-- ========== Date Functions ==========
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;
SELECT YEAR(created_at), MONTH(created_at) FROM orders;
SELECT DATE_ADD(created_at, INTERVAL 7 DAY) FROM orders;
SELECT DATEDIFF(CURRENT_DATE, created_at) AS days_ago FROM orders;
-- ========== Numeric Functions ==========
SELECT ROUND(price, 2) FROM products;
SELECT CEILING(price) FROM products;
SELECT FLOOR(price) FROM products;
SELECT ABS(balance) FROM accounts;
-- ========== Conditional Functions ==========
SELECT
name,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM users;
SELECT
product_name,
COALESCE(discount_price, price) AS final_price
FROM products;
Transactions
Ensure data consistency:
-- ========== Basic Transaction ==========
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Save changes
-- ========== Transaction with Rollback ==========
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- If error occurs
ROLLBACK; -- Undo all changes
Indexes
Improve query performance:
-- ========== Create Index ==========
CREATE INDEX idx_email ON users(email);
-- ========== Composite Index ==========
CREATE INDEX idx_country_city ON users(country, city);
-- ========== Unique Index ==========
CREATE UNIQUE INDEX idx_unique_email ON users(email);
-- ========== Drop Index ==========
DROP INDEX idx_email ON users;