SQL Basic and Intermediate Questions for Interview

Let's come to the point.

I have created an Awesome SQL Interview GitHub repo to prepare for interview questions and practice SQL queries. I have divided the SQL queries into three sections: Basic (L0), Intermediate (L1), and Advanced (L2). This is the solution for the basic section.

This is L1 (Intermediate) SQL queries to practice, refer to L0 first for better practice.

---

Note: These examples are tested in MySQL. Syntax may vary for other databases like MS-SQL or Oracle.

---

L1: Intermediate SQL

• Queries that involve working with multiple tables, using JOIN, GROUP BY, HAVING, and complex WHERE conditions.
• Introduction to subqueries, aggregate functions, and case statements.

Questions:

1. Write a query to retrieve the customerName and city for customers in 'USA' and 'France'.
2. How do you fetch the employeeNumber, lastName, and officeCode of all employees who work in the 'San Francisco' office?
3. Write a query to find the total number of orders for each customer using orders and customers tables.
4. How do you retrieve the productName, quantityInStock, and buyPrice for products that have been ordered more than 10 times?
5. Write a query to fetch the orderNumber, status, and customerName for orders placed by a customer whose customerNumber is 103.
6. Write a query to find the total sales value (quantityOrdered * priceEach) for each order in the orderdetails table.
7. How do you find the average quantityOrdered for each orderNumber in the orderdetails table?
8. Write a query to list the productLine with the highest total revenue (quantityOrdered * priceEach) in the orderdetails table.
9. Write a query to display the employeeNumber, firstName, lastName, and the office name where the employee works by joining the employees and offices tables.
10. How do you find the customers who have never placed an order?
11. Write a query to retrieve the customerName and the total number of orders placed by each customer (include customers who haven’t placed any orders).
12. Write a query to find the productName and quantityOrdered for all orders where the quantity of the product ordered is greater than 50.
13. Retrieve the employeeNumber, firstName, and orderNumber of employees who are assigned as sales representatives to customers that have placed an order.
14. Write a query to calculate the average price of products in the products table based on buyPrice.
15. How do you fetch the top 3 most expensive products in the products table?
16. Write a query to retrieve the customerName, orderNumber, and orderDate of all orders that have a status of 'Shipped'.
17. How do you display the total number of products sold for each productLine?
18. Write a query to find employees who report directly to the employee with employeeNumber = 1143.
19. Write a query to calculate the total number of orders in the orders table, grouped by status.
20. List employees with their manager’s name.

---

I will mention wrong things also, It is important to know what do to but also very important what not to do, and where we make mistake. let's go to the point again...

---

Solution with the explanation WHERE needed

1. Query to retrieve the customerName and city for customers in 'USA' and 'France'.
   

   • OR -> Slightly slower if there are a lot of conditions, as the query checks each condition one by one.
   • IN -> slightly optimized internally by the database engine, especially for long lists.
   • Both are fine for 2-3 conditions. For readability and scalability, IN is better, especially when handling larger lists of values.
   • IS is used for checking conditions like IS NULL or IS NOT NULL, not for string comparison.

2. Fetch the employeeNumber, lastName, and officeCode of all employees who work in the 'San Francisco' office.
   

3. Query to find the total number of orders for each customer using orders and customers tables.
   

   • Always include non-aggregated columns in the GROUP BY clause when using aggregate functions in your query.
   • This ensures SQL knows how to group rows and avoids ambiguity when selecting additional columns.
   • In our example: customerNumber and customerName must both be in the GROUP BY clause since we are selecting them along with COUNT(*).

   ? Golden Rule:
   Every column in the SELECT list must either:
   Be in the GROUP BY clause, OR
   Use an aggregate function like COUNT(), SUM(), etc.

4. Retrieve the productName, quantityInStock, and buyPrice for products that have been ordered more than 10 times?
   

   • This query is efficient for small and medium size databases, for large sizes we can use indexes, and reduce data scanned using WHERE clause instead of relying solely on HAVING clause

5. Fetch the orderNumber, status, and customerName for orders placed by a customer whose customerNumber is 103.
   

   Explanation:

   • Tables Used:
     • orders: Contains orderNumber and status.
     • customers: Contains customerName.
   • INNER JOIN:
     • Combines orders and customers tables using the customerNumber column (common key).
   • WHERE Clause:
     • Filters the data to include only records where customerNumber = 103.
   • Columns Selected:
     • o.orderNumber: The order number.
     • o.status: The order status.
     • c.customerName: The name of the customer placing the order.

