1. Which is an /SQL*Plus command?

a) INSERT
b) UPDATE
c) SELECT
d) DESCRIBE
e) DELETE
f) RENAME

2. Which /SQL*Plus feature can be used to replace values in the WHERE clause?
a) Substitution variables
b) Replacement variables
c) Prompt variables
d) Instead-of variables
e) This feature cannot be implemented through /SQL*Plus.

3. You want to use SQL*Plus to connect to the oracle database. Which of the following choices
does not indicate a component you must specify when logging into the oracle?
a) The SQL*Plus Keyword.
b) The username
c) The password.
d) The database name.

4. 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%’

5. Which statement concerning SQL functions is true?

a) Character functions can return character or number values.
b) Conversion functions convert a column definition from one data type to another data type.
c) Single-row functions can only be used in SELECT and WHERE clauses.
d) All date functions return DATE data type values.
6. Which script displays '01-JAN-02' when the ENROLL_DATE value is '01-JUL-01'?
a) SELECT ROUND(enroll_date, 'DAY')
FROM student;
b) SELECT ROUND(enroll_date, 'YEAR')
FROM student;
c) SELECT ROUND(enroll_date, 'MONTH')
FROM student;
d) SELECT ROUND(TO _CHAR(enroll_date, 'YYYY'))
FROM student;
7. 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
8. Which SELECT statement will the result ‘elloworld’ from the string ‘HelloWorld’?
a) SELECT SUBSTR( ‘HelloWorld’,1) FROM dual;
b) SELECT INITCAP(TRIM (‘HelloWorld’, 1,1)) FROM dual;
c) SELECT LOWER(SUBSTR(‘HellowWorld’, 1, 1) FROM dual;
d) SELECT LOWER(SUBSTR(‘HelloWorld’, 2, 1) FROM dual;
e) SELECT LOWER(TRIM (‘H’ FROM ‘HelloWorld’)) FROM dual;

9. Which clause should you use to exclude group results?

a) WHERE
b) HAVING
c) RESTRICT
d) GROUP BY
e) ORDER BY

10. Which two are character manipulation functions? (Choose two.)

a) TRIM
b) REPLACE
c) TRUNC
d) TO_DATE
e) MOD
f) CASE

11. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed
in the SELECT statement?
a) Immediately after the SELECT clause
b) Before the WHERE clause
c) Before the FROM clause
d) After the ORDER BY clause
e) After the WHERE clause
12. 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, 8), 'YYYY')
FROM dual;
d) SELECT DECODE(SUBSTR(SYSDATE, 8), 'year')
FROM dual;
e) SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;
13. Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;
What will be displayed?
a) 0
b) 1
c) 0.00
d) An error statement

14. The EMPLOYEE tables has these columns:

LAST_NAME VARCNAR2(35)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(5,2)

You want to display the name and annual salary multiplied by the commission_pct for all
employees. For records that have a NULL commission_pct, a zero must be displayed against the
calculated column. Which SQL statement displays the desired results?

a) SELECT last_name, (salary * 12) * commission_pct

FROM EMPLOYEES;
b) SELECT last_name, (salary * 12) * IFNULL(commission_pct,
FROM EMPLOYEES;
c) SELECT last_name, (salary * 12) * NVL2(commission_pct, 0)
FROM EMPLOYEES;
d) SELECT last_name, (salary * 12) * NVL(commission_pct, 0)
FROM EMPLOYEES;

15. Which of the following is NOT a GROUP function?

a) MAX
b) MIN
c) NVL
d) AVG
16. Which of the following clauses represent valid uses of group functions?
a) GROUP BY MAX(salary)
b) ORDER BY AVG(salary)
c) HAVING MAX(salary) > 10000
d) SELECT AVG(NVL(salary, 0))

17. Which SELECT statement should you use if you want to display unique combination of the
POSITION and MANAGER values from the EMPLOYEE table?
a) SELECT position, manager DISTINCT
FROM employee;
b) SELECT DISTINCT position, manager
FROM employee;
c) SELECT position, manager
FROM employee;
d) SELECT position, DISTINCT manager
FROM employee;

18. Which statement produces the number of different departments that have employees with
last name Smith?
a) SELECT COUNT(*) FROM employees 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';

19. What is/are correct statement(s) related to dual table?

a) It contains one column, DUMMY, and one row with the value X
b) The DUAL table is owned by the user SYS
c) DUAL table is useful when you want to return a value once only
d) All of the above

20. 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.
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;

21. Evaluate this SQL statement:

SELECT * FROM PRODUCTS ORDER BY price, product_name;
What is true about the SQL statement?
a) The results are not sorted.
b) The results are sorted numerically.
c) The results are sorted alphabetically.
d) The results are sorted numerically and then alphabetically.
22. What will be the outcome of the following query?
SELECT ROUND(144.23,-1) FROM dual;
a) 140
b) 144
c) 150
d) 100

