0

databases Online Quiz - 234

Description: databases Online Quiz - 234
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

Which operator can be used with a multiple-row subquery?

  1. =

  2. LIKE

  3. BETWEEN

  4. NOT IN

  5. Is

  6. <>


Correct Option: D

AI Explanation

To answer this question, you need to understand the concept of subqueries in SQL.

A subquery is a query that is nested inside another query. It is used to retrieve data from one or more tables and use that result in the outer query. In the case of a multiple-row subquery, the subquery returns more than one row.

When using a multiple-row subquery, you can use various operators to compare the result of the subquery with the outer query. The correct answer in this case is option D) NOT IN.

The NOT IN operator is used to exclude rows from the result set that match the values returned by the subquery. It returns rows from the outer query where the value of a specified column does not match any value returned by the subquery.

Here's an explanation of each option:

A. = (Equals) - This operator is used to compare two values for equality. However, it is not specifically designed to work with multiple-row subqueries.

B. LIKE - The LIKE operator is used to compare a value to a pattern. It is typically used for pattern matching with wildcard characters. It is not specifically designed to work with multiple-row subqueries.

C. BETWEEN - The BETWEEN operator is used to check if a value is within a range of values. It is not specifically designed to work with multiple-row subqueries.

D. NOT IN - This operator is used to exclude rows that match the values returned by the subquery. It is specifically designed to work with multiple-row subqueries.

E. IS - The IS operator is used to compare a value to NULL. It is not specifically designed to work with multiple-row subqueries.

F. <> (Not Equal) - This operator is used to compare two values for inequality. It is not specifically designed to work with multiple-row subqueries.

In conclusion, the correct answer is D) NOT IN because it is the operator specifically designed to work with multiple-row subqueries.

Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose three.)

  1. SELECT TO_CHAR(2000, '$#,###.##') from dual

  2. SELECT TO_CHAR(2000, '$0,000.00') from dual

  3. SELECT TO_CHAR(2000, '$9,999.00') from dual

  4. SELECT TO_CHAR(2000, '$9,999.99') from dual

  5. SELECT TO_CHAR(2000, '$2,000.00') FROM dual;

  6. SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;


Correct Option: B,C,D

AI Explanation

To answer this question, we need to understand the syntax and usage of the TO_CHAR function in Oracle.

The TO_CHAR function is used to convert a number or date to a character string. It takes two arguments: the number or date to be converted and the format model specifying the desired format of the output.

Let's go through each option to understand why it is correct or incorrect:

Option A) SELECT TO_CHAR(2000, '$#,###.##') from dual This option is incorrect because the format model '#,###.##' does not include the necessary formatting for the dollar sign and the required number of digits before and after the decimal point.

Option B) SELECT TO_CHAR(2000, '$0,000.00') from dual This option is correct because the format model '$0,000.00' specifies the dollar sign, the thousands separator, and two decimal places, which matches the required format.

Option C) SELECT TO_CHAR(2000, '$9,999.00') from dual This option is correct because the format model '$9,999.00' specifies the dollar sign, the thousands separator, and two decimal places. The '9' in the format model allows for the possibility of leading zeros.

Option D) SELECT TO_CHAR(2000, '$9,999.99') from dual This option is correct because the format model '$9,999.99' specifies the dollar sign, the thousands separator, and two decimal places. The '9' in the format model allows for the possibility of leading zeros.

Option E) SELECT TO_CHAR(2000, '$2,000.00') FROM dual; This option is incorrect because it specifies the exact value that needs to be displayed, rather than using a format model to convert the number to the desired format.

Option F) SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual; This option is incorrect because the format model '$N,NNN.NN' is not a valid format model in Oracle.

The correct answers are B, C, and D. These options provide the correct format for displaying 2000 as "$2,000.00".

Which of the following correctly describe the aggregate functions?

  1. It returns a single result row based on single rows

  2. It returns a single result row based on groups of rows

  3. It can only appear in ORDER BY clauses

  4. It cannot appear in select lists


Correct Option: B

Which of the following SQL statements can calculate and return the absolute value of -33?

  1. SELECT ABS(-33) "Absolute" FROM DUAL;

  2. SELECT ABS(-33), Absolute FROM DUAL;

  3. SELECT ABS("-33") Absolute FROM DUAL;

  4. None of these


Correct Option: A
Explanation:

To solve this question, the user needs to know the syntax for calculating the absolute value in SQL. The ABS function can be used to calculate the absolute value of a number.

Now, let's go through each option and explain why it is right or wrong:

A. SELECT ABS(-33) "Absolute" FROM DUAL; This option is correct. The ABS function is used to calculate the absolute value of a number, and the syntax in this statement is correct. The "Absolute" alias is used to rename the column with the calculated value.

B. SELECT ABS(-33), Absolute FROM DUAL; This option is incorrect. The "Absolute" alias is not defined in the statement. Additionally, the ABS function is used correctly.

