SQL Certificate Mock Exams

1. What will be the outcome of the following query?

SELECT ROUND(144.23,-1) FROM dual;

140

144

150

100

2.In which of the following cases, parenthesis should be specified?

When INTERSECT is used with other set operators

When UNION is used with UNION ALL

When MINUS is used for the queries

None of the above

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

SELECT

GROUP BY

INTERSECT

INSERT

4. Write a query to display employee details (Name, Department, Salary and Job) from EMP table.

SELECT ename, deptno, sal, job FROM emp;

SELECT * FROM emp;

SELECT DISTINCT ename, deptno, sal, job FROM emp;

SELECT ename, deptno, sal FROM emp;

5.What among the following are different types of Views?

Simple views

Complex views

Both A and B

None of the above

6.What is true about the SET operators?

The SELECT clause should have the same number of columns, data types can be different

The SET operators can be used only for combining two queries

The data type of each column in the 2nd query must match the data type of its corresponding column in the first query.

None of the above

7.Which of the following multi-row operators can be used with a sub-query?

IN

ANY

ALL

All of the above

8. When a table can be created?

When the database is not being used by any user

When the database is newly created

It can be created any time, even when a user is using the database

None of the above

9. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer)

Using SELECT clause

Manually typing each value into the INSERT clause

Using SET operators

None of the above

10. What among the following is true about a View?

Sub-queries can be embedded in a CREATE VIEW statement

A sub-query used in the CREATE VIEW statement has to have a simple SELECT syntax

You cannot use a WHERE clause in a sub-query when it is used in the CREATE VIEW statement

None of the above

11. Predict the output when below statement is executed in SQL* Plus?

DESC emp

Raises error "SP2-0042: unknown command "desc emp" - rest of pne ignored."

Lists the columns of EMP table

Lists the EMP table columns, their data type and nulpty

Lists the columns of EMP table along with their data types

12. What will be the outcome of the query given below?

SELECT 100+NULL+999 FROM dual;

100

999

NULL

1099

13. With respect to the given query, if the JOIN used is replaced with NATURAL JOIN, it throws an error. What is the reason for this error?

When the NATURAL JOIN is used, a WHERE clause is mandatory, omitting which gives an error

The ON clause should be replaced with the USING clause

The words NATURAL, JOIN and USING are mutually exclusively in the context of the same join clause

A query can t combine the NATURAL JOIN and ON (or USING) clauses while joining.

14.Which of the following syntax models is used in extensively in the software systems worldwide?

ANSI SQL: 1999

Both traditional Oracle syntax and the ANSI SQL: 1999 syntax

Traditional Oracle syntax

All of the options

15.What is true about co-related sub-queries?

The tables used in the main query are also used in a co-related sub-query

The sub-queries which reference a column used in the main query are called co-related sub-queries

The sub-queries which are written without parenthesis are called co-related sub-queries

The sub-queries which mandatorily use different tables than those used in the main query are called co-related sub-queries

16. You issue an UPDATE statement as follows:

UPDATE employees 
SET employee_id   = NULL;
WHERE job_id  =  CLERK ;

What will be the outcome of the above statement? (Here the column EMPLOYEE_ID is marked as mandatory by putting a constraint)

The first column of the data set will get updated to NULL

The 3rd column of the first row will get updated to NULL

The 3rd column of all the rows will get updated to NULL

And ORA error will be thrown

17.What is true with respect to the query given above?

It gives an ORA error as the mandatory WHERE clause is not present

The JOIN..ON clause can t contain more than one condition

The query ignores the last condition and executes without an ORA error

The JOIN..ON clause can be written in the form given above for putting more conditions.

18. Consider the following query.

SELECT e.job_id , e.first_name, d.department_id 
FROM departments D JOIN employees e JOIN BONUS b
USING (job_id );

This query results in an error. What is the reason of the error?

A JOINOUSING can happen only between two tables at a time

USING clause in the query doesn t have any column from the department

There is no WHERE clause in the query

None of the above

19. Predict the output of the below query

SELECT 50 || 0001
FROM dual

500001

51

501

5001

20. You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script?

The table will not be created

The table will be created and an underscore will be added automatically to the name COUNT_

