TechyVia
☰

Mastering SQL Fundamentals: A Comprehensive Guide

Introduction to SQL

What is SQL?

Imagine you're in a giant library with millions of books. You need to find a specific book, but you don’t know where it is. What do you do? You ask the librarian, right? Well, SQL (Structured Query Language) is like the librarian for databases. It’s a language that helps you communicate with databases, retrieve information, and manage data efficiently.

Whether you're building a website, analyzing sales data, or managing inventory, SQL is the foundation of modern data management.

A Brief History of SQL

SQL has been around since the 1970s when IBM researchers Raymond Boyce and Donald Chamberlin developed it. Back then, databases were complex and required deep expertise. SQL changed that by making database interactions simple and accessible. Today, it’s the standard language for working with relational databases like:

Fun fact: SQL is used by major companies like Google, Amazon, and Netflix to manage massive amounts of data!

SQL vs. NoSQL

You may have heard about NoSQL databases like MongoDB or Cassandra. So, what’s the difference? Let’s compare:

Feature SQL (Relational) NoSQL (Non-Relational)
Structure Table-based (rows & columns) Flexible (JSON, key-value, graph)
Use Case Structured data with relationships Unstructured or hierarchical data
Example Databases MySQL, PostgreSQL MongoDB, Firebase

For now, we’ll focus on SQL because it’s the foundation of data management. Once you master SQL, learning NoSQL will be much easier!

Types of SQL Commands

SQL commands are divided into five main categories:

Flowchart: Different Categories of SQL Commands

Why Learn SQL?

Interactive Example

Try this simple query in an SQL editor:

SELECT 'Hello, SQL World!' AS message;

This query returns a friendly message. Try running it and see the result!

Key Takeaways

What’s Next?

Now that we understand SQL basics, let's set up your SQL environment and start writing queries!

Setting Up Your SQL Environment

Why Set Up an SQL Environment?

Before you can start writing SQL queries, you need a place to practice—just like setting up a kitchen before cooking. Your SQL environment is where you’ll create databases, write queries, and see results.

Step 1: Choose Your SQL Database

There are many SQL databases, but here are some of the most popular ones:

Database Best For Free?
MySQL Web applications, beginners ✅ Yes
PostgreSQL Advanced features, scalability ✅ Yes
SQL Server Enterprise applications ⚠️ Free (Express version only)
SQLite Lightweight, mobile apps ✅ Yes

Step 2: Install Your SQL Database

Let’s use MySQL as an example. If you choose another, the steps will be similar.

  1. Download MySQL from the official website.
  2. Run the installer and follow the prompts.
  3. Set a root password (important—don’t forget it!).
  4. Optionally, install MySQL Workbench for an easy-to-use graphical interface.

Step 3: Set Up an SQL IDE

An SQL IDE is like a text editor for writing queries. Here are some popular choices:

Step 4: Try an Online SQL Playground

If you don’t want to install anything, use an online SQL playground:

Step 5: Write Your First Query

Test if your SQL setup works by running this simple query:

SELECT 'Hello, SQL World!' AS message;

Expected output:

message
Hello, SQL World!

Common Setup Issues and Fixes

What’s Next?

Now that your SQL environment is ready, let’s explore Database Fundamentals in the next section.

Interactive Example

Try this calculation in an online SQL editor:

SELECT 2 + 2 AS result;

What result do you get? 🎯

Key Takeaways

Database Fundamentals

What is a Database?

Imagine you’re running a small bookstore with a growing list of books, customers, and sales. Keeping track of everything in a notebook might work at first, but what happens when you have thousands of records? That’s where a database comes in.

A database is like a digital filing cabinet—a system that stores and organizes data for easy access, management, and updates. Whether you're running an online store, managing employee records, or analyzing customer trends, databases are the backbone of data-driven projects.

Tables, Rows, and Columns

In SQL, data is stored in tables. Think of a table as a spreadsheet with:

Here’s an example of what a books table might look like:

book_id title author price
1 Harry Potter and the Sorcerer’s Stone J.K. Rowling $20.99
2 The Great Gatsby F. Scott Fitzgerald $15.99
3 To Kill a Mockingbird Harper Lee $12.99

