DECEMBER 2024

SQL Mastery: From

Beginner to Pro
Short course

SCIENTIA ET
PRATIQUE
SQL Mastery: From Beginner to Pro
Unlocking the power of SQL

Course: SQL Mastery: From Beginner to Pro

Institution: Institute of Management Technology & Finance

(MTF)

Lecturer: Alex, Product Researcher, Research Consultant

and Lecturer; PhD in Health Anthropology

2
SQL Mastery: From Beginner to Pro
Course overview

● Introduction to SQL and SQLite

● Basic SQL Commands – The Foundation

● Retrieving and Manipulating Data

● Advanced Queries and Data Aggregation

● Working with Joins

● Subqueries and Nested Queries

● Modifying Data in SQL

● Optimising and Indexing Your Queries

● Advanced SQL Features

3
SQL Mastery: From Beginner to Pro
M1: Introduction to SQL and SQLite

What is SQL

● SQL stands for Structured Query Language

● It’s a domain-specific language for managing relational
databases
● SQL is used to:
○ Create and modify databases
○ Query data
○ Insert, update, and delete records
● Examples of databases: MySQL, PostgreSQL, Oracle,
SQLite
● SQL is universal across relational database systems

4
SQL Mastery: From Beginner to Pro
M1: Introduction to SQL and SQLite

Why SQL is Important for Business

● Marketing: SQL helps analyse customer behaviours and

optimise campaigns.
● Finance: SQL is essential for tracking financial
transactions and generating reports.
● Data Analytics: Extracts valuable business insights from
large datasets.
● Operations: SQL can help streamline and automate
workflows.
● Data-driven Decision Making: Businesses can make
more informed decisions by analysing their data with
SQL.

5
SQL Mastery: From Beginner to Pro
M1: Introduction to SQL and SQLite

What is SQLite

● SQLite is a self-contained, serverless SQL database

engine
● Open-source and free to use
● Designed for embedded database applications
● Easy to install and use
● Ideal for development, testing, and small-scale
applications
● SQLite works on multiple platforms: Windows, Mac,
Linux, and mobile devices

6
SQL Mastery: From Beginner to Pro
M1: Introduction to SQL and SQLite

Setting Up SQLite

● Download the document: Module 1: Setting up SQLite,

and follow the instructions.
● Video: Module1_Access SQLite CLI

7
SQL Mastery: From Beginner to Pro
M2: Basic SQL Commands: The Foundation

SQL commands can be divided into three main categories:

● Data Definition Language (DDL): Used to define the

structure of the database (e.g., CREATE, ALTER, DROP).
● Data Manipulation Language (DML): Used to
manipulate data within the database (e.g., INSERT,
UPDATE, DELETE).
● Data Query Language (DQL): Used to query data (e.g.,
SELECT).

8
SQL Mastery: From Beginner to Pro
M2: Basic SQL Commands: The Foundation

CREATE DATABASE, CREATE TABLE, INSERT INTO

● Create a database:
○ Syntax: CREATE DATABASE database_name;
● Create a table:
○ Syntax: CREATE TABLE table_name ( column1
datatype, column2 datatype, ... );
● Insert data:
○ Syntax: INSERT INTO table_name (column1, column2,
...) VALUES (value1, value2, ...);

9
SQL Mastery: From Beginner to Pro
M2: Basic SQL Commands: The Foundation

Exercise 1: Creating Databases and Tables

● Objective: Create a database and table for a simple

business use case.
● Step 1: Create a new database called business_db.
● Step 2: Create a table called products with the following
columns:
○ product_id (integer, primary key)
○ product_name (text)
○ price (decimal, with two decimal places)
○ quantity (integer)

10
SQL Mastery: From Beginner to Pro
M2: Basic SQL Commands: The Foundation

Exercise 2: Inserting Data

● Objective: Add sample product data to your products

table.
● Step 1: Insert data for at least 3 products with their
product_name, price, and quantity.
● Step 2: Verify that data was inserted correctly by using
the SELECT * FROM products; command.

