One-Day SQL Learning Guide.

Syed Ammar

Content Creator

SQLite

Education

Your One-Day Guide to Learning SQL

Welcome! This guide is designed to help you learn the fundamentals of SQL (Structured Query Language) within a single day. SQL is an essential skill for anyone working with data, whether in software development, data analysis, data science, or database administration.

This guide brings together a structured learning path, key resources, interactive examples, and practice exercises to provide a comprehensive learning experience.

How to Use This Guide:

Follow the Outline: The guide is structured around a learning outline. Progress through the sections sequentially.

Use the Resources: Links to high-quality tutorials and documentation are provided for deeper understanding.

Practice Interactively: Use the provided schema, data, and example queries in an online SQL playground to see SQL in action.

Test Your Knowledge: Utilize the list of practice exercise platforms to solidify your skills.

Let's get started!

Learning Outline

1. Introduction to SQL and Databases

What is SQL? Understanding the purpose and role of Structured Query Language.

What is a Database? Introduction to databases, focusing on Relational Database Management Systems (RDBMS).

Why Learn SQL? Key benefits and applications of SQL skills.

Setting Up: Options for a practice environment (e.g., using online SQL playgrounds like SQLiteOnline or DB Fiddle, or installing a local database like SQLite).

2. Basic SQL Syntax

SQL Statements: Overview of common SQL statement types.

Core Keywords: Understanding SELECT, FROM, WHERE.

Data Types: Common SQL data types (e.g., INT, VARCHAR, DATE, DECIMAL).

Comments: How to add comments to SQL code.

3. Retrieving Data (`SELECT`)

Selecting Columns: Choosing specific data fields.

Filtering Data (WHERE): Using comparison operators (=, !=, <, >, <=, >=), logical operators (AND, OR, NOT), and special operators (BETWEEN, LIKE, IN, IS NULL).

Sorting Results (ORDER BY): Arranging output in ascending (ASC) or descending (DESC) order.

Limiting Results (LIMIT / TOP): Restricting the number of rows returned.

4. Working with Multiple Tables (`JOIN`s)

Database Relationships: Understanding Primary Keys and Foreign Keys.

INNER JOIN: Combining rows from two tables based on a related column.

LEFT JOIN / RIGHT JOIN: Including all rows from one table and matched rows from the other.

(Optional) FULL OUTER JOIN: Combining all rows from both tables.

Self JOIN: Joining a table to itself.

5. Aggregating Data

Aggregate Functions: Using COUNT, SUM, AVG, MIN, MAX to perform calculations on sets of rows.

Grouping Data (GROUP BY): Grouping rows that have the same values in specified columns to apply aggregate functions.

Filtering Groups (HAVING): Filtering groups based on specified conditions after aggregation.

6. Modifying Data

Inserting Data (INSERT INTO): Adding new rows to a table.

Updating Data (UPDATE): Modifying existing data in a table.

Deleting Data (DELETE): Removing rows from a table.

7. Data Definition Language (DDL) - Basics

Creating Tables (CREATE TABLE): Defining the structure of a new table, including columns and data types.

Altering Tables (ALTER TABLE): Modifying an existing table structure (e.g., adding or dropping columns).

Dropping Tables (DROP TABLE): Deleting a table and all its data.

8. Practice and Next Steps

Practice Resources: Suggestions for online platforms and datasets for further practice.

Further Learning: Brief overview of more advanced SQL topics (e.g., Subqueries, Window Functions, Transactions, Indexes, Stored Procedures).

Recommended Learning Resources

To supplement the outline, here are some excellent resources for learning SQL basics:

W3Schools SQL Tutorial: Comprehensive, easy-to-follow tutorials with interactive "Try it Yourself" examples. Covers a wide range of SQL topics.

URL: https://www.w3schools.com/sql/

SQLTutorial.org: Clear explanations, hands-on examples, and quizzes. Well-structured for beginners.

URL: https://www.sqltutorial.org/

