1Z0-071 Free Practice Test

In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?

1. A. second normal form
2. B. first normal form
3. C. third normal form
4. D. fourth normal form

Correct Answer: A
References:
https://blog.udemy.com/database-normal-forms/

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

1. A. WHERE and HAVING clauses cannot be used together in a SQL statement.
2. B. The HAVING clause conditions can have aggregate functions.
3. C. The HAVING clause conditions can use aliases for the columns.
4. D. The WHERE clause is used to exclude rows before the grouping of data.
5. E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Correct Answer: ABD

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

1. A. You can use column alias in the GROUP BY clause.
2. B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
3. C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
4. D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
5. E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.

Correct Answer: DE

You must create a table for a banking application. (Choose the best answer.) One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid. Which data type would you use?

1. A. Date
2. B. Number
3. C. Timestamp
4. D. Interval day to second
5. E. Interval year to month

Correct Answer: D

Examine the structure of the EMPLOYEES table. (Choose the best answer.)
<>

1. A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE hire_date > SYSDATE- 180;
2. B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
3. C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = \'100\')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
4. D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;

Correct Answer: C