Oracle

Front 1

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do
you use?

A. SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id = '%SA_';

B. SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA_';

C. SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA_' ESCAPE "\";

D. SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA\_%' ESCAPE '\';

Back 1

Answer: D
ESCAPE identifier to search for the actual % and _ symbol

Front 2

You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
What happens when you execute this DELETE statement?

DELETE employees;
A. You get an error because of a primary key violation.
B. The data and structure of the EMPLOYEES table are deleted.
C. You get an error because the statement is not syntactically correct.
D. The data in the EMPLOYEES table is deleted but not the structure.

Back 2

Answer: D
You can remove existing rows from a table by using the DELETE statement.
DELETE [FROM] table
[WHERE condition];

Front 3

You need to create a table named ORDERS that contains four columns:

1. an ORDER_ID column of number data type
2. a CUSTOMER_ID column of number data type
3. an ORDER_STATUS column that contains a character data type
4. a DATE_ORDERED column to contain the date the order was placed

When a row is inserted into the table, if no value is provided for the status of the order, the value
PENDING should be used instead.

Which statement accomplishes this?
A. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered VARCHAR2 );
B. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
C. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
D. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status NUMBER(10) DEFAULT 'PENDING',
date_ordered DATE );
E. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
F. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );

Back 3

Answer:B
Requirement that Order_Status should be a character data type
Not E: Order_status must be a character data type. There is also a syntax error.

Front 4

Evaluate this SQL statement:

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A. selection, projection, join
B. selection, intersection, join
C. intersection, projection, join
D. difference, projection, product
E. difference, projection, join

Back 4

Answer: A
Selection, projection and join capabilities of a SELECT statement are performed in this view.

Front 5

Which three are DATETIME data types that can be used when specifying column definitions?
(Choose three.)
A. INTERVAL YEAR TO MONTH
B. INTERVAL DAY TO SECOND
C. TIMESTAMP
D. INTERVAL MONTH TO DAY
E. TIMESTAMP WITH DATABASE TIMEZONE

Back 5

Answer: A,B,C
TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH can be used to
specify column definition.

Front 6

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12 'Annual Salary' FROM employees;
B. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees
C. SELECT ename, salary*12 "Annual Salary" FROM employees;
D. SELECT ename, salary*12 AS Annual Salary FROM employees;

Back 6

Answer: C
This SQL statement provides correct syntax to generate the alias Annual Salary for the calculated
column SALARY*12.

Front 7

Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees( first_name, last_name)
VALUES( 'John', 'Smith');
B. INSERT INTO employees
VALUES ( NULL, 'John', 'Smith');
C. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, 'John', ' ');
D. INSERT INTO employees (employee_id)
VALUES (1000);
E. INSERT INTO employees (first_name, last_name, employee_id)
VALUES ( 1000, 'John', 'Smith');
F. INSERT INTO employees
VALUES ( '1000', 'John', NULL);

Back 7

Answer: C,D,F
EMPLOYEE_ID is a primary key.
Incorrect answer :
A EMPLOYEE_ID cannot be null
B EMPLOYEE_ID cannot be null

Front 8

Which SELECT statement should you use to extract the year from the system date and display it
in the format "1998"?
A. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;
B. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year')
FROM dual;
C. SELECT TO_DATE(SYSDATE,'yyyy')
FROM dual;
D. SELECT TO_CHAR(SYSDATE,'yyyy')
FROM dual;
E. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY')
FROM dual;

Back 8

Answer: D
Function TO_CHAR(x, y) converts the value x to a character or converts a date to a character
string using formatting conventions.

Front 9

Which two statements about sequences are true? (Choose two.)
A. You use a CURRVAL pseudo column to generate a value from a sequence that would be used
for a specified database column.
B. You use a CURRVAL pseudo column to look at the current value just generated from a
sequence, without affecting the further values to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by
actually retrieving the value from the sequence.
D. You use a NEXTVAL pseudo column to look at the next possible value that would be generated
from a sequence, without actually retrieving the value.
E. You use a REUSE clause when creating a sequence to restart the sequence once it generates
the maximum value defined for the sequence.
F. If a sequence starting from a value 100 and incremented by 1 is used by more than one
application, then all of these applications could have a value of 105 assigned to their column
whose value is being generated by the sequence.