C. SELECT ABS("-33") Absolute FROM DUAL; This option is incorrect. The quotation marks around the number indicate that it is a string, not a number. The ABS function cannot calculate the absolute value of a string.

D. None of these This option is incorrect. Option A is the correct answer.

The Answer is: A

What does the following SQL script do? SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;

  1. it has a syntax error, the AVG clause is not valid

  2. it calculates the average of the maximum salaries of all the departments

  3. it has a syntax error, the MAX clause is not valid

  4. it has no error, but the GROUP BY clause is not effective


Correct Option: B

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) It has a syntax error, the AVG clause is not valid - This option is incorrect because the AVG clause is valid in SQL and is used to calculate the average value of a column.

Option B) It calculates the average of the maximum salaries of all the departments - This option is correct. The SQL script calculates the maximum salary for each department using the MAX function and then calculates the average of these maximum salaries using the AVG function. The result will be the average of the maximum salaries for all departments.

Option C) It has a syntax error, the MAX clause is not valid - This option is incorrect because the MAX clause is valid in SQL and is used to find the maximum value of a column.

Option D) It has no error, but the GROUP BY clause is not effective - This option is incorrect. The GROUP BY clause is effective in this script because it groups the records by the department_id column, allowing the script to calculate the maximum salary for each department separately.

The correct answer is B. This option is correct because the script calculates the average of the maximum salaries of all the departments.

Which are DML statements? (Choose all that apply)

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP


Correct Option: A,B,C,D

AI Explanation

To answer this question, you need to understand the concept of Data Manipulation Language (DML). DML statements are used to manipulate the data stored in the database. Let's go through each option to understand why it is correct or incorrect:

Option A) COMMIT - This option is a DML statement. It is used to save the changes made to the database since the last COMMIT or ROLLBACK statement.

Option B) MERGE - This option is a DML statement. It is used to perform insert, update, or delete operations on a target table based on the results of a join with a source table.

Option C) UPDATE - This option is a DML statement. It is used to modify existing records in a table.

Option D) DELETE - This option is a DML statement. It is used to delete existing records from a table.

Option E) CREATE - This option is not a DML statement. CREATE statements are part of the Data Definition Language (DDL) and are used to create database objects such as tables, indexes, and views.

Option F) DROP - This option is not a DML statement. DROP statements are part of the DDL and are used to remove database objects such as tables, indexes, and views.

The correct answers are A, B, C, and D. These options are correct because they are DML statements used for manipulating data in the database.

Which Function can return a non-Null value if passed null arguments?

  1. NULLIF

  2. LENGTH

  3. CONCAT

  4. INSTR

  5. TAN


Correct Option: C

SQL has facility for programmed handling of errors that arise during the manipulation of data.

  1. True

  2. False


Correct Option: B

AI Explanation

To answer this question, we need to understand the concept of error handling in SQL.

Option A) True - This option is incorrect. SQL does have facilities for error handling, such as the use of try-catch blocks or error handling functions like @@ERROR or TRY...CATCH. These features allow programmers to handle errors that may occur during data manipulation operations.

Option B) False - This option is correct. The statement is false because SQL does have facilities for handling errors during data manipulation.

The correct answer is B) False.

In a PL/SQL block structure, which parts are optional?

  1. a) DELCARE and BEGIN

  2. b) DECALRE and EXCEPTION

  3. c) EXCEPTION and END

  4. d) BEGIN and END


Correct Option: A,B

A Rollback statement cannot be used to close transaction.

  1. True

  2. False


Correct Option: B

AI Explanation

To answer this question, let's first understand what a rollback statement is and its purpose in a transaction.

A rollback statement is used to undo or cancel the changes made in a transaction and restore the database to its previous state. It is typically used when an error or an exceptional condition occurs during the execution of a transaction.

Now, coming to the given statement: "A Rollback statement cannot be used to close a transaction."

This statement is incorrect. A rollback statement is indeed used to close a transaction by undoing the changes made within the transaction. After executing a rollback statement, the transaction is effectively closed, and any changes made within the transaction are reverted.

Therefore, the correct answer is B) False.

You want to create a PL/SQL block of code that calculates discounts on customer orders. This code will be invoked from several places, but only within the program unit ORDERTOTAL. What is the most appropriate location to store the code that calculates the discounts?

  1. a) A stored procedure on the server.

  2. b) A block of code in a PL/SQL library.

  3. c) A standalone procedure on the client machine.

  4. d) A block of code in the body of the program unit ORDERTOTAL.

  5. e) A local subprogram defined within the program unit ORDERTOTAL.


Correct Option: E
  1. a) Raise Application procedure

  2. b) Raise Application Error procedure

  3. c) Application Error procedure

  4. d) None of the Above


Correct Option: B

Which line of the following code has an error? SELECT * FROM emp WHERE comm = NULL ORDER BY ename;

  1. SELECT *

  2. FROM emp

  3. WHERE comm = NULL

  4. There is no error in this statement.


