Oracle Database 11g: SQL Fundamentals I 1Z0-051 | Free Full Dump

QUESTION 1 Which is the valid CREATE TABLE statement?

A.CREATE TABLE emp9$# (emp_no NUMBER (4));

B.CREATE TABLE 9emp$# (emp_no NUMBER(4));

C.CREATE TABLE emp*123 (emp_no NUMBER(4));

D.CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);

Correct Answer: A

QUESTION 2 Which two statements are true regarding tables? (Choose two.)

A.A table name can be of any length.

B.A table can have any number of columns.

C.A column that has a DEFAULT value cannot store null values.

D.A table and a view can have the same name in the same schema.

E.A table and a synonym can have the same name in the same schema.

F.The same table name can be used in different schemas in the same database.

Correct Answer: EF

QUESTION 3 Which two statements are true regarding constraints? (Choose two.)

A.A foreign key cannot contain NULL values. 

B.A column with the UNIQUE constraint can contain NULL values. 

C.A constraint is enforced only for the INSERT operation on a table. 

D.A constraint can be disabled even if the constraint column contains data. 

E.All constraints can be defined at the column level as well as the table level.

Correct Answer: BD

QUESTION 4 Which two statements are true regarding constraints? (Choose two.)

A.A foreign key cannot contain NULL values. 

B.The column with a UNIQUE constraint can store NULLS . 

C.A constraint is enforced only for an INSERT operation on a table.

D.You can have more than one column in a table as part of a primary key.

Correct Answer: BD

QUESTION 5 Evaluate the following CREATE TABLE commands:

CREATE TABLE orders (ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY, ord_date DATE, cust_id NUMBER(4)); CREATE TABLE ord_items (ord_no NUMBER(2), item_no NUMBER(3), qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), expiry_date date CHECK (expiry_date > SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no), CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));

The above command fails when executed. What could be the reason?

A. SYSDATE cannot be used with the CHECK constraint.

B.The BETWEEN clause cannot be used for the CHECK constraint.

C.The CHECK constraint cannot be placed on columns having the DATE data type.

D.ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Correct Answer: A

QUESTION 6 Evaluate the following SQL commands:

SQL>CREATE SEQUENCE ord_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCYCLE;

SQL>CREATE TABLE ord_items (ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL, item_no NUMBER(3), qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), expiry_date date CHECK (expiry_date > SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no), CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));

The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)

A.You cannot use SYSDATE in the condition of a CHECK constraint. 

B.You cannot use the BETWEEN clause in the condition of a CHECK constraint. 

C.You cannot use the NEXTVAL sequence value as a DEFAULT value for a column. 

D.You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.

Correct Answer: AC

QUESTION 7 Examine the structure and data in the PRICE_LIST table:

Name	Null	Type
PROD_ID	NOT NULL	NUMBER(3)
PROD_PRICE		VARCHAR2
Values In Table
PROD_ID	PROD_PRICE
100	$234.55
101	$6,509.75
102	$1,234

You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE. Which SQL statement would give the required result?

A.SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST; 

B.SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;

C.SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; 

D.SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; 

Correct Answer: C

QUESTION 8 View the Exhibit and examine the structure of the PROMOTIONS table. Which two SQL statements would execute successfully? (Choose two.)

Table Promotion
Name	Null	Type
Promo_ID	Not Null	NUMBER(6)
Promo_Name	Not Null	VARCHAR2(30)
Promo_Subcategory	Not Null	VARCHAR2(30)
Promo_Subcategory_Id	Not Null	NUMBER
Promo_Category	Not Null	VARCHAR2(30)
Promo_Category_Id	Not Null	NUMBER
Promo_Cost	Not Null	NUMBER(10,2)
Promo_Begin_Date	Not Null	DATE
Promo_End_Date	Not Null	DATE

A.UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';

B.SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98'; 

C.UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));

D.SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));

Correct Answer: AB

QUESTION 9 View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.

Which two queries give the correct result? (Choose two.)

A.SELECT promo_name, TO_CHAR(promo_end_date,'Day') ', ' TO_CHAR(promo_end_date,'Month') ' ' TO_CHAR(promo_end_date,'DD, YYYY') AS last_day FROM promotions; 

B.SELECT promo_name,TO_CHAR (promo_end_date,'fxDay') ', ' TO_CHAR(promo_end_date,'fxMonth') ' ' TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day FROM promotions; 