Data Types in SQL

Each column in a table has a data type that defines what kind of data it can store. Some common data types include:

Data Type Description Example
INT Stores whole numbers 1, 42, -7
VARCHAR(n) Stores text (max length n) 'Hello, World!'
DATE Stores dates 2023-10-01
DECIMAL(p, s) Stores precise decimal values 19.99 (p=5, s=2)
BOOLEAN Stores TRUE or FALSE TRUE

Primary Key, Foreign Key, and Unique Key

Creating a Table

Let’s create the books table using SQL:


CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100),
price DECIMAL(5, 2),
year_published INT
);

Inserting Data into the Table

Once the table is created, you can add data using INSERT INTO:


INSERT INTO books (book_id, title, author, price, year_published)
VALUES (1, 'Harry Potter and the Sorcerer’s Stone', 'J.K. Rowling', 20.99, 1997);

Viewing Data

To see all records in the books table, use:

SELECT * FROM books;

Common Mistakes to Avoid

What’s Next?

Now that we understand tables, rows, and columns, let’s dive into Basic SQL Commands in the next section.

Interactive Example

Try creating a students table using this query:


CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
grade CHAR(1)
);

Then, insert a record:


INSERT INTO students (student_id, name, age, grade)
VALUES (1, 'Alice', 18, 'A');

Finally, view the data:

SELECT * FROM students;

Key Takeaways

Basic SQL Commands

Introduction to Basic SQL Commands

SQL is like a Swiss Army knife for databases. It provides powerful commands that allow you to create, read, update, and delete data. These are the four essential SQL commands that every database user must know:

1. SELECT: Retrieving Data

The SELECT command is like asking a question to your database. It allows you to fetch data from one or more tables.

Example 1: Retrieve All Columns


SELECT * FROM books;

This query returns all rows and columns from the books table.

Example 2: Retrieve Specific Columns


SELECT title, author FROM books;

Example 3: Filter Data with WHERE


SELECT * FROM books
WHERE year_published > 2000;

This query returns books published after the year 2000.

2. INSERT INTO: Adding Data

The INSERT INTO command allows you to add new rows to a table.

Example 1: Insert a Single Row


INSERT INTO books (book_id, title, author, price, year_published)
VALUES (4, '1984', 'George Orwell', 14.99, 1949);

Example 2: Insert Multiple Rows


INSERT INTO books (book_id, title, author, price, year_published)
VALUES 
(5, 'The Catcher in the Rye', 'J.D. Salinger', 10.99, 1951),
(6, 'Pride and Prejudice', 'Jane Austen', 9.99, 1813);

3. UPDATE: Modifying Data

The UPDATE command allows you to modify existing data in a table.

Example 1: Update a Single Column


UPDATE books
SET price = 16.99
WHERE title = '1984';

Example 2: Update Multiple Columns


UPDATE books
SET price = 12.99, year_published = 1950
WHERE title = 'The Catcher in the Rye';

4. DELETE: Removing Data

The DELETE command allows you to remove rows from a table.

Example 1: Delete a Single Row


DELETE FROM books
WHERE book_id = 6;

Example 2: Delete All Rows (Be Careful!)


DELETE FROM books;

Warning: This command removes all rows from the table but keeps the structure.

Common Mistakes to Avoid

What’s Next?

Now that you know the basic SQL commands, let's move to Filtering and Sorting Data using WHERE, ORDER BY, and LIMIT.

Interactive Example

Try these queries in your SQL environment:

Retrieve all books by J.K. Rowling:


SELECT * FROM books
WHERE author = 'J.K. Rowling';

Add a new book to the books table:


INSERT INTO books (book_id, title, author, price, year_published)
VALUES (7, 'The Hobbit', 'J.R.R. Tolkien', 18.99, 1937);

Update the price of "The Hobbit" to $20.99:


UPDATE books
SET price = 20.99
WHERE title = 'The Hobbit';

Delete the book with book_id 7:


DELETE FROM books
WHERE book_id = 7;

Key Takeaways

Filtering and Sorting Data

Why Filter and Sort Data?

Imagine searching for a book in a library. Instead of browsing every book, you’d filter by author or genre and sort by title or publication date. In SQL, filtering and sorting help you efficiently retrieve the data you need.

1. Filtering Data with WHERE

The WHERE clause acts as a filter, allowing you to retrieve only rows that meet specific conditions.

Syntax


SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example 1: Filter by a Single Condition

Find all books written by J.K. Rowling:


SELECT * FROM books
WHERE author = 'J.K. Rowling';

Example 2: Filter with Comparison Operators

Retrieve books published after 2000:


SELECT * FROM books
WHERE year_published > 2000;

Example 3: Combine Filters with Logical Operators

Find books by J.K. Rowling that were published after 2000:


SELECT * FROM books
WHERE author = 'J.K. Rowling' AND year_published > 2000;

2. Sorting Data with ORDER BY

The ORDER BY clause sorts query results in ascending (ASC) or descending (DESC) order.

Syntax


SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC|DESC;

Example 1: Sort by a Single Column

Sort books alphabetically by title:


SELECT * FROM books
ORDER BY title ASC;

Example 2: Sort by Multiple Columns

Sort books by author (A-Z) and then by price (highest to lowest):


SELECT * FROM books
ORDER BY author ASC, price DESC;

3. Limiting Results with LIMIT

The LIMIT clause restricts the number of rows returned in a query, useful for paginated results.

Syntax


SELECT column1, column2, ...
FROM table_name
LIMIT number;

Example 1: Retrieve a Limited Number of Rows

Get the first 3 books:


SELECT * FROM books
LIMIT 3;

Example 2: Skip Rows with OFFSET

Skip the first 2 books and get the next 3:


SELECT * FROM books
LIMIT 3 OFFSET 2;

4. Combining Filters and Sorting

You can combine WHERE, ORDER BY, and LIMIT to create more powerful queries.

Example 1: Find the 3 Cheapest Books Published After 2000


SELECT * FROM books
WHERE year_published > 2000
ORDER BY price ASC
LIMIT 3;

Example 2: Retrieve the 2 Most Recent Books by J.K. Rowling


SELECT * FROM books
WHERE author = 'J.K. Rowling'
ORDER BY year_published DESC
LIMIT 2;

Common Mistakes to Avoid

What’s Next?

Now that you know how to filter, sort, and limit data, let's dive into Aggregate Functions like COUNT, SUM, AVG, MIN, and MAX in the next section.

Interactive Practice

Try these challenges in your SQL environment:

Find books priced between $10 and $20:


SELECT * FROM books
WHERE price BETWEEN 10 AND 20;

Sort books by author (A-Z) and year_published (newest to oldest):


SELECT * FROM books
ORDER BY author ASC, year_published DESC;

Retrieve the 5 oldest books:


SELECT * FROM books
ORDER BY year_published ASC
LIMIT 5;

Key Takeaways

Grouping Data with GROUP BY

What is GROUP BY?

The GROUP BY clause is like a magic wand for summarizing data. It allows you to group rows that have the same values in specified columns and then apply aggregate functions to each group. Some examples include:

1. Basic GROUP BY Syntax

The GROUP BY clause groups rows that have the same values in specified columns.

Syntax


SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example 1: Count Books by Author

To count the number of books written by each author:


SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author;

Example 2: Average Price by Author

Find the average price of books by each author:


SELECT author, AVG(price) AS average_price
FROM books
GROUP BY author;

2. GROUP BY with Aggregate Functions

You can use COUNT, SUM, AVG, MIN, and MAX with GROUP BY.

Example 1: Find the Most Expensive Book by Author


SELECT author, MAX(price) AS most_expensive_book
FROM books
GROUP BY author;

Example 2: Find the Oldest Book by Author


SELECT author, MIN(year_published) AS oldest_book_year
FROM books
GROUP BY author;

3. Filtering Groups with HAVING

The HAVING clause filters grouped results. Unlike WHERE, which filters individual rows, HAVING filters entire groups after aggregation.

