For example on github
https://github.com/PLP-Database-DEPT/hands-on-example.git
https://github.com/PLP-Database-DEPT/hands-on-example.git
WEEK2 MYSQL NOTE
🔍 Mastering the SELECT Statement: Your Key to Data
Retrieval 🔑
https://youtu.be/HYD8KjPB9F8
Welcome back, data enthusiasts! 📊 In this lesson, we'll explore the powerful SELECT statement,
your gateway to extracting valuable insights from your database. Let's dive in! 🚀
Understanding the SELECT Statement 🧠
The SELECT statement is your primary tool for interacting with your database and retrieving the
data you need. Here's its basic structure:
SELECT column1, column2, ...
FROM table_name;
SELECT: Initiates the SQL command to retrieve data.
column1, column2, ...: Specifies the columns you want to retrieve. Use * to select all
columns.
FROM: Identifies the table containing the data.
;: Ends the SQL statement.
Exploring SELECT Functionality with Your Application 💻
Retrieving All Records 📋
If you need a complete overview of your data, use this query:
SELECT * FROM data_table;
This command retrieves all records from your table, giving you a full picture of your app's
activity.
Retrieving Specific Details 🔍
To focus on specific information, such as transaction amounts and categories, use:
SELECT amount, category
FROM data_table;
This query narrows down the view to just the "amount" and "category" columns, helping you
analyze specific aspects of your data.
By mastering these queries, you'll be able to manage and analyze your application's data
efficiently. Keep practising, and soon you'll be an SQL pro! 🌟
More Resources:
https://www.w3schools.com/sql/sql_select.asp
Advanced Data Retrieval: Wildcards and Comparison
Operators 🎯
https://youtu.be/T11d2ScMtk8
Welcome back, data detectives! In this lesson, we'll enhance your SQL skills with powerful
techniques for flexible and targeted data retrieval. Let's get started! 🚀
Wildcards (%) for Flexible Searching 🔍
Wildcards are your secret weapon for finding records with variations in wording. Here's how to
use them:
SELECT * FROM records
WHERE description LIKE '%keyword%';
This query retrieves all records where the description contains a "keyword" anywhere in the text.
The % symbol acts as a wildcard, matching any characters before or after the "keyword." Perfect
for capturing variations and synonyms! 🔄
Comparison Operators for Targeted Filtering 🎯
Comparison operators help you filter records based on specific criteria. Here are some examples:
SELECT * FROM transactions
WHERE amount > 100;
This query finds all transactions where the amount exceeds 100. Use comparison operators like
=, >, <, <=, and >= to target specific records efficiently. 📈
Example: Filtering by date range
SELECT * FROM events
WHERE event_date
BETWEEN '2023-01-01' AND '2023-12-31';
This query retrieves events occurring within the year 2023. 📅
Combining Techniques for Powerful Queries 💪
Combine wildcards and comparison operators for advanced filtering:
SELECT * FROM products
WHERE name
LIKE '%pro%' AND price >= 50; ```
This query finds products with "pro" in their name that cost 50 or more. Combining techniques
allows you to refine your search and get precisely the data you need. 🎯
By mastering these techniques, you'll be able to craft powerful queries that extract exactly what
you're looking for from your database. Keep practising, and soon you'll be an SQL ninja! 🥷💡
More Resources:
Wild Cards
Mastering the WHERE Clause: Filtering Data with
Precision 🎯
https://youtu.be/MARn_mssG4A
Welcome back, data explorers! In this lesson, we'll dive into the powerful WHERE clause,
your tool for filtering data with precision. Let's get started! 🚀
Understanding the WHERE Clause 🧠
The WHERE clause is your filter within a SELECT statement. It specifies conditions that
records must meet to be included in the results:
SELECT column1, column2
FROM table_name
WHERE condition;
The WHERE clause follows the FROM clause and sets criteria for data retrieval. It's like a sieve,
letting only the relevant data through.
Using Comparison Operators within WHERE 🔍
Comparison operators help filter data based on specific values:
SELECT * FROM transactions
WHERE amount > 100;
This query finds all transactions where the amount exceeds 100. 📈
Example: Filtering by date range
SELECT * FROM events
WHERE event_date
BETWEEN '2023-01-01' AND '2023-12-31';
This retrieves events occurring in 2023. 📅
Logical Operators for Advanced Filtering 🎯
The WHERE clause allows for more control with logical operators:
AND: Combine conditions (e.g., retrieve records meeting two criteria).
OR: Retrieve records meeting one or more conditions.
NOT: Exclude specific data points.
Example:
SELECT * FROM users
WHERE age > 18 AND country = 'US';
This finds adult users in the US. 🇺🇸
Combining Techniques for Granular Control 💪
Combine wildcards, comparison operators, and logical operators for precise filtering:
SELECT * FROM products
WHERE name LIKE '%pro%' AND price >= 50
OR category = 'electronics';
This query finds products with "pro" in their name that cost 50 or more or belong to
electronics. 🎧📱
More Resources:
WHERE CLAUSE
🔄 Organizing Data with the ORDER BY Clause 📊
https://youtu.be/R-5F3BF8IeY
Welcome back, data organizers! 📂 In this lesson, we'll explore the `ORDER BY` clause, your
tool for sorting data in a meaningful way. Let's dive in! 🚀
Understanding the ORDER BY Clause 🧠
The `ORDER BY` clause helps you sort data retrieved by a SELECT statement. It follows the
WHERE clause (if used) and specifies the column to sort by:
SELECT column1, column2 FROM table_name
WHERE condition
ORDER BY column_name ASC|DESC;
ASC: Sorts data in ascending order (e.g., A-Z, 0-9).
DESC: Sorts data in descending order (e.g., Z-A, 9-0).
Sorting by a Single Column 📋
To view records from most recent to least recent, sort by the "date" column in descending order:
SELECT * FROM records
ORDER BY date DESC;
This query retrieves all records and sorts them with the most recent date first. 📅
Sorting by Multiple Columns 📑
Sort by multiple columns for detailed organization:
SELECT * FROM records
ORDER BY category ASC, date DESC;
This sorts records alphabetically by category and then by date within each category. 📚
Key note🎯
The `ORDER BY` clause enhances clarity and usability by organizing data meaningfully. By
sorting effectively, you can uncover patterns and trends across categories and timeframes. Keep
practising to master data organization! 🌟
More Resources:
ORDER BY CLAUSE
🎬 Movie Database Challenge
Build and query a simple database about movies, actors.
Create two tables: Movies and Actors.
📝 Table Structures:
Actors Table 🌟
id: A unique ID for each actor 🎭
name: The actor’s name ✨
age: The actor's age 🎂
Movies Tables 🎥
id: A unique ID for each movie
title: The name of the movie 🎬
Year: The release year of the movie📅
-- 🎬 Create database for the movie system
Great! Let's build a Movie Database in MySQL with two related tables: Actors and Movies.
✅ SQL Code for Movie Database (MySQL)
CREATE DATABASE IF NOT EXISTS movieDB;
-- Use the newly created database
USE movieDB;
-- 🌟 Create the Actors table
CREATE TABLE IF NOT EXISTS Actors (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT
);
-- 🎥 Create the Movies table
CREATE TABLE IF NOT EXISTS Movies (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
year INT
);
Perfect! Let's expand your Movie Database Challenge with the following:
✅ Complete Plan
1. ✅ Create the Actors and Movies tables
2. ✅ Create a join table: MovieActors to show which actors acted in which movies
3. ✅ Insert sample data into all three tables
4. ✅ Write sample queries to retrieve useful data
📦 SQL Code (Full Setup + Sample Data + Queries)
sql
CopyEdit
-- Step 1: Create database
CREATE DATABASE IF NOT EXISTS movieDB;
USE movieDB;
-- Step 2: Create Actors table
CREATE TABLE IF NOT EXISTS Actors (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT
);
-- Step 3: Create Movies table
CREATE TABLE IF NOT EXISTS Movies (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
year INT
);
-- Step 4: Create MovieActors join table (many-to-many relationship)
CREATE TABLE IF NOT EXISTS MovieActors (
movie_id INT,
actor_id INT,
PRIMARY KEY (movie_id, actor_id),
FOREIGN KEY (movie_id) REFERENCES Movies(id),
FOREIGN KEY (actor_id) REFERENCES Actors(id)
);
-- Step 5: Insert sample data into Actors
INSERT INTO Actors (name, age) VALUES
('Leonardo DiCaprio', 49),
('Emma Stone', 35),
('Denzel Washington', 70),
('Zendaya', 28),
('Robert Downey Jr.', 60);
-- Step 6: Insert sample data into Movies
INSERT INTO Movies (title, year) VALUES
('Inception', 2010),
('La La Land', 2016),
('Training Day', 2001),
('Spider-Man: No Way Home', 2021),
('Iron Man', 2008);
-- Step 7: Insert data into MovieActors (mapping actors to movies)
INSERT INTO MovieActors (movie_id, actor_id) VALUES
(1, 1), -- Inception - Leonardo DiCaprio
(2, 2), -- La La Land - Emma Stone
(3, 3), -- Training Day - Denzel Washington
(4, 4), -- Spider-Man: No Way Home - Zendaya
(5, 5), -- Iron Man - Robert Downey Jr.
(4, 5); -- Spider-Man: No Way Home - Robert Downey Jr.
🔍 Sample Queries You Can Run
sql
CopyEdit
-- 1. Show all actors over the age of 40
SELECT * FROM Actors
WHERE age > 40;
-- 2. Show all movies released after 2010
SELECT * FROM Movies
WHERE year > 2010;
-- 3. List all actors with the movies they appeared in
SELECT
a.name AS actor_name,
m.title AS movie_title
FROM MovieActors ma
JOIN Actors a ON ma.actor_id = a.id
JOIN Movies m ON ma.movie_id = m.id
ORDER BY a.name;
-- 4. Show all movies that include Robert Downey Jr.
SELECT m.title
FROM Movies m
JOIN MovieActors ma ON m.id = ma.movie_id
JOIN Actors a ON a.id = ma.actor_id
WHERE a.name = 'Robert Downey Jr.';
WEEK2 QUIZ
Which wildcard is used to represent zero or more characters in an SQL query? _ # & %
The correct wildcard used to represent zero or more characters in an SQL query is:
✅%
📌 Explanation:
In SQL, particularly with the LIKE operator:
%represents zero, one, or many characters
Example:
SELECT * FROM Movies WHERE title LIKE 'Iron%';
This would match:
o Iron Man
o Iron Man 2
o Ironheart
o etc.
_ (underscore) represents exactly one character
Example:
SELECT * FROM Actors WHERE name LIKE '_ob';
Would match:
o Bob
o Rob
o Job
o but not Robert or Bobby
Let me know if you want a cheat sheet for SQL wildcards!
You said:
What is the purpose of the SELECT statement in SQL? a) To delete data from a table b) To
update data in a table c) To retrieve data from a table d) To insert data into a table
The correct answer is:
✅ c) To retrieve data from a table
📌 Explanation:
The SELECT statement in SQL is used to query and retrieve data from one or more tables in a
database.
Example:
SELECT name, age FROM Actors;
This retrieves the name and age columns from the Actors table.
❌ Other options:
a) To delete data from a table → Use DELETE
b) To update data in a table → Use UPDATE
d) To insert data into a table → Use INSERT
Let me know if you’d like a summary of all major SQL statements!
You said:
What is the purpose of the WHERE clause in an SQL statement? a) To create a new table b) To
filter records based on a condition c) To join two tables d) To delete a table
The correct answer is:
✅ b) To filter records based on a condition
📌 Explanation:
The WHERE clause is used in SQL to specify a condition when selecting, updating, or deleting
data. It filters rows to return only those that meet the condition.
Example:
SELECT * FROM Movies
WHERE year > 2010;
This returns only movies released after 2010.
❌ Other options:
a) To create a new table → Use CREATE TABLE
c) To join two tables → Use JOIN clause
d) To delete a table → Use DROP TABLE
Let me know if you'd like a mini quiz or cheat sheet to practice more!
You said:
Which of the following is a valid comparison operator in SQL? a) === b) >= c) := d) AND
The correct answer is:
✅ b) >=
📌 Explanation:
>= is a valid SQL comparison operator meaning “greater than or equal to.”
Example:
SELECT * FROM Actors WHERE age >= 40;
This will return all actors whose age is 40 or more.
❌ Other Options:
a) === → Not valid in SQL (used in JavaScript).
c) := → Not a comparison operator (used in PL/SQL for variable assignment).
d) AND → This is a logical operator, not a comparison operator.
Here's a clear and complete list of SQL Comparison and Logical Operators, along with
examples so you can master them quickly:
🧮 SQL Comparison Operators
Operator Meaning Example
= Equal to age = 30
!= or <> Not equal to name != 'Alice'
> Greater than price > 100
< Less than age < 18
>= Greater than or equal to age >= 21
<= Less than or equal to score <= 50
BETWEEN Within a range (inclusive) age BETWEEN 18 AND 30
LIKE Pattern matching name LIKE 'A%'
IN Match any in a list country IN ('USA', 'Canada')
IS NULL Checks for NULL values email IS NULL
🔗 SQL Logical Operators
Operator Meaning Example
AND Returns true if both conditions are true age > 18 AND city = 'Paris'
OR Returns true if either condition is true age < 18 OR age > 60
NOT Reverses the result of a condition NOT country = 'USA'
🔍 Example Query Using Both:
SELECT * FROM Actors
WHERE age >= 30 AND country IN ('USA', 'UK') AND name LIKE 'J%';
This query finds actors:
aged 30 or older
from USA or UK
whose names start with "J"