C.SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ', ' TRIM(TO_CHAR (promo_end_date,'Month')) ' ' TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day FROM promotions;

D.SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')',' TO_CHAR(promo_end_date,'fmMonth') ' ' TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day FROM promotions;

Correct Answer: CD

QUESTION 10 View the Exhibit and examine the structure of the CUSTOMERS table. Using the CUSTOMERS table, y ou need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message " Not Available" displayed. Which SQL statement would produce the required result?

A.SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers; 

B.SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers; 

C.SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers; 

D.SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

Correct Answer: D

QUESTION 11: Examine the structure of the PROGRAMS table:

name Null Type
------ --------- -------
PROG_ID NOT NULL NUMBER(3)
PROG_COST NUMBER(8,2)
START_DATE NOT NULL DATE
END_DATE DATE

Which two SQL statements would execute successfully? (Choose two.)
A.SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
B.SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
C.SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;
D.SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;

Correct Answer: AD

QUESTION 12 The PRODUCTS table has the following structure:

name Null Type

------ --------- -------

PROD_ID NOT NULL NUMBER(4) 

PROD_NAME VARCHAR2(25) 

PROD_EXPIRY_DATE DATE

Evaluate the following two SQL statements:

SQL>SELECT prod_id, NVL2(prod_expiry_date, prod_expiry_date + 15,'') FROM products;

SQL>SELECT prod_id, NVL(prod_expiry_date, prod_expiry_date + 15) FROM products;

Which statement is true regarding the outcome?

A.Both the statements execute and give different results.

B.Both the statements execute and give the same result.

C.Only the first SQL statement executes successfully.

D.Only the second SQL statement executes successfully.

Correct Answer: A

QUESTION 13 Examine the structure of the INVOICE table.

Name Null Type

------ --------- -------

INV_NO NOT NULL NUMBER(3) 

INV_DATE                DATE 

INV_AMT                  NUMBER(10,2)

Which two SQL statements would execute successfully? (Choose two.)

A.SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice; 

B.SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice; 

C.SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice; 

D.SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice;

Correct Answer: AC

QUESTION 14 View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement:

SQL> SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000)"BONUS" FROM cust_status;

Which statement is true regarding the execution of the above query?

A.It produces an error because the AMT_SPENT column contains a null value. 

B.It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT. 

C.It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null . 

D.It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.

Correct Answer: C

QUESTION 15 Which statement is true regarding the COALESCE function?

A.It can have a maximum of five expressions in a list. 

B.It returns the highest NOT NULL value in the list for all rows. 

C.It requires that all expressions in the list must be of the same data type. 

D.It requires that at least one of the expressions in the list must have a NOT NULL value.

Correct Answer: C

QUESTION 16 View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to find out the average cost for all promos in the ranges $0-2000 and $2000-5000 in category A You issue the following SQL statement:

SQL>SELECT AVG(CASE WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A' then promo_cost ELSE null END) "CAT_2000A", AVG(CASE WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A' THEN promo_cost ELSE null END) "CAT_5000A" FROM promotions;

What would be the outcome?

Table Promotion
Name	Null	Type
Promo_ID	Not Null	NUMBER(6)
Promo_Name	Not Null	VARCHAR2(30)
Promo_Subcategory	Not Null	VARCHAR2(30)
Promo_Subcategory_Id	Not Null	NUMBER
Promo_Category	Not Null	VARCHAR2(30)
Promo_Category_Id	Not Null	NUMBER
Promo_Cost	Not Null	NUMBER(10,2)
Promo_Begin_Date	Not Null	DATE
Promo_End_Date	Not Null	DATE

A.It executes successfully and gives the required result. 

B.It generates an error because NULL cannot be specified as a return value. 

C.It generates an error because CASE cannot be used with group functions. 

D.It generates an error because multiple conditions cannot be specified for the WHEN clause.

Correct Answer: A

QUESTION 17 View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)

Table Promotion
Name	Null	Type
Promo_ID	Not Null	NUMBER(6)
Promo_Name	Not Null	VARCHAR2(30)
Promo_Subcategory	Not Null	VARCHAR2(30)
Promo_Subcategory_Id	Not Null	NUMBER
Promo_Category	Not Null	VARCHAR2(30)
Promo_Category_Id	Not Null	NUMBER
Promo_Cost	Not Null	NUMBER(10,2)
Promo_Begin_Date	Not Null	DATE
Promo_End_Date	Not Null	DATE

