SQL for Beginners: Complete Tutorial

SQL for Beginners: Complete Tutorial

SQL (Structured Query Language) is the language of data. Whether you're a data analyst, web developer, data scientist, or business professional—SQL is an essential skill.

Every major application uses databases, and SQL is how you communicate with them. This comprehensive tutorial takes you from zero to writing complex queries that extract meaningful insights from data.

---

What is SQL?

Definition

SQL is a standard programming language designed for managing and manipulating relational databases. It allows you to:

• Create and modify database structures
• Insert, update, and delete data
• Query data to find specific information
• Control access to data

Why Learn SQL?

Where SQL is Used

SQL vs NoSQL

---

Setting Up Your Environment

Options for Practice

Recommended Online Practice

• SQLBolt (sqlbolt.com) - Interactive lessons
• W3Schools SQL - Try-it editor
• LeetCode - SQL problems
• Mode Analytics - In-browser SQL
• Kaggle - SQL courses with datasets

---

Database Basics

Database Structure

Database
├── Table 1 (e.g., users)
│   ├── Columns (id, name, email, age)
│   └── Rows (each user's data)
├── Table 2 (e.g., orders)
│   └── ...
└── Table 3 (e.g., products)
    └── ...

Key Concepts

Sample Database: E-commerce

users table: | id | name | email | age | city | |----|------|-------|-----|------| | 1 | John | [email protected] | 28 | Delhi | | 2 | Jane | [email protected] | 24 | Mumbai | | 3 | Bob | [email protected] | 35 | Bangalore |

products table: | id | name | price | category | |----|------|-------|----------| | 1 | Laptop | 50000 | Electronics | | 2 | Phone | 20000 | Electronics | | 3 | Book | 500 | Books |

orders table: | id | user_id | product_id | quantity | order_date | |----|---------|------------|----------|------------| | 1 | 1 | 1 | 1 | 2024-01-15 | | 2 | 1 | 3 | 2 | 2024-01-20 | | 3 | 2 | 2 | 1 | 2024-02-01 |

---

Basic SQL Queries

SELECT Statement

Retrieve data from a table:

Select all columns:

SELECT * FROM users;

Select specific columns:

SELECT name, email FROM users;

Select with alias:

SELECT name AS customer_name, email AS customer_email
FROM users;

WHERE Clause

Filter results based on conditions:

SELECT * FROM users
WHERE age > 25;

Comparison Operators: | Operator | Meaning | |----------|---------| | = | Equal | | != or <> | Not equal | | > | Greater than | | < | Less than | | >= | Greater than or equal | | <= | Less than or equal |

AND, OR, NOT

Combine multiple conditions:

-- AND: Both conditions must be true
SELECT * FROM users
WHERE age > 25 AND city = 'Delhi';

-- OR: Either condition can be true
SELECT * FROM users
WHERE city = 'Delhi' OR city = 'Mumbai';

-- NOT: Negate condition
SELECT * FROM users
WHERE NOT city = 'Bangalore';

BETWEEN

Range of values:

SELECT * FROM products
WHERE price BETWEEN 1000 AND 30000;

IN

Match any value in a list:

SELECT * FROM users
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore');

LIKE

Pattern matching:

-- Starts with 'J'
SELECT * FROM users WHERE name LIKE 'J%';

-- Ends with 'n'
SELECT * FROM users WHERE name LIKE '%n';

-- Contains 'oh'
SELECT * FROM users WHERE name LIKE '%oh%';

-- Second character is 'o'
SELECT * FROM users WHERE name LIKE '_o%';

IS NULL / IS NOT NULL

Check for null values:

SELECT * FROM users
WHERE phone IS NULL;

SELECT * FROM users
WHERE phone IS NOT NULL;

---

Sorting and Limiting Results

ORDER BY

Sort results:

-- Ascending (default)
SELECT * FROM users ORDER BY age;

-- Descending
SELECT * FROM users ORDER BY age DESC;

-- Multiple columns
SELECT * FROM users ORDER BY city, age DESC;

LIMIT

Restrict number of results:

-- Get first 5 rows
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

OFFSET (skip rows):

-- Skip first 5, get next 5 (pagination)
SELECT * FROM products
LIMIT 5 OFFSET 5;

DISTINCT

Get unique values:

SELECT DISTINCT city FROM users;

SELECT DISTINCT category FROM products;

---

Aggregate Functions

Functions that calculate values across rows:

Examples

-- Count all users
SELECT COUNT(*) FROM users;

-- Count users in Delhi
SELECT COUNT(*) FROM users WHERE city = 'Delhi';

-- Total product value
SELECT SUM(price) FROM products;

-- Average product price
SELECT AVG(price) FROM products;

-- Most expensive product
SELECT MAX(price) FROM products;

-- Cheapest product
SELECT MIN(price) FROM products;

GROUP BY

Group rows and apply aggregates:

-- Count users per city
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city;

-- Average price per category
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category;

-- Total sales per product
SELECT product_id, SUM(quantity) as total_sold
FROM orders
GROUP BY product_id;

HAVING

Filter grouped results (WHERE is for rows, HAVING is for groups):

-- Cities with more than 2 users
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 2;

-- Categories with average price > 10000
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 10000;

---

JOIN Operations

JOINs combine data from multiple tables.

Types of JOINs

INNER JOIN

-- Get orders with user names
SELECT orders.id, users.name, orders.order_date
FROM orders
INNER JOIN users ON orders.user_id = users.id;

LEFT JOIN

-- All users and their orders (even users with no orders)
SELECT users.name, orders.id as order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Multiple JOINs

-- Orders with user and product details
SELECT
    users.name as customer,
    products.name as product,
    orders.quantity,
    orders.order_date
FROM orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN products ON orders.product_id = products.id;

Table Aliases

For cleaner queries:

SELECT u.name, p.name as product, o.quantity
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id;

---

Modifying Data

INSERT

Add new data:

-- Insert single row
INSERT INTO users (name, email, age, city)
VALUES ('Alice', '[email protected]', 26, 'Chennai');

-- Insert multiple rows
INSERT INTO users (name, email, age, city)
VALUES
    ('Mike', '[email protected]', 30, 'Delhi'),
    ('Sara', '[email protected]', 22, 'Pune');

UPDATE

Modify existing data:

-- Update single field
UPDATE users
SET age = 29
WHERE id = 1;

-- Update multiple fields
UPDATE users
SET age = 30, city = 'Hyderabad'
WHERE name = 'John';

-- Update with calculation
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

⚠️ Always use WHERE with UPDATE! Without it, all rows are updated.

DELETE

Remove data:

-- Delete specific row
DELETE FROM users
WHERE id = 5;

-- Delete with condition
DELETE FROM orders
WHERE order_date < '2023-01-01';

⚠️ Always use WHERE with DELETE! Without it, all rows are deleted.

---

Creating and Managing Tables

CREATE TABLE

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

Common Data Types

Constraints

ALTER TABLE

Modify table structure:

-- Add column
ALTER TABLE users
ADD phone VARCHAR(15);

-- Remove column
ALTER TABLE users
DROP COLUMN phone;

-- Modify column
ALTER TABLE users
MODIFY COLUMN name VARCHAR(150);

-- Rename column
ALTER TABLE users
RENAME COLUMN name TO full_name;

DROP TABLE

Delete entire table:

DROP TABLE employees;

-- Only if exists
DROP TABLE IF EXISTS employees;

---

Subqueries

A query inside another query:

Subquery in WHERE

-- Products more expensive than average
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Users who have placed orders
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

Subquery in FROM

-- Average orders per user
SELECT AVG(order_count) as avg_orders_per_user
FROM (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
) as user_orders;

Subquery in SELECT

-- Products with order count
SELECT
    name,
    price,
    (SELECT COUNT(*) FROM orders WHERE product_id = products.id) as times_ordered
FROM products;

---

Advanced SQL Concepts

CASE Statement

Conditional logic in queries:

SELECT
    name,
    price,
    CASE
        WHEN price < 1000 THEN 'Budget'
        WHEN price < 10000 THEN 'Mid-range'
        ELSE 'Premium'
    END as price_category
FROM products;

UNION

Combine results from multiple queries:

-- All cities from both users and suppliers
SELECT city FROM users
UNION
SELECT city FROM suppliers;

-- UNION ALL keeps duplicates
SELECT city FROM users
UNION ALL
SELECT city FROM suppliers;

Views

Saved queries that act like tables:

-- Create view
CREATE VIEW customer_orders AS
SELECT u.name, p.name as product, o.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

-- Use view
SELECT * FROM customer_orders WHERE name = 'John';

Indexes

Speed up queries on large tables:

-- Create index
CREATE INDEX idx_users_email ON users(email);

-- Create unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

---

SQL Query Order of Execution

Understanding how SQL processes queries:

This is why you can't use column aliases in WHERE but can in ORDER BY.

---

Common SQL Patterns

Find Duplicates

SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Second Highest Value

SELECT MAX(price) as second_highest
FROM products
WHERE price < (SELECT MAX(price) FROM products);

Running Totals

SELECT
    order_date,
    quantity,
    SUM(quantity) OVER (ORDER BY order_date) as running_total
FROM orders;

Rank Items

SELECT
    name,
    price,
    RANK() OVER (ORDER BY price DESC) as price_rank
FROM products;

---

SQL Best Practices

Writing Clean Queries

Performance Tips

---

Practice Problems

Beginner

1. Select all products under ₹10,000
2. Count users per city
3. Find the most expensive product
4. List users ordered by age (descending)

Intermediate

1. Find users who have never placed an order
2. Calculate total revenue per product
3. Find products ordered more than 5 times
4. List the top 3 customers by total spending

Advanced

1. Find the second-highest salary in each department
2. Calculate month-over-month growth in orders
3. Find customers who ordered all products
4. Identify users who ordered on consecutive days

---

Key Takeaways

1. SQL is essential—for almost any data-related role
2. Start with SELECT—master querying before modifying
3. WHERE filters rows—before grouping
4. HAVING filters groups—after grouping
5. JOINs connect tables—understand INNER vs LEFT
6. Use aggregates with GROUP BY—COUNT, SUM, AVG
7. Always use WHERE with UPDATE/DELETE—or risk disaster
8. Practice on real data—use Kaggle datasets
9. Read query execution order—helps debugging
10. Performance matters at scale—optimize as you learn

---

Frequently Asked Questions

How long does it take to learn SQL?

Basics: 1-2 weeks. Comfortable for jobs: 1-2 months. Advanced: ongoing with practice.

Which SQL database should I learn?

Start with any—concepts transfer. MySQL and PostgreSQL are most popular. SQLite is easiest for beginners.

Is SQL enough to get a job?

SQL alone can get data analyst roles. Combined with Python/R, it opens data science roles. With programming, backend development roles.

What's the difference between SQL and MySQL?

SQL is the language. MySQL is a database system that uses SQL. Other systems (PostgreSQL, Oracle) also use SQL with slight variations.

How do I practice SQL?

Use online platforms (SQLBolt, LeetCode), practice with Kaggle datasets, or create a local database with sample data.

---

Learning database skills? Explore more resources on Sproutern for programming tutorials, career guidance, and skill development.