Friday, May 29, 2026

Cohort 3 | Project CloudIgnite Topics: SQL Conditional Search, Operator Precedence, Aliases, NULL, Aggregate & String Functions Duration: ~3 hours

☁️ AWS CLF-C02 Relevance Note The content in this session is not directly tested on the AWS Certified Cloud Practitioner (CLF-C02) exam. The CLF-C02 focuses on AWS cloud concepts, services, security, architecture, pricing, and support — not SQL syntax. However, foundational SQL knowledge is relevant to AWS services you will encounter later, such as Amazon RDS (Relational Database Service), Amazon Aurora, and Amazon Redshift. Understanding how relational databases work supports exam topics around database service selection and use cases.

Key Takeaways

• Operator precedence: Parentheses > Arithmetic > Comparison > NOT > AND > OR
• AND has higher precedence than OR — always use parentheses when mixing AND/OR
• Aggregate functions: SUM(), AVG(), COUNT(), MAX(), MIN()
• DISTINCT removes duplicate values; COUNT(DISTINCT col) is MySQL-only (not SQLite)
• String functions: LOWER, UPPER, TRIM, CHAR_LENGTH, SUBSTRING_INDEX
• Date functions: CURRENT_DATE, DATE_ADD (MySQL only, not SQLite)
• Case sensitivity: keywords and column names are case-insensitive; string values are case-sensitive
• Labs 271–272: Aggregate queries, string functions, customer table filtering

Table of Contents

1. Conditional Search — NOT IN & NOT EQUAL
2. Operator Precedence
3. Using Parentheses to Control Precedence
4. Alias (AS)
5. NULL Values
6. Built-in Functions
7. Aggregate Functions
8. DISTINCT
9. String Functions
10. Case Sensitivity in MySQL
11. Lab Practice Summary
12. Key Takeaways & Tips

1. Conditional Search — NOT IN & NOT EQUAL

Example:

SELECT * FROM city
WHERE district NOT IN ('Ontario', 'Alberta');

Returns all rows except those where district is Ontario or Alberta.

2. Operator Precedence

SQL follows a fixed order when evaluating expressions — same as most programming languages.

Critical Rule: AND has higher precedence than OR

FALSE OR TRUE AND FALSE
→ Evaluates as: FALSE OR (TRUE AND FALSE)
→ FALSE OR FALSE
→ FALSE

If you want OR to evaluate first, use parentheses:

(FALSE OR TRUE) AND FALSE
→ TRUE AND FALSE
→ FALSE

3. Using Parentheses to Control Precedence

When mixing AND and OR, parentheses make your intent explicit and avoid bugs.

Without parentheses (AND takes priority):

SELECT name, district, population
FROM city
WHERE CountryCode = 'PAK' AND district = 'Punjab'
OR district = 'Sindh';
-- Evaluates: (CountryCode='PAK' AND district='Punjab') OR district='Sindh'

With parentheses (OR takes priority):

SELECT name, district, population
FROM city
WHERE CountryCode = 'PAK'
AND (district = 'Punjab' OR district = 'Sindh');
-- Returns PAK cities in EITHER Punjab OR Sindh

💡 Tip: When in doubt, always use parentheses. Extra parentheses do no harm and make queries easier to read.

4. Alias (AS)

Aliases give a temporary display name to a column or table in query results.

SELECT life_expectancy + 5.5 AS new_life_expectancy
FROM country;

• Without alias: column header would show life_expectancy + 5.5
• With alias: column header shows new_life_expectancy

To include spaces in an alias name, wrap it in quotes:

SELECT population AS 'Total Population'
FROM country;

💡 Why use aliases? Readability and presentation — especially important when sharing results with non-technical stakeholders.

5. NULL Values

Correct syntax:

-- Find records where life_expectancy is missing
SELECT * FROM country
WHERE life_expectancy IS NULL;

-- Exclude records with no life_expectancy data
SELECT * FROM country
WHERE life_expectancy IS NOT NULL;

Wrong syntax (will not work):

WHERE life_expectancy = NULL    -- ❌ does not work
WHERE life_expectancy <> NULL   -- ❌ does not work

🔑 Why does NULL matter? If a column with NULLs is used in a WHERE clause, those rows are silently excluded. This can cause incorrect query results, especially with arithmetic and comparison operators.

6. Built-in Functions

SQL provides built-in functions similar to those in Python or other languages.

Date Functions (MySQL)

SELECT CURRENT_DATE;                          -- Returns today's date: YYYY-MM-DD
SELECT DATE_ADD('2026-05-29', INTERVAL 3 DAY); -- Adds 3 days to a date

⚠️ CURRENT_DATE and DATE_ADD are not available in SQLite3. Use MySQL for these.

String Functions

SUBSTRING_INDEX examples:

-- Return first word before space
SELECT SUBSTRING_INDEX(region, ' ', 1) FROM country;
-- 'Eastern Africa' → 'Eastern'

-- Return last word after space
SELECT SUBSTRING_INDEX(region, ' ', -1) FROM country;
-- 'Eastern Africa' → 'Africa'

