SQL练习

最新推荐文章于 2022-12-08 18:27:52 发布

Klay_Wei

最新推荐文章于 2022-12-08 18:27:52 发布

阅读量234

点赞数

文章标签：数据仓库

本文链接：https://blog.youkuaiyun.com/baidu_35525949/article/details/120076041

版权

本文详细介绍了SQL查询技巧，包括获取员工姓名、部门和薪资，部门薪资统计，最高薪资员工，新部门加入后的员工列表，部门员工数量筛选，以及索引优化对SQL性能的影响。涉及表连接、聚合函数、索引创建和维护等知识点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

The questions asking for select statements are based upon tables which look like the following。

Create table emp_name
(
  emp_id number(10) primary key,
  emp_name varchar2(20) not null,
  dept_id  number(10)
);
Create table dept_name
(
  Dept_id number(10) primary key,
  Dept_name varchar2(20)
);
Create table emp_salary
(
  Emp_id number(10),
  Salary number(10,2),
  Bonus  number(10),
  salary_date date
);

emp_name表插入数据

insert into emp_name values( 1, 'EMP_1', 1);
insert into emp_name values( 2, 'EMP_2', 1);
insert into emp_name values( 3, 'EMP_3', 2);
insert into emp_name values( 4, 'EMP_4', 2);
insert into emp_name values( 5, 'EMP_5', 3);
insert into emp_name values( 6, 'EMP_6', 3);
insert into emp_name values( 7, 'EMP_7', 4);
insert into emp_name values( 8, 'EMP_8', 4);
insert into emp_name values( 9, 'EMP_9', 5);
insert into emp_name values( 10, 'EMP_10', 5);
insert into emp_name values( 11, 'EMP_11', 6);
insert into emp_name values( 12, 'EMP_12', 6);
insert into emp_name values( 13, 'EMP_13', 7);
insert into emp_name values( 14, 'EMP_14', 7);
insert into emp_name values( 15, 'EMP_15', 8);
insert into emp_name values( 16, 'EMP_16', 8);
insert into emp_name values( 17, 'EMP_17', 1);
insert into emp_name values( 18, 'EMP_18', 3);
insert into emp_name values( 19, 'EMP_19', 4);
insert into emp_name values( 20, 'EMP_20', 6);
commit;

dept_name表插入数据

insert into dept_name values(1,'DEPTA');
insert into dept_name values(2,'DEPTB');
insert into dept_name values(3,'DEPTC');
insert into dept_name values(4,'DEPTD');
insert into dept_name values(5,'DEPTE');
insert into dept_name values(6,'DEPTF');
insert into dept_name values(7,'DEPTG');
insert into dept_name values(8,'DEPTH');
commit;

emp_salary表插入数据

