0

databases Online Quiz - 100

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

Select the VALID trigger type(s)?

  1. AFTER statement trigger

  2. INSERT row trigger

  3. DELETE row trigger

  4. UPDATE row trigger


Correct Option: A

AI Explanation

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

Option A) AFTER statement trigger - This option is correct. AFTER statement triggers are valid trigger types in database systems. They are executed after a SQL statement is executed.

Option B) INSERT row trigger - This option is incorrect. INSERT row triggers are not valid trigger types. Instead, you can use an AFTER INSERT trigger or a BEFORE INSERT trigger.

Option C) DELETE row trigger - This option is incorrect. DELETE row triggers are not valid trigger types. Instead, you can use an AFTER DELETE trigger or a BEFORE DELETE trigger.

Option D) UPDATE row trigger - This option is incorrect. UPDATE row triggers are not valid trigger types. Instead, you can use an AFTER UPDATE trigger or a BEFORE UPDATE trigger.

The correct answer is A) AFTER statement trigger. This option is correct because AFTER statement triggers are valid trigger types in database systems. They are executed after a SQL statement is executed.

Which of the following statements about an SQL statement are not correct?

  1. SQL statements are not case-sensitive, unless indicated.

  2. Keywords can be abbreviated.

  3. SQL statements can be on one or more lines.

  4. Keywords cannot be split across lines.


Correct Option: B

What is a database cursor?

  1. A cursor is SQL keyword specifying a retrieved data order.

  2. Cursor is acronym for Current Set Of Records and is a database object pointing to a currently selected set of records.

  3. A blinking vertical line that indicates the location of the next input on the display screen.

  4. None of the above.


Correct Option: B
Explanation:

To solve this question, the user needs to have some understanding of databases and their operations.

Option A: This option is incorrect. The SQL keyword that specifies the order of retrieved data is "ORDER BY" and not "cursor."

Option B: This option is correct. A cursor is a database object that points to a currently selected set of records. It allows you to retrieve and manipulate data row by row. The term "cursor" comes from the idea of a cursor in a text editor, which is a movable indicator that shows where the next action will occur.

Option C: This option is incorrect. The blinking vertical line that indicates the location of the next input on the display screen is called a "cursor," but it is not related to database operations.

Option D: This option is incorrect because option B is the correct answer.

Therefore, The Answer is: B.

What does ACID stand for?

  1. Access. Constraint. Index. Data.

  2. Atomicity. Consistency. Isolation. Durability.

  3. Access. Consistency. Isolation. Data.

  4. None of the above


Correct Option: B
  1. foo_text varchar2(10) := 'hello world';

  2. foo_char char(1) := 'Y';

  3. foo_number varchar2(10);

  4. foo_text number(10);


Correct Option: A
Explanation:

The incorrect variable declarations are A

  • A. foo_text varchar2(10) := 'hello world'; is incorrect because the variable foo_text is declared as a varchar2 type, but the initial value 'hello world' is a string. A varchar2 type can only store a sequence of characters, while a string can store a sequence of characters and other special characters, such as spaces and symbols.

The correct variable declarations are B, C, D.

  • B. foo_char char(1) := 'Y'; is correct because the variable foo_char is declared as a char type, which is a special type of varchar2 type that can only store a single character. The initial value 'Y' is a single character, so it is a valid value for the foo_char variable.
  • C. foo_number varchar2(10); is correct because the variable foo_number is declared as a varchar2 type, which can store a sequence of characters. The initial value 'hello world' is a sequence of characters, so it is a valid value for the foo_number variable.
  • D. foo_text number(10); is correct because the variable foo_text is declared as a number type, which can store an integer

Therefore, the correct answer is A.

What command can you use to see the errors from a recently created view or stored procedure?

  1. SHOW MISTAKES;

  2. SHOW ERRORS;

  3. DISPLAY ERRORS;

  4. DISPLAY MISTAKES;

  5. None of the above.


Correct Option: B

AI Explanation

To answer this question, you need to understand how to view errors from a recently created view or stored procedure.

Option A) SHOW MISTAKES - This option is incorrect because there is no "SHOW MISTAKES" command in most database management systems.

Option B) SHOW ERRORS - This option is correct because the "SHOW ERRORS" command is commonly used to view the errors from a recently created view or stored procedure. This command displays the compilation errors that occurred during the creation of the object.

Option C) DISPLAY ERRORS - This option is incorrect because there is no "DISPLAY ERRORS" command in most database management systems.

Option D) DISPLAY MISTAKES - This option is incorrect because there is no "DISPLAY MISTAKES" command in most database management systems.

Therefore, the correct answer is B) SHOW ERRORS. This option is correct because it is commonly used to view the errors from a recently created view or stored procedure.

What is the value of l_child_number? DECLARE l_parent_number NUMBER := 1; BEGIN DECLARE l_child_number NUMBER := 2; BEGIN l_child_number := l_parent_number + l_child_number; END; DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number)); EXCEPTION WHEN OTHERS THEN l_child_number := 0; DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number); END;

  1. 1

  2. 2

  3. 3

  4. 0

  5. None of the above


Correct Option: D

AI Explanation

To determine the value of l_child_number, let's go through the code step by step:

  1. The variable l_parent_number is declared and assigned a value of 1.
  2. Inside the outer BEGIN block, the variable l_child_number is declared and assigned a value of 2.
  3. Inside the inner BEGIN block, l_child_number is updated by adding l_parent_number and l_child_number. Therefore, l_child_number becomes 1 + 2 = 3.
  4. After the inner BEGIN block, the value of l_child_number is printed using DBMS_OUTPUT.PUT_LINE.
  5. If any exception occurs in the code, the WHEN OTHERS block is executed, and l_child_number is assigned a value of 0.
  6. Since there are no exceptions in the code, the value of l_child_number remains 3, and it is printed.

Therefore, the correct answer is D) 0.

Which of the following is not a valid Oracle PL/SQL exception.

  1. NO_DATA_FOUND ORA-01403

  2. DUP_VAL_ON_INDEX ORA-00001

  3. TWO_MANY_ROWS ORA-01422

  4. OTHERS

  5. None of the above. These are all valid.


Correct Option: C
Explanation:

To solve this question, the user needs to have knowledge of Oracle PL/SQL exceptions and their syntax.

The correct answer is option C. TWO_MANY_ROWS ORA-01422 is not a valid Oracle PL/SQL exception.

Explanation for each option:

A. NO_DATA_FOUND ORA-01403: This is a valid exception in Oracle PL/SQL. It is raised when a SELECT INTO statement returns no rows.

B. DUP_VAL_ON_INDEX ORA-00001: This is a valid exception in Oracle PL/SQL. It is raised when a unique index constraint is violated.

C. TWO_MANY_ROWS ORA-01422: This is not a valid exception in Oracle PL/SQL. It is an invalid exception.

D. OTHERS: This is a valid exception in Oracle PL/SQL. It is used to catch any exception that is not explicitly handled.

E. None of the above. These are all valid: This option is incorrect as option C is not a valid exception.

Therefore, the answer is: C.

  1. DISTINCT

  2. SUM

  3. MIN

  4. COUNT

  5. All of the above.

  6. None of these above.


Correct Option: A
Explanation:

To answer this question, the user needs to know about grouping functions in SQL.

Grouping functions are used to group rows of data together and perform calculations on them. Each of the options listed is a grouping function except for one.

A. DISTINCT: This is a grouping function that returns the unique values of a column.

B. SUM: This is a grouping function that returns the sum of the values in a column.

C. MIN: This is a grouping function that returns the minimum value of a column.

D. COUNT: This is a grouping function that returns the number of rows in a table or the number of non-null values in a column.

E. All of the above: This option is incorrect because all of the options listed are grouping functions except for one.

F. None of these above: This option is also incorrect because one of the options listed is not a grouping function.

The Answer is: A. DISTINCT

  1. DECODE

  2. TRUNCATE

  3. TO_CHAR

  4. NVL

  5. Trick question, all of these are Oracle DML functions.


Correct Option: B
Explanation:

To solve this question, the user needs to know the meaning of Oracle DML (Data Manipulation Language) functions and should be able to identify which of the given options is not an Oracle DML function.

Oracle DML functions are used to manipulate data stored within the database. They are used to insert, update, select, and delete data from tables. These functions are used to modify the data values present in the database.

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

A. DECODE: This is an Oracle DML function. It is used to compare expressions and return a value when the first matching condition is found.

B. TRUNCATE: This is not an Oracle DML function. It is a DDL (Data Definition Language) command used to remove all data from a table, but not its structure.

C. TO_CHAR: This is an Oracle DML function. It is used to convert a number or date to a string.

D. NVL: This is an Oracle DML function. It is used to replace NULL values with another value.

E. Trick question, all of these are Oracle DML functions: This statement is incorrect. Option B (TRUNCATE) is not an Oracle DML function.

Therefore, the answer is: B. TRUNCATE

  1. Incantination

  2. Integration

  3. Continuation

  4. Concatenation

  5. Pipeline

  6. None of the above


Correct Option: D
Explanation:

To solve this question, the user needs to know the SQL syntax and the concept of concatenation.

The given SQL statement is using the || operator to join or concatenate multiple columns together.

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

A. Incantination: This option is incorrect as "Incantation" is not a valid SQL function or concept.

B. Integration: This option is incorrect as "Integration" is not a valid SQL function or concept.

C. Continuation: This option is incorrect as "Continuation" is not a valid SQL function or concept.

D. Concatenation: This option is correct. The || operator is used to concatenate or combine two or more strings in SQL, resulting in a single string that contains all the characters from the original strings.

E. Pipeline: This option is incorrect as "Pipeline" is not a valid SQL function or concept.

F. None of the above: This option is incorrect as option D, "Concatenation", is the correct answer.

Therefore, the answer is: D. Concatenation

  1. 09/09/2009 00:00:00

  2. 09/09/2009 09:09:09AM

  3. 09/09/2009

  4. 09/09/2009 09:09:09

  5. None of the above.


Correct Option: A
Explanation:

The given statement is:

SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS') FROM dual;

Let's break down the statement:

  1. TRUNC(SYSDATE): This truncates the current date and time to the nearest day, setting the time component to 00:00:00.

  2. TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS'): This converts the truncated date and time to a string format with the specified format mask.

Considering the given date and time is 09/09/2009 09:09:09, when we truncate this date and time, the time component will be set to 00:00:00, resulting in 09/09/2009 00:00:00.

Therefore, the statement will return "09/09/2009 00:00:00".

The correct answer is A) 09/09/2009 00:00:00.

  1. %NOTFOUND

  2. %FOUND

  3. %ROWCOUNT

  4. %ISOPEN

  5. None of the above. All of these are valid.


Correct Option: E
Explanation:

To answer this question, the user needs to have knowledge of explicit cursors in SQL.

The explicit cursor is a SELECT statement that gets declared in the declaration section of PL/SQL block, and it is used to retrieve more than one row from the table.

Now, let's examine each option to see which attribute is invalid for an explicit cursor:

A. %NOTFOUND: This attribute is used to check whether the cursor returns no rows. It is a valid attribute of an explicit cursor.

B. %FOUND: This attribute is used to check whether the cursor returns rows. It is a valid attribute of an explicit cursor.

C. %ROWCOUNT: This attribute is used to return the number of rows affected by the most recent DML statement. It is a valid attribute of an explicit cursor.

D. %ISOPEN: This attribute is used to check whether the cursor is open or not. It is a valid attribute of an explicit cursor.

E. None of the above. All of these are valid: This option is incorrect because it is asking for an invalid attribute. All of the attributes listed in the question (A, B, C, and D) are valid attributes of an explicit cursor.

Therefore, the correct answer is:

The Answer is: E

  1. In the PL/SQL working storage section

  2. In the PL/SQL declaration section

  3. In the PL/SQL body section

  4. In the PL/SQL exception section

  5. None of the above


Correct Option: B
Explanation:

To declare an explicit cursor in the PL/SQL language, the user needs to know how to write PL/SQL code and the concept of cursors.

