下订单超过4次的女顾客查询:
select c.customer_id, count(o.order_id) as orders_ct
from oe.customers c
join oe.orders o
on c.customer_id = o.customer_id
where c.gender = 'F'
group by c.customer_id
having count(o.order_id) > 4
order by orders_ct, c.customer_id
注意:group by子句
select 列表中的任何非聚合字段都必须包括在group by 表达式中 group by 还可以包含两个附加运算 ROLLUP和CUBE
标量子查询:在select中使用另外一个select 语句来产生结果中的一列值,这个查询只能返回一行一列的值,这种类型的查询称为标量子查询
例子:
select c.customer_id, c.cust_first_name||' '||c.cust_last_name,
(select e.last_name
from hr.employees e
where e.employee_id = c.account_mgr_id) acct_mgr
from oe.customers c;
子查询插入:
insert into scott.bonus (ename, job, sal)
select ename, job, sal * .10
from scott.emp;
多表插入:
insert all
--小于10000插入small_customers
when sum_orders < 10000 then
into small_customers
--介于10000~100000插入medium_customers
when sum_orders >= 10000 and sum_orders < 100000 then
into medium_customers
--大于100000插入 large_customers
else
into large_customers
select customer_id, sum(order_total) sum_orders
from oe.orders
group by customer_id ;
update语句:
--创建表利用已有表的数据
create table employees2 as select * from hr.employees ;
--添加主键
alter table employees2 add constraint emp2_emp_id_pk primary key (employee_id) ;
--1.使用表达式更新一个单列的值
update employees2 set salary = salary * 1.10 -- increase salary by 10%
where department_id = 90 ;
--2.通过子查询更新一个单列的值
update hr.employees employees set salary =
(select employees2.salary from employees2 where employees2.employee_id =
employees.employee_id and employees.salary != employees2.salary)
where department_id = 90 ;
--3.通过在where子句中使用子查询确定要更新的单列的值
update hr.employees set salary = salary * 1.10 where department_id
in (select department_id from departments where department_name = 'Executive') ;
--4.通过select语句定义表及列的值来更新表
update (select e1.salary, e2.salary new_sal
from hr.employees e1, employees2 e2
where e1.employee_id = e2.employee_id and e1.department_id = 90)
set salary = new_sal;
--5.通过子查询来更新多列
update hr.employees employees
set (salary, commission_pct) =
(select employees2.salary, .10 comm_pct
from employees2 where employees2.employee_id =
employees.employee_id and employees.salary != employees2.salary)
where department_id = 90 ;
delete语句:
--1.通过where 字句筛选的条件进行删除
delete from employees2 where department_id = 90;
--2.使用from子句的子查询删除
delete from (select * from employees2 where department_id = 90);
--3.通过where 字句中的子查询来进行删除
delete from employees2 where department_id in
(select department_id from hr.departments where department_name = 'Executive');
merge语句:同时完成了插入,更新,删除
merge into dept60_bonuses b
using (
select employee_id, salary, department_id
from hr.employees
where department_id = 60) e
on (b.employee_id = e.employee_id)
when matched then
update set b.bonus_amt = e.salary * 0.2
where b.bonus_amt = 0
delete where (e.salary > 7500)
when not matched then
insert (b.employee_id, b.bonus_amt)
values (e.employee_id, e.salary * 0.1)
where (e.salary < 7500);