11
SQL Mastery: From Beginner to Pro
M3: Retrieving and Manipulating Data

This module covers essential SQL commands for querying

and manipulating data:

● Basic Queries with SELECT

● Filtering Data with WHERE and Operators
● Sorting Data with ORDER BY
● Limiting Data with LIMIT

12
SQL Mastery: From Beginner to Pro
M3: Retrieving and Manipulating Data

Basic Queries with SELECT

● Syntax: SELECT column1, column2, ... FROM table_name;

● To select all columns: SELECT * FROM table_name;
● Filters results based on a condition: SELECT column1,
column2 FROM table_name WHERE condition;
● Sorts the result set: SELECT column1, column2 FROM
table_name ORDER BY column1 [ASC|DESC];

13
SQL Mastery: From Beginner to Pro
M3: Retrieving and Manipulating Data

Exercise 3: Basic Queries with SELECT

● Objective: Retrieve all products from your products table

and sort them by price.
● Step 1: Use the SELECT command to retrieve all columns
from the products table.
● Step 2: Sort the products by the price column in
descending order to show the most expensive items
first.

14
SQL Mastery: From Beginner to Pro
M3: Retrieving and Manipulating Data

Filtering Data with WHERE and Operators

● Comparison Operators:
○ =: Equal to
○ <> or !=: Not equal to
○ >: Greater than
○ <: Less than
○ >=: Greater than or equal to
○ <=: Less than or equal to
● Logical Operators:
○ AND: Combines multiple conditions.
○ OR: At least one condition must be true.
○ NOT: Reverses a condition.

15
SQL Mastery: From Beginner to Pro
M3: Retrieving and Manipulating Data

Exercise 4: Filtering and Sorting Data

● Objective: Filter products based on specific conditions

and sort the results.
● Step 1: Retrieve products with a price greater than
$0.60.
● Step 2: Sort the results by quantity in ascending order.

16
SQL Mastery: From Beginner to Pro
M3: Retrieving and Manipulating Data

Limiting Data with LIMIT

● Restricts the number of rows returned by the query:

SELECT column1, column2 FROM table_name LIMIT number;

17
SQL Mastery: From Beginner to Pro
M3: Retrieving and Manipulating Data

Exercise 5: Limiting Data with LIMIT

● Objective: Retrieve only the first product from the

products table.
● Step 1: Use the LIMIT clause to restrict the number of
rows returned to 1.

18
SQL Mastery: From Beginner to Pro
M4: Advanced Queries and Data Aggregation

This module focuses on aggregation SQL features:

● Aggregate Functions: COUNT(), SUM(), AVG(), MIN(),

MAX()
● Grouping Data: GROUP BY
● Filtering Grouped Data: HAVING

19
SQL Mastery: From Beginner to Pro
M4: Advanced Queries and Data Aggregation

Aggregate Functions:

● COUNT(): Returns the number of rows in a set.

● SUM(): Returns the total sum of a numeric column.
● AVG(): Returns the average value of a numeric column.
● MIN(): Returns the smallest value in a column.
● MAX(): Returns the largest value in a column.

20
SQL Mastery: From Beginner to Pro
M4: Advanced Queries and Data Aggregation

Exercise 6: Aggregate Functions

● Objective: Calculate the total and average price of all

products in the table.
● Step 1: Use the SUM() function to calculate the total
price of all products.
● Step 2: Use the AVG() function to calculate the average
price of the products.

21
SQL Mastery: From Beginner to Pro
M4: Advanced Queries and Data Aggregation

Grouping Data with GROUP BY

● SELECT column, aggregate_function(column) FROM table

GROUP BY column;

22
SQL Mastery: From Beginner to Pro
M4: Advanced Queries and Data Aggregation

Exercise 7: Grouping Products by Category and Filtering

Results

● Objective: Find the number of products in each category

