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?

1 2 Next Page