6. 复杂查询

use sql_store;
-- Find products that are more
-- expensive than Lettuce ( id = 3)
-- 编写子查询
select *
from products
where name like 'Lettuce%';
    
select *
from products
where unit_price > (
  select unit_price
  from products
  where name like 'Lettuce%'
)

-- In sql_hr database:
-- Find employees whose earn more than average
use sql_hr;
select 
  avg(salary) as avergae
from employees;
-- 没有结果
select *
from employees
having salary > avg(salary);
-- 正确答案
select *
from employees
where salary > (
  select 
  avg(salary)
  from employees
  )

use sql_store;
-- Find the products that have never been ordered
select *
from products
where product_id not in (
  select 
    distinct product_id
  from order_items
)

use sql_invoicing;
select *
from clients
where client_id not in(
  select distinct client_id
    from invoices
)

select *
from clients
where client_id not in(
  select distinct client_id
    from invoices
);
select *
from clients
left join invoices using (client_id)
where invoice_id is NULL;

use sql_store;
select 
  customer_id, 
    first_name,
    last_name
from customers c
where c.customer_id in (
  select customer_id
    from orders o
    where o.order_id in (
    select order_id
    from order_items oi
    where oi.product_id = (
      select product_id
      from products p
      where p.name like 'Lettuce%'
    )
    )
)
-- 上面是使用嵌套的方法，同样可以写出连接的方法，这里就不写了

use sql_invoicing;
select *
from invoices
where invoice_total > (
  select max(invoice_total)
  from invoices 
  where client_id = 3
);
select *
from invoices
where invoice_total > ALL (
  select invoice_total
  from invoices 
  where client_id = 3
);

select *
from clients
where client_id in(
  select client_id
  from invoices
  group by client_id
  having count(*) >= 2
);
select *
from clients
where client_id = any(
  select client_id
  from invoices
  group by client_id
  having count(*) >= 2
);

-- Select employees whose salary is
-- above the average in their office
use sql_hr;
select *
from employees e
where salary > (
  select avg(salary)
    from employees
    where office_id = e.office_id
)

-- Get invoices that are larger than the
-- client's average invoice amount
use sql_invoicing;
select *
from invoices i
where invoice_total > (
  select
    avg(invoice_total) as average
  from invoices
    where client_id = i.client_id
);

-- Select clients that have an invoice
select *
from clients
where client_id in (    -- in (1, 2, 3, 5)
  select distinct client_id
    from invoices
);
-- 使用exist
select *
from clients c
where exists (
  select client_id
    from invoices
    where client_id = c.client_id
)

-- Find the products that have never been ordered
use sql_store;
select *
from products p
where not exists(
  select *
    from order_items
    where product_id = p.product_id
)

select
  invoice_id,
    invoice_total,
    (select avg(invoice_total)
    from invoices) as invoice_average,
  invoice_total - (select invoice_average) as difference
from invoices

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

USE sql_invoicing;
select *
from (
  -- 每当我们在form子句中使用子查询，必须要给子查询的结果一个别名
  select
    client_id,
    c.name,
    (select sum(invoice_total)
      from invoices i
      where i.client_id = c.client_id) as total_sales,
    (select avg(invoice_total)
      from invoices i) as average,
    (select total_sales) - (select average) as difference 
  from clients c
) as sales_summary
where total_sales is not null