Tag: databases

Questions Related to databases

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

  1. True

  2. False

Show answer
Correct Option: A

We can view the constraint details from following data dictionary views
technology databases

  1. user_constraints

  2. user_tables

  3. user_cons_columns

  4. all_tab_cols

Show answer
Correct Option: A,C

Comparision of Decode and case statement
technology databases

  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.

Show answer
Correct Option: B,C

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

  1. 46 and 45

  2. 46 and 45.93

  3. 50 and 45.93

  4. 50 and 45.9

Show answer
Correct Option: C

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

  1. COMMIT

  2. MERGE

  3. UPDATE

  4. DELETE

  5. CREATE

  6. DROP

Show answer
Correct Option: B,C,D

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) ) ;
technology databases

  1. 1

  2. 2

  3. 7

  4. 10

Show answer
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.
technology databases

  1. A

  2. B

  3. C

  4. D

Show answer
Correct Option: A
Explanation:

To understand the given set of SQL statements, the user must have knowledge of SQL commands and their functions.

  • CREATE TABLE statement creates a table with the specified columns and data types.

  • ROLLBACK statement rolls back the current transaction and undoes all the changes, freeing the storage space occupied by the table.

  • DESCRIBE statement displays the structure of the specified table.

Now, let's evaluate each option:

A. The DESCRIBE DEPT statement displays the structure of the DEPT table.

This option is correct. The DESCRIBE statement is used to display the structure of the specified table, and in this case, the specified table is DEPT. So, the DESCRIBE DEPT statement will display the structure of the DEPT table.

B. The ROLLBACK statement frees the storage space occupied by the DEPT table.

This option is incorrect. The ROLLBACK statement rolls back the current transaction and undoes all the changes, but it does not free the storage space occupied by the DEPT table. The table will still exist even after the ROLLBACK statement is executed.

C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

This option is incorrect. The DEPT table was created using the CREATE TABLE statement, so it exists in the database. The DESCRIBE DEPT statement will display the structure of the DEPT table.

D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

This option is incorrect. The DESCRIBE statement displays the structure of the specified table regardless of the presence of a COMMIT statement. The ROLLBACK statement undoes all the changes made in the current transaction, but it does not affect the execution of the DESCRIBE statement.

Therefore, the correct answer is:

The Answer is: A. A

Among these Which are the system variables
technology databases

  1. SYSDATE

  2. USER

  3. USERENV

  4. ALL THE ABOVE

Show answer
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’ )
technology databases

  1. One

  2. N/A

  3. Two

  4. Five

Show answer
Correct 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;
technology databases

  1. A

  2. B

  3. C

  4. D

Show answer
Correct Option: B
Explanation:

To calculate the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12, we need to modify the expression "12*sal+100" in the SELECT statement.

Option A: No change is required to achieve the desired results. This option is incorrect because the original expression "12*sal+100" calculates the total compensation as monthly salary multiplied by 12, plus a fixed monthly bonus of $100. It does not add the bonus to the monthly salary before multiplying by 12.

Option B: SELECT ename, sal, 12*(sal+100) FROM emp; This option is correct. By adding the monthly bonus of $100 to the monthly salary before multiplying by 12, we get the correct annual compensation. The expression "12*(sal+100)" calculates the annual compensation as monthly salary plus bonus, multiplied by 12.

Option C: SELECT ename, sal, (12*sal)+100 FROM emp; This option is incorrect because the parentheses around "12*sal" are unnecessary. The multiplication operator has higher precedence than the addition operator, so "12*sal" is evaluated before adding 100.

Option D: SELECT ename, sal+100,12 FROM emp; This option is incorrect because the syntax is incorrect. The asterisk () is not a valid operator in this context. We need to use the multiplication operator (*) to calculate the annual compensation.

Therefore, the correct answer is: B. SELECT ename, sal, 12*(sal+100) FROM emp;