Organize Oracle interview questions and answers

The following questions are transformed according to this table

1. Table: table1(FId,Fclass,Fscore), use the most efficient and A simple SQL lists the list of the highest grades in each class, showing two fields: class and grade.

select stu_class, max(stu_score) from core group by stu_class ;

2. There is a table table1 with two fields FID and Fno, both of which are non-empty. Write an SQL statement to list the records of one FID corresponding to multiple different Fnos in the table.

select t2.* from table1 t1, table1 t2 where t1.fid = t2.fid and t1.fno <> t2.fno;

Three ways to write:

select * from core co1 where co1.STU_CLASS in ( select co.STU_CLASS from CORE co group by co.STU_CLASS having count(co.STU_CLASS) >1); select DISTINCT c2.* from core c1 ,core c2 where c1.STU_CLASS = c2.STU_CLASS and c1.STU_SCORE <> c2.STU_SCORE; SELECT * FROM core c1 where 1=1 and EXISTS (select 1 from core c2 where c1.STU_CLASS = c2.STU_CLASS and c1.STU_SCORE <> c2.STU_SCORE);

3. There is an employee table empinfo

( Fempno varchar2(10) not null pk, Fempname varchar2(20) not null, Fage number not null, Fsalary number not null );

If the amount of data is very large, about 10 million; write one that you think is the most efficient SQL, use one SQL to calculate the following four types of people:

fsalary>9999 and fage > 35 fsalary>9999 and fage < 35 fsalary <9999 and fage > 35 fsalary <9999 and fage < 35

The number of each type of employee;

select sum(case when fsalary > 9999 and fage > 35then 1else 0end) as "fsalary>9999_fage>35",sum(case when fsalary > 9999 and fage < 35then 1else 0end) as "fsalary>9999_fage<35",sum(case when fsalary < 9999 and fage > 35then 1else 0end) as "fsalary<9999_fage>35",sum(case when fsalary < 9999 and fage < 35then 1else 0end) as "fsalary<9999_fage<35"from empinfo;

select sum(case when stu_score < 60 then 1 else 0 end ) as "60分以下人数" ,sum(case when stu_score > 60 and stu_score <= 70 then 1 else 0 end ) as "60到70分人数" ,sum(case when stu_score > 70 and stu_score <= 80 then 1 else 0 end ) as "70到80分人数" ,sum(case when stu_score > 80 and stu_score <= 100 then 1 else 0 end ) as "80分以上人数" from core;

4. The fields of table A are as follows
month person income
Monthly Personnel Income
Requires one SQL statement (note that it is one) for the total income of the person (not distinguishing between persons) each month and the previous month and next month
Requirement list The output is
month's income, previous month's income, next month's income

MONTHS PERSON INCOME ---------- ---------- ----------200807 mantisXF 5000200806 mantisXF2 3500200806 mantisXF3 3000200805 mantisXF1 2000200805 mantisXF6 2200200804 mantisXF7 1800200803 8mantisXF 4000200802 9mantisXF 4200200802 10mantisXF 3300200801 11mantisXF 4600200809 11mantisXF 6800

11 rows selected

months, (incomes), (prev_months), (( ), ), ), lag(incomes) ( months), ) prev_months, decode(lead(months) ( months), to_char(add_months(to_date(months, ), ), ), lead(incomes) ( months), ) next_months ( months, (income) incomes a months) aa) aaagroup (INCOMES) (PREV_MONTHS) (NEXT_MONTHS)

5, table B
C1 c2
2005-01- 01 1
2005-01-01 3
2005-01-02 5

Required processing data
2005-01-01 4
2005-01-02 5
Total 9
Try a Sql statement to complete.

select nvl(to_char(t02,'yyyy-mm-dd'),'合计'),sum(t01)from test group by rollup(t02)

6, Concept and understanding of database 1, 2, and 3 paradigms.

Certain rules must be followed when designing a relational database. Especially the database design paradigm
A brief introduction to 1NF (first normal form), 2NF (second normal form), 3NF (third normal form),
First normal form (1NF): each specific parameter in the relational model R In a relation r, if each attribute value is the smallest data unit that cannot be further divided, then R is said to be a relation in first normal form.

For example: If employee number, name, and phone number form a table (a person may have an office phone number and a home phone number), there are three ways to standardize it into 1NF:
One is to repeatedly store employee number and Name. In this case, the keyword can only be a phone number.
The second is the employee number as the keyword, and the phone number is divided into two attributes: work phone number and residential phone number
The third is the employee number as the keyword, but it is mandatory that each record can only have one phone number.
Of the above three methods, the first method is the least advisable. Choose the latter two cases according to the actual situation.

Second Normal Form (2NF): If all non-primary attributes in the relationship schema R (U, F) are completely dependent on any candidate keyword, the relationship R is said to belong to the second normal form.
Example: Course selection relationship SCI (SNO, CNO, GRADE, CREDIT) where SNO is the student number, CNO is the course number, GRADEGE is the grade, and CREDIT is the credit. Based on the above
conditions, the keywords are combined keywords (SNO, CNO)
Using the above relational model in applications has the following problems:

a. Data redundancy, assuming the same door The course is taken by 40 students and is repeated 40 times for credit.
b. Update abnormality. If the credits of a certain course are adjusted, the corresponding tuple CREDIT value will be updated, and the credits of the same course may be different.
c. Insert an exception. For example, if you plan to open a new course, since no one is taking it and there is no student number keyword, you can only wait for someone to take it before you can deposit the course and credits.
d. Deletion exception, if the student has graduated, delete the elective record from the current database. If freshmen have not yet taken some courses, the course and credit records cannot be saved.

Reason: The non-keyword attribute CREDIT only functionally depends on CNO, that is, CREDIT partially depends on the combined keyword (SNO, CNO) rather than completely.

Solution: Divide into two relationship modes SC1 (SNO, CNO, GRADE) and C2 (CNO, CREDIT). The new relationship includes two relationship schemas, which are connected through the
foreign keyword CNO in SCN. When necessary, natural connections are made to restore the original relationship
Third normal form (3NF): If the relationship schema All non-primary attributes in R (U, F) have no transitive dependence on any candidate keyword, then the relationship R is said to belong to the third normal form.

Example: S1 (SNO, SNAME, DNO, DNAME, LOCATION) Each attribute represents the student number,
name, department, department name, and department address.
The keyword SNO determines each attribute. Since it is a single keyword, there is no problem of partial dependence, so it must be 2NF. However, there must be a lot of redundancy in this relationship. The
attributes DNO, DNAME, and LOCATION where the students are located will be repeatedly stored, inserted, deleted, and modified, and situations similar to the above example will occur.
Cause: There is a transitive dependency in the relationship. That is SNO -> DNO. But DNO -> SNO does not exist, DNO -> LOCATION, so the key is SNO vs LOCATIO

N 函数决定是通过传递依赖 SNO -> LOCATION 实现的。也就是说，SNO不直接决定非主属性LOCATION。
解决目地：每个关系模式中不能留有传递依赖。
解决方法：分为两个关系 S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）
注意：关系S中不能没有外关键字DNO。否则两个关系之间失去联系。

7，简述oracle行触发器的变化表限制表的概念和使用限制，行触发器里面对这两个表有什么限制。

变化表mutating table
被DML语句正在修改的表
需要作为DELETE CASCADE参考完整性限制的结果进行更新的表也是变化的

限制:对于Session本身，不能读取正在变化的表

限制表constraining table
需要对参考完整性限制执行读操作的表

限制:如果限制列正在被改变，那么读取或修改会触发错误，但是修改其它列是允许的。

8、oracle临时表有几种。
临时表和普通表的主要区别有哪些，使用临时表的主要原因是什么？

在Oracle中，可以创建以下两种临时表：
a。会话特有的临时表

CREATE GLOBAL TEMPORARY ( ) ON COMMIT PRESERVE ROWS；

b。事务特有的临时表

CREATE GLOBAL TEMPORARY ( ) ON COMMIT DELETE ROWS； CREATE GLOBAL TEMPORARY TABLE MyTempTable

所建的临时表虽然是存在的，但是你试一下insert 一条记录然后用别的连接登上去select，记录是空的，明白了吧。
下面两句话再贴一下：

• ON COMMIT DELETE ROWS说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）

• ON COMMIT PRESERVE ROWS说明临时表是会话指定，当中断会话时ORACLE将截断表。
  

9，怎么实现：使一个会话里面执行的多个过程函数或触发器里面都可以访问的全局变量的效果，并且要实现会话间隔离？

