Context<\/h3>\n\n\n\n
This worksheet accompanies the SQL warm-up dataset (classic retail schema) and is intended to support the expression of OLAP queries in a separate exercise.<\/p>\n\n\n\n
Objective<\/strong>:<\/em> Practise basic SQL syntax, filtering, joins, grouping, and subqueries.<\/p>\n\n\n\n SQL Memento<\/em>: cheat sheets<\/a> There are two alternatives to have access to externalised PostgreSQL SQL environments with no installation burden required:<\/p>\n\n\n\n 1) Peek at the first five rows of the customers table.<\/p>\n\n\n\n 2) Show each customer\u2019s full name and the year they joined (created_at).<\/p>\n\n\n\n 3) Find all customers from France (country=’FR’).<\/p>\n\n\n\n 4) List the newest customers, skipping the most recent one and taking the next two.<\/p>\n\n\n\n 5) Find all customer emails containing ‘shop’.<\/p>\n\n\n\n 6) List European customers (FR, ES, PT, DE) ordered alphabetically by country.<\/p>\n\n\n\n 7) Show all employees with their titles using COALESCE if missing[2]<\/sup><\/a>.<\/p>\n\n\n\n 8) Count how many orders exist and how many distinct ship countries appear.<\/p>\n\n\n\n 9) Count customers per country.<\/p>\n\n\n\n 10) Show only countries having at least two customers (HAVING).<\/p>\n\n\n\n 11) Display all orders with the customer\u2019s full name and ship country.<\/p>\n\n\n\n 12) List customers who have never placed an order.<\/p>\n\n\n\n 13) Compute the line total for each order item including discount.<\/p>\n\n\n\n 14) Show each order with its total revenue (sum of order items).<\/p>\n\n\n\n 15) List products that were never ordered.<\/p>\n\n\n\n 16) Find customers who have placed at least one order (EXISTS).<\/p>\n\n\n\n 17) List orders placed in the last 30 days relative to now().<\/p>\n\n\n\n 18) Compare UNION and UNION ALL results for customer and order countries.<\/p>\n\n\n\n 19) Find all products with names containing ‘t-shirt’ (case-insensitive).<\/p>\n\n\n\n 20) Show all products and mark whether each is discontinued.<\/p>\n\n\n\n Tip: Run your queries step-by-step and verify using COUNT(*) to estimate result sizes.<\/em><\/em><\/p>\n\n\n\n [1]<\/sup><\/a> Legend: PK = Primary Key, FK = Foreign Key, UNIQUE = Unique constraint.<\/p>\n\n\n\n [2]<\/sup><\/a> Aurora SQL Warm-up \u2014 Student Worksheet Individual work Context This worksheet accompanies the SQL warm-up dataset (classic retail schema) and is intended to support the expression of OLAP queries in a separate exercise. Objective: Practise basic SQL syntax, filtering, joins, grouping, … Continue reading
Dataset<\/em>: customers, employees, suppliers, categories, products, orders, order_items[1]<\/sup><\/a>.<\/p>\n\n\n\n![\"\"](\"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-content\/uploads\/sites\/64\/2025\/11\/erd-1024x601.jpg\")
<\/figure>\n\n\n\nToDo \u2013 ToTest<\/h3>\n\n\n\n
Execution Environment & Setup<\/em><\/strong><\/h4>\n\n\n\n
\n
\n
\n
<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n\n
Write and test the following queries<\/h3>\n\n\n\n
\n\n\n\nCOALESCE<\/strong><\/code> is a function that returns the first non-NULL value<\/strong> in a list of expressions.
SELECT COALESCE(NULL, NULL, ‘Hello’, ‘World’); \uf0e0 \u2018Hello\u2019<\/p>\n","protected":false},"excerpt":{"rendered":"