Back 9

Answer: B,C
You use a CURRVAL pseudo column to look at the current value just generated from a sequence,
without affecting the further values to be generated from the sequence. You use a NEXTVAL
pseudo column to obtain the next possible value from a sequence by actually retrieving the value
from the sequence.
Explanation:
You use a CURRVAL pseudo column to look at the current value just generated from a sequence,
without affecting the further values to be generated from the sequence. You use a NEXTVAL
pseudo column to obtain the next possible value from a sequence by actually retrieving the value
from the sequence.
Incorrect Answers:
A: You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by
actually retrieving the value from the sequence.
D: You use a CURRVAL pseudo column to look at the current value just generated from a
sequence, without affecting the further values to be generated from the sequence.
E: This statement is not correct. There is no limitation like that in Oracle.
F: You use CYCLE clause, not REUSE, when creating a sequence to restart the sequence once it
generates the maximum value defined for the sequence.OCP Introduction to Oracle 9i: SQL Exam
Guide, Jason Couchman, p. 315-322Chapter 7: Creating Other Database Objects in Oracle

Front 10

In which two cases would you use an outer join? (Choose two.)
A. Only when the tables have a primary key-foreign key relationship.
B. The tables being joined have NOT NULL columns.
C. The tables being joined have both matched and unmatched data.
D. The columns being joined have NULL values.
E. The tables being joined have only matched data.
F. The tables being joined have only unmatched data.

Back 10

Answer: C,D
You use an outer join to also see rows that do not meet the join condition.

Front 11

In which scenario would an index be most useful?
A. The indexed columns are part of an expression.
B. The indexed column is declared as NOT NULL.
C. The indexed column contains a wide range of values.
D. The indexed columns are used in the FROM clause.

Back 11

Answer: C
Index will be useful if the indexed column contains a wide range of values. Especially B-tree
indexes will work better for tables with a wide range of values. But for tables just with some distinct
values bitmap indexes will be more helpful.

Front 12

Which are DML statements? (Choose all that apply.)
A. DROP...
B. COMMIT
C. DELETE
D. MERGE
E. UPDATE
F. CREATE

Back 12

Answer: C,D,E
MERGE, UPDATE and DELETE commands are data manipulation language (DML) statements.

Front 13

Which two are attributes of iSQL*Plus? (Choose two.)
A. /SQL*Plus commands cannot be abbreviated.
B. /SQL*Plus commands are accessed from a browser.
C. /SQL*Plus commands are used to manipulate data in table.
E. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.

Back 13

Answer: D,E
D: SQL*Plus commands can be used to manipulate data in tables .
E: iSQL*Plus is a proprietary interface for executing SQL Statements
Incorrect Answers:
A: SQL*Plus commands can be abbreviated. Like command DESCRIBE can be abbreviated as
DESC, or SELECT as SELE.
B: SQL*Plus commands are not accessed from a browser.
C: SQL DML not SQL Plus is used to manipulate data defintionsOCP Introduction to Oracle 9i:
SQL Exam Guide, Jason Couchman, p. 38-48Chapter 1: Overview of Oracle Databases

Front 14

Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?
A. 45.95 and 45.93
B. 50 and 45.93
C. 46 and 45
D. 50 and 45.9
E. 46 and 45.93
F. 45 and 45.93

Back 14

Answer: B
Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left.
TRUNC (45.936,2) will truncate value to 2 decimal
The answer will be 50 and 45.93

Front 15

Which SQL statement returns a numeric value?
A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
B. SELECT sysdate-hire_date
FROM EMP;
C. SELECT TO_NUMBER(hire_date + 7)
FROM EMP;
D. SELECT ROUND(hire_date)
FROM EMP;

Back 15

