Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
SQL Interview Questions : Customer Orders
/* Question 1: You are given following two tables, Customer{cust_id, cust_name, ...<Other customer related details>} Order{order_id, order_name, cust_id, ...<Other order related details>} You have to provide the output in following format. cust_id, cust_name, [Total amount of orders] Please note that you have to do this in SQL/Scope, and print only those customer who have at least one order. */ create table Customer(cust_id int, cust_name varchar(255),gender varchar(5)); create table Orders(order_id int,order_name varchar(255),cust_id int,order_timestamp date,order_price int); insert into Customer values(1,'Pallavi','F'); insert into Customer values(2,'Shinchan','M'); insert into Customer values(3,'El','M'); insert into Customer values(4,'Monika','F'); insert into Orders values(1,'Laptop',1,'20210201',100); insert into Orders values(2,'Headphones',1,'20210205',50); insert into Orders values(3,'TV',2,'20200201',300); insert into Orders values(4,'Cleaner',3,'20200101',400); insert into Orders values(5,'PS',3,'20201221',600); select * from Customer; select * from Orders; select a.cust_name,a.cust_id,count(distinct b.order_id) from ( select cust_name,cust_id from Customer )a join ( select order_id,cust_id from orders )b on a.cust_id = b.cust_id group by a.cust_id,a.cust_name having count(distinct b.order_id)>=1 /* Question 2: Find out all customers who haven't placed any order in the past six months. */ select b.cust_name from ( select * from Orders where order_timestamp < DATEADD(m,-6,GetDate()) )a join ( select * from Customer )b on a.cust_id = b.cust_id ; /* Question 3: write a SQL query to find out customer's total purchasing payment in the past six months. */ select b.cust_name,sum(a.order_price) as 'total purchase amount' from ( select * from Orders where order_timestamp >= DATEADD(m,-6, GetDate()) )a join ( select * from Customer )b on a.cust_id = b.cust_id group by a.cust_id,b.cust_name
run
|
edit
|
history
|
help
0
Veterinary 1
decimal_test
emp
kirthi
Ranking
employees
TSQL - Globally unique string generator
index
string splitter
ss