Examine the structure of the INVOICEtable.

Which two SQL statements would execute successfully?

• A.SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')
  FROM invoice;
• B.SELECT inv_no, NVL2(inv_amt, inv_amt*.25, '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_date, 'Not Available')
  FROM invoice;

Comment: *

Name: *

Email: *

Verification: *

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 (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1,
  '10-mar-2007', 'direct', &&customer_id, 1000);
• B.INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' AND c.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
• C.INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES (1,
  '10-mar-2007', 'direct', &customer_id, 1000).
• D.INSERT INTO ordersVALUES(1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);

Comment: *

Name: *

Email: *

Verification: *

Which task can be performed by using a single Data Manipulation Language (DML) statement?

• A.adding a column with a default value while inserting a row into a table
• B.removing all data from a single column on which a unique constraint is defined
• C.adding a column constraint while inserting a row into a table
• D.removing all data only from a single column on which a primary key constraint is defined

Comment: *

Name: *

Email: *

Verification: *

Examine the commands used to create DEPARTMENT_DETAILSand COURSE_DETAILS tables:

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?
SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER

• A.JOIN course_details c ON (c.department_id=d. department_id);
• B.department_details d ON (c.department_id=d. department_id);
  SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER
• C.JOIN course_details c ON (d.department_id=c. department_id);
  SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN
• D.JOIN course_details c ON (d.department_id=c. department_id);
  SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER

Comment: *

Name: *

Email: *

Verification: *

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTSand TIMEStables.

The PROD_IDcolumn is the foreign key in the SALEStables, which references the PRODUCTStable.
Similarly, the CUST_IDand TIME_IDcolumns are also foreign keys in the SALEStable referencing the CUSTOMERSand TIMEStables, respectively.
Evaluate the following CREATE TABLEcommand:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?

• A.The NEW_SALEStable would not get created because the DEFAULTvalue cannot be specified in the column definition.
• B.The NEW_SALEStable would not get created because the column names in the CREATETABLE command and the SELECTclause do not match.
• C.The NEW_SALEStable would get created and all the FOREIGNKEYconstraints defined on the specified columns would be passed to the new table.
• D.The NEW_SALEStable would get created and all the NOTNULLconstraints defined on the specified columns would be passed to the new table.

Comment: *

Name: *

Email: *

Verification: *