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

A. You can use column alias in the GROUP BY clause.

B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.

C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.

D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.

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 clause.

View Answer

Q2. Evaluate the following SELECT statement and view the Exhibit to examine its output:

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS

Which two statements are true about the output? (Choose two.)

A. In the second column, indicates a check constraint.

B. The STATUS column indicates whether the table is currently in use.

C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

View Answer

Q3. Which two statements are true about Data Manipulation Language (DML) statements?

A. AH INSERT INTO. . .VALUES. . statement can add multiple rows per execution to a table.

B. An UPDATE...SET... statement can modify multiple rows based on multiple conditions on a table.

C. A DELETE FROM ..... statement can remove rows based on only a single condition on a table.

D. An INSERT INTO...VALUES..... statement can add a single row based on multiple conditions on a table.

E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.

F. An UPDATE...SET.... statement can modify multiple rows based on only a single condition on a table.

View Answer

Q4. View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.

You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?

A. INTERSECT

B. UNION ALL

C. MINUS

D. UNION

View Answer

Q5. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?

A. INSERT INTO orders

VALUES (1,'10-mar-2007', 'direct',

(SELECT customer_id FROM customers

WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id

FROM customers

WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total)

VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);

C. INSERT INTO orders (order_id.order_date.order_mode, (SELECT customer_id

FROM customers

WHERE cust_last_name='Roberts' AND credit _limit=600) .order_total)

VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);

D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total FROM orders o, customers c

WHERE o.customer_id = c.customer_id

AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers

WHERE cust_last_name='Roberts' AND Credit_limit=600), 1000);

View Answer

Q6. Which three statements are true regarding group functions? (Choose three.)

A. They can be used on columns or expressions.

B. They can be passed as an argument to another group function.

C. They can be used only with a SQL statement that has the GROUP BY clause.

D. They can be used on only one column in the SELECT clause of a SQL statement.

E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

View Answer

Q7. View the Exhibit and examine the descriptions of the DEPT and LOCATIOMS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

Which SQL statement would you execute to accomplish the task?

A. UPDATE dept d

SET city = ANY (SELECT city FROM locations l);

B. UPDATE dept d

SET city = (SELECT city FROM locations l) WHERE d.location_id = l.location_id;

C. UPDATE dept d

SET city = (SELECT city FROM locations l

WHERE d.location_id = l.location_id);

D. UPDATE dept d

SET city = ALL (SELECT city FROM locations l

WHERE d.location_id = l.location_id);

View Answer

Q8. Which statement is true regarding the INTERSECT operator?

A. It ignores NULL values

B. The number of columns and data types must be identical for all SELECT statements in the query

C. The names of columns in all SELECT statements must be identical

D. Reversing the order of the intersected tables the result

View Answer

Q9. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

A. The outer query stops evaluating the result set of the inner query when the first value is found.

B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

View Answer

Q10. 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_IDRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

B. The NEW_IDRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

View Answer