SQL PRACTICE SHEET

Consider the SQL tables given below and answer the questions that follow.

TABLE 1: Customers [-]

● customer_id[int]
● first_name[varchar(100)]
● last_name[varchar(100)]
● age[int]
● country[varchar(100)]

TABLE 2: Orders [-]

● order_id[integer]
● item[varchar(100)]
● amount[integer]
● Customer_id[integer]

TABLE 3: Shippings [-]

● shipping_id[integer]
● status[integer]
● customer[integer]

11th November
 1. Write a query to display the first and last names of all customers from ‘USA’.
2. Write a query to display the order_id, item, and amount for orders placed by the customer with
customer_id = 1.
3. Write a query to display the total number of orders in the Orders table.

12th November
1. Display the customer’s full name in uppercase and country in lowercase.
2. Display the length of each customer’s last name and their first name reversed.
3. Display the first three characters of each customer’s first name and age.
4. Display customers’ last names after removing the character ‘o’.
5. Display details of customers who are from the USA.
6. Find the total number of orders placed by customers from the UK.
7. Find the maximum and minimum order amounts in the Orders table.
8. Display the total number of unique items ordered in the Orders table.
9. Display the average order amount for all orders with the item “Keyboard”.
10. Display the total number of pending shipments.
11. Assuming the Shippings table has a shipping date column, display the day, month, and year of
shipping for all shipments with the status “Delivered”.
12. If the shipping date falls on a Monday, display it as “Monday, 15, January, 2024.”

13th November

1. Write a query to list all customers along with their age categorized into age groups: ‘Youth’ (below
25), ‘Adult’ (25-50), and ‘Senior’ (above 50).

2. Display the customer_id and the total number of characters in each customer’s full name (first name +
last name).

3. List the order_id and item for all orders, showing ‘High Value’ if the amount is greater than 1000,
otherwise ‘Standard’.

4. Display all customer IDs who have never placed an order.

5. Find the sum of amounts for each item in the Orders table and display the items with a total amount
greater than 5000.

6. List all distinct customer IDs from the Shippings table along with their total number of shipments.

7. Display each customer’s full name along with the last character of their first name and the first
character of their last name.

8. List the items, and corresponding order amounts for all orders placed by customers over 60.
 9. Display all customer IDs of customers who have ordered more than three unique items.

10. Show all customer IDs and the total amount spent on orders for each customer who has placed an
order for ‘Mouse’.

11. Find the average age of customers with an order amount of 200 or more.

12. Display the full names of customers who have an order with an even amount.

13. Show all customers who have placed orders but need shipping records.

14. Display customer IDs and their last order’s order_id (highest order_id).

15. List all customers’ full names and respective countries for those who have placed an order with an
odd-numbered order_id.

16. Find the number of customers who have the same first name.

17. Display the last names of all customers sorted by the second character in their last name in ascending
order.

18. Find customers with the longest first name, displaying only the full names and customer IDs.

19. Show the customer_id, age, and number of orders for customers with at least one order amounting to
precisely 100.

20. List all distinct order items and the number of times each item has been ordered, sorted in descending
order of frequency.

1. Write a query to display the first and last names of all customers from ‘USA’.
Command:
SELECT first_name, last_name
FROM Customers;

2. Write a query to display the order_id, item, and amount for orders placed by the customer with
customer_id = 1.
Command:

SELECT order_id, item, amount

FROM Orders
WHERE customer_id = 1;

3. Write a query to display the total number of orders in the Orders table.
Command:

SELECT COUNT(*) AS total_orders

FROM Orders;
 4. Display the customer’s full name in uppercase and country in lowercase.
Command:

SELECT
UPPER(first_name || ' ' || last_name) AS full_name,
LOWER(country) AS country
FROM
Customers;

5. Display the length of each customer’s last name and their first name reversed.
Command:

SELECT
LENGTH(last_name) AS last_name_length,
REVERSE(first_name) AS reversed_first_name
FROM
Customers;

6. Display the first three characters of each customer’s first name and age.
Command:
SELECT
SUBSTRING(first_name, 1, 3) AS first_name_start,
age
FROM
Customers;

7. Display customers’ last names after removing the character ‘o’.

Command:

SELECT
REPLACE(last_name, 'o', '') AS modified_last_name
FROM
Customers;

8. Display details of customers who are from the USA.

Command:
SELECT *
FROM Customers
WHERE country = 'USA';

9. Find the total number of orders placed by customers from the UK.
Command:

SELECT
COUNT(*) AS total_orders
FROM
Orders AS o
JOIN
Customers AS c ON o.customer_id = c.customer_id
WHERE
[Link] = 'UK';

10. Find the maximum and minimum order amounts in the Orders table.
Command:

SELECT
MAX(amount) AS max_order_amount,
MIN(amount) AS min_order_amount
FROM
Orders;
 11. Display the total number of unique items ordered in the Orders table.
Command:

SELECT
COUNT(DISTINCT item) AS unique_items_count
FROM
Orders;

12. Display the average order amount for all orders with the item “Keyboard”.
Command:

SELECT
AVG(amount) AS average_order_amount
FROM
Orders
WHERE
item = 'Keyboard';

13. Display the total number of pending shipments.

Command:

