Course outline
Module 1
Module 1: Getting Started with Databases
Getting Started with Databases
Knowledge check
Q1. What does SQL stand for?
- A. Structured Query Language ✓
- B. Simple Question Language
- C. Standard Quantitative Logic
- D. System Query Layer
Why: SQL stands for Structured Query Language, the standard language used to communicate with and manipulate relational databases.
Q2. Which term describes a table in a relational database?
- A. A collection of related data organized in rows and columns ✓
- B. A single value stored in memory
- C. A type of programming function
- D. A network connection between two servers
Why: A table is the basic structure of a relational database, organizing related data into rows (records) and columns (fields).
Q3. Which SQL statement is used to retrieve data from a database?
- A. INSERT
- B. UPDATE
- C. SELECT ✓
- D. DELETE
Why: The SELECT statement is used to query and retrieve data from one or more tables in a database.
Q4. What is a primary key?
- A. A password that protects the database
- B. A column that uniquely identifies each row in a table ✓
- C. The first column in any table
- D. A key used to encrypt data
Why: A primary key is a column (or set of columns) whose values uniquely identify each row, and it cannot contain NULL values.
Q5. Which of the following is a relational database management system?
- A. Microsoft Excel
- B. Notepad
- C. PostgreSQL ✓
- D. Google Chrome
Why: PostgreSQL is an open-source relational database management system that stores data in tables and uses SQL for querying.
Module 2
Module 2: Writing Basic SELECT Queries
Writing Basic SELECT Queries
Knowledge check
Q1. Which SQL statement retrieves all columns from a table named employees?
- A. GET * FROM employees
- B. SELECT * FROM employees ✓
- C. SHOW ALL employees
- D. FETCH employees.*
Why: The SELECT statement with the asterisk (*) wildcard retrieves all columns, and FROM specifies the table to query. The other options are not valid SQL syntax.
Q2. Which clause is used to filter rows returned by a SELECT query?
- A. FILTER
- B. HAVING
- C. WHERE ✓
- D. SORT BY
Why: The WHERE clause filters individual rows based on a specified condition before any grouping occurs. HAVING filters groups after aggregation, and FILTER and SORT BY are not standard clauses for this purpose.
Q3. What does the following query return? SELECT name FROM students WHERE age > 18;
- A. All columns for students older than 18
- B. Only the name column for students aged 18 or younger
- C. Only the name column for students older than 18 ✓
- D. The number of students older than 18
Why: The query selects only the name column (SELECT name) and filters rows where age is greater than 18 (WHERE age > 18). It does not return all columns or a count.
Q4. Which keyword is used to sort the results of a SELECT query in ascending or descending order?
- A. GROUP BY
- B. ORDER BY ✓
- C. SORT
- D. ARRANGE
Why: ORDER BY sorts the result set by one or more columns, using ASC for ascending (the default) or DESC for descending order. GROUP BY is used for aggregation, while SORT and ARRANGE are not valid SQL keywords.
Q5. Which query returns only unique values from the country column of a customers table?
- A. SELECT UNIQUE country FROM customers;
- B. SELECT DISTINCT country FROM customers; ✓
- C. SELECT country FROM customers WHERE unique = true;
- D. SELECT ONLY country FROM customers;
Why: The DISTINCT keyword removes duplicate values from the result set, so each country appears only once. UNIQUE is not the correct keyword in standard SQL for this purpose, and the other options are invalid.
Module 3
Module 3: Filtering and Sorting Data
Filtering and Sorting Data
Knowledge check
Q1. Which SQL clause is used to filter rows based on a condition?
- A. GROUP BY
- B. WHERE ✓
- C. ORDER BY
- D. HAVING
Why: The WHERE clause filters individual rows before any grouping or sorting takes place, keeping only the rows that satisfy the given condition.
Q2. Which query returns all employees whose salary is greater than 50000?
- A. SELECT * FROM employees WHERE salary > 50000; ✓
- B. SELECT * FROM employees HAVING salary > 50000;
- C. SELECT * FROM employees FILTER salary > 50000;
- D. SELECT * FROM employees ORDER BY salary > 50000;
Why: The WHERE clause with the comparison operator > correctly filters rows. HAVING is used with grouped data, FILTER is not a standard clause, and ORDER BY sorts rows rather than filtering them.
Q3. How do you sort query results in descending order by the column last_name?
- A. ORDER BY last_name DESC ✓
- B. SORT BY last_name DESC
- C. ORDER BY last_name ASC
- D. GROUP BY last_name DESC
Why: ORDER BY sorts results, and the DESC keyword sorts them from highest to lowest (Z to A for text). ASC sorts ascending, and SORT BY and GROUP BY are not valid for this purpose.
Q4. Which operator checks whether a value falls within a range of values, including the endpoints?
- A. LIKE
- B. IN
- C. BETWEEN ✓
- D. EXISTS
Why: BETWEEN checks whether a value is within a specified inclusive range, such as BETWEEN 10 AND 20. LIKE matches text patterns, IN checks membership in a list, and EXISTS tests for the presence of subquery results.
Q5. Which query returns products whose name starts with the letter 'C'?
- A. SELECT * FROM products WHERE name LIKE 'C%'; ✓
- B. SELECT * FROM products WHERE name = 'C%';
- C. SELECT * FROM products WHERE name LIKE '%C';
- D. SELECT * FROM products WHERE name IN 'C%';
Why: The LIKE operator with the pattern 'C%' matches any value beginning with C, since % is a wildcard for any sequence of characters. The equals operator does not interpret wildcards, '%C' matches values ending in C, and IN expects a list rather than a pattern.
Module 4
Module 4: Aggregating and Grouping Results
Aggregating and Grouping Results
Knowledge check
Q1. Which SQL function returns the number of rows in a result set?
- A. SUM()
- B. COUNT() ✓
- C. AVG()
- D. MAX()
Why: COUNT() returns the number of rows (or non-NULL values in a column) in a result set. SUM() adds values, AVG() computes the mean, and MAX() returns the largest value.
Q2. Which clause is used to group rows that share the same values so aggregate functions can be applied to each group?
- A. ORDER BY
- B. WHERE
- C. GROUP BY ✓
- D. HAVING
Why: GROUP BY collects rows with matching values into groups, allowing aggregate functions like COUNT() or SUM() to be calculated per group. WHERE filters individual rows before grouping, and HAVING filters the groups after aggregation.
Q3. What is the main difference between WHERE and HAVING?
- A. WHERE filters rows before grouping, while HAVING filters groups after aggregation ✓
- B. WHERE can use aggregate functions, while HAVING cannot
- C. HAVING filters rows before grouping, while WHERE filters groups after aggregation
- D. There is no difference; they can be used interchangeably
Why: WHERE is applied to individual rows before any grouping occurs, so it cannot reference aggregate results. HAVING is applied after GROUP BY and is used to filter groups based on aggregate values.
Q4. Given the table sales(region, amount), which query returns the total amount for each region?
- A. SELECT region, SUM(amount) FROM sales;
- B. SELECT region, SUM(amount) FROM sales GROUP BY region; ✓
- C. SELECT SUM(amount) FROM sales GROUP BY amount;
- D. SELECT region, amount FROM sales GROUP BY region;
Why: Grouping by region with SUM(amount) produces one row per region with its total. Option A without GROUP BY returns a single overall total, and option D selects a non-aggregated column without aggregation, which is invalid in standard SQL.
Q5. Which statement about NULL values in the COUNT() function is correct?
- A. COUNT(column) counts all rows, including those where the column is NULL
- B. COUNT(column) counts only the rows where the column value is not NULL ✓
- C. COUNT(column) returns an error if the column contains NULL values
- D. COUNT(column) treats NULL values as zero
Why: COUNT(column) ignores NULL values and counts only non-NULL entries. COUNT(*), by contrast, counts all rows regardless of NULLs.
Module 5
Module 5: Working with Multiple Tables
Working with Multiple Tables
Knowledge check
Q1. Which SQL clause is used to combine rows from two tables based on a related column?
- A. UNION
- B. JOIN ✓
- C. GROUP BY
- D. HAVING
Why: JOIN combines rows from two or more tables based on a related column between them. UNION stacks result sets vertically, GROUP BY aggregates rows, and HAVING filters grouped results.
Q2. What type of join returns only the rows where there is a match in both tables?
- A. LEFT JOIN
- B. RIGHT JOIN
- C. FULL OUTER JOIN
- D. INNER JOIN ✓
Why: An INNER JOIN returns only the rows that have matching values in both tables. LEFT and RIGHT JOINs keep all rows from one side, and FULL OUTER JOIN keeps all rows from both sides.
Q3. Given tables customers(id, name) and orders(id, customer_id, total), which query correctly lists each customer name with their order totals?
- A. SELECT name, total FROM customers, orders;
- B. SELECT c.name, o.total FROM customers c JOIN orders o ON c.id = o.customer_id; ✓
- C. SELECT c.name, o.total FROM customers c JOIN orders o WHERE c.id = o.id;
- D. SELECT name, total FROM customers JOIN orders;
Why: The correct query joins the tables using the ON clause to match customers.id with orders.customer_id. Option A creates a cartesian product with no join condition, and options C and D use incorrect or missing join conditions.
Q4. What does a LEFT JOIN return when a row in the left table has no matching row in the right table?
- A. The row is excluded from the result
- B. The row is returned with NULL values for the right table's columns ✓
- C. The row is returned with zeros for the right table's columns
- D. An error is raised
Why: A LEFT JOIN keeps every row from the left table. When no match exists in the right table, the columns from the right table are filled with NULL values.
Q5. What is a self join?
- A. A join that uses a table with itself, typically to compare rows within the same table ✓
- B. A join that is automatically generated by the database engine
- C. A join that combines three or more tables at once
- D. A join that removes duplicate rows from the result
Why: A self join links a table to itself by using two different aliases for the same table. It is commonly used for hierarchical data, such as matching employees to their managers stored in the same table.
Module 6
Module 6: Inserting, Updating, and Deleting Data
Inserting, Updating, and Deleting Data
Knowledge check
Q1. Which SQL statement is used to add a new row to a table?
- A. ADD ROW
- B. INSERT INTO ✓
- C. CREATE RECORD
- D. APPEND TO
Why: The INSERT INTO statement is the standard SQL command for adding new rows to a table. For example: INSERT INTO students (name, age) VALUES ('Ana', 20);
Q2. What does the UPDATE statement do?
- A. It creates a new table with updated data
- B. It modifies existing rows in a table ✓
- C. It removes all rows from a table
- D. It copies data from one table to another
Why: UPDATE changes the values of existing rows in a table. It is usually combined with a SET clause to specify the new values and a WHERE clause to choose which rows to change.
Q3. Which clause should be used to limit which rows a DELETE statement affects?
- A. HAVING
- B. GROUP BY
- C. WHERE ✓
- D. ORDER BY
Why: The WHERE clause filters rows so that only those matching the condition are deleted. Without a WHERE clause, a DELETE statement removes every row in the table.
Q4. What happens if you run UPDATE employees SET salary = 50000; without a WHERE clause?
- A. Only the first row is updated
- B. An error is raised and nothing changes
- C. Every row in the employees table gets salary set to 50000 ✓
- D. The table is deleted and recreated
Why: Omitting the WHERE clause causes the UPDATE to apply to all rows in the table. This is a common beginner mistake, so always double-check your WHERE condition before running an UPDATE or DELETE.
Q5. Which statement removes all rows from a table while keeping the table structure intact?
- A. DROP TABLE
- B. DELETE FROM table_name WHERE 1=0
- C. TRUNCATE TABLE ✓
- D. REMOVE ALL
Why: TRUNCATE TABLE quickly removes all rows while keeping the table's columns, constraints, and structure. DROP TABLE would remove the table itself, and REMOVE ALL is not valid SQL.
Module 7
Module 7: Creating Tables and Defining Schemas
Creating Tables and Defining Schemas
Knowledge check
Q1. Which SQL statement is used to create a new table in a database?
- A. MAKE TABLE
- B. CREATE TABLE ✓
- C. NEW TABLE
- D. ADD TABLE
Why: CREATE TABLE is the standard SQL statement used to define a new table, including its name, columns, and constraints. MAKE TABLE, NEW TABLE, and ADD TABLE are not valid SQL commands for this purpose.
Q2. Which data type is most appropriate for storing a whole number such as a customer's age?
- A. VARCHAR
- B. DATE
- C. INT ✓
- D. BOOLEAN
Why: INT (integer) stores whole numbers and is the right choice for values like age or quantity. VARCHAR stores text, DATE stores calendar dates, and BOOLEAN stores true/false values.
Q3. What does the PRIMARY KEY constraint ensure in a table?
- A. Each value in the column can be repeated multiple times
- B. The column can contain NULL values
- C. Each row can be uniquely identified, with no duplicate or NULL values in that column ✓
- D. The column values are automatically sorted in ascending order
Why: A PRIMARY KEY uniquely identifies each row in a table. It enforces both uniqueness and NOT NULL, so no two rows can share the same key value and the key cannot be empty.
Q4. Which statement correctly creates a table named students with an integer id column and a text name column?
- A. CREATE TABLE students (id INT, name VARCHAR(50)); ✓
- B. CREATE students TABLE (INT id, VARCHAR name);
- C. CREATE TABLE students [id INT, name VARCHAR(50)];
- D. NEW TABLE students (id INT name VARCHAR(50));
Why: The correct syntax is CREATE TABLE followed by the table name and a parenthesized list of column definitions, each written as column name then data type. Option A follows this structure, while the others use invalid ordering or brackets.
Q5. What is the purpose of the NOT NULL constraint when defining a column?
- A. It prevents the column from being deleted
- B. It forces the column to store only positive numbers
- C. It ensures that every row must have a value for that column ✓
- D. It makes the column automatically increment with each new row
Why: NOT NULL requires that a value be provided for the column in every row, so the database rejects any insert or update that leaves it empty. It does not restrict the sign of numbers or generate values automatically.