databases Online Quiz - 98
| Description: databases Online Quiz - 98 | |
| Number of Questions: 20 | |
| Created by: Aliensbrain Bot | |
| Tags: databases |
Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
-
The two statements produce identical results.
-
The second statement returns a syntax error.
-
There is no need to specify DESC because the results are sorted in descending order by default.
-
The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
-
SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;
-
SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') FROM dual;
-
SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;
-
SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
-
SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
AI Explanation
To display the system date in the format "Monday, 01 June, 2001", you can use the TO_CHAR function in Oracle SQL. Let's go through each option to understand why it is correct or incorrect:
Option A) SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual; This option is incorrect because the TO_DATE function is used to convert a string to a date. However, SYSDATE is already a date, so there is no need to convert it. Additionally, the format mask 'FMDAY' is not valid for the TO_DATE function.
Option B) SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') FROM dual; This option is incorrect because the format mask 'YYY' is missing a closing single quote. It should be 'YYYY'. Additionally, the format mask 'DY' is used to display the abbreviated name of the day, not the full name.
Option C) SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual; This option is correct because it uses the TO_CHAR function to convert the system date (SYSDATE) to a string in the desired format. The format mask 'FMDay' is used to display the full name of the day, 'DD' is used to display the day of the month, 'Month' is used to display the full name of the month, and 'YYYY' is used to display the four-digit year.
Option D) SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual; This option is incorrect because the format mask 'FMDY' is not valid. It should be 'DY' to display the abbreviated name of the day.
Option E) SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual; This option is incorrect because it uses the TO_DATE function to convert SYSDATE to a date, but SYSDATE is already a date. Additionally, the format mask 'FMDY' is not valid for the TO_DATE function.
The correct answer is option C. This option is correct because it uses the TO_CHAR function with the correct format mask to convert the system date to a string in the desired format.
Which SELECT statement will the result ‘ello world’ from the string ‘Hello World’?
-
SELECT SUBSTR( ‘Hello World’,1) FROM dual;
-
SELECT INITCAP(TRIM (‘Hello World’, 1,1)) FROM dual;
-
SELECT LOWER(SUBSTR(‘Hello World’, 1, 1) FROM dual;
-
SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;
-
SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;
-
The value displayed in the CALC_VALUE column will be lower.
-
The value displayed in the CALC_VALUE column will be higher.
-
There will be no difference in the value displayed in the CALC_VALUE column.
-
An error will be reported.
From SQL*Plus, you issue this SELECT statement: SELECT* From orders; You use this statement to retrieve data from a data table for __________. (Choose all that apply)
-
Updating
-
Viewing
-
Deleting
-
Inserting
-
Truncating
AI Explanation
To answer this question, you need to understand the purpose of the SELECT statement in SQL*Plus. Let's go through each option to understand why it is correct or incorrect:
Option A) Updating - The SELECT statement is not used for updating data in a table. It is used to retrieve data from a table, so this option is incorrect.
Option B) Viewing - The SELECT statement is used to retrieve and view data from a table. When you issue the SELECT statement "SELECT * FROM orders;", you are retrieving and viewing the data from the "orders" table. Therefore, this option is correct.
Option C) Deleting - The SELECT statement is not used for deleting data from a table. It is used to retrieve data, not delete it. So this option is incorrect.
Option D) Inserting - The SELECT statement is not used for inserting data into a table. It is used to retrieve data, not insert it. So this option is incorrect.
Option E) Truncating - The SELECT statement is not used for truncating a table. Truncating is a separate command in SQL that is used to remove all data from a table. So this option is incorrect.
The correct answers are B) Viewing and D) Inserting. The SELECT statement is used to retrieve and view data from a table, and it can also be used to insert data into another table using the INSERT INTO statement.
Which two are attributes of /SQL*Plus? (Choose two)
-
/SQL*Plus commands cannot be abbreviated.
-
/SQL*Plus commands are accesses from a browser.
-
/SQL*Plus commands are used to manipulate data in tables.
-
/SQL*Plus commands manipulate table definitions in the database.
-
/SQL*Plus is the Oracle proprietary interface for executing SQL statements.
AI Explanation
To answer this question, let's go through each option and determine if it is an attribute of SQL*Plus:
Option A) SQL*Plus commands cannot be abbreviated. - This option is incorrect. SQL*Plus commands can be abbreviated using shortcuts or aliases to make them easier to type and execute.
Option B) SQL*Plus commands are accessed from a browser. - This option is incorrect. SQL*Plus is a command-line interface and is not accessed through a browser.
Option C) SQL*Plus commands are used to manipulate data in tables. - This option is correct. SQL*Plus commands can be used to insert, update, delete, and select data from tables.
Option D) SQL*Plus commands manipulate table definitions in the database. - This option is correct. SQL*Plus commands can be used to create, alter, and drop tables, as well as define constraints and indexes.
Option E) SQL*Plus is the Oracle proprietary interface for executing SQL statements. - This option is incorrect. While SQL*Plus is a proprietary interface for Oracle, it is not limited to executing SQL statements only. It can also be used for formatting reports, running scripts, and performing administrative tasks.
Therefore, the correct attributes of SQL*Plus are options C and D.
-
The indexed column is declared as NOT NULL.
-
The indexed columns are used in the FROM clause.
-
The indexed columns are part of an expression.
-
The indexed column contains a wide range of values.
-
ORDER BY SALARY > 5000
-
GROUP BY SALARY > 5000
-
HAVING SALARY > 5000
-
WHERE SALARY > 5000
Which are correct guidelines for naming database tables?
-
Must begin with either a number or a letter.
-
Must be 1-30 characters long.
-
Should not be an Oracle Server reserved word.
-
Must contain only A-Z, a-z, 0-+, _, *, and #.
-
Must contain only A-Z, a-z, 0-9, _, $, and #.
-
Must begin with a letter.
Which operator can be used with a multiple-row subquery?
-
=
-
LIKE
-
BETWEEN
-
NOT IN
-
IS
-
<>
AI Explanation
To answer this question, you need to understand the use of operators in subqueries.
A subquery is a query nested within another query. It is used to retrieve data that will be used in the main query. When using a subquery that returns multiple rows, you need to use an appropriate operator to compare the subquery result with the main query.
Let's go through each option to understand which operator can be used with a multiple-row subquery:
Option A) = (equals) - This operator is typically used to compare a single value with another single value, not with a subquery that returns multiple rows. Therefore, this option is incorrect.
Option B) LIKE - The LIKE operator is used for pattern matching with string values. It can be used with a multiple-row subquery to compare the subquery result with a pattern. However, it is not the most appropriate operator for comparing the entire subquery result with the main query. Therefore, this option is incorrect.
Option C) BETWEEN - The BETWEEN operator is used to specify a range of values. It is typically used for comparing a single value with a range, not with a subquery that returns multiple rows. Therefore, this option is incorrect.
Option D) NOT IN - The NOT IN operator is used to exclude values that are present in a subquery result from the main query result. It can be used with a multiple-row subquery to compare the subquery result with the main query. Therefore, this option is correct.
Option E) IS - The IS operator is used for comparing a value with NULL. It is not typically used with a subquery that returns multiple rows. Therefore, this option is incorrect.
Option F) <> (not equal to) - This operator is typically used to compare a single value with another single value, not with a subquery that returns multiple rows. Therefore, this option is incorrect.
The correct answer is Option D) NOT IN. This option is correct because the NOT IN operator can be used with a multiple-row subquery to compare the subquery result with the main query.
-
SELECT last_name FROM EMP WHERE last_name LIKE ‘_A%’;
-
SELECT last_name FROM EMP WHERE last name =’*A%’
-
SELECT last_name FROM EMP WHERE last name =’_A%’;
-
SELECT last_name FROM EMP WHERE last name LIKE ‘*A%’
Which two are character manipulation functions? (Choose two.)
-
TRIM
-
REPLACE
-
TRUNC
-
TO_DATE
-
MOD
Consider the following SQL statement. SELECT qty FROM sales WHERE rate IN (300,800); Which of the following operators can be used to substitute the 'IN' operator?
-
ALL
-
BETWEEN...AND
-
LIKE
-
OR
Which of the following operators is used in a correlated subquery?
-
IN
-
EXISTS
-
UNION
-
INTERSECT
AI Explanation
To answer this question, you need to understand the concept of correlated subqueries.
A correlated subquery is a subquery that refers to a column from a table in the outer query. The subquery is executed once for each row of the outer query, and the results of the subquery are correlated to the current row of the outer query.
Among the given options, the operator used in a correlated subquery is the EXISTS operator.
Option A) IN - The IN operator is used to compare a value to a list of values or a subquery. It is not specifically used in a correlated subquery.
Option B) EXISTS - The EXISTS operator is used to check if a subquery returns any rows. It is commonly used in a correlated subquery to check for the existence of specific rows in the outer query.
Option C) UNION - The UNION operator is used to combine the result sets of two or more SELECT statements. It is not specifically used in a correlated subquery.
Option D) INTERSECT - The INTERSECT operator is used to return the common rows between two SELECT statements. It is not specifically used in a correlated subquery.
The correct answer is option B) EXISTS. This operator is used in a correlated subquery to check for the existence of specific rows in the outer query.
-
INSERT
-
PROMPT
-
ACCEPT
-
DEFINE
Which of the following events results in Automatic rollback?
-
Exiting from SQL*Plus
-
System crashing
-
Issue of GRANT or CREATE command
-
None of the above
AI Explanation
To answer this question, let's go through each option to understand why it is correct or incorrect:
Option A) Exiting from SQL*Plus - This option is incorrect because exiting from SQL*Plus does not result in an automatic rollback. Exiting SQL*Plus simply ends the session without performing any additional actions.
Option B) System crashing - This option is incorrect because a system crash does not necessarily result in an automatic rollback. A system crash can cause data inconsistencies and potential data loss, but whether or not a rollback occurs depends on the recovery mechanisms in place.
Option C) Issue of GRANT or CREATE command - This option is incorrect because issuing a GRANT or CREATE command does not automatically trigger a rollback. These commands modify the database schema or access permissions but do not inherently trigger a rollback.
Option D) None of the above - This option is correct because none of the listed events (exiting from SQL*Plus, system crashing, issuing GRANT or CREATE command) result in an automatic rollback. Automatic rollback typically occurs in the event of a transaction failure or when explicit rollback commands are issued.
The correct answer is D) None of the above.
Which of the following naming rules apply to tables? (Choose three)
-
A table name can duplicate the name of any other database object, if not owned by same user.
-
Table names can contain $,_, and # signs.
-
Table names are case-sensitive.
-
Table names begin with a letter.
-
Table names are not created in Uppercase by default.
AI Explanation
To answer this question, let's go through each option to understand why it is correct or incorrect:
Option A) A table name can duplicate the name of any other database object if not owned by the same user - This option is correct. In a database, a table name can be the same as the name of another database object (such as a view, function, or procedure) as long as they are not owned by the same user.
Option B) Table names can contain $, _, and # signs - This option is correct. Table names can include special characters like $, _, and # signs. However, it is generally recommended to use only letters, numbers, and underscores for table names to ensure compatibility across different database systems.
Option C) Table names are case-sensitive - This option is incorrect. Table names are typically not case-sensitive in most database systems. For example, in MySQL, table names are case-insensitive by default. However, there may be some database systems that treat table names as case-sensitive, so it's important to consult the documentation of the specific database system you are using.
Option D) Table names begin with a letter - This option is correct. Table names must begin with a letter. They cannot start with a number or any other special characters.
Option E) Table names are not created in Uppercase by default - This option is not provided.
Therefore, the correct answers are A, B, and D. These options are correct because they accurately describe the naming rules that apply to tables.
-
CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH READ ONLY;
-
CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK OPTION;
-
CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK CONSTRAINT;
-
None of the above
Which of the following are system privileges? (Choose two)
-
DELETE
-
SELECT
-
CREATE TABLE
-
UPDATE
-
DROP TABLE
AI Explanation
To answer this question, we need to understand what system privileges are and which options fall under this category.
System privileges are permissions that allow a user to perform certain actions or operations within a database management system. These privileges typically involve administrative tasks that affect the structure or configuration of the database.
Let's go through each option to understand why it is correct or incorrect:
Option A) DELETE - This option is not a system privilege. DELETE is a database operation that allows a user to remove rows from a table, but it does not involve administrative or structural changes to the database.
Option B) SELECT - This option is not a system privilege. SELECT is a database operation that allows a user to retrieve data from a table, but it does not involve administrative or structural changes to the database.
Option C) CREATE TABLE - This option is a system privilege. CREATE TABLE is a database operation that allows a user to create new tables in the database. This privilege involves administrative control over the structure of the database.
Option D) UPDATE - This option is not a system privilege. UPDATE is a database operation that allows a user to modify data in a table, but it does not involve administrative or structural changes to the database.
Option E) DROP TABLE - This option is a system privilege. DROP TABLE is a database operation that allows a user to delete a table from the database. This privilege involves administrative control over the structure of the database.
The correct answer is C) CREATE TABLE and E) DROP TABLE. These options are system privileges because they involve administrative control and structural changes to the database.
Which of the following data dictionary views displays the objects owned by the user?
-
DICTIONARY
-
TABLE_PRIVILEGES
-
ALL_VIEWS
-
USER_OBJECTS
AI Explanation
To answer this question, we need to understand the purpose of each data dictionary view.
Option A) DICTIONARY - This option is incorrect because the DICTIONARY view does not display the objects owned by the user. It provides information about the database dictionary itself.
Option B) TABLE_PRIVILEGES - This option is incorrect because the TABLE_PRIVILEGES view displays information about the table-level privileges granted to users, not the objects owned by the user.
Option C) ALL_VIEWS - This option is incorrect because the ALL_VIEWS view displays information about all views accessible to the user, not just the ones owned by the user.
Option D) USER_OBJECTS - This option is correct because the USER_OBJECTS view displays information about the objects owned by the user. It includes tables, views, indexes, sequences, procedures, functions, packages, and synonyms.
Therefore, the correct answer is option D) USER_OBJECTS. This option is correct because it displays the objects owned by the user.