Tag: databases
Questions Related to databases
What can a mandatory one to one relationship indicate?
-
More entities are needed
-
The model should be denormolized
-
The tables are not properly indexed
-
The model cannot be implemented physically
-
More attributes are needed
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.
-
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.