（含图）

You want to display details of all customers who reside in cities starting with the letter D followed

by at least two characters.

Which query can be used?

（含图）

Management requires a report of unique promotion costs in each promotion category. 

Which query would satisfy this requirement?

（含图）

（含图）

You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time interval.

Examine this SQL statement,What will be the result?

（含图）

Which statement is true about using functions in WHERE and HAVING?

SELECT d.department_name, ROUND (AVG (NULLIF (e.salary,0))) AS avgsal, MAX(e.salary) AS maxsal 

FROM employees e JOIN departments d ON (e.department_id = d.department_id)

GROUP BY d. department_name ORDER BY 2;

In which order are the rows displayed?

PROD_ID PROD_NAME PROD_LIST CATEGORY_ID

-----------------------------------------------------------------------------------------------------------------------------

101 Plate 10 1

102 Cup 20 1

103 Saucer 20 1

104 Knife 30 1

105 Fork 30 1

Examine these queries:

1. SELECT prod_name, prod_list FROM products WHERE prod_list = ANY (10, 20) AND category_id = 1; 

2. SELECT prod_name, prod_list FROM products WHERE prod_list IN (10, 20) AND category_id = 1;

3. SELECT prod_name, prod_list FROM products WHERE prod_list = ALL (10, 20) AND category_id = 1; 

Which queries generate the same output?

$ sqlplus hr/hr@orcl @abc.sql

What will happen upon execution?

Examine the description of the EMPLOYEES table:

Name Null? Type

-----------------------------------------------------------------------

EMPLOYEE_ID NOT NULL NUMBER(4)

LAST_NAME VARCHAR2 (10)

HIRE_DATE DATE

SALARY NUMBER(6,2)

Examine these requirements:

1. Display the last name, date of hire and the number of years of service for each employee.

2. If the employee has been employed 5 or more years but less than 10, display "5+ years of

service".

3. If the employee has been employed 10 or more years but less than 15, display "10+ years of

service".

4. If the employee has been employed 15 or more years, display "15+ years of service".

5. If none of these conditions matches, display "<5 years of service".

6. Sort the results by the HIRE_DATE column.

Which statement satisfies all the requirements?

What is true about non-equijoin statement performance?

Examine this statement which returns the name of each employee and their manager:

SELECT e.last_name AS emp, m.1ast_name AS mgr FROM employees e JOIN managers m

ON e.manager_id = m.employee_id

ORDER BY emp;

You want to extend the query to include employees with no manager.

What must you add before JOIN to do this?

Examine the data in the EMPLOYEES table:

EMPLOYEE ID LAST_NAME MONTHLY_SALARY MONTHLY_COMMISSION_PCT

------------------ ------------------- --------------------------- ------------------------------------------

101 Kochhar 24000 <NULL>

102 Ernst 17000 .5

103 Rajs 21000 .2

104 Lorentz 25000 <NULL>

105 Morris 12000 <NULL>

Which statement will compute the total annual compensation for each employee?

 You want to increase the size of the buffer cache. Free memory is available to increase the size of the buffer cache. 

You execute the command: 

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M; 

（含图）

What is the outcome?

CONNECT / AS SYSDBA 

CREATE DIRECTORY dumpdir AS ‘/u01/app’; 

Directory created.

GRANT READ,WRITE ON DIRECTORY dumpdir TO PUBLIC;

 Grant succeeded. 

Create USER TEST IDENTIFIED BY test; 

User created. 

GRANT CREATE SESSION,RESOURCE,UNLIMITED TABLESPACE TO test; 

Grant succeeded. 

CONN test/test.

Connected. 

CREATE TABLE test_employees (id NUMBER(3), name VARCHAR2(20), salary NUMBER (7)); 

Table created. 

SQL> CREATE SYNONYM emp FOR test_employees; 

Synonym create. 

Now examine this command: $ expdp test/test DIRECTORY=dumpdir DUMPFILE=test_emp.dmp LOGFILE=test.log TABLES=emp 

Which is true?

1. Compress rows for all DML statements on that table 

2. Minimize the overheads associated with compression 

Which compression option is best suited for this?

BOSTON_DB and DALLAS_DB, in two different locations.

Each database has a tnsnames.ora file defining DALLAS_DB as a service name.

Examine this command: 

CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING ‘dallas_db’;

How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT schema in DALLAS_DB?

A PFILE is also available. 

You execute this command: 

ALTER SYSTEM SET DB_CACHE_SIZE=100K; 

Where is the value changed?

SQL> SELECT COUNT(*) FROM emp; 

COUNT (*)

14 

SQL> CREATE GLOBAL TEMPORARY TABLE t_emp AS SELECT * FROM

emp; 

Table created. 

SQL> INSERT INTO t_emp SELECT * FROM emp; 

14 rows created.

 SQL> COMMIT;

 Commit complete. 

SQL> INSERT INTO t_emp SELECT * FROM emp; 

14 rows created. 

SQL> SELECT COUNT(*) FROM t_emp; 

How many rows are retrieved by the last query?

（含图）

Which statement is true?