Correct Option: D

AI Explanation

To answer this question, we need to analyze each line of the code to identify any errors:

A. SELECT * This line is correct. It specifies that all columns should be selected from the table.

B. FROM emp This line is correct. It specifies the table name "emp" from which the data should be retrieved.

C. WHERE comm = NULL This line has an error. In SQL, you cannot compare a value to NULL using the "=" operator. Instead, you should use the "IS NULL" operator to check for NULL values. The correct syntax should be: WHERE comm IS NULL

D. There is no error in this statement. This is the correct answer. The only error in the code is on line C, where the comparison operator should be "IS NULL" instead of "=".

Therefore, the correct answer is D.

  1. Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER(V_IDIN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID EXCEPTION WHEN STATS_EXIST_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE(Cannotdeletethisplayer, childrecordsexistin PLAYER_BAT_STAT table); END; What prevents this procedure from being created successfully?
  1. a) A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. b) The STATS_EXIST_EXCEPTION has not been declared as a number.

  3. c) The STATS_EXIST_EXCEPTION has not been declared as an exception.

  4. d) none of the above


Correct Option: C
Explanation:

To understand what prevents this procedure from being created successfully, we need to examine the given procedure. The procedure DELETE_PLAYER takes an input parameter V_IDIN and attempts to delete the corresponding record from the PLAYER table. It also has an exception block that handles the STATS_EXIST_EXCEPTION exception and outputs an error message.

Now, let's go through each option and see if it affects the creation of the procedure:

A. a) A comma has been left after the STATS_EXIST_EXCEPTION exception. This option is incorrect. There is no comma after the STATS_EXIST_EXCEPTION exception, so this cannot be the reason why the procedure cannot be created.

B. b) The STATS_EXIST_EXCEPTION has not been declared as a number. This option is incorrect. The STATS_EXIST_EXCEPTION is not being used as a number, so it does not need to be declared as such. It is being used as an exception.

C. c) The STATS_EXIST_EXCEPTION has not been declared as an exception. This option is correct. The STATS_EXIST_EXCEPTION exception has not been declared in the procedure. Without a declaration, the procedure does not know what to do when the exception is raised. To fix this, the STATS_EXIST_EXCEPTION should be declared as an exception before the BEGIN keyword.

D. d) none of the above This option is incorrect. As explained above, option C is the correct answer.

Therefore, the correct answer is:

The Answer is: C

Which operator will be evaluated first in the following SELECT statement? SELECT (2+3*4/2–5) FROM dual;

  1. +

  2. *

  3. /


Correct Option: B

Which two statements about the overloading feature of packages are true? (Choose two)

  1. a) Only local or packaged sub programs can be overloaded.

  2. b) Overloading allows different functions with the same name that differ only in their return types.

  3. c) Overloading allows different subprograms with the same number, type and order of the parameter.

  4. d) Overloading allows different subprograms with the same name and same number or type of the parameters.

  5. e) Overloading allows different subprograms with the same name but different in either number or type or order of parameter.


Correct Option: A,E

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) Only local or packaged subprograms can be overloaded. - This option is correct. Overloading can only be applied to subprograms that are defined either locally or within a package.

Option B) Overloading allows different functions with the same name that differ only in their return types. - This option is incorrect. Overloading allows different subprograms (procedures or functions) with the same name, but they must differ in either the number or type or order of parameters, not just the return types.

Option C) Overloading allows different subprograms with the same number, type, and order of the parameter. - This option is incorrect. Overloading requires the subprograms to have differences in either the number or type or order of parameters, not just the same.

Option D) Overloading allows different subprograms with the same name and the same number or type of the parameters. - This option is incorrect. Overloading requires differences in either the number or type or order of parameters, so subprograms with the same number and type of parameters cannot be overloaded.

Option E) Overloading allows different subprograms with the same name but different in either number or type or order of parameters. - This option is correct. Overloading allows subprograms with the same name but with differences in either the number or type or order of parameters.

The correct answer is A and E. These options are correct because overloading can only be applied to local or packaged subprograms, and it allows different subprograms with the same name but different in either the number or type or order of parameters.

Which two statements about packages are true? (Choose two)

  1. a) Packages can be nested.

  2. b) You can pass parameters to packages.

  3. c) A package is loaded into memory each time it is invoked.

  4. d) The contents of packages can be shared by many applications.

  5. e) You can achieve information hiding by making package constructs private.


Correct Option: D,E

Which of the following statements is false with respect to packages?

  1. a) The package itself cannot be called, parameterized, or nested.

  2. b) The format of a package is similar to that of a subprogram.

  3. c) The contents can be shared by many applications once written.

  4. d) None of the above.


Correct Option: D

When you use the DEFINE variable command, what datatype is the variable?

  1. VARCHAR2

  2. CHAR

  3. LONG

  4. NUMBER


Correct Option: B
- Hide questions