An ORA error will be thrown

The table COUNT will be created without any errors

21. What will be the outcome of the following query?

SELECT *
FROM employees
WHERE salary BETWEEN (SELECT max(salary)
			FROM employees
			WHERE department_id  = 100) 
AND (SELECT min(salary) FROM employees where department_id  = 100); 

This query returns an error. What is the reason for the error?

A GROUP BY clause should be used as the function MAX is used

Both the sub-queries cannot use the same department ID in the same outer query

BETWEEN operator cannot be used with a sub-query

SELECT clause should have columns mentioned and not a asterix (*)

22. Which of the following is not a property of functions?

Perform calculations on data

Convert column data types

Modify inspanidual data items

None of the above

23.What is true with respect to INNER JOINS and OUTER JOINS in Oracle DB?

INNER JOIN returns only the rows that are matched

OUTER JOIN returns only the rows that are not matched

OUTER JOIN returns the rows that are matched as well as those which do not match

None of the above

24. Which of the following can create a view even if the base table(s) does not exist?

NOFORCE

FORCE

OR REPLACE

CREATE VIEW

25. Which of the following ANSI SQL: 1999 join syntax joins are supported by Oracle?

Cartesian products

Natural joins

Full OUTER join

Equijoins

26. What among the following are the pre-requisites for creating a table?

CREATE TABLE privilege

Storage space

Data in the table

None of the above

27. What is the syntax for creating a table?

CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [,..] );

CREATE TABLE INTO [schema.] table (column datatype [DEFAULT expr] [,..] );

CREATE TABLE VALUES [schema.] table (column datatype [DEFAULT expr] [,..] );

None of the above

28.You need to display all the non-matching rows from the EMPLOYEES table and the non-matching rows from the DEPARTMENT table without giving a Cartesian product of rows between them. Which of the following queries will give the desired output?

SELECT *
FROM employees e, department d
WHERE e.department_id  = d.department_id ; 

SELECT *
FROM employees e NATURAL JOIN department d; 

SELECT *
FROM employees e FULL OUTER JOIN department d
ON  e.department_id  = d.department_id ; 

SELECT *
FROM employees e JOIN  department d
ON ( e.department_id  > d.department_id ) ; 

29. Which of the below alphanumeric characters are used to signify concatenation operator in SQL?

+

||

-

::

30.What is the best way to change the precedence of SET operators given the fact that they have equal precedence?

The order of usage of the SET operators can be changed to change the precedence

The equal precedence cannot be changed

Parenthesis can be used to change the precedence

None of the above

31.What will be displayed in the result of this query?

It will display distinct department id(s) contained jointly in EMPLOYEES and DEPARTMENTS table

It will throw ORA error

No rows selected

None of the above

32. Which of the following commands ensures that no DML operations can be performed on a view?

NOFORCE

FORCE

WITH READ ONLY

OR REPLACE

33. What is true about the NOFORCE option in CREATE VIEW statement?

It creates a view even if the base table(s) does not exist.

It creates a view only if the base table(s) exists.

It is the default while creating a view.

None of the above

34. What is true about the OR REPLACE keyword?

Object privileges are lost when a view is created using this keyword

There is no need of re granting the object privileges previously granted on it

Neither of A nor B

None of the above

35. What among the following is a type of Oracle SQL functions?

Multiple-row functions

Single column functions

Single value functions

Multiple columns functions

36. What among the following is a type of single-row function?

VARCHAR2

Character

LONG

NULLIF

37. What is the most appropriate about Multiple Row Functions?

They return multiple values per each row. 

They return one result per group of rows and can manipulate groups of rows. 

They return one result per row and can manipulate groups of rows. 

They return multiple values per a group of row.

38. Which of the following are also called Group functions?

Single row functions

Multi group functions

Multiple row functions

Single group functions.

39. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (*) FROM t_count;

12

6

9

Throws exception because COUNT function doesn t works with NULL values

40. Pick the element which you must specify while creating a table.

Column name

Column Data type

Column size

All of the above

41. What can be said about the statement given above?

Alternative names have been given for the view

Giving alternative names is mandatory if any column is derived from a function or an expression

Both A and B