and filter the results to show only those categories that
have more than 2 products.
● Step 1: Use GROUP BY to group the products by
category.
● Step 2: Use COUNT() to find the number of products in
each category.
● Step 3: Filter the groups where the count is greater than
2 using the HAVING clause.

23
SQL Mastery: From Beginner to Pro
M5: Working with Joins

Joins in SQL allow you to combine rows from two or more

tables based on a related column.

Types of Joins:

● INNER JOIN: Returns only matching rows from both

tables.
● LEFT JOIN: Returns all rows from the left table and
matching rows from the right table.
● RIGHT JOIN: Returns all rows from the right table and
matching rows from the left table.
● FULL OUTER JOIN: Returns all rows when there is a match
in either left or right table.

24
SQL Mastery: From Beginner to Pro
M5: Working with Joins

Exercise 8: Introduction to Joins

● Objective: Join two tables: Products and Categories.

● Step 1: Use INNER JOIN to match products with their
categories.

25
SQL Mastery: From Beginner to Pro
M6: Subqueries and Nested Queries

● Subqueries: A query within another query.

● Types of Subqueries:
○ Inline Subqueries: A subquery that returns a single
value to be used in the main query.
○ Nested Subqueries: A subquery that returns a
result set which can be used by another subquery.
○ Correlated Subqueries: A subquery that references
a column from the outer query.

26
SQL Mastery: From Beginner to Pro
M6: Subqueries and Nested Queries

Inline Subqueries

● Definition: An inline subquery is a simple subquery that

returns a single value.
● Use Case: It’s used in SELECT, WHERE, or HAVING
clauses to filter or modify the result set.
● Syntax: SELECT column1, column2 FROM table WHERE
column2 > (SELECT AVG(column2) FROM table);

27
SQL Mastery: From Beginner to Pro
M6: Subqueries and Nested Queries

Nested Subqueries

● Definition: A nested subquery returns a result set, which

can be used by the outer query.
● Use Case: It’s used to retrieve a list of values that can
be compared to other values.
● Syntax: SELECT column1, column2 FROM table1
● WHERE column2 IN (SELECT column3 FROM table2 WHERE
column4 = 'value');

28
SQL Mastery: From Beginner to Pro
M6: Subqueries and Nested Queries

Correlated Subqueries

● Definition: A correlated subquery refers to columns from

the outer query. It is evaluated for each row processed
by the outer query.
● Use Case: Correlated subqueries are used when the
inner query depends on the values of the outer query.
● Syntax: SELECT column1, column2 FROM table1 t1 WHERE
column2 > (SELECT AVG(column2) FROM table1 t2 WHERE
t1.column3 = t2.column3);

29
SQL Mastery: From Beginner to Pro
M6: Subqueries and Nested Queries

Exercise 10: Writing Subqueries

● Objective: Write a query that retrieves products with the

highest price from each category using subqueries.
● Step 1: Retrieve the highest price for each category
using a subquery.
● Step 2: Use the subquery to filter products with the
highest price.

30
SQL Mastery: From Beginner to Pro
M7: Modifying Data in SQL

Modifying Data: The core of DML (Data Manipulation

Language)

● UPDATE: Modifies existing data.

● DELETE: Removes data.

Key Concepts:

● Atomicity: Changes must be complete or not happen at

all.
● Consistency: Data must remain valid according to
constraints.
● Isolation: Transactions are independent of each other.
● Durability: Once committed, changes are permanent.

31
SQL Mastery: From Beginner to Pro
M7: Modifying Data in SQL

UPDATE

● Syntax: UPDATE table_name SET column_name = value

WHERE condition;
● Modifies existing data.
● Use WHERE clause to specify the rows to update.
● Always be cautious with UPDATE to avoid unintended
changes.

32
SQL Mastery: From Beginner to Pro
M7: Modifying Data in SQL

DELETE

● Syntax: DELETE FROM table_name WHERE condition;

