1z0-071 Exam Dumps - Oracle Database 12c SQL
Which three statements are true about an ORDER BY clause?
An ORDER BY clause always sorts NULL values last.
An ORDER BY clause can perform a binary sort
An ORDER BY clause can perform a linguistic sort
By default an ORDERBY clause sorts rows in ascending order
An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level
Answer:
Explanation:
In Oracle Database 12c, the behavior of the ORDER BY clause is guided by several rules:
Option B: An ORDER BY clause can perform a binary sort.
A binary sort is the default sorting mechanism in Oracle Database, which is based on the binary representation of the data.
Option C: An ORDER BY clause can perform a linguistic sort.
Oracle supports linguistic sorting through the use of the NLS_SORT parameter, which can be set to various linguistic and cultural norms.
Option D: By default, an ORDER BY clause sorts rows in ascending order.
If no ASC or DESC keyword is specified, Oracle will sort the results in ascending order by default.
Options A and E are incorrect:
Option A is not universally true; the position of NULL values in the sort order can be controlled by settings or specific SQL syntax (NULLS FIRST or NULLS LAST).
Option E is incorrect as the HAVING clause filters groups after data has been grouped by the GROUP BY clause and cannot be logically placed before ORDER BY in processing order.
The SYSDATE function displays the current Oracle Server date as:
21 -MAY-19
You wish to display the date as:
MONDAY, 21 MAY, 201 9
Which statement will do this?
SELECT TO _ CHAR (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;
SELECT TO _ DATE (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;
SELECT TO_ CHAR (SYSDATE, ' FMDD, DAY MONTH, YYYY') FROM DUAL;
SELECT TO_ CHAR (SYSDATE, ' FMDAY, DDTH MONTH, YYYY') FROM DUAL;
Answer:
Explanation:
To format a date in Oracle SQL, TO_CHAR function is used to convert date types to string with a specified format:
Option A: SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
This correctly applies the date format model 'FMDAY, DD MONTH, YYYY', which will display the date as requested: "MONDAY, 21 MAY, 2019". FM modifier is used to remove padding blanks or leading zeros.
Options B, C, and D do not achieve the desired output:
Option B is incorrect because TO_DATE converts a string to a date, not format a date.
Option C and D have incorrect format strings that do not match the required output format.
Examine the description of the ORDERS table:
Which three statements execute successfully?
(SELECT * FROM orders
UNION ALL
SELECT* FROM invoices) ORDER BY order _id;
SELECE order _id, order _ date FRON orders
LNTERSECT
SELECT invoice_ id, invoice_ id, order_ date FROM orders
SELECT order_ id, invoice_ data order_ date FROM orders
MINUS
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;
SELECT * FROM orders ORDER BY order_ id
INTERSEOT
SELECT * FROM invoices ORDER BY invoice_ id;
SELECT order_ id, order_ data FROM orders
UNION ALL
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id;
SELECT * FROM orders
MINUS
SELECT * FROM INVOICES ORDER BY 1
SELECT * FROM orders ORDER BY order_ id
UNION
SELECT * FROM invoices;
Answer:
Explanation:
In Oracle SQL, set operations like UNION, UNION ALL, INTERSECT, and MINUS can be used to combine results from different queries:
Option A:
Combining results using UNION ALL followed by ORDER BY will execute successfully because UNION ALL allows duplicate rows and ORDER BY can be used to sort the combined result set.
Option E:
Similar to option A, UNION ALL combines all rows from the two selects and allows ordering of the results.
Option G:
UNION combines the results from two queries and removes duplicates, and ORDER BY can be used to sort the final result set.
Options B, C, D, and F are incorrect because:
Option B: You cannot intersect different columns (ORDER_ID with INVOICE_ID).
Option C: Incorrect column names and syntax with ORDER BY.
Option D: ORDER BY cannot be used before a set operator like INTERSECT.
Option F: ORDER BY cannot be used directly after a MINUS operator without wrapping the MINUS operation in a subquery.
Which three statements are true?
A customer can exist in many countries.
The statement will fail if a row already exists in the SALES table for product 23.
The statement will fail because subquery may not be I contained in a values clause.
The SALES table has five foreign keys.
The statement will execute successfully and a new row will be inserted into the SALES table.
A product can have a different unit price at different times.
Answer:
Explanation:
A. A customer can exist in many countries. This is true as customers can have multiple addresses or operations in different countries, and a database design can reflect this by allowing multiple country entries for a single customer1.
C. The statement will fail because subquery may not be I contained in a values clause. In Oracle Database 12c, a subquery cannot be used within the VALUES clause of an INSERT statement. The correct approach would be to use the subquery in conjunction with the INSERT INTO … SELECT syntax if multiple rows are derived from a subquery2.
F. A product can have a different unit price at different times. It is common for products to have different unit prices at different times due to various factors such as promotions, discounts, or changes in cost price. This can be represented in a database by having a price history table or a similar mechanism to track the changes in price over time1.
Note: The other options are incorrect because:
B. The statement about the SALES table failing if a row already exists for product 23 is not necessarily true. Oracle allows for multiple rows with the same product ID if the table is designed to handle such cases, like having a composite primary key or no constraints preventing duplicates.
D. Without specific information about the SALES table’s design, we cannot verify the number of foreign keys it has.
E. The statement about the successful execution and insertion of a new row into the SALES table is too vague without the context of the actual SQL statement being referred to.
Which two statements are true about Entity Relationships?
A Relationship can be mandatory for both entities
A one-to-one relationship is always a self-referencing relationship
A many-to-many relationship can be implemented only by using foreign keys
A table name can be specified just once when selecting data from a table having a selfreferencing relationship
A one-to-many relationship in one direction is a one-to-one relationship in the other direction
Answer:
Explanation:
A relationship in an Entity-Relationship (ER) model can indeed be mandatory for both entities, meaning that an instance of one entity must relate to one and only one instance of another entity, and vice versa. This is commonly seen in a one-to-one relationship where both sides are mandatory.
Option B is incorrect; a one-to-one relationship does not have to be self-referencing. Self-referencing (or recursive) relationships occur when an entity has a relationship with itself.
Option C is incorrect; a many-to-many relationship typically requires a join table or associative entity with foreign keys that reference the primary keys of the two entities it connects.
Option D is incorrect; in the case of a self-referencing relationship, you may need to use aliases to specify the table more than once to differentiate between the self-referenced columns.
Option E is incorrect because a one-to-many relationship in one direction does not equate to a one-to-one relationship in the opposite direction.
References:
Entity-Relationship Model Concepts: ER Model
SELECT *
FROM bricks,colors;
Which two statements are true?
You can add an ON clause with a join condition.
You can add a WHERE clause with filtering criteria.
It returns the number of rows in BRICKS plus the number of rows in COLORS.
You can add a USING clause with a join condition.
It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.
Answer:
Explanation:
For the SELECT statement from two tables without a JOIN clause:
Option B: You can add a WHERE clause with filtering criteria.
A WHERE clause can be added to a query to filter the results based on specified conditions.
Option E: It returns the same rows as SELECT * FROM bricks CROSS JOIN colors.
A comma-separated list of tables in a FROM clause is equivalent to a CROSS JOIN, resulting in a Cartesian product of the tables.
Options A, C, and D are incorrect because:
Option A: You cannot add an ON clause without changing the comma-separated FROM clause to a proper JOIN.
Option C: It returns the Cartesian product, not the sum, of the rows from BRICKS and COLORS.
Option D: A USING clause is not applicable without changing the syntax to a proper JOIN.
Which three are true about multiple INSERT statements?
They can be performed only by using a subquery.
They can be performed on relational tables.
They can be performed on views.
They can be performed on remote tables.
They can be performed on external tables using SQL*Loader.
They can insert each computed row into more than one table.
Answer:
Explanation:
Multiple INSERT statements allow data insertion into one or more tables based on different conditions or datasets:
Option A: False. Multiple INSERT operations can be performed using direct values, subqueries, or even default values, not exclusively through subqueries.
Option B: True. They can indeed be performed on relational tables, which is the standard use case in most relational databases.
Option C: True. INSERT operations can be performed on updatable views, assuming the view is not complex (involving joins, GROUP BY clauses, etc.).
Option D: True. Oracle allows INSERT operations on remote tables via database links, enabling distributed database interactions.
Option E: False. Direct INSERT statements cannot be performed on external tables. External tables are typically used for read operations, with data loading handled through utilities like SQL*Loader or external data processing tools.
Option F: False. Each INSERT statement inserts data into one table. While a single SQL command block can contain multiple INSERT statements, each one is directed at a single table.
Examine this description of the PRODUCTS table:
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS?
ALTER TAELE products DROP COLUMN expiry_date;
CREATE INDEX price_idx on products (price);
ALTER TABLE products SET UNUSED(expiry_date);
TRUNCATE TABLE products;
ALTER TABLE products DROP UNUSED COLUMNS
DROP TABLE products
Answer:
Explanation:
B. CREATE INDEX price_idx on products (price); E. ALTER TABLE products DROP UNUSED COLUMNS; F. DROP TABLE products.
Comprehensive and Detailed Explanation WITH all References:
When a table is in read-only mode, most types of modifications are prohibited. However, certain operations can still be performed.
A. Incorrect. You cannot drop a column from a table that is in read-only mode, as it is a modifying operation. B. Correct. You can create an index on a read-only table. Creating an index does not modify the actual rows within the table; it builds a separate structure used for faster access. C. Incorrect. The SET UNUSED statement marks one or more columns as unused so they can be dropped when the database is not busy. This operation is considered a modifying operation and therefore is not allowed on a read-only table. D. Incorrect. Truncate is a DDL operation that would delete all rows from a table. This operation is not allowed on a read-only table. E. Correct. The ALTER TABLE ... DROP UNUSED COLUMNS statement is used to drop columns that have been previously marked as unused using the SET UNUSED statement. This operation is allowed because it only affects previously marked unused columns, not actively used data. F. Correct. Dropping a table is allowed even if it's in read-only mode because it is a DDL operation that does not operate on the rows of the table but rather on the object itself.
The behavior of read-only tables and the operations that can be performed on them are detailed in the Oracle Database SQL Language Reference and Oracle Database Administrator's Guide.