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

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

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

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