SQL Interview and Exam Questions

---

Design • 17-Feb-2021

---

SQL Interview or Exam Questions

Do you prepare for a technical interview or a university exam? These problems and questions may help you to get prepared for this.

Using this database model:

Please answer the following questions:

1. Count how many order lines included the purchases of apples or avocado. Solution

2. List all users who have ever bought apples. Each user should be listed at most one time. Solution

3. Display the three products with the least overall orders in terms of quantity. Solution

4. Display all order lines that had a unit price of less than 22.00 and a Total cost of more than 260.00. Solution

5. Display minimal and maximal order total amount for each customer. Solution

6. List customers with total orders over 50, ordered by the total price. Solution

7. List customers with no orders. Solution

---

Access the Data Model and SQL Editor using DbSchema

The data model and the SQL Editor are included in the DbSchema Free Version. Please download and install. Start DbSchema and open the sample SQL Interview Questions model.

From there you can open the SQL Editor.

---

Question 1

Count how many order lines included the purchases of apple or avocado.

SELECT p.productname, count(*)
FROM orders o
  INNER JOIN orderitems o1 ON ( o.id = o1.orderid  )  
  INNER JOIN products p ON ( o1.productid = p.id  )  
WHERE productname='apple' or productname='avocado'
GROUP BY p.productname

This can be done visually using the Query builder, for which you should request an Pro evaluation key.

---

Question 2

List all users who have ever bought apples. Each user should be listed at most one time.

This can have two solutions.

SELECT c.id, c.firstname, c.lastname
FROM customer c
  JOIN "order" o ON ( c.id = o.customerid  )  
  JOIN orderitem o1 ON ( o.id = o1.orderid  )  
  JOIN product p ON ( o1.productid = p.id  )  
WHERE p.productname='apple'
GROUP by c.id, c.firstname, c.lastname
HAVING count(*) > 0;

Using the Query Builder:

Solution 2 performs better on large tables:

SELECT c.id, c.firstname, c.lastname, c.city, c.country, c.phone
FROM customers c
WHERE  EXISTS (
  SELECT 1
  FROM orders o
    INNER JOIN orderitems o1 ON ( o.id = o1.orderid  )  
    INNER JOIN products p ON ( o1.productid = p.id  )  
  WHERE c.id = o.customerid  AND
    p.productname = 'apple' )

Using the Query Builder

---

Question 3

Display the three products with the least overall orders in terms of quantity.

SELECT p.productname, sum(o.unitprice)
FROM product p
INNER JOIN orderitem o ON ( p.id = o.productid  )  
GROUP BY p.productname, o.unitprice
ORDER BY sum(o.unitprice)
LIMIT 3

---

Question 4

Display all order lines that had a unit price of less than 22.00 and a Total cost of more than 260.00.

SELECT o.id, o.orderdate, o.ordernumber, o.customerid, o.totalamount, sum(totalamount) total
FROM "order" o
JOIN orderitem i ON ( o.id=i.orderid)
WHERE i.unitprice < 22 
  AND o.totalamount > 220;

---

Question 5

Display minimal and maximal order total amount for each customer.

SELECT c.id, c.firstname, c.lastname, min(totalamount), max(totalamount)
FROM customers c
JOIN orders o ON ( c.id=o.customerid )
GROUP by c.id, c.firstname, c.lastname

---

Question 6

List customers with total orders over 50, ordered by the total price.

SELECT c.id, c.firstname, c.lastname, sum(totalamount)
FROM customers c
JOIN orders o ON ( c.id=o.customerid )
GROUP by c.id, c.firstname, c.lastname
HAVING SUM(totalamount) > 50
ORDER BY SUM(totalamount)

---

Question 7

List customers with no orders.

SELECT *
FROM customers c
WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customerid=c.id )

Do you think we can improve this article? Please write us using DbSchema Help / Report a Bug or create a ticket.

Share this blog post on Twitter, Facebook, and LinkedIn