● Deletes records based on a condition.
● Without WHERE, all rows will be deleted (use with
caution).
● It’s possible to delete all data in a table without dropping
the table.

33
SQL Mastery: From Beginner to Pro
M7: Modifying Data in SQL

Exercise 11: Updating and Deleting Data

● Objective: Modify and remove data in the products table.

● Task 1: Update product prices for specific categories.
● Task 2: Delete products that are obsolete.

34
SQL Mastery: From Beginner to Pro
M7: Modifying Data in SQL

What is a Transaction?

● A sequence of SQL operations that are treated as a

single unit.
● Either all operations are committed or none.

ACID Properties:

● Atomicity: All or nothing.

● Consistency: Data remains valid after the transaction.
● Isolation: Each transaction is independent.
● Durability: Once committed, changes are permanent.

35
SQL Mastery: From Beginner to Pro
M7: Modifying Data in SQL

● BEGIN: Starts a transaction.

● COMMIT: Makes all changes permanent.
● ROLLBACK: Reverts changes if something goes wrong.

36
SQL Mastery: From Beginner to Pro
M7: Modifying Data in SQL

Exercise 12: Transactions in SQL

● Objective: Use transactions to update multiple product

records.
● Task: Implement a transaction that adjusts product
prices based on categories.

37
SQL Mastery: From Beginner to Pro
M8: Optimising and Indexing Your Queries

● Query optimisation: Speed up your database queries.

● Indexes: A powerful tool for improving query
performance.
● Performance: Understanding how to analyse and
optimise your SQL queries.
● Indexing: Reduces search time by organising data.
● EXPLAIN: A tool to understand how SQL queries are
executed.

38
SQL Mastery: From Beginner to Pro
M8: Optimising and Indexing Your Queries

What is an Index?

● A data structure that helps speed up data retrieval.

● Indexes are created on one or more columns in a table.

Types of Indexes:

● Single-Column Index: Index on a single column.

● Composite Index: Index on multiple columns.
● Unique Index: Ensures that all values in the indexed
column(s) are unique.

39
SQL Mastery: From Beginner to Pro
M8: Optimising and Indexing Your Queries

Creating Indexes

● Basic Syntax to Create an Index: CREATE INDEX

index_name ON table_name (column1, column2);

40
SQL Mastery: From Beginner to Pro
M8: Optimising and Indexing Your Queries

Exercise 13: Creating Indexes

● Objective: Create indexes on frequently queried

columns.
● Task 1: Create an index on the name column in the
products table.
● Task 2: Create a composite index on category_id and
price columns.

41
SQL Mastery: From Beginner to Pro
M8: Optimising and Indexing Your Queries

Viewing Indexes

● List All Indexes: SELECT name FROM sqlite_master WHERE

type = 'index';
● List Indexes for a Specific Table: SELECT name FROM
sqlite_master WHERE type = 'index' AND tbl_name =
'table_name';
● Describe a Specific Index: PRAGMA
index_info('idx_index_name');

42
SQL Mastery: From Beginner to Pro
M8: Optimising and Indexing Your Queries

● EXPLAIN: A tool used to display the execution plan of a

query.
● Syntax: EXPLAIN SELECT column_name FROM table_name
WHERE condition;
● EXPLAIN Output:
○ Type: The join type used.
○ Rows: Estimated number of rows the query will
examine.
○ Extra: Additional information on the query
execution.

43
SQL Mastery: From Beginner to Pro
M8: Optimising and Indexing Your Queries

Query Optimisation Techniques

● Query Optimisation: The process of improving the

efficiency of a SQL query.
● Goal: Minimise the time and resources required to
execute a query.
● Techniques:
● Use indexes: Speed up searches on large tables.
● Avoid SELECT : Only retrieve the columns you need.
● Use proper JOIN types: Avoid unnecessary CROSS JOINs
or FULL OUTER JOINs.
● EXPLAIN Command: Analyses how SQL queries are
executed.