Answer: B
Explanation:
DATE value subtract DATE value will return numeric value.

Front 16

Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can mix single row columns with aggregate functions in the column list of a SELECT
statement by grouping on the single row columns.
C. You can use aggregate functions only in the column list of the SELECT clause and in the
WHERE clause of a SELECT statement.
D. You can use aggregate functions on a table, only by grouping the whole table as one single
group.
E. You can pass column names, expressions, constants, or functions as parameters to an
aggregate function.
F. You cannot group the rows of a table by more than one column while using aggregate
functions.

Back 16

Answer: B,E
It is possible to mix single row columns with aggregate functions in the column list of a
SELECT statement by grouping on the single row columns. Also it is acceptable to pass
column names, expressions, constraints, or other functions as parameters to an aggregate
function.

Front 17

A. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
B. ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
C. ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
D. ALTER TABLE students
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The
table is currently empty. Which statement accomplishes this task?
ADD PRIMARY KEY student_id;
E. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Back 17

Answer: E
ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (coloumn);
Incorrect answer :
A wrong syntax
B wrong syntax
C wrong syntax
D no such MODIFY keyword

Front 18

Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
EMPLOYEE_ID NUMBER
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
LAST_NAME VARCHAR2(25)
DEPARTMENTS
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
DEPARTMENT_NAME VARCHAR2(35)
LOCATION_ID NUMBER
You want to create a report displaying employee last names, department names, and locations.
Which query should you use?
A. SELECT last_name, department_name, location_id
FROM employees , departments ;
B. SELECT employees.last_name, departments.department_name, departments.location_id
FROM employees e, departments D
WHERE e.department_id =d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments D
WHERE manager_id =manager_id;
D. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments D
WHERE e.department_id =d.department_id;

You want to create a report displaying employee last names, department names, and locations.
Which query should you use?
A. SELECT last_name, department_name, location_id
FROM employees , departments ;
B. SELECT employees.last_name, departments.department_name, departments.location_id
FROM employees e, departments D
WHERE e.department_id =d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments D
WHERE manager_id =manager_id;
D. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments D
WHERE e.department_id =d.department_id;

Back 18

Answer: D
Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.
Incorrect answer :
A there is no join
B invalid syntax
C does not involve the join in the primary and foreign key

Front 19

The EMP table contains these columns:
LAST_NAME VARCHAR2 (25)
SALARY NUMBER (6,2)
DEPARTMENT_ID NUMBER (6)
You need to display the employees who have not been assigned to any department. You write the
SELECT statement:
SELECT LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMP
WHERE DEPARTMENT_ID = NULL;
What is true about this SQL statement ?
A. The column in the WHERE clause should be changed to display the desired results.
B. The operator in the WHERE clause should be changed to display the desired results.
C. The WHERE clause should be changed to use an outer join to display the desired results.
D. The SQL statement displays the desired results.

Back 19

Answer: B
The operator in the WHERE clause should be changed to display the desired results. There are
times when you want to substitute a value in place of NULL. Oracle provides this functionality with
a special function, called NVL(). You cannot use operation equal with NULL, but you can achieve
desired results using NVL() function after the WHERE clause.

Front 20

What is necessary for your query on an existing view to execute successfully?
A. The underlying tables must be in the same schema.
B. You need SELECT privileges only on the underlying tables.
C. The underlying tables must have data.
D. You need SELECT privileges on the view.

Back 20

Answer: D
Explanation:
To query an existing view you need to have SELECT privileges on the view.

Front 21

Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100
FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the
above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of
$100, multiplied by 12"?
A. SELECT ename, sal, 12*(sal+100)
FROM emp;
B. SELECT ename, sal+100,*12
FROM emp;
C. No change is required to achieve the desired results.
D. SELECT ename, sal, (12*sal)+100
FROM emp;

Back 21

Answer: A
Explanation:
to achieve the result you must add 100 to sal before multiply with 12.
Select ename, sal, 12*(sal+100) from EMP;
Incorrect answer :
* Multiplication and division has priority over addition and subtraction in Operator precedence.
C Give wrong results
D Wrong syntax

