0

databases Online Quiz - 186

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

A subquery can be used to _________.

  1. Create groups of data

  2. Sort data in a specific order

  3. Convert data to a different format

  4. Retrieve data based on an unknown condition


Correct Option: D

AI Explanation

To answer this question, you need to understand the concept of subqueries in SQL.

A subquery is a query that is nested inside another query. It can be used to retrieve data based on an unknown condition, which makes option D the correct answer.

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

Option A) Create groups of data - This option is incorrect because creating groups of data is typically done using the GROUP BY clause, not a subquery.

Option B) Sort data in a specific order - This option is incorrect because sorting data is typically done using the ORDER BY clause, not a subquery.

Option C) Convert data to a different format - This option is incorrect because converting data to a different format is typically done using functions like CAST or CONVERT, not a subquery.

Option D) Retrieve data based on an unknown condition - This option is correct because a subquery can be used to retrieve data based on an unknown condition. The subquery can be used within the WHERE clause to filter the data based on a condition that is dynamically determined.

Therefore, the correct answer is D.

  1. query will retrieve only one row

  2. authorization failure

  3. no such code available

  4. rollback


Correct Option: B
Explanation:

To answer this question, we need to know the context in which the SQL code -922 is being used. SQL codes are specific error or informational messages generated by the database management system (DBMS) when it encounters certain conditions or situations. Without context, it is difficult to determine the exact meaning of a particular SQL code.

However, in general, SQL code -922 is associated with authorization failures. This means that the user attempting to execute a particular SQL statement does not have the required permissions or privileges to perform the operation. The exact meaning and resolution of the error will depend on the specific DBMS being used and the context in which the error occurred.

Therefore, the correct answer is:

The Answer is: B. authorization failure.

choose the environments which can access DB2

  1. TSO

  2. IMC

  3. CICS

  4. None of the above


Correct Option: A,C

5 rows are inserted to a DB2 Table. Next 3 of those are updated, then a rollback is issued. What would happen when the rollback is issued?

  1. returns 5 rows

  2. returns 3 rows

  3. returns all 8rows

  4. none


Correct Option: B

Which are true regarding the use of outer joins?

  1. You cannot use IN operator in a condition that involves an outerjoin

  2. You use (+) on both sides of the WHERE condition to perform an outerjoin

  3. You use (*) on both sides of the WHERE condition to perform an outerjoin

  4. You use an outerjoin to see only the rows that do not meet the join condition

  5. In the WHERE condition, you use (+) following the name of the column in the table

  6. You cannot link a condition that is involved in an outerjoin to another condition by


Correct Option: D,E,F

AI Explanation

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

Option A) You cannot use IN operator in a condition that involves an outer join - This option is incorrect. You can use the IN operator in a condition that involves an outer join. The use of the IN operator is not restricted in the context of an outer join.

Option B) You use (+) on both sides of the WHERE condition to perform an outer join - This option is incorrect. The use of (+) on both sides of the WHERE condition is not the correct syntax to perform an outer join. The correct syntax for an outer join varies depending on the database management system being used.

Option C) You use () on both sides of the WHERE condition to perform an outer join - This option is incorrect. The use of () on both sides of the WHERE condition is not the correct syntax to perform an outer join. The correct syntax for an outer join varies depending on the database management system being used.

Option D) You use an outer join to see only the rows that do not meet the join condition - This option is correct. An outer join is used to include all rows from one table and only the matching rows from the other table(s). When there is no match, the result will contain NULL values for the columns from the table with no matching rows.

Option E) In the WHERE condition, you use (+) following the name of the column in the table - This option is correct. The use of (+) following the name of the column in the WHERE condition is the syntax used to perform an outer join in Oracle SQL. It indicates that the join is an outer join and not an inner join.

Option F) You cannot link a condition that is involved in an outer join to another condition by - This option is correct. In some cases, when using outer joins, you cannot link a condition involved in the outer join to another condition using the AND or OR operators. This restriction depends on the specific database management system and its implementation of outer joins.