GeeksforGeeks SQL Tutorial: Detailed tutorials covering basics to advanced concepts, often with good examples.

URL: https://www.geeksforgeeks.org/sql-tutorial/

Codecademy Learn SQL: Interactive course where you write code directly in the browser. Great for hands-on learning (some content may require a subscription, but the basic course is often free).

URL: https://www.codecademy.com/learn/learn-sql

Mode Analytics SQL Tutorial: Focuses on SQL for data analysis, providing practical context.

URL: https://mode.com/sql-tutorial/

Choose one or two primary resources to follow along with the outline, and use others for clarification or different perspectives.

Interactive SQL Examples (Practice Playground)

Reading about SQL is one thing, but trying it yourself is crucial! Use the following schema, data, and queries in an online SQL playground to get hands-on experience.

Recommended Online SQL Playgrounds:

SQLiteOnline: https://sqliteonline.com/ (Choose SQLite)

DB Fiddle: https://www.db-fiddle.com/ (Choose SQLite or PostgreSQL)

W3Schools SQL Tryit Editor: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all (Uses a pre-defined database, good for SELECT practice)

Instructions:

Go to one of the recommended online playgrounds (SQLiteOnline or DB Fiddle are good choices for running all examples).

Copy the CREATE TABLE statements below and execute them in the playground to set up the database structure.

Copy the INSERT INTO statements and execute them to populate the tables with sample data.

Now you can copy and run the example queries one by one to see how they work and what results they produce.

Feel free to modify the queries and experiment!

1. Database Schema (CREATE TABLE)

-- Create the Departments table
CREATE TABLE Departments (
    dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dept_name VARCHAR(50) NOT NULL
);

-- Create the Employees table
CREATE TABLE Employees (
    emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10, 2),
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

2. Sample Data (INSERT INTO)

-- Insert data into Departments table
INSERT INTO Departments (dept_name) VALUES
('Sales'),
('HR'),
('IT'),
('Marketing');

-- Insert data into Employees table
INSERT INTO Employees (first_name, last_name, email, hire_date, salary, dept_id) VALUES
('John', 'Doe', 'john.doe@example.com', '2022-08-15', 60000.00, 1),
('Jane', 'Smith', 'jane.smith@example.com', '2023-01-20', 75000.00, 3),
('Peter', 'Jones', 'peter.jones@example.com', '2022-11-01', 50000.00, 1),
('Mary', 'Brown', 'mary.brown@example.com', '2023-03-10', 80000.00, 3),
('David', 'Williams', 'david.w@example.com', '2023-05-25', 55000.00, 4),
('Linda', 'Davis', 'linda.davis@example.com', '2022-09-30', 62000.00, 2),
('Michael', 'Miller', 'michael.m@example.com', '2023-07-11', 70000.00, 3),
('Sarah', 'Wilson', 'sarah.w@example.com', '2023-02-18', 58000.00, 4);

3. Example Queries

(Copy and run these in the playground after setting up the tables and data)

Basic Retrieval (SELECT)

SELECT * FROM Employees;
SELECT first_name, last_name, salary FROM Employees;
SELECT DISTINCT dept_id FROM Employees;

Filtering Data (WHERE)

SELECT * FROM Employees WHERE dept_id = 3;
SELECT first_name, last_name, salary FROM Employees WHERE salary > 60000;
SELECT * FROM Employees WHERE dept_id = 1 AND salary < 55000;
SELECT * FROM Employees WHERE dept_id IN (3, 4);
SELECT emp_id, first_name, email FROM Employees WHERE email LIKE '%example.com%';
SELECT * FROM Employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-06-30';

Sorting Results (ORDER BY)

SELECT * FROM Employees ORDER BY last_name ASC;
SELECT * FROM Employees ORDER BY salary DESC;

Limiting Results (LIMIT)

SELECT first_name, last_name, salary FROM Employees ORDER BY salary DESC LIMIT 3;