Front 22

What are two reasons to create synonyms? (Choose two.)
A. You have too many tables.
B. Your tables names are too long.
C. Your tables have difficult names.
D. You want to work on your own tables.
E. You want to use another schema's tables.
F. You have too many columns in your tables.

Back 22

Answer: B,C
Explanation:
to achieve the result you must add 100 to sal before multiply with 12.
Select ename, sal, 12*(sal+100) from EMP;
Incorrect answer :
* Multiplication and division has priority over addition and subtraction in Operator precedence.
C Give wrong results
D Wrong syntax

Front 23

What are two reasons to create synonyms? (Choose two.)
A. You have too many tables.
B. Your tables names are too long.
C. Your tables have difficult names.
D. You want to work on your own tables.
E. You want to use another schema's tables.
F. You have too many columns in your tables.

Back 23

Answer: B,C
Explanation:
Create a synonyms when the names of the tables are too long or the table names are difficult.
Incorrect answers:
A The number of tables in a schema is not a consideration when creating a synonym.

Front 24

The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2 (25)
SALARY NUMBER (6,2)
COMMISSION_PCT NUMBER (6)
You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct.
2. Exclude employees with a zero commission_pct.
3. Display a zero for employees with a null commission value.
Evaluate the SQL statement:
SELECT LAST_NAME, SALARY*COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

What does the statement provide?
A. one of the desired results
B. an error statement
C. two of the desired results
D. all of the desired results

Back 24

Answer: A
This statement will provide only one of the desired results: display the salary multiplied by the
commission_pct. It will not exclude employees with a zero commission_pct and display a zero for
employees with a null commission value.

Front 25

In which case would you use a FULL OUTER JOIN?
A. Both tables have NULL values.
B. You want all unmatched data from one table.
C. You want all matched and unmatched data from only one table.
D. You want all unmatched data from both tables.
E. You want all matched data from both tables.
F. One of the tables has more data than the other.

Back 25

Answer: D
Oracle9i also makes it possible for you to easily execute a full outer join, including all records from
the tables that would have been displayed if you had used both LEFT OUTER JOIN or RIGTH
OUTER JOIN clauses.
Incorrect Answers:
A: You will not use a FULL OUTER JOIN if both tables have NULL values.
B: You will not use a FULL OUTER JOIN if you want all unmatched data from one table. Only
LEFT OUTER JOIN or RIGTH OUTER JOIN needs to be used.
C: You want all unmatched, not matched, data from both tables.
E: It is not a criterion to use a FULL OUTER JOIN if one of the tables has more data than the
other.
F: You want all unmatched, not matched data or a combination of matched and unmatched data,
from both tables, not one table.OCP Introduction to Oracle 9i: SQL Exam Guide, Jason
Couchman, p. 109-118Chapter 3: Advanced Data Selection in Oracle

Front 26

Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders
(SER_NO NUMBER UNIQUE,
ORDER_ID NUMBER,
ORDER_DATE DATE NOT NULL,
STATUS VARCHAR2(10)
CHECK (status IN ('CREDIT', 'CASH')),
PROD_ID NUMBER
REFERENCES PRODUCTS(PRODUCT_ID),
ORD_TOTAL NUMBER,
PRIMARY KEY (order_id, order_date));
For which columns would an index be automatically created when you execute the above SQL
statement? (Choose two.)
A. ORDER_ID
B. SER_NO
C. PROD_ID
D. ORD_TOTAL
E. composite index on ORDER_ID and ORDER_DATE
F. STATUS

Back 26

Answer: B,E
Explanation:
Index exist for UNIQUE and PRIMARY KEY constraints
Incorrect answer :
* ORDER_ID is neither UNIQUE nor PRIMARY KEY
* STATUS is neither UNIQUE nor PRIMARY KEY
* PROD_ID is neither UNIQUE nor PRIMARY KEY
* ORD_TOTAL is neither UNIQUE nor PRIMARY KEY

