### databases Online Quiz - 99

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

Select invalid variable types

1. VARCHAR2

2. VARCHAR1

3. CHAR

4. CHAR2

5. INTEGER

6. NUMBER

Correct Option: B,D

SELECT ROUND (-123.39953,-2) FROM DUAL;

1. -123.40

2. -100

3. -120

4. -123.30

Correct Option: B

SELECT ROUND(-123.39953,-3) FROM DUAL;

1. 0

2. 100

3. 123

4. -123

Correct Option: A

#### AI Explanation

To answer this question, we need to understand the ROUND function in SQL.

The ROUND function is used to round a number to a specified number of decimal places or to the nearest whole number. It takes two arguments: the number to be rounded and the number of decimal places to round to.

In this case, the number to be rounded is -123.39953 and the number of decimal places to round to is -3.

When rounding to -3 decimal places, we are rounding to the nearest thousands place.

The correct answer is A) 0.

This is because -123.39953, when rounded to the nearest thousands place, becomes 0.

SELECT CONCAT(SUBSTR('MINDWORKS',-5,6),SUBSTR('KNOWMAX',-3,4)) FROM DUAL;

1. KNOWMIND

2. WORKSM

3. WORKSMAX

4. MAXWORKS

Correct Option: C

#### AI Explanation

To answer this question, let's break down the query step by step:

The given query is:

``````SELECT CONCAT(SUBSTR('MINDWORKS',-5,6),SUBSTR('KNOWMAX',-3,4)) FROM DUAL;
``````
1. `SUBSTR('MINDWORKS',-5,6)`:

• This function extracts a substring from the string 'MINDWORKS'.
• The starting point (-5) specifies that the substring should start from the 5th character from the end of the string.
• The length (6) specifies that the substring should have a length of 6 characters.
• Therefore, this expression returns 'WORKS'.
2. `SUBSTR('KNOWMAX',-3,4)`:

• This function extracts a substring from the string 'KNOWMAX'.
• The starting point (-3) specifies that the substring should start from the 3rd character from the end of the string.
• The length (4) specifies that the substring should have a length of 4 characters.
• Therefore, this expression returns 'MAX'.
3. `CONCAT(SUBSTR('MINDWORKS',-5,6),SUBSTR('KNOWMAX',-3,4))`:

• This function concatenates the two substrings obtained from the previous steps.
• The result of the concatenation is 'WORKSMAX'.

Therefore, the correct answer is option C) WORKSMAX.

SELECT DECODE('MINDWORKS','WORKS','MINDWORK','KNOWMAX') FROM DUAL;

1. KNOWMAX

2. MINDWORKS

3. WORKS

4. MINDWORK

Correct Option: A

1. 1

2. 2

3. 3

4. 0

5. 4

Correct Option: C

#### AI Explanation

To determine the number of columns presented after executing the given query, let's break down the query and analyze it step by step:

Therefore, the correct answer is C) 3.

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

1. Primary key access

2. Access via unique index

3. Table access by ROWID

4. Full table scan

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) Primary key access - This option involves accessing a table using the primary key. While primary key access can be efficient for retrieving a single row, it is not always the fastest method as it depends on the table's structure and data distribution.

Option B) Access via unique index - This option involves accessing a table using a unique index. Accessing a table via a unique index can be efficient for retrieving a single row. However, it may not always be the fastest method compared to other options.

Option C) Table access by ROWID - This option involves accessing a table directly using the ROWID. The ROWID is a unique identifier for each row in a table and allows for direct access to the desired row. This method is typically the fastest way to retrieve a single row as it bypasses index lookups.

Option D) Full table scan - This option involves scanning the entire table to find the desired row. This method is generally slower compared to other options, especially when retrieving a single row.

The correct answer is C) Table access by ROWID. This option is correct because it allows for direct access to the desired row, making it the fastest way for Oracle to retrieve a single row.

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

1. ALTER TABLE students ADD PRIMARY KEY student_id;

2. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

3. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

4. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

5. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Correct Option: D

#### AI Explanation

To add a primary key on the STUDENT_ID column in the STUDENTS table, you can use the following statement:

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Explanation for each option:

A. ALTER TABLE students ADD PRIMARY KEY student_id; This option is incorrect because it is missing the CONSTRAINT keyword before PRIMARY KEY.

B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id); This option is incorrect because it is missing a name for the constraint. A constraint must have a unique name.

