Web DB III

select customers.CustomerName, customers.Country, orders.OrderId, orders.OrderDate 
from orders inner join customers on orders.CustomerId = customers.CustomerId


select orders.OrderId, orders.OrderDate, employees.FirstName, employees.LastName
from orders 
inner join employees on orders.employeeId = employees.employeeId


select orders.OrderId, orders.OrderDate, (employees.FirstName || ' ' || employees.LastName) as SoldBy
from orders 
inner join employees on orders.employeeId = employees.employeeId


-- all customers that have no orders
select * from customers
-- inner join orders on customers.customerId = orders.customerId -- 196 records
left join orders on customers.customerId = orders.customerId -- 213 records
where orders.orderId is null
-- order by 1


select c.customerId, o.orderDate 
from customers as c
left join orders as o
    on c.customerId = o.customerId
where o.orderId is not null


select o.OrderId, o.OrderDate, c.CustomerName, s.ShipperName, e.FirstName 
from orders as o
inner join customers as c on o.customerId = c.customerId
inner join employees as e on o.employeeId = e.employeeId
inner join shippers as s on s.shipperId = o.shipperId


-- how many items where ordered on each order
select o.orderId, count(*) as ItemsOrderedCount
from orders as o
inner join orderDetails as od on o.orderId = od.orderId
group by o.orderId
order by ItemsOrderedCount desc


-- revenue by product. hint: sum(), only the top 5
select p.ProductName, round(sum(od.quantity * p.price), 2) as Revenue
from products as p inner join orderDetails as od on p.productId = od.productId
group by p.ProductName
order by Revenue desc
-- limit 5
-- offset 5