--1.单行子查询
--查找那些均价不是最高的产品类的id和均价
select product_type_id,
avg(price)
from products
group by product_type_id
having avg(price) < (select max(avg(price))
from products
group by product_type_id
) --单行子查询,也就是返回一行的子查询
order by product_type_id;
with p
as
(
select product_type_id,
avg_price,
rank() over(order by avg_price desc) rank_num
from
(
select product_type_id,
avg(price) as avg_price
from products
group by product_type_id
)
)
select *
from p
where rank_num >= 2;
--每个产品的购买次数
select pd.product_id,
pd.price,
p.purchase_count
from products pd
inner join
(
select product_id,
count(*) as purchase_count
from purchases
group by product_id
)p --内联视图
on p.product_id = pd.product_id
--子查询不能包含order by,下面的语句会报错
select *
from products
where price > (select avg(price)
from products
order by product_id);
--2.多行子查询
--购买过的产品名称包含字母e的产品
select *
from products
where name like '%e%'
and product_id in (select product_id from purchases);
--没有购买过的产品
select
from products
where product_id not in (select product_id from purchase);
--员工的工资低于任意一个工资级别的最低工资
select employee_id,
last_name,
salary
from employees
where salary < any(select low_salary from salary_grades);
--员工的工资高于所有工资级别的最高工资
select employee_id,
last_name,
salary
from employees
where salary > all(select high_salary from salary_grades);
--3.多列子查询
--查询那些产品价格是所在产品类中最低的产品
select *
from products
where (product_type_id,price) in (select product_type_id,min(price)
from products
group by product_type_id
);
--4.相关子查询
--找出产品价格大于同类产品的均价的产品
select product_id,
product_type_id,
name,
price
from products p1
where price > (select avg(price)
from products p2
where p1.product_type_id = p2.product_type_id);
select p1.product_id,
p1.product_type_id,
p1.name,
p1.price
from products p1
inner join
(
select product_type_id,
avg(price) as avg_price
from products
group by product_type_id
)p2
on p2.product_type_id = p1.product_type_id
and p1.price > p2.avg_price;
--有下属的人员
select employee_id,
last_name
from employees e
where exists (select 1
from employees ee
where e.employee_id = ee.manager_id);
select distinct
e.employee_id,
e.last_name
from employees e
inner join employees ee
on e.employee_id = ee.manager_id
--没有购买过的产品
select p.product_id,
p.name
from products p
where not exists(select 1
from purchases pa
where pa.product_id = p.product_id);
select p.product_id,
p.name
from products p
where product_id not in(select product_id
from purchases pa);
--找出不包含任何产品的产品类型
select *
from product_types pt
where not exists (select 1
from products p
where p.product_type_id = pt.product_type_id );
--这个表面看没什么问题,但其实是错误的,因为这里的多行子查询的返回值中包含了null
select *
from product_types pt
where product_type_id not in (select product_type_id from products);
--正确的
select *
from product_types pt
where product_type_id not in (select nvl(product_type_id,0) from products);
--5.嵌套子查询
--查找出那些产品所属品类的均价小于,多次购买的产品所属品类中最高的均价,的产品的品类和均价
select product_type_id,
avg(price)
from products
group by product_type_id
having avg(price) < (select max(avg(price))
from products
where product_id in (select product_id
from purchases
where quantity > 1)
group by product_type_id);
--6.包含子查询的update、delete语句
--把员工号为4的员工的工资,修改为所有工资级别的最高工资的平均值
update employees
set salary = (select avg(high_salary)
from salary_grades)
where employee_id = 4;
--删除工资大于所有工资级别的最高工资的平均值的员工
delete from employees
where salary > (select avg(high_salary)
from salary_grades
);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/653220/viewspace-1982358/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/653220/viewspace-1982358/