Tag: databases

Questions Related to databases

Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)
technology databases

  1. TIMESTAMP

  2. INTERVAL MONTH TO DAY

  3. INTERVAL DAY TO SECOND

  4. INTERVAL YEAR TO MONTH

  5. TIMESTAMP WITH DATABASE TIMEZONE

Show answer
Correct Option: A,C,D

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc 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 introduced before the ROLLBACK 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 because the DESCRIBE statement is used to display the structure of a table, and in this case, it 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 is used to undo transactions and restore the database to the state it was in before the transaction was initiated. It does not free the storage space occupied by the 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 statement is executed before the DESCRIBE statement, the DEPT table will exist, and the DESCRIBE statement will be able to display its structure.

Option 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 does not depend on the presence of a COMMIT statement before the ROLLBACK statement. It will display the structure of the DEPT table regardless of whether a COMMIT statement was used or not.

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

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 DEPARTMENT_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

Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?
technology databases

  1. 0

  2. 1

  3. 0.00

  4. An error statement

Show answer
Correct Option: A
Explanation:

To evaluate the SQL statement, we need to understand the functions ROUND, TRUNC and MOD.

ROUND: rounds a number to a specified number of decimal places. If the second argument is negative, it rounds to the left of the decimal point. For example, ROUND(123.456, 2) = 123.46, ROUND(123.456, -1) = 120. TRUNC: truncates a number to a specified number of decimal places. If the second argument is negative, it truncates to the left of the decimal point. For example, TRUNC(123.456, 2) = 123.45, TRUNC(123.456, -1) = 120. MOD: returns the remainder of a division operation. For example, MOD(7, 3) = 1, MOD(1600, 10) = 0. Using these functions, we can evaluate the SQL statement step by step:

MOD(1600,10) returns 0, since there is no remainder when dividing 1600 by 10. TRUNC(0,-1) returns 0, since there is nothing to truncate to the left of the decimal point. ROUND(0,2) returns 0, since there is nothing to round to the right of the decimal point. Therefore, the final result displayed by the SQL statement is 0.

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)
technology databases

  1. NOT NULL

  2. PRIMARY KEY

  3. FOREIGN KEY

  4. CHECK

  5. UNIQUE KEY

Show answer
Correct Option: B,E

Which two are true about aggregate functions? (Choose two.)
technology databases

  1. You can use aggregate functions in any clause of a SELECT statement.

  2. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

  3. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

  4. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

  5. You can use aggregate functions on a table, only by grouping the whole table as one single group.

  6. You cannot group the rows of a table by more than one column while using aggregate functions.

Show answer
Correct Option: C,D

Which two statements about subqueries are true? (Choose two.)

technology databases

  1. A single row subquery can retrieve data from only one table.

  2. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.

  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.

  4. A single row subquery can retrieve data from more than one table.

  5. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.

  6. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

Show answer
Correct Option: B,D

AI Explanation

To answer this question, we need to understand the concept of subqueries.

A subquery is a query that is nested within another query. It is used to retrieve data from one or more tables and is enclosed within parentheses.

Let's go through each option to understand which are true about subqueries:

Option A) A single row subquery can retrieve data from only one table. This statement is false. A single row subquery can retrieve data from multiple tables as long as they are related through a common column.

Option B) A SQL query statement cannot display data from table B that is referred to in its subquery unless table B is included in the main query's FROM clause. This statement is true. When a subquery references a table, that table must be included in the main query's FROM clause for the data to be displayed.

Option C) A SQL query statement can display data from table B that is referred to in its subquery without including table B in its own FROM clause. This statement is false. To display data from table B that is referred to in a subquery, table B must be included in the main query's FROM clause.

Option D) A single row subquery can retrieve data from more than one table. This statement is true. A single row subquery can retrieve data from multiple tables if they are related through a common column.

Option E) A single row subquery cannot be used in a condition where the LIKE operator is used for comparison. This statement is false. A single row subquery can be used in a condition where the LIKE operator is used for comparison.

Option F) A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison. This statement is false. A multiple-row subquery can be used in a condition where the LIKE operator is used for comparison.

Based on the explanations above, the two true statements about subqueries are:

B. A SQL query statement cannot display data from table B that is referred to in its subquery unless table B is included in the main query's FROM clause. D. A single row subquery can retrieve data from more than one table.

Therefore, the correct answer is B and D.

You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

technology databases

  1. MERGE

  2. INSERT

  3. UPDATE

  4. ADD

  5. ENTER

  6. You cannot enter the phone numbers for the existing employee records.

Show answer
Correct Option: A

AI Explanation

To enter the phone numbers of each of the 100 employees into the existing EMPLOYEES table, the appropriate data manipulation operation to perform is the INSERT operation.

Option A) MERGE - The MERGE operation is used to update or insert data into a target table based on a condition. It is not necessary in this scenario since we are only inserting data.

Option B) INSERT - This option is correct. The INSERT operation is used to add new rows of data into a table. In this case, we are adding the phone numbers of each employee into the table.

Option C) UPDATE - The UPDATE operation is used to modify existing data in a table. It is not necessary in this scenario since we are not modifying existing data, but rather adding new data.

Option D) ADD - The ADD operation is used to add new columns to an existing table. It is not necessary in this scenario since we are not adding a new column, but rather adding data to an existing column.

Option E) ENTER - This option is not a valid data manipulation operation in SQL. It is not applicable in this scenario.

Option F) You cannot enter the phone numbers for the existing employee records - This option is incorrect. It is possible to enter the phone numbers for the existing employee records by performing the INSERT operation.

Therefore, the correct answer is B) INSERT.

In which case would you use a FULL OUTER JOIN?
technology databases

  1. Both tables have NULL values.

  2. You want all unmatched data from one table.

  3. You want all matched data from both tables.

  4. You want all unmatched data from both tables.

  5. One of the tables has more data than the other.

  6. You want all matched and unmatched data from only one table.

Show answer
Correct Option: D
Explanation:

To answer this question, the user must know the different types of joins in SQL.

  • INNER JOIN: returns only matched data from both tables.
  • LEFT OUTER JOIN: returns all data from the left table and matched data from the right table.
  • RIGHT OUTER JOIN: returns all data from the right table and matched data from the left table.
  • FULL OUTER JOIN: returns all matched and unmatched data from both tables.

Now, let's go through each option and determine whether it is a valid case to use a FULL OUTER JOIN:

A. Both tables have NULL values.

  • This option is not a determining factor in deciding to use a FULL OUTER JOIN.

B. You want all unmatched data from one table.

  • This option does not require a FULL OUTER JOIN, as a LEFT OUTER JOIN or RIGHT OUTER JOIN would suffice depending on which table has the unmatched data.

C. You want all matched data from both tables.

  • This option requires an INNER JOIN, not a FULL OUTER JOIN.

D. You want all unmatched data from both tables.

  • This option requires a FULL OUTER JOIN, as it returns all matched and unmatched data from both tables.

E. One of the tables has more data than the other.

  • This option does not necessarily require a FULL OUTER JOIN, as a LEFT OUTER JOIN or RIGHT OUTER JOIN could also be used depending on which table has more data.

F. You want all matched and unmatched data from only one table.

  • This option does not require a FULL OUTER JOIN, as a LEFT OUTER JOIN or RIGHT OUTER JOIN could be used depending on which table has the data.

Therefore, the correct answer is:

The Answer is: D