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

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

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

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

Using the Set Operators Questions

1.Which SET operator does the following figure indicate?
Table UNION
  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. MINUS
2.Which SET operator does the following figure indicate?
Table INTERSECT
  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

Learn Database Programming?

Location: Mumbai, Maharashtra, India

Comments

Post a Comment

Popular posts from this blog

How E-commerce Sites can Increase Sales with Pinterest?

Image
Pinterest is basically a platform for users to visually share and discover exciting new interests by posting, or “pinning”, as it’s popularly called, videos or images to their own boards or that of others. If you’re looking to drive more traffic and sales to your e-commerce site, Pinterest is one of the social networks you must add to your marketing arsenal. People like to pin products on Pinterest and plan purchases. If used properly, your e-commerce site should be able to generate a high revenue with Pinterest. Let's see some of the best ways to harness the full potential of this social media platform: 1. Pick Your Pins Carefully Quality over quantity – that’s one rule every business owner is aware of. And if you’re planning to boost your e-commerce sales using Pinterest, this rule applies even more to you. Of course, volume is important but only to keep things consistent. Pin all your new items, engage users regularly, and keep things fresh. But, as Pinterest is an e
Keep reading

Every thing U can do with a Link-List + Programming_it_in_JaVa

Image
A linked list is a sequence of data structures, which are connected together via links. Linked List is a sequence of links which contains items. Each link contains a connection to another link. Linked list is the second most-used data structure after array.  Following are the important terms to understand the concept of Linked List: Link − Each link of a linked list can store a data called an element. Next − Each link of a linked list contains a link to the next link called Next. LinkedList − A Linked List contains the connection link to the first link called First. Linked list can be visualized as a chain of nodes, where every node points to the next node: As per the above illustration, following are the important points to be considered. Linked List contains a link element called first. Each link carries a data field(s) and a link field called next. Each link is linked with its next link using its next link. Last link carries a
Keep reading

Test Your SQL Basics - Part_1

The SQL SELECT Statement Questions 1. Determine the output of the below query SELECT '6+9'  FROM dual; A) 15 B) 6+9 C) 6 D) 9 2. At least how many clauses must be present in a SELECT statement? A) 0 (Zero) B) 1 C) 2 D) 5 3. Which of the following statement is correct? A) EVERY table expression is a part of SELECT statement B) Each SELECT statement does not consist of a table expression C) Every SELECT statement is built from a table expression D) All of the above 4. Which of the following are the optional clauses? A) SELECT and WHERE B) WHERE, GROUP BY and ORDER BY C) SELECT, WHERE and ORDER BY D) SELECT, WHERE, ORDER BY and GROUP BY 5. Which of the following is the last clause that is executed in a query? A) SELECT B) FROM C) WHERE D) GROUP BY 6. Which of the following is the correct basic syntax of SELECT clause? A) SELECT column1, column2,….columnx WHERE table_name; B) SELECT column1, column2,….columnx C) SELECT column1, column2,…columnx
Keep reading