Scribd
Upload
3 views4 pages

SQL Queries for Customer and Order Analysis

The document contains a series of SQL queries designed to extract various insights from a database. These queries cover customer counts by country, product categories, inventory levels, order statistics, and employee performance. Each question focuses on different aspects of the data, such as filtering, grouping, and aggregating results.

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
3 views4 pages

SQL Queries for Customer and Order Analysis

The document contains a series of SQL queries designed to extract various insights from a database. These queries cover customer counts by country, product categories, inventory levels, order statistics, and employee performance. Each question focuses on different aspects of the data, such as filtering, grouping, and aggregating results.

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 4

QUESTION 1

select country, count(*) numberofcustomers from customers c


where region not in ('TX','OR') or region is NULL
group by country
order by numberofcustomers desc
limit 5

QUESTION 2

select categoryname, count(productid) totalproducts from products p


join categories c
on [Link]=[Link]
group by categoryname
order by totalproducts desc

QUESTION 3

select productid, productname from products


where (unitsinstock+unitsonorder) <= reorderlevel and discontinued=0 and
reorderlevel>1
order by productid
QUESTION 4

select count(*) orders1997 from orders o


join shippers s
on [Link]=[Link]
where companyname in ('United Package', 'Speedy Express') and shippeddate
between '01-01-1997' and '12-31-1997'

QUESTION 5

select shipcountry, round(avg(freight)) avg_freight from orders o


join shippers s
on [Link]=[Link]
where companyname='Speedy Express' and shippeddate between '01-01-1996'
and '12-31-1996'
group by [Link]
order by avg_freight desc
limit 3

QUESTION 6

select lastname, firstname, count(*) lateorders from orders o


join employees e
on [Link]=[Link]
where requireddate <= shippeddate
group by lastname, firstname
having count(*) >= 5
order by lateorders desc;
QUESTION 7

select companyname, [Link], round(sum(quantity*unitprice)) totalorderamount


from orderdetails o
join orders o2
on [Link]=[Link] and [Link] between '01-01-1996' and '12-31-1996'
join customers c
on [Link]=[Link]
group by [Link], [Link]
having sum(quantity*unitprice) >= 10000

QUESTION 8

select companyname from customers c


left join orders o
on [Link]=[Link]
where [Link] is null

QUESTION 9

select [Link], [Link] from employeeterritories e


full join territories t
on [Link]=[Link] where [Link] is null or [Link] is null;

QUESTION 10
select companyname from shippers s
left join orders o
on [Link] =[Link]
where [Link] is null

QUESTION 11

select country from customers c


left join orders o
on [Link] = [Link]
where [Link] is null

QUESTION 12

select [Link], count(*) orders_per_emp from orders o


right join employees e
on [Link] = [Link]
group by [Link]
order by [Link]

You might also like