Front 27

The EMP table contains these columns:
EMPLOYEE_ID NUMBER(4)
EMPNAME VARCHAR2 (25)
SALARY NUMBER(9,2)
HIRE_DATE DATE
You query the database with this SQL statement:
SELECT empname,hire_date HIREDATE, salary
FROM EMP
ORDER BY hire_date;
How will the results be sorted?
A. descending alphabetically
B. descending by date
C. randomly
D. ascending by date
E. ascending alphabetically

Back 27

Answer: D

Front 28

Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP
table?
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)
FOREIGN KEY (deptno));
B. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk FOREIGN KEY deptno
REFERENCES dept deptno);
A. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
C. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
D. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

Back 28

Answer: D
This statement provides correct syntax to define the FOREIGN KEY constraint on the DEPTNO
column of the EMP table.

Front 29

Which two statements are true about WHERE and HAVING clauses? (Choose two.)
A. A WHERE clause CANNOT be used in a query if the query uses a HAVING clause.
B. A HAVING clause CANNOT be used in subqueries.
C. A WHERE clause can be used to restrict both rows and groups.
D. A HAVING clause can be used to restrict both rows and groups.
E. A WHERE clause can be used to restrict rows only.
F. A HAVING clause can be used to restrict groups only.

Back 29

Answer: E,F
Explanation: :
WHERE clause cannot be use when there is group functions.
groups on
B: WHERE clause cannot be use to restrict groups
D: A HAVING clause can only e used to restrict GROUPS.
Note: HAVING clause to specify which groups are to be displayed and thus further restrict the
the basis of aggregate information. The Oracle server performs the following steps when you use
the Having clause
1. rows are grouped
2. the group function is applied to the group
3. the group that match the criteria in the Having clause are displayed.

Front 30

Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHAR2(10)
START_DATE DATE
END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose two.)
A. MIN(start_date)
B. SUM(start_date)
C. COUNT(start_date)
D. MAXIMUM(start_date)
E. AVG(start_date)
F. AVG(start_date, end_date)

Back 30

Answer: A,C
It is possible to apply COUNT() and MIN() functions on the column with DATE data type.

Front 31

What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view in another schema even if you don't have privileges
C. creates a view even if the underlying parent table has constraints
D. creates a view regardless of whether or not the base tables exist

Back 31

Answer: D
Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect answer :
A the option is not valid
B the option is not valid
C the option is not valid

Front 32

The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
A promotional sale is being advertised to the customers in France. Which WHERE clause
identifies customers that are located in France?
A. WHERE lower(country_address) = 'france'
B. WHERE lower(country_address) = "france"
C. WHERE lower(country_address) LIKE %france%
D. WHERE lower(country_address) = '%france%'
E. WHERE lower(country_address) IS 'france'

Back 32

Answer: A
WHERE lower(country_address)='france'
Incorrect answer :
* invalid use of symbol ""
* invalid use of IS keyword
* invalid use of % in condition
* invalid use of condition

Front 33

Which view should a user query to display the columns associated with the constraints on a table
owned by the user?
A. USER_COLUMNS
B. USER_CONS_COLUMNS
C. ALL_CONSTRAINTS
D. USER_OBJECTS
E. USER_CONSTRAINTS

Back 33

Answer: B
Explanation:
view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

Front 34

You need to create a view EMP_VU. The view should allow the users to manipulate the records of
only the employees that are working for departments 10 or 20. Which SQL statement would you
use to create the view EMP_VU?
A. CREATE VIEW emp_vu AS

SELECT *
FROM employees
WHERE department_id IN (10,20)
WITH READ ONLY;
B. CREATE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20);
C. CREATE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20)
WITH CHECK OPTION;
D. CREATE FORCE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20);
E. CREATE FORCE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20)
NO UPDATE;

Back 34

