Q1. 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', 'Smith');
E. INSERT INTO employees (employee_id)
VALUES (1000);
F. INSERT INTO emloyees (employee_id_first_name,last_name)
VALUES (1000,'John', 'Smith');
Answer: CEF
Q2. Which SELECT statement will get the result 'elloworld' fromt the string 'HelloWorld'?
A. SELECT SUBSTR ('HelloWorld',1) FROM dual;
B. SELECT INITCAP(TRIM('HellowWorld', 1,1) FROM dual
C. SELECT LOWER (SUBSTR ('HellowWorld', 2,1) FROM dual
D. SELECT LOWER (SUBSTR('HellowWorld', 2,1) FROM dual
E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual
Answer: E
Q3. Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:
LAST NAME VARCHAR2(35) NOT NULL
SALARY NUMBER(9,2) NOT NULL
COMMISSION_PCT NUMBER(4,2)
Which statement ensures that a value is displayed in the calculated column for all employees?
A. SELECT last_name, 12 * salary* commission_pct
FROM emp;
B. SELECT last_name, 12 * salary* (commission_pct,0)
FROM emp;
C. SELECT last_name, 12 * salary* (nvl(commission_pct,0)
FROM emp;
D. SELECT last_name, 12 * salary* (decode(commission_pct,0))
FROM emp;
Answer: C
Q4. Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHAR2(10)
START_DATE DATE
END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose Two)
A. SUM(start_date)
B. AVG (start_date)
C. COUNT (start_date)
D. AVG(start_date, end_date)
E. MIN (start_date)
F. MAXIMUM (start_date)
Answer: CE
Q5. From SQL*Plus, you issue this SELECT statement:
SELECT *
FROM orders;
You use this statement to retrieve data from a database table for _______________. (Choose all that apply)
A. updating
B. viewing
C. deleting
D. inserting
E. truncating
Answer: BD
Q6. Which four statements correctly describe functions that are available in SQL? (Choose four)
A. INSTR returns the numeric position of a named character
B. NVL 2 returns the first non-null expression in theexpression 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 leading 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 : ADEG
Q7. Which two statements are true regarding the ORDER BY clause? (Choose two)
A. The sort is in ascending order by default
B. The sort is in descending order by default
C. The ORDER BY clause must precede the WHERE clause.
D. The ORDER BY clause is executed on the client side
E. The ORDER BY clause comes last in the SELECT statement
F. The ORDER BY clause is executed first in the query execution.
Answer: AE
Q8. 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: BE
Q9. Evaluate the set of SQL statements:
CREATE TABLE dept
(deptbi NUMBER (2)
dname VARCHAR2(14),
Ioc 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
Q10. Which are DML statements? (Choose all that apply)
A. COMMIT...
B. MERGE...
C. UPDATE...
D. DELETE...
E. CREATE...
F. DROP...
Answer: ABCD
Q11. 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
Q12. 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 columns contains a wide range of values.
Answer: D
Q13. Which two are attributes of /SQL* Plus? (Choose two).
A. /SQL * Plus commands cannot be abbreviated
B. /SQL* Plus commands are accessed from a browser.
C. /SQL*Plus commands are used to manipulate data in tables
D. /SQL* Plus command manipulate table definitions in the database
E. /SQL* Plus is the Oracle proprietary interface for executing SQL statements.
Answer: CE
Q14. Which three statements about subqueries are true? (Choose three).
A. A single row subquery can retrieve only one column and one row
B. A single row subquery can retrieve only one row but many columns
C. A multiple row subquery can retrieve multiple rows and multiple columns
D. A multiple row subquery can be compared using the ">" operator
E. A single row subquery can use the IN operator
F. A multiple row subquery can use the "=" operator
Answer: BCD
Q15. When should you create a role? (Choose two)
A. to simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement
B. to grant a group of related privileges to a user
C. When the number of people using the database is very high
D. to simplify the process of granting and revoking privileges
E. to simplify profile maintenance for a user who is constantly traveling.
Answer: BD
Q16. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
A. ORDER BY SALARY > 5000
B. GROUP BY SALARY > 5000
C. HAVING SALARY > 5000
D. WHERE SALARY > 5000
Answer: D
Q17. Which four are correct guidelines for naming database tables? (Choose four)
A. Must begin with either a number or a letter
B. must be 1-30 characters long
C. should not be an Oracle Server reserved word.
D. must contain only A-Z, a-z, 0-9, _,*, and #
E. must contain only A-Z, a-z, 0-9, _, $, and #
F. must begin with a letter
Answer: BCEF
Q18. 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 form 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.
Answer: BC
Q19. The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER (6,2)
DEPARTMENT_ID NUMBER(6)
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
Q20. Examine the description of the MARKS table:
STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)
SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)
SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects
Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG (subj1) AND subj2 > AVG (subj2)
ORDER BY total_marks;
What us the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.
B. The statement returns an error at the SELECT clause
C. The statement returns an error at the WHERE clause
D. The statement returns an error at the ORDER BY clause
Answer: C
Q21.Which operator can be used with a multiple row subquery?
A **
B LIKE
C. BETWEEN
D. NOT IN
E. Is
F. <>
Answer: D
Q22. 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
Q23. Which statement explicitly names a constraint?
A. ALTER TABLE student_grades
ADD FOREIGN KEY (student_id) REFERENCES students (student_id);
B. ALTER TABLE student_grades
ADD CONSTRAINT NAME=student_id_fk
FOREIGN KEY (student_id) REFERENCES student(student_id);
C. ALTER TABLE student_grades
ADD CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);
D. ALTER TABLE student grades
ADD NAMED CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
F. ALTER TABLE student grades
ADD NAME student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
Answer: C
Q24. You need to display the last names of those employees who have the letter “A” as the second character in their names. Which SQL statement displays the required results?
A. SELECT last_name
FROM EMP
WHERE last_name LIKE’_A%;
B. SELECT last_name
FROM EMP
WHERE last name=’*A%
C. SELECT last_name
FROM EMP
WHERE last name =’* _A%;
D. SELECT last_name
FROM EMP
WHERE last name LIKE ‘* a%
Answer: A
Q25. 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: F
Q26. Which two statements about creating constraints are true? (Choose two)
A. Constraint names must start with SYS_C.
B. All constraints must be defined at the column level
C. Constraints can be created after the table is created
D. Constraints can be created at the same time the table is created
E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view
Answer: CD
Q27. Examine the SQL statements 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. PRD_TOTAL
F. Composite index on ORDER_ID and ORDER_DATE
Answer: AF
Q28. You are granted the CREATE VIEW privilege. What does this allow you to do?
A. create a table view
B. create a view in any scheme
C. create a view in your schema
D. create a sequence view in any schema
E. create a view that is accessible by everyone
F. create a view only if it is based on tables that you created
Answer: C
Q29. Which three SELECT statements display value is displayed in the calculated
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: BCD
Q30. Evaluate the SQL statement
DROP TABLE DEPT;
Which four statements are true of the SQL statement? (Choose four)
A. You cannot roll back this statement
B. All pending transactions are committed
C. All views based on the DEPT table are deleted
D. All indexes based on the DEPT table are dropped
E. All data in the table is deleted, and the table structure is also deleted
F. All data in the table is deleted, but the structure of the table is retained
G. All synonyms based on the DEPT table are deleted
Answer: ABDE
Q31. Which statement describes the ROWID data type?
A. binary data up to 4 gigabytes
B. character data up to 4 gigabytes
C. raw binary data of variable length up to 2 gigabytes
D. binary data stored in an external file, up to 4 gigabytes
E. a hexadecimal string representing the unique address of a row in its table
Answer: E
Q32. You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMER table has these columns:
CUST_ID NUMBER(4) NOT NULL
CUST_NAME VARCHAR2(100) NOT NULL
CUST_ADDRESS VARCHAR2(150)
CUST_PHONE VARCHAR(20)
Which SELECT statement accomplishes this task?
A. SELECT *
FROM customers
B. SELECT name, address
FROM customers;
C. SELECT id, name, address, phone
FROM customers;
D. SELECT cust_name, cust_address
FROM customers;
E. SELECT cust_id, cust_name, cust_address, cust_phone
FROM customers;
Answer: D
Q33. Which two statements complete a transaction? (Choose two)
A. DELETE employees;
B. DESCRIBE employees
C. ROLLBACK TO SAVEPOINT C;
D. GRANT TABLE employees
E. ALTER TABLE employees
SET UNUSED COLUMN sal;
F. SELECT MAX (sal)
FROM employees
WHERE department_id = 20;
Answer: CE
Q34. The DBA issues this SQL command:
CREATE USER scott
INDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. no privileges
B. only the SELECT privilege
C. only the CONNECT privilege
D. all the privileges of a default user
Answer: A
Q35. 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
Q36. Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
Q37. What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges
B. It is required syntax for object privileges
C. It allows privileges on specified columns of tables
D. It is used to grant an object privilege on a foreign key column
E. It allows the grantee to grant object privileges to other users and roles
Answer: E
Q38. The STUDENT_GRADES table has these columns
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER (4,3)
The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplishes this?
A. SELECT AVERAGE(gpa)
FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
B. SELECT COUNT (gpa)
FROM student grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
C. SELECT MID (gpa)
FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
D. SELECT AVG (gpa)
FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
Answer: D
Q39. Which constraint can be defined only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY
Answer: B
Q40. In which scenario would Top N analysis be the best solution?
A. You want to identify the most senior employee in the company
B. You want to find the manager supervising the largest number of employees
C. You want to identify the person who makes the highest salary of all employees
D.You want to rank the top three sales representatives who have sold the maximum number of products
Answer: D
Q41. Which three are true explicitly names a constraint
joins? (Choose three.)
A. You cannot use IN operator in a condition that involves an outerjoin
B. You use (+) on both sides of the WHERE condition to perform an outerjoin
C. You use (*) on both sides of the WHERE condition to perform an outerjoin.
D. You use an outerjoin to see only the rows that do not meet the join condition
E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin
F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator
Answer: DEF
Q42. Which statement creates a new user?
A. CREATE USER susan
B. CREATE OR REPLACE USER susan
C. CREATE NEW USER susan
Default,
D. CREATE USER susan
INDENTIFIED BY blue
E. CREATE NEW USER susan
IDENTIFIED BY blue
F. CREATE OR REPLACE USER susan
IDENTIFIED BY blue;
Answer: D
Q43. 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 2000 bytes. The column can currently hold 1000 bytes per value. The table contains 20000 rows.
Which statement is valid?
A. ALTER TABLE commercial
MODIFY (description CHAR2(2000))
B. ALTER TABLE commercials
CHANGE (description CHAR2(2000))
C. ALTER TABLE commercials
CHANGE (description varchar2(2000))
D. ALTER TABLE commercials
MODIFY (description VARCHAR2(2000))
E. You cannot increase the size of a column if the able has rows.
Answer: D
Q44. What does the TRUNCATE statement do?
A. removes the table
B. removes all rows from a table
C. shortens the tale to 10 rows
D. removes all columns from a table
E. removes foreign keys from a table
Answer; B
Q45. Which SELECT statement should you use to extract the year form 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, 8), ‘YYYY’)
FROM dual
D. SELECT DECODE (SUBSTR (SYSATE, 8),’year’)
FROM dual
E. SELECT TO_CHAR (SUBSTR(SYSDATE, 8,2),’yyyy’)
FROM dual
Answer: A
Q46. 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
Q47. Which clause should you use to exclude group results?
A. WHERE
B. HAVING
C. RESTRICT
D. GROUP BY
E. ORDER BY
Answer: B
Q48. What is true about joining tables through an equation?
A. you can join a maximum of two tables through an equation
B. you can join a maximum, of two columns through an equation
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
Q49. You need to calculate the total of all salaries in the accounting department. Which group function should you use?
A. MAX
B. MIN
C. SUM
D. COUNT
E. TOTAL
F. LARGEST
Answer: C
Q80. Evaluate this SQL statement
SELECT 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 al 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
Q50. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator”=” What happens when the main query is executed?
A. the main query executes with the first value returned by the subquery
B. the main query executes with the last value returned by the subquery
C. the main query executes with all the values returned by the subquery
D. the main query fails because the multiple-row subquery cannot be used with the comparison operator.
E. You cannot define multiple-row subquery in the WHERE clause of a SQL query
Answer: D
