Scribd
Upload
48 views

Day6 Assignment Solution

The document contains 9 questions asking to write SQL queries to analyze data from employee and orders tables. The questions include finding the age difference between employees and their managers, identifying subcategories that never had returns in November, finding orders with only one product, listing employees reporting to each manager, calculating the number of business days between an order and ship date, and more. The document provides sample SQL queries that can be used to solve each problem.

Uploaded by

Aditya Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
48 views

Day6 Assignment Solution

The document contains 9 questions asking to write SQL queries to analyze data from employee and orders tables. The questions include finding the age difference between employees and their managers, identifying subcategories that never had returns in November, finding orders with only one product, listing employees reporting to each manager, calculating the number of business days between an order and ship date, and more. The document provides sample SQL queries that can be used to solve each problem.

Uploaded by

Aditya Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

Note: please do not use any functions which are not taught in the class.

you need
to solve the questions only with the concepts that have been discussed so far.

Run the following command to add and update dob column in employee table
alter table employee add dob date;
update employee set dob = dateadd(year,-1*emp_age,getdate())

1- write a query to print emp name , their manager name and diffrence in their age
(in days)
for employees whose year of birth is before their managers year of birth

select e1.emp_name,e2.emp_name as manager_name , DATEDIFF(day,e1.dob,e2.dob) as


diff_in_age
from employee e1
inner join employee e2 on e1.manager_id=e2.emp_id
where DATEPART(year,e1.dob)< DATEPART(year,e2.dob)
;

2- write a query to find subcategories who never had any return orders in the month
of november (irrespective of years)
select sub_category
from orders o
left join returns r on o.order_id=r.order_id
where DATEPART(month,order_date)=11
group by sub_category
having count(r.order_id)=0;

3- orders table can have multiple rows for a particular order_id when customers
buys more than 1 product in an order.
write a query to find order ids where there is only 1 product bought by the
customer.
select order_id
from orders
group by order_id
having count(1)=1

4- write a query to print manager names along with the comma separated list(order
by emp salary) of all employees directly reporting to him.
select e2.emp_name as manager_name , string_agg(e1.emp_name,',') as emp_list
from employee e1
inner join employee e2 on e1.manager_id=e2.emp_id
group by e2.emp_name

5- write a query to get number of business days between order_date and ship_date
(exclude weekends).
Assume that all order date and ship date are on weekdays only

select order_id,order_date,ship_date ,datediff(day,order_date,ship_date)-


2*datediff(week,order_date,ship_date) as no_of_business_days
from
orders

6- write a query to print 3 columns : category, total_sales and (total sales of


returned orders)
select o.category,sum(o.sales) as total_sales
,sum(case when r.order_id is not null then sales end) as return_orders_sales
from orders o
left join returns r on o.order_id=r.order_id
group by category

7- write a query to print below 3 columns


category, total_sales_2019(sales in year 2019), total_sales_2020(sales in year
2020)

select category,sum(case when datepart(year,order_date)=2019 then sales end) as


total_sales_2019
,sum(case when datepart(year,order_date)=2020 then sales end) as total_sales_2020
from orders
group by category

8- write a query print top 5 cities in west region by average no of days between
order date and ship date.
select top 5 city, avg(datediff(day,order_date,ship_date) ) as avg_days
from orders
where region='West'
group by city
order by avg_days desc

9- write a query to print emp name, manager name and senior manager name (senior
manager is manager's manager)

select e1.emp_name,e2.emp_name as manager_name,e3.emp_name as senior_manager_name


from employee e1
inner join employee e2 on e1.manager_id=e2.emp_id
inner join employee e3 on e2.manager_id=e3.emp_id

You might also like