Choose two
Examine the description of the PRODUCT DETALS table:
A . PRODUCT_ID can be assigned the PRIMARY KEY constraint.
In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.
B . EXPIRY_DATE cannot be used in arithmetic expressions. (Incorrect)
This statement is not necessarily true. Dates in Oracle can be used in arithmetic expressions, typically to add or subtract days from a date.
C . EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it. (Incorrect)
Unless explicitly specified, a date column does not default to SYSDATE. A default value must be set using the DEFAULT clause during the table creation or altered later.
D . PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
This is correct. In Oracle, if a numeric column like PRODUCT_PRICE has a NULL value (meaning no value stored in it), it can still be used in an arithmetic expression. In such expressions, NULL is typically treated as a zero, but the result of any arithmetic with NULL is also NULL.
E . PRODUCT_PRICE contains the value zero by default if no value is assigned to it. (Incorrect)
Unless a default value is explicitly specified during the table creation or altered later, a numeric column like PRODUCT_PRICE does not automatically have a default value of zero.
F . PRODUCT_NAME cannot contain duplicate values. (Incorrect)
There is no constraint indicated that would prevent PRODUCT_NAME from containing duplicate values. Without a UNIQUE or PRIMARY KEY constraint, a column can contain duplicates.
The correct answers are A and D. PRODUCT_ID can be the primary key because it's specified as NOT NULL, thus it can uniquely identify each row in the table. PRODUCT_PRICE can be used in an arithmetic expression with the understanding that if it's NULL, the result of the expression would be NULL as well.
Examine the description of the EMPLOYEES table:
Which query is valid?
When using the GROUP BY clause, every column in the SELECT clause that is not an aggregate function must be included in the GROUP BY clause:
A . SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date: This is a valid query because all non-aggregate columns in the SELECT list (dept_id and join_date) are included in the GROUP BY clause.
Queries B and C are invalid because they attempt to nest aggregate functions, which is not allowed. Query D is invalid because join_date is not included in the GROUP BY clause.
Oracle Database SQL Language Reference 12c, specifically the section on GROUP BY clause constraints.
You have the privileges to create any type of synonym.
Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accesible to all users?
Synonyms in Oracle are aliases for database objects that can simplify SQL statements for database users.
A . The term 'GLOBAL' is not used in the creation of synonyms in Oracle.
B . The statement without the keyword PUBLIC will create a private synonym that is only accessible to the user creating the synonym, not all users.
C . The correct syntax does not include PUBLIC as a prefix to the synonym name itself, making this option incorrect.
D . You cannot specify the SYS schema for creating synonyms, as it is reserved for system objects.
E . This is the correct syntax to create a public synonym, which makes the underlying object accessible to all users.
Oracle Database SQL Language Reference, 12c Release 1 (12.1): 'CREATE SYNONYM'
Which two statements will do an implicit conversion?
A . True. This statement will work if customer_id is a character data type in the database. Oracle will implicitly convert the numeric literal 0001 to a string to compare with customer_id.
D . True. If the insert_date is of type DATE and the NLS_DATE_FORMAT matches 'DD-MON-YY', Oracle will implicitly convert the string literal '01-JAN-19' to a date type to compare with insert_date.
B is incorrect because if customer_id is a numeric data type, there is no need for implicit conversion. C is incorrect because using the DATE literal DATE '2019-01-01' is an explicit conversion. E is incorrect because TO_CHAR(customer_id) is an explicit conversion from a numeric to a string data type.
