databases Online Quiz - 32
| Description: databases Online Quiz - 32 | |
| Number of Questions: 20 | |
| Created by: Aliensbrain Bot | |
| Tags: databases |
Which are DML statements? (Choose all that apply.)
-
COMMIT
-
MERGE
-
UPDATE
-
DELETE
-
CREATE
-
DROP
AI Explanation
To answer this question, you need to understand the concept of Data Manipulation Language (DML) statements. DML statements are used to manipulate data stored in a database. Let's go through each option to understand which ones are DML statements:
Option A) COMMIT - This option is not a DML statement. COMMIT is used to save all the changes made in a transaction to the database.
Option B) MERGE - This option is a DML statement. The MERGE statement allows you to update or insert data into a target table based on the results of a join with a source table.
Option C) UPDATE - This option is a DML statement. The UPDATE statement is used to modify existing data in a table.
Option D) DELETE - This option is a DML statement. The DELETE statement is used to remove rows from a table.
Option E) CREATE - This option is not a DML statement. CREATE is a Data Definition Language (DDL) statement used to create database objects such as tables, views, and indexes.
Option F) DROP - This option is not a DML statement. DROP is a DDL statement used to remove database objects.
The correct answers are B, C, and D. These options are DML statements because they are used to manipulate data stored in a database.
Which four are correct guidelines for naming database tables? (Choose four.)
-
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-9, _, *, and #
-
Must contain only A-Z, a-z, 0-9, _, $, and #
-
Must begin with a letter
Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
-
SELECT ename, salary*12 'Annual Salary' FROM employees;
-
SELECT ename, salary*12 "Annual Salary" FROM employees;
-
SELECT ename, salary*12 AS Annual Salary FROM employees;
-
SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees
Which operator can be used with a multiple-row subquery?
-
=
-
LIKE
-
BETWEEN
-
NOT IN
-
Is
-
<>
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%'
To solve this question, the user needs to know about the SQL SELECT statement and the LIKE operator. The LIKE operator is used to match a character pattern in a string. The underscore (_) represents a single character, while the percent sign (%) represents any number of characters.
Now, let's go through each option and explain why it is right or wrong:
A. SELECT last_name FROM EMP WHERE last_name LIKE '_A%'; This option is correct. It selects the last names of employees from the EMP table where the last name has any character as the first character and "A" as the second character, followed by any number of characters.
B. SELECT last_name FROM EMP WHERE last name ='A%' This option is incorrect because the equal sign (=) is used for exact matches, and the asterisk () is not a valid wildcard character in SQL.
C. SELECT last_name FROM EMP WHERE last name ='A%'; This option is incorrect because the equal sign (=) is used for exact matches, and the underscore () represents only a single character, not any number of characters.
D. SELECT last_name FROM EMP WHERE last name LIKE 'A%' This option is incorrect because the percent sign (%) is used to match any number of characters, not the asterisk ().
Therefore, the correct answer is:
The Answer is: 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
-
CASE
Which clause should you use to exclude group results?
-
WHERE
-
HAVING
-
RESTRICT
-
GROUP BY
-
ORDER BY
AI Explanation
To answer this question, you need to understand the purpose of each clause in a SQL query.
The WHERE clause is used to filter rows based on a condition. It is used to exclude individual rows from the result set based on a specific condition.
The HAVING clause, on the other hand, is used to filter groups based on a condition. It is typically used with the GROUP BY clause and is used to exclude groups from the result set based on a specific condition.
The RESTRICT clause does not exist in standard SQL syntax, so it is not the correct answer.
The GROUP BY clause is used to group rows based on one or more columns. It is used to create groups in the result set based on the specified columns.
The ORDER BY clause is used to sort the result set based on one or more columns. It is not used to exclude any rows or groups from the result set.
Therefore, the correct answer is B) HAVING. The HAVING clause is used to exclude groups from the result set based on a specific condition.
Which is an /SQL*Plus command?
-
INSERT
-
UPDATE
-
SELECT
-
DESCRIBE
-
DELETE
-
RENAME
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
To solve this question, the user needs to know the definition and purpose of a subquery.
A subquery is a query that is nested inside another query and returns data that will be used by the main query. It can be used to retrieve data based on an unknown condition and to filter data from a table.
Now, let's go through each option and explain why it is right or wrong:
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.
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.
C. convert data to a different format: This option is incorrect because data conversion is typically done using functions like CAST or CONVERT, not a subquery.
D. Retrieve data based on an unknown condition: This option is correct. A subquery can be used to retrieve data based on an unknown condition, such as selecting all employees with salaries greater than the average salary.
Therefore, the answer is: D.
What does the TRUNCATE statement do?
-
Removes the table
-
Removes all rows from a table
-
shortens the table to 10 rows
-
Removes all columns from a table
-
Removes foreign keys from a table
Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?
-
"&"
-
ACCEPT
-
PROMPT
-
"&&"
Which two statements about views are true? (Choose two)
-
A view can be created as read only
-
A view can be created as a join on two or more tables.
-
A view cannot have an ORDER BY clause in the SELECT statement
-
A view cannot be created with a GROUP BY clause in the SELECT statement
-
A view must have aliases defined for the column names in the SELECT statement
Explanation: To answer this question, we need to understand the concept of views in the context of databases.
A. A view can be created as read-only - This statement is true. A view can be created with the SELECT statement that restricts the columns and rows that are returned. This can be used to ensure a user only has read access to certain data.
B. A view can be created as a join on two or more tables - This statement is true. A view can be created as a SELECT statement that combines data from two or more tables. This can be used to simplify queries by combining data that is frequently used together.
C. A view cannot have an ORDER BY clause in the SELECT statement - This statement is false. A view can have an ORDER BY clause in the SELECT statement to sort the data returned by the view.
D. A view cannot be created with a GROUP BY clause in the SELECT statement - This statement is false. A view can be created with a GROUP BY clause in the SELECT statement to group the data returned by the view.
E. A view must have aliases defined for the column names in the SELECT statement - This statement is false. A view does not require aliases for the column names in the SELECT statement, although it can be useful to provide more meaningful names for the columns.
Therefore, options A and B are both true statements, making the correct answer B.
Management has asked you to calculate the value 12*salary*commission_pct for all the employees in the EMP table. The EMP table contains these columns: LAST NAME VARCHAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISSION_PCT NUMBER(4,2) Which statement ensures that a value is displayed in the calculated column for all employees?
-
SELECT last_name, 12*salary*commission_pct FROM emp;
-
SELECT last_name, 12*salary* (commission_pct,0) FROM emp;
-
SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp
-
SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;
AI Explanation
To ensure that a value is displayed in the calculated column for all employees, you need to handle the case when the commission_pct is null. The NVL function can be used to replace null values with a default value.
Let's go through each option to understand why it is correct or incorrect:
Option A) SELECT last_name, 12*salary*commission_pct FROM emp;
This option is incorrect because it does not handle the case when commission_pct is null. If commission_pct is null for any employee, the result of the calculation will also be null for that employee.
Option B) SELECT last_name, 12*salary* (commission_pct,0) FROM emp;
This option is incorrect because it uses an invalid syntax. The expression (commission_pct,0) is not a valid way to handle null values.
Option C) SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp
This option is correct because it uses the NVL function to replace null values with 0. If commission_pct is null for any employee, the NVL(commission_pct,0) expression will evaluate to 0, and the calculation will still produce a value.
Option D) SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;
This option is incorrect because the DECODE function is used incorrectly. The DECODE function should have a default value as the last argument, but in this case, it is missing. Additionally, the DECODE function is not necessary to handle null values.
The correct answer is C. This option ensures that a value is displayed in the calculated column for all employees by using the NVL function to replace null values with 0.
Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHAR2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two.)
-
SUM(start_date)
-
AVG(start_date)
-
COUNT(start_date)
-
AVG(start_date, end_date)
-
MIN(start_date)
-
MAXIMUM(start_date)
AI Explanation
To determine which two aggregate functions are valid on the START_DATE column, let's go through each option:
A. SUM(start_date) - This option is incorrect because the SUM function is used to calculate the sum of numeric values, not dates.
B. AVG(start_date) - This option is incorrect because the AVG function is used to calculate the average of numeric values, not dates.
C. COUNT(start_date) - This option is correct because the COUNT function can be used to count the number of occurrences of a column, including dates.
D. AVG(start_date, end_date) - This option is incorrect because the AVG function can only be used on a single column, not multiple columns.
E. MIN(start_date) - This option is correct because the MIN function can be used to find the minimum value in a column, including dates.
F. MAXIMUM(start_date) - This option is incorrect because there is no MAXIMUM function in SQL. The correct function is MAX, which can be used to find the maximum value in a column, including dates.
Therefore, the two valid aggregate functions on the START_DATE column are:
C. COUNT(start_date)
E. MIN(start_date)
The correct answer is C and E.
Evaluate the SQL statement: SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual; What will be displayed?
-
0
-
1
-
0.00
-
An error statement
AI Explanation
To evaluate the given SQL statement: SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual;
Let's break it down step by step:
MOD (1600, 10): This returns the remainder when 1600 is divided by 10. The result is 0.
TRUNC (0, -1): This truncates the number 0 to the nearest 10th place. Since the decimal place is -1, it rounds down to the nearest 10th. The result is still 0.
ROUND (0, 2): This rounds the number 0 to 2 decimal places. Since the number has no decimal places, it remains 0.
Therefore, the SQL statement will display 0 as the result.
The correct answer is A) 0.
Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_marks; What is the result of the SELECT statement?
-
The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.
-
The statement returns an error at the SELECT clause.
-
The statement returns an error at the WHERE clause.
-
The statement returns an error at the ORDER BY clause.
AI Explanation
To answer this question, let's go through each option to understand why it is correct or incorrect:
Option A) The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject. This option is incorrect because the SELECT statement is calculating the total marks by adding subj1 and subj2, not the sum of all marks. Additionally, the WHERE clause is checking if each subject's mark is greater than the average mark for that subject, not the sum of all marks.
Option B) The statement returns an error at the SELECT clause. This option is incorrect because there is no error in the SELECT clause. The SELECT clause is calculating the total marks correctly by adding subj1 and subj2.
Option C) The statement returns an error at the WHERE clause. This option is correct. The WHERE clause is using the AVG function to calculate the average mark for each subject. However, you cannot use aggregate functions like AVG in the WHERE clause directly. To use aggregate functions in the WHERE clause, you need to use a subquery or a HAVING clause.
Option D) The statement returns an error at the ORDER BY clause. This option is incorrect because there is no error in the ORDER BY clause. The ORDER BY clause is ordering the result set based on the total_marks column.
The correct answer is C. The statement returns an error at the WHERE clause because you cannot use aggregate functions like AVG in the WHERE clause directly.
Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose three.)
-
SELECT TO_CHAR(2000, '$#,###.##') FROM dual;
-
SELECT TO_CHAR(2000, '$0,000.00') FROM dual;
-
SELECT TO_CHAR(2000, '$9,999.00') FROM dual;
-
SELECT TO_CHAR(2000, '$9,999.99') FROM dual;
-
SELECT TO_CHAR(2000, '$2,000.00') FROM dual;
-
SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;
AI Explanation
To answer this question, we need to understand the syntax of the TO_CHAR function in Oracle SQL.
The TO_CHAR function is used to convert a number or date value to a character string. It takes two parameters: the value to be converted and the format model.
In the given question, we need to display the number 2000 in the format "$2,000.00".
Now let's go through each option to determine which ones display the number in the correct format:
Option A) SELECT TO_CHAR(2000, '$#,###.##') FROM dual; This option uses the format model '$#,###.##'. However, it does not include the leading zeros after the dollar sign. Therefore, Option A is incorrect.
Option B) SELECT TO_CHAR(2000, '$0,000.00') FROM dual; This option uses the format model '$0,000.00', which includes the leading zeros after the dollar sign and displays the number 2000 in the format "$2,000.00". Therefore, Option B is correct.
Option C) SELECT TO_CHAR(2000, '$9,999.00') FROM dual; This option uses the format model '$9,999.00', which does not include the leading zeros after the dollar sign. However, it displays the number 2000 in the correct format "$2,000.00" by using a placeholder for the thousands digit. Therefore, Option C is correct.
Option D) SELECT TO_CHAR(2000, '$9,999.99') FROM dual; This option uses the format model '$9,999.99', which includes the leading zeros after the dollar sign. However, it displays the number 2000 with two decimal places instead of the required one decimal place. Therefore, Option D is incorrect.
Option E) SELECT TO_CHAR(2000, '$2,000.00') FROM dual; This option uses the format model '$2,000.00'. While it displays the number 2000 in the correct format "$2,000.00", it does not use the correct format model. Therefore, Option E is incorrect.
Option F) SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual; This option uses the format model '$N,NNN.NN'. However, it does not include the leading zeros after the dollar sign. Therefore, Option F is incorrect.
The three SELECT statements that display 2000 in the format "$2,000.00" are Options B, C, and D.
What is necessary for your query on an existing view to execute successfully?
-
The underlying tables must have data.
-
You need SELECT privileges on the view.
-
The underlying tables must be in the same schema.
-
You need SELECT privileges only on the underlying tables.
AI Explanation
To answer this question, you need to understand how views work in a database. 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 the existence of data in the underlying tables is not necessary for the query on a view to execute successfully. The view is a virtual table that is derived from one or more underlying tables, and it does not depend on the presence of data in those tables.
Option B) You need SELECT privileges on the view - This option is correct because in order to execute a query on an existing view, you need to have SELECT privileges specifically on that view. Privileges on the underlying tables are not sufficient; you need to have the necessary privileges on the view itself.
Option C) The underlying tables must be in the same schema - This option is incorrect because the underlying tables of a view can be in different schemas. The schema of the view itself is independent of the schemas of the underlying tables.
Option D) You need SELECT privileges only on the underlying tables - This option is incorrect because, as mentioned earlier, you need SELECT privileges on the view itself, not just on the underlying tables. Privileges on the underlying tables alone are not enough to execute a query on a view.
The correct answer is Option B. This option is correct because you need SELECT privileges on the view in order to execute a query on it successfully.
You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?
-
SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE > 5;
-
SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5;
-
SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5;
-
SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5;
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; What is true about them?
-
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 understand these SQL statements, the user needs to know how to query a database and sort the results in descending order based on a specific column.
The first SQL statement selects the last name, salary, and hire date columns from the EMPLOYEES table and orders the results by salary in descending order using the ORDER BY clause with the DESC keyword.
The second SQL statement is identical to the first one but uses the shorthand ORDER BY 2 DESC instead of explicitly specifying the salary column name.
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 and sort the results in the same descending order based on the salary column. The shorthand ORDER BY 2 DESC in the second statement refers to the second column in the SELECT clause, which is the salary column.
B. The second statement returns a syntax error: This option is incorrect. The second SQL statement is a valid syntax and produces the same results as the first one.
C. There is no need to specify DESC because the results are sorted in descending order by default: This option is incorrect. SQL sorts the results in ascending order by default, so the DESC keyword is needed to sort the results in descending order.
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. Adding a column alias does not change the way the ORDER BY clause works. It only renames the column in the result set.
The Answer is: A