SQL简单语句总结习题
作者:网络转载 发布时间:[ 2016/3/30 11:03:59 ] 推荐标签:数据库 SQL
创建一个表记员工个人信息:
--创建一个表
create table plspl_company_info(
empno number(5) not null,
ename varchar2(10) not null,
job varchar2(10),
manager number(5),
hiredate date,
sal number(5),
comm number(5),
deptno number(3)
);
insert into plspl_company_info values (7369, 'SMITH', 'CLERK', 7902, date '1980-12-17', 800, NULL, 10);
insert into plspl_company_info values (7293, 'ALLEN', 'SALESMAN', 7689, date '1981-03-27', 1867, NULL, 20);
insert into plspl_company_info values (7562, 'JAMES', 'SALESMAN', 7689, date '1981-09-11', 1796, NULL, 20);
insert into plspl_company_info values (7936, 'JONES', 'ANAYST', 7656, date '1980-09-01', 3250, NULL, 30);
insert into plspl_company_info values (7688, 'WEST', 'MANAGER', 7839, date '1981-02-28', 2985, 900, 40);
insert into plspl_company_info values (7499, 'PAUL', 'MANAGER', 7839, date '1980-03-26', 3600, NULL, 40);
insert into plspl_company_info values (7778, 'FORD', 'CLERK', 7902, date '1987-04-17', 960, NULL, 10);
insert into plspl_company_info values (7289, 'ADAMS', 'SALESMAN', 7689, date '1980-01-09', 1956, NULL, 20);
insert into plspl_company_info values (7531, 'MATIN', 'SALESMAN', 7689, date '1980-05-14', 1906, NULL, 20);
insert into plspl_company_info values (7916, 'KING', 'ANAYST', 7656, date '1982-06-04', 2864, NULL, 30);
insert into plspl_company_info values (7365, 'BLKAE', 'CLERK', 7902, date '1981-09-11', 1200, 1100, 10);
insert into plspl_company_info values (7784, 'CHRIS', 'CLERK', 7902, date '1981-07-16', 1376, NULL, 10);
select * from plspl_company_info;
执行结果:
SQL>
Table created
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
EMPNO ENAME JOB MANAGER HIREDATE SAL COMM DEPTNO
------ ---------- ---------- ------- ----------- ------ ------ ------
7369 SMITH CLERK 7902 1980/12/17 800 10
7293 ALLEN SALESMAN 7689 1981/3/27 1867 20
7562 JAMES SALESMAN 7689 1981/9/11 1796 20
7936 JONES ANAYST 7656 1980/9/1 3250 30
7688 WEST MANAGER 7839 1981/2/28 2985 900 40
7499 PAUL MANAGER 7839 1980/3/26 3600 40
7778 FORD CLERK 7902 1987/4/17 960 10
7289 ADAMS SALESMAN 7689 1980/1/9 1956 20
7531 MATIN SALESMAN 7689 1980/5/14 1906 20
7916 KING ANAYST 7656 1982/6/4 2864 30
7365 BLKAE CLERK 7902 1981/9/11 1200 1100 10
7784 CHRIS CLERK 7902 1981/7/16 1376 10
12 rows selected
SQL>
习题:
1,选出部门30里的所有员工信息
select * from plspl_company_info where deptno = 30;
2,列出所有办事员(CLERK)的姓名,编号和部门编号
select ename, empno , deptno from plspl_company_info where job = 'CLERK';
3,找出薪金高于佣金60%的员工
select * from plspl_company_info where comm > sal*0.6 ;
4,找出部门10的所有经理(MANAGER)和部门20的所有办事员(CLERK)
select * from plspl_company_info where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') ;
5,找出部门10的所有经理(MANAGER)和部门20的所有办事员(CLERK),以及既不是经理有不是办事员,但薪金大于或等于2000的所有员工的详细资料
select *
from plspl_company_info
where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or ((job not in('MANAGER', 'CLERK')) and sal >= 2000) ;
6,找出收取佣金的员工的不同工作
select distinct job from plspl_company_info where comm is not null ;
7,找出不收取佣金或者收取佣金低于100的员工
select * from plspl_company_info where (comm is null) or comm < 100 ;
8,显示不带“R”的员工姓名
select * from plspl_company_info where ename not like '%A%';
9,显示姓名字段的任何位置包含'A'的所有员工的姓名,显示结果按照基本工资由高到低;如果工资相同,则按照雇佣年限由早到晚排序;如果雇佣时间相同,则按照职位排序
select * from plspl_company_info where ename like '%A%' order by sal DESC, hiredate, job;
相关推荐
更新发布
功能测试和接口测试的区别
2023/3/23 14:23:39如何写好测试用例文档
2023/3/22 16:17:39常用的选择回归测试的方式有哪些?
2022/6/14 16:14:27测试流程中需要重点把关几个过程?
2021/10/18 15:37:44性能测试的七种方法
2021/9/17 15:19:29全链路压测优化思路
2021/9/14 15:42:25性能测试流程浅谈
2021/5/28 17:25:47常见的APP性能测试指标
2021/5/8 17:01:11