44
SQL Mastery: From Beginner to Pro
M9: Advanced SQL Features

● Working with Views to simplify complex queries.

● Using Triggers and Stored Procedures for automating
tasks.

Key concepts:

● Views: Virtual tables for easier querying.

● Triggers: Automatically execute SQL code in response
to events.
● Stored Procedures: Reusable SQL code for commonly
executed tasks.

45
SQL Mastery: From Beginner to Pro
M9: Advanced SQL Features

What is a View?

● A virtual table based on the result of a query.

● Views simplify complex queries by hiding the
complexity.
● Can be used in place of a table in queries.

Advantages of Views:

● Data security (restrict access to sensitive columns).

● Simplifies complex joins and aggregations.
● Reduces redundant queries.

46
SQL Mastery: From Beginner to Pro
M9: Advanced SQL Features

Creating and Using Views

● Syntax: CREATE VIEW view_name AS SELECT column1,

column2 FROM table_name WHERE condition;

47
SQL Mastery: From Beginner to Pro
M9: Advanced SQL Features

Exercise 14: Using Views

● Objective: Create a view that combines product data

with supplier information.
● Task 1: Write a SELECT query that joins the products table
with the suppliers table.
● Task 2: Create a view named product_supplier_view
based on that query.
● Task 3: Query the view to display product and supplier
information.

48
SQL Mastery: From Beginner to Pro
M9: Advanced SQL Features

What is a Trigger?

● A database object that automatically executes a SQL

statement when a certain event occurs.
● Events: Insert, update, or delete operations.
● Purpose: Automate tasks, such as updating related data
or logging changes.

What is a Stored Procedure?

● A precompiled set of SQL statements that can be

executed as a unit.
● Can accept input parameters and return output.
● Helps automate repetitive tasks.

49
SQL Mastery: From Beginner to Pro
M9: Advanced SQL Features

Creating Triggers

Syntax: CREATE TRIGGER trigger_name AFTER INSERT ON

table_name FOR EACH ROW BEGIN -- SQL commands END;

50
SQL Mastery: From Beginner to Pro
M9: Advanced SQL Features

Exercise 15: Triggers and Stored Procedures

● Objective: Create a trigger that automatically updates

stock after a product is sold.
● Task 1: Create a sales table.
● Task 2: Create a trigger to update the stock level in the
products table when a sale occurs.

51
SQL Mastery: From Beginner to Pro
M10: Final Project

● Build a complete business database for a fictional

e-commerce store.
● Use SQL techniques learned throughout the course.
● Create tables, insert data, write queries, and generate
reports.

52
SQL Mastery: From Beginner to Pro
M10: Final Project

Project Overview: E-Commerce Business Database

● Designing the structure

● Create tables
● Inserting the data
● Write at least 5 different queries to analyse sales,
customers, and payments. These queries should cover:
○ Sales by product.
○ Revenue by category.
○ Orders by customer.
○ Unpaid orders report.
○ Payments report.

53
SQL Mastery: From Beginner to Pro
Bonus Section: Recommended Resources

Free Online Resources:

● SQLZoo (Interactive Learning Platform)

● Khan Academy – SQL Basics
● W3Schools SQL Tutorial
● LeetCode SQL Challenges
● SQL Bolt

54
SQL Mastery: From Beginner to Pro
Bonus Section: Recommended Resources

Community Forums and Discussion Platforms:

● Stack Overflow – SQL Tag

● Reddit – r/SQL
● SQLServerCentral

55
SQL Mastery: From Beginner to Pro
Bonus Section: Recommended Resources

Blogs for Continuous Learning:

● SQLServerCentral Blog
● SQL Performance Blog by SQLServerPerformance
● Use The Index, Luke! (Blog)

56
SQL Mastery: From Beginner to Pro
Bonus Section: Recommended Resources

Additional Tools to Practice SQL:

● DB Fiddle
● SQL Fiddle

57
SCIENTIA ET
PRATIQUE