-- Return first two words
SELECT SUBSTRING_INDEX(region, ' ', 2) FROM country;
-- 'Middle East South' → 'Middle East'

INSERT example:

SELECT INSERT('положение', 1, 2, 'MANI');
-- Replaces first 2 characters with 'MANI' → 'manipulation'

7. Aggregate Functions

Aggregate functions compute a single result from multiple rows.

Examples:

-- Total world population
SELECT SUM(population) AS total_population FROM world.country;

-- Average life expectancy
SELECT AVG(life_expectancy) AS avg_life_expectancy FROM world.country;

-- Population stats in one query
SELECT
  SUM(population)   AS total_population,
  AVG(population)   AS average_population,
  MAX(population)   AS max_population,
  MIN(population)   AS min_population,
  COUNT(population) AS total_countries
FROM world.country;

-- Total population for European regions (using LIKE)
SELECT SUM(population) AS europe_total_population
FROM world.country
WHERE region LIKE '%Europe%';

8. DISTINCT

DISTINCT removes duplicate values from results.

-- See all unique continents (no duplicates)
SELECT DISTINCT continent FROM country;

-- Count how many unique continents exist
SELECT COUNT(DISTINCT continent) FROM country;

⚠️ COUNT(DISTINCT col) is not available in SQLite3 — use MySQL.

When DISTINCT is applied to multiple columns, a row is only considered a duplicate if all specified columns match.

9. Case Sensitivity in MySQL

-- 'Europe' and 'europe' return DIFFERENT results for values
WHERE region LIKE '%Europe%'   -- works
WHERE region LIKE '%europe%'   -- returns nothing (case sensitive)

-- Workaround: force case before comparing
WHERE LOWER(region) LIKE '%europe%'
WHERE UPPER(region) LIKE '%EUROPE%'

10. Lab Practice Summary

Lab 271 — WHERE, LIKE, Conditional Filters

Key queries practiced:

-- BETWEEN (equivalent to >= AND <=)
SELECT * FROM world.country
WHERE population BETWEEN 50000000 AND 100000000;

-- Same as above using AND
SELECT * FROM world.country
WHERE population >= 50000000 AND population <= 100000000;

-- Total population using SUM with alias
SELECT SUM(population) AS total_population FROM world.country;

-- Using LIKE with wildcard
SELECT SUM(population) AS europe_total_population
FROM world.country
WHERE region LIKE '%Europe%';

Lab 272 — Aggregate Functions, DISTINCT, String Functions

Key queries practiced:

-- Full aggregate stats
SELECT
  SUM(population)   AS total_population,
  MAX(population)   AS max_population,
  AVG(population)   AS average_population,
  MIN(population)   AS min_population,
  COUNT(*)          AS total_countries
FROM world.country;

-- DISTINCT regions
SELECT DISTINCT region FROM world.country;

-- Split region column using SUBSTRING_INDEX
SELECT
  SUBSTRING_INDEX(region, '/', 1)  AS reason_name_1,
  SUBSTRING_INDEX(region, '/', -1) AS reason_name_2
FROM world.country
WHERE region = 'Micronesia/Caribbean';

-- TRIM to clean whitespace, then filter short names
SELECT region FROM world.country
WHERE CHAR_LENGTH(TRIM(region)) < 10;

Customer Table Practice (Custom Dataset)

-- Find customers in a specific country
SELECT first_name, last_name, email
FROM customer
WHERE country_code = 'RUS';

-- Find Yahoo email users
SELECT first_name, last_name, email
FROM customer
WHERE email_address LIKE '%@yahoo.com';

-- Find non-Yahoo users with a valid email
SELECT first_name, last_name, email
FROM customer
WHERE email_address IS NOT NULL
AND email_address NOT LIKE '%@yahoo.com';

11. Important Reminders from Instructor

• If labs or cases show "outstanding" for the last 5–6 items, ignore it — those entries haven't been manually updated yet. Check AWS portal directly to confirm your actual status.
• If a lab status says outstanding but AWS portal shows completed, contact the instructor.
• For attendance/trust issues, contact Riha.
• Next session: Wednesday (Monday and Tuesday are public holidays).
• Saturday 9am–6pm: Open session for SQL lab catch-up and Python questions.
• Python Challenge 3 will be prepared for Saturday.
• Next lecture will cover more SQL topics — instructor will demonstrate Slide 33 queries and conditional statements (IF/CASE) during the next lab.

12. Key Takeaways & Tips

• Always use parentheses when mixing AND and OR — it prevents logic errors and improves readability.
• AND has higher precedence than OR — this is consistent across SQL and most programming languages.
• NULL is not a value — never use = NULL; always use IS NULL or IS NOT NULL.
• Any arithmetic with NULL returns NULL — be careful in calculated columns.
• String values are case-sensitive in MySQL; keywords and column names are not.
• BETWEEN is inclusive on both ends (equivalent to >= AND <=).
• Use aliases to make output readable, especially for aggregate function results.
• LIKE with % is required when using wildcards — you cannot use % with =.
• SQLite3 does not support all MySQL functions — CURRENT_DATE, DATE_ADD, COUNT(DISTINCT) require MySQL.