Syntax


SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Example 1: Authors with More Than 2 Books


SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author
HAVING COUNT(*) > 2;

Example 2: Authors with an Average Price Above $15


SELECT author, AVG(price) AS average_price
FROM books
GROUP BY author
HAVING AVG(price) > 15;

4. GROUP BY with Multiple Columns

You can group by multiple columns for more detailed summaries.

Example 1: Count Books by Author and Year


SELECT author, year_published, COUNT(*) AS book_count
FROM books
GROUP BY author, year_published;

Example 2: Average Price by Author and Year


SELECT author, year_published, AVG(price) AS average_price
FROM books
GROUP BY author, year_published;

Common Mistakes to Avoid

What’s Next?

Now that you know how to group and summarize data, let’s explore Joins. Joins allow you to combine data from multiple tables, such as linking books to their authors or orders to their customers.

Interactive Practice

Try these challenges in your SQL environment:

Find the average price of books by each author, but only for authors with more than 1 book:


SELECT author, AVG(price) AS average_price
FROM books
GROUP BY author
HAVING COUNT(*) > 1;

Count the number of books published each year, but only for years after 1950:


SELECT year_published, COUNT(*) AS book_count
FROM books
WHERE year_published > 1950
GROUP BY year_published;

Find the total price of books by each author in each year, but only for authors with a total price above $50:


SELECT author, year_published, SUM(price) AS total_price
FROM books
GROUP BY author, year_published
HAVING SUM(price) > 50;

Key Takeaways

Working with NULL Values

What Are NULL Values?

In SQL, NULL represents a missing or unknown value. It’s not the same as zero, an empty string, or a blank space—it simply means the value is unknown. Think of it as a placeholder for "I don’t know" or "this doesn’t apply."

For example:

Why Are NULLs Tricky?

NULL values behave differently from other values, which can lead to unexpected results. For example:

1. Checking for NULL Values

Use IS NULL and IS NOT NULL to check for NULL values.

Example 1: Find Books with Unknown Publication Years


SELECT title
FROM books
WHERE year_published IS NULL;

Example 2: Find Books with Known Publication Years


SELECT title
FROM books
WHERE year_published IS NOT NULL;

2. Handling NULLs in Calculations

When NULL is involved in calculations, the result is always NULL.

Example 1: Calculate Total Price with NULL Values

If a discount column has NULL values, the final price calculation may be incorrect:


SELECT price - discount AS final_price
FROM prices;

Solution: Use COALESCE to replace NULL values with a default value:


SELECT price - COALESCE(discount, 0) AS final_price
FROM prices;

3. Combining NULL Checks with Other Conditions

You can combine IS NULL and IS NOT NULL with other conditions.

Example 1: Find Books with Unknown Publication Years and High Prices


SELECT title
FROM books
WHERE year_published IS NULL AND price > 20;

Example 2: Find Customers with Emails or Phone Numbers


SELECT customer_name
FROM customers
WHERE email IS NOT NULL OR phone IS NOT NULL;

4. NULLs in Aggregations

Aggregate functions like SUM, AVG, MIN, and MAX automatically ignore NULL values.

Example 1: Calculate Total Sales Ignoring NULLs


SELECT SUM(amount) AS total_sales
FROM orders;

Example 2: Count Non-NULL Values


SELECT COUNT(year_published) AS known_years
FROM books;

Common Mistakes to Avoid

What’s Next?

Now that you know how to handle NULL values, let’s explore Joins. Joins allow you to combine data from multiple tables, and they often involve handling NULL values when there’s no match.

Interactive Practice

Try these challenges in your SQL environment:

Find books with unknown publication years:


SELECT title
FROM books
WHERE year_published IS NULL;

Calculate the total price of orders, replacing NULL quantities with 1:


SELECT order_id, price * COALESCE(quantity, 1) AS total_price
FROM orders;

Find customers with missing email addresses or phone numbers:


SELECT customer_name
FROM customers
WHERE email IS NULL OR phone IS NULL;

Key Takeaways