insert into emp_salary values (1,2000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (2,3000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (3,500, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (4,1300, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (5,1800, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (6,3500, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (7,3200, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (8,3000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (9,2000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (10,2000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (11,3200, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (12,5000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (13,5500, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (14,3000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (15,3050, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (16,3000, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (17,2600, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (18,null, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (19,null, null,to_date('2015-10-1','yyyy-mm-dd'));
insert into emp_salary values (20,null, null,to_date('2015-10-1','yyyy-mm-dd'));
commit;

Section I SQL
1.Please write SQL for following requirements:
(a)Write a SQL statement to get all the employee’s name, department name and salary.

select a.emp_name,b.dept_name, c.salary
  from dept_name b,emp_name a
 left join emp_salary c on a.emp_id = c.emp_id
 where 1 = 1
   and a.dept_id = b.dept_id;

(b) Write a SQL statement to get the total number of employees, total salary and average salary of department DEPTA.

with a0 AS
(
select a.emp_name,b.dept_name, c.salary
  from dept_name b,emp_name a
 left join emp_salary c on a.emp_id = c.emp_id
 where 1 = 1
   and a.dept_id = b.dept_id
   and b.dept_name = 'DEPTA'
)
select 
	dept_name, 
	count(emp_name) total, 
	sum(salary) as total_salary, 
	avg(salary) avg_salary
  from a0
 group by dept_name;

with a1 AS(
select a.emp_name,b.dept_name, c.salary
  from dept_name b,emp_name a
 left join emp_salary c on a.emp_id = c.emp_id
 where 1 = 1
   and a.dept_id = b.dept_id
)
select emp_name, salary 
  from
(select emp_name, salary,
       row_number()over(order by salary desc) rn
  from a1
 where 1 = 1
   and salary is not null)
where rn = 1;

Now a new department DEPTI with dept_id 9 is added to table dept_name, no change on other two tables. Write a SQL statement to get all the employees and departments.

select a.emp_name,b.dept_name
  from emp_name a, dept_name b
 where 1 = 1
   and a.dept_id = b.dept_id;

Write a SELECT statement to get a list of department names with more than 10 employees.

with a0 AS
(
select a.emp_name,b.dept_name, c.salary
  from  dept_name b,emp_name a
 left join emp_salary c on a.emp_id = c.emp_id
 where 1 = 1
   and a.dept_id = b.dept_id
),
a1 as 
(select dept_name, count(emp_name) total, sum(salary) as total_salary, avg(salary) avg_salary
  from a0
 group by dept_name
 )
select dept_name, total
  from a1 
 where total >= 10;

Write SQL to list the top 10 employees by salary from highest to lowest, by alpha order

with a4 AS
(
select a.emp_name, b.dept_name, c.salary
  from  dept_name b,emp_name a
 left join emp_salary c on a.emp_id = c.emp_id
 where 1 = 1
   and a.dept_id = b.dept_id
   and c.salary is not null
)
select emp_name,salary
  from 
(select a4.*,
	   row_number()over( order by salary desc ) rn
  from a4)
 where rn <=10
 order by emp_name;

Write SQL to list the employees making the top 10

with a5 AS
(select emp_name,salary,
	   row_number()over(order by salary desc) rn
  from emp_name a, emp_salary b
 where 1 = 1 
   and a.emp_id = b.emp_id
   and salary is not null
 )
 select emp_name,salary
   from a5
  where rn <=10;

Write a SQL statement for have such kind of a list:

Emp_name   dept_name   salary       dept_max_salary
EMP_1      DEPTA        2000        3000
EMP_17     DEPTA        2600        3000
EMP_2      DEPTA        3000        3000
EMP_3      DEPTB        500         1300
EMP_4      DEPTB        1300        1300
………
………

with a6 as
(select a.emp_name,b.dept_name, c.salary
  from  dept_name b,emp_name a
 left join emp_salary c on a.emp_id = c.emp_id
 where 1 = 1
   and a.dept_id = b.dept_id)
,a61 as
(select  dept_name,salary as dept_max_salary
  from   
(select dept_name,salary,
       row_number()over(partition by dept_name order by salary desc) as flag
  from a6 
 where 1 = 1
   and salary is not null)
where 1 = 1 
  and flag = 1) 
select a6.emp_name, a6.dept_name,salary,dept_max_salary
  from a6
 left join a61 on a6.dept_name = a61.dept_name
 order by  a6.dept_name,a6.emp_name;

Create index IDX_EMP_SALARY01 on column emp_salary.SALARY. What’s the difference on execution plan for following two SQL statements and why?
a) Select * from emp_salary s where s.salary is null

b) Select * from emp_salary s where s.salary is not null

在这里插入代码片

Create index IDX_EMP_SALARY01 on column emp_salary.SALARY. What’s the difference on following two SQL statements and why?
a) Select * from emp_salary s where to_char(s.salary) = ‘2000’;

在这里插入代码片

b) Select * from emp_salary s where s.salary = 2000;

在这里插入代码片

Update emp_salary s set s.bonus = 0;
Alter table emp_salary modify bonus not null;
Rollback;
What’s the status of emp.salary after all three statements are executed?

在这里插入代码片

Section II Oracle Concept
10. What’s the difference between truncate table and delete table?
delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行回滚操作。

truncate table 则一次性从表中删除所有的数据并不把单独的删除操作记录存入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

表和索引所占空间
当表被truncate后，这个表和索引所占用的空间会恢复到初始大小。
delete操作不会减少表或索引所占用的空间。
drop语句会把表所占的空间全部释放掉。

删除效率 drop > truncate > delete
truncate table 速度快，效率高的原因是：truncate table在功能上与不带where子句的delete语句相同，都是删除表中的全部行。
但是truncate table比delete速度快，并且使用的系统和事务日志资源少。delete语句每次删除一行，并在事务日志中为删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据，并且在事务日志中记录页的释放。

适用范围：truncate只能删除table，而delete可以删除table和view

truncate和delete只删除数据，drop会删除整个表（表结构和数据）。

delete语句是DML(Data Manipulation Language)数据操纵语言，操作会被放到rollback segment中，事务提交之后才会生效。如果有相应的触发器，执行的时候会被触发。

truncate、drop是DLL(Data Definition Language)数据定义语言，DDL操作是隐性提交的，操作立即生效，不能rollback。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

delete
1.delete是DML语句，执行delete操作时，每次从表中删除一行，并且同时将该行的删除操作记录在redo和undo表空间以便进行回滚和重做操作，但是要注意表空间需要足够大，并且需要手动commit操作才能生效，也可以通过rollback撤销操作。
2.delete可以根据where子句删除指定的行数据，如果不知道where子句，那么会删除表中所有记录。
3.delete语句不影响表所占的extent，高水线保持原位置不改变。

truncate
1.truncate是数据定义语言，删除数据时会隐式提交，不能回滚也不会触发trigger。
2.truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

3.对于外键(foreign key)约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

4.truncatetable不能用于参与了索引视图的表。

drop
1.drop是数据定义语言，会隐式提交，所以，不能回滚，不会触发触发器。

2.drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3.drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

11.What’s the difference of varchar(10) and char(10)?
varchar(10)长度是可变的，字符长度小于10时，为实际长度
char(10)长度是不可变的，不管字符长度如何，都是固定的，字符长度小于10时，空格补上。
尽管如此，char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

12.List the types of Oracle index you know.
B树索性，位图索引，函数索引

13.List the Oracle optimizer hints you know.

Section III Data Warehouse
14.What is dimensional modeling? What is star schema? 什么是维度建模？什么是星型模型？
维度建模是用于面向主题的分析型数据库设计的一套数据设计方法。
15.Describe the differences between OLTP database and data warehouse.

16.What is fact table? What is dimension table? 什么是事实表？什么是维度表
事实表包含对于分析主题的度量。事实表还包含与维度表关联的外码。事实表中的度量通常是数值型的，用于数学计算和定量分析。

维度表包含对分析主题所属类型的描述，如商业，组织或企业。例如，如果对主题商业进行销售方面的分析，则可以对产品品牌、顾客性别、顾客收入水平等进行分析。

17.What is SCD? Describe the differences between SCD1, SCD2 and SCD3. 什么是切片？
切片和切块操作是从已经显示的结果增加、替换或者消除指定的维度属性。
SCD1，SCD2，SCD3是不同维度来分析数据。

18.What is ETL? List the ETL tools you have used before. 什么是ETL？ETL工具？
对数据进行提取-转换-加载 Kettle