Oracle Exam Questions

One, the choice question (40 points)

Oracle issues The following SELECT statements:

Sql> Select E.empno, E.ename, D.loc

2 from EMP E, Dept D

3 Where E.deptno = D.deptno

4 and substr (E.ename, 1, 1) = ' S ';

Which of the following statements is an ANSI-compatible equivalent statement available in the Oracle database?

A Select Empno, ename, loc from EMP Join dept on emp.deptno = Dept.deptno where

SUBSTR (Emp.ename, 1, 1) = ' S ';

B Select Empno, ename, loc from EMP, dept on emp.deptno = Dept.deptno where

SUBSTR (Emp.ename, 1, 1) = ' S ';

C Select Empno, ename, loc from EMP Join dept where emp.deptno = Dept.deptno and

SUBSTR (Emp.ename, 1, 1) = ' S ';

D Select Empno, ename, loc from EMP LEFT JOIN dept on emp.deptno = Dept.deptno and

SUBSTR (Emp.ename, 1, 1) = ' S ';

Which of the following options represents the functionality of the SELECT statement in Oracle?

A You can change data B in Oracle with the SELECT statement. You can delete data from Oracle using the SELECT statement

C You can generate a table D with the SELECT statement and the contents of another table. Table truncation can be made with the SELECT statement

You want to manipulate Oracle data, which of the following is not a SQL command?

A SELECT * from dual; B Set define

C Update emp Set ename = 6543 where ename = ' SMITHERS ';

D CREATE TABLE Employees (Empid VARCHAR2 (Ten) primary key);

You want to define SQL queries in Oracle. Which of the following database objects cannot be referenced directly from a SELECT statement?

A Table B. Sequence C. Index D. View

You want to filter the returned data from the Profits table query based on the Product_Name column. Which of the following clauses contains references to the corresponding filter criteria?

A Select B. From C. where D. Having

Answer the next five questions with the following code:

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

7369simthclerk790217-dec-8080020

7499allensalesman769820-feb-81160030030

7521wardsalesman769822-feb-81125050030

7566jonesmanager783902-apr-81297520

7654martinsalesman769828-sep-811250140030

7698blakemanager783901-may-81285030

7782clarkmanager783909-jun-81245010

7788scottanalyst756619-apr-82300020

7839kingpresident17-nov-81500010

7844turnersalesman769808-set-811500030

7876adamsclerk778823-dec-82110020

7900jamesclerk769803-dec-8195030

7902 FORD ANALYST 7566 03-dec-81 3000 20

7934 MILLER Clerk 7782 23-jan-82 1300 10

Which of the following options is the value returned by the following query: select SUM (SAL) + SUM (comm) from the emp where job = ' ANALYST ' or ename like ' j% '?

A 6000 B. 9925 C. 9975 D. Null NULL for the result of a JOIN operation or null

Which of the following options is the value returned by the following query: SELECT COUNT (Mgr) from emp where Deptno = 10?

A 1 B. 2 The count operation can ignore empty line C. 3 D. Null

Which of the following options is the value returned by the following query: SELECT COUNT (*) from emp where mgr = 7700-2?

A 5 B. 6 C. 7 D. Null

Which of the following options is the third employee produced by the following SQL command: Select ename, sal from EMP where job= ' salesman ' ORDER by empno Desc?

A ALLEN B. MARTIN C. TURNER D. WARD

Which of the following options is the value returned by Oracle after issuing the following query: Select SUBSTR (Job, 1, 3) from EMP where ename like Upper (' __ar% ')?

A ANA B. CLE C. Man D. SAL

To obtain data from the Orders table, which includes three columns customer, Order_date, and Order_amt. Which of the following where clauses can be used to query the Orders table for orders with more than 2700 customer Leslie?

A where customer = ' LESLIE ';

B where customer = ' LESLIE ' and Order_amt < 2700;

C where customer = ' LESLIE ' or Order_amt > 2700;

D where customer = ' LESLIE ' and Order_amt > 2700;

For a row in a table, the VARCHAR2 column contains a numeric smithy, and the application fills seven spaces on the right. What value is returned when the length () function processes this column value?

A 6 B. C. D. 60

The following queries are issued in Oracle:

Sql> Select Months_between (' 15-mar-83 ', ' 15-mar-97 ') Form dual;

What does Oracle return?

A B. -14 C. 168 D. -168

When you develop a report, you connect three tables of information, such as EMP, dept, and Salgrade. Only the employee's name, department address and salary record are required for the company level 10 employees. How many conditions does this query require?

A 2 B. 3 C. 4 D. 5

The following commands are issued in Oracle:

Sql> Select E.ename,a.street_address,a.city,a.post_code

2 from EMP e,addr a

3 Where E.empno = A.empno (+)

4 and a.state = ' TEXAS ';

Which of the following options shows the equivalent Ansi/iso statement?

A Select E.ename, A.street_address, a.city, A.state, a.post_code from EMP e outer join addr A on e.empno = A.empno where a.s Tate = ' TEXAS ';