Joining Tables (JOIN)

SELECT E.first_name, E.last_name, D.dept_name
FROM Employees E
INNER JOIN Departments D ON E.dept_id = D.dept_id;

SELECT D.dept_name, E.first_name, E.last_name
FROM Departments D
LEFT JOIN Employees E ON D.dept_id = E.dept_id;

Aggregating Data (Aggregate Functions & GROUP BY)

SELECT COUNT(*) AS total_employees FROM Employees;
SELECT AVG(salary) AS average_salary FROM Employees;
SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary FROM Employees;
SELECT dept_id, COUNT(*) AS num_employees FROM Employees GROUP BY dept_id;
SELECT D.dept_name, AVG(E.salary) AS avg_dept_salary
FROM Departments D
JOIN Employees E ON D.dept_id = E.dept_id
GROUP BY D.dept_name;

Filtering Groups (HAVING)

SELECT dept_id, AVG(salary) AS avg_dept_salary
FROM Employees
GROUP BY dept_id
HAVING AVG(salary) > 65000;

Modifying Data (INSERT, UPDATE, DELETE - Use with Caution!)

-- INSERT INTO Departments (dept_name) VALUES ('Finance');
-- UPDATE Employees SET salary = 62000.00 WHERE emp_id = 1;
-- DELETE FROM Employees WHERE emp_id = 5;

Practice Exercises

Theory is important, but practice makes perfect! Use these resources to test your understanding and build your SQL querying skills:

Interactive Platforms & Online Terminals

W3Schools SQL Exercises: Offers interactive exercises integrated with their tutorials, covering various SQL topics. Good for reinforcing concepts as you learn them.

URL: https://www.w3schools.com/sql/sql_exercises.asp

SQL-Practice.com: Provides an online terminal where you can practice writing SQL queries against a sample database. It includes specific questions to solve.

URL: https://www.sql-practice.com/

HackerRank (SQL Domain): Features a wide variety of SQL challenges, often used for interview preparation. Problems range in difficulty.

URL: https://www.hackerrank.com/domains/sql

Codecademy (Learn SQL Course): While primarily a learning platform, their SQL course includes numerous hands-on exercises and projects within the interactive environment.

URL: https://www.codecademy.com/learn/learn-sql

Problem Sets & Collections

GeeksforGeeks SQL Exercises: Provides a collection of SQL exercises with solutions, covering various topics.

URL: https://www.geeksforgeeks.org/sql-exercises/

w3resource SQL Exercises: Offers a very large collection of SQL problems (over 2600) with solutions, categorized by topic.

URL: https://www.w3resource.com/sql-exercises/

LearnSQL.com (Blog Exercises): Features blog posts with specific practice exercises, often focusing on particular SQL concepts.

URL: https://learnsql.com/blog/sql-practice-exercises/

Wise Owl Training: Provides a good number of free SQL exercises covering standard SQL operations.

URL: https://www.wiseowl.co.uk/sql/exercises/standard/

CodeChef SQL Practice: Offers practice queries categorized by SQL concepts like SELECT, JOINs, Subqueries, etc., often based on real-life scenarios.

URL: https://www.codechef.com/practice/sql-case-studies-topic-wise

Recommendation: Start with exercises integrated into tutorials (like W3Schools or Codecademy) as you learn. Then, move to platforms like SQL-Practice.com or HackerRank for more focused query practice.

Final Tips for Today

Focus on Understanding: Don't just copy-paste queries. Try to understand why they work.

Experiment: Modify the example queries. What happens if you change a WHERE condition or an ORDER BY clause?

Don't Get Stuck: If you're struggling with a concept, consult multiple resources or try a different explanation.

Take Breaks: Learning intensely for a whole day can be tiring. Take short breaks to refresh.

Good luck with your SQL learning journey today!

Like this project

Posted May 2, 2025

Created a one-day guide to learn SQL fundamentals.

Likes

Views