databases Online Quiz - 186
Description: databases Online Quiz - 186 | |
Number of Questions: 20 | |
Created by: Aliensbrain Bot | |
Tags: databases |
A subquery can be used to _________.
-
Create groups of data
-
Sort data in a specific order
-
Convert data to a different format
-
Retrieve data based on an unknown condition
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.
what does the sql code -922 represents?
-
query will retrieve only one row
-
authorization failure
-
no such code available
-
rollback
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
-
TSO
-
IMC
-
CICS
-
None of the above
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?
-
returns 5 rows
-
returns 3 rows
-
returns all 8rows
-
none
Which are true regarding the use of outer joins?
-
You cannot use IN operator in a condition that involves an outerjoin
-
You use (+) on both sides of the WHERE condition to perform an outerjoin
-
You use (*) on both sides of the WHERE condition to perform an outerjoin
-
You use an outerjoin to see only the rows that do not meet the join condition
-
In the WHERE condition, you use (+) following the name of the column in the table
-
You cannot link a condition that is involved in an outerjoin to another condition by
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)
-
DELETE employees;
-
DESCRIBE employees;
-
ROLLBACK TO SAVEPOINT C;
-
GRANT SELECT ON employees TO SCOTT;
-
ALTER TABLE employees SET UNUSED COLUMN sal;
-
Select MAX(sal) FROM employees WHERE department_id = 20;
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.
Which two are true about aggregate functions? (Choose two.)
-
You can use aggregate functions in any clause of a SELECT statement.
-
You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
-
You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
-
You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
-
You can use aggregate functions on a table, only by grouping the whole table as one single group.
-
You cannot group the rows of a table by more than one column while using aggregate functions.
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. Aggregate functions can only be used in the SELECT clause, HAVING clause, or ORDER BY 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. As mentioned in the explanation for Option A, aggregate functions can also be used in the HAVING clause and ORDER BY clause.
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 in the SELECT clause, you can mix them with single row columns by grouping on the single row columns. This allows you to perform calculations on groups of rows while also displaying individual row data.
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. This allows you to perform calculations on specific columns or values within the aggregate function.
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 rows based on one or more columns. This allows you to calculate aggregate values for different groups of rows.
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 multiple columns while using aggregate functions. This allows you to calculate aggregate values for different combinations of grouped columns.
Therefore, the correct answers are Option C and Option D.
Which of the following is not a valid aggregate function
-
COUNT
-
SUM
-
COMPUTE
-
MIN
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.
-
TRUE
-
FALSE
-
BOTH
-
NONE
Which of the following is non-PL/SQL variable?
-
Scalar
-
LOB
-
Reference
-
host
PL/SQL does not support boolean variables
-
True
-
False
Which four are valid Oracle constraint types?
-
CASCADE
-
UNIQUE
-
NONUNIQUE
-
CHECK
-
PRIMARY KEY
-
NOT NULL
AI Explanation
To answer this question, you need to understand the different types of constraints in Oracle.
Let's go through each option to understand why it is correct or incorrect:
Option A) CASCADE - This option is incorrect because CASCADE is not a valid constraint type in Oracle. It is used in the context of referential integrity to specify that changes made to a primary key value are automatically propagated to the foreign key values.
Option B) UNIQUE - This option is correct. The UNIQUE constraint ensures that all values in a column or a set of columns are unique. It allows for null values but does not allow duplicate values.
Option C) NONUNIQUE - This option is incorrect because NONUNIQUE is not a valid constraint type in Oracle. It is not used to define a constraint.
Option D) CHECK - This option is correct. The CHECK constraint is used to specify a condition that must be true for each row in a table. It allows you to define custom validation rules for the data in a column.
Option E) PRIMARY KEY - This option is correct. The PRIMARY KEY constraint is used to uniquely identify each row in a table. It enforces the uniqueness of values in a column or set of columns and also ensures that the values are not null.
Option F) NOT NULL - This option is correct. The NOT NULL constraint is used to ensure that a column does not contain any null values. It enforces the requirement that the column must have a value for every row.
The correct answer is B, D, E, F. These options are correct because they represent valid constraint types in Oracle.
Which is an iSQL*PLUS command?
-
INSERT
-
UPDATE
-
SELECT
-
DESCRIBE
-
DELETE
-
RENAME
Top N analysis requires _____ and ______?(Choose two)
-
the use of rowed
-
a GROUP BY clause
-
an ORDER BY clause
-
only an inline view
-
an inline view and an outer query
Which constraint can be defined only at the column level?
-
UNIQUE
-
NOT NULL
-
CHECK
-
PRIMARY KEY
-
FOREIGN KEY
What does the FORCE option for creating a view do?
-
creates a view with constraints
-
creates a view even if the underlying parent table has constraints
-
creates a view in another schema even if you don't have privileges
-
creates a view regardless of whether or not the base tables exist
-
GRANT select ON dept TO ALL USERS;
-
GRANT select ON dept TO ALL;
-
GRANT QUERY ON dept TO ALL USERS;
-
GRANT select ON dept TO PUBLIC;
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
-
A MERGE statement is used to merge the data of one table with data from another
-
A MERGE statement replaces the data of one table with that of another
-
A MERGE statement can be used to insert new rows into a table
-
A MERGE statement can be used to update existing rows in a table
What is true about Sequences?
-
Once created, a sequence belongs to a specific schema
-
Once created, a sequence is linked to a specific table
-
Once created, a sequence is automatically available to all users
-
Only the DBA can control which squence is used by certain a table
-
Once created, a sequence is automatically in all INSERT and UPDATE statements
John has created a procedure named SALARY_CALC. Which SQL query allows him to view the text of the procedure?
-
SELECT text FROM user_source WHERE name ='SALARY_CALC';
-
SELECT * FROM user_source WHERE source_name ='salary_calc';
-
SELECT * FROM user_objects WHERE object_name = 'SALARY_CALC';
-
SELECT text FROM user_source WHERE name='SALARY_CALC' AND owner ='JOHN';
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';