B Select E.ename, A.street_address, a.city, A.state, a.post_code from EMP e left outer join addr A on e.empno = A.empno wher E a.state = ' TEXAS ';

C Select E.ename, A.street_address, a.city, A.state, a.post_code from EMP e right outer join addr A on e.empno = A.empno whe Re a.state = ' TEXAS ';

D Select E.ename, A.street_address, a.city, A.state, a.post_code from EMP e right outer join addr a WHERE e.empno = A.empno ( +) and a.state = ' TEXAS ';

Which of the following group by queries will be generated from Oracle when running against the database?

A Select Deptno, Job, sum (SAL) from the EMP group by job, Deptno;

B Select sum (SAL), Deptno, job from EMP Group by job, Deptno;

C Select Deptno, Job, sum (SAL) from EMP;

D Select Deptno, sum (SAL), job from EMP Group by job, Deptno;

Check the output of the following SQL

Sql> Select A.deptno,a.job,b.loc,sum (a.sal)

2 from DMP A,dept b

3 Where A.deptno = B.depton

4 GROUP BY A.deptno,a.job,b.loc

5 ORDER by sum (a.sal);

Which column of the query returns the output in the order?

A A.deptno B. A.job C. B.loc D. SUM (A.sal)

Answer the question with the following code block:

Sql> Select Deptno,job,avg (SAL)

2 from EMP

3 GROUP BY Deptno,job

4 having AVG (SAL) >

5 (Select Sal

6 from EMP

7 where ename = ' MARTIN ');

Which of the following seed queries is used in the above statement?

A Single-line subquery B. Multiline subquery C. From sub-sentence query D. Dolez Query

The result returned by the query statement is that a single row is a single subquery

A query statement returns multiple rows or multiple rows of subqueries

In Oracle to generate a database table, which of the following is an invalid table-generated statement?

A CREATE TABLE cats (C_name varchar2 (Ten), c_weight number, C_owner varchar2 (10));

B CREATE TABLE My_cats as SELECT * from cats where owner = ' ME ';

C Create global temporary table Temp_cats (C_name varchar2 (Ten), c_weight number, C_owner varchar2 (10));

D CREATE TABLE 51cats as select C_name, c_weight from Cats where c_weight > 5;

The JOB table has three columns job_name, Job_desc, and Job_wage. Insert a new row in the Job_desc table with the following command:

sql> insert INTO Job (job_name, Job_desc)

2 values (' Luckey ', ' makes COFFEE ');

The results of this table are then queried:

Sql> select * from job where job_name = ' Luckey ';

Job_name Job_desc Job_wage

Luckey makes COFFEE 35

How is the data filled in with the Job_wage form?

A The Luckey row in the JOB table already exists and the job_wage is set to 35.

B The default clause defined by the Job_wage column when the table is generated specifies the value of the inserted row.

C The values clause of the INSERT statement contains hidden values that are joined when the row is inserted.

D The only reason is that the Job_wage value is added to the last update statement issued by the job table

Ii. Questions and Answers (20 points)

1. Write a common transaction control statement? For example, the specific application of the business?

Answer: Common things statement: Commit,rollback,savepoint

Example: ①dml A ...;

②commit;

③dml B ...;

④rollback;

⑤DML C;

⑥savepoint sp;

⑦dml D ...;

⑧rollback to SP;

Analysis: After the second line commits the statement execution, the fourth line of the rollback statement rollback can only be rolled back to the second row, stating that the third row of the DML statement is invalid for the database operation; After the setting of the six row is marked, the rollback on line eighth can be rolled back to the SP savepoint.

2. How can I get the system time in Oracle?

Answer: Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

3. What is the difference between truncate and delete?

A: Both delete the data in the table, except that the DELETE statement deletes only the data, does not release the storage space for the table, and the TRUNCATE statement deletes the table data and automatically frees the table storage space, which is more efficient.

4. What does DDL and DML stand for?

Answer: ddl:date definition Language (data definition language), including alter, rename, create, drop ...

Dml:date manipulation Language (Data manipulation language)

5. What statements does Oracle use to assign permissions to users?

Answer: Grant......to ...

Three, there is an Employee form EMP (ENO,ENAME,SALARY,DNO), wherein the meaning of each field is the employee number, name, salary and department number, there is a Department table Dept (Dno,dname), wherein the meaning of each field is Department number, department name. (10 points)

1) Use the SQL statement to increase the salary of the "sales department" for those employees who pay less than 3000 yuan 10%.

Update emp

Set salary=salary*1.1

where salary<3000 and dno= (

Select DNO from dept where dname= ' Sales department ');

Four, the following data sheet: (10 points)

Name

Course

Results

Tom

Mathematical

88

John doe

Chinese

80

Tom

English

51

Harry

English

62

John doe

Mathematical

92

Harry

Chinese

78

Tom

Chinese

56

Harry

Mathematical

42

John doe

English

73

......

......

......

1) write a generic query to find out the names of all the students who have passed the course.

Select Sname

From table_name

GROUP BY Sname

Having min (grade) >=60;

Oracle Exam Questions