Thursday, May 28, 2026

Cohort 3 | Project CloudIgnite Topics: SQL NULL Values, SELECT Statements, Conditional Search Duration: ~3 hours

Key Takeaways

• NULL: absence of value; NULL = NULL is FALSE; any arithmetic with NULL returns NULL
• SELECT structure: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
• WHERE filtering: =, <>, >, <, AND, OR, IN, BETWEEN (inclusive), LIKE
• LIKE wildcards: % = zero or more chars; _ = exactly one char
• GROUP BY groups rows for aggregate calculations; HAVING filters groups after GROUP BY
• Aliases: AS gives temporary display names to columns
• Use IS NULL / IS NOT NULL — never use = NULL

⚠️ AWS CLF-C02 Relevance

Short answer: This lecture is not directly tested in the AWS Certified Cloud Practitioner (CLF-C02) exam.

The CLF-C02 exam focuses on AWS cloud concepts, services, pricing, and security — not SQL syntax. However, the foundational database concepts covered here provide useful background for understanding AWS database services such as:

• Amazon RDS (Relational Database Service) – uses SQL-based engines (MySQL, PostgreSQL, etc.)
• Amazon Aurora – MySQL/PostgreSQL-compatible relational database
• Amazon Redshift – data warehousing with SQL queries
• Amazon DynamoDB – NoSQL (contrast with relational/SQL databases covered here)

Understanding what a relational database is and how SQL works conceptually helps when the exam asks you to choose the right AWS database service for a scenario.

1. NULL Values in SQL

What is NULL?

• NULL = absence of any value — not zero, not empty string, just nothing
• Represents missing or unknown data
• A column can be of type INTEGER and still be NULL (instead of 0)

Key Characteristics

NOT NULL Constraint

-- When creating a table, NOT NULL forces a value to be provided
CREATE TABLE city (
    ID INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country_code TEXT NOT NULL
);

• NOT NULL means the field is required — any value is fine, but it cannot be left empty

Inserting NULL

INSERT INTO table_name (column1) VALUES (NULL);
-- Only works if column1 does NOT have a NOT NULL constraint

2. SQLite DB Browser – Quick Setup

1. Download DB Browser for SQLite (free, lightweight)
2. Open → New Database → choose a folder → save as anything.db
3. Paste SQL in the Execute SQL tab
4. Select specific lines then click Run to execute only that portion
5. Use -- (double dash) to comment out lines you don't want to run
6. To change theme: Preferences → Application Style → Dark/Light

Tip: .sql files can be renamed to .txt to open and read them in any text editor.

3. SELECT Statement

Basic Structure

SELECT column1, column2      -- or * for all columns
FROM table_name
WHERE condition              -- optional: filter rows
GROUP BY column              -- optional: group results
HAVING condition             -- optional: condition on groups
ORDER BY column ASC|DESC     -- optional: sort results
LIMIT n;                     -- optional: cap number of rows returned

• SELECT and FROM are the only required clauses
• All other clauses (WHERE, GROUP BY, HAVING, ORDER BY) are optional
• SQL keywords are not case-sensitive, but convention is UPPERCASE
• Column and table names are not case-sensitive
• String data values ARE case-sensitive

Selecting Columns

SELECT * FROM city;                        -- all columns
SELECT name FROM city;                     -- single column
SELECT id, name, country_code FROM city;   -- multiple columns

4. WHERE Clause (Filtering)

SELECT id, name, country_code
FROM city
WHERE country_code = 'BRA';

• Filters rows based on a condition
• String values must be in quotation marks

Comparison Operators

Arithmetic in SELECT

SELECT name, population / surface_area AS density FROM country;
SELECT name, life_expectancy + 5.5 AS adjusted_life FROM country;

Supported operators: +, -, *, /, % (modulus)

5. Logical Operators

AND / OR

-- Both conditions must be true
WHERE population > 50000000 AND population < 100000000;

-- Either condition can be true
WHERE continent = 'Asia' OR continent = 'Africa';

IN (shorthand for multiple OR)

-- Instead of multiple OR conditions:
WHERE continent IN ('Asia', 'Africa', 'Europe');

BETWEEN (range)

WHERE population BETWEEN 10000000 AND 50000000;
-- Equivalent to: WHERE population >= 10000000 AND population <= 50000000

LIKE (partial/pattern matching)

WHERE region LIKE 'S%';        -- starts with 'S'
WHERE region LIKE '%Asia';     -- ends with 'Asia'
WHERE region LIKE '%Asia%';    -- contains 'Asia'
WHERE code LIKE '_B_';         -- exactly 3 chars, middle char is 'B'

• % = wildcard for zero or more characters
• _ = wildcard for exactly one character

NOT (negate any condition)

WHERE district NOT IN ('Ontario', 'Alberta');
WHERE region NOT LIKE '%Asia';
WHERE population NOT BETWEEN 10000000 AND 50000000;

6. GROUP BY & HAVING

GROUP BY

Groups rows that share the same value in a column — used with aggregate functions.

SELECT continent, COUNT(*) 
FROM country
GROUP BY continent;

Difference from ORDER BY: GROUP BY lets you perform calculations per group (e.g. count, sum, average), not just sort.

HAVING

Filters groups after GROUP BY (like WHERE, but for groups).

SELECT continent, COUNT(*) 
FROM country
GROUP BY continent
HAVING COUNT(*) > 3;
-- Shows only continents with more than 3 countries

Clause Order (must follow this sequence)

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

7. ORDER BY (Sorting)

SELECT * FROM country ORDER BY population;           -- ascending (default)
SELECT * FROM country ORDER BY population DESC;      -- descending
SELECT * FROM country ORDER BY population ASC;       -- explicitly ascending
SELECT * FROM country ORDER BY name, continent;      -- sort by multiple columns

8. Column Alias (AS)

Gives a column a temporary display name in the result.

SELECT COUNT(*) AS count FROM country;
SELECT surface_area AS 'Surface Area' FROM country;
SELECT population / surface_area AS density FROM country;

9. SQL Comments

-- This is a single-line comment

SELECT name -- this is an inline comment
FROM city;

/* This is
   a multi-line comment */

• Shortcut in SQLite DB Browser: Ctrl + / (toggles single-line comment)

10. Useful MySQL / AWS Lab Commands

These were used in the AWS Lab (Lab 270) via SSM Session Manager on EC2:

SHOW DATABASES;               -- list all databases on the server
USE world;                    -- switch to a specific database
SHOW TABLES;                  -- list all tables in the current database
DESCRIBE city;                -- show schema/structure of a table
SHOW COLUMNS FROM city;       -- same as DESCRIBE

11. LIMIT

Caps the number of rows returned — useful when exploring large tables.

SELECT * FROM country ORDER BY population DESC LIMIT 10;
-- Returns top 10 most populated countries

12. Practice Resources

13. Quick-Reference Checkpoint Q&A

14. Next Session Preview

• Slide 25 onwards — continuing Conditional Search
• Lab – Practice with WHERE, NOT, LIKE, IN, BETWEEN
• Query2.sql pre-populated data available for local SQLite practice