A.SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions;

B.SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;

C.SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') "Catcost" FROM promotions;

D.SELECT promo_id, DECODE(promo_cost, >10000, 'High', <10000, 'Low') "Range" FROM promotions;

Correct Answer: AB

QUESTION 18 Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:

PROMO_BEGIN _DATE

-------------------

04-jan-00 

10-jan-00 

15-dec-99 

18-oct-98 

22-aug-99

You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output?

A.SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000", SUM(DECODE(SUBSTR (promo_begin_date,8),'99',1,0)) "1999" FROM promotions;

B.SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", SUM (CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;

C.SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions; 

D.SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1, 0)) "2000" FROM promotions;

Correct Answer: A

QUESTION 19 Examine the structure of the TRANSACTIONS table:

name Null Type

------------- ----------- --------------------

TRANS_ID NOT NULL NUMBER(3) 

CUST_NAME VARCHAR2(30) 

TRANS_DATE TIMESTAMP 

TRANS_AMT NUMBER(10,2)

You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?

A.SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), TO_CHAR(trans_amt,'$99999999D99') FROM transactions WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND COALESCE(trans_amt,NULL)<>NULL; 

B.SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),NVL(TO_CHAR(trans_amt,'$99999999D99'),0) FROM transactions WHERE TO_CHAR(trans_date,'hh24') < 12; 

C.SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12; 

D.SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;

Correct Answer: B

QUESTION 20 Examine the structure of the TRANSACTIONS table:

name Null Type

----------- ----------- --------------------

TRANS_ID NOT NULL NUMBER(3) 

CUST_NAME VARCHAR2(30) 

TRANS_DATE DATE

TRANS_AMT NUMBER(10,2)

You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:

SQL>SELECT TRANS_DATE,CASE WHEN TRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend' ELSE 'weekday' END "Day Type" FROM transactions;

SQL>SELECT TRANS_DATE, CASE WHEN TO_CHAR(trans_date,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday' ELSE 'weekend' END "Day Type"FROM transactions;

Which statement is true regarding the above queries?

A.Both give wrong results. 

B.Both give the correct result. 

C.Only the first query gives the correct result. 

D.Only the second query gives the correct result.

Correct Answer: C

QUESTION 21 View the Exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements: 

Statement 1

SQL>SELECT faculty_name,COUNT(student_id) FROM student JOIN faculty USING (faculty_id, location_id) GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name,COUNT(student_id) FROM student NATURAL JOIN faculty GROUP BY faculty_name;

Which statement is true regarding the outcome?

A.Only statement 1 executes successfully and gives the required result.

B.Only statement 2 executes successfully and gives the required result.

C.Both statements 1 and 2 execute successfully and give different results. 

D.Both statements 1 and 2 execute successfully and give the same required result.

Correct Answer: D

QUESTION 22 Which two statements are true regarding the USING clause in table joins? (Choose two .)

A.It can be used to join a maximum of three tables. 

B.It can be used to restrict the number of columns used in a NATURAL join. 

C.It can be used to access data from tables through equijoins as well as nonequijoins. 

D.It can be used to join tables that have columns with the same name and compatible data types.

Correct Answer: BD

QUESTION 23 Examine the structure of the CUSTOMERS table:

name Null Type

----------- ----------- --------------------

CUSTNO NOT NULL NUMBER(3) 

CUSTNAME NOT NULL VARCHAR2(25) 

CUSTADDRESS VARCHAR2(35) 

CUST_CREDIT_LIMIT NUMBER(5)

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names. Which two methods can you use to get the required result? (Choose two.)

A.self-join 

B.subquery 

C.full outer-join with self-join 

D.left outer-join with self-join 

E.right outer-join with self-join

Correct Answer: AB

QUESTION 24 View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.

You issue the following query:

SQL>SELECT p.prod_id,prod_name,prod_list_price, quantity_sold,cust_last_name FROM products p NATURAL JOIN sales s NATURAL JOIN customers c WHERE prod_id =148;

Which statement is true regarding the outcome of this query?

A.It executes successfully. 

B.It produces an error because the NATURAL join can be used only with two tables. 

C.It produces an error because a column used in the NATURAL join cannot have a qualifier. 

D.It produces an error because all columns used in the NATURAL join should have a qualifier.

Correct Answer: C

QUESTION 25 View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables. You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in 'Tokyo' .

Which two queries give the required result? (Choose two.)

A.SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p USING(prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo'; 

B.SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s JOIN customers c ON(p.prod_id=s.prod_id) ON(s.cust_id=c.cust_id) WHERE c.cust_city='Tokyo'; 

C.SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s ON(p.prod_id=s.prod_id) JOIN customers c ON(s.cust_id=c.cust_id) AND c.cust_city='Tokyo'; 

D.SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN sales s USING(prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo';

Correct Answer: AC

QUESTION 26 View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007. You issue the following query:

SQL> SELECT promo_name,cust_name FROM promotions p JOIN sales s ON(time_id BETWEEN promo_begin_date AND promo_end_date) JOIN customer c ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';

Which statement is true regarding the above query?

A.It executes successfully and gives the required result.

B.It executes successfully but does not give the required result. 

C.It produces an error because the join order of the tables is incorrect. 

D.It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.

Correct Answer: B

QUESTION 27 View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.

The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them. The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks. You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on. Which query would give the required result?

A.SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);

B.SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id); 

C.SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id); 

