Oracle PL 1z0-071 Oracle Study Notes

In Oracle 12c SQL, the GROUP BY clause is used to arrange identical data into groups with the GROUP BY expression followed by the SELECT statement. The SUM() function is then used to calculate the sum for each grouped record on a specific column, which in this case is the salary column.

Option A is valid because it correctly applies the GROUP BY clause. Both dept_id and join_date are included in the SELECT statement, which is a requirement when using these columns in conjunction with the GROUP BY clause. This means that the query will calculate the sum of salaries for each combination of dept_id and join_date. It adheres to the SQL rule that every item in the SELECT list must be either an aggregate function or appear in the GROUP BY clause.

Option B is invalid due to a typo in SELECT depe_id and also because it ends with a colon rather than a semicolon.

Option C is invalid because you cannot nest aggregate functions like MAX(AVG(salary)) without a subquery.

Option D is invalid for the same reason as option C, where it tries to nest aggregate functions AVG(MAX(salary)), which is not allowed directly in SQL without a subquery.

For further reference, you can consult the Oracle 12c documentation, which provides comprehensive guidelines on how to use the GROUP BY clause and aggregate functions like SUM():

Oracle Database SQL Language Reference, 12c Release 1 (12.1): GROUP BY Clause

Oracle Database SQL Language Reference, 12c Release 1 (12.1): Aggregate Functions

In Oracle SQL, date arithmetic can be performed directly, subtracting one date from another to get the number of days between them:

Option A:

SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL; This query successfully calculates the difference between the current date and a specific date.

Option D:

SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL; This query subtracts a specific date from the current date and then subtracts 1 more day, which will execute successfully.

Option F:

SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL; Similar to D, this will successfully compute the date difference minus one day.

Options B, C, and E are incorrect because:

Option B and Option C: You cannot divide by a date or divide a date by a number.

Option E: You cannot subtract a date from a number.

A: This statement will not execute successfully because you cannot subtract a DAY interval from a DATE directly. SYSDATE needs to be cast to a TIMESTAMP first.

B: This statement is correct. It adds an interval of '1' DAY to the current TIMESTAMP.

C: This statement is correct. It subtracts an interval of '1' MINUTE from '1' DAY.

D: This statement will not execute successfully. In Oracle, you cannot add intervals of different date fields (DAY and MONTH) directly.

E: This statement has a syntax error with the quotes around INTERVAL and a misspelling, it should be 'INTERVAL'.

Oracle Database 12c SQL supports interval arithmetic as described in the SQL Language Reference documentation.

A: True. The DESCRIBE command can indeed be used from SQL Developer as well as other Oracle database tools such as SQL*Plus. This command is used to display the structure of a table, view, or other object, showing information such as column names, data types, and whether a column is nullable.

B: True. The DESCRIBE command can be used to display the structure of an existing view, showing similar information as it would for a table. This includes the columns, their data types, and other pertinent details.

D: True. When DESCRIBE is used, it does display the NOT NULL constraints for columns that have this constraint. This is part of the basic information about the structure of a table or view that can help developers understand the requirements of the data stored therein.