关于数据库的基本测试（oracle,sql）
[align=center][b][size=2][color=#000000][font=Arial][/font][/color][/size][/b][/align][align=center][b][color=#000000][font=Arial][size=5]ORACLE[/size][/font][/color][/b][/align][b][size=2][color=#000000][font=Arial]1. Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?[/font][/color][/size][/b][b][font=Arial][size=2][color=#000000][/color][/size][/font][/b]
[list=a][*][font=Arial][size=10pt][color=#000000]SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\'; [/color][/size][/font][*][font=Arial][size=10pt][color=#000000]SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE; [/color][/size][/font][*][font=Arial][size=10pt][color=#000000]SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%'; [/color][/size][/font][*][b][font=Arial][size=10pt][color=red]SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';[/color] [/size][/font][/b][/list][font=Arial][size=10pt][color=#000000][/color][/size][/font]
[b][font=Arial][size=10pt]2.[/size][/font][/b][b][font=Arial][size=10pt][color=#000000] Refer to the SQL codes below: [/color][/size][/font][/b]
[font=Arial][size=10pt][color=#000000]SELECT manager_id, last_name, hire_date, salary, AVG (salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS C_mavg FROM employees; [/color][/size][/font]
[font=Arial][size=10pt][color=#000000]What has been achieved? [/color][/size][/font]
[font=Arial][size=10pt][color=#000000][/color][/size][/font]
[list=a][*][font=Arial][size=10pt][color=#000000]Because of a syntax problem, no row will be returned[/color][/size][/font][*][font=Arial][size=10pt][color=#000000]It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager [/color][/size][/font][*][font=Arial][size=10pt][color=#000000]It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just before the employee [/color][/size][/font][*][b][font=Arial][size=10pt][color=red]It calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee[/color] [/size][/font][/b][*][font=Arial][size=10pt][color=#000000]It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just after the employee [/color][/size][/font][/list][font=Arial][size=10pt][color=#000000][/color][/size][/font]
[b][font=Arial][size=10pt][color=#000000]3. with 9i SQL Plus, What kinds of commands can you enters at the command prompt (Choose all that apply)? [/color][/size][/font][/b]
[font=Arial][size=10pt][color=#000000][/color][/size][/font]
[list=a][*][b][font=Arial][color=red][size=10pt]PL/SQL blocks[/size][/color][/font][/b][*][b][font=Arial][size=10pt][color=red]SQL*Plus commands[/color] [/size][/font][/b][*][font=Arial][size=10pt][color=#000000]Security commands [/color][/size][/font][*][b][font=Arial][color=red][size=10pt]SQL commands [/size][/color][/font][/b][/list][font=Arial][size=10pt][color=#000000][/color][/size][/font]
[font=Arial][size=2][color=#000000][b]4. to write a query that performs an outer join of tables A and B and returns all rows from B, You need to write [/b][/color][/size][/font]
[b][font=Arial][size=2][color=#000000][/color][/size][/font][/b]
[list=a][*][font=Arial][size=10pt][color=#000000]any outer join [/color][/size][/font][*][font=Arial][size=10pt][color=#000000]a left outer join [/color][/size][/font][*][font=Arial][size=10pt][color=#000000]a cross join [/color][/size][/font][*][b][font=Arial][size=10pt][color=red]a right outer join[/color] [/size][/font][/b][*][font=Arial][size=10pt][color=#000000]an inner join [/color][/size][/font][/list][font=Arial][size=10pt][color=#000000]
[b][font=Arial][size=10pt]5.[/size][/font][/b][b][font=Arial][size=10pt] Which of the following is true if you use the alter tablespace statement and specify the TEMPORARY clause (Choose all that apply)? [/size][/font][/b]
[font=Arial][size=10pt][font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]Oracle no longer perform any checkpoint for the online datafiles in the tablespace [/size][/font][*][font=Arial][color=red][size=10pt][b]Oracle performs a checkpoint for all online datafiles in the tablespace [/b][/size][/color][/font][*][font=Arial][color=red][size=10pt][b]Oracle does not ensure that all files are written [/b][/size][/color][/font][*][font=Arial][size=10pt][b][color=red]The offline files may require media recovery before you bring the tablespace online[/color] [/b][/size][/font][*][font=Arial][size=10pt]The offline files may require media recovery after you bring the tablespace online [/size][/font][/list][font=Arial][size=10pt][/size][/font]
[b][color=black]6.[/color] Which of the following correctly shows the correct use of the TRUNC command on a date? [/b]
[list=a][*][font=Arial][size=10pt]SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL; [/size][/font][*][font=Arial][size=10pt]TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL; [/size][/font][*][font=Arial][color=red][size=10pt][b]SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;[/b][/size][/color][/font][*][font=Arial][size=10pt]date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL [/size][/font][/list][font=Arial][size=10pt][/size][/font]
[b]7. To grant a system privilege with the GRANT statement, you must (Choose all that apply)? [/b]
[list=a][*][font=Arial][size=10pt]have been granted the GRANT ROLE PRIVILEGE system privilege [/size][/font][*][font=Arial][color=red][size=10pt][b]have been granted the system privilege with the ADMIN OPTION [/b][/size][/color][/font][*][font=Arial][size=10pt][b][color=red]have been granted the GRANT ANY PRIVILEGE system privilege[/color] [/b][/size][/font][*][font=Arial][size=10pt]have been granted the system privilege with the GRANT OPTION [/size][/font][/list][font=Arial][size=10pt][/size][/font]
[b][color=black]8.[/color] Which of the following are the conditions that must be met before you can use RENAME DATAFILE with the alter tablespace command (Choose all that apply)? [/b]
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][color=red][size=10pt][b]the datafile must be taken offline before renaming [/b][/size][/color][/font][*][font=Arial][size=10pt][b][color=red]the database must be open[/color] [/b][/size][/font][*][font=Arial][size=10pt]when only a single datafile is to be renamed [/size][/font][*][font=Arial][size=10pt]when only a single datafile on the same drive is to be renamed [/size][/font][/list][font=Arial][size=10pt][/size][/font]
[b][color=black]9.[/color] Before making a tablespace read only, which of the following conditions must be met (Choose all that apply)? [/b]
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]The tablespace must contain an active rollback segments. [/size][/font][*][font=Arial][color=red][size=10pt][b]The tablespace must be online. [/b][/size][/color][/font][*][font=Arial][color=red][size=10pt][b]The tablespace must not contain any active rollback segments. [/b][/size][/color][/font][*][font=Arial][size=10pt][b][color=red]The tablespace must not be involved in an open backup.[/color] [/b][/size][/font][*][font=Arial][size=10pt]The tablespace must be involved in an open backup. [/size][/font][/list]
[b][color=black]10.[/color] The MANAGE TABLESPACE system privilege allows you to perform which of the following operations (Choose all that apply)? [/b]
[b][list=a][*][font=Arial][color=red][size=10pt]Take the tablespace offline [/size][/color][/font][*][font=Arial][color=red][size=10pt]Begin a backup [/size][/color][/font][*][font=Arial][color=red][size=10pt]End a backup [/size][/color][/font][*][font=Arial][color=red][size=10pt]Take the tablespace online [/size][/color][/font][*][font=Arial][color=red][size=10pt]Make the tablespace read only [/size][/color][/font][*][font=Arial][size=10pt][color=red]Make the tablespace read write[/color] [/size][/font][/list][font=Arial][size=10pt][/size][/font]
11. Which of the following has been achieved by the following SQL codes?
[b]SELECT employee_id [/b]
[b]FROM employees [/b]
[b]WHERE commission_pct = .5 OR salary > 23000; [/b]
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]it returns employees who have a 50% of a salary greater than $23,000: [/size][/font][*][font=Arial][color=red][size=10pt]it returns employees who have a 50% commission rate or a salary greater than $23,000:[/size][/color][/font][*][font=Arial][size=10pt]runtime error [/size][/font][*][font=Arial][size=10pt]it returns employees who have a 50% of a salary less than $23,000: [/size][/font][*][font=Arial][size=10pt]invalid syntax [/size][/font][*][font=Arial][size=10pt]it returns employees who have a 50% commission rate and a salary greater than $23,000: [/size][/font][/list][font=Arial][size=10pt][/size][/font]
12. Which of the following has been achieved by the following SQL codes?
[b]SELECT * FROM employees [/b]
[b]WHERE hire_date < TO_DATE ('01-JAN-1999', 'DD-MON-YYYY') AND salary > 3500; [/b]
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]only those hired before 1999 and earning less than $3500 a month are returned [/size][/font][*][font=Arial][size=10pt]compile time error [/size][/font][*][font=Arial][size=10pt]only those hired after 1999 and earning more than $3500 a month are returned [/size][/font][*][font=Arial][size=10pt]runtime error [/size][/font][*][font=Arial][size=10pt][color=red]only those hired before 1999 and earning more than $3500 a month are returned[/color] [/size][/font][/list][font=Arial][size=10pt][/size][/font]
13. Which of the following SQL statements can calculate and return the absolute value of -33?
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]SELECT ABS("-33") Absolute FROM DUAL; [/size][/font][*][font=Arial][size=10pt]SELECT ABS('-33') "Absolute" FROM DUAL; [/size][/font][*][font=Arial][size=10pt][color=red]SELECT ABS(-33) "Absolute" FROM DUAL;[/color] [/size][/font][*][font=Arial][size=10pt]SELECT ABS(-33), Absolute FROM DUAL; [/size][/font][/list][font=Arial][size=10pt][/size][/font]
[font=Arial][size=10pt][/size][/font][color=black]14. Which two statements about Subqueries are true? (Choose two.) [/color]
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]A single row subquery can retrieve data from only one table. [/size][/font][/list][/b][font=Arial][size=10pt]
[list=a][*][b][font=Arial][size=10pt]A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause. [/size][/font][/b][*][font=Arial][size=10pt][color=red]A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.[/color] [/size][/font][*][b][font=Arial][size=10pt]A single row subquery can retrieve data from more than one table. [/size][/font][/b][*][font=Arial][size=10pt][color=red]A single row subquery cannot be used in a condition where the LIKE operator is used for comparison[/color]. [/size][/font][*][font=Arial][size=10pt]A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison. [/size][/font][/list][font=Arial][size=10pt][/size][/font]
[color=black][b]15. Examine the description of the STUDENTS table: [/b][/color]
[font=Arial][size=10pt][b]STD_ID
NUMBER (4)[/b][/size][/font]
[b][font=Arial][size=10pt]COURSE_ID
VARCHAR2 (10)[/size][/font][/b]
[b][font=Arial][size=10pt]START_DATE
DATE[/size][/font][/b]
[b][font=Arial][size=10pt]END_DATE
DATE[/size][/font][/b]
[b][font=Arial][size=10pt][/size][/font][/b]
[b][font=Arial][size=10pt]Which two aggregate functions are valid on the START_DATE column? (Choose two) [/size][/font][/b]
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]SUM(start_date) [/size][/font][*][font=Arial][size=10pt]AVG(start_date) [/size][/font][*][b][font=Arial][size=10pt][color=red]COUNT(start_date)[/color] [/size][/font][/b][*][font=Arial][size=10pt]AVG(start_date, end_date) [/size][/font][*][b][font=Arial][size=10pt][color=red]MIN(start_date)[/color] [/size][/font][/b][*][font=Arial][size=10pt]MAXIMUM(start_date) [/size][/font][/list][font=Arial][size=10pt][/size][/font]
[color=black][b]16. Examine the structure of the EMP_DEPT_VU view: [/b][/color]
[b]Column Name
Type Remarks [/b]
[b]EMPLOYEE_ID
NUMBER [/b]
[b]From the EMPLOYEES table:[/b]
[b]EMP_NAME
VARCHAR2 (30) [/b]
[b]JOB_ID
VARCHAR2 (20) [/b]
[b]SALARY
NUMBER [/b]
[b]DEPARTMENT_ID
NUMBER [/b]
[b]From the DEPARTMENTS table:[/b]
[b]DEPT_NAME VARCHAR2 (30) [/b]
[b]Which SQL statement produces an error? [/b]
[font=Arial][size=10pt][/size][/font]
[list=a][*][font=Arial][size=10pt]SELECT * FROM emp_dept_vu; [/size][/font][*][font=Arial][size=10pt]SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department_id;[/size][/font][*][font=Arial][size=10pt]SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id; [/size][/font][*][font=Arial][size=10pt]SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM(salary) > 20000; [/size][/font][*][b][font=Arial][size=10pt][color=red]None of the statements produce an error; all are valid.[/color] [/size][/font][/b][/list]
[/size][/font][/size][/font][/color][/size][/font]
[[i] 本帖最后由 canning 于 2007-6-23 14:19 编辑 [/i]]
页:
[1]
