ultimate SQL Queries for Interview

TOP 10 SQL Queries for Interview :

We’ll be using the following sample tables:

1. Sales:

• Columns: sale_id, sale_date, product_id, quantity, unit_price, total_amount, customer_id
• Description: This table stores the details of each sale transaction.

2.Products:

• Columns: product_id, product_name, category, price
• Description: Contains details about each product.

3.Customers:

• Columns: customer_id, customer_name, city, state
• Description: Holds information about customers.

4.Salesperson:

• Columns: salesperson_id, salesperson_name, region
• Description: Information about the salespeople handling different regions.

Sample Data for Queries:

Sales Table:

sale_id	sale_date	product_id	quantity	unit_price	total_amount	customer_id
1	2024-01-15	101	5	200	1000	201
2	2024-02-10	102	3	150	450	202

Products Table:

product_id	product_name	category	price
101	Laptop	Electronics	800
102	Mouse	Accessories	50

Customers Table:

customer_id	customer_name	city	state
201	John Doe	Seattle	WA
202	Jane Smith	Austin	TX

Salesperson Table:

salesperson_id	salesperson_name	region
301	Mike Johnson	North
302	Sarah Lee	South

---

Question 1: Identify the top 3 products that have generated the highest total sales amount across all transactions.

Answer:

SELECT 
    p.product_name,
    SUM(s.total_amount) AS total_sales
FROM 
    Sales s
JOIN 
    Products p ON s.product_id = p.product_id
GROUP BY 
    p.product_name
ORDER BY 
    total_sales DESC
LIMIT 3;

Optimization Suggestion: Ensure there’s an index on product_id in both Sales and Products tables for faster JOIN operations.

---

Question 2: Find the sales made by customers who purchased products from more than one category.

Answer:

SELECT 
    s.customer_id,
    c.customer_name,
    COUNT(DISTINCT p.category) AS category_count,
    SUM(s.total_amount) AS total_spent
FROM 
    Sales s
JOIN 
    Products p ON s.product_id = p.product_id
JOIN 
    Customers c ON s.customer_id = c.customer_id
GROUP BY 
    s.customer_id, c.customer_name
HAVING 
    COUNT(DISTINCT p.category) > 1;

Optimization Suggestion: Use composite indexes on (product_id, category) in the Products table.

---

Question 3: Find the month with the highest sales for each product category.

Answer:

SELECT 
    p.category,
    EXTRACT(MONTH FROM s.sale_date) AS sale_month,
    SUM(s.total_amount) AS monthly_sales
FROM 
    Sales s
JOIN 
    Products p ON s.product_id = p.product_id
GROUP BY 
    p.category, EXTRACT(MONTH FROM s.sale_date)
ORDER BY 
    p.category, monthly_sales DESC;

Optimization Suggestion: Consider using a date-based partition index on sale_date to speed up monthly calculations.

---

Question 4: Calculate the average sales amount per customer per product category.

Answer:

SELECT 
    s.customer_id,
    c.customer_name,
    p.category,
    AVG(s.total_amount) AS avg_sales
FROM 
    Sales s
JOIN 
    Products p ON s.product_id = p.product_id
JOIN 
    Customers c ON s.customer_id = c.customer_id
GROUP BY 
    s.customer_id, c.customer_name, p.category;

---

Question 5: Find sales people who have never sold products in the ‘Electronics’ category.

Answer:

SELECT 
    sp.salesperson_name
FROM 
    Salesperson sp
LEFT JOIN 
    Sales s ON sp.salesperson_id = s.salesperson_id
LEFT JOIN 
    Products p ON s.product_id = p.product_id
WHERE 
    p.category = 'Electronics'
    OR p.category IS NULL
GROUP BY 
    sp.salesperson_name
HAVING 
    COUNT(p.product_id) = 0;

---

Question 6: Retrieve the top 5 customers who have spent the most in total, regardless of the product category.

Answer:

SELECT 
    c.customer_name,
    SUM(s.total_amount) AS total_spent
FROM 
    Sales s
JOIN 
    Customers c ON s.customer_id = c.customer_id
GROUP BY 
    c.customer_name
ORDER BY 
    total_spent DESC
LIMIT 5;

---

Question 7: Identify the products that haven’t been sold in the last 6 months.

Answer:

SELECT 
    p.product_name
FROM 
    Products p
LEFT JOIN 
    Sales s ON p.product_id = s.product_id 
           AND s.sale_date >= CURRENT_DATE - INTERVAL '6 months'
WHERE 
    s.product_id IS NULL;

---

Question 8: Find the highest single sale transaction amount and the corresponding customer details.

Answer:

SELECT 
    s.sale_id,
    c.customer_name,
    s.total_amount
FROM 
    Sales s
JOIN 
    Customers c ON s.customer_id = c.customer_id
ORDER BY 
    s.total_amount DESC
LIMIT 1;

---

Question 9: Display the percentage contribution of each product to the overall sales.

Answer:

SELECT 
    p.product_name,
    (SUM(s.total_amount) * 100.0 / (SELECT SUM(total_amount) FROM Sales)) AS percentage_contribution
FROM 
    Sales s
JOIN 
    Products p ON s.product_id = p.product_id
GROUP BY 
    p.product_name;

---

Question 10: Retrieve the sales data for customers whose names start with the same letter as the cities they live in.

Answer:

SELECT 
    c.customer_name,
    c.city,
    SUM(s.total_amount) AS total_spent
FROM 
    Sales s
JOIN 
    Customers c ON s.customer_id = c.customer_id
WHERE 
    LEFT(c.customer_name, 1) = LEFT(c.city, 1)
GROUP BY 
    c.customer_name, c.city;

Note : These SQL queries are pretty versatile and work well with several popular databases. They run perfectly in PostgreSQL without any changes. If you’re using MySQL or MariaDB, you’ll just need to tweak things a bit, like swapping CURRENT_DATE with CURDATE(). For SQL Server, simple adjustments like using GETDATE() instead of CURRENT_DATE and DATEADD() for intervals will do the trick. Oracle is quite similar, but you’ll want to replace CURRENT_DATE with SYSDATE and use TO_CHAR() for date handling. SQLite is mostly compatible too, but make sure functions like EXTRACT() work as expected. In short, these queries are quite adaptable and should run smoothly with just a few tweaks, no matter which database you’re using!

• Mastering Oracle SQL Joins: The Ultimate Guide to Data Retrieval and Performance
• Mastering Oracle Database Architecture: A Comprehensive Overview
• Mastering PostgreSQL Architecture: 6 Powerful Steps to Optimize Performance & Scalability
• Understanding Oracle Triggers – A Comprehensive Guide with Examples
• SQL Query Design Patterns: Writing Efficient Queries