D.SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);

Correct Answer: B

QUESTION 28 Examine the data in the CUSTOMERS table:

CUSTNO CUSTNAME CITY

--------- --------- ----------

1 KING SEATTLE 

2 GREEN BOSTON 

3 KOCHAR SEATTLE 

4 SMITH NEW YORK

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:

SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname);

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

A.JOIN 

B.NATURAL JOIN 

C.LEFT OUTER JOIN 

D.FULL OUTER JOIN 

E.RIGHT OUTER JOIN

Correct Answer: AE

QUESTION 29 View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables. You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers. Which FROM clause gives the required result?

A.FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id); 

B.FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id); 

C.FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id); 

D.FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);

Correct Answer: C

QUESTION 30 View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.

Table Promotion
Name	Null	Type
Promo_ID	Not Null	NUMBER(6)
Promo_Name	Not Null	VARCHAR2(30)
Promo_Subcategory	Not Null	VARCHAR2(30)
Promo_Subcategory_Id	Not Null	NUMBER
Promo_Category	Not Null	VARCHAR2(30)
Promo_Category_Id	Not Null	NUMBER
Promo_Cost	Not Null	NUMBER(10,2)
Promo_Begin_Date	Not Null	DATE
Promo_End_Date	Not Null	DATE
Table Sales
Name	Null	Type
PROD_ID	Not Null	NUMBER
CUST_ID	Not Null	NUMBER
TIME_ID	Not Null	DATE
CHANNEL_ID	Not Null	NUMBER
PROMO_ID	Not Null	NUMBER
QUANTITY_SOLD	Not Null	NUMBER(10,2)

Evaluate the following SQL statement:

SQL>SELECT p.promo_id, p.promo_name, s.prod_id FROM sales s RIGHT OUTER JOIN promotions p ON (s.promo_id = p.promo_id);

Which statement is true regarding the output of the above query?

A.It gives the details of promos for which there have been sales. 

B.It gives the details of promos for which there have been no sales. 

C.It gives details of all promos irrespective of whether they have resulted in a sale or not. 

D.It gives details of product ID s that have been sold irrespective of whether they had a promo or not.

Correct Answer: C

QUESTION 31 View the Exhibit and examine the data in the EMPLOYEES table:

You want to display all the employee names and their corresponding manager names. Evaluate the following query:

SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME" FROM employees e ______________ employees m ON e.manager_id = m.employee_id;

Which JOIN option can be used in the blank in the above query to get the required output?

A.only inner JOIN 

B.only FULL OUTER JOIN 

C.only LEFT OUTER JOIN 

D.only RIGHT OUTER JOIN

Correct Answer: C

QUESTION 32 View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.

View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.

In PRODUCT table, PDTNO is the primary key. In COMPONENT table, COMPNO is the primary key. In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT table.

You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist. Evaluate the following query:

SQL>SELECT pdtno,pdtname, compno,compname FROM product _____________ pdt_comp USING (pdtno) ____________ component USING(compno) WHERE compname IS NOT NULL;

Which combination of joins used in the blanks in the above query gives the correct output?

A.JOIN; JOIN 

B.FULL OUTER JOIN; FULL OUTER JOIN 

C.RIGHT OUTER JOIN; LEFT OUTER JOIN 

D.LEFT OUTER JOIN; RIGHT OUTER JOIN

Correct Answer: C