None of the above

42. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (num) FROM t_count;

12

6

9

Throws exception because COUNT function doesn t works with NULL values

43. You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added / modified to the above query?

ON (e.department_id = d.department_id ) should be added

USING (e.department_id ) should be added

WHERE e.department_id in (100,101) should be added

None of the above

44. Which of the following is NOT a GROUP BY extensions in SQL?

GROUP BY

GROUPING SETS

CUBE

ROLLUP

45. What will happen if the above statement is modified as below?

CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal)
AS 
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name; 

It will be no different than the original statement

It will execute successfully giving the same results but change in apas names.

It will throw an ORA error

None of the above

46. What among the following is true about the DELETE statement?

The DELETE statement has to be accompanied by the WHERE clause

It is not mandatory to write a WHERE clause with the DELETE statement

DELETE can remove data from multiple tables at a time

None of the above

47. Assuming the last names of the employees are in a proper case in the table employees, what will be the outcome of the following query?

SELECT employee_id, last_name, department_id  FROM employees WHERE last_name =  smith ;

It will display the details of the employee with the last name as Smith

It will give no result.

It will give the details for the employee having the last name as Smith in all Lower case.

It will give the details for the employee having the last name as Smith in all INITCAP case.

48.What among the following happens when we issue a DELETE statement on a table? (Choose the most appropriate answer)

A prompt pops up asking the user whether he/she is sure of deleting the rows requested

The rows obeying the condition given in the DELETE statement are removed immediately

The requested rows are removed immediately without any prompt.

None of the above

49.What is true about the query given above?

This query returns an ORA error

It executes successfully but gives no results

Queries from different tables cannot be used with the SET operators

The query executes successfully and gives the results as expected

50.What will happen if a value is provided to the &N variable in the above query (option C in question 76) does not match with any row? (Choose the best answer)

The statement would throw an ORA error

The statement would return all the rows in the table

The statement would return NULL as the output result.

The statement would return no rows in the result.

51.What is the default sorting order of the results when UNION ALL operator is used?

Descending

Ascending

Either A or B

All of the above

52. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (ALL num) FROM t_count;

12

6

9

Throws exception because COUNT function doesn t works with NULL values

53.What is the maximum level up to which Sub-queries can be nested?

255

100

2

16

54. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (DISTINCT num) FROM t_count;

12

6

9

Throws exception because COUNT function doesn t works with NULL values

55. Here are few statements about VARIANCE function in SQL.

i. The function accepts multiple numeric inputs and returns variance of all the values

ii. The function accepts a number column and returns variance of all column values including NULLs

iii. The function accepts a number column and returns variance of all column values excluding NULLs

Chose the correct combination from the below options.

i and iii

i and ii

ii

iii

56. Which clause is used to filter the query output based on aggregated results using a group by function?

WHERE

LIMIT

GROUP WHERE

HAVING

57. A user named "Kevin" wants to access a table which is owned by another user named "Jonathan". Which of the following will work for Kevin?

Select * from Kevin.employees;

Select * from jonathan.employees;

Either of A or B

None of the above

58.What is true about the ALL operator used for sub-queries? (Choose the most appropriate answer.)

Returns rows that match all the values in a pst/sub-query

Returns rows that match only some values in a pst/sub-query

Returns rows only if all the values match in a pst/sub-query

All of the above

59. Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

The rows will get removed without any error

Only the first 10 rows will get removed

The rows cannot be deleted.

None of the above

60.What will happen if the SELECT pst of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

Oracle will convert them imppcitly and return a VARCHAR2 data type result

Oracle will convert them imppcitly and return a NUMBER data type result

An ORA error is thrown

None of the above

61. What is true about a schema?

A schema is owned by a database user and has the same name as that user

Each user owns a single schema

Schema objects include database pnks

All of the above

62. In which order the values will get inserted with respect to the above INSERT statement?

Location_id , manager_id, department_name , department_id

department_id , department_name , manager_id, location_id

department_id , manager_id, department_name , location_id

department_id , department_name , location_id , manager_id

63. What among the following is true about tables?

A default value is given to a table

A default value can be given to a column of a table during an INSERT statement

Either of A or B

None of the above

65. Which of the below SQL query will display employee names, department, and annual salary?

SELECT ename, deptno, sal FROM emp;

SELECT ename, deptno, sal + comm FROM emp;

SELECT ename, deptno, (sal * 12) Annual_Sal FROM emp;

Annual salary cannot be queried since the column doesn t exists in the table

66. What is true about the SUBSTR function in Oracle DB?

It extracts a string of determined length

It shows the length of a string as a numeric value

It finds the numeric position of a named character

It trims characters from one (or both) sides from a character string

67. Which of the following SELECT statements psts the highest retail price of all books in the Family category?

SELECT MAX(retail) FROM books WHERE category =  FAMILY ;

SELECT MAX(retail) FROM books HAVING category =  FAMILY ;

SELECT retail FROM books WHERE category =  FAMILY  HAVING MAX(retail);

None of the above

68. Which of the following functions can be used to include NULL values in calculations?

SUM

NVL

MAX

MIN

69.Which statements best describes the inference drawn from the questions 34 and 35?

There are duppcate values for job codes

The query executes but results produced are unexpected

There are no duppcate values for departments

None of the above

70. What will be the outcome of the following query?

SELECT length( hi ) FROM dual;

2

3

1

hi

Answer:

Answer(1): A. The ROUND function will round off the value 144.23 according to the specified precision -1 and returns 140.

Examine the structure of the EMPLOYEES table as given and answer the questions 2 and 3 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

Answer(2): A. Using parenthesis will exppcitly change the order of evaluation when INTERSECT is used with other operators.

Answer(3): A, D. On strict grounds, SELECT is a DML command as it is one of the mandatory clauses for manipulation of data present in tables.

Answer(4): A.Select the required from the tables each separated by a comma.

Answer(5): C. Simple and Complex views are two types of views. Simple views are based on a subquery that references only one table and doesn t include group functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

Answer(6): C. All the combined should have the same no. of columns when using SET operators. The corresponding columns in the queries that make up a compound query must be of the same data type group.

Answer:(7) D. Multiple-row subqueries return more than one row of results.Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.

Answer(8): C. An index can be created to speed up the query process. DML operations are always slower when indexes exist. Oracle 11g creates an index for PRIMARY KEY and UNIQUE constraints automatically. An exppcit index is created with the CREATE INDEX command. An index can be used by Oracle 11g automatically if a query criterion or sort operation is based on a column or an expression used to create the index.

Answer(9): A. Using the SELECT clause is the most common technique for inserting rows into tables. It reduces the effort of manually keying in values for each column.

Answer(10): A. View definition can make use of sub-queries.

Answer(11): C. DESCRIBE is used to show the table structure along with table columns, their data type and nulpty

Answer(12): C. Any arithmetic operation with NULL results in a NULL.

Answer()13: C, D.

Answer(14): C. The ANSI SQL: 1999 syntax though not used as much as the traditional Oracle syntax, it still is one of the syntaxes that may be used in Oracle SQL

Answer(15): B. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.

Answer(16): D. The constraints on the column must be obeyed while updating its value. In the given UPDATE statement, error will be thrown because the EMPLOYEE_ID column is a primary key in the EMPLOYEES table which means it cannot be NULL.

Answer(17): D. The WHERE clause can be omitted and the relevant conditions can be accommodated in the JOIN..ON clause itself as shown in the given query

Answer(18): A. Table1 JOIN table2 JOIN table3 is not allowed without the ON clauses for between each JOIN

Answer(19): C. The leading zeroes in the right operand of expression are ignored by Oracle.

Answer(20): A, C. You cannot create a table with the name same as an Oracle Server reserved word.

Answer(21): C. The BETWEEN operator can be used within a sub-query but not with a sub-query.

Answer(22): D. Functions can perform calculations, perform case conversions and type conversions.

Answer(23): A, C. A join can be an inner join,in which the only records returned have a matching record in all tables,or an outer join, in which records can be returned regardless of whether there s a matching record in the join.An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they re included in the output.

Answer(24): B. Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be vapd, or the view isn t created.

Answer(25): D.

Answer(26): A, B. A user must possess the CREATE TABLE privilege and must have sufficient space to allocate the initial extent to the table segment.

