Tag: databases

Questions Related to databases

The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
technology databases

  1. SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;

  2. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;

  3. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;

  4. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;

Show answer
Correct Option: D
Explanation:

Explanation: To display the name and annual salary multiplied by the commission_pct for all employees, we need to use the multiplication (*) operator. For records that have a NULL commission_pct, a zero must be displayed against the calculated column, we need to use the NVL function.

Option A) SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES; This option is incorrect because it does not account for NULL commission_pct values and does not use the NVL function.

Option B) SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES; This option is incorrect because the IFNULL function is not valid in Oracle SQL, and it does not use the NVL function.

Option C) SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES; This option is incorrect because the NVL2 function is not valid in Oracle SQL. It should be replaced with the NVL function.

Option D) SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES; This option is correct because it uses the multiplication operator to calculate the annual salary multiplied by the commission_pct and the NVL function to handle NULL commission_pct values.

Therefore, the correct answer is D.

Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
technology databases

  1. USER_TAB_PRIVS_MADE

  2. USER_TAB_PRIVS

  3. USER_COL_PRIVS_MADE

  4. USER_COL_PRIVS

Show answer
Correct Option: D

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARMENT_ID = NULL; What is true about this SQL statement?

technology databases

  1. The SQL statement displays the desired results.

  2. The column in the WHERE clause should be changed to display the desired results.

  3. The operator in the WHERE clause should be changed to display the desired results.

  4. The WHERE clause should be changed to use an outer join to display the desired results.

Show answer
Correct Option: C

AI Explanation

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

Option A) The SQL statement displays the desired results. This option is incorrect because the SQL statement will not display the desired results.

Option B) The column in the WHERE clause should be changed to display the desired results. This option is incorrect because the column in the WHERE clause is correct. The column 'DEPARTMENT_ID' is being checked for NULL values, which is the correct approach.

Option C) The operator in the WHERE clause should be changed to display the desired results. This option is correct because the operator used in the WHERE clause is incorrect. To check for NULL values, you should use the 'IS NULL' operator instead of the '=' operator. So the correct SQL statement should be: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID IS NULL;

Option D) The WHERE clause should be changed to use an outer join to display the desired results. This option is incorrect because using an outer join is not necessary to display the desired results. The problem lies in the operator used in the WHERE clause, not the join type.

The correct answer is Option C. The operator in the WHERE clause should be changed to use 'IS NULL' instead of '=' to display the desired results.

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), 1oc VARCNAR2 (13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

technology databases

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

  2. The ROLLBACK statement frees the storage space occupies by the DEPT table.

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

  4. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement

Show answer
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) The DESCRIBE DEPT statement displays the structure of the DEPT table. This option is correct. The DESCRIBE statement in SQL is used to display the structure of a table. In this case, the DESCRIBE DEPT statement will display the structure of the DEPT table.

Option B) The ROLLBACK statement frees the storage space occupied by the DEPT table. This option is incorrect. The ROLLBACK statement in SQL is used to undo the changes made in the current transaction. It does not directly free the storage space occupied by a table.

Option C) The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist. This option is incorrect. Since the CREATE TABLE dept statement is executed before the DESCRIBE DEPT statement, the DEPT table does exist. Therefore, the DESCRIBE DEPT statement will not return an error.

Option D) The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement. This option is incorrect. The DESCRIBE statement can be used to display the structure of a table regardless of whether a COMMIT statement has been executed or not.

The correct answer is A. The DESCRIBE DEPT statement displays the structure of the DEPT table.

What various validations do you perform on the data after extraction?

technology databases

  1. NULL check

  2. MetaData Check

  3. Duplicate Check

  4. Invalid Check

Show answer
Correct Option: A,B,C,D

Array Size is mainly used to increase the buffer during Write operations into a Oracle DB in a Server job using the Oracle OCI stage.

technology databases

  1. True

  2. False

Show answer
Correct Option: A

Will the performance improve by using dataset in parallel jobs?

technology databases

  1. True

  2. False

Show answer
Correct Option: A

What is the default cache size?

technology databases

  1. 512MB

  2. 128MB

  3. 256MB

  4. 64MB

Show answer
Correct Option: B

Types of vies in Datastage Director?
technology databases

  1. Job View

  2. Run view

  3. Log View

  4. Status View

Show answer
Correct Option: A,C,D

A subject based Addressing is used as :
technology databases

  1. Identifies Data producer to Data Consumer

  2. Identifies Data Consumer to Producer .

  3. Communication between data producer and Data consumers

  4. None

Show answer
Correct Option: C