Practice & Assessment
Test your understanding of Foundations: How Databases Think
Multiple Choice Questions
5You run SELECT name FROM users WHERE id = NULL. The users table has a row with id = NULL. How many rows are returned?
Which data type is most appropriate for storing a product price like 19.99?
You need the 3rd and 4th most expensive products. Which query is correct?
What does the AS keyword do in SELECT price AS unit_price FROM products?
A products table has 1000 rows. SELECT * FROM products ORDER BY name ASC LIMIT 10 OFFSET 990 returns how many rows?
Coding Challenges
1Product Catalog Query Builder
Given a products table with columns id (INTEGER), name (VARCHAR), category (VARCHAR), price (NUMERIC), in_stock (BOOLEAN), and created_at (TIMESTAMP), write three queries: (1) Return all in-stock products priced between 10 and 100, sorted by price ascending, limited to 5 results. (2) Return the 5 most recently added out-of-stock products showing only name and created_at, with created_at aliased as added_on. (3) Return all products where name starts with 'Pro' or category is 'Electronics', sorted by category then name. Expected output: valid result sets with correct column names, correct row counts based on sample data provided. Time estimate: 20 minutes.
Mini Project
E-commerce Product Explorer
Create a products table using appropriate data types for: id (auto-increment), sku (unique, max 50 chars), name, category, price (exact decimal), stock_quantity (integer), is_active (boolean), created_at (timestamp with timezone). Insert at least 15 rows across 3 categories with varied prices and stock levels. Write five queries: (1) All active products in stock, sorted by price descending. (2) Top 3 cheapest products per keyword search using LIKE on name. (3) Products added in the last 30 days using timestamp comparison. (4) All products with NULL discount column using IS NULL. (5) Paginated product list — page 2, 5 items per page, sorted alphabetically. Document each query with a comment explaining what it returns.