Blog

Latest: SQL queries interview questions for freshers

In this article, we will be discussing the latest SQL queries interview questions and answers.

If you would like to ace the section with ease then you are at the right place!

Question 1: Show Product Id and Total Quantity sold for each product across all the orders?
*Hint: Refer table [Order Details] 
Answers : select ProductID, sum( Quantity ) as Quantity 
from [Order Details]
Group by ProductID
Order by ProductID

Question 2: Show Product Id, ProductName, and Total Quantity for each product across all the orders?
–*Hint : Refer table [Order Details], [Products]

Answers : 
select [Order Details].ProductID, Products.ProductName, sum([Order Details].Quantity) as Quantity
from [Order Details]
inner join Products 
on [Order Details].ProductId=Products.ProductID
group by [Order Details].ProductID,Products.ProductName 
order by ProductID

 

Question 3: List all the orders that were ordered(date of order) in July 1996?

Answers : select * from Orders where OrderDate between '1996-07-01' and '1996-07-31'

 

Question 4: To which postal code are maximum orders shipped? *Hint: Refer Table [Orders]

Answers :
SELECT top 1 ShipPostalCode,COUNT(*) as cout 
FROM Orders 
GROUP BY ShipPostalCode ORDER BY COUNT(*) DESC

 

Question 5:Which Product(s) has been given the highest ever discount? *Hint : Refer table [Order Details]

Answers :
select ProductID, Discount as discount
from [Order Details]
Where Discount in (Select TOP 1 max(Discount) from [Order Details] group by Discount 
order by Discount DESC )
group by ProductID, Discount
Order BY ProductID

Question 6: From which city do maximum employees belong? *Hint : Refer table Employees

Answers :

SELECT city, count(city) as CityCount
FROM employees 
group by city
HAVING count(city) = 
(
Select Max(cityCount)
from
(select count(city) cityCount
from Employees
group by City) a
)

Question 7: Generate Email IDs for all the Employees using a Select statement.
— For example, if the first name of Employee is Andrew and LastName is Fuller
— then his email ID should be andrew.full@saama.com. *Hint : Refer table [Employees]

Answers :
select concat(firstname,'.',lastname,'@saama.com') as name from employees

Question 8: Show names of all employees and their reporting manager names. *Hint : Refer table [Employees].
— Also, see column ‘ReportsTo’. This column is technically EmployeeId of their reporting manager
— from the same table.

Answers :
select e2.EmployeeID,e2.FirstName,e2.LastName,e2.ReportsTo as ManagerID,
e1.FirstName as ManagerFirstName,e1.LastName as ManagerLastName
from Employees e1,Employees e2
where e1.EmployeeID=e2.ReportsTo order by EmployeeID

Question 9: Create a procedure to delete a record from the Suppliers table.
— This procedure should take SupplierId as an argument?
— *Hint : Refer table [Suppliers]

Answers :
CREATE PROCEDURE usp_DeleteEmployeeRcord
( @SupplierID int)
AS 
BEGIN
DELETE [dbo]. Suppliers 
WHERE SupplierId = @SupplierId
END
exec usp_DeleteEmployeeRcord @SupplierId = 111

Question 10: Create a view that should join data from Customers, Orders &
— Employees and show CustomerId, CompanyName, OrderId, EmployeeName.

Answers :
create view [JoinCustOrderEmpView] as 
select Customers.CustomerId, Customers.CompanyName, Orders.OrderId, Employees.FirstName
from Customers
join Orders 
on Customers.CustomerId = Orders.CustomerId
join Employees
on Employees.EmployeeID = Orders.EmployeeID
select * from [JoinCustOrderEmpView]

Question 11: Create a view to show OrderId, OrderDate, TotalAmount for each order placed.
— *Hint : Refer table [Orders] & [Order Details]

Answers :
CREATE VIEW [OrderView] AS
SELECT Orders.OrderId, Orders.OrderDate, 
([Order Details].UnitPrice * [Order Details].Quantity - [Order Details].Discount ) as TotalAmount
FROM Orders inner join [Order Details] 
on Orders.OrderId=[Order Details].OrderId
select * from [OrderView]

Question 12: List the name of the employee who has place received the highest order value?
— *Hint : Refer table [Employees],[Orders] & [Order Details]

Answers :
select top 1 Employees.EmployeeID, concat(Employees.FirstName,Employees.LastName) as EmployeeName,
count(Orders.OrderID) as Total_Orders
from Orders join Employees
on Orders.EmployeeID = Employees.EmployeeID
join [Order Details]
on [Order Details].OrderId = Orders.OrderID
group by Employees.EmployeeID,Employees.FirstName,Employees.LastName
order by Total_Orders desc

Question 12: Name the product which has the maximum quantity of sales made?
–*Hint : Refer table [Orders], [Order Details], [Products]

Answers :
select top 1 Products.ProductName , sum([Order Details].Quantity) as Maximum_Quantity 
from Orders
join [Order Details] 
on Orders.OrderID = [Order Details].OrderID
join Products
on Products.ProductID = [Order Details].ProductID
group by Products.ProductName
order by Maximum_Quantity desc

Question 13:Create a procedure to update the Categories table?

Answers :Create Procedure UpdateCategories ( @CategoryID INT, @CategoryName varchar(20))
AS
BEGIN
UPDATE [dbo]. Categories
SET
CategoryName = @CategoryName
WHERE CategoryID = @CategoryID
END

exec UpdateCategories @CategoryID=8, @CategoryName = 'Pizza'

Question 14: To which country was the last order shipped? *Hint : Refer table [Orders]

Answers :SELECT top 1 ShipCountry FROM Orders Order By OrderId DESC

Question 15: Which employee made the first sale? *Hint : Refer table [Employees],[Orders]

Answers :
select top 1 FirstName,LastName,OrderDate from Orders
join Employees 
on Orders.EmployeeID = Employees.EmployeeID

Suggested Articles :

  1. Capgemini Pseudocode for practice.
  2. Latest Capgemini data structure MCQ’s questions and answers

Leave a Reply