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.
To solve this question, the user needs to understand the SQL SELECT statement and the ORDER BY clause. The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.
Now, let's go through each option and explain why it is right or wrong:
A. The two statements produce identical results. This option is correct. Both SQL statements select the same columns from the same table, and they both sort the results by the salary column in descending order. The syntax used to specify the column to sort by is different (column name vs. column position), but they are functionally equivalent.
B. The second statement returns a syntax error. This option is incorrect. The second SQL statement is also valid syntax and should execute without errors.
C. There is no need to specify DESC because the results are sorted in descending order by default. This option is incorrect. The ORDER BY clause sorts the result set in ascending order by default. If you want to sort in descending order, you need to explicitly specify the DESC keyword.
D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement. This option is incorrect. The column alias does not affect the sorting behavior of the ORDER BY clause. It only changes the name of the column in the result set.
Therefore, the correct answer is:
The Answer is: A
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;
Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?
-
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.
AI Explanation
To evaluate the SQL statement and understand the impact of removing parentheses from the calculation, let's break down the statement and analyze each part:
The SQL statement is as follows:
SELECT e.employee_id, (.15 * e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE
FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
The statement selects the employee ID (e.employee_id
) and calculates the value in the CALC_VALUE
column. The calculation involves multiple mathematical operations, including multiplication and addition.
If we remove all the parentheses from the calculation, the statement would look like this:
SELECT e.employee_id, .15 * e.salary + .5 * e.commission_pct + s.sales_amount * .35 * e.bonus AS CALC_VALUE
FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
Now, let's compare both versions of the SQL statement.
The original version of the statement uses parentheses to group certain parts of the calculation, which ensures that these parts are evaluated together before performing the addition operation. Removing the parentheses means that the calculation will be evaluated based on the order of operations (PEMDAS/BODMAS), where multiplication is performed before addition.
However, since the multiplication and addition operations are being performed on constants and column values, the order of operations does not affect the final result. The multiplication and addition operations are commutative, which means the order in which they are performed does not change the result.
Therefore, removing the parentheses from the calculation will not make any difference in the value displayed in the CALC_VALUE
column. Hence, the correct answer is C) There will be no difference in the value displayed in the CALC_VALUE column.
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.
In which scenario would index be most useful?
-
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.
AI Explanation
To answer this question, we need to understand the purpose and functionality of an index in a database.
An index is a data structure that improves the speed of data retrieval operations on a database table. It allows the database system to locate and access specific rows more quickly, based on the values stored in one or more columns.
Let's go through each option to understand why it is correct or incorrect:
Option A) The indexed column is declared as NOT NULL. This option is incorrect because declaring a column as NOT NULL does not directly affect the usefulness of an index. The purpose of an index is to improve data retrieval speed, regardless of whether the indexed column allows NULL values or not.
Option B) The indexed columns are used in the FROM clause. This option is incorrect because using indexed columns in the FROM clause does not necessarily determine the usefulness of an index. The FROM clause is used to specify the tables from which data is retrieved, and an index is not specifically tied to this clause.
Option C) The indexed columns are part of an expression. This option is incorrect because using indexed columns as part of an expression does not directly determine the usefulness of an index. While an index can still be used in certain cases when columns are part of an expression, it is not the primary factor that determines the usefulness of an index.
Option D) The indexed column contains a wide range of values. This option is correct because an index is most useful when the indexed column contains a wide range of values. When a column has a wide range of values, an index allows for faster data retrieval by organizing the values in a way that makes it easier to search and locate specific rows.
The correct answer is Option D. This option is correct because having a wide range of values in the indexed column makes the index more useful in improving the speed of data retrieval operations.
Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?
-
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.
You need to display the last names of those employees who have the letter “A” as the second character in their names. Which SQL statement displays the required results?
-
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
AI Explanation
To answer this question, you need to understand character manipulation functions. Let's go through each option to understand why it is correct or incorrect:
Option A) TRIM - This option is correct because the TRIM function is used to remove leading and trailing spaces from a string.
Option B) REPLACE - This option is correct because the REPLACE function is used to replace a specified substring with another substring in a string.
Option C) TRUNC - This option is incorrect because the TRUNC function is used to truncate a number to a specified number of decimal places.
Option D) TO_DATE - This option is incorrect because the TO_DATE function is used to convert a string to a date value.
Option E) MOD - This option is incorrect because the MOD function is used to find the remainder of a division operation.
The correct answers are Option A) TRIM and Option B) REPLACE. These options are correct because they are character manipulation functions used to modify strings.
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
To solve this question, the user needs to understand the different SQL operators and how they work. The user must also know how to use these operators in a SQL statement to perform a specific task.
Now, let's go through each option and explain whether it can be used to substitute the 'IN' operator:
A. ALL: The ALL operator is used to compare a value to all values in another set of values. It cannot be used to substitute the 'IN' operator. Therefore, option A is incorrect.
B. BETWEEN...AND: The BETWEEN...AND operator is used to select values within a given range. It cannot be used to substitute the 'IN' operator. Therefore, option B is incorrect.
C. LIKE: The LIKE operator is used to compare a value to similar values using wildcard characters. It cannot be used to substitute the 'IN' operator. Therefore, option C is incorrect.
D. OR: The OR operator is used to combine multiple conditions in a WHERE clause. It can be used to substitute the 'IN' operator by specifying each value as a separate condition joined by the OR operator. Therefore, option D is correct.
The Answer is: D
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.
Which of the following are used to define a CHAR datatype variable?
-
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.
Which of the following statements creates a view and does not allow the values to be changed through the view?
-
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.