SQL_midterm
Họ và tên: Nghiêm Xuân Diện
MSSV: 20215007
Lớp: 139353
1. Display top 10 best-revenue products (the best- revenue
products mean that the products give the highest revenue;
revenu = quantity * price)
select [Link], sum([Link] * [Link]) as revenue
from products p
join orderlines ol on p.prod_id = ol.prod_id
group by p.prod_id, [Link]
order by revenue desc
limit 10;
2. The average income of the customers who purchased the
product titled “AIRPORT ROBBERS”
select avg([Link]) as average_income
from customers c
join orders o on [Link] = [Link]
join orderlines ol on [Link] = [Link]
join products p on ol.prod_id = p.prod_id
where [Link] = 'AIRPORT ROBBERS';
3. Give a list of customers who have never ordered any product
in December 2004.
select [Link], [Link], [Link]
from customers c
where not exists (
select 1
from orders o
where [Link] = [Link]
and [Link] >= '2004-12-01'::date
and [Link] < '2005-01-01'::date
);
4. Give a list of the country names, their number of customers
who have purchased at least 2 times
select [Link], count(distinct [Link]) as
customer_count
from customers c
join orders o on [Link] = [Link]
group by [Link]
having count(distinct [Link]) >= 2;
5. Please list the orders in which both products are ordered
titled “ADAPTATION SECRETS” and “AFFAIR GENTLEMENT”
select [Link]
from orders o
join orderlines ol1 on [Link] = [Link]
join orderlines ol2 on [Link] = [Link]
join products p1 on ol1.prod_id = p1.prod_id
join products p2 on ol2.prod_id = p2.prod_id
where [Link] = 'ADAPTATION SECRETS' and [Link] =
'AFFAIR GENTLEMENT';
6. Show detailed information of products in the latest order:
orderlineid, prod_id, product title, quantity, unit price
(with currency unit), amount (with currency unit)
select [Link], p.prod_id, [Link] as
product_title, [Link],
concat([Link], ' USD') AS unit_price,
concat(([Link] * [Link]), ' USD') AS amount
from orderlines ol
join products p ON ol.prod_id = p.prod_id
where [Link] = (
select orderid
from orders
order by orderdate desc
limit 1
);
7. Display the maximum, minimum and average price of the
products in the store
select max(price) as max_price, min(price) as min_price,
avg(price) as avg_price
from products;
8. List of products that have been ordered by the current date.
select [Link] as product_title
from products p
join orderlines ol on p.prod_id = ol.prod_id
join orders o on [Link] = [Link]
where [Link] = current_date;
9. Provide a list of female customers whose income is at least
10000
select firstname, lastname, income
from customers
where gender = 'F' and income >= 10000;
10. Display a list of the most expensive products that have been
purchased by a male customer
select [Link] as product_title, [Link]
from products p
join orderlines ol on p.prod_id = ol.prod_id
join orders o on [Link] = [Link]
join customers c on [Link] = [Link]
where [Link] = 'M'
order by [Link] desc
limit 10;
11. Please indicate the number of orders that each customer has
ordered. The list must contain customer ID, customer
fullname, number of orders. Sort in descending order of the
number of orders.
select [Link], concat([Link], ' ', [Link])
as fullname, count([Link]) as num_orders
from customers c
join orders o on [Link] = [Link]
group by [Link], fullname
order by num_orders DESC;
12. Add a new column “amount” (number) into the table
“orderlines” to store the amount paid for the corresponding
product in this order. Write a SQL statement to update the
correct value for this columns
alter table orderlines add column amount numeric;
update orderlines ol
set amount = [Link] * [Link]
from products p
where ol.prod_id = p.prod_id;