The correct answer is D, E, F. These options are correct because an outer join is used to see only the rows that do not meet the join condition, (+) is used in the WHERE condition to indicate an outer join in Oracle SQL, and there can be restrictions on linking conditions involved in an outer join to other conditions.

Which two statements complete a transaction? (Choose two)

  1. DELETE employees;

  2. DESCRIBE employees;

  3. ROLLBACK TO SAVEPOINT C;

  4. GRANT SELECT ON employees TO SCOTT;

  5. ALTER TABLE employees SET UNUSED COLUMN sal;

  6. Select MAX(sal) FROM employees WHERE department_id = 20;


Correct Option: C,E

AI Explanation

To answer this question, we need to understand what a transaction is and what actions can be considered as completing a transaction.

A transaction is a sequence of database operations that are treated as a single unit of work. These operations can include inserting, updating, or deleting data in the database.

Let's go through each option to understand which statements complete a transaction:

Option A) DELETE employees; - This statement deletes all records from the "employees" table. While it is a valid operation, it does not necessarily complete a transaction as it does not include other necessary operations such as committing or rolling back changes.

Option B) DESCRIBE employees; - This statement describes the structure of the "employees" table. It does not involve any transaction-related operations and does not complete a transaction.

Option C) ROLLBACK TO SAVEPOINT C; - This statement rolls back all changes made since the savepoint named "C." Rolling back changes is an essential part of completing a transaction, as it ensures that any changes made are undone if necessary. Therefore, this statement completes a transaction.

Option D) GRANT SELECT ON employees TO SCOTT; - This statement grants the SELECT privilege on the "employees" table to the user "SCOTT." While it is a valid operation, it does not involve any transaction-related operations and does not complete a transaction.

Option E) ALTER TABLE employees SET UNUSED COLUMN sal; - This statement marks the "sal" column in the "employees" table as unused. It is a valid operation, but it does not involve any transaction-related operations and does not complete a transaction.

Option F) Select MAX(sal) FROM employees WHERE department_id = 20; - This statement retrieves the maximum salary from the "employees" table for the employees in the department with ID 20. It is a valid query, but it does not involve any transaction-related operations and does not complete a transaction.

Based on the above explanations, the correct answers are:

C. ROLLBACK TO SAVEPOINT C; E. ALTER TABLE employees SET UNUSED COLUMN sal;

These two statements complete a transaction as they involve rollbacks and modifications to the database structure.

  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

Which of the following is not a valid aggregate function

  1. COUNT

  2. SUM

  3. COMPUTE

  4. MIN


Correct Option: C
Explanation:

To solve this question, the user needs to have knowledge about aggregate functions in SQL.

Aggregate functions are used to perform calculations on a set of values to return a single value.

Now let's go through each option and explain why it is right or wrong:

A. COUNT: This is a valid aggregate function in SQL. It returns the number of rows that match a specified condition.

B. SUM: This is a valid aggregate function in SQL. It returns the sum of a set of values.

C. COMPUTE: This is not a valid aggregate function in SQL. There is no such function as COMPUTE in SQL.

D. MIN: This is a valid aggregate function in SQL. It returns the minimum value of a set of values.

Therefore, the answer is:

The Answer is: C

To use a FETCH staement with a cursor you always have to supply an explicit direction such next or prior.

  1. TRUE

  2. FALSE

  3. BOTH

  4. NONE


Correct Option: B

PL/SQL does not support boolean variables

  1. True

  2. False


Correct Option: B
  1. CASCADE

  2. UNIQUE

  3. NONUNIQUE

  4. CHECK

  5. PRIMARY KEY

  6. NOT NULL


Correct Option: B,D,E,F

Which is an iSQL*PLUS command?

  1. INSERT

  2. UPDATE

  3. SELECT

  4. DESCRIBE

  5. DELETE

  6. RENAME


Correct Option: D

Top N analysis requires _____ and ______?(Choose two)

  1. the use of rowed

  2. a GROUP BY clause

  3. an ORDER BY clause

  4. only an inline view

  5. an inline view and an outer query


Correct Option: C,E

