0、显示正好为5个字符的员工的姓名
SQL> select concat(first_name,last_name) name from employees where length(concat(first_name,last_name))=5;
NAME
---------------------------------------------
KiGee
或者
SQL> select concat(first_name,last_name) name from employees where concat(first_name,last_name) like '_____';
NAME
---------------------------------------------
KiGee
1、 显示不带有"R"的员工的姓名.
SQL> select first_name from employees where first_name not like '%R%';
2、 显示所有员工姓名的前三个字符.
SQL> select substr(first_name,1,3) name from employees;
3、 显示所有员工的姓名,用a替换所有"A"
SQL> select replace(first_name,'A','a') name from employees;
4、 显示员工的详细资料,按姓名排序
Desc employees;
Select * from employees order by 2;
或者
Select * from employees order by first_name;
5、 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
SQL> select first_name,last_name,hire_date from employees
2 order by 3 ;
6、 显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序
SQL> select first_name,job_id,salary from employees order
by salary desc,salary;
7、 显示在一个月为30天的情况所有员工的日薪金,忽略余数.
select first_name,trunc(salary/30,0) rixin from employees;
8、 显示姓名字段的任何位置包含"A"的所有员工的姓名.
SQL> select first_name from employees where first_name
like ’%A%’;
9、 找出员工名字中含有a和e的
SQL> select first_name name from employees where
2 first_name like '%a%e%';
10、50部门有哪些职位
SQL> select job_id,first_name,department_id from employees
2 where department_id =50;
11、除了Sales部门,还有哪些部门,列出部门号、部门名称。
SQL> select department_id,department_name from departments
2 where department_name !='Sales';
12、select语句的输出结果格式如下:
select * from hr_departments;
select * from hr_emp;
select * from hr_region;
…….
Departments是表名,可以查询tab
SQL> desc tab;
Name Null? Type
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
SQL> select 'select * '|| 'from' || tname || ';' as "select" from tab;
13. 查看每月可以得到奖金的雇员信息
SQL> select first_name,salary*commission_pct jiangjin from
2 employees where salary*commission_pct is not null;
14. 要求基本工资大于1500,同时可以领取奖金的雇员信息
SQL> select first_name,salary,commission_pct from employees
2 where salary >= 1500 and commission_pct is not null;
15. 查询基本工资大于1500,同时不可以领取奖金的雇员信息
SQL> select first_name,salary,commission_pct from employees
2 where salary >= 1500 and commission_pct is null;
16.查询在1981年雇佣的全部雇员信息
SQL> select first_name,to_char(hire_date,'yyyy') hire from
2 employees where to_char(hire_date,'yyyy') = 1981;
或者
SQL> select first_name,salary,hire_date from employees where
2 hire_date between '01-JAN-81' and '31-DEC-81';
17.要求查询出雇员编号不是 7369、7499的雇员信息
SQL> select first_name,employee_id from employees where
2 employee_id not in (7369,7499);
18. 查看雇员编号不是7369的雇员信息
SQL> select first_name,salary,employee_id from employees
2 where employee_id != 7369;
19. 运行以下这个语句并解释
SELECT substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;
SQL> select substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;
SUB LENGTH('HELLO') REPLA
--- --------------- -----
hel 5 hexxo
SUB字段:将hello从第1位开始数3位输出,其余都截断;
LENGTH('HELLO')字段:数HELLO的字符长度输出;
REPLA字段:将hello字符中的l全部替换为x;
20.说明substr从第0个字符和从第1个字符开始截取字段,有什么区别
从第0个字符和从第1个字符开始截取只要要截取的位数是一样的,则输出结果就一样:
示例如下:
SQL> select substr('hello',0,1) FROM dual;
S
-
h
SQL> select substr('hello',0,2) FROM dual;
SU
--
he
SQL> select substr('hello',0,0) FROM dual;
S
-
SQL> select substr('hello',1,0) FROM dual;
S
-
SQL> select substr('hello',1,1) FROM dual;
S
-
h
SQL> select substr('hello',1,2) FROM dual;
SU
--
he
21. 要求显示所有雇员的姓名及姓名的后3个字符
SQL> select last_name,substr(last_name,-3,3) name from employees;
22. 求出从雇用日期到今天所有雇员的雇员编号、姓名和月数
SQL> select first_name,employee_id,(sysdate-hire_date)/30 ys
2 from employees order by 3;
23.说明以下语句最后输出的结果:
SQL> SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;
SQL> SELECT NEXT_DAY(SYSDATE,'MON') FROM DUAL;
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
SQL> SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;
ADD_MONTH
---------
03-JAN-17 (2016-9-3,月数加4变成03-JAN-17)
SQL> SELECT NEXT_DAY(SYSDATE,'MON') FROM DUAL;
NEXT_DAY(
---------
05-SEP-16(意2016-9-3是星期六,求下一个星期一是几号,则输出05-SEP-16)
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY(
---------
30-SEP-16(意2016-9-3的这个月的最后一天是几号,则输出30-SEP-16)
24. 求出每个雇员的年薪(应算上奖金)注意处理Null值
SQL> select first_name,12*(salary+salary*nvl(commission_pct,0)) as nianxin from employees order by 2;
或者
select first_name,salary*12+(salary*nvl(commission_pct,0))*12 nianxin from employees order by 2;
25. 找出有奖金的员工的不同工作
SQL> select distinct job_id,commission_pct from
2 employees where commission_pct is not null;
26. 找出在2月受聘的员工
SQL> select first_name,to_char(hire_date,'MM') hire from
2 employees where to_char(hire_date,'MM') =2;
27.以年月日方式显示所有员工的受聘日期。
SQL> select first_name,to_char(hire_date,'yyyy-mm-dd') hire
2 from employees;
28.用concat显示所有员工的姓名全称
SQL> col FIRST_NAME for a10
SQL> col LAST_NAME for a10
SQL> select first_name,last_name,concat(first_name,last_name) name from employees;
29.显示所有员工姓名(last_name)倒数第三个字符。
SQL> select last_name,substr(last_name,-3,1) name from employees;
或
select last_name,substr(last_name,length(last_name) -2,1) from employees;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2127033/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2127033/