6. Find the total sales value (quantityOrdered * priceEach) for each order in the orderdetails table.
   

7. Find the average quantityOrdered for each orderNumber in the orderdetails table.
   

   • Explanation:
   • orderNumber:
     • Groups the rows by the orderNumber.
   • AVG(quantityOrdered):
     • Calculates the average quantityOrdered for all rows that belong to the same orderNumber.
   • GROUP BY:
     • Ensures the average is calculated for each orderNumber separately.

8. Query to list the productLine with the highest total revenue (quantityOrdered * priceEach) in the orderdetails table.
   

   • Explanation:
   • productLine:
     • Categorizes the products into different lines, like "Motorcycles" or "Planes."
   • SUM(od.quantityOrdered * od.priceEach):
     • Calculates the total revenue for each productLine.
   • INNER JOIN:
     • Joins products and orderdetails tables on productCode to associate product lines with their order details.
   • GROUP BY p.productLine:
     • Groups the results by each productLine.
   • ORDER BY totalRevenue DESC:
     • Sorts the grouped results in descending order of revenue, so the highest revenue appears first.
   • LIMIT 1:
     • Restricts the result to only the productLine with the highest revenue.

9. Query to display the employeeNumber, firstName, lastName, and the office name where the employee works by joining the employees and offices tables.
   

   • CONCAT(column, 'separater', column, 'separater', column)
   • CONCAT_WS('separater', columns)

10. Find the customers who have never placed an order
    

    Explanation:

    • LEFT JOIN: Retrieves all customers from the customers table, whether or not they have matching rows in the orders table.
    • o.orderNumber IS NULL: Identifies customers who do not have any corresponding orders (i.e., orderNumber is NULL because there's no match in the orders table).
    • Columns:
      • customerNumber: Unique identifier for the customer.
      • customerName: Name of the customer.

11. Query to retrieve the customerName and the total number of orders placed by each customer (include customers who haven’t placed any orders).

12. Find the productName and quantityOrdered for all orders where the quantity of the product ordered is greater than 50.
    

13. Retrieve the employeeNumber, firstName, and orderNumber of employees who are assigned as sales representatives to customers that have placed an order.
    

    Explanation:

    • FROM employees e:
      • We start with the employees table (aliased as e) because we want the employee details, specifically the employeeNumber and firstName.
    • JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber:
      • We join the customers table (aliased as c) on the employeeNumber from employees and salesRepEmployeeNumber from customers. This creates the relationship between employees (sales reps) and customers. Now, we can identify which employee is assigned to each customer.
    • JOIN orders o ON c.customerNumber = o.customerNumber:
      • We further join the orders table (aliased as o) with the customers table using the customerNumber. This gives us the orders placed by each customer.
    • SELECT e.employeeNumber, e.firstName, o.orderNumber:
      • Finally, we select the employeeNumber and firstName from the employees table (sales reps) and the orderNumber from the orders table for each customer who has placed an order.

14. Query to calculate the average price of products in the products table based on buyPrice.
    

15. Fetch the top 3 most expensive products in the products table?
    
    

16. Rretrieve the customerName, orderNumber, and orderDate of all orders that have a status of 'Shipped'.
    

17. Display the total number of products sold for each productLine
    

18. Find employees who report directly to the employee with employeeNumber = 1143.
    

19. Query to calculate the total number of orders in the orders table, grouped by status.
    

20. List employees with their manager’s name.
    

---

Hey, My name is Jaimin Baria AKA Cloud Boy..., If you have enjoyed and learned something useful, like this post, add a comment, and visit my Awesome SQL Interview GitHub repo.

Don't forget to give it a start ?.

Happy Coding ?‍?

---

Other Posts

• SQL Practices:
  • Part 1
    • L0: Basic SQL
    • L1: Intermediate SQL
    • L2: Advanced SQL - Will Come soon
• System Design
  • Implementation of ACID transaction in Database
  • ACID Transactions in System Design

---

?️ Fixes Suggested by Readers

The above is the detailed content of SQL Basic and Intermediate Questions for Interview. For more information, please follow other related articles on the PHP Chinese website!