Q1. View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECTfirst_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the first Monday after the completion of six months of the hiring. The
NLS_TERRITORY parameter is set to AMERICA in the session. Which statement is true regarding this query?
A. The query would execute to give the desired output.
B. The query would not execute because date functions cannot be nested.
C. The query would execute but the output would give review dates that are Sundays.
D. The query would not execute because the NEXT_DAY function accepts a string as argument.
Answer: C
Q2. The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT'FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issuedbyOE.
B. CREATEPUBLIC SYNONYM ordFORorders; This command is issued by OE.
C. CREATESYNONYM ord FOR oe.orders; This command isissuedby thedatabaseadministrator.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders;Thiscommand is issued by the database administrator.
Answer: D
Q3. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)
A. The FIRST_NAME column wouldbedropped provided it does notcontainany data.
B. The FIRST_NAME column would bedropped providedat least one or more columnsremain inthe table.
C. The FIRST_NAME column can be rolledback providedthe SET UNUSED option is added to the above SQL statement.
D. The FIRST_NAME column can be dropped evenif itis part ofacomposite PRIMARY KEY provided the CASCADE option is used.
Answer: BD
Q4. Which view would you use to display the column names and DEFAULT values for a table?
A. DBA_TABLES
B. DBA_COLUMNS
C. USER_COLUMNS
D. USER TAB COLUMNS
Answer: D
Q5. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o
USING(order_id);
Which statement is true regarding the execution of this SQL statement?
A. The statementwouldnot execute because table aliasesarenot allowed in the JOIN clause.
B. Thestatement wouldnotexecute because thetablealias prefix is not usedin theUSING clause.
C. The statementwouldnot execute because allthecolumns in the SELECT clause are not prefixed with table aliases.
D. The statement would not execute because the column part of the USING clause cannot haveaqualifier in the SELECT list.
Answer: D
Q6. View the Exhibit and examine the structure of the ORDERS table.
The ORDERS table belongs to the user OE. HR is another user in the database.
Evaluate the commands issued by users OE and HR in the following order:
Statement 1 by user OE: GRANT SELECT,
UPDATE(customer_id, order_total)
ON orders
TOhr;
Statement 1 by user HR: SELECT * FROM oe.orders;
Statement 2 by user HR: UPDATE oe.orders
SET order_totah 10000;
Which statement is true regarding the above commands?
A. Statement 1 by user OE would not work because the statement has to be issued by the DBA.
B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update.
C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully.
D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns.
Answer: C
Q7. Evaluate the following SQL statement:
SELECT 2 col1,ycol2
FROM dual
UNION
SELECT 1 ,'x'
FROM dual
UNION
SELECT 3 .NULL
FROM dual
ORDER BY 2;
Which statement is true regarding the output of the SQL statement?
A. It would execute and the order of the values in the first column would be 3,2,1.
B. It would execute and the order of the values in the first column would be 1,2,3.
C. It would not execute because the column alias name has not been used in the ORDER BY clause.
D. It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement.
Answer: B
Q8. View the Exhibit and examine the structure of the ORDERS table.
The ORDER_ID column is the PRIMARY KEY in the ORDERS table. Evaluate the following
CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date.customer_id
FROM orders;
Which statement is true regarding the above command?
A. The NEW_ORDERS table would not get created because the DEFAULT value can not be specified in the column definition.
B. The NEW_ORDERS table would get created and only the NOT NULL constraint definedon the specified columns would bepassed to the new table.
C. The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
Answer: B
Q9. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?
A. WHERE
B. HAVING
C. GROUP BY
D. START WITH
E. CONNECT BY PRIOR
Answer: E
Q10. Which statement correctly differentiates a system privilege from an object privilege?
A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.
Answer: D