Database Management System (DBMS) is the core subject of Computer Science and Engineering. To improve the SQL Query fundaments you have to practice a lot of questions.
This question will cover all the basic fundamental concepts of SQL.
Given a Table Sales as given below:-
Q 1: Count how many orders have made a customer with CustomerName of Smith.
Ans:
Select count(orderID) from sales where customerName=’Smith’;
Q 2: Find the number of unique customers that have ordered from the store.
Ans:
Select count(distinct CustomerName) as NumerofUnique from sales;
Q 3: Find out total number of items ordered by all the customers.
Ans:
select sum(orderQuality) from sales;
Q 4: Find out average number of items per order.
Ans:
select avg(orderQuality) from sales;
Q 5: Find out the average OrderQuantity for all orders with OrderPrice greater than 200
Ans:
select avg(orderQuality) from sales where orderPrice>200;
Q 6: Find out what was the minimum price paid for any of the orders.
Ans:
select min(orderPrice) from sales;
Q 7: Find out the highest OrderPrice from the given sales table
Ans:
select max(orderPrice) from sales;
Q 8: List out unique customers' name only from the given sales table
Ans:
select distinct customerName from sales;
Q 9: List out name of the customers who have given order in the month of December.
Ans:
select customerName from sales where month(orderDate)=’12’;
Q 10: Find out the total amount of money spent for each of the customers.
Ans:
select sum(orderPrice*orderQuality) as totalAmount from sales;
Q 11: Select all unique customers, who have spent more than 1200 in the store.
Ans:
select distinct customerName from sales where (orderPrice*orderQuality)>1200;
Q 12: Select all customers that have ordered more than 5 items in total from all their orders.
Ans:
select customerName, sum(orderQuality) from sales group by customerName having sum(orderQuality)>5;
Q 13: Select all customers who have spent more than 1000 after 10/01/2005.
Ans:
select customerName, (orderPrice*orderQuality) from sales where (orderPrice*orderQuality)>1000 and orderDate>'2005-10-01';
