الرئيسية
من طرف Oracle 1Z0-007
Introduction to Oracle9i: SQL
156 Q&A
Looking for Real Exam Questions for IT Certification Exams!
We guarantee you can pass any IT certification exam at your first attempt with just 10-12
hours study of our guides.
Our study guides contain actual exam questions, you will get word to word same on your
actual test; accurate answers with detailed explanation verified by experts and all graphics
and drag-n-drop exhibits shown just as on the real test.
To test the quality of our guides, you can download the one-fourth portion of any guide from
http://www.certificationking.com absolutely free.
Besides, we also offer complete version of following exams absolutely free. You can start
your certification from these free guides and if you are satisfied you can buy the rest
Microsoft: 70-270, 70-305, 70-526, MB2-421
Cisco 642-901, 642-871, 642-611, 642-502, 642-425, 646-561
Oracle: 1Z0-001, 1Z0-007, 1Z0-040, 1Z0-200 Novell: 50-676, 50-565
Adobe: 9A0-019 Apple: 9L0-004, 9L0-505, 9L0-606 3COM: 3M0-211
Citrix: 1Y0-223, 1Y0-256 CIW: 1D0-420 EMC: E20-001 Enterasys: 2B0-011
Exam Express: EE0-011 Foundry: FN0-100 Hyperion: 4H0-002
HP: HP0-045, HP0-052, HP0-055, HP0-090, HP0-216, HP0-417
Juniper: JN0-120, JN0-330 Lotus: 190-510 Network Appliances: NS0-121
Network General: 1T6-520 Nortel: 920-430, 920-803 OMG: UM0-100
Sybase: 510-015 SAIR: 3X0-101, 201 SAS: A00-201 Tibco: TB0-103 …..
For pricing and placing order, please visit http://certificationking.com/order.html
We accept all major credit cards through www.paypal.com
For other payment options and any further query, feel free to mail us at
info@certificationking.com
Q: 1 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
VALUES ( NULL, 'John', 'Smith');
B. INSERT INTO employees( first_name, last_name)
VALUES( 'John', 'Smith');
C. INSERT INTO employees
VALUES ( '1000', 'John', NULL);
D. INSERT INTO employees (first_name, last_name, employee_id)
VALUES ( 1000, 'John', 'Smith');
E. INSERT INTO employees (employee_id)
VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, 'John', ' ');
Answer: C, E, F
Q: 2 Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?
A. 46 and 45
B. 46 and 45.93
C. 50 and 45.93
D. 50 and 45.9
E. 45 and 45.93
F. 45.95 and 45.93
Answer: C
www.CertificationKing.com
- 3 -
Q: 3 Which are DML statements? (Choose all that apply.)
A. COMMIT
B. MERGE
C. UPDATE
D. DELETE
E. CREATE
F. DROP...
Answer: B, C, D
Q: 4 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?
A. The DESCRIBE DEPT statement displays the structure of the DEPT table.
B. The ROLLBACK statement frees the storage space occupied by the DEPT table.
C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT
statement introduced before the ROLLBACK statement.
Answer: A
Q: 5 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. No change is required to achieve the desired results.
www.CertificationKing.com
- 4 -
B. SELECT ename, sal, 12*(sal+100)
FROM emp;
C. SELECT ename, sal, (12*sal)+100
FROM emp;
D. SELECT ename, sal+100,*12
FROM emp;
Answer: B
Q: 6 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. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE
Answer: A, F
Q: 7 Examine the structure of the EMP_DEPT_VU view:
Column Name Type Remarks
EMPLOYEE_ID NUMBER From the EMPLOYEES table
EMP_NAME VARCHAR2(30) From the EMPLOYEES table
JOB_ID VARCHAR2(20) From the EMPLOYEES table
SALARY NUMBER From the EMPLOYEES table
DEPARTMENT_ID NUMBER From the DEPARTMENTS table
DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table
www.CertificationKing.com
- 5 -
Which SQL statement produces an error?
A. SELECT *
FROM emp_dept_vu;
B. SELECT department_id, SUM(salary)
FROM emp_dept_vu
GROUP BY department_id;
C. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vu
GROUP BY department_id, job_id;
D. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E. None of the statements produce an error; all are valid.
Answer: E
Q: 8 Click the Exhibit button and examine the data from the ORDERS and
CUSTOMERS tables.
Evaluate this SQL statement:
SELECT cust_id, ord_total
FROM orders
WHERE ord_total > ANY(SELECT ord_total
FROM orders
WHERE cust_id IN (SELECT cust_id
FROM customers
WHERE city LIKE
'New York'));
What is the result when the above query is executed?
www.CertificationKing.com
- 6 -
www.CertificationKing.com
- 7 -
www.CertificationKing.com
- 8 -
A. A
Answer: A
Q: 9 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. difference, projection, join
C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product
Answer: A
Q: 10 Click the Exhibit button and examine the data from the EMP table.
The COMMISSION column shows the monthly commission earned by the employee.
Which three tasks would require subqueries or joins in order to be performed in a single step? (Choose
three.)
www.CertificationKing.com
- 9 -
A. deleting the records of employees who do not earn commission
B. increasing the commission of employee 3 by the average commission earned in department 20
C. finding the number of employees who do NOT earn commission and are working for department 20
D. inserting into the table a new employee 10 who works for department 20 and earns a commission that is
equal to the commission earned by employee 3
E. creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and
COMMISSION of the EMP table
F. decreasing the commission by 150 for the employees who are working in department 30 and earning a
commission of more than 800
Answer: B, D, E
Q: 11 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?
A. ALTER TABLE students
ADD PRIMARY KEY student_id;
B. ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
www.CertificationKing.com
- 10 -
Q: 12 Which three are DATETIME data types that can be used when specifying
column definitions? (Choose three.)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: A, C, D
Q: 13 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. all of the desired results
B. two of the desired results
C. one of the desired results
D. an error statement
Answer: C
Q: 14 Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
www.CertificationKing.com
- 11 -
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an
error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT
table.
Answer: A, D, F
Q: 15 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. randomly
B. ascending by date
C. descending by date
D. ascending alphabetically
E. descending alphabetically
Answer: B
Q: 16 Mary has a view called EMP_DEPT_LOC_VU that was created based on
the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott
on this view. Which option enables Scott to eliminate the need to qualify the view with the name
MARY.EMP_DEPT_LOC_VU each time the view is referenced?
www.CertificationKing.com
- 12 -
A. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE PRIVATE SYNONYM EDL_VU
FOR mary.EMP_DEPT_LOC_VU;
then he can prefix the columns with this synonym.
B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE SYNONYM EDL_VU
FOR mary.EMP_DEPT_LOC_VU;
then he can prefix the columns with this synonym.
C. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE LOCAL SYNONYM EDL_VU
FOR mary.EMP_DEPT_LOC_VU;
then he can prefix the columns with this synonym.
D. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE SYNONYM EDL_VU
ON mary(EMP_DEPT_LOC_VU);
then he can prefix the columns with this synonym.
E. Scott cannot create a synonym because synonyms can be created only for tables.
F. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and
grant SELECT privilege on that synonym to Scott.
Answer: B
Q: 17 A subquery can be used to ___.
A. create groups of data
B. sort data in a specific order
C. convert data to a different format
D. retrieve data based on an unknown condition
Answer: D
Q: 18 Click the Exhibit button to examine the data of the EMPLOYEES table.
Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's
manager, for all the employees who have a manager and earn more than 4000?
www.CertificationKing.com
- 13 -
A. SELECT employee_id "Emp_id", emp_name "Employee",
salary,
employee_id "Mgr_id", emp_name "Manager"
FROM employees
WHERE salary > 4000;
B. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e JOIN employees m
WHERE e.mgr_id = m.mgr_id
AND e.salary > 4000;
C. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e JOIN employees m
ON (e.mgr_id = m.employee_id)
AND e.salary > 4000;
D. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id", m.emp_name "Manager"
FROM employees e SELF JOIN employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;
E. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id" m.emp_name "Manager"
FROM employees e JOIN employees m
www.CertificationKing.com
- 14 -
USING (e.employee_id = m.employee_id)
AND e.salary > 4000;
Answer: C
Q: 19 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. Column definitions cannot be altered to add DEFAULT values.
B. A change to the DEFAULT value affects only subsequent insertions to the table.
C. Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.
D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
Answer: B
Q: 20 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?
www.CertificationKing.com
- 15 -
A. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco');
B. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address, customer_id;
D. SELECT city_address, COUNT(customer_id)
FROM customers
GROUP BY city_address IN ('Los Angeles', 'San Francisco');
Answer: B
Q: 21 Click the Exhibit button to examine the structures of the EMPLOYEES,
DEPARTMENTS, and TAX tables.
For which situation would you use a nonequijoin query?
www.CertificationKing.com
- 16 -
A. to find the tax percentage for each of the employees
B. to list the name, job_id, and manager name for all the employees
C. to find the name, salary, and the department name of employees who are not working with Smith
D. to find the number of employees working for the Administrative department and earning less than 4000
E. to display name, salary, manager ID, and department name of all the employees, even if the employees do
not have a department ID assigned
Answer: A
Q: 22 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
www.CertificationKing.com
- 17 -
FROM EMP
WHERE DEPARTMENT_ID = NULL;
What is true about this SQL statement ?
A. The SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired results.
Answer: C
Q: 23 Which two statements about sequences are true? (Choose two.)
A. 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.
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 CURRVAL pseudo column to generate a value from a sequence that would be used for a
specified database column.
E. 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.
F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum
value defined for the sequence.
Answer: B, C
Q: 24 What is true of using group functions on columns that contain NULL
values?
A. Group functions on columns ignore NULL values.
B. Group functions on columns returning dates include NULL values.
C. Group functions on columns returning numbers include NULL values.
D. Group functions on columns cannot be accurately used on columns that contain NULL values.
E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
Answer: A
www.CertificationKing.com
- 18 -
Q: 25 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 "Annual Salary"
FROM employees;
C. SELECT ename, salary*12 AS Annual Salary
FROM employees;
D. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY")
FROM employees
Answer: B
Q: 26 Click the Exhibit button and examine the data in the EMPLOYEES and
DEPARTMENTS tables.
You want to retrieve all employees, whether or not they have matching departments in the departments
table. Which query would you use?
A. SELECT last_name, department_name
www.CertificationKing.com
- 19 -
FROM employees NATURAL JOIN departments;
B. SELECT last_name, department_name
FROM employees JOIN departments ;
C. SELECT last_name, department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
D. SELECT last_name, department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
E. SELECT last_name, department_name
FROM employees FULL JOIN departments
ON (e.department_id = d.department_id);
F. SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);
Answer: F
Q: 27 Which SQL statement defines a FOREIGN KEY constraint on the
DEPTNO column of the EMP table?
A. 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);
B. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)
FOREIGN KEY (deptno));
D. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
www.CertificationKing.com
- 20 -
Answer: B
Q: 28 Click the Exhibit button and examine the data in the EMPLOYEES table.
Which three subqueries work? (Choose three.)
A. SELECT *
FROM employees
where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
C. SELECT distinct department_id
FROM employees
WHERE salary > ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
D. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
E. SELECT last_name
FROM employees
WHERE salary > ANY (SELECT MAX(salary)
FROM employees
GROUP BY department_id);
F. SELECT department_id
www.CertificationKing.com
- 21 -
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY AVG(SALARY));
Answer: C, D, E
Q: 29 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 &3
WHERE '&last_name = '&4'';
C. SELECT &1, &2
FROM &3
WHERE last_name = '&4';
D. SELECT &1, '&2'
FROM EMP
WHERE last_name = '&4';
Answer: C
Q: 30 Evaluate these two SQL statements:
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY salary DESC;
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY 2 DESC;
What is true about them?
A. The two statements produce identical results.
B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order by default.
D. The two statements can be made to produce identical results by adding a column alias for the salary column
in the second SQL statement.
www.CertificationKing.com
- 22 -
Answer: A
Q: 31 In which scenario would an index be most useful?
A. The indexed column is declared as NOT NULL.
B. The indexed columns are used in the FROM clause.
C. The indexed columns are part of an expression.
D. The indexed column contains a wide range of values.
Answer: D
Q: 32 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(SYSDATE,'yyyy')
FROM dual;
B. SELECT TO_DATE(SYSDATE,'yyyy')
FROM dual;
C. SELECT DECODE(SUBSTR(SYSDATE,
, 'YYYY')
FROM dual;
D. SELECT DECODE(SUBSTR(SYSDATE,, 'year')
FROM dual;
E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;
Answer: A
Q: 33 Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name
Smith?
A. SELECT COUNT(*)
FROM employees
www.CertificationKing.com
- 23 -
WHERE last_name='Smith';
B. SELECT COUNT(dept_id)
FROM employees
WHERE last_name='Smith';
C. SELECT DISTINCT(COUNT(dept_id))
FROM employees
WHERE last_name='Smith';
D. SELECT COUNT(DISTINCT dept_id)
FROM employees
WHERE last_name='Smith';
E. SELECT UNIQUE(dept_id)
FROM employees
WHERE last_name='Smith';
Answer: D
Q: 34 Examine the description of the MARKS table:
STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)
SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)
SUBJ3 NUMBER(3)
SUBJ1, SUBJ2, and SUBJ3 indicate the marks (grades) obtained by a student in the three subjects.
Which two statements are valid? (Choose two.)
A. SELECT SUM(subj1, subj2, subj3)
FROM marks;
B. SELECT SUM(subj1 + subj2 + subj3)
FROM marks;
C. SELECT SUM(subj1), SUM(subj2), SUM(subj3)
FROM marks;
D. SELECT MAX(subj1, subj2, subj3)
FROM marks;
E. SELECT MINIMUM(subj1)
FROM marks;
F. SELECT COUNT(std_id)
FROM marks
WHERE subj1 >= AVG(subj1);
Answer: B, C
www.CertificationKing.com
- 24 -
Q: 35 Which are iSQL*Plus commands? (Choose all that apply.)
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Q: 36 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;
Answer: B, C, D
Q: 37 What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
www.CertificationKing.com
- 25 -
Q: 38 Click the Exhibit button to examine the structure of the EMPLOYEES,
DEPARTMENTS, and LOCATIONS tables.
Two new departments are added to your company as shown:
DEPARTMENT_ID DEPARTMENT_NAME MGR_ID LOCATION_ID
9998 Engineering 123
9999 Administrative Boston
You need to list the names of employees, the department IDs, the department names, and the cities where
the departments are, even if there are no employees in the departments and even if the departments are
not yet assigned to a location. You need to join the EMPLOYEES, DEPARTMENTS, and LOCATIONS
tables to retrieve this information.
Which statement do you execute to retrieve this information?
A. SELECT e.last_name, d.department_id,
d.department_name, l.city
www.CertificationKing.com
- 26 -
FROM departments d
RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id;
B. SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
FULL OUTER JOIN employees e
ON d.department_id = e.department_id
FULL OUTER JOIN locations l
ON d.location_id = l.location_id;
C. SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
LEFT OUTER JOIN locations l
ON d.location_id = l.location_id;
D. SELECT last_name, department_id,
department_name, city
FROM departments d
NATURAL JOIN employees e
NATURAL JOIN locations l;
Answer: C
Q: 39 What is true about joining tables through an equijoin?
A. You can join a maximum of two tables through an equijoin.
B. You can join a maximum of two columns through an equijoin.
C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign
key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum
of n-1 join conditions.
Answer: E
Q: 40 Which two statements are true about constraints? (Choose two.)
A. The UNIQUE constraint does not permit a null value for the column.
www.CertificationKing.com
- 27 -
B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D. The NOT NULL constraint ensures that null values are not permitted for the column.
Answer: B, D
Q: 41 Which two statements complete a transaction? (Choose two.)
A. DELETE employees;
B. DESCRIBE employees;
C. ROLLBACK TO SAVEPOINT C;
D. GRANT SELECT ON employees TO SCOTT;
E. ALTER TABLE employees
SET UNUSED COLUMN sal;
F. SELECT MAX(sal)
FROM employees
WHERE department_id = 20;
Answer: D, E
Q: 42 Which SQL statement returns a numeric value?
A. SELECT ADD_MONTHS(MAX(hire_Date), 6)
FROM EMP;
B. SELECT ROUND(hire_date)
FROM EMP;
C. SELECT sysdate-hire_date
FROM EMP;
D. SELECT TO_NUMBER(hire_date + 7)
FROM EMP;
Answer: C
Q: 43 Examine the structure of the EMPLOYEES and NEW_EMPLOYEES
tables:
EMPLOYEES
__________
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
www.CertificationKing.com
- 28 -
HIRE_DATE DATE
NEW_EMPLOYEES
_______________
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which DELETE statement is valid?
A. DELETE FROM employees
WHERE employee_id = (SELECT employee_id
FROM employees);
B. DELETE * FROM employees
WHERE employee_id = (SELECT employee_id
FROM new_employees);
C. DELETE FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WHERE name ='Carrey');
D. DELETE * FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WHERE last_name ='Carrey');
Answer: C
Q: 44 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(,
order_status NUMBER(10) DEFAULT 'PENDING',
date_ordered DATE );
www.CertificationKing.com
- 29 -
B. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
C. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
D. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
E. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
F. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered VARCHAR2 );
Answer: E
Q: 45 In which two cases would you use an outer join? (Choose two.)
A. The tables being joined have NOT NULL columns.
B. The tables being joined have only matched data.
C. The columns being joined have NULL values.
D. The tables being joined have only unmatched data.
E. The tables being joined have both matched and unmatched data.
F. Only when the tables have a primary key-foreign key relationship.
Answer: C, E
Q: 46 Which two statements are true about WHERE and HAVING clauses?
(Choose two.)
www.CertificationKing.com
- 30 -
A. A WHERE clause can be used to restrict both rows and groups.
B. A WHERE clause can be used to restrict rows only.
C. A HAVING clause can be used to restrict both rows and groups.
D. A HAVING clause can be used to restrict groups only.
E. A WHERE clause CANNOT be used in a query if the query uses a HAVING clause.
F. A HAVING clause CANNOT be used in subqueries.
Answer: B, D
Q: 47 Examine the structure of the EMPLOYEES table:
Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column
of the DEPARTMENTS table
You need to create a view called EMP_VU that allows the users to insert rows through the view.
Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?
A. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name,
department_id
FROM employees
WHERE mgr_id IN (102, 120);
B. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
department_id
FROM employees
WHERE mgr_id IN (102, 120);
C. CREATE VIEW emp_vu AS
SELECT department_id, SUM(sal) TOTALSAL
FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_id;
D. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM employees;
www.CertificationKing.com
- 31 -
Answer: B
Q: 48 Evaluate this SQL statement:
e.employee_id, (.15* e.salary) + (.5 * e.commission_pct)
+ (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE
FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
What will happen if you remove all the parentheses from the calculation?
A. The value displayed in the CALC_VALUE column will be lower.
B. The value displayed in the CALC_VALUE column will be higher.
C. There will be no difference in the value displayed in the CALC_VALUE column.
D. An error will be reported.
Answer: C
Q: 49 Which four are attributes of single row functions? (Choose four.)
A. cannot be nested
B. manipulate data items
C. act on each row returned
D. return one result per row
E. accept only one argument and return only one value
F. accept arguments which can be a column or an expression
Answer: B, C, D, F
Q: 50 Which view should a user query to display the columns associated with the
constraints on a table owned by the user?
A. USER_CONSTRAINTS
B. USER_OBJECTS
C. ALL_CONSTRAINTS
D. USER_CONS_COLUMNS
E. USER_COLUMNS
Answer: D
www.CertificationKing.com
- 32 -
Q: 51 Examine the structure of the EMPLOYEES and NEW_EMPLOYEES
tables:
EMPLOYEES
___________
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
________________
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which UPDATE statement is valid?
A. UPDATE new_employees SET name = (SELECT last_name||
first_name
FROM employees
WHERE employee_id
=180)
WHERE employee_id =180;
B. UPDATE new_employees SET name = (SELECT
last_name||first_name
FROM employees )
WHERE employee_id =180;
C. UPDATE new_employees SET name = (SELECT last_name ||
first_name
FROM employees
WHERE employee_id
=180)
WHERE employee_id =(SELECT employee_id
FROM new_employees);
D. UPDATE new_employees SET name = (SELECT last_name||
first_name
FROM employees
WHERE employee_id =
(SELECT employee_id
FROM new_employees))
WHERE employee_id
=180;
www.CertificationKing.com
- 33 -
Answer: A
Q: 52 Which two statements about subqueries are true? (Choose two.)
A. A subquery should retrieve only one row.
B. A subquery can retrieve zero or more rows.
C. A subquery can be used only in SQL query statements.
D. Subqueries CANNOT be nested by more than two levels.
E. A subquery CANNOT be used in an SQL query statement that uses group functions.
F. When a subquery is used with an inequality comparison operator in the outer SQL statement, the column
list in the SELECT clause of the subquery should contain only one column.
Answer: B, F
Q: 53 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?
A. ALTER TABLE students
ADD PRIMARY KEY student_id;
B. ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
Q: 54 You need to change the definition of an existing table. The
COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up
to 1000 bytes. The column can currently hold 500 bytes per value. The table contains 20000 rows. Which
statement is valid?
A. ALTER TABLE commercials
MODIFY (description CHAR2(1000));
B. ALTER TABLE commercials
CHANGE (description CHAR2(1000));
C. ALTER TABLE commercials
CHANGE (description VARCHAR2(1000));
www.CertificationKing.com
- 34 -
D. ALTER TABLE commercials
MODIFY (description VARCHAR2(1000));
E. You cannot increase the size of a column if the table has rows.
Answer: D
Q: 55 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 data from both tables.
D. You want all unmatched data from both tables.
E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table.
Answer: D
Q: 56 Which are DML statements? (Choose all that apply.)
A. COMMIT
B. MERGE
C. UPDATE
D. DELETE
E. CREATE
F. DROP...
Answer: B, C, D
Q: 57 Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL LAST_
NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR2(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid in that
department, only if the minimum salary is less than 5000 and maximum salary is more than 15000?
www.CertificationKing.com
- 35 -
A. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;
B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Answer: D
Q: 58 Which four statements correctly describe functions that are available in
SQL? (Choose four.)
A. INSTR returns the numeric position of a named character.
B. NVL2 returns the first non-null expression in the expression list.
C. TRUNCATE rounds the column, expression, or value to n decimal places.
D. DECODE translates an expression after comparing it to each search value.
E. TRIM trims the heading or trailing characters (or both) from a character string.
F. NVL compares two expressions and returns null if they are equal, or the first expression if they are not
equal.
G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not
equal.
Answer: A, D, E, G
Q: 59 Which iSQL*Plus feature can be used to replace values in the WHERE
clause?
A. substitution variables
B. replacement variables
C. prompt variables
www.CertificationKing.com
- 36 -
D. instead-of variables
E. This feature cannot be implemented through iSQL*Plus.
Answer: A
Q: 60 Which is an iSQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Q: 61 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. randomly
B. ascending by date
C. descending by date
D. ascending alphabetically
E. descending alphabetically
Answer: B
Q: 62 Click the Exhibit button and examine the data from the ORDERS and
CUSTOMERS tables.
www.CertificationKing.com
- 37 -
Evaluate the SQL statement:
SELECT *
FROM orders
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_name = 'Smith');
What is the result when the query is executed?
www.CertificationKing.com
- 38 -
A. D
Answer: A
Q: 63 What is true about updates through a view?
A. You cannot update a view with group functions.
B. When you update a view group functions are automatically computed.
C. When you update a view only the constraints on the underlying table will be in effect.
www.CertificationKing.com
- 39 -
D. When you update a view the constraints on the views always override the constraints on the underlying
tables.
Answer: A
Q: 64 What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
Q: 65 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 LIKE '%SA\_%' ESCAPE '\';
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 = '%SA_';
Answer: A
Q: 66 Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
www.CertificationKing.com
- 40 -
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT 'SA_REP'
SAL NUMBER
COMM_PCT NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement you specify should
update the rows with the values specified below:
JOB_ID: Default value specified for this column definition.
SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission percentage
column, if any.
If no default value is specified for the column,
the value should be NULL.
DEPARTMENT_ID: Supplied by the user during run time through
substitution variable.
Which UPDATE statement meets the requirements?
A. UPDATE employees
SET job_id = DEFAULT
AND Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP')
AND comm_pct = DEFAULT
AND department_id = &did
WHERE employee_id IN (103,115);
B. UPDATE employees
SET job_id = DEFAULT
AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL
AND department_id = &did
WHERE employee_id IN (103,115)
AND job_id = 'SA_REP';
C. UPDATE employeesC.UPDATE employees
SET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115);
D. UPDATE employeesD.UPDATE employees
www.CertificationKing.com
- 41 -
SET job_id = DEFAULT,
Sal = MAX(sal),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115)
AND job_id = 'SA_REP';
E. UPDATE employees
SET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT OR NULL,
department_id = &did
WHERE employee_id IN (103,115);
Answer: C
Q: 67 Click the Exhibit button to examine the structures of the EMPLOYEES and
TAX tables.
You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
A. SELECT employee_id, salary, tax_percent
FROM employees e JOIN tax t
www.CertificationKing.com
- 42 -
ON e.salary BETWEEN t.min_salary AND t.max_salary;
B. SELECT employee_id, salary, tax_percent
FROM employees e JOIN tax t
WHERE e.salary > t.min_salary AND < t.max_salary;
C. SELECT employee_id, salary, tax_percent
FROM employees e JOIN tax t
ON (MIN(e.salary) = t.min_salary
AND MAX(e.salary) = t.max_salary);
D. You cannot find the information because there is no common column between the two tables.
Answer: A
Q: 68 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?
A. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco');
B. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address, customer_id;
D. SELECT city_address, COUNT(customer_id)
FROM customers
GROUP BY city_address IN ('Los Angeles', 'San Francisco');
www.CertificationKing.com
- 43 -
Answer: B
Q: 69 You need to perform certain data manipulation operations through a view
called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the
SELECT statement on which the view was created.) How do you obtain the definition of the view?
A. Use the DESCRIBE command on the EMP_DEPT_VU view.
B. Use the DEFINE VIEW command on the EMP_DEPT_VU view.
C. Use the DESCRIBE VIEW command on the EMP_DEPT_VU view.
D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view.
E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view.
F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.
Answer: D
Q: 70 Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SAL NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You want to create a SQL script file that contains an INSERT statement. When the script is run, the
INSERT statement should insert a row with the specified values into the EMPLOYEES table. 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);
B. INSERT INTO employees
www.CertificationKing.com
- 44 -
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid',
2000, NULL, &did IN (20,50));
C. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
D. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50)
WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E. 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);
Answer: D
Q: 71 Which three are DATETIME data types that can be used when specifying
column definitions? (Choose three.)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: A, C, D
Q: 72 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.
www.CertificationKing.com
- 45 -
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Answer: C, D
Q: 73 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?
A. You have no rows in the table.
B. You have an employee with the name of James.
C. You cannot roll back to the same savepoint more than once.
D. Your last update fails to update any rows because employee ID 180 was already deleted.
Answer: A
www.CertificationKing.com
- 46 -
Q: 74 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
Answer: C, E
Q: 75 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 when the order was placed, today's date
should be used instead.
Which statement accomplishes this?
A. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2 (10),
date_ordered DATE = SYSDATE);
B. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE);
C. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE);
D. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
www.CertificationKing.com
- 47 -
order_status VARCHAR2 (10),
date_ordered DATE = SYSDATE);
E. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status NUMBER (10),
date_ordered DATE = SYSDATE);
F. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(,
order_status NUMBER (10),
date_ordered DATE DEFAULT SYSDATE);
Answer: B
Q: 76 In which three cases would you use the USING clause? (Choose three.)
A. You want to create a nonequijoin.
B. The tables to be joined have multiple NULL columns.
C. The tables to be joined have columns of the same name and different data types.
D. The tables to be joined have columns with the same name and compatible data types.
E. You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.
Answer: C, D, E
Q: 77 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 SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired results.
Answer: C
Introduction to Oracle9i: SQL
156 Q&A
Looking for Real Exam Questions for IT Certification Exams!
We guarantee you can pass any IT certification exam at your first attempt with just 10-12
hours study of our guides.
Our study guides contain actual exam questions, you will get word to word same on your
actual test; accurate answers with detailed explanation verified by experts and all graphics
and drag-n-drop exhibits shown just as on the real test.
To test the quality of our guides, you can download the one-fourth portion of any guide from
http://www.certificationking.com absolutely free.
Besides, we also offer complete version of following exams absolutely free. You can start
your certification from these free guides and if you are satisfied you can buy the rest
Microsoft: 70-270, 70-305, 70-526, MB2-421 Cisco 642-901, 642-871, 642-611, 642-502, 642-425, 646-561 Oracle: 1Z0-001, 1Z0-007, 1Z0-040, 1Z0-200 Novell: 50-676, 50-565 Adobe: 9A0-019 Apple: 9L0-004, 9L0-505, 9L0-606 3COM: 3M0-211 Citrix: 1Y0-223, 1Y0-256 CIW: 1D0-420 EMC: E20-001 Enterasys: 2B0-011 Exam Express: EE0-011 Foundry: FN0-100 Hyperion: 4H0-002 HP: HP0-045, HP0-052, HP0-055, HP0-090, HP0-216, HP0-417 Juniper: JN0-120, JN0-330 Lotus: 190-510 Network Appliances: NS0-121 Network General: 1T6-520 Nortel: 920-430, 920-803 OMG: UM0-100 Sybase: 510-015 SAIR: 3X0-101, 201 SAS: A00-201 Tibco: TB0-103 …..For pricing and placing order, please visit http://certificationking.com/order.html
We accept all major credit cards through www.paypal.com
For other payment options and any further query, feel free to mail us at
info@certificationking.com
Q: 1 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
VALUES ( NULL, 'John', 'Smith');
B. INSERT INTO employees( first_name, last_name)
VALUES( 'John', 'Smith');
C. INSERT INTO employees
VALUES ( '1000', 'John', NULL);
D. INSERT INTO employees (first_name, last_name, employee_id)
VALUES ( 1000, 'John', 'Smith');
E. INSERT INTO employees (employee_id)
VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, 'John', ' ');
Answer: C, E, F
Q: 2 Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?
A. 46 and 45
B. 46 and 45.93
C. 50 and 45.93
D. 50 and 45.9
E. 45 and 45.93
F. 45.95 and 45.93
Answer: C
www.CertificationKing.com
- 3 -
Q: 3 Which are DML statements? (Choose all that apply.)
A. COMMIT
B. MERGE
C. UPDATE
D. DELETE
E. CREATE
F. DROP...
Answer: B, C, D
Q: 4 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?
A. The DESCRIBE DEPT statement displays the structure of the DEPT table.
B. The ROLLBACK statement frees the storage space occupied by the DEPT table.
C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT
statement introduced before the ROLLBACK statement.
Answer: A
Q: 5 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. No change is required to achieve the desired results.
www.CertificationKing.com
- 4 -
B. SELECT ename, sal, 12*(sal+100)
FROM emp;
C. SELECT ename, sal, (12*sal)+100
FROM emp;
D. SELECT ename, sal+100,*12
FROM emp;
Answer: B
Q: 6 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. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE
Answer: A, F
Q: 7 Examine the structure of the EMP_DEPT_VU view:
Column Name Type Remarks
EMPLOYEE_ID NUMBER From the EMPLOYEES table
EMP_NAME VARCHAR2(30) From the EMPLOYEES table
JOB_ID VARCHAR2(20) From the EMPLOYEES table
SALARY NUMBER From the EMPLOYEES table
DEPARTMENT_ID NUMBER From the DEPARTMENTS table
DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table
www.CertificationKing.com
- 5 -
Which SQL statement produces an error?
A. SELECT *
FROM emp_dept_vu;
B. SELECT department_id, SUM(salary)
FROM emp_dept_vu
GROUP BY department_id;
C. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vu
GROUP BY department_id, job_id;
D. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E. None of the statements produce an error; all are valid.
Answer: E
Q: 8 Click the Exhibit button and examine the data from the ORDERS and
CUSTOMERS tables.
Evaluate this SQL statement:
SELECT cust_id, ord_total
FROM orders
WHERE ord_total > ANY(SELECT ord_total
FROM orders
WHERE cust_id IN (SELECT cust_id
FROM customers
WHERE city LIKE
'New York'));
What is the result when the above query is executed?
www.CertificationKing.com
- 6 -
www.CertificationKing.com
- 7 -
www.CertificationKing.com
- 8 -
A. A
Answer: A
Q: 9 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. difference, projection, join
C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product
Answer: A
Q: 10 Click the Exhibit button and examine the data from the EMP table.
The COMMISSION column shows the monthly commission earned by the employee.
Which three tasks would require subqueries or joins in order to be performed in a single step? (Choose
three.)
www.CertificationKing.com
- 9 -
A. deleting the records of employees who do not earn commission
B. increasing the commission of employee 3 by the average commission earned in department 20
C. finding the number of employees who do NOT earn commission and are working for department 20
D. inserting into the table a new employee 10 who works for department 20 and earns a commission that is
equal to the commission earned by employee 3
E. creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and
COMMISSION of the EMP table
F. decreasing the commission by 150 for the employees who are working in department 30 and earning a
commission of more than 800
Answer: B, D, E
Q: 11 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?
A. ALTER TABLE students
ADD PRIMARY KEY student_id;
B. ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
www.CertificationKing.com
- 10 -
Q: 12 Which three are DATETIME data types that can be used when specifying
column definitions? (Choose three.)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: A, C, D
Q: 13 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. all of the desired results
B. two of the desired results
C. one of the desired results
D. an error statement
Answer: C
Q: 14 Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
www.CertificationKing.com
- 11 -
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an
error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT
table.
Answer: A, D, F
Q: 15 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. randomly
B. ascending by date
C. descending by date
D. ascending alphabetically
E. descending alphabetically
Answer: B
Q: 16 Mary has a view called EMP_DEPT_LOC_VU that was created based on
the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott
on this view. Which option enables Scott to eliminate the need to qualify the view with the name
MARY.EMP_DEPT_LOC_VU each time the view is referenced?
www.CertificationKing.com
- 12 -
A. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE PRIVATE SYNONYM EDL_VU
FOR mary.EMP_DEPT_LOC_VU;
then he can prefix the columns with this synonym.
B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE SYNONYM EDL_VU
FOR mary.EMP_DEPT_LOC_VU;
then he can prefix the columns with this synonym.
C. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE LOCAL SYNONYM EDL_VU
FOR mary.EMP_DEPT_LOC_VU;
then he can prefix the columns with this synonym.
D. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE SYNONYM EDL_VU
ON mary(EMP_DEPT_LOC_VU);
then he can prefix the columns with this synonym.
E. Scott cannot create a synonym because synonyms can be created only for tables.
F. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and
grant SELECT privilege on that synonym to Scott.
Answer: B
Q: 17 A subquery can be used to ___.
A. create groups of data
B. sort data in a specific order
C. convert data to a different format
D. retrieve data based on an unknown condition
Answer: D
Q: 18 Click the Exhibit button to examine the data of the EMPLOYEES table.
Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's
manager, for all the employees who have a manager and earn more than 4000?
www.CertificationKing.com
- 13 -
A. SELECT employee_id "Emp_id", emp_name "Employee",
salary,
employee_id "Mgr_id", emp_name "Manager"
FROM employees
WHERE salary > 4000;
B. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e JOIN employees m
WHERE e.mgr_id = m.mgr_id
AND e.salary > 4000;
C. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e JOIN employees m
ON (e.mgr_id = m.employee_id)
AND e.salary > 4000;
D. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id", m.emp_name "Manager"
FROM employees e SELF JOIN employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;
E. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id" m.emp_name "Manager"
FROM employees e JOIN employees m
www.CertificationKing.com
- 14 -
USING (e.employee_id = m.employee_id)
AND e.salary > 4000;
Answer: C
Q: 19 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. Column definitions cannot be altered to add DEFAULT values.
B. A change to the DEFAULT value affects only subsequent insertions to the table.
C. Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.
D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
Answer: B
Q: 20 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?
www.CertificationKing.com
- 15 -
A. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco');
B. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address, customer_id;
D. SELECT city_address, COUNT(customer_id)
FROM customers
GROUP BY city_address IN ('Los Angeles', 'San Francisco');
Answer: B
Q: 21 Click the Exhibit button to examine the structures of the EMPLOYEES,
DEPARTMENTS, and TAX tables.
For which situation would you use a nonequijoin query?
www.CertificationKing.com
- 16 -
A. to find the tax percentage for each of the employees
B. to list the name, job_id, and manager name for all the employees
C. to find the name, salary, and the department name of employees who are not working with Smith
D. to find the number of employees working for the Administrative department and earning less than 4000
E. to display name, salary, manager ID, and department name of all the employees, even if the employees do
not have a department ID assigned
Answer: A
Q: 22 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
www.CertificationKing.com
- 17 -
FROM EMP
WHERE DEPARTMENT_ID = NULL;
What is true about this SQL statement ?
A. The SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired results.
Answer: C
Q: 23 Which two statements about sequences are true? (Choose two.)
A. 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.
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 CURRVAL pseudo column to generate a value from a sequence that would be used for a
specified database column.
E. 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.
F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum
value defined for the sequence.
Answer: B, C
Q: 24 What is true of using group functions on columns that contain NULL
values?
A. Group functions on columns ignore NULL values.
B. Group functions on columns returning dates include NULL values.
C. Group functions on columns returning numbers include NULL values.
D. Group functions on columns cannot be accurately used on columns that contain NULL values.
E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
Answer: A
www.CertificationKing.com
- 18 -
Q: 25 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 "Annual Salary"
FROM employees;
C. SELECT ename, salary*12 AS Annual Salary
FROM employees;
D. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY")
FROM employees
Answer: B
Q: 26 Click the Exhibit button and examine the data in the EMPLOYEES and
DEPARTMENTS tables.
You want to retrieve all employees, whether or not they have matching departments in the departments
table. Which query would you use?
A. SELECT last_name, department_name
www.CertificationKing.com
- 19 -
FROM employees NATURAL JOIN departments;
B. SELECT last_name, department_name
FROM employees JOIN departments ;
C. SELECT last_name, department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
D. SELECT last_name, department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
E. SELECT last_name, department_name
FROM employees FULL JOIN departments
ON (e.department_id = d.department_id);
F. SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);
Answer: F
Q: 27 Which SQL statement defines a FOREIGN KEY constraint on the
DEPTNO column of the EMP table?
A. 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);
B. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)
FOREIGN KEY (deptno));
D. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
www.CertificationKing.com
- 20 -
Answer: B
Q: 28 Click the Exhibit button and examine the data in the EMPLOYEES table.
Which three subqueries work? (Choose three.)
A. SELECT *
FROM employees
where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
C. SELECT distinct department_id
FROM employees
WHERE salary > ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
D. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
E. SELECT last_name
FROM employees
WHERE salary > ANY (SELECT MAX(salary)
FROM employees
GROUP BY department_id);
F. SELECT department_id
www.CertificationKing.com
- 21 -
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY AVG(SALARY));
Answer: C, D, E
Q: 29 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 &3
WHERE '&last_name = '&4'';
C. SELECT &1, &2
FROM &3
WHERE last_name = '&4';
D. SELECT &1, '&2'
FROM EMP
WHERE last_name = '&4';
Answer: C
Q: 30 Evaluate these two SQL statements:
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY salary DESC;
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY 2 DESC;
What is true about them?
A. The two statements produce identical results.
B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order by default.
D. The two statements can be made to produce identical results by adding a column alias for the salary column
in the second SQL statement.
www.CertificationKing.com
- 22 -
Answer: A
Q: 31 In which scenario would an index be most useful?
A. The indexed column is declared as NOT NULL.
B. The indexed columns are used in the FROM clause.
C. The indexed columns are part of an expression.
D. The indexed column contains a wide range of values.
Answer: D
Q: 32 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(SYSDATE,'yyyy')
FROM dual;
B. SELECT TO_DATE(SYSDATE,'yyyy')
FROM dual;
C. SELECT DECODE(SUBSTR(SYSDATE,
, 'YYYY')FROM dual;
D. SELECT DECODE(SUBSTR(SYSDATE,
, 'year')FROM dual;
E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;
Answer: A
Q: 33 Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name
Smith?
A. SELECT COUNT(*)
FROM employees
www.CertificationKing.com
- 23 -
WHERE last_name='Smith';
B. SELECT COUNT(dept_id)
FROM employees
WHERE last_name='Smith';
C. SELECT DISTINCT(COUNT(dept_id))
FROM employees
WHERE last_name='Smith';
D. SELECT COUNT(DISTINCT dept_id)
FROM employees
WHERE last_name='Smith';
E. SELECT UNIQUE(dept_id)
FROM employees
WHERE last_name='Smith';
Answer: D
Q: 34 Examine the description of the MARKS table:
STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)
SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)
SUBJ3 NUMBER(3)
SUBJ1, SUBJ2, and SUBJ3 indicate the marks (grades) obtained by a student in the three subjects.
Which two statements are valid? (Choose two.)
A. SELECT SUM(subj1, subj2, subj3)
FROM marks;
B. SELECT SUM(subj1 + subj2 + subj3)
FROM marks;
C. SELECT SUM(subj1), SUM(subj2), SUM(subj3)
FROM marks;
D. SELECT MAX(subj1, subj2, subj3)
FROM marks;
E. SELECT MINIMUM(subj1)
FROM marks;
F. SELECT COUNT(std_id)
FROM marks
WHERE subj1 >= AVG(subj1);
Answer: B, C
www.CertificationKing.com
- 24 -
Q: 35 Which are iSQL*Plus commands? (Choose all that apply.)
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Q: 36 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;
Answer: B, C, D
Q: 37 What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
www.CertificationKing.com
- 25 -
Q: 38 Click the Exhibit button to examine the structure of the EMPLOYEES,
DEPARTMENTS, and LOCATIONS tables.
Two new departments are added to your company as shown:
DEPARTMENT_ID DEPARTMENT_NAME MGR_ID LOCATION_ID
9998 Engineering 123
9999 Administrative Boston
You need to list the names of employees, the department IDs, the department names, and the cities where
the departments are, even if there are no employees in the departments and even if the departments are
not yet assigned to a location. You need to join the EMPLOYEES, DEPARTMENTS, and LOCATIONS
tables to retrieve this information.
Which statement do you execute to retrieve this information?
A. SELECT e.last_name, d.department_id,
d.department_name, l.city
www.CertificationKing.com
- 26 -
FROM departments d
RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id;
B. SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
FULL OUTER JOIN employees e
ON d.department_id = e.department_id
FULL OUTER JOIN locations l
ON d.location_id = l.location_id;
C. SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
LEFT OUTER JOIN locations l
ON d.location_id = l.location_id;
D. SELECT last_name, department_id,
department_name, city
FROM departments d
NATURAL JOIN employees e
NATURAL JOIN locations l;
Answer: C
Q: 39 What is true about joining tables through an equijoin?
A. You can join a maximum of two tables through an equijoin.
B. You can join a maximum of two columns through an equijoin.
C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign
key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum
of n-1 join conditions.
Answer: E
Q: 40 Which two statements are true about constraints? (Choose two.)
A. The UNIQUE constraint does not permit a null value for the column.
www.CertificationKing.com
- 27 -
B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D. The NOT NULL constraint ensures that null values are not permitted for the column.
Answer: B, D
Q: 41 Which two statements complete a transaction? (Choose two.)
A. DELETE employees;
B. DESCRIBE employees;
C. ROLLBACK TO SAVEPOINT C;
D. GRANT SELECT ON employees TO SCOTT;
E. ALTER TABLE employees
SET UNUSED COLUMN sal;
F. SELECT MAX(sal)
FROM employees
WHERE department_id = 20;
Answer: D, E
Q: 42 Which SQL statement returns a numeric value?
A. SELECT ADD_MONTHS(MAX(hire_Date), 6)
FROM EMP;
B. SELECT ROUND(hire_date)
FROM EMP;
C. SELECT sysdate-hire_date
FROM EMP;
D. SELECT TO_NUMBER(hire_date + 7)
FROM EMP;
Answer: C
Q: 43 Examine the structure of the EMPLOYEES and NEW_EMPLOYEES
tables:
EMPLOYEES
__________
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
www.CertificationKing.com
- 28 -
HIRE_DATE DATE
NEW_EMPLOYEES
_______________
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which DELETE statement is valid?
A. DELETE FROM employees
WHERE employee_id = (SELECT employee_id
FROM employees);
B. DELETE * FROM employees
WHERE employee_id = (SELECT employee_id
FROM new_employees);
C. DELETE FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WHERE name ='Carrey');
D. DELETE * FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WHERE last_name ='Carrey');
Answer: C
Q: 44 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(
,order_status NUMBER(10) DEFAULT 'PENDING',
date_ordered DATE );
www.CertificationKing.com
- 29 -
B. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
C. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
D. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
E. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
F. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered VARCHAR2 );
Answer: E
Q: 45 In which two cases would you use an outer join? (Choose two.)
A. The tables being joined have NOT NULL columns.
B. The tables being joined have only matched data.
C. The columns being joined have NULL values.
D. The tables being joined have only unmatched data.
E. The tables being joined have both matched and unmatched data.
F. Only when the tables have a primary key-foreign key relationship.
Answer: C, E
Q: 46 Which two statements are true about WHERE and HAVING clauses?
(Choose two.)
www.CertificationKing.com
- 30 -
A. A WHERE clause can be used to restrict both rows and groups.
B. A WHERE clause can be used to restrict rows only.
C. A HAVING clause can be used to restrict both rows and groups.
D. A HAVING clause can be used to restrict groups only.
E. A WHERE clause CANNOT be used in a query if the query uses a HAVING clause.
F. A HAVING clause CANNOT be used in subqueries.
Answer: B, D
Q: 47 Examine the structure of the EMPLOYEES table:
Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column
of the DEPARTMENTS table
You need to create a view called EMP_VU that allows the users to insert rows through the view.
Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?
A. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name,
department_id
FROM employees
WHERE mgr_id IN (102, 120);
B. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
department_id
FROM employees
WHERE mgr_id IN (102, 120);
C. CREATE VIEW emp_vu AS
SELECT department_id, SUM(sal) TOTALSAL
FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_id;
D. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM employees;
www.CertificationKing.com
- 31 -
Answer: B
Q: 48 Evaluate this SQL statement:
e.employee_id, (.15* e.salary) + (.5 * e.commission_pct)
+ (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE
FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
What will happen if you remove all the parentheses from the calculation?
A. The value displayed in the CALC_VALUE column will be lower.
B. The value displayed in the CALC_VALUE column will be higher.
C. There will be no difference in the value displayed in the CALC_VALUE column.
D. An error will be reported.
Answer: C
Q: 49 Which four are attributes of single row functions? (Choose four.)
A. cannot be nested
B. manipulate data items
C. act on each row returned
D. return one result per row
E. accept only one argument and return only one value
F. accept arguments which can be a column or an expression
Answer: B, C, D, F
Q: 50 Which view should a user query to display the columns associated with the
constraints on a table owned by the user?
A. USER_CONSTRAINTS
B. USER_OBJECTS
C. ALL_CONSTRAINTS
D. USER_CONS_COLUMNS
E. USER_COLUMNS
Answer: D
www.CertificationKing.com
- 32 -
Q: 51 Examine the structure of the EMPLOYEES and NEW_EMPLOYEES
tables:
EMPLOYEES
___________
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
________________
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which UPDATE statement is valid?
A. UPDATE new_employees SET name = (SELECT last_name||
first_name
FROM employees
WHERE employee_id
=180)
WHERE employee_id =180;
B. UPDATE new_employees SET name = (SELECT
last_name||first_name
FROM employees )
WHERE employee_id =180;
C. UPDATE new_employees SET name = (SELECT last_name ||
first_name
FROM employees
WHERE employee_id
=180)
WHERE employee_id =(SELECT employee_id
FROM new_employees);
D. UPDATE new_employees SET name = (SELECT last_name||
first_name
FROM employees
WHERE employee_id =
(SELECT employee_id
FROM new_employees))
WHERE employee_id
=180;
www.CertificationKing.com
- 33 -
Answer: A
Q: 52 Which two statements about subqueries are true? (Choose two.)
A. A subquery should retrieve only one row.
B. A subquery can retrieve zero or more rows.
C. A subquery can be used only in SQL query statements.
D. Subqueries CANNOT be nested by more than two levels.
E. A subquery CANNOT be used in an SQL query statement that uses group functions.
F. When a subquery is used with an inequality comparison operator in the outer SQL statement, the column
list in the SELECT clause of the subquery should contain only one column.
Answer: B, F
Q: 53 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?
A. ALTER TABLE students
ADD PRIMARY KEY student_id;
B. ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
Q: 54 You need to change the definition of an existing table. The
COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up
to 1000 bytes. The column can currently hold 500 bytes per value. The table contains 20000 rows. Which
statement is valid?
A. ALTER TABLE commercials
MODIFY (description CHAR2(1000));
B. ALTER TABLE commercials
CHANGE (description CHAR2(1000));
C. ALTER TABLE commercials
CHANGE (description VARCHAR2(1000));
www.CertificationKing.com
- 34 -
D. ALTER TABLE commercials
MODIFY (description VARCHAR2(1000));
E. You cannot increase the size of a column if the table has rows.
Answer: D
Q: 55 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 data from both tables.
D. You want all unmatched data from both tables.
E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table.
Answer: D
Q: 56 Which are DML statements? (Choose all that apply.)
A. COMMIT
B. MERGE
C. UPDATE
D. DELETE
E. CREATE
F. DROP...
Answer: B, C, D
Q: 57 Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL LAST_
NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR2(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid in that
department, only if the minimum salary is less than 5000 and maximum salary is more than 15000?
www.CertificationKing.com
- 35 -
A. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;
B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Answer: D
Q: 58 Which four statements correctly describe functions that are available in
SQL? (Choose four.)
A. INSTR returns the numeric position of a named character.
B. NVL2 returns the first non-null expression in the expression list.
C. TRUNCATE rounds the column, expression, or value to n decimal places.
D. DECODE translates an expression after comparing it to each search value.
E. TRIM trims the heading or trailing characters (or both) from a character string.
F. NVL compares two expressions and returns null if they are equal, or the first expression if they are not
equal.
G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not
equal.
Answer: A, D, E, G
Q: 59 Which iSQL*Plus feature can be used to replace values in the WHERE
clause?
A. substitution variables
B. replacement variables
C. prompt variables
www.CertificationKing.com
- 36 -
D. instead-of variables
E. This feature cannot be implemented through iSQL*Plus.
Answer: A
Q: 60 Which is an iSQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Q: 61 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. randomly
B. ascending by date
C. descending by date
D. ascending alphabetically
E. descending alphabetically
Answer: B
Q: 62 Click the Exhibit button and examine the data from the ORDERS and
CUSTOMERS tables.
www.CertificationKing.com
- 37 -
Evaluate the SQL statement:
SELECT *
FROM orders
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_name = 'Smith');
What is the result when the query is executed?
www.CertificationKing.com
- 38 -
A. D
Answer: A
Q: 63 What is true about updates through a view?
A. You cannot update a view with group functions.
B. When you update a view group functions are automatically computed.
C. When you update a view only the constraints on the underlying table will be in effect.
www.CertificationKing.com
- 39 -
D. When you update a view the constraints on the views always override the constraints on the underlying
tables.
Answer: A
Q: 64 What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
Q: 65 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 LIKE '%SA\_%' ESCAPE '\';
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 = '%SA_';
Answer: A
Q: 66 Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
www.CertificationKing.com
- 40 -
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT 'SA_REP'
SAL NUMBER
COMM_PCT NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement you specify should
update the rows with the values specified below:
JOB_ID: Default value specified for this column definition.
SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission percentage
column, if any.
If no default value is specified for the column,
the value should be NULL.
DEPARTMENT_ID: Supplied by the user during run time through
substitution variable.
Which UPDATE statement meets the requirements?
A. UPDATE employees
SET job_id = DEFAULT
AND Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP')
AND comm_pct = DEFAULT
AND department_id = &did
WHERE employee_id IN (103,115);
B. UPDATE employees
SET job_id = DEFAULT
AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL
AND department_id = &did
WHERE employee_id IN (103,115)
AND job_id = 'SA_REP';
C. UPDATE employeesC.UPDATE employees
SET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115);
D. UPDATE employeesD.UPDATE employees
www.CertificationKing.com
- 41 -
SET job_id = DEFAULT,
Sal = MAX(sal),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115)
AND job_id = 'SA_REP';
E. UPDATE employees
SET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT OR NULL,
department_id = &did
WHERE employee_id IN (103,115);
Answer: C
Q: 67 Click the Exhibit button to examine the structures of the EMPLOYEES and
TAX tables.
You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
A. SELECT employee_id, salary, tax_percent
FROM employees e JOIN tax t
www.CertificationKing.com
- 42 -
ON e.salary BETWEEN t.min_salary AND t.max_salary;
B. SELECT employee_id, salary, tax_percent
FROM employees e JOIN tax t
WHERE e.salary > t.min_salary AND < t.max_salary;
C. SELECT employee_id, salary, tax_percent
FROM employees e JOIN tax t
ON (MIN(e.salary) = t.min_salary
AND MAX(e.salary) = t.max_salary);
D. You cannot find the information because there is no common column between the two tables.
Answer: A
Q: 68 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?
A. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco');
B. SELECT city_address, COUNT(*)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id)
FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco')
GROUP BY city_address, customer_id;
D. SELECT city_address, COUNT(customer_id)
FROM customers
GROUP BY city_address IN ('Los Angeles', 'San Francisco');
www.CertificationKing.com
- 43 -
Answer: B
Q: 69 You need to perform certain data manipulation operations through a view
called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the
SELECT statement on which the view was created.) How do you obtain the definition of the view?
A. Use the DESCRIBE command on the EMP_DEPT_VU view.
B. Use the DEFINE VIEW command on the EMP_DEPT_VU view.
C. Use the DESCRIBE VIEW command on the EMP_DEPT_VU view.
D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view.
E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view.
F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.
Answer: D
Q: 70 Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SAL NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You want to create a SQL script file that contains an INSERT statement. When the script is run, the
INSERT statement should insert a row with the specified values into the EMPLOYEES table. 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);
B. INSERT INTO employees
www.CertificationKing.com
- 44 -
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid',
2000, NULL, &did IN (20,50));
C. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
D. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50)
WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E. 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);
Answer: D
Q: 71 Which three are DATETIME data types that can be used when specifying
column definitions? (Choose three.)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: A, C, D
Q: 72 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.
www.CertificationKing.com
- 45 -
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Answer: C, D
Q: 73 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?
A. You have no rows in the table.
B. You have an employee with the name of James.
C. You cannot roll back to the same savepoint more than once.
D. Your last update fails to update any rows because employee ID 180 was already deleted.
Answer: A
www.CertificationKing.com
- 46 -
Q: 74 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
Answer: C, E
Q: 75 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 when the order was placed, today's date
should be used instead.
Which statement accomplishes this?
A. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2 (10),
date_ordered DATE = SYSDATE);
B. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE);
C. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE);
D. CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,www.CertificationKing.com
- 47 -
order_status VARCHAR2 (10),
date_ordered DATE = SYSDATE);
E. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status NUMBER (10),
date_ordered DATE = SYSDATE);
F. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(
,order_status NUMBER (10),
date_ordered DATE DEFAULT SYSDATE);
Answer: B
Q: 76 In which three cases would you use the USING clause? (Choose three.)
A. You want to create a nonequijoin.
B. The tables to be joined have multiple NULL columns.
C. The tables to be joined have columns of the same name and different data types.
D. The tables to be joined have columns with the same name and compatible data types.
E. You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.
Answer: C, D, E
Q: 77 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 SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired results.
Answer: C
