杭州-SQL杭州国迈软件有限公司笔试题

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: E

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: A E

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: C E

4. Which operator can be used with a multiple-row subquery?
   A. =
   B. LIKE
   C. BETWEEN
   D. NOT IN
   E. Is
   F. <>

ANS: D

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: A D E

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: B E

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: A

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: B

9. Which three SELECT statements display 2000 in the format "$2,000.00"?(Choosethree.)A.SELECTT{O}_{C}HAR(2000{,}^{\prime }$#,###.##’) 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^{\prime })FROMdual;F.SELECTT{O}_{C}HAR(2000{,}^{\prime }$N,NNN.NN’) FROM dual;

ANS: B C D

数据库实战
有三张表：
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 天
CREATE OR REPLACE PRODURE TT(NDEP_ID VARCHAR2)
AS
CURSOR TT IS SELECT EMP_ID,BUSI_DATETIME FROM BUSI T1,EMP T2 WHERE T1.EMP_ID = T2.EMP_ID AND T2.DEP_ID = NDEP_ID ;
BEGIN
FOR TNT IN TT LOOP
IF TNT.BUSI_DATETIME < TO_DATE(‘20010101 12:00:00’,’YYYY-MM-DD HH24:MI:SS’)
THEN
UPDATE BUSI SET BUSI_STATUS = ‘删除’ WHERE EMP_ID TNT.EMP_ID;
ELSE IF TNT.BUSI_DATETIME < TO_DATE(‘20020101 12:00:00’,’YYYY-MM-DD HH24:MI:SS’)
THEN
UPDATE BUSI SET BUSI_STATUS = ‘失效’ WHERE EMP_ID TNT.EMP_ID;
ELSE IF TNT.BUSI_DATETIME < TO_DATE(‘20030101 12:00:00’,’YYYY-MM-DD HH24:MI:SS’)
THEN
UPDATE BUSI SET BUSI_DATETIME = TNT.BUSI_DATETIME + 5 WHERE EMP_ID TNT.EMP_ID;
END IF;
COMMIT;
END LOOP
END

CREATE OR REPLACE PRODURE TT(NDEP_ID VARCHAR2)
AS
CURSOR TT IS SELECT EMP_ID,BUSI_DATETIME
FROM BUSI T1,EMP T2
WHERE T1.EMP_ID = T2.EMP_ID AND T2.DEP_ID = NDEP_ID ;
num1 number:=0;
BEGIN
/* SELECT count(*) into num1
FROM BUSI T1,EMP T2
WHERE T1.EMP_ID = T2.EMP_ID AND T2.DEP_ID = NDEP_ID ;
*/
FOR TNT IN TT LOOP
IF TNT.BUSI_DATETIME < TO_DATE(‘20010101 12:00:00’,‘YYYY-MM-DD HH24:MI:SS’)
THEN
UPDATE BUSI SET BUSI_STATUS = ‘删除’ WHERE EMP_ID TNT.EMP_ID;
ELSIF TNT.BUSI_DATETIME < TO_DATE(‘20020101 12:00:00’,‘YYYY-MM-DD HH24:MI:SS’)
THEN
UPDATE BUSI SET BUSI_STATUS = ‘失效’ WHERE EMP_ID TNT.EMP_ID;
ELSIF TNT.BUSI_DATETIME < TO_DATE(‘20030101 12:00:00’,‘YYYY-MM-DD HH24:MI:SS’)
THEN
UPDATE BUSI SET BUSI_DATETIME = TNT.BUSI_DATETIME + 5 WHERE EMP_ID TNT.EMP_ID;
END IF;
COMMIT;
num1:=num1+1;
END LOOP;
dbms_output.put_line(num1);
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 的执行效率？

Where 子句使用参数导致全表扫描，使用字符型进行比较
针对比较创建联合索引

3．业务量增加，table busi 数据量急剧增加，每天增加 20 万数据，又删除该表的历史数据 10 万，二个月后，上面的SQL执行效率极差。用什么方法可以提高该 SQL 的执行效率？
定时分析，降低hwm高水位
按时间分区，定时对象统计收集