Oracle Interview Questions and answers

1. Table: Table1 (FID, fclass, FSCORE). The most efficient and simple SQL statement is used to list the highest scores of each class. Two fields are displayed: class and score.

Select fclass, max (FSCORE) from Table1 group by fclass, FID

2. There is a table in table 1 with two fields FID, fno, and the word is not empty. Write an SQL statement to list records with multiple fno records corresponding to one FID in the table.

Class:
101a1001
101a1001
102a1002
102a1003
103a1004
104a1005
104a1006
105a1007
105a1007
105a1007
Result:
102a1002
102a1003
104a1005
104a1006

Select T2. * From Table1 T1, Table1 T2 where t1.fid = t2.fid and t1.fno <> t2.fno;

3. empinfo
(
Fempno varchar2 (10) Not null PK,
Fempname varchar2 (20) not null,
Fage number not null,
Fsalary number not null
);
Assume that there are about 10 million data records. Write an SQL statement that you think is the most efficient. Use one SQL statement to calculate the following four types of users:
Fsalary> 9999 and Fage> 35
Fsalary> 9999 and Fage <35
Fsalary <9999 and Fage> 35
Fsalary <9999 and Fage <35
The number of employees;
Select sum (case when fsalary> 9999 and Fage> 35
Then 1
Else 0end) as "fsalary> 9999_fage> 35 ",
Sum (case when fsalary> 9999 and Fage <35
Then 1
Else 0
End) as "fsalary> 9999_fage <35 ",
Sum (case when fsalary <9999 and Fage> 35
Then 1
Else 0
End) as "fsalary <9999_fage> 35 ",
Sum (case when fsalary <9999 and Fage <35
Then 1
Else 0
End) as "fsalary <9999_fage <35"
From empinfo;
4. The fields in Table A are as follows:
Month person income
Monthly staff income
Requires the use of an SQL statement (note that it is one) Where the owner (not distinguished by personnel) the total revenue of each month and last month and next month
The output of the requirement list is
Month: When the monthly income is higher than the monthly income
Months person income
---------- -------- ---------- 200807 mantisxf 5000200806 mantisxf2 3500200806 mantisxf3 3000200805 mantisxf1 2000200805 mantisxf6 2200200804 mantisxf7 1800200803 8 mantisxf 4000200802 9 mantisxf 4200200802 10 mantisxf 3300200801 11 manxtisf 4600200809 11 mantisxf 6800
11 rows selected
Select months, max (incomes), max (prev_months), max (next_months)
From (select months,
Incomes,
Decode (Lag (months) over (order by months ),
To_char (add_months (to_date (Months, 'yyyymm'),-1), 'yyyymm'), lag (incomes) over (order by months), 0) as prev_months, decode (lead (months) over (order by months), to_char (add_months (to_date (Months, 'yyyymm'), 1), 'yyyymm'), lead (incomes) over (order by months), 0) as next_months from (select months, sum (income) as incomes from a group by months) aa) aaagroup by months;

Months max (incomes) max (prev_months) max (next_months) ---------- ------------ ---------------- -------------- 200801 4600 7500200802 7500 4600 4000200803 4000 7500 1800200804 1800 4000 4200200805 4200 1800 6500200806 6500 4200 5000200807 5000 0 0

5. Table B
C1 C2
2005-01-01
2005-01-01 3
2005-01-02 5

Required data
2005-01-01 4
2005-01-02 5
Total 9
Try an SQL statement.

 

Select nvl (to_char (t02, 'yyyy-mm-dd'), 'Total'), sum (t01) from test
Group by rollup (t02)

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

7. Briefly describe the concept and restrictions of the Oracle row trigger change table. What are the restrictions on these two tables in the row trigger.

8. There are several temporary Oracle tables.
What are the main differences between a temporary table and a common table? What are the main reasons for using a temporary table?

9. How to implement the global variables that can be accessed by multiple process functions or triggers executed in a session and achieve inter-session isolation?

The 10, AA, and BB tables all have 20 fields and a large number of records. The X fields (not blank) of the AA and BB tables have indexes,
Use SQL to list the values of X in the AA table that does not exist in the BB table. Write the fastest statement and explain the cause.

11. Briefly describe the main structure and application of SGA?