--个人理解就是建立一个包,将常量或所谓的全局变量用包中的函数返回出来就可以了,摘抄一短网上的解决方法Oracle数据库程序包中的变量，在本程序包中可以直接引用，但是在程序包之外，则不可以直接引用。对程序包变量的存取，可以为每个变量配套相应的存储过程<用于存储数据>和函数<用于读取数据>来实现。 　　 　　3.2 实例 　　--定义程序包 　　create or replace package PKG_System_Constant is 　　 　　　 C_SystemTitle nVarChar2(100):='测试全局程序变量';　--定义常数 　　　 --获取常数<系统标题> 　　　 Function FN_GetSystemTitle 　　　　　Return nVarChar2; 　　 　　　 G_CurrentDate Date:=SysDate; --定义全局变量 　　　 --获取全局变量<当前日期> 　　　 Function FN_GetCurrentDate 　　　　　Return Date; 　　　 --设置全局变量<当前日期> 　　　 Procedure SP_SetCurrentDate 　　　　　(P_CurrentDate In Date); 　　End PKG_System_Constant; 　　/ 　　create or replace package body PKG_System_Constant is 　　　 --获取常数<系统标题> 　　　 Function FN_GetSystemTitle 　　　　　Return nVarChar2 　　　　　Is 　　　　　Begin 　　　　　　 Return C_SystemTitle; 　　　　　End FN_GetSystemTitle; 　　 　　　 --获取全局变量<当前日期> 　　　 Function FN_GetCurrentDate 　　　　　Return Date 　　　　　Is 　　　　　Begin 　　　　　　 Return G_CurrentDate; 　　　　　End FN_GetCurrentDate; 　　　 --设置全局变量<当前日期> 　　　 Procedure SP_SetCurrentDate 　　　　　(P_CurrentDate In Date) 　　　　　Is 　　　　　Begin 　　　　　　 G_CurrentDate:=P_CurrentDate; 　　　　　End SP_SetCurrentDate; 　　End PKG_System_Constant; 　　/ 　　　　 　　3.3 测试 　　--测试读取常数 　　Select PKG_System_Constant.FN_GetSystemTitle From Dual;　　　 　　--测试设置全局变量 　　Declare　 　　Begin 　　　 PKG_System_Constant.SP_SetCurrentDate(To_Date('2001.01.01','yyyy.mm.dd')); 　　End; 　　/ 　　--测试读取全局变量 　　Select PKG_System_Constant.FN_GetCurrentDate From Dual;

10，aa，bb表都有20个字段，且记录数量都很大，aa，bb表的X字段（非空）上有索引，
请用SQL列出aa表里面存在的X在bb表不存在的X的值，请写出认为最快的语句，并解译原因。

select aa.x from aa where not exists (select 'x' from bb where aa.x = bb.x) ;

以上语句同时使用到了aa中x的索引和的bb中x的索引

11，简述SGA主要组成结构和用途？

SGA是Oracle为一个实例分配的一组共享内存缓冲区，它包含该实例的数据和控制信息。SGA在实例启动时被自动分配，当实例关闭时被收回。数据库的所有数据操作都要通过SGA来进行。
SGA中内存根据存放信息的不同，可以分为如下几个区域：
a.Buffer Cache：存放数据库中数据库块的拷贝。它是由一组缓冲块所组成，这些缓冲块为所有与该实例相链接的用户进程所共享。缓冲块的数目由初始化参数DB_BLOCK_BUFFERS确定，缓冲块的大小由初始化参数DB_BLOCK_SIZE确定。大的数据块可提高查询速度。它由DBWR操作。
b. 日志缓冲区Redo Log Buffer：存放数据操作的更改信息。它们以日志项（redo entry）的形式存放在日志缓冲区中。当需要进行数据库恢复时，日志项用于重构或回滚对数据库所做的变更。日志缓冲区的大小由初始化参数LOG_BUFFER确定。大的日志缓冲区可减少日志文件I/O的次数。后台进程LGWR将日志缓冲区中的信息写入磁盘的日志文件中，可启动ARCH后台进程进行日志信息归档。
c. 共享池Shared Pool：包含用来处理的SQL语句信息。它包含共享SQL区和数据字典存储区。共享SQL区包含执行特定的SQL语句所用的信息。数据字典区用于存放数据字典，它为所有用户进程所共享。

12什么是分区表？简述范围分区和列表分区的区别，分区表的主要优势有哪些？

使用分区方式建立的表叫分区表

范围分区
每个分区都由一个分区键值范围指定（对于一个以日期列作为分区键的表，“2005 年 1 月”分区包含分区键值为从“2005 年 1 月 1 日”
到“2005 年 1 月 31 日”的行）。

列表分区
每个分区都由一个分区键值列表指定（对于一个地区列作为分区键的表，“北美”分区可能包含值“加拿大”“美国”和“墨西哥”）。

分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务。通过分区,数据库设计人员和管理员能够解决前沿应用程序带来的一些难题。分区是构建千兆字节数据系统或超高可用性系统的关键工具。

13, Background: A certain data is running in archivelog, and RMAN has been used to make full backup and cold backup of the database.
And all the archive logs are available. Now all the control files are damaged and all other files are intact. What is the problem? How to restore the database, tell me one or two methods.

Reply method:
1. Use cold backup, directly COPY all the cold backup files to the original directory, and then restart the database
2. Use archive logs,

• Start database NOMOUNT

• Create a control file that specifies the location of the data file and redo log file.

• Use the RECOVER DATABASE using backup controlfile until cancel command to restore the database. At this time, you can use the archive log

• ALETER DATABASE OPEN RESETLOGS;

• Back up the database and control files again

14. Use rman to write a backup statement: backup table space TSB, level 2 incremental backup.

15, there is a table a(x number(20),y number(20)) using the fastest and most efficient SQL to insert 10 million consecutive records starting from 1 into the table.

Related learning recommendations:oracle database learning tutorial

[Topic recommendation]:2020 Oracle interview questions summary ( up to date)

The above is the detailed content of Organize Oracle interview questions and answers. For more information, please follow other related articles on the PHP Chinese website!