Answer: C
Tables that underlie views often have constraints that limit the data that can be added to those
tables. Views cannot add data to the underlying table that would violate the table's constraints.
However, you can also define a view to restrict the user's ability to change underlying table data
even further, effectively placing a special constraint for data manipulation through the view. This
additional constraint says that INSERT or UPDATE statements issued against the view are cannot
create rows that the view cannot subsequently select. This constraint is configured when the view
is defined by adding the WITH CHECK OPTION to the CREATE VIEW statement.
Incorrect Answers:
A: This view does not provide correct semantic of question task.
B: WITH READ ONLY clause is incorrect syntax in the CREATE VIEW command.
D: This view does not provide correct semantic of question task.
E: NO UPDATE clause is incorrect syntax in the CREATE VIEW command.OCP Introduction to
Oracle 9i: SQL Exam Guide, Jason Couchman, p. 301-303Chapter 7: Creating Other Database
Objects in Oracle

Front 35

Which two statements complete a transaction? (Choose two.)
A. ROLLBACK TO SAVEPOINT C;
B. SELECT MAX(sal)
FROM employees
WHERE department_id = 20;
C. GRANT SELECT ON employees TO SCOTT;
D. DESCRIBE employees;
E. ALTER TABLE employees
SET UNUSED COLUMN sal;
F. DELETE employees;

Back 35

Answer: C,E
Explanation:
C: GRANT is a DML operation which will cause an implicit commit
E: It is important to understand that an implicit COMMIT occurs on the database when a user exits
SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE
statement, used to create a database object, or an ALTER TABLE statement, used to alter a
database object.

Front 36

What is true regarding subqueries?
A. The inner query must always return a value or the outer query will give an error.
B. The inner query returns a value to the outer query.
C. The inner query always sorts the results of the outer query.
D. The outer query must return a value to the inner query.
E. The outer query always sorts the results of the inner query.

Back 36

Answer: B
The inner query returns a value to the outer query. If the inner query does not return a value, the
outer query does not return a result

Front 37

Which four are attributes of single row functions? (Choose four.)
A. accept only one argument and return only one value
B. cannot be nested
C. manipulate data items
D. act on each row returned
E. return one result per row
F. accept arguments which can be a column or an expression

Back 37

Answer: C,D,E,F
Explanation:
manipulate data items, act on each row returned, return one result per row, and accept arguments
that can be a column or expression.
Incorrect answer :
A is not single row attributes
* functions can accept more than one argument, e.g NVL2

Front 38

Evaluate the set of SQL statements:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
ROLLBACK;
DESCRIBE DEPT
What is true about the set?
What is true about the set?
A. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
B. The DESCRIBE DEPT statement displays the structure of the DEPT table.
C. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a
COMMIT statement introduced before the ROLLBACK statement.
D. The ROLLBACK statement frees the storage space occupied by the DEPT table.

Back 38

Answer: B
Explanation:
The structure of the DEPT table will be displayed because the CREATE TABLE statement is DDL
operation and it cannot be rolled back because implicit commit occurs on the database when a
user exits SQL*Plus or issues a data-definition language (DDL) command such as a create table
statement, user to create a database object, or an alter table statement, used to alter a database
object.

Front 39

Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
You issue these statements:
CREATE table new_emp ( employee_id NUMBER, name VARCHAR2(30));
INSERT INTO new_emp SELECT employee_id , last_name from employees;
Savepoint s1;
UPDATE new_emp set name = UPPER(name);
Savepoint s2;
Delete from new_emp;
Rollback to s2;
Delete from new_emp where employee_id =180;
UPDATE new_emp set name = 'James';
Rollback to s2;
UPDATE new_emp set name = 'James' WHERE employee_id =180;
Rollback;
At the end of this transaction, what is true?
B. You have no rows in the table.
A. Your last update fails to update any rows because employee ID 180 was already deleted.
C. You cannot roll back to the same savepoint more than once.
D. You have an employee with the name of James.

Back 39

Answer: B
At the end of this transaction you will not have rows in the table.

