Title: SQL Basic Query

Date: 2025-10-22 12:22

Status: Child

Tags: SQL programming Data Analysis Data Analyst

SQL Training Session Notes

Introduction

SQL training session.
SQL relevance in banking, e-commerce, and retail.
Introductions, SQL fundamentals overview, practical exercises.
Sample database: “DVD Rental Company” (“Maven movies”).
Topics: Database setup, basic SQL queries, filtering.
Focus: Hands-on learning.

SQL Fundamentals

Definition and Importance

SQL, or Structured Query Language, is a programming language used for managing and manipulating relational databases. Originating in the 1960s or 1970s, SQL remains highly relevant across various industries due to its ability to handle large datasets efficiently. It is particularly valuable in:

Banking: Managing financial data.
E-commerce: Handling customer and transaction data.
Retail: Managing inventory and sales data.

Why SQL Matters in Business Analytics

In business analytics, SQL is critical for handling large datasets, such as:

Customer Data: Names, addresses, and transaction histories.
Business Data: Sales, staff, and rental information.
Inventory Data: Stock levels, product categories, and descriptions.

SQL enables analysts to extract insights from cloud-based servers, where massive datasets (e.g., 200–300 million BDT in revenue) are stored. This is particularly useful for:

Querying specific data points.
Filtering and aggregating data for decision-making.
Connecting data across multiple tables.

Hands-On Practice: DVD Rental Company Dataset

The session uses a sample dataset from a DVD rental company, provided by Maven Analytics, to demonstrate SQL concepts. The dataset includes three main types of data:

Customer Data: Customer IDs, names, addresses, and contact details.
Business Data: Staff, rental, sales, and payment information.
Inventory Data: Film titles, categories, descriptions, and stock levels.

Database Structure

The dataset comprises 16 tables, each with specific columns and relationships. Key tables include:

Rental Table:
- Contains rental_id (primary key),
- customer_id,
- staff_id,
- inventory_id, and
- rental_date.
Customer Table:
- Includes customer_id (primary key),
- first_name,
- last_name, and
- address.
Payment Table:
- Contains payment_id (primary key),
- customer_id,
- rental_id,
- amount, and
- payment_date.
Film Table: Includes film_id (primary key), title, description, and rating.

Primary and Foreign Keys

Primary Key: A unique identifier for each row in a table (e.g., rental_id in the rental table). It cannot contain duplicates or null values.
Foreign Key: A column that links to a primary key in another table.
- References the primary key of another table
- Can contain duplicate values
- Can be NULL
- Used to establish relationships between tables
- Example: In the rental table, customer_id, staff_id, and inventory_id are foreign keys

Relational Tables

Tables connected through primary and foreign key relationships
Allows data to be joined across multiple tables
Similar to lookup values in Excel
Enables complex queries across related data

MySQL Installation and Setup

Initial Setup Process

Install MySQL and create a server
Set a password (write it down - cannot be recovered if forgotten)
Login requires password every time

First-Time Database Setup

Open MySQL
Go to File → Open SQL Script
Select the database file (PFMavenmovies - already shared)
Click the thunder button (⚡) to execute
Database loads onto your local server

After the initial setup:

Open MySQL
Click on the existing tab
Click the thunder button (⚡)
Data becomes available

Important: This is a one-time installation. After setup, you only need to click the thunder button when logging in.

Verification

To verify successful installation:

Tables should appear in the left panel
If tables don’t show, use the refresh button
Action output shows at the bottom of the screen
Green checkmark (✓) indicates success
Red cross (✗) indicates error

SQL Query Basics

SQL queries follow a standard structure with mandatory and optional clauses:

Mandatory Clauses:
- SELECT: Specifies the columns to retrieve.
- FROM: Indicates the table to query.
Optional Clauses:
- WHERE: Filters data based on conditions.
- GROUP BY: Groups data for aggregation.
- HAVING: Filters grouped data.
- ORDER BY: Sorts the results.

Selecting All Data from a Table

SELECT * FROM rental;

Explanation:

SELECT *: Retrieves all columns
FROM rental: Specifies the rental table
Returns all rows and columns from the rental table

Result: rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update

Selecting Specific Columns

SELECT title, description FROM film;

Explanation:

Returns only the title and description columns
Comma separates multiple column names
No comma after the last column name

Adding More Columns:

SELECT title, description, rating FROM film;

Important Notes on Column Selection

Comma Rules:

Use commas to separate column names
Do NOT use comma after the last column
Error occurs if comma rules are violated

Example of Single Column:

SELECT title FROM film;

No comma needed when selecting only one column.

Code Formatting Best Practices

Vertical Format (Recommended):

SELECT 
    title,
    description,
    rating
FROM film;

Horizontal Format (Also Valid):

SELECT title, description, rating FROM film;

Why Vertical?

Easier to read
Easier to debug
Better for complex queries
Standard practice for larger queries

Case Sensitivity

Table Names: Case-sensitive

SELECT * FROM rental;  -- ✓ Correct
SELECT * FROM Rental;  -- ✗ Error (if table name is 'rental')
SELECT * FROM RENTAL;  -- ✗ Error (if table name is 'rental')

Column Names: Not case-sensitive

SELECT Title FROM film;  -- Works
SELECT title FROM film;  -- Works
SELECT TITLE FROM film;  -- Works

DISTINCT Keyword

Finding Unique Values

SELECT DISTINCT rating FROM film;

Purpose: Returns only unique values from a column, removing duplicates.

Result: Shows 5 unique ratings (PG, G, PG-13, R, NC-17)

Another Example:

SELECT DISTINCT rental_duration FROM film;

Returns unique rental duration values from the film table.

WHERE Clause - Filtering Data

The Concept of Filtering

