Execise03
1.显示符合以下条件的书籍的名称:
(1)购买于2001年1月21日之前;
(2)价格低于500美元或超过900美元.
2.检查EMPLOYEES表的结果
EMP_ID NUMBER(4) not null 主键
LAST_NAME VARCHAR2(30) not null
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2) 外键关联到表DEPT(表DEPT包括DEPT_ID、DEPT_NAME两个字段)
JOB_CAT VARCHAR2(30) [说明]JOB_CAT为工作类别
SALARY NUMBER(8,2) 0-50000之间
(1)创建表EMPLOYEES和DEPT,满足题目中的要求,并给出测试数据
(2)编写一个语句,只有当最低工资少于5000而且最高工资超过15000时,才显示部门ID以及该部门支付的最低工资和最高工资.
3.参照上题给出的EMPLOYEES表,编写一个语句,显示各部门的每个工作类别中支付的最高工资.
SELECT book_name from book
where b_date<'21-1月-01' and b_price <500 or b_price >900;
create table employees
(
emp_id number(4) not null primary key,
last_name varchar2(30) not null,
first_name varchar2(30),
dept_id number(30),
job_cat varchar2(30),
salary number(8,2),
constraint dept_id_fk_employees foreign key(dept_id) references dept1(dept_id),
constraint salary_ck_employees check(salary between 0 and 50000)
);
create table dept1
(
dept_id number(30) not null primary key,
dept_name varchar2(30)
);
insert into dept1 values(10,'销售部');
insert into dept1 values(20,'技术部');
insert into dept1 values(30,'生产部');
insert into dept1 values(40,'人力资源部');
insert into dept1 values(50,'市场部');
select * from dept1;
insert into employees values('0001','一','刘',10,'销售人员',2000);
insert into employees values('0002','三','张',20,'技术径路',6000);
insert into employees values('0003','四','李',30,'生产人员',1500);
insert into employees values('0004','五','王',40,'人力专员',1800);
insert into employees values('0005','六','马',50,'市场经理',5000);
insert into employees values('0006','七','赵',50,'市场经理',25000);
insert into employees values('0008','双','吴',10,'销售人员',1500);
insert into employees values('0009','号','吴',20,'技术人员',3500);
insert into employees values('0010','号','张',20,'技术人员',1500);
insert into employees values('0011','元','张',20,'技术人员',1700);
insert into employees values('0012','元','王',30,'生产人员',2400);
insert into employees values('0013','凯','王',30,'生产人员',2700);
select tb1.id as 部门号,min(tb1.sal) as 最低工资,max(tb1.sal) as 最高工资
from (select dept_id as id,salary as sal from employees where salary<5000 or salary>=1500)tb1
group by tb1.id;
select job_cat 工作类别,dept_id 部门号,max(salary) as 最高工资 from employees
group by job_cat,dept_id;