SELECT COUNT(*) AS total_pending_shipments

FROM Shippings
WHERE status = 'Pending';

14. Write a query to list all customers along with their age categorized into age groups: ‘Youth’
(below 25), ‘Adult’ (25-50), and ‘Senior’ (above 50).

Command:

SELECT customer_id, first_name || ' ' || last_name "full name" , 'Youth' AS

age_group FROM Customers WHERE age < 25

UNION

SELECT customer_id, first_name || ' ' || last_name "full name" , 'Adult' AS

age_group FROM CustomerS WHERE age between 25 and 50

UNION

SELECT customer_id, first_name || ' ' || last_name "full name" , 'Senior'

AS age_group FROM Customers WHERE age > 50;

15. Display the customer_id and the total number of characters in each customer’s full name (first
name + last name).
Command:

SELECT customer_id, LENGTH(first_name) + LENGTH(last_name) AS

total_name_length
FROM Customers;

16. List the order_id and item for all orders, showing ‘High Value’ if the amount is greater than
1000, otherwise ‘Standard’.

Command:

SELECT
order_id,
item,
CASE
WHEN amount > 1000 THEN 'High Value'
ELSE 'Standard'
END AS order_category
FROM Orders;

17. Display all customer IDs who have never placed an order.
Command:
SELECT
customer_id
FROM
Customers
WHERE
customer_id NOT IN (SELECT customer_id FROM Orders);

18. Find the sum of amounts for each item in the Orders table and display the items with a total
amount greater than 5000.

Command:

SELECT
item,
SUM(amount) AS total_amount
FROM
Orders
GROUP BY
item
HAVING
SUM(amount) > 5000;

19. List all distinct customer IDs from the Shippings table along with their total number of
shipments.

Command:

SELECT
 customer AS customer_id,
COUNT(*) AS total_shipments
FROM
Shippings
GROUP BY
Customer;

20. Display each customer’s full name along with the last character of their first name and the first
character of their last name.

Command:

SELECT
first_name || ' ' || last_name AS full_name,
SUBSTR(first_name, -1, 1) AS last_char_first_name,
SUBSTR(last_name, 1, 1) AS first_char_last_name
FROM
Customers;

21. List the items, and corresponding order amounts for all orders placed by customers over 60.
Command:

SELECT
item,
amount
FROM
Orders
WHERE
 customer_id IN (
SELECT customer_id
FROM Customers
WHERE age > 60
);

22. Display all customer IDs of customers who have ordered more than three unique items.
Command:

SELECT
customer_id
FROM
Orders
GROUP BY
customer_id
HAVING
COUNT(DISTINCT item) > 3;

23. Show all customer IDs and the total amount spent on orders for each customer who has placed
an order for ‘Mouse’.

Command:

SELECT customer_id, SUM(amount) AS total_amount_spent

FROM Orders
WHERE item = 'Mouse'
GROUP BY customer_id;

24. Find the average age of customers with an order amount of 200 or more.
Command:

SELECT AVG(age) AS average_age

FROM Customers
WHERE customer_id IN (
SELECT customer_id
FROM Orders
WHERE amount >= 200
);

25. Display the full names of customers who have an order with an even amount.

Command:

SELECT first_name || ' ' || last_name AS full_name

FROM Customers
WHERE customer_id IN (
SELECT customer_id FROM Orders WHERE amount % 2 = 0
);

26. Show all customers who have placed orders but need shipping records.

Command:
SELECT c.*
FROM Customers c, Orders o
WHERE c.customer_id = o.customer_id
AND o.order_id NOT IN (SELECT order_id FROM Shippings);

27. Display customer IDs and their last order’s order_id (highest order_id).

Command:

SELECT customer_id, MAX(order_id) AS last_order_id

FROM Orders
GROUP BY customer_id;

28. List all customers’ full names and respective countries for those who have placed an order with
an odd-numbered order_id.

Command:

SELECT first_name || ' ' || last_name AS full_name, country

FROM Customers
WHERE customer_id IN (
SELECT customer_id FROM Orders WHERE order_id % 2 = 1
);
 29. Find the number of customers who have the same first name.

Command:

SELECT first_name, COUNT(*) AS num_customers

FROM Customers
GROUP BY first_name
HAVING COUNT(*) > 1;

30. Display the last names of all customers sorted by the second character in their last name in
ascending order.

Command:

SELECT last_name
FROM Customers
ORDER BY SUBSTRING(last_name, 2, 1);

31. Find customers with the longest first name, displaying only the full names and customer IDs.

Command:

SELECT customer_id, first_name || ' ' || last_name AS full_name

FROM Customers
WHERE LENGTH(first_name) = (SELECT MAX(LENGTH(first_name)) FROM
Customers);
 32. Show the customer_id, age, and number of orders for customers with at least one order
amounting to precisely 100.

Command:

(SELECT COUNT(*) FROM Orders WHERE Orders.customer_id =

Customers.customer_id AND amount = 100) AS num_orders
FROM Customers
WHERE customer_id IN (
SELECT customer_id FROM Orders WHERE amount = 100
);

33. List all distinct order items and the number of times each item has been ordered, sorted in
descending order of frequency.

Command:

SELECT item, COUNT(*) AS num_orders

FROM Orders
GROUP BY item;