-
-
Save bangarangler/f4cc98b4c2312751e844aea4601053d5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment