0

databases Online Quiz - 142

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

What is true about triggers I. instead of trigger can be used on tables and views 2. instead of trigger can fire at eah row and statement levels. 3. Compund trigger in 10g allows us to specify multiple timig events on single database objects. 4. we should not use the same object which fired the trigger inside the trigger. this will result in mutating trigger error.

  1. 2 and 4 are true

  2. 1 , 3 and 4 are true

  3. 1 and 3 are true

  4. Only 4 is true


Correct Option: D

For which constraints are indexes created when the constraint is added?

  1. Check

  2. Unique

  3. Primary key

  4. not null


Correct Option: B,C

What is the difference between Union and Union all statements

  1. Union all used for combining multiples queries using single union all

  2. There is no difference

  3. the union will eliminate duplicate records in result set but, union all will print the duplicate records

  4. Union all is used for select with multiple columns


Correct Option: C

what is true about Delete and truncate 1. Truncate can not be used in PL/SQL 2. Delete will remove the data and will not move watermark. 3. Both statements require commit 4. truncate can remove selected records

  1. 1 and 2 are correct

  2. All are true

  3. 1,2 and 3 are correct

  4. 2,3 and 4 are correct


Correct Option: A

What is the default value for the OPTIMIZER_MODE initialization parameter?

  1. FIRST_ROWS

  2. ALL_ROWS

  3. CHOOSE

  4. COST


Correct Option: C

Can we have multiple primary keys in a table?

  1. True

  2. False


Correct Option: B

Which are the aggregate functions?

  1. SUM

  2. AVG

  3. trim

  4. median


Correct Option: A,B,D

We have unique constraint on sal column on emp table. can the column accept multiple null values?

  1. True

  2. False


Correct Option: A

Can we have multiple check constraints on a single column of a table.

  1. True

  2. False


Correct Option: A

We can view the constraint details from following data dictionary views

  1. user_constraints

  2. user_tables

  3. user_cons_columns

  4. all_tab_cols


Correct Option: A,C

Comparision of Decode and case statement

  1. Both are same

  2. case can check for non equality operator but decode checks for equality operator

  3. decode can be used for comparing value of one variable/condition. but case can check for multiple conditions

  4. case can not be used in where clause. but decode can be used.


Correct Option: B,C

Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; Which values are displayed?

  1. 46 and 45

  2. 46 and 45.93

  3. 50 and 45.93

  4. 50 and 45.9


Correct Option: C

Which are DML statements? (Choose all that apply.)

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP


Correct Option: B,C,D

AI Explanation

To answer this question, we need to understand what DML (Data Manipulation Language) statements are. DML statements are used to manipulate data stored in a database. They include operations such as inserting, updating, deleting, and merging data.

Let's go through each option to determine which ones are DML statements:

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

Option B) MERGE - This option is a DML statement. The MERGE statement combines insert, update, and delete operations into one statement. It is used to perform conditional updates or inserts based on a specified condition.

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

Option D) DELETE - This option is a DML statement. The DELETE statement is used to remove existing data from a table.

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

Option F) DROP - This option is not a DML statement. DROP is a DDL statement used to remove database objects such as tables or views.

Based on the explanations above, the correct answers are B) MERGE, C) UPDATE, and D) DELETE. These options are DML statements as they involve manipulating data in a database.

On which line will the following command fail? INSERT INTO plsql101_product ( product_name, product_price, quantity_on_hand, last_stock_date ) VALUES ( 'New Product', 1.95, 10, TO_CHAR(USER) ) ;

  1. 1

  2. 2

  3. 7

  4. 10


Correct Option: D

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set? A. The DESCRIBE DEPT statement displays the structure of the DEPT table. B. The ROLLBACK statement frees the storage space occupied by the DEPT table. C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist. D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

  1. A

  2. B

  3. C

  4. D


Correct Option: A

Among these Which are the system variables

  1. SYSDATE

  2. USER

  3. USERENV

  4. ALL THE ABOVE


Correct Option: D

What would be the result of the following DECODE function? DECODE(‘B’, ‘A’, ‘One’, ‘E’, ‘Five’, ‘I’, ‘Nine’, ‘O’, ‘Fifteen’, ‘U’, ‘Twenty-one’, ‘N/A’ )

  1. One

  2. N/A

  3. Two

  4. Five


Correct Option: B

AI Explanation

To answer this question, we need to understand how the DECODE function works.

The DECODE function in SQL is used to perform conditional logic within a query. It takes multiple pairs of values and returns a result based on the comparison of an expression with each pair.

In the given DECODE function:

DECODE('B', 'A', 'One', 'E', 'Five', 'I', 'Nine', 'O', 'Fifteen', 'U', 'Twenty-one', 'N/A')

The expression being compared is 'B'.

The DECODE function compares the expression 'B' with each pair of values. If the expression 'B' matches the first value of a pair, then the function returns the second value of that pair.

Let's go through each pair:

  1. ('A', 'One'): The expression 'B' does not match 'A', so this pair is not considered.
  2. ('E', 'Five'): The expression 'B' does not match 'E', so this pair is not considered.
  3. ('I', 'Nine'): The expression 'B' does not match 'I', so this pair is not considered.
  4. ('O', 'Fifteen'): The expression 'B' does not match 'O', so this pair is not considered.
  5. ('U', 'Twenty-one'): The expression 'B' does not match 'U', so this pair is not considered.
  6. ('N/A'): This is the default pair, which is considered when no other pairs match the expression. Since the expression 'B' does not match any of the previous pairs, the function returns 'N/A'.

Therefore, the result of the given DECODE function is 'N/A', which corresponds to Option B.

Evaluate this SQL statement: SELECT ename, sal, 12*sal+100 FROM emp; The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"? Evaluate this SQL statement: SELECT ename, sal, 12*sal+100 FROM emp; The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"? A. No change is required to achieve the desired results. B. SELECT ename, sal, 12*(sal+100) FROM emp; C. SELECT ename, sal, (12*sal)+100 FROM emp; D. SELECT ename, sal+100,*12 FROM emp;

  1. A

  2. B

  3. C

  4. D


Correct Option: B

Which of the following is NOT a benefit of indexes?

  1. Faster operation during INSERT commands

  2. Faster operation during UPDATE commands

  3. Faster operation during SELECT commands

  4. Faster operation during DELETE commands


Correct Option: D

Which of the following commands will rename a table?

  1. RENAME table_name new_table_name;

  2. RENAME table_name TO new_table_name;

  3. RENAME TABLE table_name new_table_name;

  4. RENAME TABLE table_name TO new_table_name;


Correct Option: B
- Hide questions