Wednesday, June 3, 2026

Cohort 3 | Project CloudIgnite Topics: SQL Advanced Queries, Amazon RDS Duration: ~3 hours

Key Takeaways

• ORDER BY: sort ascending (default) or descending; multiple columns (left = highest priority)
• GROUP BY + HAVING: group rows, then filter groups; WHERE filters rows, HAVING filters groups
• Set operations: UNION (dedupe), UNION ALL (keep dupes), INTERSECT (common), MINUS (A not in B)
• JOINs: INNER (matching only), LEFT (all left + matching right), RIGHT (all right + matching left), FULL (all from both)
• Window functions: OVER(PARTITION BY ... ORDER BY ...) — aggregate without collapsing rows; RANK() assigns rank per partition
• Amazon RDS: fully managed relational DB; supports MySQL, PostgreSQL, Aurora, Oracle, SQL Server
• RDS Lab 160: DB Security Group (port 3306 from web SG only), DB Subnet Group (private subnets), Multi-AZ for high availability
• Architecture: Internet → EC2 (public subnet) → RDS (private subnet) — standard 3-tier security pattern

Table of Contents

1. SQL Recap – ORDER BY, GROUP BY, HAVING
2. Set Operations – UNION, INTERSECT, MINUS
3. JOIN Operations
4. Window Functions – OVER & RANK
5. Amazon RDS – Lab 160
6. CLF-C02 Exam Relevance

1. SQL Recap

ORDER BY (Sorting)

• Sorts query results by one or more columns.
• Default order is ascending (ASC); use DESC for descending.
• Multiple columns: Priority goes left to right. The second column is only applied when the first column has equal values.
• You can reference columns by name or by column number (e.g., ORDER BY 2, 3).

-- Sort by surface area descending
SELECT name, continent, surfacearea
FROM country
ORDER BY surfacearea DESC;

-- Sort by multiple columns
SELECT name, continent, surfacearea
FROM country
ORDER BY continent, surfacearea DESC;

-- Sort by column number (equivalent to above)
SELECT name, continent, surfacearea
FROM country
ORDER BY 2, 3 DESC;

GROUP BY (Grouping)

• Groups rows that share the same value in a specified column.
• Typically used with aggregate functions like COUNT(), SUM(), AVG().

-- Count number of countries per continent
SELECT continent, COUNT(name) AS countries
FROM country
WHERE continent = 'South America' AND population > 12000000
   OR continent = 'Antarctica'
GROUP BY continent
ORDER BY 1, 2;

HAVING (Filtering Groups)

• Works like WHERE but is applied after GROUP BY.
• Use WHERE to filter individual rows before grouping.
• Use HAVING to filter aggregated/grouped results after grouping.

-- Show only continents with more than 5 countries
SELECT continent, COUNT(name) AS countries
FROM country
GROUP BY continent
HAVING COUNT(name) > 5;

Key Rule: You cannot use aggregate functions (e.g., COUNT) in a WHERE clause — use HAVING instead.

2. Set Operations

Combines results from two or more SELECT queries. Both queries must return the same number of columns with compatible data types.

Example with sets A = {1,2,3,4} and B = {3,4,5,6}:

-- Combine country names and city names (no duplicates)
SELECT name FROM country
UNION
SELECT name FROM city;

UNION vs JOIN:

• UNION combines query results (rows from multiple queries).
• JOIN combines tables (columns from multiple tables).

3. JOIN Operations

Used to retrieve data from multiple related tables using a common column (usually a foreign key).

Types of JOIN

Interview Tip: JOIN types are very common questions in Data Analyst/Data Science interviews.

Syntax Example

-- Join city and country tables using country code
SELECT ci.id       AS city_id,
       ci.name     AS city_name,
       co.name     AS country_name
FROM city   ci          -- 'ci' is a table alias
JOIN country co          -- 'co' is a table alias
  ON ci.countrycode = co.code;

• Table aliases (ci, co) act as shorthand names to avoid repetition, especially in self-joins.
• The ON clause defines the join condition (which columns to match).
• The joining column does not have to be a foreign key — any matching column works.