An explicit cursor is a cursor that is defined and managed by the user. It requires the user to declare a cursor variable that references the cursor and then define the SQL statement that will be executed when the cursor is opened.

With that in mind, the correct answer is:

B. In the PL/SQL declaration section

The declaration section of a PL/SQL block is where variables, constants, and cursors are declared. In this section, the user can declare the explicit cursor variable by specifying its name, data type, and the query to be executed. Once the cursor variable is declared, the user can then open, fetch, and close the cursor in the body section of the block.

Option A is incorrect because the PL/SQL working storage section does not exist.

Option C is incorrect because the PL/SQL body section is where the logic of the block is implemented, not where the cursor is declared.

Option D is incorrect because the PL/SQL exception section is where exception handling code is written.

Option E is incorrect because one of the above options (Option B) is the correct answer.

Therefore, the answer is: B. In the PL/SQL declaration section.

Select the best answer. This is an example of what _____ type of cursor? DECLARE l_date DATE; CURSOR c1 IS SELECT TRUNC(SYSDATE) FROM DUAL; BEGIN OPEN c1; FETCH c1 INTO l_date; CLOSE c1; END;

  1. Explicit

  2. Implicit

  3. Select

  4. PL/SQL

  5. None of the above.


Correct Option: A

Select the best answer to complete this variable declaration for a column value. DECLARE l_foo_column_id SOME_TABLE.SOME_COLUMN_________; BEGIN ...

  1. %ID

  2. %ROWTYPE

  3. %TYPE

  4. %COLUMNTYPE

  5. None of the above.


Correct Option: C

AI Explanation

To answer this question, we need to understand the different options and their meanings:

Option A) %ID - This option is incorrect because %ID is not a valid option for completing a variable declaration for a column value.

Option B) %ROWTYPE - This option is incorrect because %ROWTYPE is used to declare a record variable that can hold an entire row of a table or view. It is not used to declare a specific column value.

Option C) %TYPE - This option is correct because %TYPE is used to declare a variable with the same datatype as a specified column. In this case, it would declare the variable "l_foo_column_id" with the same datatype as the column "SOME_COLUMN" in the table "SOME_TABLE".

Option D) %COLUMNTYPE - This option is incorrect because %COLUMNTYPE is not a valid option for completing a variable declaration for a column value.

Therefore, the correct answer is C) %TYPE. This option is correct because it allows us to declare the variable with the same datatype as the specified column.

Select the best answer PACKAGE foo_foo IS PROCEDURE foo ( p_foo_text IN VARCHAR2 ); PROCEDURE foo (p_foo_number IN NUMBER); END;

  1. Package specification is invalid. Too many procedures named foo.

  2. Package specification is invalid. First procedure should be called called foo_1, second procedure should be called foo_2.

  3. Package specification is valid. This is an example of overloading.

  4. Package specification is invalid. We can only have one procedure named foo in the package

  5. Package specification is valid. We can have an unlimited number of procedures name foo.

  6. None of the above


Correct Option: C

List the correct sequence of commands to process a set of records when using explicit cursors

  1. INITIALIZE, GET, CLOSE

  2. OPEN, FETCH, CLOSE

  3. CURSOR, GET, FETCH, CLOSE

  4. CURSOR, FETCH, CLOSE

  5. GET, SEEK, HIDE

  6. None of the above.


Correct Option: B
Explanation:

To process a set of records using explicit cursors, the correct sequence of commands is:

B. OPEN, FETCH, CLOSE

Explanation:

  • The first step is to declare a cursor, which is typically done outside of the sequence of commands listed in the question. The cursor declaration specifies the SELECT statement that defines the result set to be processed.
  • The next step is to open the cursor using the OPEN command. This prepares the result set for processing.
  • The FETCH command is then used to retrieve each record in the result set one at a time. This command is usually used in a loop, where processing is performed on each record as it is retrieved.
  • Finally, the CLOSE command is used to release the resources associated with the cursor.

Therefore, the correct answer is:

The Answer is: B. OPEN, FETCH, CLOSE

- Hide questions