Think of filtering like Netflix:

You open Netflix (SELECT * FROM netflix)
You choose a genre filter (Action, Sci-Fi, Romance)
This is WHERE in SQL

SELECT * FROM netflix WHERE genre = 'Action';

Basic WHERE Syntax

SELECT * FROM payment;

Returns all payment data.

SELECT * FROM payment WHERE amount = 0.99;

Returns only transactions where amount equals $0.99.

WHERE Operators

Comparison Operators

= : Equal to
!= or <> : Not equal to
> : Greater than
< : Less than
>= : Greater than or equal to
<= : Less than or equal to
BETWEEN : Between a range
LIKE : Pattern matching
IN : Matches any value in a list

Examples with Different Operators

Equal To

SELECT * FROM payment WHERE amount = 0.99;

Not Equal To

SELECT * FROM payment WHERE amount != 0.99;

Greater Than

SELECT * FROM payment WHERE amount > 5;

Between (Three Equivalent Ways)

Method 1:

SELECT * FROM payment WHERE customer_id >= 1 AND customer_id <= 100;

Method 2:

SELECT * FROM payment WHERE customer_id BETWEEN 1 AND 100;

Method 3:

SELECT * FROM payment WHERE customer_id < 101;

All three return the first 100 customer IDs.

Working with Dates and Text

Important: Text and dates must be enclosed in single quotes.

SELECT * FROM payment WHERE payment_date = '2006-01-01';

Date Comparison:

SELECT * FROM payment 
WHERE payment_date >= '2006-01-01';

AND Operator - Multiple Conditions

When to Use AND

Use AND when all conditions must be satisfied.

Real-World Example: Bike Purchase

You want to buy a bike with specific requirements:

Model = Jigsaw
Color = Blue

SELECT * FROM mamoon_motors 
WHERE bike_model = 'Jigsaw' 
AND color = 'Blue';

Result: Only returns bikes that are BOTH Jigsaw model AND blue color.

Multiple AND Conditions

SELECT * FROM payment
WHERE amount = 0.99
AND payment_date >= '2006-01-01'
AND customer_id <= 100;

Explanation: Returns transactions that meet ALL three conditions:

Amount is exactly $0.99
Payment date is on or after January 1, 2006
Customer ID is 100 or less

AND Behavior

All conditions must match
If any condition fails, the row is excluded
Narrows down results (more conditions = fewer results)

OR Operator - Alternative Conditions

When to Use OR

Use OR when any one condition can be satisfied.

Real-World Example: Bike Purchase (Flexible)

You want:

Jigsaw model (specifically) OR
Any 150cc bike from any brand

SELECT * FROM mamoon_motors
WHERE bike_model = 'Jigsaw'
OR cc = 150;

Result: Returns bikes that are EITHER Jigsaw OR 150cc (or both).

OR Examples

Simple OR

SELECT * FROM payment
WHERE customer_id = 5
OR customer_id = 100
OR customer_id = 200;

Returns all transactions for these three specific customers.

OR with Other Conditions

SELECT * FROM payment
WHERE amount > 5
OR customer_id = 5
OR customer_id = 100
OR customer_id = 200;

Result:

All transactions over $5 (for any customer)
PLUS all transactions for customers 5, 100, and 200 (any amount)

The Key Difference: AND vs OR

Using AND

SELECT * FROM payment
WHERE amount > 5
AND customer_id = 5
AND customer_id = 100
AND customer_id = 200;

Returns: Nothing (impossible for customer_id to be 5 AND 100 AND 200 simultaneously)

Using OR

SELECT * FROM payment
WHERE amount > 5
OR customer_id = 5
OR customer_id = 100
OR customer_id = 200;

Returns:

All $5+ transactions
All transactions for the three specified customers

OR Behavior

Any condition can match
If at least one condition is true, the row is included
Broadens results (more conditions = more results)

Practical Examples and Best Practices

Example 1: Manager Request

Scenario: Manager wants $0.99 transactions after a specific date.

SELECT 
    customer_id,
    rental_id,
    amount
FROM payment
WHERE amount = 0.99
AND payment_date >= '2006-01-01';

Example 2: Multiple Conditions

Scenario: $0.99 transactions, after specific date, for first 100 customers.

SELECT 
    customer_id,
    rental_id,
    amount
FROM payment
WHERE amount = 0.99
AND payment_date >= '2006-01-01'
AND customer_id <= 100;

Example 3: IN Operator (Alternative to Multiple OR)

Instead of:

SELECT * FROM payment
WHERE customer_id = 5
OR customer_id = 100
OR customer_id = 200;

Use:

SELECT * FROM payment
WHERE customer_id IN (5, 100, 200);

Both produce the same result, but IN is cleaner.

Common Mistakes and Troubleshooting

Error 1146: Table Not Found

Cause: Incorrect table name (case-sensitive)
Solution: Verify exact table name spelling and case

Missing Commas

Always check comma placement between column names
Last column should have NO comma

Quote Usage

Text and dates MUST be in single quotes
Numbers do NOT use quotes

-- Correct
WHERE amount = 5           -- Number
WHERE city = 'Dhaka'       -- Text
WHERE date = '2006-01-01'  -- Date
 
-- Incorrect
WHERE amount = '5'         -- Don't quote numbers
WHERE city = Dhaka         -- Must quote text

Future Topics

The session briefly mentioned upcoming topics, including:

GROUP BY: For aggregating data (e.g., summing transactions by customer).
HAVING: For filtering grouped data.
ORDER BY: For sorting results.
JOINS: For combining data from multiple tables.
Subqueries: For nested queries.

These will be covered in subsequent classes to build on the foundational knowledge.

References

Datanalytics

Explorer

8.1-SQL Basic Query