SQL OPERATIONS - COMPLETE MASTERY GUIDE

🎯 Basic SQL Operations

CREATE TABLE - Define Database Structure
What it does: Creates a new table with specified columns and constraints

sql
 -- Basic table creation
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Explanation of each part:

-- id INT = Integer column for user ID
-- PRIMARY KEY = Makes this column unique identifier
-- AUTO_INCREMENT = Automatically generates incrementing numbers
-- VARCHAR(255) = Variable character string, max 255 chars
-- NOT NULL = This field cannot be empty
-- UNIQUE = No two rows can have same email
-- TIMESTAMP = Date and time data type
-- DEFAULT CURRENT_TIMESTAMP = Automatically sets to current date/time

-- More complex table with relationships

CREATE TABLE posts (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
title VARCHAR(500) NOT NULL,
content TEXT,
status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Common data types:

-- INT, BIGINT = Numbers
-- VARCHAR(n), TEXT = Strings
-- TIMESTAMP, DATE = Dates
-- BOOLEAN, TINYINT(1) = True/false
-- DECIMAL(10,2) = Numbers with decimals
-- ENUM('val1','val2') = Predefined options

INSERT - Add New Data

What it does: Adds new rows of data to a table

sql
 -- Single row insert
INSERT INTO users (name, email)
VALUES ('John', 'john@[Link]');

-- Multiple rows insert (more efficient)

INSERT INTO users (name, email)
VALUES
('Alice', 'alice@[Link]'),
('Bob', 'bob@[Link]'),
('Charlie', 'charlie@[Link]');

-- Insert all columns (including optional ones)

INSERT INTO users (name, email, created_at)
VALUES ('David', 'david@[Link]', '2024-01-15 [Link]');

-- Insert with auto-generated ID (ID will be auto-assigned)

INSERT INTO posts (user_id, title, content)
VALUES (1, 'My First Post', 'This is the content of my first post.');

-- Real-world example - User registration

INSERT INTO users (name, email, password_hash, role)
VALUES ('newuser', 'newuser@[Link]', 'hashed_password', 'user');

-- Insert from another query

INSERT INTO backup_users (name, email)
SELECT name, email FROM users WHERE active = 1;

SELECT - Retrieve Data

What it does: Fetches data from one or more tables

sql
-- Select all columns and rows
SELECT * FROM users;

-- Select specific columns

SELECT name, email FROM users;

-- Select with WHERE condition

SELECT name, email FROM users WHERE id = 1;

-- Pattern matching with LIKE

SELECT * FROM users WHERE name LIKE '%john%'; -- Contains 'john'
SELECT * FROM users WHERE name LIKE 'john%'; -- Starts with 'john'
SELECT * FROM users WHERE name LIKE '%john'; -- Ends with 'john'
SELECT * FROM users WHERE email LIKE '%@gmail.%'; -- Gmail users

-- Multiple conditions
SELECT * FROM users
WHERE age >= 18 AND active = 1;

SELECT * FROM users

WHERE role = 'admin' OR role = 'moderator';

-- Ordering results
SELECT * FROM users ORDER BY created_at DESC; -- Newest first
SELECT * FROM users ORDER BY name ASC; -- Alphabetical
SELECT * FROM users ORDER BY age DESC, name ASC; -- Age desc, then name asc

-- Limiting results (pagination)

SELECT * FROM users ORDER BY created_at DESC LIMIT 10; -- First 10
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- Skip 20, get next 10

-- Aggregation functions
SELECT COUNT(*) FROM users; -- Total count
SELECT COUNT(*) FROM users WHERE active = 1; -- Active users count
SELECT AVG(age) FROM users; -- Average age
SELECT MAX(created_at) FROM users; -- Most recent signup
SELECT MIN(age) FROM users WHERE age > 0; -- Youngest user

-- Group by (count users by role)

SELECT role, COUNT(*)
FROM users
GROUP BY role;

-- Having clause (like WHERE but for grouped data)

SELECT role, COUNT(*)
FROM users
 GROUP BY role
HAVING COUNT(*) > 5; -- Only roles with more than 5 users

UPDATE - Modify Existing Data

What it does: Changes existing data in table rows

sql

-- Update single field

UPDATE users
SET email = 'newemail@[Link]'
WHERE id = 1;

-- Update multiple fields

UPDATE users
SET name = 'John Smith',
email = 'johnsmith@[Link]',
updated_at = NOW()
WHERE id = 1;

-- Update with conditions

UPDATE users
SET active = 0
WHERE last_login < '2023-01-01';

-- Update all rows (BE CAREFUL!)

UPDATE users SET role = 'user'; -- This updates ALL users!

-- Update with calculated values

UPDATE users
SET login_count = login_count + 1,
last_login = NOW()
WHERE id = 1;

-- Update from joined table

UPDATE users u
JOIN user_stats s ON [Link] = s.user_id
SET u.total_posts = s.post_count
WHERE s.post_count > 0;

-- Conditional update
UPDATE products
SET price = price * 0.9 -- 10% discount
WHERE category = 'electronics' AND stock > 50;
DELETE - Remove Data
What it does: Removes rows from a table

sql

-- Delete specific row

DELETE FROM users WHERE id = 1;

-- Delete with conditions

DELETE FROM users WHERE active = 0;

-- Delete with multiple conditions

DELETE FROM posts
WHERE user_id = 1 AND status = 'draft';

-- Delete all rows (DANGEROUS!)

DELETE FROM users; -- This deletes ALL users!

-- Better: Use TRUNCATE for all rows (faster and resets AUTO_INCREMENT)
TRUNCATE TABLE users;

-- Delete with JOIN (remove inactive users' posts)

DELETE p FROM posts p
JOIN users u ON p.user_id = [Link]
WHERE [Link] = 0;

-- Delete old records

DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY); -- Older than 30 days

🎯 JOIN Operations - Combine Data from Multiple Tables

INNER JOIN - Only Matching Records
What it does: Returns only rows where there's a match in both tables

sql
 -- Basic INNER JOIN
SELECT [Link], [Link]
FROM users u
INNER JOIN posts p ON [Link] = p.user_id;

-- This returns:
-- Only users who have posts
-- Only posts that belong to existing users

-- Example with sample data:

-- users: {id:1, name:'John'}, {id:2, name:'Jane'}, {id:3, name:'Bob'}
-- posts: {id:1, user_id:1, title:'Post1'}, {id:2, user_id:1, title:'Post2'}, {id:3, user_id:4, title:'Post3'}
-- Result: John|Post1, John|Post2 (Bob has no posts, Post3's user doesn't exist)

-- More specific conditions

SELECT [Link], [Link], p.created_at
FROM users u
INNER JOIN posts p ON [Link] = p.user_id
WHERE [Link] = 1 AND [Link] = 'published'
ORDER BY p.created_at DESC;

-- Count posts per user

SELECT [Link], COUNT([Link]) as post_count
FROM users u
INNER JOIN posts p ON [Link] = p.user_id
GROUP BY [Link], [Link]
ORDER BY post_count DESC;

LEFT JOIN - All Records from Left Table

What it does: Returns all rows from left table, with matching rows from right table (NULL if no match)

sql
 -- Basic LEFT JOIN
SELECT [Link], [Link]
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id;

-- This returns:
-- ALL users (even those without posts)
-- Their posts if they have any
-- NULL for title if user has no posts

-- Example with sample data:

-- users: {id:1, name:'John'}, {id:2, name:'Jane'}, {id:3, name:'Bob'}
-- posts: {id:1, user_id:1, title:'Post1'}, {id:2, user_id:1, title:'Post2'}
-- Result: John|Post1, John|Post2, Jane|NULL, Bob|NULL

-- Find users without posts

SELECT [Link]
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
WHERE [Link] IS NULL;

-- Count posts per user (including users with 0 posts)

SELECT [Link], COUNT([Link]) as post_count
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
GROUP BY [Link], [Link]
ORDER BY post_count DESC;

-- Get users with their latest post

SELECT [Link], [Link], p.created_at
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
AND [Link] = (
SELECT MAX([Link])
FROM posts p2
WHERE p2.user_id = [Link]
);

Multiple JOINs - Connecting 3+ Tables

What it does: Combines data from multiple tables in a single query

sql
-- Three-table JOIN: users -> posts -> comments
SELECT [Link], [Link], [Link]
FROM users u
JOIN posts p ON [Link] = p.user_id
JOIN comments c ON [Link] = c.post_id
WHERE [Link] = 1;

-- Explanation:
-- 1. Start with users table
-- 2. JOIN posts where [Link] = post.user_id
-- 3. JOIN comments where [Link] = comment.post_id
-- Result: Each row is a user-post-comment combination

-- Four-table JOIN with categories

SELECT
[Link] as author,
[Link] as category,
[Link],
[Link] as comment,
c.created_at
FROM users u
JOIN posts p ON [Link] = p.user_id
JOIN categories cat ON p.category_id = [Link]
JOIN comments c ON [Link] = c.post_id
WHERE [Link] = 'Technology'
ORDER BY c.created_at DESC;

-- Mixed JOIN types

SELECT
[Link],
[Link],
[Link] as comment
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id -- All users
LEFT JOIN comments c ON [Link] = c.post_id -- All posts (even without comments)
WHERE [Link] = 'author';

-- Complex example with aggregation

SELECT
[Link],
COUNT(DISTINCT [Link]) as total_posts,
COUNT(DISTINCT [Link]) as total_comments,
AVG(p.view_count) as avg_views
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
LEFT JOIN comments c ON [Link] = c.post_id
 WHERE u.created_at >= '2024-01-01'
GROUP BY [Link], [Link]
HAVING total_posts > 0
ORDER BY total_posts DESC;

RIGHT JOIN & FULL OUTER JOIN

sql

-- RIGHT JOIN (less common, opposite of LEFT JOIN)

SELECT [Link], [Link]
FROM users u
RIGHT JOIN posts p ON [Link] = p.user_id;
-- Returns all posts, even if user doesn't exist

-- FULL OUTER JOIN (MySQL doesn't support, use UNION)

SELECT [Link], [Link] FROM users u LEFT JOIN posts p ON [Link] = p.user_id
UNION
SELECT [Link], [Link] FROM users u RIGHT JOIN posts p ON [Link] = p.user_id;

🚀 INTERVIEW SUCCESS TIPS

Common Interview Questions:
Q: "What's the difference between INNER JOIN and LEFT JOIN?" A: "INNER JOIN returns only rows
where there's a match in both tables. LEFT JOIN returns all rows from the left table, with NULLs where
there's no match in the right table."

Q: "How would you find users who haven't made any posts?" A: "Using LEFT JOIN with WHERE
NULL:

sql
 SELECT [Link] FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
WHERE [Link] IS NULL;
```"

### **Critical SQL Interview Patterns:**

1. **Always use table aliases** (`users u`, `posts p`) for readability
2. **Primary/Foreign key relationships** - Understand how tables connect
3. **NULL handling** - Know when JOINs produce NULL values
4. **WHERE vs HAVING** - WHERE filters rows, HAVING filters grouped results
5. **Aggregate functions** - COUNT, SUM, AVG, MAX, MIN

### **Your Interview Context:**

They asked about the two-table scenario with foreign keys. Practice this exact pattern:
```sql
-- Insert into both tables
INSERT INTO table1 (name) VALUES ('satham');
INSERT INTO table2 (age, sl_no) VALUES (25, LAST_INSERT_ID());

-- Display combined data

SELECT [Link], [Link]
FROM table1 t1
JOIN table2 t2 ON t1.s_no = t2.sl_no;

Master these JOIN patterns - they're fundamental to database work! 🎯