Which constraint can be defined only at the column level?

  1. UNIQUE

  2. NOT NULL

  3. CHECK

  4. PRIMARY KEY

  5. FOREIGN KEY


Correct Option: B

What does the FORCE option for creating a view do?

  1. creates a view with constraints

  2. creates a view even if the underlying parent table has constraints

  3. creates a view in another schema even if you don't have privileges

  4. creates a view regardless of whether or not the base tables exist


Correct Option: D

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

  1. GRANT select ON dept TO ALL USERS;

  2. GRANT select ON dept TO ALL;

  3. GRANT QUERY ON dept TO ALL USERS;

  4. GRANT select ON dept TO PUBLIC;


Correct Option: D
Explanation:

To solve this question, the user needs to know the SQL syntax for granting privileges to users and the difference between "ALL USERS" and "PUBLIC".

Now, let's go through each option and explain why it is right or wrong:

A. GRANT select ON dept TO ALL USERS; This option is incorrect because the correct keyword to grant privileges to all users is "PUBLIC" instead of "ALL USERS". Also, "QUERY" is not a valid keyword, and the correct keyword to grant query privileges is "SELECT". Therefore, option A is incorrect.

B. GRANT select ON dept TO ALL; This option is incorrect because the correct keyword to grant query privileges is "SELECT" instead of "QUERY". Also, "ALL" is not a valid keyword to grant privileges to all users, and the correct keyword is "PUBLIC". Therefore, option B is incorrect.

C. GRANT QUERY ON dept TO ALL USERS; This option is incorrect because "QUERY" is not a valid keyword to grant privileges, and the correct keyword is "SELECT". Also, "ALL USERS" is not a valid keyword to grant privileges to all users, and the correct keyword is "PUBLIC". Therefore, option C is incorrect.

D. GRANT select ON dept TO PUBLIC; This option is correct. The correct keyword to grant select (query) privileges is "SELECT". The keyword "PUBLIC" is used to grant privileges to all users. Therefore, option D is the correct answer.

The Answer is: D

Which three are true?(Choose three)

  1. A MERGE statement is used to merge the data of one table with data from another

  2. A MERGE statement replaces the data of one table with that of another

  3. A MERGE statement can be used to insert new rows into a table

  4. A MERGE statement can be used to update existing rows in a table


Correct Option: A,B,C

What is true about Sequences?

  1. Once created, a sequence belongs to a specific schema

  2. Once created, a sequence is linked to a specific table

  3. Once created, a sequence is automatically available to all users

  4. Only the DBA can control which squence is used by certain a table

  5. Once created, a sequence is automatically in all INSERT and UPDATE statements


Correct Option: C

John has created a procedure named SALARY_CALC. Which SQL query allows him to view the text of the procedure?

  1. SELECT text FROM user_source WHERE name ='SALARY_CALC';

  2. SELECT * FROM user_source WHERE source_name ='salary_calc';

  3. SELECT * FROM user_objects WHERE object_name = 'SALARY_CALC';

  4. SELECT text FROM user_source WHERE name='SALARY_CALC' AND owner ='JOHN';


Correct Option: A
Explanation:

To solve this question, the user needs to know the structure of the system tables in Oracle database where all the objects are stored.

Option A is correct. This query will return the text of the procedure SALARY_CALC. The user_source view contains all the source code for the stored procedures, functions, and triggers in the current user's schema. By specifying the name of the procedure in the WHERE clause, the query filters the result set to only show the source code for the SALARY_CALC procedure.

Option B is incorrect. There is no column named "source_name" in the user_source view. Therefore, this query will return an error.

Option C is incorrect. The user_objects view contains information about all objects in the current user's schema, including tables, views, indexes, and procedures. However, the view does not contain the source code for the procedures. Therefore, this query will not return any rows.

Option D is incorrect. The condition "AND owner='JOHN'" is unnecessary because the user_source view only contains source code for the objects owned by the current user. Therefore, this query will return the same result as option A, but with an unnecessary condition.

The Answer is: A. SELECT text FROM user_source WHERE name ='SALARY_CALC';

- Hide questions