2021-08-21练习mysql

use sql_store;
-- select distinct customer_id,first_name,last_name
-- from order_items
-- left join products using(product_id)
-- left join orders using(order_id)
-- left join customers using(customer_id)
-- where product_id = 3;

-- select  customer_id,first_name,last_name
-- from customers
-- where customer_id in (select customer_id from orders where order_id in (select order_id from order_items where product_id = 3))

select  customer_id,first_name,last_name
from customers
where customer_id in (select orders.customer_id from orders join order_items using(order_id) where product_id = 3)

use sql_invoicing;

select *
from invoices
where invoice_total >(
select max(invoice_total)
from invoices
where client_id = 3)

use sql_invoicing;

select *
from invoices
where invoice_total > all(
select invoice_total
from invoices
where client_id = 3

select *
from invoices i
where invoice_total > (
select avg(invoice_total)
from invoices
where client_id = i.client_id)

select product_id
from products
where product_id not in (select distinct product_id from order_items)

select *
from products
where not exists (select distinct product_id from order_items where products.product_id = order_items.product_id)

use sql_invoicing;

select 
	client_id,
    name,
    (select sum(invoice_total) from invoices where client_id = clients.client_id) as total_sales,
    (select avg(invoice_total) from invoices) as average,
    (select total_sales - average)
from clients

use sql_invoicing;

select 
	client_id,
    name,
    (select sum(invoice_total) from invoices where client_id = clients.client_id) as total_sales,
    (select avg(invoice_total) from invoices) as average,
    (select total_sales - average) as difference
from clients