首页 > 代码库 > 【考试】简单的sql语句
【考试】简单的sql语句
1)显示正好为5个字符的员工的姓名
HR@ORA11GR2>select last_name,first_name from employees
2 where length(first_name) = 5;
2)显示不带有"R"的员工的姓名.
HR@ORA11GR2>select last_name,first_name from employees
2 where first_name not like ‘%R%‘;
3)显示所有员工的姓名,用a替换所有"A"
HR@ORA11GR2>select replace (first_name,‘A‘,‘a‘) from employees;
4)显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序
HR@ORA11GR2>select last_name,first_name,job_id,salary from employees
2 order by job_id desc,salary;
5)显示在一个月为30天的情况所有员工的日薪金,忽略余数.
HR@ORA11GR2>select last_name,first_name,trunc(salary/30) daysal from employees;
6)找出员工名字中含有a和e的
HR@ORA11GR2>select distinct(first_name) from employees
2 where first_name like ‘%a%‘ and first_name like ‘%e%‘;
7)select语句的输出结果格式如下:
select * from hr_departments;
select * from hr_emp;
select * from hr_region;
…….
Departments是表名,可以查询tab
HR@ORA11GR2>select ‘select * from ‘||‘hr_‘||tname||‘;‘ as select_from_hr_table from tab where tabtype=‘TABLE‘;
7)要求基本工资大于1500,同时可以领取奖金的雇员信息
HR@ORA11GR2>select * from employees
2 where salary > 1500 and commission_pct is not null;
8)要求显示所有雇员的姓名及姓名的后3个字符
HR@ORA11GR2>select first_name,substr(first_name,-3,3) from employees;
9)求出每个雇员的年薪(应算上奖金)注意处理Null值
HR@ORA11GR2>select last_name,first_name,(salary+salary*nvl(commission_pct,0))*12 yearsal from employees;
10)以年月日方式显示所有员工的受聘日期。
HR@ORA11GR2>select last_name,first_name,to_char(hire_date,‘yyyy-mm-dd‘)
2 from employees;
11)用concat显示所有员工的姓名全称
HR@ORA11GR2>select concat (first_name || chr(32),last_name) from employees;
12)显示所有员工姓名(last_name)倒数第三个字符。
HR@ORA11GR2>select first_name,substr(first_name,-3,1) ename from employees;
13)Hr用户下拼接sql显示如下格式内容:注:index_name字段可以从user_indexes中查询
Alter index “index_name” rebuild;
HR@ORA11GR2>select ‘alter index ‘||index_name||‘ rebuild‘ from user_indexes;
15)查员工表显示如下信息:年终奖是工资+奖金
部门号 姓名 年终奖
HR@ORA11GR2>Select department_id,first_name,last_name,(salary+salary*nvl(commission_pct,0)) commission from employees;
16)显示整个公司的最高工资、最低工资、工资总和、平均工资,保留到整数位。
HR@ORA11GR2>select max(nvl(salary,0)) maxsal,min(nvl(salary,0)) minsal,sum(nvl(salary,0)) sumsal,trunc(avg(nvl(salary,0))) avgsal
2 from employees;
MAXSAL MINSAL SUMSAL AVGSAL
---------- ---------- ---------- ----------
24000 2100 691416 6461
17)哪些部门的人数比32号部门的人数多
SCOTT@ORA11GR2>select deptno,count(empno)
2 from emp
3 group by deptno
4 having count(empno) > (select count(empno) from emp where deptno = 10);
18)查询出比7654工资要高的全部雇员的信息
SCOTT@ORA11GR2>select * from emp
2 where sal > (select sal from emp where empno = 7654);
19)要求查询工资比7654高,同时与7788从事相同工作的全部雇员
SCOTT@ORA11GR2>select * from emp
2 where sal > (select sal from emp where empno = 7654)
3 and deptno in (select deptno from emp where empno = 7788);
20)哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序)
HR@ORA11GR2>select last_name,first_name,salary from employees
2 where salary > (select avg(salary) from employees)
3 order by salary desc;
21)列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
SCOTT@ORA11GR2> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
2 from dept a left join emp b on a.deptno=b.deptno;
22)查询每个员工的领导是谁(自连接)。
SCOTT@ORA11GR2>select a.ename as clerk,b.ename as boss from emp a,emp b where a.mgr=b.empno;
23)要求查询雇员的编号、姓名、部门编号、部门名称及部门位置
SCOTT@ORA11GR2>select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
【考试】简单的sql语句
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。