Basic SQL Practice: A Store

Table of contents

1. 1. Selecting from one table

   0/34

   Review the fundamentals of SQL. Practice using SELECT and WHERE to filter data.

   List of exercises Start now
   1. Introduction
      1. Introduction
      2. The store database
   2. SELECT
      1. Do you remember?
      2. All products
      3. Categories' names
      4. The customers' names and emails
      5. Purchases
      6. Purchases and items
   3. WHERE – numbers
      1. Do you remember?
      2. Customers with ID 4
      3. Employees who report to Aylett Sigismondo
      4. Products with the unit price greater than 3.5
      5. Products from category with ID other than 1
      6. Purchases that cost less than 14
   4. WHERE – text
      1. Do you remember?
      2. Description for alcohols
      3. Emails ending with .com
      4. Products with name starting with Wine
      5. Employees with last name ending with ixer
      6. Products with names containing a dash
   5. WHERE – NULL
      1. Do you remember?
      2. Categories without a description
      3. Customers with non-NULL companies
      4. Purchases without the shipping address
   6. WHERE – Boolean
      1. Do you remember?
      2. Discontinued products
      3. Continued products
      4. Products continued or without info
   7. WHERE – Logical Operators
      1. Do you remember?
      2. Customers from Oloo and Fliptune companies
      3. Products with specific conditions for category and price
      4. All products except some specific products
      5. Employee belonging to only one of the groups
      6. Purchases fulfilling three conditions

2. 2. JOIN

   0/30

   Practice using JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and non-equi JOIN. Check your skills of working with multiple tables.

   List of exercises Start now
   1. INNER JOIN
      1. Do you remember?
      2. Products and categories
      3. Customers and their purchases
      4. Employees taking care of the purchases
      5. Purchases details
   2. Multiple JOINs
      1. Do you remember?
      2. Customers and employees for each purchase
      3. Purchase, product and category
      4. Purchases and products
      5. Purchases and categories
   3. Other JOIN types
      1. Do you remember?
      2. All employees and matching purchases
      3. The customers' purchases
      4. The purchased products
      5. All products and their quantity in a purchase
   4. Multiple JOINs of different types
      1. Do you remember?
      2. Products and people who bought them
      3. Customers and the products they bought
      4. Categories and products in purchases
      5. Purchase data for all products
      6. All customers, employees and purchases
   5. Self JOIN
      1. Do you remember?
      2. Category and the parent category
      3. Employees and the employees they report to
      4. Products from two categories
   6. Non-equi JOIN
      1. Do you remember?
      2. Purchases from different cities
      3. Products from the same category
      4. Products purchased with different quantity
      5. The products with similar prices

3. 3. ORDER BY

   0/23

   Practice sorting the rows by single and multiple columns.

   List of exercises Start now
   1. ORDER BY one column
      1. Do you remember?
      2. Sorted categories
      3. Employees sorted by birth date
      4. The cheapest products
      5. Products from category with ID other than 1
   2. ORDER BY multiple columns
      1. Do you remember?
      2. Products sorted by two columns
      3. Many product items and low unit price
      4. Employees sorted by birth date and hire date
      5. Latest purchases in each city
      6. Purchases sorted by employees, customers, and total prices
   3. ORDER BY + WHERE
      1. Do you remember?
      2. Sorted categories with no description
      3. Employees reporting to Scholar Haslett
      4. Continued products sorted by category and price
      5. Customers with no company name, sorted by email
      6. Products sorted by the number of units
   4. ORDER BY + WHERE + JOIN
      1. Do you remember?
      2. Purchases made by Denny Guye
      3. Employees who took care of expensive purchases
      4. More about purchased items
      5. Products and their categories
      6. Discontinued products that were purchased

4. 4. GROUP BY

   0/36

   Test your knowledge of GROUP BY and HAVING clauses.

   List of exercises Start now
   1. Aggregate functions
      1. Do you remember?
      2. The number of categories
      3. The number of distinct cities
      4. The average product price
      5. Total price for all purchases
      6. Maximum and minimum unit price
   2. GROUP BY one column
      1. Do you remember?
      2. The average unit price for each category
      3. Number of purchases for each customer
      4. Total products quantity for each purchase
      5. Total number of units in stock for each category
      6. Number of purchases for each employee
   3. GROUP BY multiple columns
      1. Do you remember?
      2. Total purchases price for (customer, employee) group
      3. Minimum unit price for two-columns group
      4. Total purchases prices for each city and employee
      5. Purchases with products of given quantity
      6. Lowest and highest prices, and the number of purchases
   4. HAVING
      1. Do you remember?
      2. Customers with more than one purchase
      3. The number of products in category
      4. The number of purchases and customers for each city
      5. Minimum price for (customer, employee) group
      6. Total quantities
   5. GROUP BY exercises
      1. Do you remember?
      2. Count the statistics for purchases
      3. Filter the employees
      4. Filter the customers
      5. Number of products and total quantity for purchase
      6. Maximum number of products and maximum price for category
   6. GROUP BY with WHERE, HAVING and ORDER BY
      1. Do you remember?
      2. The number of discontinued products
      3. Number of employees reporting to an employee
      4. The number of customers in cities
      5. The number of purchases
      6. Purchases and quantity for each product

5. 5. Other SQL Features

   0/29

   Practice making calculations and dealing with NULLs and set operators. Verify your knowledge of subqueries. Practice simple and correlated subqueries.

   List of exercises Start now
   1. Calculations
      1. Do you remember?
      2. Total prices of all units in stock
      3. Difference between the maximum and minimum price
      4. The total price of product items
      5. Percentage of the product price in a purchase price
      6. The price for each item in the unit
   2. NULL
      1. Do you remember?
      2. Employees with an unknown hire date
      3. Employees and their purchases
      4. Customers with no company
      5. Average price
      6. Employees with unknown birth and hire dates
   3. Subqueries
      1. Do you remember?
      2. Do you remember? – Multiple subqueries
      3. Average unit price in category
      4. Difference between the maximum and minimum price
      5. Products in categories
      6. Purchases in categories
      7. Purchases with two conditions
   4. Correlated subqueries
      1. Do you remember?
      2. Percentage of money spent by the customer on the purchase
      3. The number of expensive products in each category
      4. Products in categories
      5. Discontinued, continued, and all products in a category
   5. Set operators
      1. Do you remember?
      2. Purchases in two prices groups
      3. Purchases for specific employees and customers
      4. Purchases with high quantity and specific products
      5. Products from two groups combined together

6. 6. Advanced queries

   0/17

   Write some more advanced queries which combine SELECT, WHERE, different kinds of JOINs, GROUP BY, HAVING, ORDER BY, set operators, and subqueries.

   List of exercises Start now
   1. Advanced queries
      1. Do you remember?
      2. Number of purchases for some employees
      3. Difference between the maximum and minimum price
      4. Products in categories
      5. Purchases in categories
      6. The number of wines and their prices
   2. Advanced queries – set 2
      1. Products from two groups
      2. Prices for some customers' purchases
      3. Purchases with high quantity
      4. Cities and purchases
      5. Products that aren't purchased
   3. Advanced queries – set 3
      1. The average prices in groups
      2. Employees' and customers' cities
      3. Purchases with a lower and a higher than average price
      4. Percentage of products in food categories
      5. Minimum and maximum unit prices
      6. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more