SQL Exercices

This is a tutorial for SQL.

You can use the following online platform: DB-Fiddle.

The database is available at the following link.

Questions

  1. Write a command to get the customer_id, first_name and order_id from the tables customers and orders. It should be ordered and joined by customer_id.

  2. Change the JOIN with LEFT JOIN. What do you observe ?

  3. Choose the correct join clause to display all information from the orders and shippers tables where there is a match

  4. Display all records from the customers table along with the corresponding information, where available, from the orders table using a join clause

  5. The orders table contains a shipper_id column which identifies a supplier for each order.

This identifier corresponds to a supplier name accessible via the shippers table.

Modify the query in Question 1 (with the LEFT JOIN added) to display :

  • column 1, the customer ID, for all customers
  • column 2, the customer’s first name
  • column 3, the order ID (NULL for customers who haven’t ordered anything)
  • column 4, the supplier name (if documented and if an order has been placed)
  1. Consider the following query containing an “explicit” cross-join:
Query 
SELECT
  c.first_name AS customer,
  p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name

Write a query without CROSS JOIN but displaying the same result.

  1. In the customers table, each customer has loyalty points.

There are three categories of customer:

  • Silver for points < 2000,
  • Gold for 2000 <= points <= 3000,
  • Platinum for points > 3000.

Write a query displaying :

  • column 1, the customer ID
  • column 2, the customer’s first name
  • column 3, points
  • column 4, category

The rows should be ordered alphabetically by first name.

  1. What are the customer_id details of customers who have never placed an order?

  2. Ilene Dowson’s order has been delivered. Change its status in the ‘orders’ table, and check it.

  3. Which products are ordered most often? Rank them in descending order by displaying the product identifier, its name and the number of times it has been ordered