SQL MUST Interview Questions & Answers (Level 1 MYSQL)

These questions cover what data analysts would face at an entry-level to the intermediate range for SQL. Depending on the organization and role, the requirement for SQL knowledge may increase. However, these will cover the main concepts from joins, subqueries, grouping, alias, and filtering. You can practice all these SQL queries on our free platform: Test Your SQL Query on This Platform

1. Find the customer ID with the number of orders sorted in descending order (Table Used: Orders)

select customerid, count(orderid) as order_count from orders
group by customerid
order by order_count desc

2a. Find the customer ID with the maximum number of orders (Table Used: Orders)

select customerid, count(orderid) as order_count from orders
group by customerid
order by order_count desc
limit 1

2b. Find the customer ID with the maximum number of orders (Table Used: Orders) – Alternative Solution – Using Subqueries

select customerid, count(orderid) as order_count from orders
group by customerid
having order_count in (
select max(order_count) from(
(select customerid, count(orderid) as order_count from orders
group by customerid) a))

3. Find the customer Name and Customer ID with the number of orders sorted in descending order (Table Used: Orders and Customer)

select a.customername, a.customerid, count(distinct b.orderID) as order_count 
from customers as a 
inner join orders b
on a.customerid = b.customerid
group by a.customername, a.customerid
order by  order_count desc

4. Find the Customer Name, Customer ID with the number of products they purchased in a single order transaction sorted by descending order.

Select C.CustomerName,C.CustomerID,B.OrderID,count(distinct A.ProductID) as Number_of_Product from 
orderdetails as A
inner join 
orders as B 
on A.OrderID=B.OrderID
inner join 
customers as C 
on C.CustomerID=B.CustomerID
group by C.CustomerName,C.CustomerID,B.OrderID
order by Number_of_Product desc 

5. Find the Average Orders per Total Customer for every employee ID and filter the ones which have a value greater than 1.8

Select EmployeeID, count(distinct OrderID)/count(distinct CustomerID) as Avg from 
orders 
group by EmployeeID 
having Avg>1.8 
order by Avg desc

6. Find the number of Orders by each EmployeeID on a month-by-month basis and place a (Premium Customer) flag on customers which have more than 5 orders on a Month-by-month basis.

Select EmployeeID, year(OrderDate) as Year, 
month(OrderDate) as Month, 
count(OrderID) as OrderCount,
case when count(OrderID)>5 then 1 else 0 end as Premium_Customer
from orders 
group by EmployeeID, year(OrderDate),month(OrderDate)
order by Year, Month, OrderCount desc 

7. Use Inner join to filter Employees that are born post-1960 

select b.* from 
(Select EmployeeID from employees
where year(BirthDate)>1960) a
inner join 
(Select EmployeeID, year(OrderDate) as Year, 
month(OrderDate) as Month, 
count(OrderID) as OrderCount,
case when count(OrderID)>5 then 1 else 0 end as Premium_Customer
from orders 
group by EmployeeID, year(OrderDate),month(OrderDate)
order by Year, Month, OrderCount desc) b 
on a.EmployeeID=b.EmployeeID

8.  Compare Sales of Different Products by developing a Pairwise Table, for example, the first record should be one product’s sales compared with other productSales.

select *,
abs(Sales_2-Sales_1) as Gap
 from 
(Select ProductID as Product1, sum(Quantity) as Sales_1 from orderdetails
group by ProductID) as a
cross join
(Select ProductID as Product2, sum(Quantity) as Sales_2 from orderdetails
group by ProductID) as b
where Product1>Product2

9.  In the previous question find the pair of products with the highest difference in sales

You can watch the live code and explanation of all these queries here: