Test Your SQL Basics - Part_2

Conversion Functions Questions

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

SELECT ROUND(144.23,-1) FROM dual;

1. 140
2. 144
3. 150
4. 100

---

2. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database?

1. NOT NULL
2. INSTR
3. SUBSTR
4. COUNT

---

3. Which of the following statements are true regarding the single row functions?

1. They accept only a single argument.
2. They can be nested only to two levels.
3. Arguments can only be column values or constants.
4. They can return a data type value different from the one that is referenced.

---

4. Which of the following type of single-row functions cannot be incorporated in Oracle DB?

1. Character
2. Numeric
3. Conversion
4. None of the above

---

5. Which of the following is true for the statement given as under.

NVL2 (arg1, arg2, arg3)

1. Arg2 and Arg3 can have any data type
2. Arg1 cannot have the LONG data type
3. Oracle will convert the data type of expr2 according to Arg1
4. If Arg2 is a NUMBER, then Oracle determines the numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

---

6. What is true about the NULLIF function in Oracle DB?

1. NULLIF(expr1,expr2) will return expr2 if the two expressions are NOT NULL.
2. NULLIF(expr1,expr2) will return 0 if the two expressions are NULL.
3. NULLIF(expr1,expr2) will return NULL if the two expressions are equal.
4. Expr1 can be NULL in NULLIF(expr1, expr2)

---

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

1. Perform calculations on data
2. Convert column data types
3. Modify individual data items
4. None of the above

---

8. What is the most appropriate about single row functions?

1. They return no value
2. They return one result per row and operate on all the rows of a table.
3. They return one result per row with input arguments
4. They return one result per set of rows and operate on multiple rows.

---

9. What is the number of arguments Single Row functions accept?

1. 0
2. Only 1
3. Only 2
4. 1 or more than 1

---

10. Which of the following is an exception to the return value of a DATE type single-row function?

1. TO_DATE
2. SYSDATE
3. MONTHS_BETWEEN
4. TO_NUMBER

---

11. What is true about the CONCAT function in Oracle DB?

1. It can have only characters as input.
2. It can have only 2 input parameters.
3. It can have 2 or more input parameters
4. It joins values by putting a white space in between the concatenated strings by default.

---

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

1. It extracts a string of determined length
2. It shows the length of a string as a numeric value
3. It finds the numeric position of a named character
4. It trims characters from one (or both) sides from a character string

---

Answer
1-A,   2-D,   3-D,   4-D,  5-D,   6-C,   7-D,   8-B,   9-D,  10-C,   11-B,  12-A

---

Using the Group Functions Questions

1. Which of the following is NOT a GROUP BY function?

1. MAX
2. MIN
3. NVL
4. AVG

---

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

1. Nested Tables
2. NUMBER
3. CLOB
4. DATE

---

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

1. 12
2. 6
3. 9
4. Throws exception because COUNT function doesn't works with NULL values

---

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

1. GROUP BY
2. GROUPING SETS
3. CUBE
4. ROLLUP

---

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

1. The MIN function can be used only with numeric data.
2. The MAX function can be used only with date values.
3. The AVG function can be used only with numeric data.
4. The SUM function canít be part of a nested function.

---

6. Which of the following functions is used to calculate the total value stored in a specified column?

1. COUNT
2. ADD
3. TOTAL
4. SUM

---

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

1. SUM
2. NVL
3. MAX
4. MIN

---

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

1. 2-3-4-5-1-6
2. 1-2-3-4-5-6
3. 6-5-4-3-2-1
4. 5-4-2-3-1-6

---

9. What is true of using group functions on columns that contain NULL values?

1. Group functions on columns ignore NULL values.
2. Group functions on columns returning dates include NULL values.
3. Group functions on columns returning numbers include NULL values.
4. Group functions on columns cannot be accurately used on columns that contain NULL values.

---

10. Which of the following statements are true?

1. AVG and SUM can be used only with numeric data types.
2. STDDEV and VARIANCE can be used only with numeric data types.
3. MAX can be used with LONG data type.
4. MAX and MIN cannot be used with LOB or LONG data types.

---

11. Up to how many levels, the group functions can be nested?

1. 1
2. 2
3. 3
4. No limits

---

12. Which of the following are true about the CUBE extension of GROUP BY?

1. Enables performing multiple GROUP BY clauses with a single query.
2. Performs aggregations for all possible combinations of columns included.
3. Performs increasing levels of cumulative subtotals, based on the provided column list.
4. None of the above

---

Answer
1-C,   2-B,   3-C,   4-A,  5-C,   6-D,   7-B,   8-A,   9-A,  10-(A,B,D),   11-B,  12-B

---

Get Data from Multiple Tables Questions

1.Which of the following is not related to a Relational Database?

1. Selection
2. Projection
3. Joining
4. None of the above

---

2.Which of following will be used to join rows with other tables if the column values fall in a range defined by inequality operators?

1. Equijoin
2. Simple join
3. Non-equijoin
4. None of the above

---

3.What is true about a cartesian join of two tables in Oracle DB?

1. It must be avoided as it is costly and non optimized
2. It is formed when every row from one table is joined with all rows in the second table
3. Both A and B
4. None of the above

---

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

1. There is no condition
2. At least one of the columns in both the tables should be common.
3. The names of the columns in both the joining tables should be the same for using joins
4. None of the above

---

5.What is true about Natural joins in Oracle DB?

1. The column names of the source and the target tables should be identical
2. If the column names of the source and the target tables are not same, Oracle implicitly does the needful
3. NATURAL JOINS, USING and ON are the keywords associated with Natural Joins
4. All of the above

---

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

1. JOIN..ON
2. Either NATURAL JOIN or JOIN...ON clauses
3. Both A and B
4. None of the above

---

7. What are Cartesian Joins also known as in Oracle DB?

1. Equi-join
2. Anti-join
3. Cross-Join
4. None of the above

---

8.Which of the following is used to avoid the ambiguous column problem in Oracle DB?

1. ;
2. ,
3. .
4. /

---

9.How many tables can be joined by using the JOINS in Oracle DB?

1. 1
2. 2
3. 255
4. No limit

---

10.What is true about Non-equijoins in Oracle DB?

1. They join based on the keyword NON-EQUI JOIN
2. They are used using the JOIN..ON clause with "=" sign
3. The results are obtained when the result of the inequality mentioned evaluates to true.
4. None of the above

---

11.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

1. The result of the inequality match operation is true
2. The result of the inequality match operation is 0
3. The result of the inequality match operation is 1
4. The result of the inequality match operation is false

---

12.In what scenarios can we use Self-Joins ideally in Oracle DB?

1. When we need to find the duplicate records in a table
2. When we need to obtain NULL values from a table
3. When we need to display a column of a table twice
4. When we need to display hierarchy of relevant entities

---

Answer
1-D,   2-C,   3-B,   4-B,  5-C,   6-A,   7-C,   8-C,   9-D,  10-C,   11-A,  12-D

---

Subqueries to Solve Queries Questions

1. Which of the following are the types of sub-queries?

1. Ordered sub-queries
2. Grouped sub-queries
3. Single row sub-queries
4. None of the above

---

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

1. SELECT
2. WHERE
3. ORDER BY
4. GROUP BY

---

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

1. HAVING
2. WHERE
3. FROM
4. All of the above

---

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

1. >=
2. <
3. =
4. All of the above

---

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

1. Sub-queries can be written on either side of a comparison operator
2. Parenthesis is not mandatory for sub-queries
3. Single-row sub-queries can use multi-row operators but vice versa is not possible
4. All of the above

---

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

1. They return only one row
2. They use single-row operators
3. Both A and B
4. None of the above

---

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

1. <=
2. ANY >=
3. !=
4. >=

---

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

1. TRUE
2. FALSE
3. NULL
4. 0

---

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

1. It gives the maximum value of salary
2. It gives the minimum value of salary
3. It means it gives the values that are lesser than the highest
4. None of the above

---

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

1. It will execute successfully giving the same result.
2. It will throw an ORA error
3. It will execute successfully but give the employees' details who have salaries lesser than all the employees with job_id 'FI_ACCOUNTANT'.
4. None of the above

---

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

1. Sub-queries have to be executed separately from the main queries
2. Sub-queries can be executed at the will of the user, they are not related to the main query execution
3. Sub-queries are equal to two sequential queries where the results of inner query are used by the main query
4. All of the above

---

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

1. Using GROUP BY
2. Using sub-queries
3. Using HAVING
4. None of the above

---

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

1. 255
2. 300
3. 216
4. Unlimited

---

Answer
1-C,   2-A,   3-D,   4-D,  5-A,   6-C,   7-B,   8-A,   9-C,  10-C,   11-C,  12-D,   13-D

---

Using the Set Operators Questions

1.Which SET operator does the following figure indicate?

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

---

2.Which SET operator does the following figure indicate?

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

---

3.What is true about the UNION ALL operator?

1. It returns rows from the combined queries along with NULL values
2. It returns rows for the combined queries after eliminating duplicates
3. It returns rows for the combined queries along with duplicate values
4. It returns rows for the combined queries ignoring the NULL values

---

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

1. UNION, UNION ALL, INTERSECT and MINUS
2. MINUS, UNION, UNION ALL and INTERSECT
3. INTERSECT, MINUS, UNION ALL, UNION
4. Equal precedence

---

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

1. Left to Right
2. Right to Left
3. Random Evaluation
4. Top to Bottom

---

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

1. SET operators cannot be used in sub-queries
2. SET operators can only be used in the WHERE clause
3. ORDER BY can be used for all queries combined by a SET operator
4. SET operators can be used in sub-queries

---

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

1. The order of usage of the SET operators can be changed to change the precedence
2. The equal precedence cannot be changed
3. Parenthesis can be used to change the precedence
4. None of the above

---

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

1. Oracle will convert them implicitly and return a VARCHAR2 data type result
2. Oracle will convert them implicitly and return a NUMBER data type result
3. An ORA error is thrown
4. None of the above

---

9.What is true about the UNION operator?

1. It eliminates the duplicate values ignoring NULL values
2. It returns duplicate values ignoring NULL values
3. It returns duplicate values including NULL values
4. It eliminates duplicate values and does not ignore NULL values

---

10.What is true about the INTERSECT operator?

1. The number of columns and data types of the columns in the component queries should be the same
2. The names of the columns and data types of the columns in the component queries should be the same
3. Both A and B
4. None of the above

---

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

1. The result is altered
2. The result remains the same
3. The sorting changes on alteration
4. None of the above

---

Answer
1-A,   2-C,   3-C,   4-D,  5-(A,D),   6-D,   7-C,   8-C,   9-D,  10-A,  11-B

---

Learn Database Programming?