23. Which two statements are true about WHERE and HAVING clauses? (Choose two)
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 of the query uses a HAVING clause.
f) A HAVING clause CANNOT be used in subqueries.

24. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause
placed in the SELECT statement?
a) Immediately after the SELECT clause
b) Before the WHERE clause
c) Before the FROM clause
d) After the ORDER BY clause
e) After the WHERE clause

25. Which of the following queries can you use to search for employees with the pattern 'A_B' in
their names?
a) SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';
b) SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;
c) SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';
d) SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%'ESCAPE '\';

26. To write a query that performs an outer join of tables A and B and returns all rows from B,
You need to write
a) any outer join
b) a left outer join
c) a cross join
d) a right outer join
e) an inner join

27. Evaluate this SQL statement:

SELECT * FROM PRODUCTS ORDER BY price, product_name;
What is true about the SQL statement?
a) The results are not sorted.
b) The results are sorted numerically.
c) The results are sorted alphabetically.
d) The results are sorted numerically and then alphabetically.
28. 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.

29. What is true about SET operators?

a) They change values of rows
b) They combine the results of only two component queries into one result
c) They combine the results of 10 component queries into two result sets.
d) They combine the results of two or more component queries into one result

30. Which of the following correctly shows the correct use of the TRUNC command on a date?
a) SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL;
b) TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL;
c) SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROMDUAL;
d) date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL

31. In which of the following clauses can a sub-query be used?

a) HAVING
b) WHERE
c) FROM
d) All of the above

32. What among the following is true about single-row sub-queries?

a) They return only one row
b) They use single-row operators
c) Both A and B
d) None of the above

33. Which of the following are DML commands in Oracle Database?

a) SELECT
b) GROUP BY
c) INTERSECT
d) INSERT

34. Which of the following best defines a transaction?

a) A transaction consists of DDL statements on the database schema
b) A transaction consists of COMMIT or ROLLBACK in a database session
c) A transaction consists of either a collection of DML statements or a DDL or DCL or TCL
statement to form a logical unit of work in a database session
d) A transaction consists of collection of DML and DDL statements in different sessions of
the database
35. What are Cartesian Joins also known as in Oracle DB?
a) Equi-join
b) Anti-join
c) Cross-Join
d) None of the above

36. In which of the following clauses can a sub-query be used?

a) HAVING
b) WHERE
c) FROM
d) All of the above

37. What among the following is true about single-row sub-queries?

a) They return only one row
b) They use single-row operators
c) Both A and B
d) None of the above

38. Which of the following are DML commands in Oracle Database?

a) SELECT
b) GROUP BY
c) INTERSECT
d) INSERT

39. Which of the following can be used to insert rows in tables?

a) SELECT
b) INSERT
c) Sub-queries
d) All of the above

40. Which of the following best defines a transaction?

a) A transaction consists of DDL statements on the database schema
b) A transaction consists of COMMIT or ROLLBACK in a database session
c) A transaction consists of either a collection of DML statements or a DDL or DCL or TCL
statement to form a logical unit of work in a database session
d) A transaction consists of collection of DML and DDL statements in different sessions of
the database

41. 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.
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;

43. Examine this statement:

SELECT student_id, GPA FROM student_grades WHERE GPA > &&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is produced.
What happens when you run the statement a second time?
a) An error is returned.
b) You are prompted to enter a new value.
c) A report is produced that matches the first report produced.
d) You are asked whether you want a new value or if you want to run the report based on
the previous value.

44. A data manipulation language statement _____.

a) completes a transaction on a table
b) modifies the structure and data in a table
c) modifies the data but not the structure of a table
d) modifies the structure but not the data of a table

45. View the image below and examine the data in the EMPLOYEES table.
Examine the subquery:
SELECT last_name
FROM employees
WHERE salary IN (SELECT MAX (salary)
FROM employees
GROUP BY department_id);
Which statement is true?
a) The SELECT statement is syntactically accurate.
b) The SELECT statement does not work because there is no HAVING clause.
c) The SELECT statement does not work because the column specified in the GROUP BY
clause is not in the SELECT list.
d) The SELECT statement does not work because the GROUP BY clause should be in the
main query and not in the subquery.

46. 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.
47. 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';

48. Evaluate this SQL statement:

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROMEMP e, DEPARTMENT 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

49. 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

50. Which two statements is 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.

51. 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.
52. Which operator can be used with a multiple row subquery?
a) =
b) LIKE
c) BETWEEN
d) NOT IN
e) Is
f) <>

53. 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

54. Which clause should you use to exclude group results?

a) WHERE
b) HAVING
c) RESTRICT
d) GROUP BY
e) ORDER BY

55. 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