C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id; This option is incorrect because it is missing parentheses around the column name student_id.

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id); This option is correct because it uses the correct syntax to add a constraint with a name (stud_id_pk) on the column student_id as the primary key.

E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id); This option is incorrect because MODIFY CONSTRAINT is used to modify an existing constraint, not to add a new constraint.

The correct answer is D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id). This option is correct because it adds a primary key constraint with the name stud_id_pk on the column student_id in the STUDENTS table.

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

1. TIMESTAMP

2. INTERVAL MONTH TO DAY

3. INTERVAL DAY TO SECOND

4. INTERVAL YEAR TO MONTH

5. TIMESTAMP WITH DATABASE TIMEZONE

Correct Option: A,C,D

#### AI Explanation

To answer this question, we need to understand the different DATETIME data types that can be used when specifying column definitions. Let's go through each option to understand which ones are correct:

Option A) TIMESTAMP - This option is correct. TIMESTAMP is a DATETIME data type that represents a date and time value.

Option B) INTERVAL MONTH TO DAY - This option is incorrect. INTERVAL MONTH TO DAY is not a DATETIME data type. It is a data type used to represent an interval of time in terms of months and days.

Option C) INTERVAL DAY TO SECOND - This option is correct. INTERVAL DAY TO SECOND is a DATETIME data type used to represent an interval of time in terms of days, hours, minutes, and seconds.

Option D) INTERVAL YEAR TO MONTH - This option is correct. INTERVAL YEAR TO MONTH is a DATETIME data type used to represent an interval of time in terms of years and months.

Option E) TIMESTAMP WITH DATABASE TIMEZONE - This option is incorrect. TIMESTAMP WITH DATABASE TIMEZONE is not a DATETIME data type. It is a TIMESTAMP data type that includes the database time zone.

The correct answers are A) TIMESTAMP, C) INTERVAL DAY TO SECOND, and D) INTERVAL YEAR TO MONTH. These three options are correct because they are DATETIME data types that can be used when specifying column definitions.

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?

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.

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?

2. USER_TAB_PRIVS

4. USER_COL_PRIVS

Correct Option: D

#### AI Explanation

To view the object privileges granted to the user on specific columns, you should query the USER_COL_PRIVS data dictionary table.

Let's go through each option to understand why it is correct or incorrect:

Option A) USER_TAB_PRIVS_MADE - This option is incorrect because the USER_TAB_PRIVS_MADE table provides information about the object privileges the user has granted to other users, not the privileges granted to the user on specific columns.

Option B) USER_TAB_PRIVS - This option is incorrect because the USER_TAB_PRIVS table provides information about the object privileges granted to the user on tables, not specific columns.

Option C) USER_COL_PRIVS_MADE - This option is incorrect because the USER_COL_PRIVS_MADE table provides information about the column privileges the user has granted to other users, not the privileges granted to the user on specific columns.

Option D) USER_COL_PRIVS - This option is correct because the USER_COL_PRIVS table provides information about the column privileges granted to the user, including the privileges granted on specific columns of tables.

The correct answer is D. This option is correct because querying the USER_COL_PRIVS table allows you to view the object privileges granted to the user on specific columns.

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.

Correct Option: C

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

1. 0

2. 1

3. 0.00

4. An error statement

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.)

1. NOT NULL

2. PRIMARY KEY

3. FOREIGN KEY

4. CHECK

5. UNIQUE KEY

Correct Option: B,E

#### AI Explanation

To answer this question, we need to understand the different types of constraints in a database and how they relate to the creation of unique indexes.

Option A) NOT NULL - The NOT NULL constraint ensures that a column cannot have a NULL value. However, it does not implicitly create a unique index. Therefore, this option is incorrect.

Option B) PRIMARY KEY - The PRIMARY KEY constraint uniquely identifies each record in a table. When a primary key constraint is defined on a column or a combination of columns, the Oracle Server implicitly creates a unique index on those columns. This is because a primary key must be unique and enforce entity integrity. Therefore, this option is correct.

Option C) FOREIGN KEY - The FOREIGN KEY constraint establishes a relationship between two tables, where the values in one table must match the values in another table's primary key or unique key. However, this constraint does not implicitly create a unique index. Therefore, this option is incorrect.

Option D) CHECK - The CHECK constraint is used to limit the values that can be placed in a column. It does not implicitly create a unique index. Therefore, this option is incorrect.

Option E) UNIQUE KEY - The UNIQUE KEY constraint ensures that all values in a column or a combination of columns are unique. When a unique key constraint is defined on a column or a combination of columns, the Oracle Server implicitly creates a unique index on those columns. This is because a unique key must enforce uniqueness. Therefore, this option is correct.

