Scribd
Upload
31 views3 pages

SQL Queries for Product and Order Analysis

The document contains SQL queries for various data retrieval tasks from a database. It includes queries to select products based on price and stock, calculate average orders per employee, analyze discounts by category, count customers by country, identify customers with above-average orders, calculate average stock units for top products, and retrieve orders handled by employees with high sales. Each query is structured to extract specific insights from the database using subqueries and aggregations.

Uploaded by

superfilo98i
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
31 views3 pages

SQL Queries for Product and Order Analysis

The document contains SQL queries for various data retrieval tasks from a database. It includes queries to select products based on price and stock, calculate average orders per employee, analyze discounts by category, count customers by country, identify customers with above-average orders, calculate average stock units for top products, and retrieve orders handled by employees with high sales. Each query is structured to extract specific insights from the database using subqueries and aggregations.

Uploaded by

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

ASSIGNMENT 5

QUESTION 1

select productid, productname, unitprice, unitsinstock from products p


where (unitprice) > (select avg(unitprice) from products p)
and (unitsinstock) < (select avg(unitsinstock) from products p)
order by productname

QUESTION 2

with order_per_emp as
(select employeeid, count(*) tot_order_emp from orders o
group by employeeid)
select round(avg(tot_order_emp)) avg_order_per_emp from order_per_emp

QUESTION 3

with avg_disc as
(select categoryname, avg(discount*[Link]*quantity) avg_cat_disc from
products p
join orderdetails o
on [Link] = [Link]
join categories c
on [Link] = [Link]
group by categoryname)
select categoryname, round(avg_cat_disc) from avg_disc
where avg_cat_disc > (select avg(discount*unitprice*quantity) from orderdetails o)
order by categoryname

QUESTION 4

select country, count(*) num_customers from customers c


where country in (select country from customers c
left join orders o
on [Link] = [Link]
where [Link] is null)
group by country

QUESTION 5

with c_order as
(select customerid, count(*) num_orders from orders o
group by customerid)
select customerid, num_orders from c_order
where num_orders > (select avg(num_orders) from c_order)
order by num_orders desc
limit 5

QUESTION 6
select avg(unitsinstock) avg_stock_units from products p
where productid in (select productid from orderdetails o
group by productid
order by sum(unitprice*quantity) desc
limit 5)

QUESTION 7

select orderid, customerid, freight, orderdate from orders o


where employeeid in
(select [Link] from orderdetails o
join orders o2
on [Link] = [Link]
group by employeeid
having sum([Link]*[Link]) > 100000)
order by orderdate desc
limit 10

You might also like