Scribd Logo
Upload

Welcome to Scribd!

  • 30 views

    Day6 Assignment Solution

    Uploaded by

    Aditya Singh
    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.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Day6 Assignment Solution

    Uploaded by

    Aditya Singh
    30 views2 pages
    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.

    Original Title

    day6_assignment_solution

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    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.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    30 views2 pages

    Day6 Assignment Solution

    Uploaded by

    Aditya Singh
    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.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 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