Answer(27): A.

Answer(28): C. The FULL OUTER JOIN returns the non-matched rows from both the tables. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

Answer(29): B.In SQL, concatenation operator is represented by two vertical bars (||).

Answer(30): C. Parenthesis can be used to group the specific queries in order to change the precedence exppcitly. Parentheses are preferred over other SET operators during execution.

Answer(31): A. UNION Returns the combined rows from two queries, sorting them and removing duppcates.

Answer(32): C. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it s important that users can only query data, not make any changes to it.

Answer(33): B, C. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be vapd, or the view isn t created.

Answer(34): B. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view s previous version should be replaced with the one defined in the new command.

Answer(35): A. There are basically two types of functions - Single row and Multiple row functions.

Answer(36): B. Character, Date, Conversion, General, Number are the types of Single row functions.

Answer(37): B. Multiple Row functions always work on a group of rows and return one value per group of rows.

Answer(38): C. Group functions are same as Multi row functions and aggregate functions.

Answer(39): A. The COUNT(*) counts the number of rows including duppcates and NULLs. Use DISTINCT and ALL keyword to restrict duppcate and NULL values.

Answer(40): D. A table must have atleasr one column, its data type specification, and precision (if required).

Answer(41): C. Specifying apas name is good practice to improve the readabipty of the code and the view queries.

Answer(42): C. COUNT (column) ignores the NULL values but counts the duppcates.

Answer(43): C. The NATURAL JOIN clause imppcitly matches all the identical named columns. To add additional conditions the WHERE clause can be used.

Answer(44): A. GROUPING SETS operations can be used to perform multiple GROUP BY aggregations with a single query.

Answer(45): B. The sequence of the column apas not matters much as they don t carry any behavioral attribute.

Answer(46): B. The WHERE clause predicate is optional in DELETE statement. If the WHERE clause is omitted, all the rows of the table will be deleted.

Answer(47): B. Provided the last names in the employees table are in a proper case, the condition WHERE last_name = smith will not be satistified and hence no results will be displayed.

Answer(48): C. As a part of the active or a new transaction, the rows in the table will be deleted.

Answer(49): D. A compound query is one query made up of several queries using different tables.

Answer(50): D.

Answer(51): B. A compound query will by default return rows sorted across all the columns,from left to right in ascending order.The only exception is UNION ALL, where the rows will not be sorted. The only place where an ORDER BY clause is permitted is at the end of the compound query.

Answer(52): C. COUNT(ALL column) ignores the NULL values but counts the duppcates.

Answer(53): A.

Answer(54): B. COUNT (DISTINCT column) counts the distinct not null values.

Answer(55): C. The VARIANCE function accepts single numeric argument as the column name and returns variance of all the column values considering NULLs.

Answer(56): D. HAVING Clause is used for restricting group results. You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is appped to the groups in the SELECT pst.

Answer(57): B.

Answer(58): C. > ALL More than the highest value returned by the subquery. < ALL Less than the lowest value returned by the subquery. < ANY Less than the highest value returned by the subquery. > ANY More than the lowest value returned by the subquery. = ANY Equal to any value returned by the subquery (same as IN). [NOT] EXISTS Row must match a value in the subquery.

Answer(59): C. The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.

Answer(60): C. Oracle does not convert data types imppcitly.

Answer(61): D. The user space in a database is known as schema. A schema contains the objects which are owned or accessed by the user. Each user can have single schema of its own.

Answer(62): B. If the columns are mentioned in the INSERT clause, the VALUES keyword should contain values in the same order

Answer(63): B. A default value can be specified for a column during the definition using the keyword DEFAULT.

Answer(65): C. Use numeric expressions in SELECT statement to perform basic arithmetic calculations.

Answer(66): A. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

Answer(67): A. Since the category FAMILY has to be restricted before grouping, table rows must be filtered using WHERE clause and not HAVING clause.

Answer(68): B. NVL is a general function to provide alternate values to the NULL values. It can really make a difference in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.

Answer(69): C. As the combination of the job codes and departments is unique, there are no duppcates obtained.

Answer(70): A. the LENGTH function simply gives the length of the string.