12 what is a partition table? Briefly describe the differences between range partitions and list partitions. What are the main advantages of partition tables?

13. Background: a certain data runs in archivelog and RMAN is used for full backup and cold backup of the database,
All the archived logs are found, the current control file is damaged, and all other files are in good condition. How can I restore the database? One or two methods are provided.

14. Use RMAN to write a backup statement: Backup Incremental backup of table space TSB with Level 2.

15. There is a table a (x number (20) and Y number (20) that inserts 10 million consecutive records starting from 1 into the table with the fastest and most efficient SQL statement.

1. Table: Table1 (FID, fclass, FSCORE). The most efficient and simple SQL statement is used to list the highest scores of each class. Two fields are displayed: class and score.

2. There is a table in table 1 with two fields FID, fno, and the word is not empty. Write an SQL statement to list records with multiple fno records corresponding to one FID in the table.
Class:
101 a1001
101 a1001
102 a1002
102 a1003
103 a1004
104 a1005
104 a1006
105 a1007
105 a1007
105 a1007
Result:
102 a1002
102 a1003
104 a1005
104 a1006

3. empinfo
(
Fempno varchar2 (10) Not null PK,
Fempname varchar2 (20) not null,
Fage number not null,
Fsalary number not null
);
Assume that there are about 10 million data records. Write an SQL statement that you think is the most efficient. Use one SQL statement to calculate the following four types of users:
Fsalary> 9999 and Fage> 35
Fsalary> 9999 and Fage <35
Fsalary <9999 and Fage> 35
Fsalary <9999 and Fage <35
The number of employees;

4. The fields in Table A are as follows:
Month person income
Monthly staff income
Requires the use of an SQL statement (note that it is one) Where the owner (not distinguished by personnel) the total revenue of each month and last month and next month
The output of the requirement list is
Month: When the monthly income is higher than the monthly income

5. Table B
C1 C2
2005-01-01
2005-01-01 3
2005-01-02 5

Required data
2005-01-01 4
2005-01-02 5
Total 9
Try an SQL statement.

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

7. Briefly describe the concept and restrictions of the Oracle row trigger change table. What are the restrictions on these two tables in the row trigger.

8. There are several temporary Oracle tables.
What are the main differences between a temporary table and a common table? What are the main reasons for using a temporary table?

9. How to implement the global variables that can be accessed by multiple process functions or triggers executed in a session and achieve inter-session isolation?

The 10, AA, and BB tables all have 20 fields and a large number of records. The X fields (not blank) of the AA and BB tables have indexes,
Use SQL to list the values of X in the AA table that does not exist in the BB table. Write the fastest statement and explain the cause.

11. Briefly describe the main structure and application of SGA?

12 what is a partition table? Briefly describe the differences between range partitions and list partitions. What are the main advantages of partition tables?

13. Background: a certain data runs in archivelog and RMAN is used for full backup and cold backup of the database,
All the archived logs are found, the current control file is damaged, and all other files are in good condition. How can I restore the database? One or two methods are provided.

14. Use RMAN to write a backup statement: Backup Incremental backup of table space TSB with Level 2.

15. There is a table a (x number (20) and Y number (20) that inserts 10 million consecutive records starting from 1 into the table with the fastest and most efficient SQL statement.

Answer:
1. Select fclass, max (FSCORE) from Table1 group by fclass
2. Select * From Table1 where FID in (select FID from Table1 group by FID having (count (distinct fno)> = 2)
3. Select sum (case when fsalary> 9999 and Fage> 35 then 1 else 0 end ),
Sum (case when fsalary> 9999 and Fage <35 then 1 else 0 end ),
Sum (case when fsalary <9999 and Fage> 35 then 1 else 0 end ),
Sum (case when fsalary <9999 and Fage <35 then 1 else 0 end) from empinfo
4,
Select (select month from table where month = to_char (sysdate, 'mm') month,
(Select sum (income) from table where month = to_char (sysdate, 'mm') when monthly income,
(Select sum (income) from table where to_number (month) = to_number (extract (month from sysdate)-1) monthly income,
(Select sum (income) from table where to_number (month) = to_number (extract (month from sysdate) + 1) Monthly Income
From dual

5. Select nvl (C1, 'Total'), sum (C2) from B group by rollup (C1)
6.
When designing relational databases, you must follow certain rules. Especially the database design paradigm
Brief Introduction to 1nf (first paradigm), 2nf (second paradigm), 3nf (third paradigm ),
1nf: in each specific relational R in relational mode R, if each attribute value is the smallest data unit that cannot be further divided, R is called the relationship of the first paradigm.
For example, the 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 methods to standardize 1nf:
The first is to repeat the employee ID and name. In this way, the keyword can only be a phone number.
Second, the employee number is a keyword. The phone number is divided into two attributes: the phone number of the organization and the residential phone number.
Third, the employee number is a keyword, but each record must have only one phone number.
The first method is the least desirable of the above three methods. The last two cases are selected based on the actual situation.
2nf: if all the non-primary attributes in the relational mode R (u, f) depend entirely on any candidate keyword, the relational R belongs to the second paradigm.
For example, in the Course Selection relation SCI (SNO, CNO, grade, and credit), SnO indicates the student ID, CNO indicates the course number, gradege indicates the score, and credit indicates the credit. From above
Condition. The keyword is the combination keyword (SNO, CNO)
There are the following problems when using the above link mode in applications:
A. data redundancy. Assume that 40 students take the same course and 40 credits are repeated.
B. The update is abnormal. If the credits of a course are adjusted, the corresponding credit values of the corresponding tuples must be updated, and the credits of the same course may be different.
C. Insert exceptions. For example, if you plan to open a new course, because no one takes the course or has no student ID keyword, you can only save the course and credits after someone takes the course.
D. Deletion exception. If the student has completed the course, the electives will be deleted from the current database. Some new students have not yet taken the course, so the course and credits cannot be saved.
Cause: the non-Keyword attribute credit only depends on CNO, that is, the credit part depends on the combined keyword (SNO, CNO) rather than the full dependency.
Solution: It is divided into two relational modes: SC1 (SNO, CNO, grade), C2 (CNO, credit ). The new relationship includes two relational modes
The external keyword CNO of is associated. If necessary, the native join is performed to restore the original link.
3nf: If all non-primary attributes in the relational model R (u, f) do not transmit trust to any candidate keywords, the relational model R belongs to the 3nf.
For example, each attribute of S1 (SNO, sname, DNO, dname, location) represents a student ID,
Name, Department, Department name, and Department address.
The SNO keyword determines the attributes. Because it is a single keyword, there is no part of the dependency problem, it must be 2nf. However, there must be a lot of redundancy in this relationship.
The attributes DNO, dname, and location are repeatedly stored, inserted, deleted, and modified.
Cause: A dependency is transmitted in the link. That is, Sno-> DNO. While DNO-> SnO does not exist, DNO-> location, so the key Liao SnO for low.o
The N function is implemented by passing the dependency Sno-> location. That is to say, SnO does not directly determine the non-primary attribute location.
Solution: the transfer dependency cannot be left in each link mode.
Solution: There are two relationships: S (SNO, sname, DNO), D (DNO, dname, location)
Note: The outer keyword DNO cannot be found in link S. Otherwise, the relationship is lost.

7.
Mutating table
Table being modified by DML statement
The table that needs to be updated as a delete cascade reference results of integrity restrictions also changes

Restriction: for the session itself, the table in change cannot be read.

Restrict table constraining table
Table requiring read operation on the reference integrity Restriction

Restriction: if the restriction column is being changed, an error is triggered when reading or modifying the restriction column, but modifying other columns is allowed.

8.
In Oracle, you can create two types of temporary tables:
A. Session-specific temporary table
Create global temporary ()
On commit preserve rows;

B. Temporary tables specific to transactions
Create global temporary ()
On commit Delete rows;
Create global temporary table mytemptable
Although the created temporary table exists, you can try to insert a record and then mount it to select using other connections. The record is empty.
The following two sentences will be added:
-- On commit Delete rows indicates that the temporary table is specified by a transaction. After each commit, Oracle truncates the table (delete all rows)
-- On commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, Oracle truncates the table.

9. -- in my personal understanding, it is to create a package and return the constants or so-called global variables using the functions in the package. I will extract a short online solution.
Oracle Database Program The variables in the package can be directly referenced in this package, but cannot be directly referenced outside the package. The access to the package variables can be implemented by matching the corresponding Stored Procedure <used to store data> and function <used to read data> for each variable.

3.2 instance
-- Define a package
Create or replace package pkg_system_constant is

C_systemtitle nvarchar2 (100): = 'test Global Program variable'; -- Define a constant
-- Get constant <system title>
Function fn_getsystemtitle
Return nvarchar2;

G_currentdate Date: = sysdate; -- defines global variables
-- Get global variables <Current date>
Function fn_getcurrentdate
Return date;
-- Set global variables <Current date>
Procedure sp_setcurrentdate
(P_currentdate in date );
End pkg_system_constant;
/
Create or replace package body pkg_system_constant is
-- Get constant <system title>
Function fn_getsystemtitle
Return nvarchar2
Is
Begin
Return c_systemtitle;
End fn_getsystemtitle;

-- Get global variables <Current date>
Function fn_getcurrentdate
Return date
Is
Begin
Return g_currentdate;
End fn_getcurrentdate;
-- Set global variables <Current date>
Procedure sp_setcurrentdate
(P_currentdate in date)
Is
Begin
G_currentdate: = p_currentdate;
End sp_setcurrentdate;
End pkg_system_constant;
/

3.3 Test
-- Test Read Constant
Select pkg_system_constant.fn_getsystemtitle from dual;
-- Test setting global variables
Declare
Begin
Pkg_system_constant.sp_setcurrentdate (to_date ('1970. 100', 'yyyy. Mm. dd '));
End;
/
-- Test reading global variables
Select pkg_system_constant.fn_getcurrentdate from dual;

10.
Select AA. X from AA
Where not exists (select 'x' from BB where AA. x = BB. X );
The preceding statement uses both the index of X in AA and the index of X in BB.

11
SGA is a group of shared memory buffers allocated by Oracle for an instance. It contains the data and control information of the instance. SGA is automatically allocated when the instance is started, and is withdrawn when the instance is closed. All database data operations must be performed through SGA.
The memory in SGA can be divided into the following areas based on the storage information:
A. buffer cache: stores copies of database blocks in the database. It is composed of a group of buffer blocks, which are shared by all user processes linked to the instance. The number of buffer blocks is determined by the initialization parameter db_block_buffers. The size of the buffer block is determined by the initialization parameter db_block_size. Large data blocks increase the query speed. It is operated by dbwr.
B. Log buffer redo log Buffer: stores data operation changes. They are stored in the log buffer as a log entry. When you need to recover the database, log entries are used to reconstruct or roll back the changes made to the database. The size of the log buffer is determined by the initialization parameter log_buffer. A large log buffer can reduce the number of log file I/O times. The background process lgwr writes the information in the log buffer to the log file on the disk. You can start the arch background process to archive the log information.
C. Shared Pool: Contains SQL statement information for processing. It includes the shared SQL area and data dictionary storage area. The shared SQL area contains the information used to execute specific SQL statements. The data dictionary area is used to store data dictionaries, which are shared by all user processes.

12.
A partition table is called a partition table.

Range Partition
Each partition is specified by a partition key value range. (For a table that uses a date column as the partition key, the "January 2005" partition contains the partition key value from "January 1, 2005"
To the line of "January 31, 2005 ).

List Partition
Each partition is specified by a list of partition key values (for a table with a region column as the partition key, the "North America" partition may contain values "Canada", "United States", and "Mexico ").

By improving manageability, performance, and availability, the partitioning function brings great benefits to various applications. In general, partitions can greatly improve the performance of some queries and maintenance operations. In addition, partitions can greatly simplify common management tasks. Through partitioning, database designers and administrators can solve some of the challenges posed by cutting-edge applications. Partitioning is a key tool for building a gigabit data system or an ultra-high availability system.

13
Reply method:
1. Use cold backup to copy all cold backup files to the original directory.
2. Use archive logs,
1. Start database nomount
2. Create a control file and specify the location of the data file and redo log file.
3. Use the recover database using backup controlfile until cancel command to reply to the database. Archive logs can be used at this time.
4. aleter database open resetlogs;
5. Back up the database and control files again.

14. Refer to the RMAN user manual.
15