杭州-SQL杭州国迈软件有限公司.doc

jenk

222

3页

0次

2021-02-22

40墨值下载

1. Which SELECT statement will get the result 'elloworld' from the string 'HelloWorld'?

A. SELECT SUBSTR( 'HelloWorld',1) FROM dual;

B. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;

C. SELECT LOWER(SUBSTR('HelloWorld', 1, 1) FROM dual;

D. SELECT LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual;

E. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;

ANS:

2. Which two statements are true regarding the ORDER BY clause? (Choose two.)

A. The sort is in ascending order by default.

B. The sort is in descending order by default.

C. The ORDER BY clause must precede the WHERE clause.

D. The ORDER BY clause is executed on the client side.

E. The ORDER BY clause comes last in the SELECT statement.

F. The ORDER BY clause is executed first in the query execution.

ANS:

3. Which two tasks can you perform using only the TO_CHAR function? (Choose two.)

A. convert 10 to 'TEN'

B. convert '10' to 10

C. convert '10' to '10'

D. convert 'TEN' to 10

E. convert a date to a character expression

F. convert a character expression to a date

ANS:

4. Which operator can be used with a multiple-row subquery?

A. =

B. LIKE

C. BETWEEN

D. NOT IN

E. Is

F. <>

ANS:

5. Which three statements about subqueries are true? (Choose three.)

A. A single row subquery can retrieve only one column and one row.

B. A single row subquery can retrieve only one row but many columns

C. A multiple row subquery can retrieve multiple rows and multiple columns.

D. A multiple row subquery can be compared using the ">" operator.

E. A single row subquery can use the IN operator.

F. A multiple row subquery can use the "=" operator.

ANS:

6. Based on the following conditions, which of the following will make a true condition?

last_name LIKE Ma%

A. MADNESS

B. Man

C. MARY

D. mary

E. Mandy

ANS:

7. Which of the following SQL statements can calculate and return the absolute value of

-33?

A. SELECT ABS(-33) "Absolute" FROM DUAL;

B. SELECT ABS(-33), Absolute FROM DUAL;

C. SELECT ABS("-33") Absolute FROM DUAL;

ANS:

8. What does the following SQL script do?

SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;

A. it has a syntax error, the AVG clause is not valid

B. it calculates the average of the maximum salaries of all the departments

C. it has a syntax error, the MAX clause is not valid

D. it has no error, but the GROUP BY clause is not effective

E. it has no error, but the AVG clause is not effective

F. it has no error, but the MAX clause is not effective

ANS:

9. Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose

three.)

A. SELECT TO_CHAR(2000, '$#,###.##') FROM dual;

B. SELECT TO_CHAR(2000, '$0,000.00') FROM dual;

C. SELECT TO_CHAR(2000, '$9,999.00') FROM dual;

D. SELECT TO_CHAR(2000, '$9,999.99') FROM dual;

E. SELECT TO_CHAR(2000, '$2,000.00') FROM dual;

F. SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;

ANS:

数据库实战

有三张表：

table dep

（

dep_id number(2), //

部门编号

dep_name varchar2(10)) //

部门名称

table emp (emp_id number(6), //

员工编号

emp_name varchar2(20), //

员工名称

dep_id number(2)) //

部门编号

table busi(emp_id number(6), //

员工编号

busi_id number(10), //

业务编号

busi_status char(2), //

业务状态

正常

未生效

失效

删除

busi_datetime date, //

业务时间

busi_desc varchar2(10)) //

业务描述

alter table busi add constraint PK_busi primary key (busi_id);

问题：

．

完成一个

procedure

，输入

dep_id,

输出该部门的员工数。并对该部门所有员工

的业务记录，按下列条件更新：

busi_datetime

早于

20010101 12:00:00

的，

busi_status

置为

‘删除’

busi_datetime

早于

20020101 12:00:00

的，

busi_status

置为

‘失效’，

busi_datetime

在

20020101 12:00:00

和

20030101 12:00:00

之间的，

busi_datetime

时间修

改为

原

busi_datetime

＋

天

．

Table busi

有

1000000

万记录。有一句

sql

：

select emp_id,busi_id,busi_datetime,busi_desc from busi where emp_id= :value1 and

busi_status = :value2

执行异常频繁，效率很差，请解释该

SQL

的执行计划。用什么方法可以提高该

SQL

的

执行效率？

．业务量增加，

table busi

数据量急剧增加，每天增加

万数据，又删除该表的历史数

据

万，二个月后，上面的

SQL

执行效率极差。用什么方法可以提高该

SQL

的执行效

率？

of 3

40墨值下载

database

相关文档

评论