SQL Tutorial: Beginner to Advanced

This tutorial covers key SQL concepts explained in the 2-hour YouTube walkthrough, tailored for aspiring data engineers and analysts.

🎯 Introduction to SQL

SQL (Structured Query Language) is used to interact with relational databases — retrieving, modifying, and managing data using a standardized syntax.

1. SELECT and Filtering Basics

The SELECT statement is the foundation of SQL used to retrieve data from one or more tables.

SELECT name, age FROM customers;

Filtering with WHERE:

SELECT * FROM customers WHERE age > 30;

Sorting results:

SELECT * FROM customers ORDER BY age DESC;

Limiting results:

SELECT * FROM products LIMIT 10;

2. Aggregation and Grouping

Aggregate functions summarize data, often used with GROUP BY.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Using HAVING:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

3. JOINs: Combining Tables

Joins are used to combine rows from two or more tables based on a related column.

SELECT orders.order_id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

Types of Joins:

• INNER JOIN: Only matching rows
• LEFT JOIN: All from left table + matched from right
• RIGHT JOIN: All from right table + matched from left
• FULL OUTER JOIN: All rows from both tables

4. Subqueries and Nested Queries

Subqueries are queries inside another SQL query, often used for filtering or calculating derived data.

SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE total > 1000
);

5. Modifying Data: INSERT, UPDATE, DELETE

Inserting new records:

INSERT INTO users(name, email) VALUES ('Alice', '[email protected]');

Updating records:

UPDATE users SET active = true WHERE last_login > '2024-01-01';

Deleting records:

DELETE FROM users WHERE status = 'inactive';

6. Schema Definition: DDL Commands

These statements define and alter database structures.

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  published_year INT
);

ALTER TABLE books ADD COLUMN author VARCHAR(100);

DROP TABLE obsolete_data;

7. Advanced Concepts

Views

CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

Indexing

Indexes speed up queries on large datasets:

CREATE INDEX idx_customer_name ON customers(name);

Window Functions

SELECT name, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

📚 Sample Database & Practice

Throughout the tutorial, you can follow along using the Sakila sample database:

Sakila Database Installation Guide

🎉 Wrap-Up

This post covered all major SQL concepts from the beginner to advanced level as demonstrated in the video. Practice regularly and explore real-world problems to sharpen your skills.