Author: alien

Khóa học miễn phí SQL – Using the Set Operators nhận dự án làm có lương
SQL – Using the Set Operators Questions
1.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

MINUS

Answer: A. Set operators are used to combine the results of two (or more) SELECT statements.Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS. When used with two SELECT statements, the UNION set operator returns the results of both queries.However,if there are any duplicates, they are removed, and the duplicated record is listed only once.To include duplicates in the results,use the UNION ALL set operator.INTERSECT lists only records that are returned by both queries; the MINUS set operator removes the second query”s results from the output if they are also found in the first query”s results. INTERSECT and MINUS set operations produce unduplicated results.

2.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

MINUS

Answer: B. UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.
sql_certificate
3.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

MINUS

Answer: C. INTERSECT Returns only the rows that occur in both queries” result sets, sorting them and removing duplicates.

4.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

MINUS

Answer: D. MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

5.What is true about SET operators?

They change values of rows

They combine the results of only two component queries into one result

They combine the results of 10 component queries into two result sets.

They combine the results of two or more component queries into one result

Answer: D. Set operators are used to combine the results of two (or more) SELECT statements. Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS.

6.What are the queries containing SET operators called?

Sub-queries

Co-related sub-queries

GROUP BY queries

Compound queries

Answer: D.

7.What is true about the UNION operator?

It returns rows from the combined queries along with NULL values

It returns rows for the combined queries after eliminating duplicates

It returns rows for the combined queries along with duplicate values

It returns rows for the combined queries ignoring the NULL values

Answer: B. UNION Returns the combined rows from two queries, sorting them and removing duplicates.

8.What is true about the UNION ALL operator?

It returns rows from the combined queries along with NULL values

It returns rows for the combined queries after eliminating duplicates

It returns rows for the combined queries along with duplicate values

It returns rows for the combined queries ignoring the NULL values

Answer: C. UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

9.What is true about the INTERSECT operator?

It returns rows from the combined queries along with NULL values

It returns rows for the combined queries after eliminating duplicates

It returns the common rows from the combined queries

None of the above

Answer: C. INTERSECT Returns only the rows that occur in both queries” result sets, sorting them and removing duplicates.

10.What is true about the MINUS operator?

It returns rows from the first query but not from the second query

It returns rows for the second query but not from the first query

It returns duplicate rows for the combined queries

It returns rows for the combined queries ignoring the NULL values

Answer: A. MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

11.What is the precedence of the set operators UNION, UNION ALL, INTERSECT and MINUS?

UNION, UNION ALL, INTERSECT and MINUS

MINUS, UNION, UNION ALL and INTERSECT

INTERSECT, MINUS, UNION ALL, UNION

Equal precedence

Answer: D. SET operators have an equal precedence.

12.What is the order of evaluation of set operators?

Left to Right

Right to Left

Random Evaluation

Top to Bottom

Answer: A, D. Assuming that there are no grouping of queries using parentheses, the SET operators will be evaluated from top to bottom and left to right horizontally.

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

Answer: A. Using parenthesis will explicitly change the order of evaluation when INTERSECT is used with other operators.

14.What is true about the SELECT clause when SET operators are used?

There is no restriction on the columns being selected

The columns, expressions used in the SELECT clause must match in number in the combined queries

The columns, expressions used in the SELECT clause must be N in the first query and N-1 in the subsequent combined queries

Both B and C

Answer: B. 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.

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

Answer: 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.

16.Where can the ORDER BY clause be used in case when SET operators are used?

In each of the queries being combined

In the first query only

At the very end of the compound query

None of the above

Answer: C. If the ORDER BY clause is used in between any of the queries joined using SET operators, it will throw an ORA error.

17.What is true about the queries that have SET operators in their WHERE clause?

These queries must have the same no. and data type of columns in their SELECT clause.

The no. of columns used in the WHERE clause query and the main SELECT can be different

The no. of columns used in the WHERE clause should be the same, the data type can be different

None of the above

Answer: A. 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.

18.What is true about the columns in the second query with respect to the columns in the first query?

The column in the 2nd query must be in the same data type group as the corresponding column in the 1st query

If a column in the 1st query is a NUMBER, the corresponding column in the 2nd query should be a VARCHAR2

If a column in the 1st query is a NUMBER, the corresponding column in the 2nd query should be also be NUMBER.

None of the above

Answer: A, C.

19.What among the following is true about SET operators?

SET operators cannot be used in sub-queries

SET operators can only be used in the WHERE clause

ORDER BY can be used for all queries combined by a SET operator

SET operators can be used in sub-queries

Answer: D.

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

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

21.What can be said about duplicate values and SET operators?

No SET operator displays duplicate values

All SET operators can display duplicate values

Only UNION ALL operator displays duplicate values

None of the above

Answer: C. UNION, INTERSECT and MINUS automatically eliminate duplicate values

Examine the structure of the EMPLOYEES and DEPARTMENTS tables and consider the following query and answer the questions 22 and 23.

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT department_id FROM employees e UNION SELECT department_id FROM departments

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

Answer: A. UNION Returns the combined rows from two queries, sorting them and removing duplicates.

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

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

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

Answer: 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.

25.What will be the output of the compound query in which columns in the SELECT are of CHAR and equal length?

The output will have VARCHAR2 data type of equal length

The output will have CHAR data type of equal length

The output will have CHAR data type of different lengths

The output will have NUMBER data type of equal length

Answer: B. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query. The corresponding columns in the queries that make up a compound query must be of the same data type group.

26.What will be the output of the compound query in which columns in the SELECT are of CHAR and different lengths?

The output will have VARCHAR2 data type of equal length

The output will have CHAR data type of equal length

The output will have CHAR data type of different lengths

The output will have VARCHAR2 data type with the length of the larger CHAR value

Answer: D. While the selected column lists do not have to be exactly the same data type, they must be from the same data type group. The result set of the compound query will have columns with the higher level of precision.

27.What will be the output of a compound query if either or both queries select values of VARCHAR2?

The output will have VARCHAR2 data type.

The output will have CHAR data type of equal length

The output will have CHAR data type of different lengths

The output will have VARCHAR2 data type with the length of the larger CHAR value

Answer: A. While the selected column lists do not have to be exactly the same data type, they must be from the same data type group. The result set of the compound query will have columns with the higher level of precision.

28.What is true if the compound queries select numeric data?

There will be an equal precedence of the numeric values, operators

The return values will be determined by the numeric precedence

The return values will be of NUMBER data type

None of the above

Answer: B, C. While the selected column lists do not have to be exactly the same data type, they must be from the same data type group. The result set of the compound query will have columns with the higher level of precision.

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

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

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

An ORA error is thrown

None of the above

Answer: C. Oracle does not convert data types implicitly.

30.What is true about the UNION operator?

It eliminates the duplicate values ignoring NULL values

It returns duplicate values ignoring NULL values

It returns duplicate values including NULL values

It eliminates duplicate values and does not ignore NULL values

Answer: D. NULL values are not ignored when the UNION operator is used

31.What can be said about the names and columns of a SQL query which uses the UNION operator?

The names of the columns should be identical

The names and data type of the columns should be identical

The names of the columns need not be identical

None of the above

Answer: C. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

Consider the following exhibit of the JOB_HISTORY table and the query that follows. Answer the questions 32 and 33 below the query.

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id , first_name, last_name, job_id FROM employees E UNION SELECT employee_id , first_name, last_name, job_id From job_history;

32.How many times the each employee will get displayed by the above query?

0

1

2

4

Answer: B. UNION Returns the combined rows from two queries, sorting them and removing duplicates.

33.What will be the outcome of the above query?

It displays the current and previous job details of the employees twice

It displays the current and previous job details of the employees only once

Either A or B

None of the above

Answer: B.

Examine the given table structures and consider the following query and answer the questions 34 to 37 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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id , job_id, department_id FROM employees UNION SELECT employee_id , job_id, department_id From job_history;

34.Assuming that an employee with ID 121 has held 2 job IDs in his tenure in the company. Considering the above query, how many times will his records be displayed in the results?

Once

Twice

Thrice

None of the above

Answer: B. UNION Returns the combined rows from two queries, sorting them and removing duplicates. Duplicity is measured by the combination of columns and not the individual column separately.

35.Assuming that the employee with ID 121 held two positions in two different departments – 10 and 20 in the company.He worked as ”SA_REP” in both the departments 10 and 20. What will be the outcome of the above query ?

2 rows

3 rows

No rows

ORA error

Answer: B.

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

There are duplicate values for job codes

The query executes but results produced are unexpected

There are no duplicate values for departments

None of the above

Answer: C. As the combination of the job codes and departments is unique, there are no duplicates obtained.

37.What will be the sorting in the result set obtained by the query?

Descending on Employee ID

Descending on Job ID

Ascending on Employee ID

Ascending on Department ID

Answer: C. The default sorting will be ascending based on the first column i.e.: Employee ID in this case.However, this behavior can be modified by placing a single ORDER BY clause at the end.

38.Which of the following operators will be used to obtain duplicate records from the component queries?

UNION

UNION ALL

MINUS

None of the above

Answer: B. UNION ALL doesn”t eliminates the duplicate values.

39.What is the difference between the UNION and the UNION ALL operators?

There is no difference

UNION ALL displays duplicate values too

The output in the case of UNION ALL is not sorted by default

None of the above

Answer: B, C. When used with two SELECT statements, the UNION set operator returns the results of both queries. However, if there are any duplicates, they are removed, and the duplicated record is listed only once. To include duplicates in the results, use the UNION ALL set operator

40.What is true about the INTERSECT operator?

The number of columns and data types of the columns in the component queries should be the same

The names of the columns and data types of the columns in the component queries should be the same

Both A and B

None of the above

Answer: A. This is common property criteria feature of SET operators.

41.What can be said about the result set if the order of the intersected tables is altered when using INTERSECT?

The result is altered

The result remains the same

The sorting changes on alteration

None of the above

Answer: B.

42.What among the following is true about the INTERSECT operator?

It ignores NULL values

It does not ignore NULL values

It returns all the rows from the first component query

None of the above

Answer: B.

Answer the related questions 43 and 44 given below.

43.You need to display the names and job IDs of those employees who currently have a job title that is the same as their previous one. Which of the following queries will work? (Consider the table structures as given)

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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id , job_id, first_name, last_name FROM employees UNION SELECT employee_id , job_id, first_name, last_name FROM job_history;

SELECT employee_id , job_id, first_name, last_name FROM employees INTERSECT SELECT employee_id , job_id, first_name, last_name FROM job_history;

SELECT employee_id , job_id, first_name, last_name FROM employees UNION ALL SELECT employee_id , job_id, first_name, last_name FROM job_history;

None of the above

Answer: B.

44.Considering the above query i.e. Option B in question 43, what will be the result if the department ID is also included in the SELECT clause?

The result will be the same

The result will be different

The result will be the same but the order will be different

None of the above

Answer: A. The result can be interpreted as – the employees who have worked with the same job title in the same department.

45.What is true about the MINUS operator?

It returns all the rows from all the component queries

It returns only the common rows from all the component queries

It returns all the rows from the first query and not from the subsequent queries

It returns all distinct rows selected by the first query, but not present in the subsequent queries

Answer: D. MINUS set operator removes the second query”s results from the output if they are also found in the first query”s results

46.What can be said regarding the number of columns and data types of the component queries when a MINUS operator is used?

They should be the same, the data type might be different but they should belong to the same data type group.

They should be the same along with the names of the columns

Both A and B

None of the above

Answer: A. Common feature of SET operators.

47.You need to display the employee IDs of the employees who have not changed their jobs even once during tenure in the company. Which of the following queries will be correct in this case? (Consider the table structures as given)

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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id FROM employees UNION SELECT employee_id FROM job_history;

SELECT employee_id FROM employees INTERSECT Select employee_id FROM job_history;

SELECT employee_id FROM employees MINUS Select employee_id FROM job_history;

SELECT employee_id FROM employees UNION ALL SELECT employee_id FROM job_history;

Answer: C.

Examine the given table structures and consider the following query answer the questions 48 and 49 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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id , first_name, job_id FROM employees UNION SELECT employee_id , NULL "first_name", job_id FROM job_history;

48.What is true about the above query?

It throws an error as TO_CHAR (NULL) cannot be used

It executes successfully and gives the values for employees” id, first_name and current job role including duplicate rows

It executes successfully and gives the values for employees” id, first_name and all jobs held by the employees excluding duplicate rows

None of the above

Answer: C. Each query must contain the same number of columns, which are compared positionally. NULL can be substituted in place of column which is missing in the other query within the same compound query.

49.Considering the above query, if the UNION operator is replaced by the MINUS operator, what will the result mean?

The result shows those employees who have an entry in the JOB_HISTORY table

The result shows those employees who do not have an entry in the JOB_HISTORY, but they are present in the EMPLOYEES table

Either of A or B

None of the above

Answer: B. MINUS gives the unique results that are present in the first query but not the second query.

Consider the exhibit given below and answer the questions 50 and 51 that follow:

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

SELECT AU_DETAILS FROM AUDIT UNION SELECT AU_DETAILS FROM AUDIT_YEARLY;

It executes successfully giving the correct results including the duplicate values

It executes successfully giving the correct results excluding the duplicate values

It throws an ORA error

None of the above

Answer: C. CLOB or LONG columns cannot be in the SELECT clause when using the UNION set operators.

51.What will be the outcome of the query if UNION is replaced with UNION ALL?

It will execute successfully giving the correct results including duplicate values

It throws an ORA error

It will execute successfully giving the correct results excluding duplicate values

It executes successfully but gives the incorrect results.

Answer: B. .UNION, UNION ALL, INTERSECT and MINUS operators when used with a LONG or CLOB column throws error.

52.Assume that there are 4 component queries. How many SET operators can be used to combine them in a single compound query?

1

2

4

3

Answer: D. The SET operator to be used will are N-1 where N is the number of component queries.

53.What are SET operators called owning to the fact that two or more SELECTs are involved based on columns instead of rows when SET operators are used?

Horizontal joins

Cartesian Joins

Vertical joins

Outer joins

Answer: C.

54.What is the difference between a UNION and INTERSECT operators? (Choose only the best difference)

UNION combines the results of two component queries into one result set with duplicate values

INTERSECT returns only those rows that are returned by each of the two component queries

UNION gives the distinct values from the component queries, INTERSECT gives the common values from the component queries

Both B and C

Answer: C.

Examine the structure of the EMPLOYEES table and consider the following query. Answer the questions 55 to 60 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)
Query 1
SELECT * FROM EMPLOYEES where department_id = 10
Query 2
SELECT * FROM EMPLOYEES E where E.job_id IN (select first_name from EMPLOYEES E1 where E1.job_id = ''CLERK'' and E.job_id = E1.job_id )

55.You need to extract a report where the results from both the queries are displayed. Which of the following operators should be used to get the required results?

UNION

UNION ALL

INTERSECT

None of the above

Answer: B. UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

56.You need to display all the duplicate values along with all the values existing in the result set from both the queries. Which of the following SET operators you can use in the above given queries?

INTERSECT

UNION

MINUS

None of the above

Answer: D. UNION ALL will give the unsorted results with duplicates.

57.What is the difference between the result sets when using a UNION and a UNION ALL set operators?

Result set from UNION ALL is filtered including duplicate values

Result set from UNION is filtered and sorted including duplicate values

Result set from UNION ALL is not sorted and it has duplicate values

Result set from UNION is filtered and sorted without duplicate values

Answer: C, D.

58.The UNION operator has more overhead on the database than the UNION ALL. What is wrong in this statement?

The statement is correct

UNION ALL operator has more overhead on the Data base than the UNION operator

UNION has to sort and eliminate duplicates which results into additional overhead

None of the above

Answer: A, C. UNION has to perform more tasks than UNION ALL because it sorts and deduplicates the result sets. Hence it is recommended that unless distinct rows are required, UNION ALL should be used.

59.What will be the outcome if the two queries given above are combined using the INTERSECT operator?

It will display only those employees who are Clerks in the Department 10

It will display all those employees who are in the department 10

It will display all the Clerks.

None of the above

Answer: A. INTERSECT returns those records that are present in query 1 AND query 2.

60.What among the following is the difference between the INTERSECT and the UNION operators?

INTERSECT follows the ”AND” Boolean logic, UNION follows the ”OR” Boolean logic

UNION follows the ”OR” Boolean logic, whereas INTERSECT follows the ”AND” logic

Either of A or B

None of the above

Answer: A.

61.In which of the following SET operators, changing the order of the component queries will change the result set?

UNION

UNION ALL

MINUS

INTERSECT

Answer: C. MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

Consider the following query and answer the questions 62 to 66 that follow:

SELECT 4 from dual INTERSECT SELECT 1 from dual;

62.What will be the outcome of the given query?

No rows

4

1

NULL

Answer: A. No rows will be selected as the INTERSECT operator will not get any common results from both the queries – INTERSECT operators gives common results present in query 1 AND query 2.

63.What will be the outcome of the query if the INTERSECT operator is replaced with MINUS operator?

3

4

0

1

Answer: B. MINUS gives results that are present in the first query and not present in the second query.

64.What will be the outcome of the above query if the INTERSECT operator is replaced with the UNION operator?

1
4

4
1

NULL

0

Answer: A. UNION will produce distinct rows in the result set in ascending order.

65.What will be the outcome of the above query if the INTERSECT operator is replaced with the UNION ALL operator?

4
1

0

NULL

1
4

Answer: A. UNION ALL displays the results as they are positioned in the query without sorting them.

66.What will be the outcome if the above query is modified as below?

SELECT 1 from dual UNION ALL SELECT 4 from dual;

1
4

4
1

NULL

None of the above

Answer: A.

Examine the JOB_HISTORY_ARCHIVE table structure. It is a backup table for the JOB_HISTORY table with no additional column. Assuming that both the table have dissimilar data, consider the query given below and answer the questions 67 to 70 that follow:

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

(SELECT * FROM job_history; MINUS SELECT * FROM job_history_archive) UNION ALL (SELECT * FROM job_history_archive MINUS SELECT * FROM job_history;);

67. What will be the outcome of the query given above? (Choose the best answer)

It will return those rows that are different in the two tables

It will return the common rows in the two tables

It will return all the rows from the two tables

None of the above

Answer: A.

68.What can concluded if the above given query yields rows only from JOB_HISTORY table?

It shows that the JOB_HISTORY contains two rows different from JOB_HISTORY_ARCHIVE table

It shows that two rows are same in JOB_HISTORY and JOB_HISTORY_ARCHIVE tables

It shows that the JOB_HISTORY_ARCHIVE contains two rows different from JOB_HISTORY table

None of the above

Answer: A.

69.What can be said if the above query gives no results?

It shows that the two tables have same data

It shows that the component queries are wrongly placed

It shows that the SET operators are wrongly used in the compound query

None of the above

Answer: A.

70.With respect to the query given above, if duplicate records exist in the two tables, which of the following modifications should be made to the above given query?

COUNT(*)

COUNT(*) and GROUP BY employee_id

COUNT (*) and ORDER BY employee_id

None of the above

Answer: B. COUNT(*) can be used to see the difference between the tables.

Consider the following query:

SELECT 1 NUM, ''employee'' TEXT FROM dual UNION SELECT TO_CHAR(NULL) NUM, ''departments'' TEXT FROM dual;

71.What will be the outcome of the query given above?

NUM TEXT ---------- ----------- 1 employee departments

NUM TEXT ---------- ----------- 1 employee NULL departments

ORA error

NUM TEXT ---------- ----------- departments 1 employee

Answer: C. Here the numeric 1 is compared to a character NULL which throws the error “ORA-01790: expression must have same datatype as corresponding expression”.

Consider the following query and answer the questions 72 and 73 that follow:

SELECT months_between (sysdate, to_date(''21-MAY-2013'',''DD-MON-YYYY'')) FROM dual UNION SELECT TO_date(NULL) NUM FROM dual;

72.What will be the outcome of the query given above? (Assume that the SYSDATE is 1st July, 2013)

It executes successfully with correct results

It executes successfully but with no results

It throws an ORA error

None of the above

Answer: C. NUMBER and DATE do not belong to same data type fail. Here a number obtained by MONTHS_BETWEEN is compared with a DATE and hence the error.

73.Assume that the SELECT statement in the 2nd query is modified as below:

SELECT to_number (NULL) NUM FROM dual;

What will be the outcome because of this change?

It executes successfully with correct results

It executes successfully but with no results

It throws an ORA error

None of the above

Answer: A.

74.Examine the table structures and consider the following query:

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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id "Employee ID" FROM employees UNION SELECT employee_id "EMP ID" FROM job_history;

Which of the below column headings will display in the result set?

EMP ID

Employee ID

EMPLOYEE_ID

ORA error because the column names must be same in the component queries.

Answer: B. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

Examine the two table structures given and consider the following query and answer the questions 75 and 76 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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id FROM employees e UNION SELECT employee_id FROM job_history j ORDER BY j.employee_id ;

75.What will be the outcome of the query given above?

The results will be ordered by the employee ID from the JOB_HISTORY table

The results will be ordered by the employee ID from the EMPLOYEES table

There will be no ordering of the results

ORA error

Answer: D. The ORDER BY should be done based on the names of the columns from the first query and not from the 2nd query columns.

76.Which of the following ORDER BY clauses can replace the erroneous ORDER BY in the query given above?

ORDER BY e.employee_id

ORDER BY j.2

ORDER BY 1

None of the above, ORDER BY is not allowed in the query

Answer: C. This is a more generic specification and Oracle will order based on the first column of the first query.

77.Consider the following exhibit and answer the question below:

SELECT au_doc From audit UNION SELECT au_doc From audit_yearly;

What will be the outcome of the above given query?

It gives the Audit documents between the two tables

It gives an ORA error on execution

It gives the Audit documents from the table AUDIT

None of the above

Answer: B. LONG columns cannot be used with SET operators.

78.Consider the query given below:

SELECT col_1 From TABLE (package1.proc1) UNION SELECT col_1 From TABLE (package2.proc2);

What will be the outcome of the query given above?

It executes successfully with duplicates

It executes successfully without duplicates

It throws an ORA error

None of the above

Answer: C. TABLE expressions cannot be used with SET operators.

Examine the two table structures given and consider the following query. Answer the questions 79 and 80 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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT employee_id , job_id FROM employees E UNION SELECT employee_id , job_id FROM job_history J FOR UPDATE OF job_id;

79.What happens when the query is executed?

ORA error

Employee_id and job_id

Employee_id

None of the above

Answer: A. The FOR UPDATE clause cannot be used with the query combined using the SET operators.

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

SELECT * from employees UNION SELECT job_id FROM job_history;;

It will give all the columns from the employees tables and only the job_id column from the job_history table

It will throw an error as the number of columns should match in the component queries

Neither B or C

None of the above

Answer: B.

81.If UNION, UNION ALL, INTERSECT are used in one SQL statement which of the following is true regarding the SQL statement?

UNION, UNION ALL will be executed first and then the result set will go for the INTERSECT statement.

The execution of INTERSECT will precede the UNION and UNION ALL execution.

The execution will be done from right to left taking into consideration all the operators at the same time.

The execution will be done from left to right taking into consideration all the operators at the same time.

Answer: D.

82.Consider the query given below and answer the question that follow:

SELECT ''3'' FROM dual INTERSECT SELECT 3f FROM dual;

What is true regarding the execution of the query given above?

It executes successfully.

It throws an error

It gives the result 3.

It gives the result 3f

Answer: B. Character literals must be enclosed within single quotes.

83.Which of the following is false for set operators used in SQL queries?

The set operators are valid when used on columns with the LONG datatype.

The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.

In order for the select query containing an expression, a column alias should be provided in order to refer it to the order_by_clause.

You cannot use these operators in SELECT statements containing TABLE collection expressions.

Answer: A. SET operators are unsupported for LONG, CLOB and BLOB data types.

84.Examine the given table structure and evaluate the following SQL statement:

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)

SELECT employee_id , last_name "Last Name" FROM employees WHERE department_id = 100 UNION SELECT employee_id EMPLOYEE_NO, last_name FROM employees WHERE department_id = 101;

Which ORDER BY clauses are valid for the above query? (Choose all that apply.)

ORDER BY 2,1

ORDER BY EMPLOYEE_NO

ORDER BY 2, employee_id

ORDER BY “EMPLOYEE_NO”

Answer: A, C. The ORDER BY clause must reference column by its position or the name referred by the first query.

85.Which of the following clauses would you use to exclude the column from the 2nd query out of the two queries combined using SET operators?

GROUP BY

ORDER BY

MINUS

UNION

Answer: C.

86.Examine the given table structure as given. What will be the outcome of the below query?

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)

SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG (salary) FROM employees GROUP BY department_id ) UNION SELECT * FROM employees WHERE salary > ANY (SELECT MAX (salary) FROM employees GROUP BY department_id );

It will display all the department IDs which have the average salaries and the maximum salaries

It will throw an ORA error as the no. of columns selected in both the query is different

It will display all the department IDs which have the average salaries

It will display all the department IDs which have the maximum salaries

Answer: B. The no. of columns should be the same.

87.What among the following is true about the UNION operator?

UNION operates over only the first column in the SELECT list

UNION operates over the first columns of the SELECT lists in the component queries

UNION operates over all the columns being selected.

None of the above

Answer: C. UNION operates over all the columns in the SELECT list and does not ignore any columns.

88.You need to display the departments where the employees with the JOB IDs ”SA_REP” or ”ACCOUNTANT” work. Which of the following queries will fetch you the required results? (Consider the given table structure)

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)

SELECT department_id FROM employees E Where job_id = ''SA_RE'' UNION SELECT department_id FROM employees E Where job_id = ''ACCOUNTANT

SELECT department_id FROM employees E Where job_id = ''SA_REP'' UNION ALL Select department_id FROM employees E Where job_id = ''ACCOUNTANT

SELECT department_id FROM employees E Where job_id = ''SA_REP'' INTERSECT Select department_id FROM employees E Where job_id = ''ACCOUNTANT

SELECT department_id FROM employees E Where job_id = ''SA_REP'' MINUS Select department_id FROM employees E Where job_id = ''ACCOUNTANT

Answer: A.

89.Which of the following statement is true about the ordering of rows in a query which uses SET operator?

It is not possible to use ORDER BY in the individual queries that make a compound query.

An ORDER BY clause can be appended to the end of a compound query.

The rows returned by a UNION ALL will be in the order they occur in the two source queries.

The rows returned by a UNION will be sorted across all their columns, right to left.

Answer: A, B, C.

90.The UNION operator was used to fulfill which of the following function before the ANSI SQL syntax in place?

RIGHT OUTER JOIN

LEFT OUTER JOIN

EQUI-JOIN

FULL OUTER JOIN

Answer: D.

Answer the related questions 91 and 92 given below. Consider the table structures as given here:

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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

91.You need to find the job IDs which do not have any JOB history logged for them. Which of the following queries will work? (Consider the given table structures)

SELECT job_id FROM employees UNION ALL SELECT job_id FROM job_history;;

SELECT job_id FROM employees MINUS Select job_id FROM job_history;;

SELECT job_id FROM employees UNION SELECT job_id FROM job_history;;

None of the above

Answer: B.

92.Consider the following query:

SELECT distinct job_id FROM employees NATURAL JOIN job_history ;

Which of the following queries are identical to the above query?

SELECT job_id FROM employees UNION SELECT job_id FROM job_history;;

SELECT job_id FROM employees UNION ALL SELECT job_id FROM job_history;;

SELECT job_id FROM employees MINUS Select job_id FROM job_history;;

SELECT job_id FROM employees INTERSECT SELECT job_id FROM job_history;;

Answer: A.

Examine the table structures given here. Consider the query given below and answer the related questions 93 to 97 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)

SQL> desc job_history Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE END_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

SELECT job_id FROM employees UNION ALL SELECT job_id FROM job_history;;

93.If the EMPLOYEES table contains 5 records and the JOB_HISTORY contains 3 records, how many records will be obtained from the below query?

4

3

0

8

Answer: D. UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

94.If the UNION ALL operator is replaced with UNION operator, how many records will be obtained? (Assume there are 6 distinct values in both the tables)

5

3

2

6

Answer: D. UNION Returns the combined rows from two queries, sorting them and removing duplicates.

95.If the UNION ALL operator is replaced with MINUS operator, how many records will be obtained? (Assume there are 3 distinct values in EMPLOYEES and 2 in JOB_HISTORY)

3

2

1

0

Answer: C. MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

96.If the UNION ALL operator is replaced with INTERSECT operator, how many records will be obtained? (Assume there are 3 values common between the two tables)

8

6

3

2

Answer: C. INTERSECT Returns only the rows that occur in both queries” result sets, sorting them and removing duplicates.

97.Consider the following query:

1.select job_id 2. from employees 3.ORDER BY department_id 4.UNION ALL 5.select job_id 6.FROM job_history; 7.ORDER BY department_id ;

The above query generates an error. Which line in the above query generates an error?

3

7

2

No error is obtained

Answer: A. ORDER BY should only appear at the end of the compound query and not in the component queries.

98.Which of the following SET operator features are supported in SQL/Foundation:2003 but not by Oracle?

UNION ALL

MINUS ALL

INTERSECT ALL

EXCEPT ALL

Answer: B, C, D.

99.You need to find out the common JOB IDs (excluding duplicates) in the departments 100 and 200. Which query will you fire to get the required results? (Consider the table structure as given)

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)

SELECT job_id from employee WHERE department_id = 100 INTERSECT SELECT job_id from employee WHERE department_id = 200;

SELECT job_id from employee WHERE department_id = 100 UNION ALL SELECT job_id from employee WHERE department_id = 200;

SELECT job_id from employee WHERE department_id = 100 MINUS Select job_id from employee WHERE department_id = 200;

SELECT job_id from employee WHERE department_id = 100 INTERSECT ALL Select job_id from employee WHERE department_id = 200;

Answer: A.

100.If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.)

The INTERSECT, because INTERSECT has higher precedence than MINUS.

The MINUS, because MINUS has a higher precedence than INTERSECT.

The precedence is determined by the order in which they are specified.

It is not possible for a compound query to include both MINUS and INTERSECT.

Answer: C. All set operators have equal precedence, so the precedence is determined by the sequence in which they occur.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL – Subqueries to Solve Queries nhận dự án làm có lương
SQL – Subqueries to Solve Queries Questions
1. Which of the following are the types of sub-queries?

Ordered sub-queries

Grouped sub-queries

Single row sub-queries

None of the above

Answer: C. A subquery is a complete query nested in the SELECT,FROM, HAVING, or WHERE clause of another query.The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum. Single row sub-queries and multi-row sub-queries are the main types of sub-queries

2.Which of the following is true about sub-queries?

They execute after the main query executes

They execute in parallel to the main query

The user can execute the main query and then, if wanted, execute the sub-query

They execute before the main query executes.

Answer: D. The sub-query always executes before the execution of the main query.Subqueries are completed first.The result of the subquery is used as input for the outer query.

3.Which of the following is true about the result of a sub-query?

The result of a sub-query is generally ignored when executed.

The result of a sub-query doesn”t give a result, it is just helpful in speeding up the main query execution

The result of a sub-query is used by the main query.

The result of a sub-query is always NULL

Answer: C. Subqueries are completed first.The result of the subquery is used as input for the outer query.

4.Which of the following clause is mandatorily used in a sub-query?

SELECT

WHERE

ORDER BY

GROUP BY

Answer: A. A sub-query is just like any other query which has to start with a SELECT clause. They are contained within an outer query.

5. Which of the following is a method for writing a sub-query in a main query?

By using JOINS

By using WHERE clause

By using the GROUP BY clause

By writing a SELECT statement embedded in the clause of another SELECT statement

Answer: D. A subquery is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query.The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum.

6.In the given scenarios, which one would appropriately justify the usage of sub-query?

When we need to sum up values

When we need to convert character values into date or number values

When we need to select rows from a table with a condition that depends on the data from the same or different table.

None of the above

Answer: C.

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

HAVING

WHERE

FROM

All of the above

Answer: D. A sub-query is not different from a normal query. It can make use of all the primary clauses of a SELECT statement.

8.Which of the following single-row operators can be used for writing a sub-query?

>=

<

=

All of the above

Answer: D. Single-row operators include =, >, <, >=, <=, and <>.

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

IN

ANY

ALL

All of the above

Answer: 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.

10.What is true about the output obtained from a sub-query?

It remains in the buffer cache

It remains inside the sub-query and can be used later when needed

It is used to complete the outer (main) query

Both A and C

Answer: C. Subqueries are completed first.The result of the subquery is used as input for the outer query.

11.You need to find the salaries for all the employees who have a higher salary than the Vice President of a company ”ABC”.Which of the following queries will give you the required result? (Consider the table structure as given)

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)

SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE job_id = ''VICE-PRESIDENT'');

SELECT first_name, last_name, salary FROM employees WHERE salary = (SELECT salary FROM employees WHERE job_id = ''VICE-PRESIDENT'');

SELECT first_name, last_name, salary FROM employees WHERE job_id = ''VICE-PRESIDENT'');

None of the above

Answer: A. In the option ”A”, the inner sub-query gives the VP”s salary as a result to the outer query.

12.What among the following is true about sub-queries?

Sub-queries can be written on either side of a comparison operator

Parenthesis is not mandatory for sub-queries

Single-row sub-queries can use multi-row operators but vice versa is not possible

All of the above

Answer: A. Sub queries can be placed on left or right hand side of the comparison operator depending on the query indentation and usability.

13. What will be the outcome of the following query? (Consider the given table structure)

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)

SELECT first_name, last_name, salary FROM employees WHERE salary ANY (SELECT salary FROM employees);

It executes successfully giving the desired results

It executes successfully but does not give the desired results

It throws an ORA error

It executes successfully and gives two values for each row obtained in the result set

Answer: C. Multi-row operators cannot be used in single-row sub-queries and vice versa.

14.Which of the following is true about single-row sub-queries?

They give one result from the main query

They give only one row in the result set

They return only one row from the inner SELECT statement

They give multiple rows from the main (outer) query

Answer: C. A single-row subquery can return a maximum of one value.

15.What is true about multi-row sub-queries?

They can return more than one column as the result of the inner query

They return multiple rows in the main query but only a single result set in the inner query

They return single row in the main query but multiple rows in the inner sub-query

They return more than one row from the inner SELECT statement

Answer: D. Multi-column sub-queries return more than one column in their result set, multi-row sub-queries return more than one row from the inner query.

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

They return only one row

They use single-row operators

Both A and B

None of the above

Answer: C.

17.Which of the following operators cannot be used in a sub-query?

AND

<

>

<>

Answer: A. Single-row operators include =, >, <, >=, <=, and <>. Multi-row operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.

Examine the exhibit and answer the questions 18 to 21 that follow.

18.You need to find out the names of all employees who belong to the same department as the employee ”Jessica Butcher” who is in department 100 and has an employee ID 40. Which of the following queries will be correct?

SELECT first_name, last_name FROM employees WHERE last_name = ''Butcher'' And first_name = ''Jessica

SELECT first_name, last_name FROM employees WHERE department =100;

SELECT first_name, last_name FROM employees WHERE department = (SELECT department FROM employees WHERE first_name = ''Jessica'' AND last_name = ''Butcher'');

SELECT first_name, last_name FROM employees WHERE department = (SELECT department FROM employees WHERE first_name = ''Jessica'' AND last_name = ''Butcher'' AND department = 100 AND employee_id = 40);

Answer: D. ”D” is more appropriate than ”C” because it filters on employee id which is unique and ensures that the sub-query will return single row only. ”C” can fail if there are more than one employee with the same first and last name.

19.You need to find out the employees which belong to the department of ”Jessica Butcher” and have salary greater than the salary of ”Jessica Butcher” who has an employee ID of 40. Which of the following queries will work?

SELECT first_name, last_name FROM employees WHERE last_name = ''Butcher'' AND first_name = ''Jessica'' AND salary > 10000;

SELECT first_name, last_name FROM employees WHERE department = 100;

SELECT first_name, last_name FROM employees WHERE department = (SELECT department FROM employees WHERE first_name = ''Jessica'' AND last_name = ''Butcher'' AND employee_id = 40) AND salary > (SELECT salary FROM employees WHERE first_name = ''Jessica'' AND last_name = ''Butcher'' AND employee_id = 40);

SELECT first_name, last_name FROM employees WHERE department = (SELECT department FROM employees WHERE first_name = ''Jessica'' AND last_name = ''Butcher'' AND department = 100);

Answer: C. More than one sub-query can be written in one SQL statement to add more than one condition.

20.Based on the answers for questions 18th and 19th, what type of sub-queries is used by them?

Single row sub-query

Multiple row sub-query

Both A and B

Inline sub-query

Answer: A. The questions 18th and 19th given above demonstrate the usage sub-queries in a SELECT statement.

21.Consider two statements about outer and inner queries in context of SQL sub-queries?

i. The inner queries can get data from only one table

ii. The inner queries can get data from more than one table

Which of the above statements are true?

(i)

(ii)

Both (i) and (ii)

Neither (i) nor (ii)

Answer: B. Sub-queries can fetch data from more than one table.

Examine the table structure as follows and answer the questions 22 to 27 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)

22.What will be the outcome of the following query? (Choose the most appropriate answer)

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)

SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT max(salary) FROM employees);

It executes successfully and gives the employees who have salaries equal to the max salary.

It executes successfully but does not give the required results

It throws an error as a group function is used in the sub-query

It throws an error as a single row sub-query should contain a multi-row operator

Answer: A. A group function can be used within a sub-query.

23.What will be the outcome of the query that follows?

SELECT first_name, last_name, min(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT min(salary) FROM employees WHERE department_id = 100);

It executes successfully and gives the names and minimum salary greater than department 100 of all employees

It executes successfully and gives the salaries of the employees in department 100

It executes successfully and gives the names and minimum salaries of all the employees.

It throws an error.

Answer: A. HAVING clause can be used in sub-queries as shown

24.You need to find the job which has a maximum average salary.Which of the following queries will give you the required results?

SELECT job_id, avg(salary) FROM employees GROUP BY job_id;

SELECT job_id, avg(salary) FROM employees GROUP BY job_id HAVING job_id in (SELECT max(avg(salary) FROM employees);

SELECT job_id, avg(salary) FROM employees GROUP BY job_id HAVING max(avg(salary) in (SELECT max(avg(salary) FROM employees);

SELECT job_id, avg(salary) FROM employees GROUP BY job_id HAVING avg(salary) in (SELECT max(avg(salary) FROM employees GROUP BY job_id);

Answer: D. Sub-queries can make use of group functions and HAVING clause to restrict the groups.

25.The following query throws an error. Choose the correct reason for the error as given in the options.

SELECT first_name, last_name FROM employees WHERE commission_pct = (SELECT min(commission_pct ) FROM employees GROUP BY department_id);

The GROUP BY clause is not required in the sub-query

A function cannot be used in a sub-query SELECT statement

The single row sub-query gives multiple records

The use of “=” operator is invalid; an IN operator will work correctly

Answer: C, D. The GROUP BY clause gives the minimum commission_pct for each department and hence multiple results are fetched to the main query giving an error.

26.Consider the query given below.How many records will be returned as a result of the above query? (Assuming the no employee with job id XX exists in the company)

SELECT first_name, last_name FROM employees WHERE salary = (SELECT salary FROM employees WHERE job_id = ''XX'');

1

NULL

0

The query raises ORA error because sub-query is invalid.

Answer: C. Since there is no employee with job_id “XX” in the company, the sub-query returns no result, which when equated to job_id in the main query gives a 0.

27.What happens if the WHERE condition in the query given in question 26 is replaced with a new one (WHERE job_id IS NOT NULL)? (Assume the number of records in ”employees” table is 14).

1

14

0

ORA error

Answer: D. The query execution raises the exception “ORA-01427: single-row subquery returns more than one row”.

28.Which of the following are valid multi row operators used for sub-queries?

<=

ANY >=

!=

>=

Answer: B. 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.The multi row operators IN, ANY, ALL must be used with single row operators as shown in the option B.

Examine the table structure as given. Consider the query given below and answer the questions 29 to 33 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)

SELECT first_name, last_name, salary, commission_pct FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE department_id = 100) AND department_id <> 101;

29.What does the ANY operator evaluates to in the above query?

TRUE

FALSE

NULL

0

Answer: A. The multi row operators return Boolean results. As there are results of salary in the department 100, it returns TRUE. If there are 0 results, it evaluates to FALSE.

30.What will be the outcome of the query if we assume that the department 100 has only one employee?

It executes successfully giving the one result

It executes successfully giving salaries of all the employees

NULL

It throws an ORA error

Answer: D. If the department 100 has one result (single row sub-query), the < ANY operator gives the error as it is a multi-row operator.

31.What will be the outcome of the query given above if the < ANY operator is replaced with = ANY operator?

Oracle will treat each value of the salary returned from the sub-query as it does with IN operator

There will be no difference in the results

The results will differ

The execution will thrown an ORA error

Answer: A. = ANY operator is equivalent to IN operator.

32.What can be said about the < ANY operator in the query given above?

It gives the maximum value of salary

It gives the minimum value of salary

It means it gives the values that are lesser than the highest

None of the above

Answer: C. The multi row operator < ANY evaluates to the statements “Less than the maximum” of the subquery. ”> 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
<
33.Assume that the < ANY operator is replaced with the > ANY. What is true about this operator?

It gives the maximum salary

It finds only the maximum salary from the sub-query

It gives more than the minimum salary

It gives the minimum salary

Answer: C. The multi row operator > ANY evaluates to the statements “Greater than the minimum” of the subquery. ”> 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
<
34. Examine the given table structure and consider the following query:

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)

SELECT employee_id, first_name, last_name FROM employees WHERE salary IN (SELECT max(salary) FROM employees GROUP BY department_id );

Which WHERE clause among the following is equivalent to that given in the above query? (Assume that the salaries are 2500, 3000, 3500,4000)

WHERE salary < ANY (SELECT max(salary) FROM employees GROUP BY department_id );

WHERE salary < ALL (SELECT max(salary) FROM employees GROUP BY department_id );

WHERE salary = (SELECT max(salary) FROM employees GROUP BY department_id );

WHERE salary IN (2500,3000,3500,4000);

Answer: D. When the IN operator is used, Oracle treats individual results of the sub-query as shown in the option D.

Examine the structure of the EMPLOYEES table as given below and answer the questions 35 to 37 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)
<
35. You need to find out which of the employees have a salary less than that of the salary for the job ID ”FIN_ACT”. Which of the following queries will give you the required output?

SELECT employee_id, first_name, last_name FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = ''FIN_ACT'') AND job_id <> ''FIN_ACT

SELECT employee_id, first_name, last_name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = ''FIN_ACT'') AND job_id <> ''FIN_ACT

SELECT employee_id, first_name, last_name FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = ''FIN_ACT'') AND job_id <> ''FIN_ACT

SELECT employee_id, first_name, last_name FROM employees WHERE salary = (SELECT salary FROM employees WHERE job_id = ''FIN_ACT'') AND job_id <> ''FIN_ACT

Answer: A. < ALL means less than the minimum. ”> 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

36.What will be the outcome of the above query (the option A in the question above), if the < ALL is replaced with the >ALL?

It will execute successfully giving the same result.

It will throw an ORA error

It will execute successfully but give the employees” details who have salaries lesser than all the employees with job_id ”FI_ACCOUNTANT”.

None of the above

Answer: C. >ALL means less than the minimum. ”> 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

37.You need to find the salaries for all employees who are not in the department 100. Which of the following queries will give you the required result?

SELECT employee_id, first_name, last_name FROM employees WHERE salary !=ALL (SELECT salary FROM employees WHERE department_id = 100) AND department_id <> 100;

SELECT employee_id, first_name, last_name FROM employees WHERE salary NOT IN (SELECT salary FROM employees WHERE department_id = 100) AND department_id <> 100;

SELECT employee_id, first_name, last_name FROM employees WHERE salary NOT ALL (SELECT salary FROM employees WHERE department_id = 100) AND department_id <> 100;

SELECT employee_id, first_name, last_name FROM employees WHERE salary != (SELECT salary FROM employees WHERE department_id = 100) AND department_id <> 100;

Answer: C. NOT can be used with the multi row operators IN, ANY and ALL.

Examine the table structure as given. Consider the following query and answer the questions 38 and 39 that follow. You need to find the employees who do not have a sub-ordinate reporting to them. (Assume there are 0 expected results)

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)

SELECT first_name, last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees);

38.What will be the result of the query given above?

10

NULL

ORA error

0

Answer: D. One of the values in the inner sub-query is NULL (all employees are not managers!)

39.Which of the following WHERE clauses should be added / modified to the above query to give the expected results?

WHERE employee_id != (SELECT manager_id FROM employees);

WHERE employee_id IN (SELECT manager_id FROM employees);

WHERE employee_id <>ALL (SELECT manager_id FROM employees);

WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id is NOT NULL);

Answer: B, D. If the sub-query is likely to have NULL values, do not use the NOT IN operator or if using, modify the sub-query with an additional WHERE clause (option D)

40.What is true about sub-queries in general?

Sub-queries have to be executed separately from the main queries

Sub-queries can be executed at the will of the user, they are not related to the main query execution

Sub-queries are equal to two sequential queries where the results of inner query are used by the main query

All of the above

Answer: C.

41. Which of the following is true about sub-queries?

A sub-query can return 0 or more rows

A sub-query can be used only in the SELECT clause

Nesting of sub-queries is limited to 2 levels

Group functions cannot be used in sub-queries

Answer: A. A subquery is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query. The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum. A single-row subquery can return a maximum of one value. Multiple-column subqueries return more than one column to the outer query.

42. Examine the table structure as given.

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)

Consider the following query.

SELECT first_name, last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id, hire_date FROM employees WHERE manager_id is not null);

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

The NOT IN operator used is invalid

The WHERE clause in the sub-query is incorrectly written

The column in the sub-query SELECT clause should only be one when there”s an inequality used in the main query

The sub-query uses the same table as the main query

Answer: C. The columns selected in the sub-query should be same as on the other side of comparison operator. Any inequality of data type or number of columns would result in an ORA error.

43.A report has to be extracted which displays all the departments that have one or more employees assigned to them. Which of the following queries will give the required output? (Consider the table structure as given)

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)

SELECT department_name FROM employees WHERE department_id IN (SELECT distinct (department_id ) FROM employees);

SELECT department_name FROM employees WHERE department_id ANY (SELECT distinct (department_id ) FROM employees);

SELECT department_name FROM employees WHERE department_id < ANY (SELECT distinct (department_id ) FROM employees);

SELECT department_name FROM employees WHERE department_id = ANY (SELECT distinct (department_id ) FROM employees);

Answer: A, D.

44.What is the maximum level of sub-queries allowed in Oracle in a single SQL statement?

20

50

Unlimited

255

Answer: D. Oracle supports the Nesting of queries to 255 levels.

45. What should be the best practice to follow when we know what values we need to pass on to the main query in Oracle queries?

Using GROUP BY

Using sub-queries

Using HAVING

None of the above

Answer: D. It might become possible that the sub-queries give a NULL result, which results in 0 rows in the main result; hence it is a good practice to use them only if we know what values we need.

Examine the table structure as given. Consider the following query and answer the questions 46 and 47 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)

SELECT employee_id, first_name, last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees);

46.You need to find all the employees whose job ID is the same as that of an employee with ID as 210. Which of the following WHERE clauses would you add / modify to achieve this result? (Consider the table structure as given

WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 210);

WHERE job_id IN (SELECT job_id FROM employees WHERE employee_id = 210);

WHERE job_id > (SELECT job_id FROM employees WHERE employee_id = 210);

WHERE job_id >= (SELECT job_id FROM employees WHERE employee_id = 210);

Answer: A.

47.Assume that you change the WHERE clause as given in the option A in question 46 as the following.

WHERE job_id = (SELECT job_id FROM employees WHERE employee_id < 210);

What will be the outcome of this change?

The results will be the same

ORA error thrown on execution

The results will differ

The query will execute successfully giving 0 rows.

Answer: B. The sub-query gives more than one result on the given change and hence a multi row operator should replace the “=” in the main query given above.

48.Examine the table structures as shown in the exhibit below.

You need to display the names of the employees who have the highest salary. Which of the following SQL statements will be correct?

SELECT first_name, last_name, grade FROM employees, grade WHERE (SELECT max (salary) FROM employees) BETWEEN losal and hisal;

SELECT first_name, last_name, grade FROM employees, grade WHERE (SELECT max (salary) FROM employees) BETWEEN losal and hisal AND salary BETWEEN losal and hisal;

SELECT first_name, last_name, grade FROM employees, grade WHERE salary = (SELECT max (salary) FROM employees) AND salary BETWEEN losal and hisal;

SELECT first_name, last_name, grade FROM employees, grade WHERE salary IN (SELECT max (salary) FROM employees) AND max(salary) BETWEEN losal and hisal;

Answer: B, C. The sub-queries can be written on either side of the operator

49.What is the sub-query in the FROM clause of an SQL statement? (Choose the most appropriate answer)

Single row sub-query

Multi row sub-query

Inline View

Co-related sub-query

Answer: C. If a sub-query appears in the FROM clause of the SELECT statements,it forms an Inline view. Oracle internally creates a temporary view for the query execution.

50.What is the maximum number of nesting level allowed in an Inline View type sub-query?

255

300

216

Unlimited

Answer: D. As there is no limit on the number of tables which can be joined, there is no limit on the number of inline view in a query.

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

Answer: 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.

52.Which of the following statements cannot be parent statements for a sub-query?

SELECT

GROUP BY

UPDATE

DELETE

Answer: B. The rest of the options can be in the main query (parent query) of a sub-query.

53.What is true about a co-related sub-query?

It is evaluated only once for the parent query

It is evaluated only thrice for the parent query

It is evaluated once for each row processed by the parent sub-query

All of the above

Answer: C. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present.

54.Examine the given table structure. You need to write a query which returns the names of the employees whose salaries exceed their respective department”s average salary. Which of the following will work? (Choose the most appropriate answer)

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)

SELECT employee_id, first_name, last_name FROM employees e WHERE salary > (SELECT avg (salary) FROM employees WHERE e.department_id = department_id ) ORDER BY department_id ;

SELECT employee_id, first_name, last_name FROM employees e WHERE salary > ANY (SELECT avg(salary) FROM employees WHERE e.department_id = department_id ) ORDER BY department_id ;

SELECT employee_id, first_name, last_name FROM employees e WHERE salary = (SELECT avg(salary) FROM employees WHERE e.department_id = department_id ) ORDER BY department_id ;

SELECT employee_id, first_name, last_name FROM employees e WHERE salary < ANY (SELECT avg(salary) FROM employees WHERE e.department_id = department_id ) ORDER BY department_id ;

Answer: A. Here the department ID is obtained, used to evaluate the parent query and if the salary in that row is greater than the average salary of the departments of that row, that result is returned.

55.Examine the given table structure. Which of the following queries will display duplicate records in a table EMPLOYEES?

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)

SELECT * FROM employees E WHERE exists (SELECT 1 FROM employees E1 WHERE E.employee_id = E1.employee_id);

SELECT * FROM employees E WHERE exists (SELECT 1 FROM employees E1 WHERE E.employee_id = E1.employee_id AND E.ROWID < E1.ROWID);

SELECT * FROM employees E WHERE exists (SELECT 1 FROM employees E1 WHERE E.ROWID < E1.ROWID);

SELECT * FROM employees E WHERE = ANY (SELECT 1 FROM employees E1 WHERE E.employee_id = E1.employee_id And E.ROWID < E1.ROWID);

Answer: A. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present. It can be used to find the duplicate rows in a table where duplicity is subjected to a column or set of columns.

Examine the structures for the tables DEPARTMENTS and EMPLOYEES and answer the questions 56 and 57 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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

56.Which of the following queries will display the system date and count of records in the DEPARTMENTS and EMPLOYEES table?

SELECT sysdate, (SELECT * FROM departments) dept_count, (SELECT * FROM employees) emp_count FROM DUAL;

SELECT sysdate, (SELECT count(*) FROM departments) dept_count, (SELECT count(*) FROM employees) emp_count FROM DUAL GROUP BY department_id ;

SELECT sysdate, (SELECT * FROM departments) dept_count, (SELECT * FROM employees) emp_count FROM DUAL GROUP BY employee_id;

SELECT sysdate, (SELECT count(*) FROM departments) dept_count, (SELECT count(*) FROM employees) emp_count FROM DUAL;

Answer: D. A single-row subquery can also be nested in the outer query”s SELECT clause. In this case, the value the subquery returns is available for every row of output the outer query generates. Typically, this technique is used to perform calculations with a value produced from a subquery.

57.Which of the following queries will tell whether a given employee is a manager in a Company ”XYZ”?

SELECT employee_id, manager_id FROM employees A WHERE employee_id ANY (SELECT manager_id from employees B) ORDER BY manager_id desc;

SELECT employee_id, manager_id FROM employees A WHERE employee_id < ALL (SELECT manager_id from employees B)

SELECT employee_id, manager_id FROM employees A WHERE employee_id IN (SELECT manager_id from employees B) ORDER BY manager_id desc;

SELECT employee_id, manager_id FROM employees A WHERE employee_id in (SELECT manager_id from employees B) GROUP BY department_id ;

Answer: C.

Examine the exhibit and answer the question 58 that follows:

58.Which of the following queries will give you maximum salary of an employee in a particular city?

SELECT max (salary), city FROM (SELECT salary, department_id , loc, city FROM employees natural join departments natural join locations);

SELECT salary, city FROM (SELECT salary, department_id , loc, city FROM employees natural join departments natural join locations);

SELECT max (salary), city FROM (SELECT salary, department_id , loc, city FROM employees natural join departments natural join locations) GROUP BY city;

SELECT max (avg(salary)), city FROM (SELECT salary, department_id , loc, city FROM employees natural join departments natural join locations);

Answer: C. When a multiple-column subquery is used in the outer query”s FROM clause, it creates a temporary table that can be referenced by other clauses of the outer query. This temporary table is more formally called an inline view. The subquery”s results are treated like any other table in the FROM clause. If the temporary table contains grouped data, the grouped subsets are treated as separate rows of data in a table.

Examine the table structures as given below.

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

Consider the following query and answer the questions that 59 to 62 that follow.

SELECT department_name FROM departments d INNER JOIN employees e ON (d.employee_id = e.employee_id) GROUP BY department_name;

59.Which of the following queries can replace the above query by using sub-queries giving the same result?

SELECT department_name FROM departments WHERE department_id = ANY (SELECT department_id FROM employees);

SELECT department_name FROM departments WHERE department_id IN (SELECT distinct(department_id ) FROM employees);

SELECT department_name FROM departments WHERE department_id = (SELECT distinct(department_id ) FROM employees);

SELECT department_name FROM departments WHERE department_id ANY (SELECT distinct(department_id ) FROM employees);

Answer: A, B.

60.Assume that the sub-query as shown in the query given above is modified to the following.

(SELECT distinct (department_id ) FROM employees ORDER BY department_id );

What will be the outcome as a result of this change? (Choose the most appropriate answer)

It will order the department_id fetched from the sub-query and display them in ascending order

It will throw an ORA error as the ORDER BY clause should be accompanied by the GROUP BY clause

It will throw an ORA error because an ORDER BY clause cannot be used inside a sub-query

It will execute successfully.

Answer: C. A subquery, except one in the FROM clause, can”t have an ORDER BY clause.If you need to display output in a specific order, include an ORDER BY clause as the outer query”s last clause.

61.Assume that the query given above is modified as the below one.

SELECT department_name FROM departments WHERE department_id = ANY (SELECT department_id FROM employees) ORDER BY department_id desc;

What will be the outcome as a result of this change? (Choose the most appropriate answer)

It will order the department_id fetched from the sub-query and display them in ascending order

It will order the department_id fetched from the sub-query and display them in descending order

It will throw an ORA error because an ORDER BY clause cannot be used inside a sub-query

None of the above

Answer: D. A subquery, except one in the FROM clause, can”t have an ORDER BY clause.If you need to display output in a specific order, include an ORDER BY clause as the outer query”s last clause.

62.Which of the following can be used to order results in a sub-query?

ORDER BY

HAVING

GROUP BY

All of the above

Answer: C. By default, the GROUP BY clause performs ordering in a sub-query.

Examine the exhibit below and answer the questions 63 to 65 that follow:

Consider the following query:

SELECT au_id, au_title FROM audit WHERE au_details in (SELECT au_details FROM audit WHERE au_title like ''S%'') ORDER BY au_title;

63.What will be the outcome of the query given above?

It gives all AU_ID and AU_TITLEs starting with the letter ”S%”

It gives all AU_ID and AU_TITLEs starting with the letter ”S%” ordered by the titles in ascending order

It throws an ORA error

It returns a 0 value

Answer: C. A column with a CLOB, BLOB, NCLOB or an ARRAY cannot be used in a sub-query.

64. 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 (*)

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

65.What is true about using NOT IN when writing queries with sub-queries in them?

NOT IN ignores all the NULL values and gives only the NOT NULL values

NOT IN puts all the NULL values at the last and gives the NOT NULL to be displayed first

NOT IN should be not be used if a NULL value is expected in the result set

NOT IN is just a negation of the operator IN and can be changed without any caveat.

Answer: C. SQL handles NULL values in a different way and hence it is a good practice to avoid NOT IN if the result set might contain a NULL.

Consider the following table structures and answer the questions 66 to 72 that follow:

66. You need to find out the names and IDs of the departments in which the least salary is greater than the highest salary in the department 10. Which of the following queries will give the required result.

SELECT department_id , min(salary) FROM employees GROUP BY department_id HAVING min(salary) > ( select max(salary) FROM employees where department_id = 10 )

SELECT department_id , min(salary) FROM employees GROUP BY department_id HAVING min(salary) > ANY ( select max(salary) FROM employees )

SELECT department_id , min(salary) FROM employees HAVING max(salary) < ANY ( select min(salary) FROM employees where department_id = 10 )

SELECT department_id , min(salary) FROM employees GROUP BY department_id HAVING min(salary) > ALL ( select max(salary) FROM employees where department_id = 10 )

Answer: A.

67.Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 10. (Choose the best answer)

SELECT employee_id, Salary FROM employees WHERE salary in ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary =ANY ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary ALL ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary < ANY ( SELECT salary FROM employees where department_id = 10 )

Answer: A, B.

68.You need to find out all the employees who have salary greater than at least one employee in the department 10. Which of the following queries will give you the required output?

SELECT employee_id, Salary FROM employees WHERE salary >= ANY ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary > ANY ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary < ANY ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary = ALL ( SELECT salary FROM employees where department_id = 10 )

Answer: B.

69.You need to find out all the employees who have salary lesser than the salary of all the employees in the department 10. Which of the following queries will give you the required output?

SELECT employee_id, Salary FROM employees WHERE salary > ALL ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary =ALL ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary < ALL ( SELECT salary FROM employees where department_id = 10 )

SELECT employee_id, Salary FROM employees WHERE salary < ANY ( SELECT salary FROM employees where department_id = 10 )

Answer: C. 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.Multiple-column subqueries return more than one column to the outer query. The columns of data are passed to the outer query in the same order in which they”re listed in the subquery”s SELECT clause.

70.You need to find out all the employees who have their manager and department matching with the employee having an Employee ID of 121 or 200. Which of the following queries will give you the required output?

SELECT employee_id, manager_id,department_id FROM employees WHERE (manager_id,department_id ) = ANY ( select manager_id, department_id FROM employees where employee_id in (121,200) )

SELECT employee_id, manager_id,department_id FROM employees WHERE (manager_id,department_id ) < ANY ( select manager_id, department_id FROM employees where employee_id in (121,200) )

SELECT employee_id, manager_id,department_id FROM employees WHERE (manager_id,department_id ) > ANY ( select manager_id, department_id FROM employees where employee_id in (121,200) )

SELECT employee_id, manager_id,department_id FROM employees WHERE (manager_id,department_id ) in ( select manager_id, department_id FROM employees where employee_id in (121,200) )

Answer: A, 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. Multiple-column subqueries return more than one column to the outer query. The columns of data are passed to the outer query in the same order in which they”re listed in the subquery”s SELECT clause.

71.You need to find the department name of an employee with employee ID 200. Which of the following queries will be correct? (Choose the most appropriate answer)

SELECT employee_id, first_name, last_name,department_id , (SELECT department_name FROM departments d, employees E WHERE d.department_id = e.department_id And employee_id = 200 ) FROM employees e

SELECT employee_id, first_name, last_name,department_id , (SELECT department_ID FROM departments d WHERE d.department_id = department_id ) FROM employees e WHERE employee_id = 200;

SELECT employee_id, first_name, last_name,department_id , (SELECT department_name FROM departments d WHERE d.department_id = e.department_id And employee_id = 200 ) FROM employees e

SELECT employee_id, first_name, last_name,department_id , (SELECT department_name FROM departments d,employee E WHERE d.department_id = e.department_id ) FROM employees e

Answer: C.

72.You need to find the highest earning employee with the job ID as ”SA_REP”. Which of the following queries will be correct? (Choose the most appropriate answer)

SELECT job_id, employee_id, Salary FROM employees e WHERE job_id = ( SELECT distinct salary FROM employees E1 WHERE E.job_id = E1.job_id AND E.salary <= E1.salary AND job_id = ''SA_REP''

SELECT department_id , employee_id, Salary FROM employees E WHERE 1 = ( SELECT count(distinct salary) FROM employees E1 WHERE E.job_id = E1.job_id AND E.salary <= E1.salary AND job_id = ''SA_REP'' )

SELECT department_id , employee_id, Salary FROM employees E WHERE 0 = ( SELECT count(distinct salary) FROM employees E1 WHERE E.job_id = E1.job_id AND E.salary = E1.salary AND job_id = ''SA_REP'' )

SELECT department_id , employee_id, Salary FROM employees E WHERE 1 = ( SELECT salary FROM employees E1 WHERE E.job_id < E1.job_id AND E.salary <= E1.salary AND job_id = ''SA_REP'' )

Answer: B.

Consider the EMPLOYEES table structure as shown in the exhibit and answer the questions 73 to 77 that follow:

73.You need to find the job which has at least one employee in it. Which of the following queries will be correct? (Choose the most appropriate answer)

SELECT employee_id, Job_id FROM employees E WHERE exists ( SELECT 1 FROM employees E1 WHERE E.job_id = E1.job_id )

SELECT employee_id, Job_id FROM employees E WHERE exists ( SELECT * FROM employees E1 WHERE E.job_id = E1.job_id )

SELECT employee_id, Job_id FROM employees E WHERE not exists ( SELECT * FROM employees E1 WHERE E.job_id = E1.job_id )

SELECT employee_id, Job_id FROM employees E WHERE exists ( SELECT 1 FROM employees E1 WHERE E.job_id < E1.job_id )

Answer: A. The EXISTS operator is used to check and match records between queries. It returns a BOOLEAN value. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present. An Uncorrelated subquery executes the subquery first and passes the value to the outer query.

74.You need to find the job which has no employees in it. Which of the following queries will be correct? (Choose the most appropriate answer)

SELECT employee_id, Job_id FROM employees E WHERE exists ( SELECT * FROM employees E1 WHERE E.job_id = E1.job_id )

SELECT employee_id, Job_id FROM employees E WHERE not exists ( SELECT 1 FROM employees E1 WHERE E.job_id = E1.job_id )

SELECT employee_id, Job_id FROM employees E WHERE not exists ( SELECT * FROM employees E1 WHERE E.job_id = E1.job_id )

SELECT employee_id, Job_id FROM employees E WHERE exists ( SELECT 1 FROM employees E1 WHERE E.job_id < E1.job_id )

Answer: B. The NOT EXISTS is the negation operator for EXISTS.

75.You need to find the 3rd maximum salary from the EMPLOYEES table. Which of the following queries will give you the required results? (Choose the most appropriate answer)

SELECT * FROM employees E WHERE salary = (SELECT count(distinct salary ) FROM employees WHERE e.salary = salary );

SELECT * FROM employees E WHERE 1 = (SELECT count(distinct salary ) FROM employees WHERE e.salary < salary );

SELECT * FROM employees E WHERE 2 = (SELECT count(distinct salary ) FROM employees WHERE e.salary >salary );

SELECT * FROM employees E WHERE 3 = (SELECT count(distinct salary ) FROM employees WHERE e.salary <= salary );

Answer: D.

76. You need to find the maximum salary by using the user input for getting the value of N. Which of the following queries will give you the required results? (Choose the most appropriate answer)

SELECT salary FROM ( SELECT rowid as user_sal FROM (SELECT distinct salary from employees ORDER BY salary desc) ) WHERE user_sal=&N ;

SELECT salary FROM ( SELECT rownum as user_sal FROM (SELECT distinct salary FROM employees GROUP BY salary ) ) WHERE user_sal <= &N ;

SELECT salary FROM ( SELECT rownum as user_sal, salary FROM (SELECT distinct salary FROM employees ORDER BY salary desc) ) WHERE user_sal=&N ;

SELECT salary FROM ( SELECT max(rownum) as user_sal, salary FROM (SELECT distinct salary FROM employees ORDER BY salary desc) ) WHERE user_sal=&N ;

Answer: C. ROWNUM is a pseudo column used for finding the nth order results.

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

Answer: D.

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

255

100

2

16

Answer: A.

79.What is true about the EXISTS operator in SQL queries with respect to sub-queries?

The columns selected in the sub-queries are important

The inner query”s should return rows, any result is what is important, not what is SELECTED

Both A and B

Neither A nor B

Answer: B.

80.What is true about the ANY operator used for sub-queries?

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

Returns rows that match the first 5 values in a list/sub-query

Returns rows that match any value in a list/sub-query

Returns the value 0 when all the rows match in a list/sub-query

Answer: C.

81.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 list/sub-query

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

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

All of the above

Answer: 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.

82.What is true about using sub-queries in INSERT statements in Oracle?

They can be used in the INSERT clause without any restriction

They can be used in the INSERT clause only for Numeric values

The SELECT list of a sub-query should be the same as the column list of the INSERT statement.

None of the above

Answer: C.

Examine the table structures as given below and answer the questions 83 to 86 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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

83.You need to find the details of all employees who were hired for the job ID ”SA_REP” in the month of June, 2013. Which of the following queries will give the required results? (Consider the table structure as given)

SELECT first_name FROM employees WHERE employee_id = ( SELECT employee_id FROM employees WHERE to_char(hiredate, ''MM/YYYY'')= ''02/1981'' AND job_id = ''SA_REP'' );

SELECT first_name FROM employees WHERE employee_id = ANY ( SELECT employee_id FROM employees WHERE to_char(hiredate, ''MM/YYYY'')= ''02/1981'' AND job_id = ''SA_REP'' );

SELECT first_name FROM employees WHERE employee_id ANY ( SELECT employee_id FROM employees WHERE to_char(hiredate, ''MM/YYYY'')= ''02/1981'' AND job_id = ''SA_REP'' );

SELECT first_name FROM employees WHERE employee_id exists ( SELECT employee_id FROM employees WHERE to_char(hiredate, ''MM/YYYY'')= ''02/1981'' AND job_id = ''SA_REP'' );

Answer: B.

84.Which of the following statements are equivalent?

SELECT employee_id , salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE department_id=100);

SELECT employee_id , salary FROM employees WHERE salary < (SELECT min(salary) FROM employees WHERE department_id=100);

SELECT employee_id FROM employees WHERE salary not >= ANY (SELECT salary FROM employees WHERE department_id=100);

None of the above

Answer: A, B.

85.Consider the following two queries:
Query 1:
SELECT first_name FROM employees e join departments d ON e.department_id = d.department_id WHERE department_name=''ACCOUNTS
Query 2:
SELECT first_name FROM employees e WHERE department_id = ANY (SELECT department_id FROM departments d WHERE department_name=''ACCOUNTS'');

What can be said about the two statements?

Both the queries should generate the same result.

Both the queries will throw an error.

If there are two departments with the same name, both the queries will fail.

Both the queries will run successfully even if there is more than one department named ”ACCOUNTS”.

Answer: A, D.

86.You need to display all the employees who have the highest salary in a department 100. You fire a query as below.

SELECT E.first_name, E.last_name , E.salary FROM employees E WHERE E.salary > ALL (SELECT E1.salary FROM employees E1 WHERE E.department_id =E1.department_id AND E.department_id = 100);

What will be the outcome of the above query?

It executes successfully and gives the required results

It executes successfully but doesn”t give the required output

It throws an ORA error on execution

It executes successfully and gives the required result when >ALL is replaced with >=ALL

Answer: B, D. >ALL will not give the required result as there may be two employees with the same salary and who are the highest earners in the department 100

Consider table structures as shown in the exhibit and answer the questions 87 to 89 that follow:

87.You need to fetch the first names (in a reverse alphabetical order) of all the employees in the department ID = 100 and who have the maximum salary in the JOB ID = ”SA_REP”. Which of the following queries will give the required output? (Choose the most appropriate output)

SELECT E.first_name, job_id , salary FROM employees E WHERE salary = (SELECT max(salary) FROM employees E1 WHERE E1.department_id = 100 GROUP BY job_id ) AND job_id = ''SA_REP'' ORDER BY first_name;

SELECT E.first_name, job_id , salary FROM employees E WHERE salary in (SELECT max(salary) FROM employees E1 where E1.department_id = 100) ORDER BY first_name;

SELECT E.first_name, job_id , salary FROM employees E WHERE salary IN (SELECT max(salary) FROM employees E1 where job_id = ''SA_REP'' GROUP BY job_id ) AND WHERE E.department_id = 100 ORDER BY first_name desc;

SELECT E.first_name, job_id , salary FROM employees E WHERE salary IN (SELECT max(salary) FROM employees E1 WHERE E1.department_id = 100 GROUP BY job_id ) ORDER BY first_name ;

Answer: C.

88.In the queries given above (option C is the correct answer), you need to display all the employees with the JOB ID ”SA_REP” who have the maximum salary in the department 100. Which of the following queries will give the required output?

SELECT E.first_name, job_id , salary FROM employees E WHERE salary IN (SELECT max(salary) FROM employees E1 WHERE E1.department_id = 100 GROUP BY job_id ) AND job_id = ''SA_REP'' ORDER BY first_name;

SELECT E.first_name, job_id , salary FROM employees E WHERE salary in (SELECT max(salary) FROM employees E1 WHERE E1.department_id = 100) ORDER BY first_name;

SELECT E.first_name, job_id , salary FROM employees E WHERE salary in (SELECT max(salary) FROM employees E1 WHERE job_id = ''SA_REP'' GROUP BY job_id ) And WHERE E.department_id = 100 ORDER BY first_name desc;

SELECT E.first_name, job_id , salary FROM employees E WHERE salary in (SELECT max(salary) FROM employees E1 WHERE E1.department_id = 100 GROUP BY job_id ) ORDER BY first_name ;

Answer: A.

89.Select the query which will give you the maximum salary and maximum comm percentage. The query should also give the maximum comm percentage paid if the highest salaried employee gets the maximum comm percentage.

SELECT employee_id, max(salary), max(commission_pct ) FROM employees E GROUP BY salary, commission_pct ;

SELECT employee_id, max(salary), max(commission_pct ) FROM employees E GROUP BY salary;

SELECT employee_id, max(salary) FROM employees E GROUP BY salary, commission_pct HAVING max(commission_pct ) = 100;

SELECT employee_id, (SELECT max(salary) FROM employees) * (SELECT max(commission_pct ) FROM employees) FROM DUAL;

Answer: D. A single-row subquery can also be nested in the outer query”s SELECT clause. In this case, the value the subquery returns is available for every row of output the outer query generates. Typically, this technique is used to perform calculations with a value produced from a subquery.

90.What is true about the sub-queries used in the SELECT clause of an SQL statement?

These sub-queries are the same in all aspects as those used in the FROM or WHERE clauses

These sub-queries have to mandatorily be single row sub-queries

We can use multi row operators when writing such sub-queries

None of the above

Answer: B.

91.What will be the outcome of the following query? (Consider the table structure as given)

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)

SELECT sysdate, (SELECT max(salary) FROM employees GROUP BY department_id ) FROM DUAL;

It gives the system date and the maximum salary for each department

It gives the maximum salary for all the departments

It throws an ORA error

It executes successfully with 0 rows

Answer: C. A Multi row sub-query cannot be used in the SELECT clause of an SQL statement. Only a single-row subquery can be nested in the outer query”s SELECT clause.

Examine the given table structure. Consider the following query and answer the questions 92 to 95 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)

SELECT salary FROM employees WHERE salary > ALL (10, 20, 30);

92.Which of the following queries are equivalent to the above query?

SELECT salary FROM employees WHERE salary >10 or salary > 20 and salary >30;

SELECT salary FROM employees WHERE salary <10 and salary < 20 and salary <30;

SELECT salary FROM employees WHERE salary >10 and salary > 20 and salary >30;

SELECT salary FROM employees WHERE salary >10 and salary > 20 or salary < 30;

Answer: C. The question shows the ALL clause in a simplified manner when it is followed by a list.

93. If in the above query the list (10,20,30) is replaced by a sub-query, which of the following queries will give the required output for the department number 100?

SELECT E.salary FROM employees E WHERE E.salary > (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary >ALL (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary = (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary >= (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

Answer: B. The question shows the ALL clause in a simplified manner when it is followed by a sub-query

94.With respect to the question 14 above, what among the following will be an equivalent query if ALL has to be replaced with ANY?

SELECT E.salary FROM employees E WHERE NOT EXISTS (E.salary =ANY (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary >ANY (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary =ANY (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE NOT ( E.salary <= ANY (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100));

Answer: D. The NOT operator used while using ”<= ANY” is used for negation of the results returned by the sub-query

95.With respect to the question 94, if the operator ANY is not to be used, which of the following queries will be correct?

SELECT E.salary FROM employees E WHERE NOT EXISTS (E.salary = ANY (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE NOT EXISTS (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100 And E.salary <= E1.salary);

Either A or B

None of the above

Answer: B. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present. An Uncorrelated subquery executes the subquery first and passes the value to the outer query.

Examine the given table structures. Consider the following query and answer the questions 96 to 98 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)

SELECT salary FROM employees WHERE salary > ANY (10, 20, 30);

96. Which of the following queries are equivalent to the above query?

SELECT salary FROM employees WHERE salary >10 or salary > 20 and or >30;

SELECT salary FROM employees WHERE salary <10 and salary < 20 and salary <30;

SELECT salary FROM employees WHERE salary >10 and salary > 20 or salary >30;

SELECT salary FROM employees WHERE salary >10 and salary > 20 or salary < 30;

Answer: A. The question shows the ANY clause in a simplified manner when it is followed by a list.

97. In the above query, if the list (10, 20, 30) is replaced by a sub-query, which of the following queries will give the required output for the department number 100?

SELECT E.salary FROM employees E WHERE E.salary > (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary >ANY (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary = (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE E.salary >= (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

Answer: B. The question shows the ANY clause in a simplified manner when it is followed by a sub-query

98.With respect to the question 97 above, what among the following will be an equivalent query if ANY is removed?

SELECT E.salary FROM employees E WHERE NOT EXISTS (E.salary =ANY (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

SELECT E.salary FROM employees E WHERE EXISTS (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100 And E.salary >E1.salary);

SELECT E.salary FROM employees E WHERE EXISTS (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100 );

SELECT E.salary FROM employees E WHERE IN (SELECT E1.salary FROM employees E1 WHERE E1.department_id = 100);

Answer: B. The EXISTS operator can substitute the ANY operator. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present.

99.Examine the given table structure. How many rows will get generated if the sub-query mentioned returns 0 rows?

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)

SELECT E.salary FROM employees E WHERE E.salary > ANY ( select E1.salary FROM employees E1 where E1.department_id = 100);

1 row

No rows

Either A or B

None of the above

Answer: B. If the sub-query returns zero rows, the ”> ANY” condition evaluates to FALSE, hence “No rows” are returned.

100. A subquery must be placed in the outer query”s HAVING clause if:

The inner query needs to reference the value returned to the outer query.

The value returned by the inner query is to be compared to grouped data in the outer query.

The subquery returns more than one value to the outer query.

None of the above. Subqueries can”t be used in the outer query”s HAVING clause.

Answer: B. A HAVING clause is used when the group results of a query need to be restricted based on some condition. If a subquery”s result must be compared with a group function, you must nest the inner query in the outer query”s HAVING clause.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL – Using the Group Functions nhận dự án làm có lương
SQL – Using the Group Functions Questions
1. Which of the following is NOT a GROUP BY function?

MAX

MIN

NVL

AVG

Answer: C. NVL is a general function used to provide alternate value to the NULL values. The functions MAX, MIN and AVG can be used as GROUP BY functions.

2. Which of the following functions can be used without GROUP BY clause in SELECT query?

COUNT

MAX

MIN

AVG

Answer: A, B, C, D. All the listed group functions can be used in a query provided no other columns are selected in the SELECT query.

3. Which of the following SELECT query returns the department number with maximum salary compensated to an employee? (Consider the table structure as given)

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)

SELECT department_id , max(salary ) FROM employees ;

SELECT department_id , max(salary ) FROM employees GROUP BY department_id ;

SELECT max(salary ) FROM employees GROUP BY department_id ;

SELECT max(salary ) FROM employees ;

Answer: B. The MAX function can be used to return the maximum salary in a department where each group is formed by a department.

4. Which of the following statements are true about the COUNT function?

The COUNT function counts the number of rows

The COUNT(*) function counts the number of rows with duplicates and NULL values

The COUNT(DISTINCT) function counts the number of distinct rows

COUNT(*) is equivalent to COUNT(ALL)

Answer: B. The COUNT(*) counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.

5. What are the appropriate data types accepted by GROUP BY functions?

Nested Tables

NUMBER

CLOB

DATE

Answer: B. The data types for the functions with an argument may be CHAR, VARCHAR2, NUMBER or DATE.

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

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

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

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

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

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

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

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

10. What happens when the below query is executed in SQL* Plus?

SELECT COUNT() FROM dual;

Executes successfully and returns no output

Executes successfully and returns output as ”1”

Throws exception “ORA-00909: invalid number of arguments”

Throws exception “ORA-00904: “COUNT”: invalid identifier” because COUNT function doesn”t works with DUAL table

Answer: C. COUNT function requires minimum one argument which can be either the column with [ALL | DISTINCT] modifier or ”*”.

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

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

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

GROUP BY

GROUPING SETS

CUBE

ROLLUP

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

13. Select the correct statements about the below query. Consider the table structure as given.

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)

SELECT department_id , SUM(salary ) FROM employees GROUP BY department_id ;

SUM is a group by function because it processes group of employees working in a department

SUM is an aggregate function because it produces one result per group of data

SUM is a single row function because it returns single value for a group i.e. department

SUM is a group by extension function because it uses GROUP BY clause to logically group the departments

Answer: A. SUM is a group function which calculates the sum of salaries of a group of employees working in a department.

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

WHERE

LIMIT

GROUP WHERE

HAVING

Answer: 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 applied to the groups in the SELECT list.

15. Examine the given table structure and predict the outcome of the following query.

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)

SELECT count(*) FROM employees WHERE comm = NULL;

The query returns the number of employees who have no commission

The query throws error because equal sign cannot be used when searching for NULL value

The query returns the number of employees in a department whose commission is NULL value

The query throws error because GROUP BY clause is missing in the query

Answer: B. Excluding out NULLs using WHERE condition is a way to direct the query to ignore NULLs. But here the usage of IS NULL operator is wrong. The condition should be ”WHERE comm IS NULL”.

16. Which of the following statements is true about the group functions?

The MIN function can be used only with numeric data.

The MAX function can be used only with date values.

The AVG function can be used only with numeric data.

The SUM function canít be part of a nested function.

Answer: C. The AVG function can be only used with numeric values. Other functions which have such restriction are SUM, STDDEV and VARIANCE.

17. Which of the following is a valid SELECT statement?

SELECT AVG(retail-cost) FROM books GROUP BY category;

SELECT category, AVG(retail-cost) FROM books;

SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category;

SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;

Answer: A. Column aliases cannot be used in GROUP BY or HAVING clause.

18. Which of the following statements is correct?

The WHERE clause can contain a group function only if the function isnít also listed in the SELECT clause.

Group functions canít be used in the SELECT, FROM, or WHERE clauses.

The HAVING clause is always processed before the WHERE clause.

The GROUP BY clause is always processed before the HAVING clause.

Answer: D. Though Oracle doesn”t raise error if HAVING clause precedes the GROUP BY clause but it is processed only after the GROUP BY clause is processed and group are ready to be filtered.

19. Which of the following is not a valid SQL statement?

SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;

SELECT MIN(pubdate) FROM books WHERE category = ''COOKING

SELECT COUNT(*) FROM orders WHERE customer# = 1005;

SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;

Answer: A.

20. Which of the following statements is correct?

The COUNT function can be used to determine how many rows contain a NULL value.

Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause.

The WHERE clause restricts which rows are processed.

The HAVING clause determines which groups are displayed in the query results.

Answer: C, D. The WHERE clause restricts the rows before they are grouped and processed while HAVING clause restricts the groups.

21. Which of the following is a valid SQL statement?

SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001;

SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;

SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#);

SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;

Answer: B. The GROUP BY clause must contain all the columns except the one which is used inside the group function.

22. Which of the following SELECT statements lists only the book with the largest profit?

SELECT title, MAX(retail-cost) FROM books GROUP BY title;

SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost);

SELECT title, MAX(retail-cost) FROM books;

None of the above

Answer: A.

23. Which of the following statement(s) is/are correct?

1. A group function can be nested inside a group function.

2. A group function can be nested inside a single-row function.

3. A single-row function can be nested inside a group function.

1

2

3

1 and 3

Answer: A, B, C. Group functions can be nested only to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In addition, single-row functions can be nested inside group functions.

24. Which of the following functions is used to calculate the total value stored in a specified column?

COUNT

ADD

TOTAL

SUM

Answer: D. SUM function is used to get the addition of numeric values.

25. Which of the following SELECT statements lists 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

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

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

SUM

NVL

MAX

MIN

Answer: 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.

27. Which of the following is not a valid statement?

You must enter the ALL keyword in a group function to include all duplicate values.

The AVG function can be used to find the average calculated difference between two dates.

The MIN and MAX functions can be used on VARCHAR2 columns.

All of the above

Answer: A. The ALL keyword counts duplicates but ignores NULLs. Duplicates are also included with ”*” and column name specification.

28. Which of the following SQL statements determines how many total customers were referred by other customers?

SELECT customer#, SUM(referred) FROM customers GROUP BY customer#;

SELECT COUNT(referred) FROM customers;

SELECT COUNT(*) FROM customers;

SELECT COUNT(*) FROM customers WHERE referred IS NULL;

Answer: B. Considering all customers as one group, COUNT(referred) will count only those who are referred by someone. COUNT(referred) will ignore NULL values of the column.

29. Determine the correct order of execution of following clauses in a SELECT statement.

1.SELECT

2.FROM

3.WHERE

4.GROUP BY

5.HAVING

6.ORDER BY

2-3-4-5-1-6

1-2-3-4-5-6

6-5-4-3-2-1

5-4-2-3-1-6

Answer: A. Processing order starts from FROM clause to get the table names, then restricting rows using WHERE clause, grouping them using GROUP BY clause, restricting groups using HAVING clause. ORDER BY clause is the last one to be processed to sort the final data set.

30. Which of the below clauses is used to group a set of rows based on a column or set of columns?

HAVING

WHERE

GROUP BY

GROUPING

Answer: C. GROUP BY clause forms the groups of the data based on the column list specified.

31. Which of the following group functions can be used for population variance and population standard deviation problems?

VAR_POP

STDDEV_POP

VARIANCE

STDDEV_SASMP

Answer: A, B.

32. Select the positions in a SELECT query where a group function can appear.

SELECT statement

WHERE clause

ORDER BY clause

GROUP BY clause

Answer: A, C, D. Group functions can appear in SELECT, ORDER BY and HAVING clause. Oracle raises exception if group functions are used in WHERE or GROUP BY clauses.

33. Examine the structure of the EMPLOYEES table as given. Which query will return the minimum salary in each department?

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)

SELECT department_id , MIN (salary ) from EMPLOYEES ;

SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY department_id ;

SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY salary ;

SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY employee_id ;

Answer: B. MIN function returns the minimum salary in a group formed by department.

34. Examine the structure for the table EMPLOYEES and Interpret the output of the below query

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)

SELECT COUNT(*), COUNT(all comm) FROM employees ;

It throws error because only one aggregate function can be used in a query.

It throws error because GROUP BY clause is missing.

It executes successfully and returns same values for both.

It executes successfully where COUNT(*) including NULLs and COUNT(all comm) excluding NULLs.

Answer: D.

35. Which of the following are true about group functions?

You can use group functions in any clause of a SELECT statement.

You can use group functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.

You can mix single row columns with group functions in the column list of a SELECT statement by grouping on the single row columns.

You can pass column names, expressions, constants, or functions as parameter to an group function.

Answer: C. Group functions can be nested only to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In addition, single-row functions can be nested inside group functions.

36. Examine the structure of the table EMPLOYEES as given. You want to create a “emp_dept_sales” view by executing the following SQL statements.

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)

CREATE VIEW emp_dept_sales AS SELECT d.department_name , sum(e.salary ) FROM employees e, departments d where e.department_id =d.department_id GROUP by d.department_name ;

Which statement is true regarding the execution of the above statement?

The view will be created and you can perform DLM operations on the view

The view will not be created because the join statements are not allowed for creating a view

The view will not be created because the GROUP BY clause is not allowed for creating a view

The view will be created but no DML operations will be allowed on the view

Answer: D. Rules for Performing DML Operations on a View. You cannot add data through a view if the view includes group functions or a GROUP BY clause or DISTINCT keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view.

37. Which of the following statements are true regarding views?

A sub query that defines a view cannot include the GROUP BY clause

A view is created with the sub query having the DISTINCT keyword can be updated

A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table

A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

Answer: C, D. Rules for Performing DML Operations on a View. You cannot add data through a view if the view includes group functions or a GROUP BY clause or DISTINCT keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view.

38. Examine the table structure as given.

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

Which clause in the below SQL query generates error?

SELECT department_id , avg(salary ) FROM departments WHERE upper(job) in (''SALES'',''CLERK'') GROUP BY job ORDER BY department_id ;

WHERE

SELECT

ORDER BY

GROUP BY

Answer: D. GROUP BY clause must contain all the columns appearing in the SELECT statement. It raises error because JOB is not a selected column. It should have used DEPARTMENT_ID in placed of JOB.

39. Examine the table structure as given.

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)

Which of the below SELECT query will display the maximum and minimum salary earned by each job category?

SELECT job, MAX(salary ), MIN (salary ) FROM employees GROUP BY department_id ;

SELECT job, MAX(salary ), MIN (salary ) FROM employees GROUP BY job;

SELECT job, MAX(salary ), MIN (salary ) FROM employees ;

Two aggregate functions cannot be used together in SELECT statement.

Answer: B. More than one group function can appear in the SELECT statement.

40. Consider the table structure as given.

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)

Examine the error in the below query.

SELECT department_id FROM employees WHERE hiredate > ''01-JAN-1985'' AND COUNT(*) > 2 GROUP by department_id HAVING SUM (salary ) > 1000;

It executes successfully and generates the required result.

It produces an error because COUNT(*) should be specified in the SELECT clause also.

It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).

It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.

Answer: D. Group functions cannot be used in WHERE clause. The can appear in SELECT, HAVING and ORDER BY clause.

41. Examine the table structure as given.

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)

Predict the outcome of the below query

SELECT job, COUNT(employee_id ),sum(salary ) FROM employees GROUP BY job HAVING SUM (salary ) > 5000;

It executes successfully and lists the count of employees under each job category but ignores the HAVING clause since “salary ” is not in GROUP BY clause.

It throws error because HAVING clause is invalid.

It throws error because “salary ” is not included in the GROUP BY clause.

It executes successfully and lists the count of employees under each category having sum of salary greater than 5000.

Answer: D. The HAVING clause restricts the group results. COUNT function is used for counting while SUM is used for adding the numeric values.

42. What is true of using group functions on columns that contain NULL values?

Group functions on columns ignore NULL values.

Group functions on columns returning dates include NULL values.

Group functions on columns returning numbers include NULL values.

Group functions on columns cannot be accurately used on columns that contain NULL values.

Answer: A. Except COUNT function, all the group functions ignore NULL values.

43. Which of the following statetments are true about the usage of GROUP BY columns in a subquery?

Subqueries can contain GROUP BY and ORDER BY clauses.

Subqueries cannot contain GROUP BY and ORDER BY clauses.

Subqueries can contain ORDER BY but not the GROUP BY clause.

Subqueries cannot contain ORDER BY but can have GROUP BY clause.

Answer: A. Like the primary query, a subquery can contain a GROUP BY as well as ORDER BY clause.

Examine the table structure as given and answer the questions 44 to 49 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)

44. Predict the outcome of the below query

SELECT avg(max(salary )) FROM employees GROUP BY department_id HAVING avg(max(salary ))>100;

It executes successfully.

It gives an error because the HAVING clause is not valid.

It gives an error because the GROUP BY expression is not valid.

It gives an error because aggregate functions cannot be nested in SELECT statement.

Answer: B. The HAVING clause doesn”t allows nesting of aggregate functions.

45. Predict the output of the below query

SELECT avg(salary ), department_id FROM employees GROUP BY department_id ;

It gives error because an aggregate function cannot appear just after SELECT clause.

It gives error because GROUP BY clause is invalid.

It executes without errors but produces no output.

It executes successfully and gives average salary in each department.

Answer: D. Group functions can be used in any sequence (before or after the group by columns) in a SELECT query.

46. Predict the output of the below query

SELECT lower(job),avg(salary ) FROM employees GROUP BY upper(job);

It executes successfully and displays “job” in lower case.

It executes successfully but display “job” in original case.

It throws error because singe row and aggregate functions cannot be used together.

It throws error because case conversion in the SELECT list mismatches with the case conversion GROUP BY clause.

Answer: D. The function LOWER, being a single row function must be specified in the GROUP BY clause to base the grouping of EMPLOYEES data.

47. Which of the below query executes successfully?

SELECT employee_id , COUNT(hiredate-sysdate) FROM employees ;

SELECT AVG(salary ), MAX(salary ) FROM employees ;

SELECT AVG(salary ), MAX(salary ) FROM employees GROUP BY department_id ;

SELECT AVG(hiredate) FROM employees ;

Answer: B, C. The first query operates of the whole EMPLOYEES data while the second one processes the data in groups of department.

48. Identify the error in the below SELECT statement.

SELECT department_id , AVG (salary ) FROM employees GROUP BY department_id HAVING department_id > 10;

It executes successfully and displays average salary of departments higher than 10.

It throws error because non aggregated column cannot be used in HAVING clause.

It executes successfully but displays wrong result for the departments.

It throws error because HAVING clause must be placed before GROUP BY clause.

Answer: A. GROUP BY expressions can be used in HAVING clause to filter out the groups from the final data set.

49. Predict the output of the below query

SELECT department_id , AVG (salary ) FROM employees GROUP BY department_id HAVING (department_id >10 and AVG(salary )>2000);

It throws error because multiple conditions cannot be given in HAVING clause.

It throws error because a non aggregate column cannot be used in HAVING clause.

It executes successfully and displays average salary of department higher than 10 and greater than 2000.

It executes successfully but no result is displayed.

Answer: C. The HAVING clause can impose multiple conditions joined using AND or OR operator filter the groups.

50. Which of the following group functions can be used with DATE values?

AVG

MIN

SUM

COUNT

Answer: B, D. The group function AVG and SUM can be used with numeric data only.

51. Which of the following statements are true?

AVG and SUM can be used only with numeric data types.

STDDEV and VARIANCE can be used only with numeric data types.

MAX can be used with LONG data type.

MAX and MIN cannot be used with LOB or LONG data types.

Answer: A, B, D. The group functions AVG,SUM, VARIANCE and STDDEV can be used with numeric data only. None of the group functions can be used with LONG data type.

52. Examine the table structure as given.

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)

Identify the error in the below query.

SELECT department_id , avg(salary ), count(hiredate) FROM employees GROUP BY department_id ;

Multiple aggregate functions cannot be used in a single SELECT query

GROUP BY clause is invalid

COUNT function cannot be used with DATE values

No errors and it executes successfully

Answer: D.

53. Which of the following group function can be used with LOB data types?

MAX

MIN

COUNT

None of these

Answer: D. No aggregate function can be used with LOB data types.

54. Examine the table structure as given.

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)

Predict the output of the below two queries

Query – 1

SELECT avg(comm) FROM employees ;

Query – 2

SELECT avg(nvl(comm,0)) FROM employees ;

Both the queries produce same result

Query – 1 and Query – 2 produce different results because Query-1 considers NULL values of COMM and Query-2 substitutes NULL values of COMM with zero

Query – 1 produces error because COMM has NULL values

Query – 2 produces error because NVL cannot be nested with aggregate function.

Answer: B. The AVG function ignores NULL values while calculating the average of numeric data. AVG(column) will calculate average for only non null values. However, if NVL is used to substitute NULLs with a zero, all the values will be considered.

55. Choose the correct statements about the GROUP BY clause.

Column alias can be used in the GROUP BY clause.

GROUP BY column must be in the SELECT clause.

GROUP BY clause must appear together with HAVING clause a SELECT query.

GROUP BY clause must appear after WHERE clause in a SELECT query.

Answer: D. As per the processing sequence, the GROUP BY clause must appear after the WHERE clause in a SELECT query.

56. Examine the table structure as given.

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)

Predict the outcome of the below query

SELECT department_id ,avg(salary ) FROM employees GROUP BY department_id , job ORDER BY department_id ;

It throws error because GROUP BY column list doesn”t matches with SELECT column list.

It executes successfully and produces average salary of a job category in each department.

It executes successfully and produces average salary for a department in each job category.

It throws error because GROUP BY and ORDER BY clause have different list of columns.

Answer: B. Though GROUP BY clause implicitly sorts the groups, the GROUP BY and ORDER BY clauses can be used together in a query.

57. Which clause should you use to exclude group results in a query using group functions?

WHERE

HAVING

GROUP BY

ORDER BY

Answer: B. HAVING clause is used to restrict the groups.

Examine the table structure as given and answer the questions 58 and 59 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)

58. Predict the outcome of the below query

SELECT department_id ,avg(salary ) FROM employees HAVING avg(salary )>2000 GROUP BY department_id ORDER BY department_id

It executes successfully.

It throws error because HAVING clause precedes the GROUP BY clause.

It throws error because HAVING clause uses the aggregate function.

It executes but no results are displayed because HAVING clause precedes the GROUP BY clause.

Answer: A. HAVING clause can precede the GROUP BY clause but it is processed only after the group results are calculated.

59. Predict the outcome of the below query

SELECT department_id , COUNT(first_name ) FROM employees WHERE job IN (''SALESMAN'',''CLERK'',''MANAGER'',''ANALYST'') GROUP BY department_id HAVING AVG(salary ) BETWEEN 2000 AND 3000;

It returns an error because the BETWEEN operator cannot be used in the HAVING clause.

It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.

It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

It executes successfully.

Answer: D. The WHERE clause restricts the number of rows participating in group clause processing.

60. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?

The HAVING clause can be used with group functions in subqueries.

The WHERE clause can be used to exclude rows after dividing them into groups.

The WHERE clause can be used to exclude rows before dividing them into groups.

The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

Answer: A, C. WHERE and HAVING clause can be used together in a query. WHERE excludes the rows before group processing while HAVING restricts the groups.

Examine the table structure as given and answer the questions 61 and 62 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)

61. Predict the outcome of the below query.

SELECT department_id , avg(salary ) FROM employees HAVING avg(salary ) > min(salary ) GROUP BY department_id ;

It throws an error because the aggregate functions used in HAVING clause must be in SELECT list.

It throws an error because the HAVING clause appears before GROUP BY clause.

It displays the departments whose average salary is greater than the minimum salary of the department.

It displays the departments whose average salary is greater than the minimum salary of the organization.

Answer: C. Group functions can be used by HAVING clause to filter the groups.

62. Interpret the output of the below query.

SELECT SUM(AVG(LENGTH(first_name ))) FROM employees GROUP BY department_id ;

It calculates the sum of averages of length of employee”s name in each department.

It calculates the average length of employee”s name in each department.

It throws error because single row function cannot be used with group functions.

It throws error because group column DEPARTMENT_ID is not used in the SELECT list.

Answer: A. Group functions can be used with single row or general functions in the SELECT query.

63. Up to how many levels, the group functions can be nested?

1

2

3

No limits

Answer: B. Group functions can be nested maximum up to 2 levels. However, single row functions can be nested up to any number of levels.

64. What is the limit of number of groups within the groups created by GROUP BY clause?

1

2

3

No Limit

Answer: D. There is no limit to the number of groups and subgroups that can be formed.

65. Choose the correct statements about the HAVING clause.

The HAVING clause is an optional clause in SELECT statement.

The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause.

The HAVING clause can appear in a SELECT statement only if it uses a GROUP BY clause.

The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause.

Answer: A, C. HAVING clause can only appear in a query if GROUP BY clause is present, but vice versa is not true.

66. What is the output of the below query.

SELECT count(*) FROM dual GROUP BY dummy;

1

0

NULL

Throws error because group functions cannot be applied on DUAL table.

Answer: A. The DUAL table contains single column DUMMY of type CHAR(1) whose value is ”X”.

Based on the below scenario, answer the question from 67 to 74.

An organization has 14 employees who work on fixed salary of 1000. The company recruits 5 new employees whose salary is not yet fixed by the payroll department. However, during the month end processing, the HR payroll department generates several reports to reconcile the financial data of the organization. Examine the table structure as given.

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)

67. What is the output of the below query?

SELECT SUM (salary ) FROM employees ;

NULL

14000

19000

0

Answer: B. The SUM function adds the salaries of the employees.

68. What is the output of the below query?

SELECT AVG (salary ) FROM employees ;

1000

736.84

NULL

0

Answer: A. The AVG (salary ) function calculates the average of salaries and ignoring the NULL values. In this case, AVG(salary)=(14*1000)/14=1000.

69. What is the output of the below query?

SELECT AVG (nvl(salary ,0)) FROM employees ;

1000

NULL

736.84

0

Answer: C. The AVG(NVL(salary ,0)) gives an alternate value to the NULLs and enables them to participate in average calculation. In this case, (14*1000)/19 = 736.84.

70. What is the output of the below query?

SELECT VARIANCE (salary ) FROM employees ;

1000

0

NULL

204678.36

Answer: B. The VARIANCE (salary ) calculates the variance of salary column values ignoring NULLs.

71. What is the output of the below query?

SELECT VARIANCE (nvl(salary ,0)) FROM employees ;

1000

0

NULL

204678.36

Answer: D. The VARIANCE (NL(salary ,0)) calculates the variance of salary column values including NULLs.

72. What is the output of the below query?

SELECT STDDEV (salary ) FROM employees ;

1

1000

0

NULL

Answer: C. The STDDEV (salary ) calculates the standard deviation of salary column values ignoring NULLs.

73. What is the output of the below query?

SELECT STDDEV (nvl(salary ,0)) FROM employees ;

0

452.41

1000

NULL

Answer: B. The STDDEV (nvl(salary ,0)) calculates the standard deviation of salary column values including NULLs.

74. What is the output of the below query?
select count(*),count(salary ) from employees ;

19,19

14,19

19,14

14,14

Answer: C. COUNT(*) includes NULLs while COUNT(salary ) ignores NULL values.

75. Examine the table structure as given.

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)

Which of the below query will give the department who have more than 5 employees working in it?

SELECT department_id FROM employees WHERE COUNT(*) > 5 GROUP BY department_id ;

SELECT department_id FROM employees HAVING COUNT(*) > 5;

SELECT department_id FROM employees GROUP BY employee_id HAVING COUNT(*) > 5;

SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

Answer: D.

76. Which of the following are true about the CUBE extension of GROUP BY?

Enables performing multiple GROUP BY clauses with a single query.

Performs aggregations for all possible combinations of columns included.

Performs increasing levels of cumulative subtotals, based on the provided column list.

None of the above

Answer: B. CUBE, ROLLUP are the GROUP BY extensions used for OLAP processing. CUBE aggregates the results whenever a new permutation of column is formed.

Use the following SELECT statement to answer below questions 77 to 82:

1 SELECT customer#, COUNT(*) 2 FROM customers JOIN orders USING (customer#) 3 WHERE orderdate > ''02-APR-09'' 4 GROUP BY customer# 5 HAVING COUNT(*) > 2;

77. Which line of the SELECT statement is used to restrict the number of records the query processes?

1

3

4

5

Answer: B. WHERE clause is used to restrict the rows before the groups are formed.

78. Which line of the SELECT statement is used to restrict groups displayed in the query results?

1

3

4

5

Answer: D. HAVING is used to restrict the group results after the group processing is over.

79. Which line of the SELECT statement is used to group data stored in the database?

1

3

4

5

Answer: C. GROUP BY clause uses the group by columns to group the data in the table.

80. Which clause must be included for the query to execute successfully?

1

3

4

5

Answer: C. Because the SELECT clause contains the CUSTOMER# column, it is mandatory to have GROUP BY clause with the CUSTOMER# column.

81. What is the purpose of using COUNT(*) in the SELECT query?

The number of records in the specified tables

The number of orders placed by each customer

The number of NULL values in the specified tables

The number of customers who have placed an order

Answer: B. It counts the number of rows processing under a group. In this case, group is formed by the customer and COUNT(*) counts the orders placed by each customer.

82. Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?

COUNT function

MAX function

MIN function

STDDEV function

Answer: C. MIN function is used to retrieve the least value of the column. When used with date columns, it fetches the minimum date from the column.

83. Which of the following is not a valid SELECT statement?

SELECT STDDEV(retail) FROM books;

SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#;

SELECT order#, TO_CHAR(SUM(retail),''999.99'') FROM orderitems JOIN books USING (isbn) GROUP BY order#;

SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;

Answer: D. The GROUP BY clause must specify a column or set of columns contained in the SELECT clause. Here PUBID is not contained in the SELECT clause, hence the query is not valid.

84. Which of the below statements are true about the nesting of group functions?

The inner most function is resolved first.

Oracle allows nesting of group function up to 3 levels.

Single row functions can be nested with group functions.

Oracle allows nesting of group function up to 2 levels.

Answer: A, C, D. In an expression containing nested functions, the innermost function is executed first whose result is fed into the next function moving in outwards direction. Single row functions can be well used with group functions which can be maximum nested up to 2 levels.

85. What are the statistical group functions in Oracle?

AVG

STDDEV

VARIANCE

STATS

Answer: B, C. VARIANCE and STATS are the statistical group functions available in Oracle SQL.

86. If the SELECT list contains a column and a group functions, which of the following clause must be mandatorily included?

ORDER BY

HAVING

GROUP BY

None of these

Answer: C. GROUP BY clause should necessarily contain the column or set of columns contained in the SELECT clause.

87. Examine the table structure as given.

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)

What is the best explanation as to why this SQL statement will NOT execute?

SELECT department_id "Department", AVG (salary)"Average" FROM employees GROUP BY Department;

Salaries cannot be averaged as not all the numbers will divide evenly.

You cannot use a column alias in the GROUP BY clause.

The GROUP BY clause must have something to GROUP.

The department id is not listed in the departments table.

Answer: B. Neither GROUP BY clause nor HAVING clause works with column alias.

88. Which of the following data types are compatible with AVG, SUM, VARIANCE, and STDDEV functions?

Only numeric data types

Integers only

Any data type

All except numeric

Answer: A. The functions AVG, SUM, VARIANCE and STDDEV mandatorily work with numeric data type only.

Examine the table structure as given below and answer the questions 89 and 90 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)

89. Which of the below query will display the number of distinct job categories working in each department?

SELECT department_id , COUNT(DISTINCT job) FROM employees GROUP BY job;

SELECT department_id , COUNT(job) FROM employees GROUP BY employee_id ;

SELECT department_id , COUNT(job) FROM employees GROUP BY department_id ;

SELECT department_id , COUNT(DISTINCT job) FROM employees GROUP BY department_id ;

Answer: D. Use DISTINCT modifier to filter out the duplicates.

90. Evaluate this SQL statement:

SELECT employee_id , first_name , department_id , SUM(salary ) FROM employees WHERE salary > 1000 GROUP BY department_id , employee_id , first_name ORDER BY hiredate;

Why will this statement cause an error?

The HAVING clause is missing.

The WHERE clause contains a syntax error.

The SALARY column is NOT included in the GROUP BY clause.

The HIRE_DATE column is NOT included in the GROUP BY clause.

Answer: D. All the columns appearing in SELECT and ORDER BY clause must be included in the GROUP BY clause.

91. Which of the following statements is true about the GROUP BY clause?

To exclude rows before dividing them into groups using the GROUP BY clause, you use should a WHERE clause.

You must use the HAVING clause with the GROUP BY clause.

Column alias can be used in a GROUP BY clause.

By default, rows are not sorted when a GROUP BY clause is used.

Answer: A. Using a WHERE clause, you can exclude rows before dividing them into groups.

92. Examine the table structure as given.

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)

Interpret the outcome of the below query.

SELECT department_id , MIN (hiredate) FROM employees GROUP by department_id ;

The earliest hire date in the organization.

The latest hire date in the organization.

The earliest hire date in a department.

The latest hire date in a department.

Answer: C. The query returns the earliest hired employee in each department.

93. Which statement about group functions is true?

Group functions except COUNT(*), ignore null values.

A query that includes a group function in the SELECT list must include a GROUP BY clause.

Group functions can be used in a WHERE clause.

Group functions can only be used in a SELECT list.

Answer: A. All the group functions except COUNT(*), ignore NULL values. It is because they process the values directly contained in a specific column.

94. Which of the following clauses represent valid uses of group functions?

GROUP BY MAX(salary)

ORDER BY AVG(salary)

HAVING MAX(salary) > 10000

SELECT AVG(NVL(salary, 0))

Answer: B, C, D. Group functions can appear in SELECT, HAVING and ORDER BY clauses only.

95. Which of the following statements are true about the GROUP BY clause?

The last column listed in the GROUP BY clause is the most major grouping.

The first column listed in the GROUP BY clause is the most major grouping.

A GROUP BY clause cannot be used without an ORDER BY clause.

The GROUP BY clause do not ensure the sorting of output.

Answer: B. The grouping of data is based on the sequence of columns appearing in the GROUP BY clause.

96. What is difference between WHERE clause and HAVING clause?

WHERE clause restrict rows before grouping while HAVING clause restricts groups.

WHERE clause cannot contain a group function but HAVING clause can have.

WHERE clause can join multiple conditions using AND or OR operators but HAVING clause cannot.

WHERE clause can appear in SELECT query without GROUP BY clause but HAVING clause cannot.

Answer: A, B, D. WHERE clause restricts the rows before grouping but HAVING restricts the groups.

97. Examine the table structure as given.

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)

Predict the outcome of the below query.

SELECT department_id ,job,count(*) FROM employees GROUP BY department_id ,job ORDER BY department_id ,count(*);

It executes successfully.

It throws error because ORDER BY clause is invalid.

It throws error because GROUP BY clause is invalid.

It throws error because GROUP BY and ORDER BY clause cannot be used together.

Answer: A. ORDER BY clause can use the group functions for sorting.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL – Get Data from Multiple Tables nhận dự án làm có lương
SQL – Get Data from Multiple Tables Questions
1.Which of the following is not related to a Relational Database?

Selection

Projection

Joining

None of the above

Answer: D. The options A, B and C are the major capabilities of the Oracle Relational Database.

2.Which of the following methods is used for writing a query with columns from multiple tables?

SELECT

GROUP BY

ORDER BY

JOINS

Answer: D. Joins are used to connect multiple tables and project column data from multiple tables in Oracle.

3.Which of the following is one of the most common methods to join multiple tables?

Hash Join

Equijoin

Self Join

Cross Join

Answer: B. Equijoin is one of the types of joins which is the most common and simple technique for joining more than one tables. Equijoins are also called simple joins or inner joins.Equijoin involve primary key and foreign key.

4.Which of following will be used to join rows with other tables if the column values fall in a range defined by inequality operators?

Equijoin

Simple join

Non-equijoin

None of the above

Answer: C. Equijoins use equality operators to join rows, non-equijoins use inequality operators.

5.Which of the following statements is true about Oracle joins?

NULL values are included in the result set

Only the rows which have matching conditions are fetched

All the rows that are present in any of the one tables are fetched

None of the above

Answer: B. NULL values and differing entries in common join columns are excluded when joins are used.

6.Which of the following can be used to join the rows of a table with other rows of the same table?

Equijoin

Non-equijoin

Outer join

Self-join

Answer: D. The association is based on columns with logical and usually hierarchical relationships with each other.

7.What is true about a cartesian join of two tables in Oracle DB?

It must be avoided as it is costly and non optimized

It is formed when every row from one table is joined with all rows in the second table

Both A and B

None of the above

Answer: B. Cartesian join is often the result of missing or inadequate join conditions.It is simply the cross product of two tables.

8.Which of the following is one of the basic types of joins in Oracle DB ?

Outer join

Self-join

Equi-join

All of the above

Answer: C. Equi-join and non-equijoin are the two basic types of joins in Oracle DB.

9.What is the main condition for using joins between a source table and a target table in Oracle DB for getting a non-cartesian product result?

There is no condition

At least one of the columns in both the tables should be common.

The names of the columns in both the joining tables should be the same for using joins

None of the above

Answer: B. The tables must be connected through a common column relating two entities.The table joined on a common column produce non Cartesian product.

10. Which of the following can be used to fetch rows from multiple tables in a single SQL query?

SELECT

WHERE

FROM

Equi-joins

Answer: D. Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.

11.What is true about the source table and the target table in terms of Oracle Joins?

They must have atleast one column of same name

All the columns should be of the same name and same data type for joining the two tables

The source and the target tables cannot be swapped and are position specific

None of the above

Answer: D. The source and the target tables can be swapped and are not fixed at their positions.Depending of the type of join used in the query, the result may differ or remain same.

12.What is true about Natural joins in Oracle DB?

The column names of the source and the target tables should be identical

If the column names of the source and the target tables are not same, Oracle implicitly does the needful

NATURAL JOINS, USING and ON are the keywords associated with Natural Joins

All of the above

Answer: C. The keyword NATURAL JOIN instruct Oracle to identify columns with identical names between source and target tables. Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.

13.Assume the tables EMPLOYEES and DEPARTMENT have to be joined using NATURAL JOIN. What is the difference between the following two queries which follow? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT department_id FROM employees NATURAL JOIN department WHERE first_name = ''John'' AND last_name = ''Bacon

SELECT department_id FROM department NATURAL JOIN employees WHERE first_name = ''John'' AND last_name = ''Bacon

There is no difference

The result is different in both the cases

Both the queries will give an ORA error on execution

None of the above

Answer: B. The source and target tables can be swapped while using NATURAL JOIN giving relationally different result sets.

14.Which of the following options is true regarding the NATURAL JOIN in Oracle DB?

While using NATURAL JOIN mentioning the names of all the columns from both the tables is mandatory

NATURAL JOIN can be used only if the names of all the columns of both the tables are identical

The join in NATURAL JOIN happens only when the user specifies the columns of the source and the target tables.

There is no need to mention the columns when using NATURAL JOINS.

Answer: D. There”s an implicit joining of the columns from the source and the target tables when a NATURAL JOIN is used. A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined.Common columns are columns that have the same name in both tables.

15. What is the difference between a NATURAL JOIN and a join with JOIN..ON?

There is no difference between the both

JOIN..ON joins the source and target tables on specific columns with the same name

NATURAL JOIN implicitly joins all the matching columns from the source and target tables

None of the above

Answer: B, C. The join condition for the natural join is basically an equijoin of all columns with the same name.Use the ON clause to specify arbitrary conditions or specify columns to join.The join condition is separated from other search conditions. The ON clause makes code easy to understand.

16.What is true about the JOIN..ON clause in Oracle DB?

It does not depend on the columns in the source and target tables having identical names

Only those columns from the source and the target tables which have identical names can be used with this clause

It is a format of the NATURAL JOIN

All of the above

Answer: A, C. The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify arbitrary conditions or specify columns to join. The join condition is separated from other search conditions. The ON clause makes code easy to understand.

17. The database designer has named the key (unique) columns from two tables differently.While joining these tables, which among the following will be the best practice?

JOIN..ON

Either NATURAL JOIN or JOIN…ON clauses

Both A and B

None of the above

Answer: A. Using NATURAL JOINS in this case can yield unexpected results as there is an implicit searching of columns with identical names which in this case is not present.

18.What of the following can be used to fetch non-matching rows along with the matching rows between a source and a target table in Oracle DB?

EQUI-JOIN

SELF-JOIN

NATURAL JOIN

OUTER-JOIN

Answer: D. 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.

19. What are Cartesian Joins also known as in Oracle DB?

Equi-join

Anti-join

Cross-Join

None of the above

Answer: C. A Cartesian join between two tables returns every possible combination of rows from the tables. A Cartesian join can be produced by not including a join operation in the query or by using a CROSS JOIN.

20.What will be the result of a NATURAL JOIN between two tables EMPLOYEES and DEPARTMENT as given in the query below? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT * FROM employees NATURAL JOIN department;

The common column DEPARTMENT_ID with identical name in both the tables will appear twice in the result set

All the columns having identical names joined with the NATURAL JOIN, will appear twice in the result set

The result set will have only one column for each pair of identically named columns from both tables

None of the above

Answer: C. The NATURAL JOIN keywords don”t require a condition to establish the relationship between two tables. However, a common column must exist. Column qualifiers can”t be used with the NATURAL JOIN keywords.

21.What is the difference between a NATURAL JOIN and an EQUI-JOIN in Oracle DB?

There is no difference

They are the same with respect to the result set that is obtained from both

Both A and B

None of the above

Answer: D. NATURAL JOIN joins all the columns with identical names whereas EQUI-JOIN requires the columns to be explicitly mentioned in the SQL query.

22.What is an INNER JOIN in Oracle DB?

The join giving the matching records between two tables is called an INNER JOIN

An inner join can use operators like <,>, <>

Both A and B

None of the above

Answer: 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.

23.What is the difference between a INNER JOIN and an EQUI-JOIN in Oracle DB?

They are the same in terms of syntax and result sets obtained.

An INNER JOIN is a subset of an EQUI-JOIN

An INNER JOIN can use operators like <,>, <> along with “=” while EQUI-JOIN only uses the “=” operator

All of the above

Answer: C. EQUI-JOIN is a type of INNER JOIN containing “=” operator in a join condition, whereas the INNER JOIN can contain both equality as well non-equality operators

24.What is true about NATURAL JOINS in terms of ANSI SQL: 1999 syntaxes in Oracle DB?

An Equality operator (=) is used

They fetch different results when compared to the traditional syntax

The ANSI SQL syntax uses words like NATURAL JOIN in the SQL queries

None of the above.

Answer: C. ANSI SQL syntax is different from the traditional way of using (=) in the traditional ways. There are keywords like NATURAL JOIN etc. in the ANSI SQL syntax to distinguish the joins used.

25.What of the following is true with respect to the query given below? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT first_name, salary FROM employees e, departments d WHERE e.department_id (+) = d.department_id ;

There is an outer join between the department_id from both tables which is equivalent to a Right Outer join in ANSI SQL

There is an outer join between the department_id from both tables which is equivalent to a Left Outer join in ANSI SQL

It fetches all the records of the department_id from the employees table whether they match or not

It fetches all the records of the department_id from the department table whether they match or not

Answer: A, D. The condition e.department_id (+) = d.department_id means it will perform a Right Outer Join and all the department_id s from the department table will be displayed whether they match or not

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

Answer: 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

27.What of the following is true regarding the Cartesian product in Oracle DB?

If ”N” is the no of tables joined, then if no. of joins is N-1, Cartesian product is not performed

If ”N” is the no of tables joined, then if no. of joins is N, Cartesian product is performed

If ”N” is the no of tables joined, then if no. of joins is N+1, Cartesian product is performed

If ”N” is the no of tables joined, then if no. of joins is N-1 or less, Cartesian product is performed.

Answer: A. A Cartesian join between two tables returns every possible combination of rows from the tables. A Cartesian join can be produced by not including a join operation in the query or by using a CROSS JOIN. A query must have at least (N-1) join conditions to prevent a cartesian product, where N is the number of tables in the query.

28.What is the reason of error in the following SQL query? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT first_name, last_name FROM employees, departments WHERE department_id (+) = department_id ;

There are no aliases used for the tables.

The word RIGHT OUTER JOIN is not used here hence it throws the error

The (+) should be on the Right side of the equality condition and not on the left side

Table Aliases should be used with the department_id in the condition to remove ambiguous naming

Answer: D. Without the table aliases, Oracle is unable to derive the origin of the columns being joined and hence throws an Ambiguity error on execution.

29.Which of the following is used to avoid the ambiguous column problem in Oracle DB?

;

,

.

/

Answer: C. The syntax for removing the Ambiguous column issue is: table_alias.column_name

30.Which of the following is the most appropriate about the following query? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT employee_id , first_name, last_name FROM employees e right outer join department d On e.department_id = d.department_id ;

It gives the details of those employees who are not in any department

It gives the details of those departments which do not have any employee

It gives the details of all departments irrespective of whether they”ve any employee or not

It gives the details of the employees which were hired in the company ”ABC” irrespective of the departments.

Answer: C. With the JOIN method for outer joins, you can add the LEFT, RIGHT, or FULL keywords. A left outer join includes all records from the table listed on the left side of the join, even if no match is found with the other table in the join operation. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

31.What will be the outcome of the following query? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

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

It will give all the matching values from both the tables which have the same department_id

It will give all the columns from the table employees and only the top 100 rows from the departments table

It will give an ORA error: “b.department_id ” invalid identifier

None of the above options

Answer: C. The same aliases should in the WHERE clause as declared in the FROM clause

32.Which of the following is true regarding the USING and ON clauses in table joins? (Choose more than one options if applicable)

The ON clause can be used to join tables on columns with the same data type but not necessarily the same name

The USING and ON clauses are used on only equijoins and non-equijoins

Not more than one condition can be used with the ON clause

The WHERE clause can be written after the USING..ON clause to apply additional conditions

Answer: A, D. The JOIN … USING approach is similar to the NATURAL JOIN approach, except the common column is specified in the USING clause. A condition can”t be included in the USING clause to indicate how the tables are related. In addition, column qualifiers can”t be used for the common column specified in the USING clause. The JOIN… ON approach joins tables based on a specified condition. The JOIN keyword in the FROM clause indicates the tables to be joined, and the ON clause indicates how the two tables are related. This approach must be used if the tables being joined don”t have a common column with the same name in each table.

33.How many tables can be joined by using the JOINS in Oracle DB?

1

2

255

No limit

Answer: D. There is currently no limit on the number of tables participating in a join.

34. What is true when multiple joins are used in an SQL statement?

The joins are evaluated from left to right

The joins are evaluated from right to left

There is no precedence in the process of the evaluation of joins

None of the above

Answer: A. When multiple-joins exist in a statement, they are evaluated from left to right.

35.What is true with respect to the following query? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SQL> desc bonus Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2)

SELECT bonus, first_name, department_id FROM bonus b NATURAL JOIN employees e NATURAL JOIN department d;

The use of NATURAL JOIN to join multiple tables is a better option and yields accurate results

The error probability is very less when NATURAL JOINs are used to join multiple tables

The clauses USING..JOIN..ON give more accurate results than NATURAL JOINs when joining multiple tables

Oracle implicitly joins multiple tables when a NATURAL JOIN is used and hence it is a good practice to use NATURAL JOINS

Answer: C. The use of NATURAL JOINS can create Cartesian products of rows and also are error prone with non-dependable result sets.

36.What is true about the clauses JOIN..ON in Oracle DB?

They are not very dependable as compared to NATURAL JOINS when joining multiple tables

The JOIN..ON clause is similar to the WHERE clause which limits rows with conditions

An additional WHERE clause is mandatory when the JOIN..ON clause is used

None of the above

Answer: B. The JOIN …. ON approach joins tables based on a specified condition. The JOIN keyword in the FROM clause indicates the tables to be joined, and the ON clause indicates how the two tables are related. This approach must be used if the tables being joined don”t have a common column with the same name in each table.

Examine the table structures as given. Answer the questions 37 and 38 that follow the query given below:

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT e.salary, d.department_id FROM employees e JOIN department d On (e.department_id = d.department_id and e.last_name = ''Brandon'');

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

Answer: 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

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

Answer: C, D.

39.What is true about Non-equijoins in Oracle DB?

They join based on the keyword NON-EQUI JOIN

They are used using the JOIN..ON clause with “=” sign

The results are obtained when the result of the inequality mentioned evaluates to true.

None of the above

Answer: C. The non-equi joins are used with the JOIN..ON clause but with inequality operators.

Examine the structures of the tables EMPLOYEES and DEPARTMENTS as given and answer the questions 40 and 41 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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

40.What will be the outcome of the following query in Oracle DB?

SELECT e.first_name, e.last_name, e.employee_id FROM employees e JOIN department d ON (e.salary BETWEEN 1000 AND 10000);

It will throw an ORA error as the condition in the ON clause is incorrect.

It will throw an ORA error due to a syntax error as there is no Equality sign “=” in the ON clause

It will execute successfully and give the first name, last name and employee ID of employees with the condition mentioned.

Non-equi joins can only be used for showing in-equalities and not ranges.

Answer: C.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) AND d.loc = upper (''Geneva'');

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000);

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000) AND d.loc = ''Geneva

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) WHERE d.loc = upper(''Geneva'');

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

The result of the inequality match operation is true

The result of the inequality match operation is 0

The result of the inequality match operation is 1

The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN … USING, or JOIN … ON keywords.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) AND d.loc = upper (''Geneva'');

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000);

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000) AND d.loc = ''Geneva

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) WHERE d.loc = upper(''Geneva'');

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

The result of the inequality match operation is true

The result of the inequality match operation is 0

The result of the inequality match operation is 1

The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN … USING, or JOIN … ON keywords.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) AND d.loc = upper (''Geneva'');

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000);

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000) AND d.loc = ''Geneva

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) WHERE d.loc = upper(''Geneva'');

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

The result of the inequality match operation is true

The result of the inequality match operation is 0

The result of the inequality match operation is 1

The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN … USING, or JOIN … ON keywords.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) AND d.loc = upper (''Geneva'');

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000);

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >=20000) AND d.loc = ''Geneva

SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary >20000) WHERE d.loc = upper(''Geneva'');

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

The result of the inequality match operation is true

The result of the inequality match operation is 0

The result of the inequality match operation is 1

The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN … USING, or JOIN … ON keywords.

43.What is true regarding a Self-Join in Oracle DB?

Only two tables are required for the join to work

The columns in the result set are obtained from two tables but are displayed in one table

Conceptually, the source table duplicates itself to create the target table. (Oracle doesn”t duplicate tables)

All of the above

Answer: C. Self-joins are used when a table must be joined to itself to retrieve the data you need. Table aliases are required in the FROM clause to perform a self-join.

44. With respect to the query and the table structure given below,answer the question.

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)

SELECT * FROM employees a join employees b WHERE a.employee_id = b.employee_id ;

Which of the following tables does Oracle treat as source table and target table?

a is source and b is target

b is source and a is target

Oracle doesn”t treat any of the tables as source or target

None of the above

Answer: A. The first occurrence of employees table is taken as source and the subsequent occurrences as b, c and so on.

45.In what scenarios can we use Self-Joins ideally in Oracle DB?

When we need to find the duplicate records in a table

When we need to obtain NULL values from a table

When we need to display a column of a table twice

When we need to display hierarchy of relevant entities

Answer: D.

46. What is true about NATURAL JOINS in Oracle DB?

The joined columns have the same name but can have different data types

The joined columns can have the same data type but different names

The joined columns should have identical names and the same data type

None of the above

Answer: C. The NATURAL JOIN keywords don”t require a condition to establish the relationship between two tables. However, a common column must exist.Column qualifiers can”t be used with the NATURAL JOIN keywords.

47.A report has to be extracted which gives the department name, department ID, department city and location ID only for departments 100 and 101. Using NATURAL JOINS, which of the following options will give the required results?

SELECT department_id , department_name ,location, city FROM departments NATURAL JOIN locations WHERE department_id in (100,101);

SELECT department_id , department_name ,location, city FROM locations NATURAL JOIN departments WHERE department_id BETWEEN 100 AND 101;

SELECT department_id , department_name ,location, city FROM departments NATURAL JOIN locations WHERE department_id >100 AND department_id >101;

SELECT department_id , department_name ,location, city FROM departments NATURAL JOIN locations ;

Answer: A. The WHERE can be used for additional conditions after the NATURAL JOIN clause.

48.In which of the following scenarios shall a USING clause or a NATURAL JOIN clause be used?

When the names of the columns from the tables are identical then use USING clause

When the data types of the columns from the tables are identical then use NATURAL JOINS

If several columns have the same names but the data types do not match, USING can be used

NATURAL JOINS should be used only when the column names and their data types are the same

Answer: C, D. NATURAL JOINS and USING are mutually exclusive, the USING clause should be used to match only one column when more than one columns match.

49.Examine the table structures given. What will be the outcome of the following query? (Choose the most appropriate answer)

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)

SQL> desc bonus Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2)

SELECT e.name, b.bonus FROM employees e JOIN bonus b USING (job_id) WHERE e.job_id like ''SA%'');

It gives the names and bonus obtained by all the employees in some company

It gives the names and bonus obtained by all the employees in a particular job title in a company

It executes successfully giving all the names and the bonus obtained by all the employees in all jobs

It throws an ORA error.

Answer: D. The column(s) used in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

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

Answer: 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.

51. What is true regarding FULL OUTER JOIN in Oracle DB?

When both LEFT OUTER JOIN and RIGHT OUTER JOIN appear in the same query,it is called a FULL OUTER JOIN

A FULL OUTER JOIN is the same as an OUTER JOIN

Both A and B

A join between two tables that returns the results of an INNER join and a LEFT and RIGHT OUTER JOIN is called a FULL OUTER JOIN

Answer: D. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

Examine the given table structures and answer the questions 52 and 53 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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SQL> desc bonus Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2)

52.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 JOIN..USING 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

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

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

Answer: 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.

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

Cartesian products

Natural joins

Full OUTER join

Equijoins

Answer: D.

55.Which of the following is not a format for Outer Joins in Oracle DB?

Right

Left

Centre

Full

Answer: C. Except ”Centre”, rest 3 types are the types of formats of the Outer Joins in Oracle DB. With the JOIN method for outer joins, you can add the LEFT, RIGHT, or FULL keywords.

Examine the given table structures. Answer the questions 56, 57 and 58 that follow by referring to the following query:

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT * FROM employees e NATURAL JOIN department d;

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

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

57.You need to find the results obtained by the above query for all those employees who have salaries greater than 20000. Which of the following clauses should be added / modified to the above query?

ON (e.department_id = d.department_id ) WHERE salary > 20000;

USING (e.department_id ) WHERE salary > 20000;

USING (department_id ) WHERE salary>20000;

WHERE salary >20000;

Answer: D.

58.If the NATURAL JOIN in the above query is replaced by only JOIN which of the following should be added / modified to the above query to give the results pertaining to Department 100?

ON (department_id = 100);

USING (e.department_id =100);

WHERE d.department_id = 100;

ON (e.department_id = d.department_id and d.department_id = 100);

Answer: D. The equi-joins can be added for more conditions after the ON clause.

59.A report has to be extracted to get the Managers for all the employees in the departments 10 and 20 of a company ”ABC”. Which of the following queries will give the required results? (Consider the table structure as given)

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)

SELECT a.first_name || '' ''||a.last_name "Manager", b.first_name||'' ''||b.last_name "Employees" FROM employees a join employees b On (employee_id );

SELECT a.first_name || '' ''||a.last_name "Manager", b.first_name||'' ''||b.last_name "Employees" FROM employees a join employees b On (b.employee_id = a.employee_id );

SELECT a.first_name || '' ''||a.last_name "Manager", b.first_name||'' ''||b.last_name "Employees" FROM employees a join employees b On (a.manager_id = b.employee_id ) WHERE department_id in (10,20);

SELECT a.first_name || '' ''||a.last_name "Manager", b.first_name||'' ''||b.last_name "Employees" FROM employees a join employees b On (a.manager_id = b.employee_id ) WHERE a.department_id in (10,20);

Answer: D. The option C is incorrect because the non-aliased department_id in the WHERE clause will throw an error.

60.Which of the following queries will give results without duplicate values between the two tables EMPLOYEES and DEPARTMENT? (Consider the table structures as given)

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SELECT * FROM employees e NATURAL JOIN department d;

SELECT * FROM employees e JOIN department d;

SELECT * FROM employees e NATURAL JOIN department d USING (e.department_id );

SELECT * FROM employees e FULL OUTER JOIN department d USING (department_id );

Answer: D. The FULL OUTER JOIN will give all the matching as well non-matching rows from both the tables excluding duplicate values.

Examine the structures for the tables as given here and answer the questions 61 to 64.

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)

SQL> desc bonus Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2)

SQL> desc locations Name Null? Type ----------------------- -------- ---------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_ID CHAR(2)

SQL> desc locations Name Null? Type ----------------------- -------- ---------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_ID CHAR(2)

61.What is true about the following query? (Choose the most appropriate answer)

SELECT * FROM bonus b, employees e WHERE b.job_id (+) = e.job_id ;

It will display all the bonuses obtained by all the employees.

It will display NULL for the bonus column if a particular employee has not received any bonus

Both A and B

None of the above

Answer: B. The (+) is on the LHS of the equation means it is a RIGHT OUTER JOIN and vice versa.

62.You have to list all the departments who have no employees yet in a company named ”XYZ”. Which of the following queries will give you the required results?

SELECT department_id , department_name FROM departments d NATURAL JOIN employees e;

SELECT department_id , department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id );

SELECT department_id , department_name FROM employees e LEFT OUTER JOIN departments d USING (department_id );

SELECT department_id , department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id );

Answer: D.

63.You need to extract a report which displays ”No department yet” for all those employees who are yet to be allocated to a department. Which of the following will fulfill the purpose?

SELECT nvl(department_id ,''No department yet'') FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id );

SELECT nvl(department_id ,''No department yet'') FROM departments d LEFT OUTER JOIN employees e ON (e.department_id = d.department_id );

SELECT nvl(department_id ,''No department yet'') FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id );

SELECT nvl(department_id ,''No department yet'') FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id );

Answer: C.

64.You need to extract a report which displays all the departments which have not been assigned to a city yet. Which of the following queries will give you the required output?

SELECT department_id , department_name FROM departments d NATURAL JOIN locations l;

SELECT department_id , department_name FROM departments d FULL OUTTER JOIN locations l ON (d.location_id = l.location_id);

SELECT d.department_id , d.department_name FROM departments d JOIN locations l USING (location_id);

SELECT department_id , department_name FROM departments d LEFT OUTER JOIN locations l ON (d.location_id = l.location_id);

Answer: D.

65.In which two cases an OUTER JOIN should be used?

If the joined tables” columns have NULL values

If the joined tables have NOT NULL columns

If the joined tables have only un-matched data

If the joined tables have both matching as well as non-matching data

Answer: A, D. 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.

66.You need to find the salary grade obtained by each employee. Which of the following query will you use? (Consider the table structures as given)

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)

SQL> desc grade Name Null? Type ----------------------- -------- ---------------- GRADE NUMBER LOSAL NUMBER HISAL NUMBER

SELECT employee_id , salary, grade FROM employees e JOIN grade g ON g.grade BETWEEN g.losal AND g.hisal

SELECT employee_id , salary, grade FROM employees e FULL OUTER JOIN grade g WHERE g.grade > g.losal AND < g.hisal;

SELECT employee_id , salary, grade FROM employees e JOIN grade g ON (MIN(g.grade) = g.losal AND MAX(g.grade) = g.hisal);

None of the above

Answer: A.

67.Examine the table structures given.

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)

SQL> desc bonus Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2)

Evaluate this SQL statement:

SELECT e.employee_id , (.25* e.salary) + (.5 * e.commission_pct) + (.75 * b.bonus) as calc_val FROM employees e, bonus b WHERE e.employee_id = b.employee_id ;

What will happen if you remove all the parentheses from the calculation?

The value displayed in the calc_val column will be lesser.

The value displayed in the calc_val column will be higher.

There will be no difference in the calc_val column.

An error will be reported.

Answer: C.

68.Consider the exhibit and examine the structures of the EMPLOYEES, DEPARTMENTS, and GRADE tables. For which situation would you use a non-equijoin query?

To find the grade for each of the employees

To list the name, job_id, and manager name for all the employees

To find the department name of employees.

To find the number of employees working for the Administrative department and earning less than 30000

Answer: A. A non-equality join establishes a relationship based on anything other than an equal condition. Range values used with non-equality joins must be mutually exclusive.

69.In which three cases would you use the USING clause? (Choose three.)

You want to create a non-equijoin.

The tables to be joined have multiple NULL columns.

The tables to be joined have columns of the same name and different data types.

You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.

Answer: C, D. The JOIN …. USING approach is similar to the NATURAL JOIN approach, except the common column is specified in the USING clause. A condition can”t be included in the USING clause to indicate how the tables are related. In addition, column qualifiers can”t be used for the common column specified in the USING clause.

70.If the tables EMPLOYEES and BONUS have two columns with identical names viz: – SALARY and JOB_ID, which of the following queries are equivalent to each other? (Consider the table structures as given)

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)

SQL> desc bonus Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2)

SELECT * FROM employees E JOIN bonus B on (salary, job_id )

SELECT * FROM employees E NATURAL JOIN bonus B on (salary, job_id )

SELECT * FROM employees E JOIN bonus B USING (salary, job_id )

SELECT * FROM employees E JOIN bonus B on (salary, job_id )

Answer: B, C.

71.Examine the table structures as given.

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

Examine the following two SQL statements:
Query 1
SELECT first_name,department_id FROM employees E JOIN departments D USING (department_id );
Query 2
SELECT first_name,department_id FROM employees E NATURAL JOIN departments D USING (department_id );

Which statement is true regarding the outcome?

Only query 1 executes successfully and gives the required result.

Only query 2 executes successfully and gives the required result.

Both queries 1 and 2 execute successfully and give different results.

Both queries 1 and 2 execute successfully and give the same required result.

Answer: D.

72.You need to generate a report showing the department location along with the employee name for all hires made before 20th January, 2013.

You issue the following query:

SELECT department_name , first_name||'' ''||last_name FROM employees E JOIN department d ON ( hire_date < ''20-JAN-2013'') JOIN locations L ON (l.location_id = d.location_id) ;

Which statement is true regarding the above query?

It executes successfully and gives the required result.

It executes successfully but does not give the required result.

It produces an error because the join order of the tables is incorrect.

It produces an error because equijoin and non-equijoin conditions cannot be used in the same SELECT statement.

Answer: B.

73.Examine the structure of the EMPLOYEES table:

You want to find out if any employee” details have been entered more than once using different EMPLOYEE_ID , by listing all the duplicate names. Which method can you use to get the required result?

self-join

full outer-join with self-join

left outer-join with self-join

right outer-join with self-join

Answer: A. Self-joins are used when a table must be joined to itself to retrieve the data you need. Table aliases are required in the FROM clause to perform a self-join.

Examine the structure of the tables DEPARTMENTS and LOCATIONS and answer the questions 74 and 75 that follow.

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SQL> desc locations Name Null? Type ----------------------- -------- ---------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_ID CHAR(2)

74.You need to find out the departments that have not been allocated any location. Which query would give the required result?

SELECT d.department_id , d.department_name FROM departments d JOIN locations l ON (d.location_id = l.location_id);

SELECT d.department_id , d.department_name FROM departments d RIGHT OUTER JOIN locations l ON (d.location_id = l.location_id);

SELECT d.department_id , d.department_name FROM departments d FULL JOIN locations l ON (d.location_id = l.location_id);

SELECT d.department_id , d.department_name FROM departments d LEFT OUTER JOIN locations l ON (d.location_id = l.location_id);

Answer: B.

75.You want to list all departments that are not located in any location along with the department name. Evaluate the following query:

SELECT d.department_id , d.department_name ,l.location_id, l.city FROM departments D __________________ location L ON (d.location_id = l.location_id);

Which two JOIN options can be used in the blank in the above query to give the correct output?

JOIN

NATURAL JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

Answer: A, C.

76. You need to generate a report that shows all department IDs, with corresponding employees (if any) and bonus details (if any), for all employees. Which FROM clause gives the required result? (Consider the table structures as given)

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)

SQL> desc bonus Name Null? Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2)

FROM departments LEFT OUTER JOIN employees USING (department_id ) FULL OUTER JOIN bonus

FULL OUTER JOIN department USING (department_id );

FROM bonus JOIN employees USING (job_id )

FROM employees FULL OUTER JOIN departments FULL OUTER JOIN bonus

Answer: A.

77. Examine the following exhibits:

You want to generate a report listing the employees” IDs and their corresponding commissions and departments (if any), if the commissions exist or not. Evaluate the following query:

SELECT e.employee_id , bonus, department_name FROM bonus b_____________ employees USING (job_id ) ____________ departments USING (department_id ) WHERE commission_pct IS NOT NULL;

Which combination of joins used in the blanks in the above query gives the correct output?

JOIN; LEFT OUTER JOIN

FULL OUTER JOIN; FULL OUTER JOIN

RIGHT OUTER JOIN; LEFT OUTER JOIN

LEFT OUTER JOIN; RIGHT OUTER JOIN

Answer: A.

78.Predict the outcome of the following query.

SELECT e.salary, bonus FROM employees E JOIN bonus b USING (salary,job_id );

It executes successfully.

It throws an error because bonus in SELECT is not aliased

It throws an error because the USING clause cannot have more than 1 column.

It executes successfully but the results are not correct.

Answer: D.

View the Exhibit and examine the structure of the EMPLOYEES, DEPARTMENTS, LOCATIONS and BONUS. Answer the questions from 79 and 80 that follow:

79.You need to list all the departments in the city of Zurich. You execute the following query:

SELECT D.DEPARTMENT_ID , D.DEPARTMENT_NAME , L.CITY FROM departments D JOIN LOCATIONS L USING (LOC_ID,CITY) WHERE L.CITY = UPPER(''ZURICH'');

Predict the outcome of the above query.

It executes successfully.

It gives an error because a qualifier is used for CITY in the SELECT statement.

It gives an error because the column names in the SELECT do not match

It gives an error because the USING clause has CITY which is not a matching column.

Answer: D. Only the matching column names should be used in the USING clause.

80.Answer the question that follows the query given below:

SELECT e.first_name, d.department_id , e.salary, b.bonus FROM bonus b join employees e USING (job_id ) JOIN department d USING (department_id ) WHERE d.loc = ''Zurich

You need to extract a report which gives the first name, department number, salary and bonuses of the employees of a company named ”ABC”. Which of the following queries will solve the purpose?

SELECT e.first_name, d.department_id , e.salary, b.bonus FROM bonus b join employees e join departments d on (b.job_id = e.job_id ) on (e.department_id =d.department_id ) WHERE d.loc = ''Zurich

SELECT e.first_name, d.department_id , e.salary, b.bonus FROM bonus b join employees e on (b.job_id = e.job_id ) JOIN department d on (e.department_id =d.department_id ) WHERE d.loc = ''Zurich

SELECT e.first_name, d.department_id , e.salary, b.bonus FROM employees e join bonus b USING (job_id ) JOIN department d USING (department_id ) WHERE d.loc = ''Zurich

None of the above

Answer: C. The query A will throw a syntactical error, query B will throw an invalid identifier error between bonus and department.

Examine the Exhibits given below and answer the questions 81 to 85 that follow.

81. You need to find the managers” name for those employees who earn more than 20000. Which of the following queries will work for getting the required results?

SELECT e.employee_id "Employee", salary, employee_id , FROM employees E JOIN employees M USING (e.manager_id = m.employee_id ) WHERE e.salary >20000;

SELECT e.employee_id "Employee", salary, employee_id , FROM employees E JOIN employees M USING (e.manager_id) WHERE e.salary >20000;

SELECT e.employee_id "Employee", salary, employee_id , FROM employees E NATURAL JOIN employees M USING (e.manager_id = m.employee_id ) WHERE e.salary >20000;

SELECT e.employee_id "Employee", salary, employee_id , FROM employees E JOIN employees M ON (e.manager_id = m.employee_id ) WHERE e.salary >20000;

Answer: D.

82.You issue the following query:

SELECT e.employee_id ,d.department_id FROM employees e NATURAL JOIN department d NATURAL JOIN bonus b WHERE department_id =100;

Which statement is true regarding the outcome of this query?

It executes successfully.

It produces an error because the NATURAL join can be used only with two tables.

It produces an error because a column used in the NATURAL join cannot have a qualifier.

It produces an error because all columns used in the NATURAL join should have a qualifier.

Answer: C.

83.You want to display all the employee names and their corresponding manager names. Evaluate the following query:

SELECT e.first_name "EMP NAME", m.employee_name "MGR NAME" FROM employees e ______________ employees m ON e.manager_id = m.employee_id ;

Which JOIN option can be used in the blank in the above query to get the required output?

Simple inner JOIN

FULL OUTER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

Answer: C. A left outer join includes all records from the table listed on the left side of the join, even if no match is found with the other table in the join operation.

Consider the below exhibit and following query to answer questions 84 and 85. (Assume the table department has manager_id and department_name as its columns)

Select * FROM employees e JOIN department d ON (e.employee_id = d.manager_id);

84. You need to display a sentence “(first_name) (last_name) is manager of the (department_name) department”. Which of the following SELECT statements will successfully replace ”*” in the above query to fulfill this requirement?

SELECT e.first_name||'' ''||e.last_name||'' is manager of the ''||d.department_name||'' department.'' "Managers"

SELECT e.first_name, e.last_name||'' is manager of the ''||d.department_name||'' department.'' "Managers"

SELECT e.last_name||'' is manager of the ''||d.department_name||'' department.'' "Managers"

None of the above

Answer: A.

85.What will happen if we omit writing the braces “( )” after the ON clause in the above query?

It will give only the names of the employees and the managers” names will be excluded from the result set

It will give the same result as with braces “( )”

It will give an ORA error as it is mandatory to write the braces “()” when using the JOIN..ON clause

None of the above

Answer: B. The braces are not mandatory, but using them provides a clear readability of the conditions within it.

86. Which of the following queries creates a Cartesian join?

SELECT title, authorid FROM books, bookauthor;

SELECT title, name FROM books CROSS JOIN publisher;

SELECT title, gift FROM books NATURAL JOIN promotion;

all of the above

Answer: A, B. A Cartesian join between two tables returns every possible combination of rows from the tables. A Cartesian join can be produced by not including a join operation in the query or by using a CROSS JOIN.

87. Which of the following operators is not allowed in an outer join?

AND

=

OR

>

Answer: C. Oracle raises the exception “ORA-01719: outer join operator (+) not allowed in operand of OR or IN”

88. Which of the following queries contains an equality join?

SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20;

SELECT title, name FROM books CROSS JOIN publisher;

SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail;

None of the above

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).

89. Which of the following queries contains a non-equality join?

SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20;

SELECT title, name FROM books JOIN publisher USING (pubid);

SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail;

None of the above

Answer: D. Nonequijoins match column values from different tables based on an inequality expression. The value of the join column in each row in the source table is compared to the corresponding values in the target table. A match is found if the expression used in the join, based on an inequality operator, evaluates to true. When such a join is constructed, a nonequijoin is performed.A nonequijoin is specified using the JOIN..ON syntax, but the join condition contains an inequality operator instead of an equal sign.

90. The following SQL statement contains which type of join?

SELECT title, order#, quantity FROM books FULL OUTER JOIN orderitems ON books.isbn = orderitems.isbn;

equality

self-join

non-equality

outer join

Answer: D. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

91. Which of the following queries is valid?

SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# = 1005;

SELECT b.title, b.retail, o.quantity FROM books b, orders od, orderitems o WHERE orders.order# = orderitems.order# AND orderitems.isbn=books.isbn AND od.order#=1005;

SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order#=1005;

None of the above

Answer: C. If tables in the joins have alias, the selected columns must be referred with the alias and not with the actual table names.

92. Given the following query.

SELECT zip, order# FROM customers NATURAL JOIN orders;

Which of the following queries is equivalent?

SELECT zip, order# FROM customers JOIN orders WHERE customers.customer# = orders.customer#;

SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer#;

SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer# (+);

none of the above

Answer: B. Natural join instructs Oracle to identify columns with identical names between the source and target tables.

93. Examine the table structures as given. Which line in the following SQL statement raises an error?

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)

SQL> DESC departments Name Null? Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

1. SELECT e.first_name, d.department_name 2. FROM employees e, department d 3. WHERE e.department_id=d.department_id

line 1

line 2

line 3

No errors

Answer: A. If a query uses alias names in the join condition, their column should use the alias for reference.

94. Given the following query:

SELECT lastname, firstname, order# FROM customers LEFT OUTER JOIN orders USING (customer#) ORDER BY customer#;

Which of the following queries returns the same results?

SELECT lastname, firstname, order# FROM customers c OUTER JOIN orders o ON c.customer# = o.customer# ORDER BY c.customer#;

SELECT lastname, firstname, order# FROM orders o RIGHT OUTER JOIN customers c ON c.customer# = o.customer# ORDER BY c.customer#;

SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer# (+) ORDER BY c.customer#;

None of the above

Answer: B, C.

95. Which of the below statements are true?

Group functions cannot be used against the data from multiple data sources.

If multiple tables joined in a query, contain identical columns, Oracle selects only one of them.

Natural join is used to join rows from two tables based on identical columns.

A and B

Answer: C. Group functions can be used on a query using Oracle joins. Ambiguous columns must be referenced using a qualifier.

96. Which line in the following SQL statement raises an error?

1. SELECT name, title 2. FROM books JOIN publisher 3. WHERE books.pubid = publisher.pubid 4. AND 5. cost < 45.95

line 1

line 2

line 3

line 4

Answer: C. Since the tables are joined using JOIN keyword, the equality condition should be written with the USING clause and not WHERE clause.

97. Given the following query:

SELECT title, gift FROM books CROSS JOIN promotion;

Which of the following queries is equivalent?

SELECT title, gift FROM books NATURAL JOIN promotion;

SELECT title FROM books INTERSECT SELECT gift FROM promotion;

SELECT title FROM books UNION ALL SELECT gift FROM promotion;

SELECT title, gift FROM books, promotion;

Answer: D. Cartesian joins are same as Cross joins.

98. If the CUSTOMERS table contains seven records and the ORDERS table has eight records, how many records does the following query produce?

SELECT * FROM customers CROSS JOIN orders;

0

56

7

15

Answer: B. Cross join is the cross product of rows contained in the two tables.

99. Which of the following SQL statements is not valid?

SELECT b.isbn, p.name FROM books b NATURAL JOIN publisher p;

SELECT isbn, name FROM books b, publisher p WHERE b.pubid = p.pubid;

SELECT isbn, name FROM books b JOIN publisher p ON b.pubid = p.pubid;

SELECT isbn, name FROM books JOIN publisher USING (pubid);

Answer: A. Ambiguous columns must be referred with the table qualifiers.

100. Which of the following lists all books published by the publisher named ”Printing Is Us”?

SELECT title FROM books NATURAL JOIN publisher WHERE name = ''PRINTING IS US

SELECT title FROM books, publisher WHERE pubname = 1;

SELECT * FROM books b, publisher p JOIN tables ON b.pubid = p.pubid WHERE name = ''PRINTING IS US

none of the above

Answer: A. Assuming that the column NAME is not contained in BOOKS table, query A is valid.

101. Which of the following SQL statements is not valid?

SELECT isbn FROM books MINUS SELECT isbn FROM orderitems;

SELECT isbn, name FROM books, publisher WHERE books.pubid (+) = publisher.pubid (+);

SELECT title, name FROM books NATURAL JOIN publisher

All the above SQL statements are valid.

Answer: B. The query B raises an exception “ORA-01468: a predicate may reference only one outer-joined table”.

102. Which of the following statements about an outer join between two tables is true?

If the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator.

To include unmatched records in the results, the record is paired with a NULL record in the deficient table.

The RIGHT, LEFT, and FULL keywords are equivalent.

all of the above

Answer: B.

103. Which line in the following SQL statement raises an error?

1. SELECT name, title 2. FROM books b, publisher p 3. WHERE books.pubid = publisher.pubid 4. AND 5. (retail > 25 OR retail-cost > 18.95);

line 1

line 3

line 4

line 5

Answer: B. Since the tables used in the query have a qualifier, the columns must be referred using the same.

104. What is the maximum number of characters allowed in a table alias?

10

155

255

30

Answer: D. The table alias can be maximum of 30 characters.

105. Which of the following SQL statements is valid?

SELECT books.title, orderitems.quantity FROM books b, orderitems o WHERE b.isbn= o.ibsn;

SELECT title, quantity FROM books b JOIN orderitems o;

SELECT books.title, orderitems.quantity FROM books JOIN orderitems ON books.isbn = orderitems.isbn;

none of the above

Answer: C.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL Certificate – Discussion nhận dự án làm có lương

Discuss SQL Certificate

Oracle”s Oracle Database 11g: SQL Fundamentals exam is part of the Oracle Certified Oracle Database 11g Administrator track, combining training, experience, and testing to endorse candidates with a strong foundation and expertise in the industry’s most advanced database management system.

This certification is to put you on the short list for winning Oracle SQL-Based projects. An Oracle Technical Certification is a valuable, industry-recognized credential that signifies a proven level of knowledge and skill.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL Certificate – Mock Exams nhận dự án làm có lương
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 line ignored.”

Lists the columns of EMP table

Lists the EMP table columns, their data type and nullity

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 individual 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 alias 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 list/sub-query

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

Returns rows only if all the values match in a list/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 list of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

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

Oracle will convert them implicitly 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 links

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 lists 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 duplicate values for job codes

The query executes but results produced are unexpected

There are no duplicate 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 explicitly 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 explicit 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 nullity

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 valid, 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 explicitly. Parentheses are preferred over other SET operators during execution.

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

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 valid, 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 duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate 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 alias name is good practice to improve the readability of the code and the view queries.

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

Answer(43): C. The NATURAL JOIN clause implicitly 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 alias 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 duplicates.

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 applied to the groups in the SELECT list.

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

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 duplicates obtained.

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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL Certificate – Useful Resources nhận dự án làm có lương
SQL Certificate – Useful Resources
The following resources contain additional information on SQL Certificate. Please use them to get more in-depth knowledge on this topic.

Useful Links on SQL Certificate

− Database Language SQL (Proposed revised text of DIS 9075) July 1992 (Second Informal Review Draft).

− DB2 resource for SQL users.

− Oracle SQL Developer is a free and fully supported graphical tool for database development.

− A small article on SQL, worth to go through it.

− Here you can download the latest MySQL release, get the MySQL news update. The mailing list is also a great resources for anyone who want to build dynamic websites using MySQL.

− Its a tutorial from Tutorials Point which explains you how to use MySQL along with PERL and DBI module. Here you will learn all the required MySQL operations alongwith examples.

Useful Books on SQL Certificate

To enlist your site on this page, please drop an email to contact@tutorialspoint.com

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL – Using DDL Statements nhận dự án làm có lương
SQL – Using DDL Statements
Using DDL Statements to Create and Manage Tables

A schema is the collection of multiple database objects,which are known as schema objects.These objects have direct access by their owner schema.Below table lists the schema objects.

Table – to store data

View – to project data in a desired format from one or more tables

Sequence – to generate numeric values

Index – to improve performance of queries on the tables

Synonym – alternative name of an object

One of the first steps in creating a database is to create the tables that will store an organization”s data.Database design involves identifying system user requirements for various organizational systems such as order entry, inventory management, and accounts receivable. Regardless of database size and complexity, each database is comprised of tables.

Creating the table

To create a table in the database,a DBA must have certain information in hand – the table name, column name, column data types, and column sizes. All this information can be modified later using DDL commands.

Table Naming Conventions –

The name you choose for a table must follow these standard rules:

The name must begin with a letter A-Z or a-z

Can contain numbers and underscores

Can be in UPPER of lower case

Can be up to 30 characters in length

Cannot use the same name of another existing object in your schema

Must not be a SQL reserved word

Following the above guidelines, ”EMP85” can be a valid table name.But 85EMP is not.Similarly, UPDATE cannot be a chosen as a table name since it a SQL reserved keyword.

CREATE TABLE statement

The CREATE TABLE is a DDL statement which is used to create tables in the database.The table gets created as soon as the CREATE TABLE script is executed and is ready to hold the data onwards.The user must have the CREATE TABLE system privilege to create the table in its own schema.But to create a table in any user”s schema, user must have CREATE ANY TABLE schema.

Here is the syntax of a basic CREATE TABLE statement.There may be many additional clauses to explicitly provide the storage specifications or segment values.

CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} [, { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} ]...) [AS subquery]

In the above syntax, DEFAULT specifies default value which can be used during INSERT statement if the column is ignored. It cannot contain references to other table columns or pseudo columns (CURRVAL, NEXTVAL, LEVEL, and ROWNUM) except SYSDATE and USER, or date constants that are not fully specified.

Constraints are the rules defined optionally at the column level or table level (covered later in this chapter).These rules are checked during any data action (Insert, update) on the table and raise error to abort the action upon its violation.

For example, the CREATE TABLE statement below creates a table EMP_TEST. Note the column specifications, data type and precision.

CREATE TABLE SCOTT.EMP_TEST (EMPID NUMBER, ENAME VARCHAR2(100), DEPARTMENT_ID NUMBER, SALARY NUMBER, JOB_ID VARCHAR2(3), HIREDATE DATE, COMM NUMBER);

A user can refer the tables from other user”s schema by prefixing the username or schema with the table name.For example, a user GUEST wishes to query the employee name and salary from the EMP_TEST table which is owned by SCOTT. He can issue the below query –

SELECT ENAME, SALARY, FROM GUEST.EMP_TEST;

A column can hold a default value during the time of table creation.It helps to restrict the NULL values getting into the column. Default value can be deduced from either a literal, expression or SQL function which must return a compatible data type to the column. In the below CREATE TABLE statement, note that the LOCATION_ID column has default value 100.

CREATE TABLE SCOTT.DEPARTMENT (DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100);

CTAS – Create table using subquery

A table can be created from an existing table in the database using a subquery option.It copies the table structure as well as the data from the table. Data can also be copied based on conditions.The column data type definitions including the explicitly imposed NOT NULL constraints are copied into the new table.

The below CTAS script creates a new table EMP_BACKUP. Employee data of department 20 gets copied into the new table.

CREATE TABLE EMP_BACKUP AS SELECT * FROM EMP_TEST WHERE department_id=20;

Data types

Data types are used to specify the basic behavior of a column in the table.On a broader basis,column behavior can either belong to number,character or a date family.There are multiple other subtypes which belong to these families.

Number data type

The NUMBER datatype encompasses both integer,fixed-point,and floating-point numeric values.Early versions of Oracle defined different datatypes for each of these different types of numbers,but now the NUMBER datatype serves all of these purposes.Choose the NUMBER datatype when a column must store numerical data that can be used in mathematical calculations.Occasionally,the NUMBER datatype is used to store identification numbers where those numbers are generated by the DBMS as sequential numbers.

NUMBER (p, s), where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point).The scale can range between -84 to 127.

NUMBER (p),is a fixed-point number with a scale of zero and a precision of p.

FLOAT [(p)],where p is the binary precision that can range from 1 to 126. If p is not specified the default value is binary 126.

Date data type

For each DATE data type, Century, Year, Month, Day, Hour, Minute, Second are stored in database. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY.If you do not specify a time, the default time is 12:00:00 a.m.

Character data type

Oracle supports three predefined character datatypes including CHAR, VARCHAR, VARCHAR2, and LONG.VARCHAR and VARCHAR2 are actually synonymous, and Oracle recommends using VARCHAR2 instead of VARCHAR.Use the CHAR datatype when the column will store character values that are fixed-length.For example, a Social Security number (SSN) in the United States is assigned to every citizen and is always 9 characters in size (even though an SSN is strictly composed of digits,the digits are treated as characters), and would be specified as CHAR(9). Use the VARCHAR2 datatype to store alphanumeric data that is variable-length.For example, a customer name or address will vary considerably in terms of the number of characters to be stored.The maximum size of a VARCHAR2 column is 4,000 characters.

LOB data type

Oracle provides several different LOB datatypes, including CLOB (character large object) and BLOB (binary large object).Columns of these datatypes can store unstructured data including text, image, video, and spatial data.The CLOB datatype can store up to eight terabytes of character data using the CHAR database character set.The BLOB datatype is used to store unstructured binary large objects such as those associated with image and video data where the data is simply a stream of “bit” values.A BLOB datatype can store up to eight terabytes of binary data.The NCLOB data type can store character large objects in multibyte national character set up to 8TB to 128TB.The BFILE data type value works as a file locator or pointer to file on the server”s file system. The maximum file size supported is 8TB to 128TB.

Constraints

Constraints are the set of rules defined in Oracle tables to ensure data integrity.These rules are enforced placed for each column or set of columns.Whenever the table participates in data action, these rules are validated and raise exception upon violation. The available constraint types are NOT NULL, Primary Key, Unique, Check, and Foreign Key.

The below syntax can be used to impose constraint at the column level.

Syntax:

column [data type] [CONSTRAINT constraint_name] constraint_type

All constraints except NOT NULL, can also be defined at the table level. Composite constraints can only be specified at the table level.

NOT NULL Constraint

A NOT NULL constraint means that a data row must have a value for the column specified as NOT NULL.If a column is specified as NOT NULL,the Oracle RDBMS will not allow rows to be stored to the employee table that violate this constraint.It can only be defined at column level, and not at the table level.

Syntax:

COLUMN [data type] [NOT NULL]

UNIQUE constraint

Sometimes it is necessary to enforce uniqueness for a column value that is not a primary key column.The UNIQUE constraint can be used to enforce this rule and Oracle will reject any rows that violate the unique constraint.Unique constraint ensures that the column values are distinct, without any duplicates.

Syntax:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [constraint name] UNIQUE (column name)

Note: Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.

CREATE TABLE TEST ( ... , NAME VARCHAR2(20) CONSTRAINT TEST_NAME_UK UNIQUE, ... );

In case of composite unique key,it must be defined at table level as below.

CREATE TABLE TEST ( ... , NAME VARCHAR2(20), STD VARCHAR2(20) , CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD) );

Primary Key

Each table must normally contain a column or set of columns that uniquely identifies rows of data that are stored in the table.This column or set of columns is referred to as the primary key.Most tables have a single column as the primary key.Primary key columns are restricted against NULLs and duplicate values.

Points to be noted –

A table can have only one primary key.

Multiple columns can be clubbed under a composite primary key.

Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.

Syntax:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

The following example shows how to use PRIMARY KEY constraint at column level.

CREATE TABLE TEST ( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... );

The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.

CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) );

Foreign Key

When two tables share the parent child relationship based on specific column, the joining column in the child table is known as Foreign Key.This property of corresponding column in the parent table is known as Referential integrity.Foreign Key column values in the child table can either be null or must be the existing values of the parent table.Please note that only primary key columns of the referenced table are eligible to enforce referential integrity.

If a foreign key is defined on the column in child table then Oracle does not allow the parent row to be deleted,if it contains any child rows.However,if ON DELETE CASCADE option is given at the time of defining foreign key,Oracle deletes all child rows while parent row is being deleted.Similarly,ON DELETE SET NULL indicates that when a row in the parent table is deleted, the foreign key values are set to null.

Syntax:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

The following example shows how to use FOREIGN KEY constraint at column level.

CREATE TABLE TEST (ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode), ... );

Usage of ON DELETE CASCADE clause

CREATE TABLE TEST (ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode) ON DELETE CASCADE, ... );

Check constraint

Sometimes the data values stored in a specific column must fall within some acceptable range of values.A CHECK constraint requires that the specified check condition is either true or unknown for each row stored in the table.Check constraint allows to impose a conditional rule on a column, which must be validated before data is inserted into the column. The condition must not contain a sub query or pseudo column CURRVAL NEXTVAL, LEVEL, ROWNUM, or SYSDATE.

Oracle allows a single column to have more than one CHECK constraint. In fact, there is no practical limit to the number of CHECK constraints that can be defined for a column.

Syntax:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] CHECK (condition)

The following example shows how to use CHECK constraint at column level.

CREATE TABLE TEST ( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK (upper (GRADE) in (''A'',''B'',''C'')), ... );

The following example shows how to use CHECK constraint at table level.

CREATE TABLE TEST ( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate), );

ALTER TABLE statement

A DBA can make changes to the table structure or column definitions after the table has been created in the database.The DDL command ALTER TABLE is used to perform such actions.Alter command provides multiple utilities exclusive for schema objects.The ALTER TABLE statement is used to add, drop, rename, and modify a column in a table.

The below ALTER TABLE statement renames the table EMP to EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

The below ALTER TABLE statement adds a new column TESTCOL to the EMP_NEW table

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

The below ALTER TABLE statement renames the column TESTCOL to TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

The below ALTER TABLE statement drop the column TESTNEW from EMP_NEW table

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

The below ALTER TABLE statement adds primary key on the EMPLOYEE_ID column.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

The below ALTER TABLE statement drop the primary key.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

The below ALTER TABLE statement switches the table mode to read only.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

Read only tables came as an enhancement in Oracle 11g.It allows the tables to be used for read only purpose. In earlier oracle versions, tables were made read only by granting SELECT privilege to the other users, but owner still had the read write privilege.But now,if a table is set as Read only,even owner doesn”t have access on data manipulation.

Syntax:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

Illustration

SQL>CREATE TABLE ORATEST (id NUMBER) SQL>INSERT INTO ORATEST VALUES (1); SQL>ALTER TABLE ORATEST READ ONLY; SQL> INSERT INTO ORATEST VALUES (2); INSERT INTO ORATEST VALUES (2) * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."ORATEST" SQL> UPDATE ORATEST SET id = 2; UPDATE ORATEST SET id = 2 * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."ORATEST" SQL> DELETE FROM ORATEST; DELETE FROM ORATEST * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."ORATEST" SQL> TRUNCATE TABLE ORATEST; TRUNCATE TABLE ORATEST * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."ORATEST" SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50)); ALTER TABLE ORATEST ADD (description VARCHAR2 (50)) * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."ORATEST" SQL> ALTER TABLE ORATEST READ WRITE; Table altered. SQL> DELETE FROM ORATEST; 1 row deleted.

DROP TABLE statement

The DROP TABLE statement is used to remove a table from the database. The dropped table and its data remain no longer available for selection.Dropped table can be recovered using FLASHBACK utility,if available in recyclebin.Dropping a table drops the index and triggers associated with it.

Syntax:

DROP TABLE [TABLE NAME] [PURGE]

The below statement will drop the table and place it into the recyclebin.

DROP TABLE emp_new;

The below statement will drop the table and flush it out from the recyclebin also.

DROP TABLE emp_new PURGE;

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL – Conversion Functions nhận dự án làm có lương
SQL – Conversion Functions Questions
1. What will be the outcome of the following query?

SELECT ROUND(144.23,-1) FROM dual;

140

144

150

100

Answer: 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)

2. You are currently located in New Jersey and have connected to a remote database in San Diego. You issue the following command.

SELECT ROUND (sysdate-hire_date,0) FROM employees WHERE (sysdate-hire_date)/180 = 2;

What is the outcome of this query?

An error because the ROUND function cannot be used with Date arguments.

An error because the WHERE condition expression is invalid.

Number of days since the employee was hired based on the current San Diego date and time.

Number of days since the employee was hired based on the current New Jersey date and time.

Answer: C. The SYSDATE function will take the current time of the database which it is connecting to remotely. You must perform basic arithmetic operation to adjust the time zone.

3. You need to display the names of the employees who have the letter ”s” in their first name and the letter ”t” at the second position in their last name. Which query would give the required output?

SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''s'') 0 AND SUBSTR(last_name,2,1) = ''t

SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''s'') '''' AND SUBSTR(last_name,2,1) = ''t

SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''e'') IS NOT NULL AND SUBSTR(last_name,2,1) = ''t

SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''e'') 0 AND SUBSTR(last_name,LENGTH(first_name),1) = ''t

Answer: A. The INSTR function returns the position of a given character in the required string. The SUBSTR function returns set of characters from the string from a given starting and end position.

4. Which of the following statements is true regarding the COUNT function?

COUNT (*) counts duplicate values and NULL values in columns of any data type.

COUNT function cannot work with DATE datatypes.

COUNT (DISTINCT job_id) returns the number of rows excluding rows containing duplicates and NULL values in the job_id column.

A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

Answer: A. The COUNT(*) function returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause. In contrast, COUNT(expr) returns the number of non-null values that are in the column identified by expr. COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

5. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database?

NOT NULL

INSTR

SUBSTR

COUNT

Answer: D. The COUNT (ALL column_name) is used to count number of rows excluding NULLs. Similarly, COUNT(*) is used to count the column values including NULLs.

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

SELECT 100+NULL+999 FROM dual;

100

999

NULL

1099

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

7. Which of the following statements are true regarding the single row functions?

They accept only a single argument.

They can be nested only to two levels.

Arguments can only be column values or constants.

They can return a data type value different from the one that is referenced.

Answer: D. Single row functions can take more than one argument and the return type can be different from the data type of the inputs.

8. Which of the below queries will format a value 1680 as $16,80.00?

SELECT TO_CHAR(1680.00,''$99G99D99'') FROM dual;

SELECT TO_CHAR(1680.00,''$9,999V99'') FROM dual;

SELECT TO_CHAR(1680.00,''$9,999D99'') FROM dual;

SELECT TO_CHAR(1680.00,''$99G999D99'') FROM dual;

Answer: A, D. The format model $99G999D99 formats given number into numeric, group separator, and decimals. Other format elements can be leading zeroes, decimal position, comma position, local currency, scientific notation, and sign.

9. Determine the output of the below query.

SELECT RPAD(ROUND(''78945.45''),10,''*'') FROM dual;

78945*****

**78945.45

The function RPAD cannot be nested with other functions

78945.45****

Answer: A. The LPAD(string, num, char) and RPAD(string, num, char) functions add a character to the left or right of a given string until it reaches the specified length (num) after padding. The ROUND function rounds the value 78945.45 to 78945 and then pads it with ”*” until length of 10 is reached.

10. Which of the following commands allows you to substitute a value whenever a NULL or non-NULL value is encountered in an SQL query?

NVL

NVLIF

NVL2

LNNVL

Answer: C. The NVL2 function takes minimum three arguments. The NVL2 function checks the first expression. If it is not null, the NVL2 function returns the second argument. If the first argument is null, the third argument is returned.

11. Which of the following type of single-row functions cannot be incorporated in Oracle DB?

Character

Numeric

Conversion

None of the above

Answer: D. The types of single-row functions like character, numeric, date, conversion and miscellaneous as well as programmer-written can be incorporated in Oracle DB.

12. Out of the below clauses, where can the single-row functions be used?

SELECT

WHERE

ORDER BY

All of the above

Answer: D. Single row function can be used in SELECT statement, WHERE clause and ORDER BY clause.

13. What is true regarding the NVL function in Oracle DB?

The syntax of NVL is NVL (exp1, exp2) where exp1 and exp2 are expressions.

NVL (exp1, exp2) will return the value of exp2 if the expression exp1 is NULL.

NVL (exp1, exp2) will return the value of the expression exp2 if exp1 is NOT NULL.

NVL (exp1, exp2) will return exp1 if the expression exp2 is NULL.

Answer: B. NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

14. Examine the structure of the EMPLOYEES table as given.

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)

What will be the outcome of the following query?

SELECT last_name, NVL(job_id, ''Unknown'') FROM employees WHERE last_name LIKE ''A%'' ORDER BY last_name;

It will throw an ORA error on execution.

It will list the job IDs for all employees from EMPLOYEES table.

It will list the job IDs of all employees and substitute NULL job IDs with a literal ”Unknown”.

It will display the last names for all the employees and their job IDs including the NULL values in the job ID.

Answer: C. The NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

15. What will the outcome of the following query?

SELECT NVL (NULL,''1'') FROM dual;

NULL

1

0

Gives an error because NULL cannot be explicitly specified to NVL function

Answer: B. The NVL will treat NULL as a value and returns the alternate argument i.e. 1 as the result.

16. What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)

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)

SELECT employee_id , NVL(salary, 0) FROM employees WHERE first_name like ''P%'' ORDER BY first_name;

It will display 0 in the salary column for all the employees whose first name starts with a ”P”

It will display the salaries for the employees whose name start with a ”P” and 0 if the salaries are NULL.

It will throw an ORA error as the ORDER BY clause should also contain the salary column.

The NVL function should be correctly used as NVL (0, salary)

Answer: B. NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

17. Which of the following statements is true regarding the NVL statement?

SELECT NVL (arg1, arg2) FROM dual;

The two expressions arg1 and arg2 should only be in VARCHAR2 or NUMBER data type format.

The arguments arg1 and arg2 should have the same data type

If arg1 is VARCHAR2, then Oracle DB converts arg2 to the datatype of arg1 before comparing them and returns VARCHAR2 in the character set of arg1.

An NVL function cannot be used with arguments of DATE datatype.

Answer: C. If arg1 is of VARCHAR2 data type, Oracle does implicit type conversion for arg2 id arg2 is of NUMBER datatype. In all other cases, both the arguments must be of same datatype.

18. What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)

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)

SELECT NVL2(job_id,''Regular Employee'',''New Joinee'') FROM employees;

It will return the value ”Regular Employee” for all the employees who have NULL job IDs

It will return the value ”New Joinee” for all the employees who have NULL job IDs

It will return ”Regular Employee” if the job ID is NULL

It will throw an ORA error on execution.

Answer: B. The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

19. Which of the following is true for the statement given as under.

NVL2 (arg1, arg2, arg3)

Arg2 and Arg3 can have any data type

Arg1 cannot have the LONG data type

Oracle will convert the data type of expr2 according to Arg1

If Arg2 is a NUMBER, then Oracle determines the numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

Answer: D. The data types of the arg2 and arg3 parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert arg3 to the type of the arg2 parameter. The data type returned by the NVL2 function is the same as that of the arg2 parameter.

20. Examine the structure of the EMPLOYEES table as given.

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)

What will be the outcome of the query mentioned below?

SeLECT first_name, salary, NVL2(commission_pct, salary + (salary * commission_pct), salary) "Income" FROM employees WHERE first_name like ''P%'' ORDER BY first_name;

Salary will be returned if the Commission for the employee is NOT NULL.

Commission_pct will be returned if the Commission for the employee is NOT NULL.

Employees with the first name starting with ”P” and salary+(salary*commission_pct) will be returned if the employee earns a commission.

The query throws an error because a mathematical expression is written inside NVL2.

Answer: C. The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

21. What is true about the NULLIF function in Oracle DB?

NULLIF(expr1,expr2) will return expr2 if the two expressions are NOT NULL.

NULLIF(expr1,expr2) will return 0 if the two expressions are NULL.

NULLIF(expr1,expr2) will return NULL if the two expressions are equal.

Expr1 can be NULL in NULLIF(expr1, expr2)

Answer: C. The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(arg1,arg2), where the arguments arg1 and arg2 are compared. If they are identical, then NULL is returned. If they differ, the arg1 is returned.

22. Pick the correct answer given after the statement shown as under.

NULLIF (arg1,arg2)

Arg1 and Arg2 can be of different data types.

Arg1 and Arg2 have to be equal in order to be used in the NULLIF function.

There is no internal conversion of data types if NULLIF used as in the case of NVL and NVL2.

This is equivalent to CASE WHEN Arg1 = Arg22 THEN NULL ELSE Arg1 END.

Answer: D.

23. Examine the structure of the EMPLOYEES table as given.

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)

You need to create a report from the HR schema displaying employees who have changed jobs since they were hired. You execute the query given below.

SELECT e.last_name, NULLIF(e.job_id, j.job_id,"Old Job ID") FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name;

What will be the outcome of the query given above?

It will display the old job ID when the new job ID is NULL.

It will execute successfully and produce the required output.

It will display the new job ID if the new job ID is equal to the old job ID

It will throw an ORA error on execution.

Answer: B.

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

Perform calculations on data

Convert column data types

Modify individual data items

None of the above

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

25. What is the most appropriate about single row functions?

They return no value

They return one result per row and operate on all the rows of a table.

They return one result per row with input arguments

They return one result per set of rows and operate on multiple rows.

Answer: B. Single row functions always return one result per row and they operate on single rows only; hence the name ‘Single Row” is given to them.

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

Multiple-row functions

Single column functions

Single value functions

Multiple columns functions

Answer: A. There are basically two types of functions – Single row and Multiple row functions.

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

VARCHAR2

Character

LONG

NULLIF

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

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

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

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

Single row functions

Multi group functions

Multiple row functions

Single group functions.

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

30. Which of the following is true about Single Row Functions?

They can be nested

They accept arguments and return more than one value.

They cannot modify a data type

They cannot accept expressions as arguments.

Answer: A. Single row functions can be nested up to multiple levels.

31. What is the number of arguments Single Row functions accept?

0

Only 1

Only 2

1 or more than 1

Answer: D. Single row functions can accept one or more arguments depending upon the objective they serve.

32. Which of the following can be an argument for a Single Row Function?

Data types

SELECT statements

Expression

Table name

Answer: C. A user-supplied constant, variable value, column value and expression are the types of arguments of a single row function.

33. What is true about Character functions?

They return only character values

They accept NUMBER values

They accept character arguments and can return both character and number values

They accept values of all data type

Answer: C. The character function INSTR accepts a string value but returns numeric position of a character in the string.

34. What is true about Number functions?

They return both Character as well as Number values

They can”t accept expressions as input

Number functions can”t be nested.

They accept Number arguments and return Number values only.

Answer: D.

35. Which of the following is an exception to the return value of a DATE type single-row function?

TO_DATE

SYSDATE

MONTHS_BETWEEN

TO_NUMBER

Answer: C. All the DATE data type functions return DATE as return values except MONTHS_BETWEEN which returns a number.

36. Which of the following is not a Conversion type Single Row function?

TO_CHAR

TO_DATE

NVL

TO_NUMBER

Answer: C. Conversion functions convert a value from one data type to another. The NVL function replaces a null value with an alternate value.

37. Which of the following is a Case-Conversion Character function?

CONCAT

SUBSTR

INITCAP

REPLACE

Answer: C. The CONCAT, SUBSTR and REPLACE are Character-manipulation Character functions while INITCAP, LOWER and UPPER are case conversion character functions.

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

SELECT lower(''HI WORLD !!!'') FROM dual;

Hi World !!!

Hi WORLD !!!

hi world !!!

HI WORLD !!!

Answer: C. The LOWER function converts a string to lower case characters.

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

SELECT lower(upper(initcap(''Hello World'') )) FROM dual;

Hello World

HELLO world

hello World

hello world

Answer: C. Case conversion characters can be nested in the SELECT queries.

Examine the structure of the EMPLOYEES table as given and answer the questions 40 to 42 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)

40. Which of the following queries will give the same result as given in the query given below?

SELECT CONCAT(first_name, last_name) FROM employees;

SELECT first_name||last_name FROM employees;

SELECT first_name||” ” || last_name FROM employees;

SELECT last_name||”, ”||first_name FROM employees;

SELECT first_name||”,”||last_name FROM employees;

Answer: A. The CONCAT function joins two strings without any space in between.

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

SELECT ''The job id for ''||upper(last_name) ||'' is a ''||lower(job_id) FROM employees;

The job id for ABEL is a sa_rep

The job id forABEL is a sa_rep

The job id for abel is SA_REP

The job id for abel is sa_rep

Answer: A.

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

Answer: 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.

43. What is true about the CONCAT function in Oracle DB?

It can have only characters as input.

It can have only 2 input parameters.

It can have 2 or more input parameters

It joins values by putting a white space in between the concatenated strings by default.

Answer: B. The CONCAT function accepts only two arguments of NUMBER or VARCHAR2 datatypes.

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

Answer: 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.

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

SELECT length(''hi'') FROM dual;

2

3

1

hi

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

46. What is the difference between LENGTH and INSTR functions in Oracle DB?

They give the same results when operated on a string.

LENGTH gives the position of a particular character in a string

INSTR gives the position of a particular character in a string while LENGTH gives the length of the string.

LENGTH and INSTR can be used interchangeably.

Answer: C.

47. Examine the structure of the EMPLOYEES table as given.

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)

What will be the outcome of the following query?

SELECT upper(&jobid) FROM employees;

It results in an error as substitution variables cannot be used with single row functions

It prompts the user to input the jobid on each execution and then displays the job id in UPPER case

It gives the jobid as it is present in the table EMPLOYEES without making any change

It will not ask the user to input the job id and will convert all the job IDs in the table in UPPER case

Answer: B. Substitution variables can be used with the UPPER and LOWER functions.

48. What is false about the table DUAL in Oracle database?

It is owned by the user SYS and can be access by all the users.

It contains only one column and one row.

The value in the DUMMY column of the DUAL table is ”X”

The DUAL table is useful when you want to return a value only once

Answer: C. The DUAL table has one column named DUMMY and one row which has a value ”X”.

49. What will be the result of the following query?

SELECT sysdate+4/12 FROM dual;

The query produces error.

No of hours to a date with date as the result.

Sysdate arithmetic is ignored.

Returns the system date as result.

Answer: B. Arithmetic operations can be performed on dates in the Oracle DB.

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

SELECT lower (100+100) FROM dual;

100

100+100

ORA error

200

Answer: D. Arithmetic expressions can be specified within case conversion functions.

51. What will be the outcome of the following query if the SYSDATE = 20-MAY-13?

SELECT upper (lower (sysdate)) FROM dual;

20-may-2013

ORA error as LOWER and UPPER cannot accept date values.

20-MAY-13

20-May-13

Answer: C. The functions UPPER and LOWER can accept date type inputs and will yield the same result as they do on Strings.

52. What is the result of the following query?

SELECT INITCAP (24/6) FROM dual;

4

24

24/6

No result

Answer: A. Arithmetic expressions can be specified within case conversion functions.

53. Examine the structure of the EMPLOYEES table as given here.

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)

You need to display the last name of all employees which starts with the letter ”A”. Which of the following queries will yield the required result?

SELECT INITCAP (last_name||'' works as a ''||job_id "Job Description" FROM employees WHERE initcap (last_name) like ''A%

SELECT INITCAP (last_name) ||INITCAP('' works as a: '')|| INITCAP(job_id) "Job Description" FROM employees WHERE initcap (last_name) like ''A %

SELECT INITCAP (last_name||'' works as a ''||INITCAP(job_id)) "Job Description" FROM employees WHERE initcap (last_name) = ''A

SELECT UPPER (LOWER (last_name||'' works as a ''||job_id)) "Job Description" FROM employees WHERE lower (last_name) = ''A

Answer: A, B.

54. Assuming the SYSDATE is 20-FEB-13, What will be the outcome of the following query?

SELECT CONCAT (''Today is :'', SYSDATE) FROM dual;

Today is : 20-feb-13

The query throws error of incompatible type arguments.

Today is : 20-Feb-13

Today is : 20-FEB-13

Answer: D. The CONCAT function accepts arguments of all types.

55. What will be the result pattern of the following query?

SELECT CONCAT(first_name, CONCAT (last_name, job_id)) FROM dual;

First_namelast_namejob_id

First_name, last_name, job_id

Error as CONCAT cannot be nested

First_namelast_name, job_id

Answer: A. The CONCAT function can be nested with self or other character function.

56. Examine the structure of the EMPLOYEES table as given here.

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)

You need to generate a report which shows the first name, last name and the salary for all the employees in the department 100. The report should show the results in the form ”Andy Smith earns 50000”. Which of the following queries will give the required output?

SELECT concat (first_name,concat ('' '', concat(last_name, concat('' earns '', SALARY)))) Concat_String FROM employees WHERE department_id = 100;

SELECT concat (first_name, last_name||'' ''|| salary) FROM employees WHERE department_id = 100;

SELECT concat (first_name, concat(last_name, '' ''))||earns||salary FROM employees WHERE department_id = 100;

SELECT concat (first_name, concat(last_name, ''earns salary'') FROM employees WHERE department_id = 100;

Answer: A. The CONCAT function can be nested with self or other character function.

57. What will the following query show as a result?

SELECT LENGTH(''It is a lovely day today!'') FROM dual;

25

19

20

0

Answer: A. The LENGTH functions counts blank spaces, tabs and special characters too.

58. You need to display the country name from the COUNTRIES table. The length of the country name should be greater than 5 characters. Which of the following queries will give the required output?

SELECT country_name FROM countries WHERE LENGTH (country_name)= 5;

SELECT country_name FROM countries WHERE length (country_name)> 5;

SELECT SUBSTR(country_name, 1,5) FROM countries WHERE length (country_name)

SELECT country_name FROM countries WHERE length (country_name) 5;

Answer: B. The LENGTH function can be used in WHERE clause.

59. How does the function LPAD works on strings?

It aligns the string to the left hand side of a column

It returns a string padded with a specified number of characters to the right of the source string

It aligns character strings to the left and number strings to right of a column

It returns a string padded with a specified number of characters to the left of the source string

Answer: D. The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

60. Which of the following options is true regarding LPAD and RPAD functions?

The character strings used for padding include only characters.

The character strings used for padding include only literals

The character strings used for padding cannot include expressions.

The character strings used for padding include literals, characters and expressions.

Answer: D.

61. What is the maximum number of input arguments in LPAD and RPAD functions?

1

2

3

0

Answer: C. LPAD and RPAD take maximum of 3 arguments. If there are 2 arguments given, the padding happens by spaces.

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

SELECT lpad (1000 +300.66, 14, ''*'') FROM dual;

*******1300.66

1300*******

1300.66

****1300.66

Answer: A. To make the total length of 14 characters, the return value 1300.66 is padded with 7 asterisks (*) on the left.

63. What is true regarding the TRIM function?

It is similar to SUBSTR function in Oracle

It removes characters from the beginning or end of character literals, columns or expression

TRIM function cannot be applied on expressions and NUMBERS

TRIM function can remove characters only from both the sides of a string.

Answer: B. The TRIM function literally trims off leading or trailing (or both) character strings from a given source string. TRIM function when followed by TRAILING or LEADING keywords, can remove characters from one or both sides of a string.

64. You need to remove the occurrences of the character ”.” and the double quotes ””” from the following titles of a book present in the table MAGAZINE.

"HUNTING THOREAU IN NEW HAMPSHIRE" THE ETHNIC NEIGHBORHOOD."

Which of the following queries will give the required result?

SELECT LTRIM(Title,''"'') FROM MAGAZINE;

SELECT LTRIM(RTRIM(Title,''."''),''"'') FROM MAGAZINE;

SELECT LTRIM (Title,''"THE'') FROM MAGAZINE;

SELECT LTRIM(RTRIM(Title,''."THE''),''"'') FROM MAGAZINE;

Answer: B. The LTRIM and RTRIM functions can be used in combination with each other.

65. What will be returned as a result of the following query?

SELECT INSTR(''James'',''x'') FROM dual;

1

2

0

3

Answer: C. INSTR function returns a 0 when the search string is absent in the given string.

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

SELECT INSTR(''1$3$5$7$9$'',''$'',3,4)FROM dual;

2

10

7

4

Answer: B. INSTR function search for the 4th occurrence of ”$” starting from the 3rd position.

67. What will be the result of the following query?

SELECT INSTR(''1#3#5#7#9#'', -3,2) FROM dual;

#5

#3

#7

#9

Answer: D. SUBSTR function will search 3 places starting from the end of string and will give 2 characters in the forward direction giving #9.

Examine the structure of the EMPLOYEES table as given below and answer the questions 68 and 69 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)

68. You need to extract a consistent 15 character string based on the SALARY column in the EMPLOYEES table. If the SALARY value is less than 15 characters long, zeros must be added to the left of the value to yield a 15 character string. Which query will fulfill this requirement?

SELECT rpad(salary, 15,0) FROM employees;

SELECT lpad(salary,15,0) FROM employees;

SELECT ltrim(salary,15,0) FROM employees;

SELECT trim(salary,15,0) FROM employees;

Answer: B. The LPAD and RPAD functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

69. You need to display the last 2 characters from the FIRST_NAME column in the EMPLOYEES table without using the LENGTH function. Which of the following queries can fulfill this requirement?

SELECT SUBSTR(first_name, 2) FROM employees;

SELECT SUBSTR(first_name, -2) FROM employees;

SELECT RTRIM(first_name, 2) FROM employees;

SELECT TRIM(first_name, 2) FROM employees;

Answer: B. 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.

70. Assuming the SYSDATE is 13-JUN-13, what will be the outcome of the following query?

SELECT SUBSTR(sysdate,10,7) FROM dual;

3

N-13

0

NULL

Answer: D. The query will give a NULL as the position 10 to start with in the SYSDATE doesn”t exist.

71. Which of the following is used to replace a specific character in a given string in Oracle DB?

LTRIM

TRIM

TRUNC

REPLACE

Answer: D.

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

SELECT replace(9999.00-1,''8'',88) FROM dual;

999

9998

99988

9999.88

Answer: C. The REPLACE function searches for ”8” in 9998 and replaces it with ”88”.

73. Examine the structure of the EMPLOYEES table as given here.

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)

You need to retrieve the first name, last name (separated by a space) and the formal names of employees where the combined length of the first name and last name exceeds 15 characters. A formal name is formed by the first letter of the First Name and the first 14 characters of the last name. Which of the following queries will fulfill this requirement?

SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees;

SELECT first_name, last_name ,SUBSTR(first_name, 1,14)||'' ''||SUBSTR(last_name, 1,1) formal_name FROM employees WHERE length (first_name) + length(last_name)

SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length (first_name) + length(last_name) =15;

SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length (first_name) + length(last_name) > 15;

Answer: D.

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

SELECT round(148.50) FROM dual;

148.50

140

150

149

Answer: D. if the decimal precision is absent, the default degree of rounding is 0 and the source is rounded to the nearest whole number.

75. Assuming the sysdate is 10-JUN-13, What will be the outcome of the following query?

SELECT trunc (sysdate,''mon'') FROM dual;

10-JUN-13

1-JUN-13

ORA error as the TRUNC function can”t have an input parameter when used with dates.

31-JUN-13

Answer: B. The date is truncated to the first day of the month. Similarly, it can be done for year also.

76. What will be the result of the following query?

SELECT trunc(1902.92,-3) FROM dual;

2000

1000

1901

1901.00

Answer: B.

77. What is the syntax of the MOD function in Oracle DB?

Mod(divisor,dividend)

MOD(divisor,1)

MOD(dividend,divisor)

None of the above

Answer: C. The MOD function is used to get the remainder of a division operation.

78. What will be outcome of the following query?

SELECT mod(100.23,-3) FROM dual;

ORA error

1.23

100

0

Answer: B. The MOD function gives the same answer for a positive divisor as well as a negative divisor

79. Which of the following functions are used to differentiate between even or odd numbers in Oracle DB?

ROUND

TRUNC

MOD

REPLACE

Answer: C. The MOD function can be used to check whether a given number is even or odd. If MOD (num,2) returns zero, the number ”num” is an even. If MOD (num,2) returns 1, the number ”num” is odd.

80. Examine the structure of the EMPLOYEES table as given below.

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)

You need to allocate the first 12 employees to one of the four teams in a round-robin manner. The employee IDs start with a 100. Which of the following queries will fulfill the requirement?

SELECT * FROM employees WHERE employee_id between 100 and 111 ORDER BY employee_id;

SELECT first_name, last_name, employee_id, mod(employee_id, 4) Team# FROM employees WHERE employee_id between 100 and 111 ORDER BY employee_id;

SELECT first_name, last_name,mod(employee_id, 2) Team# FROM employees WHERE employee_ID 100;

SELECT first_name, last_name, mod(employee_id, 4) Team# FROM employees WHERE employee_ID = 100;

Answer: B.

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

SELECT SUBSTR(''Life is Calling'',1) FROM dual;

ORA error as there should be minimum 3 arguments to the SUBSTR function.

Life is Calling

NULL

Life

Answer: B. Calling the SUBSTR function with just the first two parameters results in the function extracting a string from a start position to the end of the given source string.

82. What is the default data format for the sysdate in SQL Developer?

DD-MON-YY

DD-MON-RR

DD/MON/RR

DD/MON/YYYY

Answer: C. For SQL*PLUS the default date format is DD-MON-RR.

83. Assuming the SYSDATE to be 10-JUN-2013 12:05pm, what value is returned after executing the below query?

SELECT add_months(sysdate,-1) FROM dual;

09-MAY-2013 12:05pm

10-MAY-2013 12:05pm

10-JUL-2013 12:05pm

09-JUL-2013 12:05pm

Answer: B. The ADD_MONTHS(date, x) function adds ”x” number of calendar months to the given date. The value of ”x” must be an integer and can be negative.

84. What value will be returned after executing the following statement? Note that 01-JAN-2013 occurs on a Tuesday.

SELECT next_day(''01-JAN-2013'',''friday'') FROM dual;

02-JAN-2013

Friday

04-JAN-2013

None of the above

Answer: C. The NEXT_DAY(date,”day”) finds the date of the next specified day of the week (”day”) following date. The value of char may be a number representing a day or a character string.

85. What is the maximum number of parameters the ROUND function can take?

0

1

2

3

Answer: C. If there is only one parameter present, then the rounding happens to the nearest whole number

86. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-2004 in the DD-MON-RR format?

19

21

20

NULL

Answer: C. If the two digits of the current year and the specified year lie between 0 and 49, the current century is returned.

87. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

19

21

20

NULL

Answer: A. If the two digits of the current year lie between 0 and 49 and the specified year falls between 50 and 99, the previous century is returned.

88. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

19

21

20

NULL

Answer: A. if the two digits of the current and specified years lie between 50 and 99, the current century is returned by default.

89. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-07 in the DD-MON-RR format?

19

21

20

NULL

Answer: C. if the two digits of the current year lie between 50 and 99 and the specified year falls between 0 and 49, the next century is returned.

90. How many parameters does the SYSDATE function take?

1

2

4

0

Answer: D. The SYSDATE is a pseudo column in Oracle.

91. What is true about the SYSDATE function in Oracle DB?

It returns only the system date

It takes 2 parameters at least.

The default format is DD-MON-YY

The default format of SYSDATE is DD-MON-RR and it returns the date and time of the system according to the database server.

Answer: D.

92. What will be the datatype of the result of the following operation?
“Date3 = Date1-Date2”

Date

Num1

0

NULL

Answer: B. Subtraction of two dates results in number of days.

93. What will be the datatype of the result of the following operation?
“Date2 = Date1-Num1”

Date

Num1

0

NULL

Answer: A. Subtraction of a number from a date value results in date.

94. What does a difference between two dates represent in Oracle DB?

The number of days between them

Difference in dates in not possible in Oracle DB

A date

NULL

Answer: A.

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

SELECT months_between(''21-JUN-13'',''19-JUN-13'') FROM dual;

ORA error

A positive number

A negative number

0

Answer: C. If the first parameter is less than the second parameter, the MONTHS_BETWEEN returns a negative number.

96. What can be deduced if the result of MONTHS_BETWEEN (start_date,end_date) function is a fraction?

It represents the difference in number between the start date and end date.

The result cannot be a fractional number, it has to be a whole number.

NULL

It represents the days and the time remaining after the integer difference between years and months is calculated and is based on a 31-day month.

Answer: D.

97. You are connected to a remote database in Switzerland from India. You need to find the Indian local time from the DB. Which of the following will give the required result?

SELECT sysdate FROM dual;

SELECT round(sysdate) FROM dual;

SELECT trunc (sysdate) FROM dual;

SELECT current_date FROM dual;

Answer: D.

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

SELECT months_between (to_date (''29-feb-2008''), to_date (''29-feb-2008 12:00:00'',''dd-mon-yyyy hh24:mi:ss''))*31 FROM dual;

Approximately 0

1

The query will throw an ORA error

0.5 days

Answer: D. The MONTHS_BETWEEN(date1, date2) finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

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

SELECT add_months (''31-dec-2008'',2.5) FROM dual;

31-feb-2009

28-feb-2009

31-mar-2009

15-jan-2009

Answer: B. the fractional part of 2.5 will be ignored and 2 months will be added to 31-dec-2012 which is 31-feb-2013 but as it is not a valid date, the result is 28-feb-2009.

100. You need to identify the date in November when the staff will be paid. Bonuses are paid on the last Friday in November. Which of the following will fulfill the requirement?

SELECT next_day (''30-nov-2012'' , ''Friday'') FROM dual;

SELECT next_day (''30-nov-2012'' , ''Friday'') -7 FROM dual;

SELECT last_day (''01-nov-2012'' ) FROM dual;

SELECT next_day (''30-nov-2012'' , ''sat'') -1 FROM dual;

Answer: B. The NEXT_DAY(date,”day”) and LAST_DAY (date,”day”) functions find the date of the next or last specified day of the week (”day”) following date. The value of char may be a number representing a day or a character string.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024
Khóa học miễn phí SQL – Conditional Expressions nhận dự án làm có lương
SQL – Conditional Expressions Questions
1. What is true about data types in Oracle DB?

They are given to columns for sorting purposes.

They are given to columns for a structured representation in a table.

They are given to columns to constrain the nature of the data it can store.

They are not mandatory.

Answer: C. Data types define the nature of data which a column can store in a table. A column can store only one type of data. The primary data types available in Oracle are NUMBER, VARCHAR2, and DATE.

2. What is true about nested functions?

There is a limit to use the Nested functions only 5 times in Oracle DB.

They are evaluated from the outermost level to the innermost level.

They are evaluated from the innermost level to the outermost level.

All the functions in a Nested expression return the same data type.

Answer: C. Single row functions can group functions can be nested in a SELECT query in which the innermost function is the first one to be executed. The result of the execution of innermost function serves as the input for the outer function.

3. Which of the following functions simplify working with columns that potentially contain null values?

Nested functions

General functions

Conditional functions

None of the above

Answer: B. The general functions like NVL, NVL2, NULLIF, and COALESCE are used to pacify the effect of NULL while displaying the query results. They bypass the NULL values by assigning an alternative value.

4. Which of the following data types are appropriate for general functions?

VARCHAR2

NUMBER

DATE

All Datatypes

Answer: D. General functions are usually compatible with all primary data types like NUMBER, VARCHAR2 and DATE.

5. What is true about the COALESCE function?

It accepts minimum 2 and maximum 5 input parameters

It always returns the first NULL value among the input parameters

It can accept unlimited number of input parameters

It returns the first non-null parameter else it returns a null.

Answer: C, D. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,Ö,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2), COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))

6. How many input parameters are mandatory in NVL function?

0

1

2

3

Answer: C. The NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.

7. What is wrong in the following statement?

NVL (ifnull, original)

There is nothing wrong

The parameter original is not required

The parameter ”ifnull” is not required

The correct statement is NVL (original,ifnull)

Answer: D. The NVL function evaluates whether a column or expression of any data type is null or not. If the term is null, an alternative not null value is returned; otherwise, the initial term is returned.

8. What will be the output of the following query?

SELECT NVL(1234) FROM dual;

1234

1000

NULL

ORA-00909:invalid number of arguments error

Answer: D. he NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null.

9. What will be output of the following query?

SELECT NVL(1234,'' '') FROM dual;

A white space i.e. ” ”

1234

NULL value

ORA-01722: invalid number

Answer: D. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter. The 1234 should be in single quotes. Implicit conversion of data type doesn”t happen in this case.

10. What will be outcome of the following query?

SELECT NVL(SUBSTR(''abc'',-4),''SUBSTR didn''t work'') FROM dual;

abc

bc

c

SUBSTR didn”t work

Answer: D.

11. You need to extract a report which gives the first name, last name and the commission percentage earned by all the employees in department 100. The report should not have any columns which are empty. All the columns should have at least a ”0” if there is no value for them. Which of the following queries will fulfill this requirement? (Consider the table structure as given)

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)

SELECT first_name, last_name, commission_pct*salary FROM employees WHERE department_id = 100;

SELECT first_name, last_name, NVL(commission_pct*salary,0) monthly_commission FROM employees WHERE department_id = 100;

SELECT first_name, last_name, NVL(commission_pct,0)*salary monthly_commission FROM employees WHERE department_id = 100;

SELECT first_name, last_name, commission_pct*salary FROM employees;

Answer: B, C.

12. What are the types of Data conversions in Oracle DB?

Implicit conversions

Explicit conversions

External conversions

Physical conversions

Answer: A, B. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

13. What happens during an implicit conversion in Oracle DB?

Oracle DB implicitly converts one data type to the expected data type

The user has to convert the data type to the expected data type

Oracle DB doesn”t convert any data type

Implicit conversion can be controlled by the user

Answer: A. If Oracle database implicitly converts a value to a compatible data type, it is known as Implicit conversion.

14. What happens during an explicit conversion in Oracle DB?

Oracle DB converts one data type to the other and displays to the user explicitly

Oracle DB prompts the user to convert one data type to the other and then converts the data type

The user uses conversion functions supplied by Oracle DB to convert data types

The data type is never converted explicitly in Oracle DB

Answer: C. When the programmer has to programmatically convert a value using one of the conversion functions, it is known as explicit conversion.

15. Which of the following conversion methods is recommended for the reliability of SQL statements in Oracle DB?

Implicit and Explicit conversions

Implicit conversion

Explicit conversion

None of the above

Answer: C. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

16. Which of the following is a valid implicit conversion performed by Oracle?

NUMBER TO VARCHAR2

NUMBER TO DATE

CHAR TO DATE

DATE TO VARCHAR2

Answer: A, D.

17. Examine the structure of the EMPLOYEES table as given.

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)

Which conversion method is applied to the following query?

SELECT first_name, salary FROM employees WHERE hire_date > ''01-JAN-13

No conversion happens and this query results in an ORA error

Explicit conversion

Implicit conversion

Both Implicit and explicit conversion

Answer: C. The string (VARCHAR2 or CHAR) is converted implicitly to a DATE by Oracle giving the required output as selected.

18. Which of the following is supported with respect to expression evaluation is supported by Oracle DB?

NUMBER TO CHAR

DATE TO VARCHAR2

CHAR to DATE

NUMBER TO DATE

Answer: A, B. DATE and NUMBER values can easily be converted to their character equivalents. Implicit character to date conversions are possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY].

19. What is mandatory for and implicit conversion of CHAR to NUMBER in Oracle to work?

Nothing in particular is mandatory for this type of conversion

It is mandatory that the character string represents a valid number

No such conversion is supported by Oracle

CHAR to NUMBER has to be converted explicitly only

Answer: B. Character data must represent a valid number to be considered for implicit conversion.

20. Which of the following expressions can be used explicitly for a conversion of a CHAR to a NUMBER?

TO_CHAR

Use TO_DATE and then convert the date to a NUMBER

TO_NUMBER

Such conversion is not possible

Answer: C. The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask.

21. Which of the following expressions can be used explicitly for a conversion of a NUMBER to a CHAR?

TO_CHAR

Use TO_DATE and then convert the date to a NUMBER

TO_NUMBER

Such conversion is not possible

Answer: A. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

22. Which of the following expressions can be used explicitly for a conversion of a CHAR to a DATE?

TO_CHAR

Use TO_DATE and then convert the date to a NUMBER

TO_NUMBER

TO_DATE

Answer: D. The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date time elements comprising a DATE. When strings with only a subset of the date time elements are converted, Oracle provides default values to construct a complete date. Components of character strings are associated with different date time elements using a format model or mask.

23. Which of the following expressions can be used explicitly for a conversion of a DATE to a CHAR?

TO_CHAR

TO_DATE

TO_NUMBER

Such conversion is not possible

Answer: A. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

24. Which of the following are the functions for explicit conversion provided by Oracle to convert one data type to the other?

TO_CHAR

TO_DATE

TO_NUMBER

All of the above

Answer: D. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

25. Interpret the working of the below function.

TO_CHAR(number/date, [format], [nlsparameters])

It converts a VARCHAR2 to a CHAR

It converts a number/date to a VARCHAR2 string with the format model [format]

It converts a VARCHAR2 to a NUMBER or a DATE

[NLSPARAMETERS] is mandatory in the statement

Answer: B. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

26. What does the [NLSPARAMETERS] clause in the following statement specify?

TO_CHAR(number/date, [format], [nlsparameters])

Decimal character

Group separator

Currency symbol

All of the above

Answer: D.

27. What value will the TO_CHAR (number/date, [format], [nlsparameters]) use if the [nlsparameters] parameter is omitted?

It throws an ORA error

The [nlsparameters] parameter is mandatory and it can”t be omitted.

It will use the default parameter values for the session.

It will use the default parameter values set during the database design.

Answer: C. By default, the TO_CHAR function considers the NLS settings of the current active session.

28. What is true about the following statement?

TO_CHAR(number/date, [format], [nlsparameters])

The nlsparameters parameter specifies the language in which the month and day names are returned.

The nlsparameters parameter is omitted on the execution of the above statement.

The nlsparameters parameter will return a NULL whether specified or not

The nlsparameters parameter will return the default language of the DB on every execution

Answer: A.

29. What is true regarding the following statement in Oracle DB?

TO_NUMBER(char, [format],[nlsparameters])

It converts any string to a number in the format specified in [format]

It converts only a NUMBER to the desired format as mentioned in [format]

It converts a string with digits to a number in the format specified in [format]

The result of this function is always a character

Answer: C. The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask.

30. What is true regarding the following statement in Oracle DB?

TO_DATE(char, [format],[nlsparameters])

It converts any string to a DATE in the format specified in [format]

It converts only a DATE to another DATE in the desired format as mentioned in [format]

It converts a string with DATE to a number in the format specified in [format]

It converts a string with DATE to a DATE in the format specified in [format]

Answer: C. The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date time elements comprising a DATE.

31. What will be the result if the [format] parameter in the following statement is omitted?

TO_DATE(char, [format],[nlsparameters])

It will return a DATE value with the format DD-MON-YY

It will return a DATE value with the format DD-MON-RR

It will return a character value

It will return a NUMBER value

Answer: A.

32. Which of the following is true about the following statement in Oracle DB?

TO_CHAR(date, ''frmt'')

The fmt can be written in double quotes as well as single quotes.

Case of the fmt doesn”t matter in this function

Fmt can include any character or NUMBER

The fmt has to be enclosed in single quotes and has to be a valid date format.

Answer: D.

33. What will the following statement on execution yield?

SELECT TO_CHAR (''01-JAN-13'' ''DD-MON-YY'') FROM dual;

01-JAN-13

01-01-2013

An ORA error

1-JAN-13

Answer: C. The parameters ”01-JAN-13” and format model should be separated by a “,”.

34. What is true about the [fmt] parameter in the following statement?

TO_DATE ([date as string],[format])

The fmt can be written in double quotes as well as single quotes.

Case of the fmt doesn”t matter in this function

The [fmt] parameter has an ”fm” element which removes spaces and suppresses leading zeroes.

Fmt can include any character or NUMBER

Answer: C.

35. What is the abbreviation for the FM modifier in Oracle DB?

First Move

Filter Mode

Fill Mode

First Mode

Answer: C. The format model ”fm” stands for Fill Mode.

36. What is the abbreviation for the FX modifier in Oracle DB?

First Expression

Fill Expression

First Extra

Format Exact

Answer: D. The format model ”fm” stands for Format Exact.

37. How many maximum places for display will Oracle DB allocate to the Month element in the following statement?

SELECT TO_CHAR (sysdate, ''fmMonth'') FROM dual;

5

6

7

9

Answer: D. The longest word for Month is ”September” and hence Oracle pads according to 9 places for the display of the Month parameter.

38. Which of the following is true about the FM modifier in Oracle DB?

This modifier suppresses blank padding in the subsequent character elements such as MONTH

This modifier suppresses leading zeroes for subsequent number of elements such as MI

This modifier has no effect on the date format

This modifier is mandatory for all the date formats used with the function TO_CHAR

Answer: A, B.

39. What happens when the FM modifier is not used in the DATE format model in Oracle DB?

The result of the character element is left padded with blanks to a variable length

The result of the character element is right padded with blanks to a fixed length

The leading zeroes are not returned in the result of the character element

The length of the return value is fixed if the FM modifier is used

Answer: B.

40. How is a number result justified in the output buffer in a number format element of a TO_CHAR function when the FM modifier is used?

Right

Left

Centre

None of the above

Answer: B. The FM modifier suppresses blanks added to the left of the number.

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

SELECT TO_CHAR (TO_DATE(''01-JAN-13''), ''fmDD Month YYYY'') FROM dual;

1 January2013

1 January 2013

1 Jan 2013

1 January 13

Answer: B. The TO_CHAR formats the input date as per the given format model.

42. How many spaces will be added to the ”DD” of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''fmDD Month YYYY'') FROM dual;

0

1

2

3

Answer: A. The FM modifier removes all the padded spaces from the Date format..

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

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''fmDdspth "of" Month YYYY fmHH:MI:SS AM'') FROM dual;

It will return an ORA error because of the use of double quotes in the Date format

1st January 2013

First of JANUARY 2013 12:00:00 AM

First of January 2013 12:00:00 AM

Answer: D. The TO_CHAR formats the input date ”01-JAN-13” as per the given format.

44. Which of the following specifies the exact match for the character argument and the date format model of a TO_DATE function?

TO_DATE

TO_CHAR

FM

FX

Answer: D.

45. What is true about the FX modifier in the Oracle DB?

It is case sensitive

It ignores the spaces in the character argument when matching with the Date format model mentioned

The punctuations and quoted text in the character argument do not necessarily match the format model

None of the above

Answer: D.

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

SELECT TO_DATE (''January 21, 2013'' , ''fxMonth DD, YYYY'') FROM dual;

It will execute successfully

It will give the result January 21, 2013

It creates an ORA error

It will give the result JANUARY 21, 2013

Answer: C. The character argument should match exactly with the format model if FX is used. Here the extra spaces after January are mismatching.

47. What is true about the FX modifier in Oracle DB?

It can be used with TO_CHAR

It can be used with both TO_CHAR and TO_DATE

It can be used with only TO_DATE

None of the above

Answer: C. The FX format modifier can only be used with the TO_DATE function.

48. Assuming the SYSDATE is 01-JAN-13, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''DDTH'') FROM dual;

1st of January

1st

1 ST

01ST

Answer: D.

49. Assuming the SYSDATE is 01-JAN-13, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''fmDDTH'') FROM dual;

1st of January

1st

1ST

01ST

Answer: C.

50. Assuming the SYSDATE is 01-JAN-13 and falls on Tuesday, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''fmDay'')||''''''s Meeting'' FROM dual;

Tuesday

TUESDAY

TUESDAY”s Meeting

Tuesday”s Meeting

Answer: D.

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

SELECT TO_DATE(''01 / JAN / 13'',''DD-MON-YY'') FROM dual;

ORA error

01-JAN-2013

01-JANUARY-13

01-JAN-13

Answer: D.

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

SELECT TO_DATE(''01 ## JAN / 13'',''DD-MON-YY'') FROM dual;

ORA error

01-JAN-2013

01-JANUARY-13

01-JAN-13

Answer: A. Use a single delimiter between the dates.

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

SELECT TO_DATE(''01/JAN/13'',''fxDD-MON-YY'') FROM dual;

01-JAN-2013

ORA error

01-JAN-13

01-JANUARY-13

Answer: B. With the format exact modifier, the input literal must match the format string.

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

SELECT TO_DATE(''01-JAN-13'',''fxDD-MON-YY'') FROM dual;

01-JAN-2013

ORA error

01-JAN-13

01-JANUARY-13

Answer: C.

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

SELECT TO_DATE (''11-JAN-2013'',''fxDD-MON-YYYY'') FROM dual;

11-JAN-13

11-01-13

11-JAN-2013

ORA error

Answer: C.

56. An employee Allen was hired on 1-JAN -13. What will be the outcome of the following query? (Assume that the NLS parameter for the session is set to DD-MON-YY)

SELECT TO_DATE(hire_date, ''fxfmDD-MON-YY'') FROM employees WHERE first_name=''ALLEN

ORA error

01-JAN-2013

1-JAN-13

1-JAN-2013

Answer: C.

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

SELECT TO_CHAR(TO_DATE (''01-JAN-2013''), ''DD-Month-RR'') FROM dual;

01-JAN-13

01-01-2013

01-January-13

01-January -13

Answer: D. The Month modifier is padded up to 9 places with spaces.

Examine the structure of the EMPLOYEES table as given and answer the questions 58 and 59 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)

58. You need to list out the first and the last names for all the employees who were hired before the year 1990. Which of the following WHERE statements will give you the required results? (Assume that this list is to be generated on ”01-JAN-2013”)

WHERE TO_DATE (hire_date, ”DD-MON-YY”)

WHERE TO_DATE (hire_date, ”DD-MON-YYYY”)

WHERE TO_DATE (hire_date, ”DD-MON-YY”)

WHERE TO_DATE (hire_date, ”DD-MON-RR”)

Answer: D. Using the RR format will consider the year portion of the date between 1950 and 1999.

59. Which of the following is an example of a nested function?

SELECT lower(last_name) FROM employees;

SELECT upper (last_name) FROM employees;

SELECT concat (first_name, last_name) FROM employees;

SELECT upper (concat(SUBSTR(first_name,1,6),''_UK'')) FROM employees;

Answer: D. More than one functions in a function is known as nesting of functions.

60. What is true about the COALESCE function in Oracle DB?

It can take only 2 arguments

All the arguments in the COALESCE function can be of different data types

All the arguments in the COALESCE function should be of the same data type

None of the above

Answer: C. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,Ö,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on.

61. Which of the following functions is used for conditional expressions?

TO_CHAR

COALESCE

NVL

CASE

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

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

SELECT TO_CHAR(TO_DATE(''01-JAN-13'',''DD-MON-YY''),''dy-mon-yyyy'') FROM dual;

01-jan-2013

01-jan-13

tue-jan-13

tue-jan-2013

Answer: D. The format model ”dy” spells the first three letters of the day from the input date. ”DY” will give ìTUEî and not ìtueî as in the query given above.

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

SELECT TO_CHAR(TO_DATE(''01-JAN-13'',''DD-MON-YY''),''fmDAY-mon-yyyy'') FROM dual;

1-jan-2013

01-jan-13

TUESDAY -jan-13

TUESDAY-jan-2013

Answer: D. fmDAY (for all capital letters) or fmday (for all small letters) format model will spell the day of the input date without any trailing or leading spaces.

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

SELECT TO_CHAR(TO_DATE(''19-JUN-13''),''qth'') FROM dual;

1st

2nd

3rd

4th

Answer: B. The format model ”q” gives the quarter in which the given date falls. In the given query, APR-JUN is the 2nd quarter.

Examine the structure of the EMPLOYEES table as given and answer the questions 65 to 67 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)

65. Some employees joined company ABC in the second week of the current year i.e. 2013. You need to list out the first names, last names and the department IDs for all these employees. Which of the following queries will give you the required result?

SELECT first_name, last_name, department_id FROM employees WHERE TO_DATE(hire_date,''w'') >2;

SELECT first_name, last_name, department_id FROM employees WHERE TO_DATE(hire_date,''w'') between 1 and 2;

SELECT first_name, last_name, department_id FROM employees WHERE TO_CHAR(hire_date,''w'')

SELECT first_name, last_name, department_id FROM employees WHERE TO_CHAR(sysdate,''ww'') =2;

Answer: D. The format model ”ww” gives the week of the year.

66. The management of a company ”ABC” wants to find out how many employees were hired in the 3rd quarter of the year 2012. Which of the following queries will give the required result?

SELECT count(employee_id ) FROM employees WHERE TO_CHAR(hire_date, ''q'') > 1;

SELECT count(employee_id ) FROM employees Where TO_CHAR(hire_date, ''q'') = 3;

SELECT employee_id FROM employees Where TO_CHAR(hire_date, ''q'') = 3;

SELECT count(employee_id ) FROM employees Where TO_CHAR(hire_date, ''q'') between 0 and 3;

Answer: B. The format model ”q” gives the quarter of a year.

67. A certificate of achievement has to be printed and presented to all those employees who joined the organization before the year 2008 and are still a part of the organization. The printing of the first name, last name and the dates will happen by using placeholders fetched from a query. The Certificate should contain all the digits spelled out. Example: Tuesday, the 1st of January, Two Thousand and eight. The final text of the Certificate should be in the following form: This is to certify that first_name last_name who joined the organization on Tuesday, the 1st of January, Two Thousand and eight has successfully completed 5 glorious years in the company. Which of the following queries will be helpful in printing the dates as in the required format?

SELECT TO_CHAR (hire_date, ''fmDay,"the "ddth "of " Month, Yysp.'') FROM employees;

SELECT TO_CHAR (hire_date, ''Day,"the "ddth "of " Mon, Yyyy.'') FROM employees;

SELECT TO_CHAR (hire_date, ''fmDAY,"the "ddth "of " Month, Ysp.'') FROM employees;

SELECT TO_CHAR (hire_date, ''fmDay,"the "ddth "of " MONTH, Rsp.'') FROM employees;

Answer: A. The ”sp” identifier spells the year in simple english language.

68. A report has to be generated which creates an audit history table for all the employees from an available paper source. The paper source only has data for the year 2011 when the employees were hired. This data only has the year of the hire date. You need to put the date in the audit-history table as 1st of January of that particular year (without leading zeroes and spaces). Which of the following clauses will achieve this requirement?

TO_DATE(”2011”,”YYYY”)

TO_CHAR (TO_DATE (”2011”,”YYYY”),”fmMM/DD/YYYY”)

TO_CHAR(”2011”,”DD-MON-YYYY”)

TO_DATE (”01-01-2011”,”DD-MM-YYYY”)

Answer: B.

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

SELECT TO_NUMBER (''$3000'') FROM dual;

3000

$3000

NULL

ORA error

Answer: D. The query throws error of “ORA-01722: invalid number” because the given string cannot be recognized in numbers.

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

SELECT TO_NUMBER(''$3,000.67'',''$999,999.99'') FROM dual;

$3000.67

3000

3000.67

ORA error as the input string has lesser characters than the format model mentioned.

Answer: C. The appropriate format model helps the TO_NUMBER to convert given string in numbers.

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

SELECT TO_NUMBER(''$3,000,000.67'',''$999,999.99'') FROM dual;

$3,000,000.67

3000,000.67

3000.67

ORA error as the format model has lesser characters than the input string. It should be the same.

Answer: D.

72. What will the following query yield?

SELECT TO_NUMBER(''456.23'',''999.99'') FROM dual;

ORA error

456.23

456

None of the above

Answer: B.

73. What is true about the nested functions?

Nesting implies the use of output from one function as an input to another.

Nesting can be applied up to 3 levels of nesting.

Nesting are applied to Multiple row functions to any level of depth

None of the above

Answer: A. The output from a function execution is used as input for its preceding function.

74. What will be the result of the following query?

SELECT NULLIF(1,2-1) FROM dual;

0

1

NULL

None of the above

Answer: C. The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. Here 1 and the expression “2-1” are considered equal by oracle and hence NULL is returned.

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

SELECT NULLIF(''01-JAN-2013'',''01-JAN-13'') FROM dual;

1-JAN-13

01-JAN-2013

NULL

ORA error

Answer: B. Since the lengths for both the dates is different, the first parameter is returned.

76. What is the ratio of mandatory parameters to optional parameters in the COALESCE function in Oracle DB?

0:1

1:2

2: any number

None of the above

Answer: C. The COALESCE function takes two mandatory parameters and any number of optional parameters. OALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2), COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)).

77. Which of the following equations are true?

COALESCE(expr1,expr2) = NVL(expr1,expr2)

COALESCE(expr1,expr2) = NVL2(expr1,expr2,expr3)

COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))

All of the above

Answer: A, C.

78. Which of the following is the correct syntax of NVL2?

NVL(original,ifnotnull)

NVL2(original,ifnull,ifnotnull)

NVL(original,NULL)

NVL(original,ifnull) and NVL2(original,ifnotnull,ifnull)

Answer: D.

79. Which of the following functions is an ANSI standard keyword inherited in Oracle?

CASE

DECODE

Both A and B

None of the above

Answer: A. CASE is an ANSI SQL compliant and not Oracle specific.

80. What is true about the DECODE statement in Oracle DB?

DECODE(expr1,comp1,iftrue1,comp2,[iftrue2])

Comp2 is not optional

If expr1 is equal to comp1 then comp2 is returned

If expr1 is equal to comp1 then iftrue1 is returned

None of the above

Answer: C. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more.

81. What is true about the parameters in the DECODE function?

All parameters must be VARCHAR2

No expressions can be parameters to the DECODE function

All parameters must be NUMBER

The return data type is the same as that of the first matching comparison item.

Answer: D. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not.

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

SELECT DECODE (null,null,''expr3'') FROM dual;

NULL

0

Expr3

ORA error

Answer: C. DECODE considers two NULL values to be equivalent. One of the anomalies of NULL in Oracle.

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

SELECT DECODE (''elephant'',''rat'',''lion'',''tiger'',''cat'',''squirrel'',''elephant'',''koala'',''rat'',''And it continues'') FROM dual;

elephant

rat

koala

And it continues

Answer: D. The DECODE function takes at least three mandatory parameters, but can take many more.

84. What is the number of minimum mandatory parameters for the CASE expression in Oracle DB?

0

1

2

3

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression. It takes atleast 3 mandatory parameters but it can take more also.

85. Which of the following keyword combinations is used to enclose a CASE statement in Oracle DB?

CASEÖEND IF;

IFÖEND IF;

CASEÖ;

CASEÖEND;

Answer: D.

86. Which of the following values is returned in case of a false value if the ELSE block in the CASE statement is undefined?

0

NULL

Either 0 or NULL

None of the above

Answer: B.

87. Which of the following options is true if more than one WHEN..THEN levels exist in a CASE statement?

The CASE searches or compares only the first level and exists without checking other levels of WHENÖTHEN.

The CASE statement will search in all the levels of WHENÖTHEN until it finds a match.

Both A and B

None of the above

Answer: B.

88. What data types can be the search, comparison and result parameters in the CASE statement?

VARCHAR2

DATE

NUMBER

Column values, literals and expressions

Answer: D.

89. The CASE statement cannot be used in which of the following parts of an Oracle SQL query?

SELECT

None of these options

WHERE

ORDER BY

Answer: B.

90. Examine the structure of the EMPLOYEES table as given.

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)

What will be the outcome of the following query in Oracle DB?

SELECT first_name, salary, CASE department_id WHEN 100 THEN ''Accounts'' WHEN 101 THEN ''Human Resources'' WHEN 102 THEN ''Sales'' ELSE ''Unknown'' END FROM employees;

It will create an ORA error as more than one WHENÖTHEN statements cannot be written in the CASE statement.

It will display the department IDs as 100,101,102

It will only display ”Accounts” where ever the department ID 100 appears and ignore the remaining commands.

None of the above

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

91. What is the maximum number of WHENÖTHEN levels a CASE statement in Oracle DB can have?

Unlimited

1000

216

255

Answer: D.

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

SELECT NVL2( NULLIF (''BMW'',''AUDI''), ''HYUNDAI'', ''FERRARI'' ) FROM dual;

BMW

FERRARI

NULL

HYUNDAI

Answer: D. The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. If the first term is not null, the second parameter is returned, else the third parameter is returned.

93. Assuming the SYSDATE is 01-JAN-13 , what will the following query yield?

SELECT TO_CHAR (sysdate, ''fmddth" of" Month YYYY'') FROM dual;

1st January, 2013

1st of Jan, 2013

01st of January, 2013

1st of January 2013

Answer: D. The ìthî format model gives the day of the date as ìstî or ìthî.

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

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''MmSP Month Yyyysp'') FROM dual;

First January Two Thousand Thirteen

First JAN Two Thousand Thirteen

One January Two Thousand Thirteen

None of the above

Answer: C.

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

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''DD-MON-YYYY hh24SpTh'') FROM dual;

First January Two Thousand Thirteen

One January Two Thousand Thirteen

ORA error

01-JAN-2013 zeroeth

Answer: D. Spelling out the timestamp component can be done using ”SpTh” format modifier.

96. Which of these functions do the work similar to if-then-else logic in SQL statements?

TO_CHAR

TO_NUMBER

Both A and B

CASE

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

97. Examine the structure of the EMPLOYEES table as given.

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)

What will be the outcome of the following SQL query?

SELECT DECODE (salary,10000) FROM employees;

10000

NULL

0

ORA error

Answer: B. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. If the default value in the DECODE function is omitted, a NULL is returned.

98. You need to display the time of the Oracle DB session up to 3 decimal places of the fractional seconds. Which of the following queries will give the required output?

SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS.FF'') FROM dual;

SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS'') FROM dual;

SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS.FF3'') FROM dual;

SELECT TO_CHAR(sysdate, ''DD-MON-YY'') FROM dual;

Answer: C. The FF [1..9] extension to the HH:MI:SS format yields fractional seconds up to 1..9 digits in the fractional seconds.

99. Which of the following punctuation marks can be used with Dates and Times in Oracle DB?

#

@

,

:

Answer: C, D.

100. Examine the structure of the EMPLOYEES table as given.

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)

You need to find the day of the year when the employee Jaimie Patrick was hired in the company ”ABC”. Which of the following queries will give the required output?

SELECT TO_CHAR(hire_date, ''DDD'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John

SELECT TO_CHAR(hire_date, ''YYYY'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John

SELECT TO_CHAR(hire_date, ''DD-MON-YYYY'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John

SELECT TO_CHAR(hire_date, ''DD-MON-RR'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John

Answer: A. The format model ”DDD” returns the day of the year on which the given date falls.

101. A report is required to be generated which gives the timings for all the batch runs that started on midnight 1st June, 2013. These timings should be in the precision of seconds after midnight. Which of the following clauses will fulfill the requirement?

TO_CHAR(sysdate,”HH24:MI:SS”)

TO_CHAR(sysdate,”HH24:MI:SS.FF”)

TO_CHAR(sysdate,”HH24:MI:SSSS”)

TO_CHAR(sysdate,”HH24:MI:SS.FF3”)

Answer: C. the ”SSSS” format model gives the seconds after midnight.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024