SELECT
WHERE
CustomerID
Orders
Simple Order Query
Given a database with a table named 'Orders' containing columns 'OrderID', 'CustomerID', 'OrderDate', and 'TotalAmount', write a SQL query to retrieve all orders placed by customer with CustomerID 'ALFKI'.
SELECT
WHERE
HireDate
Employees
Date Comparison
Employees Hired After Date
You are a SQL expert. Based on the 'Employees' table with columns 'EmployeeID', 'FirstName', 'LastName', 'HireDate', and 'Salary', generate a SQL query to find all employees hired after January 1, 2020.
SELECT
WHERE
UnitsInStock
Products
Products with Low Stock
Given a table named 'Products' with the columns 'ProductID', 'ProductName', 'UnitsInStock', 'UnitPrice', and 'SupplierID', create a SQL query to list all products where the 'UnitsInStock' is less than 10.
AVG
Orders
OrderTotal
Aggregate Function
Average Order Value
You are provided with an 'Orders' table containing 'OrderID', 'CustomerID', and 'OrderTotal' columns. Write a SQL query to calculate the average order total across all orders.
LEFT JOIN
WHERE
NULL
Customers
Orders
Customers Who Have Not Placed Orders
Using two tables, 'Customers' with 'CustomerID' and 'CustomerName', and 'Orders' with 'OrderID', 'CustomerID', write a SQL query to find all customers who have not placed any orders.
SELECT
WHERE
MONTH
OrderDate
Orders
Orders Placed in Specific Month
Given the 'Orders' table with 'OrderID', 'OrderDate', and 'CustomerID', construct a SQL query to retrieve all orders placed in the month of March, regardless of the year.
GROUP BY
SUM
ORDER BY
OrderDetails
Quantity
Products Ordered by Popularity
You have an 'OrderDetails' table with 'OrderID', 'ProductID', and 'Quantity'. Write a SQL query to find the most popular products (based on total quantity ordered), and list them in descending order.
SELECT
ORDER BY
LIMIT
Salary
Employees
Top 5 Highest Paying Employees
Using the 'Employees' table with columns 'EmployeeID', 'FirstName', 'LastName', and 'Salary', generate a SQL query to retrieve the top 5 highest paying employees.
SELECT
WHERE
Discount
Orders
Orders with Discount
Given the 'Orders' table (OrderID, CustomerID, OrderDate, Discount), construct a SQL query to retrieve all orders that have a discount greater than 0.1.
GROUP BY
COUNT
ORDER BY
LIMIT
CustomerID
Orders
Customer with the Most Orders
You are a helpful SQL assistant. Considering the 'Orders' table with 'OrderID' and 'CustomerID', formulate a SQL query to find the customer who has placed the most orders.
JOIN
SUM
GROUP BY
Customers
Orders
Show Customers and Their Total Order Amounts
With 'Customers' (CustomerID, CustomerName) and 'Orders' (OrderID, CustomerID, OrderAmount) tables, write a SQL query to display each customer's name and their total amount of all their orders.
SELECT
ORDER BY
LIMIT
BirthDate
Employees
Find the Oldest Employee
Given the 'Employees' table with 'EmployeeID', 'FirstName', 'LastName', and 'BirthDate' columns, create a SQL query to find the oldest employee.
JOIN
WHERE
ProductName
SupplierName
Products
Suppliers
Products Supplied by a Specific Supplier
Based on the 'Products' (ProductID, ProductName, SupplierID) and 'Suppliers' (SupplierID, SupplierName) tables, retrieve the names of all products supplied by the supplier named 'Exotic Liquids'.
SELECT
WHERE
ShipCountry
Orders
Orders Shipped to a Specific Country
You have an 'Orders' table with 'OrderID', 'CustomerID', and 'ShipCountry'. Construct a SQL query to find all orders shipped to 'Germany'.
AVG
GROUP BY
UnitPrice
SupplierID
Products
Average Product Price per Supplier
Given the 'Products' table (ProductID, ProductName, UnitPrice, SupplierID), write a SQL query to calculate the average product price for each supplier.
WHERE
YEAR
GROUP BY
HAVING
Orders
Customers Who Placed Orders in Both 2022 and 2023
Using the 'Orders' table with 'OrderID', 'CustomerID', and 'OrderDate', write a SQL query to find all customers who placed at least one order in both the year 2022 and 2023.
WHERE
Salary
AVG
Subquery
Employees
Show Employees with Salaries Above Average
Using the 'Employees' table with 'EmployeeID', 'FirstName', 'LastName', and 'Salary', write a SQL query to show all employees whose salary is above the average salary of all employees.
SELECT
WHERE
Discontinued
Products
List of Products Discontinued
Given the 'Products' table with 'ProductID', 'ProductName', and 'Discontinued' (boolean), write a SQL query to list all discontinued products.
JOIN
WHERE
OrderDate
DATE
Customers
Orders
Customers Who Have Not Placed an Order in the Last Year
Based on 'Customers' (CustomerID, CustomerName) and 'Orders' (OrderID, CustomerID, OrderDate), find customers who haven't placed an order in the last year from today's date.
JOIN
SUM
GROUP BY
Products
Categories
Sales by Category
You have 'Products' (ProductID, CategoryID, Price) and 'Categories' (CategoryID, CategoryName). Find total sales (sum of prices) for each product category.
GROUP BY
MAX
OrderDate
CustomerID
Orders
Most Recent Order for Each Customer
Given 'Orders' (OrderID, CustomerID, OrderDate), write a SQL query to find the most recent order date for each customer.
SELECT
ORDER BY
LIMIT
UnitPrice
Products
Find Product with Highest Price
Using the 'Products' table, find the product with the highest unit price. The table includes columns for ProductID, ProductName, and UnitPrice.
COUNT
GROUP BY
MONTH
OrderDate
Orders
Number of Orders per Month
Write a SQL query that counts the number of orders placed each month from the 'Orders' table (OrderID, OrderDate).
JOIN
WHERE
Customers
Orders
List Customers with Orders Over a Certain Amount
From tables 'Customers' (CustomerID, Name) and 'Orders' (OrderID, CustomerID, TotalAmount), list customers who have placed at least one order with a total amount over $1000.
SELECT
WHERE
UnitPrice
Products
Products with Price Range
Given 'Products' table with 'ProductID', 'ProductName', 'UnitPrice'. Write a SQL query to show the products with unit prices between 20 and 50 (inclusive).
LEFT JOIN
WHERE
NULL
Suppliers
Products
Suppliers Who Don't Supply Products
You have 'Suppliers' (SupplierID, SupplierName) and 'Products' (ProductID, SupplierID). List suppliers who don't supply any products.
SUM
GROUP BY
Quantity
UnitPrice
OrderDetails
Total Sales for Each Product
Using the 'OrderDetails' table ('OrderID', 'ProductID', 'Quantity', 'UnitPrice'), determine the total sales for each product.
JOIN
AVG
GROUP BY
Employees
Departments
Average Salary by Department
With 'Employees' (EmployeeID, DepartmentID, Salary) and 'Departments' (DepartmentID, DepartmentName), calculate the average salary for each department.
GROUP BY
COUNT
HAVING
CustomerID
Orders
Customers Who Have Only Placed One Order
From the 'Orders' table ('OrderID', 'CustomerID'), find all customers who have placed only one order.
ORDER BY
LIMIT
OFFSET
Employees
Salary
Find The Second Highest Salary
Given the 'Employees' table with the 'Salary' column, write a SQL query to find the second highest salary.
JOIN
COUNT
GROUP BY
Suppliers
Orders
Show Suppliers and Their Total Orders
Given 'Suppliers' (SupplierID, SupplierName) and 'Orders' (OrderID, SupplierID), generate SQL to display each supplier and the number of orders they have supplied for.
SELECT
WHERE
CustomerName
LIKE
Customers
Customers Whose Names Start with 'A'
Based on the 'Customers' table (CustomerID, CustomerName), write a SQL query to fetch the customer names that begin with the letter 'A'.
SELECT
WHERE
OrderDate
BETWEEN
Orders
Products Ordered in a Specific Date Range
From the 'Orders' table (OrderID, OrderDate, ProductID), show the products ordered between January 1, 2023 and December 31, 2023.
SUM
GROUP BY
Region
Orders
Sales by Region
You have 'Orders' (OrderID, Region, TotalAmount) table. Write SQL to show total sales (sum of TotalAmount) per region.
WHERE
Subquery
Salary
Employees
Employees with Salaries in Top 10 Percent
Using the 'Employees' table ('EmployeeID', 'Salary'), find employees whose salaries are in the top 10 percent of all salaries.
SELECT
ProductName
LIKE
Products
Find Products with Similar Names
Given the 'Products' table (ProductID, ProductName), write a query to find products with similar names (e.g., 'Widget 1' and 'Widget 2'). Use the LIKE operator and wildcards. Try to capture cases where the product name only differs by a number at the end.
SUM
GROUP BY
MONTH
YEAR
OrderDate
TotalAmount
Orders
Monthly Revenue
From the 'Orders' table (OrderID, OrderDate, TotalAmount), show the total revenue earned each month.
LEFT JOIN
WHERE
NULL
OrderDate
Products
Orders
Products Not Ordered in the Last Month
Using 'Products' (ProductID, ProductName) and 'Orders' (OrderID, ProductID, OrderDate), find products that have not been ordered in the last month from today.
SUM
YEAR
GROUP BY
TotalAmount
Orders
Date Comparison
Sales Performance vs. Last Year
With the 'Orders' table (OrderID, OrderDate, TotalAmount), create a SQL query to calculate this year's sales vs. last year's sales. You will need to SUM(TotalAmount) for each year.
SELECT
WHERE
IN
Subquery
Employees
ReportsTo
Show Employees Who Are Managers
Using an 'Employees' table with columns 'EmployeeID', 'FirstName', 'LastName', and 'ReportsTo' (EmployeeID of their manager), construct a SQL query to list the employees who are managers (i.e., have other employees reporting to them).
JOIN
COUNT
GROUP BY
Suppliers
Products
Count of Products by Supplier
You are provided with two tables: 'Suppliers' ('SupplierID', 'SupplierName') and 'Products' ('ProductID', 'ProductName', 'SupplierID'). Write a SQL query to find the number of products provided by each supplier.
ORDER BY
LIMIT
OFFSET
Salary
Employees
Find the Third Highest Salary
Using the 'Employees' table with the 'EmployeeID', 'FirstName', 'LastName', and 'Salary' columns, write a SQL query to find the employee(s) with the third highest salary.
SELECT
FirstName
LastName
LIKE
Employees
List All Employees With Similar Names
Using the Employees table with the 'FirstName' and 'LastName' columns, write a query to find all employees that have a similar first name. Specifically show employees whose first name starts with the same three letters. You may need to use string functions to achieve this.
OVER
PARTITION BY
SUM
ORDER BY
OrderDate
OrderAmount
Orders
Calculate running total order amount
Using the 'Orders' table, with columns like OrderID, OrderDate and OrderAmount, generate the SQL query to compute the running total of OrderAmount ordered by order date. Note that the specific SQL syntax might vary based on the database system used (MySQL, PostgreSQL etc).
RANK
PARTITION BY
CustomerName
OrderDate
Orders
Customers
Show the latest order of each customer
You have two tables, 'Customers' with 'CustomerID' and 'CustomerName', and 'Orders' with 'OrderID', 'CustomerID', and 'OrderDate'. Write SQL query that displays only the most recent order (based on OrderDate) for each customer. List 'CustomerName' and latest 'OrderDate'.
SUM
OVER
PARTITION BY
Price
Products
Categories
Percentage contribution of total Sales amount to each category
Given two tables Products ('ProductID', 'ProductName', 'CategoryID', 'Price') and Categories ('CategoryID', 'CategoryName'), formulate an SQL query to show the percentage contribution of each category to the total Sales amount. Round the output to two decimal places.
LAG
DATEDIFF
PARTITION BY
CustomerID
OrderDate
Orders
Find the Customers Who Placed Orders on Consecutive Days
Using an Orders table which contains columns such as OrderID, CustomerID, and OrderDate, create the SQL Query to find all customers who placed orders on two consecutive days.
COUNT
JOIN
GROUP BY
Products
Orders
Find Products That Have Been Ordered More Than Three Times
Given two tables, 'Products' ('ProductID','ProductName') and 'Orders' ('OrderID','ProductID'), compose the SQL query that show only the names of those products which have been included in more than three different orders.