SQL Basics

Essential SQL commands: SELECT, INSERT, UPDATE, DELETE, and JOINs

SELECT Statements

Basic SELECT

Select all columns from a table

SELECT * FROM users;

Specific Columns

Select specific columns

SELECT name, email FROM users;

DISTINCT

Select unique values

SELECT DISTINCT category FROM products;

WHERE Clause

Filter results with conditions

SELECT * FROM users WHERE age > 18;

ORDER BY

Sort results

SELECT * FROM users ORDER BY name ASC;

LIMIT

Limit number of results

SELECT * FROM users LIMIT 10;

INSERT, UPDATE, DELETE

INSERT

Add new records

INSERT INTO users (name, email) VALUES ("John", "[email protected]");

UPDATE

Modify existing records

UPDATE users SET email = "[email protected]" WHERE id = 1;

DELETE

Remove records

DELETE FROM users WHERE id = 1;

JOINs

INNER JOIN

Join tables with matching records

SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id;

LEFT JOIN

Include all records from left table

SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id;

RIGHT JOIN

Include all records from right table

SELECT u.name, p.title FROM users u RIGHT JOIN posts p ON u.id = p.user_id;

FULL JOIN

Include all records from both tables

SELECT u.name, p.title FROM users u FULL JOIN posts p ON u.id = p.user_id;

Aggregate Functions

COUNT

Count number of records

SELECT COUNT(*) FROM users;

SUM

Sum of numeric values

SELECT SUM(price) FROM products;

AVG

Average of numeric values

SELECT AVG(price) FROM products;

MAX

Maximum value

SELECT MAX(price) FROM products;

MIN

Minimum value

SELECT MIN(price) FROM products;

GROUP BY & HAVING

GROUP BY

Group results by column

SELECT category, COUNT(*) FROM products GROUP BY category;

HAVING

Filter grouped results

SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5;

Common Patterns

Basic CRUD Operations

Create, Read, Update, Delete operations

-- Create (INSERT)
INSERT INTO users (name, email, created_at) 
VALUES ('John Doe', '[email protected]', NOW());

-- Read (SELECT)
SELECT * FROM users WHERE email = '[email protected]';

-- Update (UPDATE)
UPDATE users 
SET name = 'Jane Doe', updated_at = NOW() 
WHERE id = 1;

-- Delete (DELETE)
DELETE FROM users WHERE id = 1;

Complex JOIN Example

Joining multiple tables with conditions

SELECT 
  u.name,
  p.title,
  c.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.status = 'published'
ORDER BY p.created_at DESC;

Subqueries

Using subqueries for complex queries

-- Subquery in WHERE clause
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery in FROM clause
SELECT category, avg_price 
FROM (
  SELECT category, AVG(price) as avg_price 
  FROM products 
  GROUP BY category
) subquery;