Front 40

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(TRIM ('H' FROM 'HelloWorld')) FROM dual;
D. SELECT LOWER(SUBSTR('HelloWorld', 1, 1) FROM dual;
E. SELECT LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual;

Back 40

Answer: C
Explanation:
This statement will return correct result because function TRIM() will trim letter 'H' in the 'Hello
World' and function LOWER() will return data in string in lowercase..

Front 41

Which SQL statement accepts user input for the columns to be displayed, the table name, and the
WHERE condition?
A. SELECT &1, &2
FROM &3
WHERE last_name = '&4';
B. SELECT &1, '&2'
FROM EMP
WHERE last_name = '&4';
C. SELECT &1, "&2"
FROM &3
WHERE last_name = '&4';
D. SELECT &1, '&2'
FROM &3
WHERE '&last_name = '&4'';

Back 41

Answer: A
In a WHERE clause, date and characters values must be enclosed within single quotation marks.
Sample of the correct syntax
SELECT EMPLOYEE_ID, &COLUMN_NAME
FROM EMPLOYEES

Front 42

EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SAL NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
the INSERT statement should insert a row with the specified values into the EMPLOYEES table.
Examine the structure of the EMPLOYEES table:
You want to create a SQL script file that contains an INSERT statement. When the script is run,
The INSERT statement should pass values to the table columns as specified below:
EMPLOYEE_ID: Next value from the sequence EMP_ID_SEQ
EMP_NAME and JOB_ID: As specified by the user during run time, through
substitution variables
SAL: 2000
MGR_ID: No value
DEPARTMENT_ID: Supplied by the user during run time through
substitution variable. The INSERT statement should
fail if the user supplies a value other than 20 or 50.
Which INSERT statement meets the above requirements?

A. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid',
2000, NULL, &did IN (20,50));
B. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
C. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50)
WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
D. INSERT INTO (SELECT *
FROM employees
WHERE (department_id = 20 AND
department_id = 50)
WITH CHECK OPTION )
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

Back 42

Answer: C

Front 43

Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate
the DEPT table.
B. You can roll back the deletion of rows after the statement executes.
C. You can NOT roll back the deletion of rows after the statement executes.
D. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will
display an error.
E. It does not release the storage space used by the table.
F. It releases the storage space used by the table.

Back 43

Answer: A,C,F
Explanation:
A: The TRUNCATE TABLE Statement releases storage space used by the table,
D: Can not rollback the deletion of rows after the statement executes,
F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate
the DEPT table.

Front 44

The EMPLOYEES table has these columns:
LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2)
HIRE_DATE DATE
Management wants to add a default value to the SALARY column. You plan to alter the table by
using this SQL statement:
ALTER TABLE EMPLOYEES
MODIFY (SALARY DEFAULT 5000);
Which is true about your ALTER statement?
A. A change to the DEFAULT value affects only subsequent insertions to the table.
B. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
C. Column definitions cannot be altered to add DEFAULT values.
D. Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.

Back 44

Answer: A
Explanation:
A change to the DEFAULT value affects only subsequent insertions to the table. Existing rows will
not be affected.

Front 45

Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID NUMBER
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column
of the DEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the
EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
C. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
D. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
A. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
B. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
E. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
F. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

Back 45

Answer: E,F
Explanation:
the EMP_ID_SEQ sequence is not affected by modification to the EMPLOYEES table. Any other
column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
Incorrect answer :
* EMP_ID_SEQ sequence can be use to populate JOB_ID
* EMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
* EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES table.
* EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEE_ID
column.

Front 46

Examine the description of the CUSTOMERS table:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
The CUSTOMER_ID column is the primary key for the table.
Which statement returns the city address and the number of customers in the cities Los Angeles
or San Francisco?
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address, customer_id;
B. SELECT city_address, COUNT(customer_id)
FROM customers
GROUP BY city_address IN ('Los Angeles', 'San Francisco');
C. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address;

A. SELECT city_address, COUNT(customer_id)
D. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco');

Back 46

Answer: C
Explanation:
Not C: The customer ID in the GROUP BY clause is wrong