QUESTION 33 View the Exhibit and examine the structure of the SALES and PRODUCTS tables. In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table, You want to list each product ID and the number of times it has been sold. Evaluate the following query:

SQL>SELECT p.prod_id, COUNT(s.prod_id) FROM products p _____________ sales s ON p.prod_id = s.prod_id GROUP BY p.prod_id;

Which two JOIN options can be used in the blank in the above query to get the required output?

(Choose two.)

A.JOIN 

B.FULL OUTER JOIN 

C.LEFT OUTER JOIN 

D.RIGHT OUTER JOIN

Correct Answer: BC

QUESTION 34 Which two statements are true regarding subqueries? (Choose two.)

A.A subquery can retrieve zero or more rows. 

B.Only two subqueries can be placed at one level. 

C.A subquery can be used only in SQL query statements. 

D.A subquery can appear on either side of a comparison operator. 

E.There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Correct Answer: AD

QUESTION 35 Where can subqueries be used? (Choose all that apply.)

A.field names in the SELECT statement 

B.the FROM clause in the SELECT statement 

C.the HAVING clause in the SELECT statement 

D.the GROUP BY clause in the SELECT statement 

E.the WHERE clause in only the SELECT statement 

F.the WHERE clause in SELECT as well as all DML statements

Correct Answer: ABCF

QUESTION 36 Which three statements are true regarding subqueries? (Choose three.)

A.Subqueries can contain GROUP BY and ORDER BY clauses. 

B.Main query and subquery can get data from different tables. 

C.Main query and subquery must get data from the same tables. 

D.Subqueries can contain ORDER BY but not the GROUP BY clause. 

E.Only one column or expression can be compared between the main query and subquery.

F.Multiple columns or expressions can be compared between the main query and subquery.

Correct Answer: ABF

QUESTION 37 View the Exhibits and examine PRODUCTS and SALES tables.

View the Exhibits and examine PRODUCTS and SALES tables.

ou issue the following query to display product name and the number of times the product has been sold:

SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p ON i.prod_id = p.prod_id;

What happens when the above statement is executed?

A.The statement executes successfully and produces the required output. 

B.The statement produces an error because ITEM_CNT cannot be displayed in the outer query. 

C.The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together. 

D.The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.

Correct Answer: A

QUESTION 38 View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks would require subqueries? (Choose two.)

A.Display the minimum list price for each product status. 

B.Display all suppliers whose list price is less than 1000. 

C.Display the number of products whose list price is more than the average list price.

D.Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.

E.Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.

Correct Answer: CE

QUESTION 39 Which statement is true regarding subqueries?

A.The LIKE operator cannot be used with single- row subqueries. 

B.The NOT IN operator is equivalent to IS NULL with single- row subqueries. 

C.=ANY and =ALL operators have the same functionality in multiple- row subqueries. 

D.The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

Correct Answer: D

QUESTION 40 Which three statements are true about multiple-row subqueries? (Choose three.)

A.They can contain a subquery within a subquery. 

B.They can return multiple columns as well as rows. 

C.They cannot contain a subquery within a subquery. 

D.They can return only one column but multiple rows. 

E.They can contain group functions and GROUP BY and HAVING clauses. 

F.They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Correct Answer: ABE

QUESTION 41 View the Exhibit and examine the structure of CUSTOMERS and GRADES tables.

You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.)

A.  SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval;

B.  SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval;

C.  SELECT custname, grade FROM customers, grades WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers) AND cust_credit_limit BETWEEN startval AND endval;

D.  SELECT custname, grade

FROM customers , grades WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers) AND MAX(cust_credit_limit) BETWEEN startval AND endval;

Correct Answer: BC

QUESTION 42

Examine the structure of the PRODUCTS table:

name Null Type

--------- --------- ---------- 

PROD_ID NOT NULL NUMBER(4) 

PROD_NAME VARCHAR2(20)

PROD_STATUS VARCHAR2(6)

QTY_IN_HAND NUMBER(8,2) 

UNIT_PRICE NUMBER(10,2)

You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND. Which SQL statement gives the required output?

A.  SELECT prod_name

FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);

B.  SELECT prod_name

FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);    

C.  SELECT prod_name

FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);

D.  SELECT prod_name

FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products) GROUP BY prod_name;

Correct Answer: A

QUESTION 43

View the Exhibit and examine the structure of the PRODUCTS table. Evaluate the following query:

SQL> SELECT prod_name FROM products WHERE prod_id IN (SELECT prod_id FROM products WHERE prod_list_price = (SELECT MAX(prod_list_price)FROM products WHERE prod_list_price < (SELECT MAX(prod_list_price)FROM products)));

What would be the outcome of executing the above SQL statement?

A.  It produces an error. 

B.  It shows the names of all products in the table.

C.  It shows the names of products whose list price is the second highest in the table.

D.  It shows the names of all products whose list price is less than the maximum list price.

Correct Answer: C

QUESTION 44

View the Exhibit and examine the structure of the PROMOTIONS table. You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category. Which query would give you the required output?

Table Promotion
Name	Null	Type
Promo_ID	Not Null	NUMBER(6)
Promo_Name	Not Null	VARCHAR2(30)
Promo_Subcategory	Not Null	VARCHAR2(30)
Promo_Subcategory_Id	Not Null	NUMBER
Promo_Category	Not Null	VARCHAR2(30)
Promo_Category_Id	Not Null	NUMBER
Promo_Cost	Not Null	NUMBER(10,2)
Promo_Begin_Date	Not Null	DATE
Promo_End_Date	Not Null	DATE

A.  SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET';

B.  SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');

C.  SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');

D.  SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');

Correct Answer: C

QUESTION 45

View the Exhibit and examine the structure of the PRODUCTS table. You want to display the category with the maximum number of items. You issue the following query:

SQL>SELECT COUNT(*),prod_category_id FROM products

GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);

What is the outcome?

A.  It executes successfully and gives the correct output. 

B.  It executes successfully but does not give the correct output. 

C.  It generates an error because the subquery does not have a GROUP BY clause. 

D.  It generates an error because = is not valid and should be replaced by the IN operator.

Correct Answer: C

QUESTION 46

View the Exhibit and examine the structure of the CUSTOMERS table. You issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'KING' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'KING':

SQL> SELECT cust_id,cust_last_name FROM customers WHERE country_id IN(SELECT country_id FROM customers WHERE cust_last_name ='King') AND cust_credit_limit < (SELECT MAX(cust_credit_limit) FROM customers WHERE country_id IN(SELECT country_id FROM customers WHERE cust_last_name='King'));

Which statement is true regarding the outcome of the above query? 

A.  It executes and shows the required result. B.  It produces an error and the < operator should be replaced by < ALL to get the required output. C.  It produces an error and the < operator should be replaced by < ANY to get the required output.

D.  It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to

get the required output.

Correct Answer: A

QUESTION 47

Evaluate the following SQL statement:

SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore');

Which statement is true regarding the above query if one of the values generated by the subquery is NULL?

A.  It produces an error. B.  It executes but returns no rows. C.  It generates output for NULL as well as the other values produced by the subquery. D.  It ignores the NULL value and generates output for the other values produced by the subquery.

Correct Answer: C

QUESTION 48

View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

SQL>SELECT promo_name,CASE WHEN promo_cost >=(SELECT AVG(promo_cost) FROM promotions WHERE promo_category='TV')    

then 'HIGH' else 'LOW' END COST_REMARK FROM promotions;

Which statement is true regarding the outcome of the above query?

Table Promotion
Name	Null	Type
Promo_ID	Not Null	NUMBER(6)
Promo_Name	Not Null	VARCHAR2(30)
Promo_Subcategory	Not Null	VARCHAR2(30)
Promo_Subcategory_Id	Not Null	NUMBER
Promo_Category	Not Null	VARCHAR2(30)
Promo_Category_Id	Not Null	NUMBER
Promo_Cost	Not Null	NUMBER(10,2)
Promo_Begin_Date	Not Null	DATE
Promo_End_Date	Not Null	DATE

A.  It shows COST_REMARK for all the promos in the table.

B.  It produces an error because the subquery gives an error. 

C.  It shows COST_REMARK for all the promos in the promo category 'TV'. 

D.  It produces an error because subqueries cannot be used with the CASE expression.

Correct Answer: A

QUESTION 49

View the Exhibit and examine the structure of the PRODUCTS tables. You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category. Which query would give the correct output?

A.  SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);

B.  SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);

C.  SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);

D.  SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);

Correct Answer: A

QUESTION 50

View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. Evaluate the following SQL statement:

SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date- promo_begin_date)));

What would be the outcome of the above SQL statement?

A.  It displays prod IDs in the promo with the lowest cost. `

B.  It displays prod IDs in the promos with the lowest cost in the same time interval. 

C.  It displays prod IDs in the promos with the highest cost in the same time interval. 

D.  It displays prod IDs in the promos with cost less than the highest cost in the same time interval.

Correct Answer: D

QUESTION 51 

View the Exhibit and examine the data in the PROMOTIONS table 

You need to display all promo categories that do not have 'discount' in their subcategory. Which two SQL statements give the required result? (Choose two.)

A.  SELECT promo_category

FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';

B.  SELECT promo_categoryFROM promotions 

INTERSECT 

SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';

C.  SELECT promo_category FROM promotions 

MINUS 

SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount';

D.  SELECT promo_category FROM promotions 

INTERSECT 

SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount';

Correct Answer: AD

QUESTION 52

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

B.  UNION 

C.  INTERSECT 

D.  UNION ALL

Correct Answer: A

QUESTION 53

Which statement is true regarding the UNION operator?

A.  By default, the output is not sorted. 

B.  NULL values are not ignored during duplicate checking. 

C.  Names of all columns must be identical across all SELECT statements. 

D.  The number of columns selected in all SELECT statements need not be the same.

Correct Answer: B

QUESTION 54

View the Exhibits and examine the structures of the PRODUCTS and SALES tables.

Which two SQL statements would give the same output? (Choose two.)

A.  SELECT prod_id FROM products

INTERSECT SELECT prod_id FROM sales;

B.  SELECT prod_id FROM products

MINUS SELECT prod_id FROM sales;

C.  SELECT DISTINCT p.prod_id

FROM products p JOIN sales s ON p.prod_id=s.prod_id;

D.  SELECT DISTINCT p.prod_id

FROM products p JOIN sales s ON p.prod_id <> s.prod_id;

Correct Answer: AC

QUESTION 55

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Evaluate the following SQL statement:

SQL>SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales MINUS SELECT prod_id FROM costs;

Which statement is true regarding the above compound query?

A.  It produces an error.

B.  It shows products that were sold and have a cost recorded. 

C.  It shows products that were sold but have no cost recorded. 

D.  It shows products that have a cost recorded irrespective of sales.

Correct Answer: C

QUESTION 56

Evaluate the following SQL statement:

SQL> SELECT promo_id, promo_category FROM promotions WHERE promo_category = 'Internet' ORDER BY 2 DESC UNION SELECT promo_id, promo_category FROM promotions WHERE promo_category = 'TV' UNION SELECT promo_id, promo_category FROM promotions WHERE promo_category ='Radio';

Which statement is true regarding the outcome of the above query?

A.  It executes successfully and displays rows in the descending order of PROMO_CATEGORY. B.  It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.

C.  It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

D.  It produces an error because the ORDER BY clause should appear only at the end of a compound query- that is, with the last SELECT statement.

Correct Answer: D

QUESTION 57

Evaluate the following SQL statement:

SQL> SELECT cust_id, cust_last_name "Last Name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30;

Which ORDER BY clauses are valid for the above query? (Choose all that apply.)

A.  ORDER BY 2,1 

B.  ORDER BY CUST_NO 

C.  ORDER BY 2,cust_id 

D.  ORDER BY "CUST_NO" 

E.  ORDER BY "Last Name"

Correct Answer: ACE

QUESTION 58

View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables. Evaluate the following SQL command:

SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) 

WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id;

Which two statements are true regarding the outcome of the above query? (Choose two.)

A.  It locks all the rows that satisfy the condition in the statement. 

B.  It locks only the columns that satisfy the condition in both the tables. 

C.  The locks are released only when a COMMIT or ROLLBACK is issued. 

D.  The locks are released after a DML statement is executed on the locked rows.

Correct Answer: AC

QUESTION 59

Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)

A.  It locks only the columns specified in the SELECT list. 

B.  It locks the rows that satisfy the condition in the SELECT statement. 

C.  It can be used only in SELECT statements that are based on a single table. 

D.  It can be used in SELECT statements that are based on a single or multiple tables. 

E.  After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.

Correct Answer: BD

QUESTION 60 

View the Exhibit and examine the structure of the CUSTOMERS table. NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table. Evaluate the following INSERT statement:

INSERT INTO new_customers (cust_id, cust_name, cust_city) VALUES(SELECT

cust_id,cust_first_name||' '||cust_last_name,cust_city FROM customers WHERE cust_id > 23004);

The INSERT statement fails when executed. What could be the reason?

A.  The VALUES clause cannot be used in an INSERT with a subquery. 

B.  Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match. 

C.  The WHERE clause cannot be used in a subquery embedded in an INSERT statement. D.  The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns

in the CUSTOMERS table.

Correct Answer: A

QUESTION 61

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust_last_name column having value Roberts. 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(SELECT o.order_id, o.order_date,o.order_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);

D.  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);

Correct Answer: A

QUESTION 62

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.

SALE_VW is a view created using the following command :

SQL>CREATE VIEW sale_vw AS SELECT prod_id, SUM(quantity_sold) QTY_SOLD FROM sales GROUP BY prod_id;

You issue the following command to add a row to the SALE_SUMMARY table :

SQL>INSERT INTO sale_summary SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products USING (prod_id) WHERE prod_id = 16;

What is the outcome?

A.  It executes successfully. 

B.  It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table. 

C.  It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match. 

D.  It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.

Correct Answer: D

QUESTION 63

View the exhibit and examine the description for the SALES and CHANNELS tables. You issued the following SQL statement to insert a row in the SALES table:

INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500);

Which statement is true regarding the execution of the above statement?

A.  The statement will execute and the new row will be inserted in the SALES table. 

B.  The statement will fail because subquery cannot be used in the VALUES clause. 

C.  The statement will fail because the VALUES clause is not required with subquery. 

D.  The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks .

Correct Answer: A

QUESTION 64

View the Exhibit and examine the description for the CUSTOMERS table. You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

A.  UPDATE customers SET cust_credit_limit = NULL WHERE CUST_INCOME_LEVEL = NULL;

B.  UPDATE customers SET cust_credit_limit = NULL WHERE cust_income_level IS NULL;

C.  UPDATE customers SET cust_credit_limit = TO_NUMBER(NULL) WHERE cust_income_level = TO_NUMBER(NULL);

D.  UPDATE customers SET cust_credit_limit = TO_NUMBER(' ',9999) WHERE cust_income_level IS NULL;

Correct Answer: B

QUESTION 65

View the Exhibit and examine the description for the CUSTOMERS table. 

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566. Which UPDATE statement will accomplish the task?

A.  UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;

B.  UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;

C.  UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;

D.  UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;

Correct Answer: A

QUESTION 66

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. You want to update the EMPLOYEES table as follows:4 ? 4;

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700). -Set department_id for these employees to the department_id corresponding to London (location_id 2100). -Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department. -Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

You issue the following command:

SQL>UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments WHERE departments.location_id IN(2900,2700,2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700)

What is the outcome?

A.  It executes successfully and gives the correct result. 

B.  It executes successfully but does not give the correct result. 

C.  It generates an error because a subquery cannot have a join condition in an UPDATE statement. 

D.  It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.

Correct Answer: B

QUESTION 67

Evaluate the following DELETE statement:

DELETE FROM sales;

There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?

A.  It would not remove the rows if the table has a primary key. 

B.  It removes all the rows as well as the structure of the table. 

C.  It removes all the rows in the table and deleted rows can be rolled back. 

D.  It removes all the rows in the table and deleted rows cannot be rolled back.

Correct Answer: C

QUESTION 68

Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

A.  DELETE can be used to remove only rows from only one table at a time. 

B.  DELETE can be used to remove only rows from multiple tables at a time.

C.  DELETE can be used only on a table that is a parent of a referential integrity constraint. 

D.  DELETE can be used to remove data from specific columns as well as complete rows. 

E.  DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule .

Correct Answer: AE

QUESTION 69

View the Exhibit and examine the structure of CUSTOMERS and SALES tables. Evaluate the following SQL statement:

UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales) SET time_id = '22-MAR-2007' WHERE cust_id = (SELECT cust_id FROM customers

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

Which statement is true regarding the execution of the above UPDATE statement 

A.  It would not execute because two tables cannot be used in a single UPDATE statement. 

B.  It would not execute because the SELECT statement cannot be used in place of the table name. 

C.  It would execute and restrict modifications to only the columns specified in the SELECT statement. 

D.  It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

Correct Answer: C

QUESTION 70

Which three statements/commands would cause a transaction to end? (Choose three.)

A.  COMMIT 

B.  SELECT 

C.  CREATE 

D.  ROLLBACK 

E.  SAVEPOINT

Correct Answer: ACD