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), //业务状态 1 正常 2 未生效 3 失效 4 删除
busi_datetime date, //业务时间
busi_desc varchar2(10)) //业务描述
alter table busi add constraint PK_busi primary key (busi_id);
问题：
1． 完成一个 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 ＋ 5 天
2． 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 的执行效率？
3．业务量增加，table busi 数据量急剧增加，每天增加 20 万数据，又删除该表的历史数据 10 万，二个月后，上面的SQL执行效率极差。用什么方法可以提高该 SQL 的执行效率？
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), //业务状态 1 正常 2 未生效 3 失效 4 删除
busi_datetime date, //业务时间
busi_desc varchar2(10)) //业务描述
alter table busi add constraint PK_busi primary key (busi_id);
问题：
1． 完成一个 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 ＋ 5 天
2． 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 的执行效率？
3．业务量增加，table busi 数据量急剧增加，每天增加 20 万数据，又删除该表的历史数据 10 万，二个月后，上面的SQL执行效率极差。用什么方法可以提高该 SQL 的执行效率？
数据库实战
有三张表：
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), //业务状态 1 正常 2 未生效 3 失效 4 删除
busi_datetime date, //业务时间
busi_desc varchar2(10)) //业务描述
alter table busi add constraint PK_busi primary key (busi_id);
问题：
1． 完成一个 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 ＋ 5 天 假设输入的部门ID变量是input_dep_id，存储过程如下： create or replace procedure proc_test (
input_dep_id in number,emp_count out number
)
as begin
select count(*) into emp_count
from emp
where dep_id=input_dep_id;
update busi
set busi_status='4'
where busi_datetime<to_date('2001-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') and
emp_id in (select emp_id from emp where dep_id=input_dep_id) ;
update busi
set busi_status='3'
where busi_datetime<to_date('2002-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') and
emp_id in (select emp_id from emp where dep_id=input_dep_id);
update busi
set busi_datetime=busi_datetime+5
where busi_datetime<to_date('2003-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') and
busi_datetime>to_date('2002-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') and
emp_id in (select emp_id from emp where dep_id=input_dep_id);
end;
2． 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 的执行效率？ 上面的执行计划将进行全表扫描。如果更新不频繁，可以考虑建立：emp_id的b树索引。（如果几乎是静态表还可以考虑为 busi_status建立位图索引。）
3．业务量增加，table busi 数据量急剧增加，每天增加 20 万数据，又删除该表的历史数据 10 万，二个月后，上面的SQL执行效率极差。用什么方法可以提高该 SQL 的执行效率？应该将表exp出来，drop掉原来的表，再imp进去。这应该是考察高水位和碎片的处理。
UPDATE busi
SET busi_status = '4'
where busi_datetime < '20010101 12:00:00'
AND emp_ID IN
( SELECT EMP_ID FROM EMP A
INNER JOIN DEP ON DEP.dep_id = a.dep_id
and a.dep_id = @DEP_ID)
UPDATE busi
SET busi_status = '3'
where busi_datetime between '20010101 12:00:00' and '20020101 12:00:00'
AND emp_ID IN
( SELECT EMP_ID FROM EMP A
INNER JOIN DEP ON DEP.dep_id = a.dep_id
and a.dep_id = @DEP_ID )
UPDATE busi
SET busi_datetime = dateadd(dd,5,busi_datetime)
where busi_datetime between '20020101 12:00:00' and '20030101 12:00:00'
AND emp_ID IN
( SELECT EMP_ID FROM EMP A
INNER JOIN DEP ON DEP.dep_id = a.dep_id
and a.dep_id = @DEP_ID)