(day1)数据库的安装以及简单的命令

1.查看s_emp表结构;

desc s_emp;

2.查询s_emp所有信息;

select * from s_emp;

select * from dba_tables;

select * from user_tables;

3.查询s_emp表中所有员工的部门编号、工资;

select id "编号",first_name||'.'||last_name "姓名", salary "工资" from s_emp;

4.查询s_emp表中所有员工的年薪;

select last_name,salary+(salary*NVL(commission_pct,0)) "总工资" from s_emp;
selsct last_name ,salary+commission from s_emp;
selsct last_name ,salary+commission_pct from s_emp;
SELECT last_name, salary, 12 * (salary + 100) FROM s_emp;
select first_name , salary+(salary*NVL(commission_pct,0)) "总工资" from s_emp;

6.查询s_emp表中所有员工的工资;

select first_name,salary from s_emp;
select first_name ,commission from ;

5.查询s_emp表中所有员工姓名（给列起别名，使用连接符）;

SELECT first_name||'.'||last_name "姓名" from s_emp;
select id "编号",first_name||'.'||last_name "姓名" ,salary from s_emp;

7.查询s_dept表的部门名称（去掉重复行distinct）;

select * from s_dept;
select distinct name from s_dept;

8.查询s_emp表中所有的部门ID及职称（去掉多列重复行）;

select * from s_emp;
select manager_id "部门ID",
select distinct dept_id, title from s_emp;

9.查询s_emp表要求输出员工姓名(firs_name、last_name)和实际工资(基本工资+提成);

SELECT first_name||'.'||last_name "姓名" ,salary+(salary*NVL(commission_pct,0)) "总工资" from s_emp;

10.查询s_emp表中dept_id为41的员工;

select dept_id from s_emp;
select * from s_emp where dept_id=41;

11.请查询出s_emp表中last_name为Smith的员工的信息

select *from s_emp where last_name like 'Smith';

12.请查询出s_emp表中部门ID为50并且工资大于1500的员工的信息;

select *from s_emp;

select *from s_emp where dept_id = 50 and salary > 1500;

13.请查询出s_emp表中工资在1500到2000之间的员工信息（between and）;(包括边界)

select * from s_emp where salary between 1400 and 2500;

14.请查询出s_dept表中region_id为1，3的部门信息;

select * from s_dept;
第一种
select *from s_dept where region_id in(1,3);
第二种
select *from s_dept where region_id=1 or region_id=3;

15.查询s_dept表所有的所有部门的名称;

select distinct name from s_dept ;

16.查询s_emp表中工资为1500，2000，1550的员工信息。

select *from s_emp where salary in(1500,2000,1550,2500);