The correct answers are B) PRIMARY KEY and E) UNIQUE KEY. These constraints result in the Oracle Server implicitly creating a unique index to enforce uniqueness and entity integrity.

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

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.

Correct Option: C,D

#### AI Explanation

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

Option A) You can use aggregate functions in any clause of a SELECT statement. - This option is incorrect because aggregate functions can only be used in the SELECT clause and in the HAVING clause of a SELECT statement.

Option B) You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement. - This option is incorrect because aggregate functions can also be used in the HAVING clause of a SELECT statement.

Option C) You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. - This option is correct. When using aggregate functions, you can include both single row columns and aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

Option D) You can pass column names, expressions, constants, or functions as parameters to an aggregate function. - This option is correct. Aggregate functions can take column names, expressions, constants, or functions as parameters.

Option E) You can use aggregate functions on a table, only by grouping the whole table as one single group. - This option is incorrect. Aggregate functions can be used on a table by grouping the data based on specific columns, not necessarily the whole table as one group.

Option F) You cannot group the rows of a table by more than one column while using aggregate functions. - This option is incorrect. You can group the rows of a table by more than one column while using aggregate functions. This allows for more specific aggregation based on multiple criteria.

The correct answers are Option C and Option D.

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

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.

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?

1. MERGE

2. INSERT

3. UPDATE

5. ENTER

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

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?

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.

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.

Which two statements accurately describe a role? (Choose two.)

1. A role can be given to a maximum of 1000 users.

2. A user can have access to a maximum of 10 roles.

3. A role can have a maximum of 100 privileges contained in it.

4. Privileges are given to a role by using the CREATE ROLE statement.

5. A role is a named group of related privileges that can be granted to the user.

6. A user can have access to several roles, and several users can be assigned the same role.

Correct Option: D,F

#### AI Explanation

To answer this question, you need to understand the concept of roles and their characteristics. Let's go through each option to understand why it is correct or incorrect:

Option A) A role can be given to a maximum of 1000 users. - This statement is not accurate. A role can be assigned to any number of users, so there is no maximum limit.

Option B) A user can have access to a maximum of 10 roles. - This statement is not accurate. A user can have access to any number of roles, so there is no maximum limit.

Option C) A role can have a maximum of 100 privileges contained in it. - This statement is not accurate. A role can contain any number of privileges, so there is no maximum limit.

Option D) Privileges are given to a role by using the CREATE ROLE statement. - This statement is accurate. Privileges can be assigned to a role using the CREATE ROLE statement in SQL.

Option E) A role is a named group of related privileges that can be granted to the user. - This statement is partially accurate. A role is indeed a named group of related privileges, but it is granted to the user, not the other way around.

Option F) A user can have access to several roles, and several users can be assigned the same role. - This statement is accurate. A user can have access to multiple roles, and multiple users can be assigned the same role.

The correct answers are Option D and Option F. These statements accurately describe a role.

What is necessary for your query on an existing view to execute successfully?

1. The underlying tables must have data.

2. You need SELECT privileges on the view.

3. The underlying tables must be in the same schema.

4. You need SELECT privileges only on the underlying tables.

Correct Option: D

#### AI Explanation

To answer this question, you need to understand the concept of views in databases.

A view is a virtual table that is based on the result of a query. It does not store any data itself but instead retrieves data from the underlying tables whenever it is accessed.

Now let's go through each option to understand why it is correct or incorrect:

Option A) The underlying tables must have data - This option is incorrect because a view can still be executed successfully even if the underlying tables do not have data. The view will return an empty result set in such cases.

Option B) You need SELECT privileges on the view - This option is incorrect because having SELECT privileges on the view alone is not sufficient for the query to execute successfully. You also need SELECT privileges on the underlying tables from which the view is derived.

Option C) The underlying tables must be in the same schema - This option is incorrect because the underlying tables can be in different schemas and the view can still be executed successfully. The schema of the view itself is what matters.

Option D) You need SELECT privileges only on the underlying tables - This option is correct. In order to execute a query on an existing view successfully, you need to have SELECT privileges on the underlying tables from which the view is derived. This is because the view retrieves data from these tables when it is accessed.

The correct answer is D) You need SELECT privileges only on the underlying tables. This option is correct because having SELECT privileges on the underlying tables is necessary for the query on the view to execute successfully.

- Hide questions