Visual Summary

INNER JOIN          LEFT JOIN           RIGHT JOIN          FULL JOIN
  A ∩ B              A + (A ∩ B)         B + (A ∩ B)          A ∪ B

4. Window Functions

OVER()

• Allows aggregate functions to operate across a set of rows related to the current row, without collapsing them into one result (unlike GROUP BY).
• Often used with PARTITION BY to divide data into groups, and ORDER BY to sort within each partition.
• Produces a running/cumulative total per row.

-- Cumulative population sum per region
SELECT name,
       region,
       population,
       SUM(population) OVER (
           PARTITION BY region
           ORDER BY population
       ) AS running_total
FROM world.country
WHERE region IN ('Australia and New Zealand', 'South America');

RANK()

• Assigns a rank number to each row within a partition, based on the ORDER BY column.
• Restarts ranking for each new partition.

-- Rank countries in each region by population (largest to smallest)
SELECT name,
       region,
       population,
       RANK() OVER (
           PARTITION BY region
           ORDER BY population DESC
       ) AS ranked
FROM world.country
ORDER BY region, ranked;

OVER vs GROUP BY:

• GROUP BY collapses rows into one result per group.
• OVER keeps all individual rows and adds a computed column alongside them.

5. Amazon RDS

Note: This section is directly relevant to the AWS CLF-C02 exam. See section below.

What is Amazon RDS?

• Relational Database Service (RDS) – a fully managed database service by AWS.
• Supports engines: MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, Amazon Aurora.
• AWS handles: server provisioning, software installation, patching, updates, and backups.

RDS vs Self-Managed DB on EC2

Key Concepts from Lab 160

Security Group for RDS

• Created a dedicated DB Security Group.
• Inbound rule: MySQL/Aurora, port 3306, source = Web Security Group.
• This means only traffic from the web tier (EC2) can reach the database — the database is not directly accessible from the internet.

DB Subnet Group

• Defines which subnets RDS instances can be deployed in.
• Configured to use two private subnets across two Availability Zones.
• Ensures the database is not publicly accessible.

Multi-AZ Deployment

• Deploys the primary DB in one Availability Zone and a standby replica in another.
• If the primary fails, it automatically fails over to the standby — high availability.
• Multi-AZ DB Cluster = 2 standby instances (higher redundancy, higher cost).

Launching an RDS Instance (Lab Steps Summary)

1. Create a DB Security Group (inbound: MySQL/Aurora from web SG).
2. Create a DB Subnet Group using two private subnets.
3. Launch RDS DB instance:
   • Engine: MySQL
   • Template: Dev/Test
   • Deployment: Multi-AZ (2 instances)
   • Instance class: db.t3.medium
   • Storage: 20 GB, gp3
   • VPC: Lab VPC (not default)
   • Public access: No
   • Security group: DB Security Group
   • Disable performance insights & enhanced monitoring (for lab)
   • Disable automated backups (for lab/testing)
4. Connect via endpoint through a web application hosted on EC2.

Architecture Pattern

Internet → EC2 (Public Subnet) → RDS (Private Subnet)

The database sits in a private subnet and is only reachable through the EC2 instance, not directly from the internet. This is a standard 3-tier architecture security best practice.

Automation Note

• All RDS configurations can be scripted using Terraform or Python (Boto3 SDK) for repeatable deployments.

CLF-C02 Exam Relevance

The following topics from today's lecture map directly to the AWS Certified Cloud Practitioner (CLF-C02) exam objectives:

Domain 3: Cloud Technology & Services

Domain 2: Security & Compliance

Domain 1: Cloud Concepts

Study Tip: For CLF-C02, you don't need to know how to configure RDS step-by-step, but you should be able to:

• Describe what RDS is and why it's used.
• Differentiate RDS from running a database on EC2.
• Understand Multi-AZ and its purpose (high availability, not performance).
• Know that RDS supports MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, and Aurora.

Upcoming Sessions