See 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 range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

• A.It generates an error because multiple conditions cannot be specified for the WHEN clause.
• B.It executes successfully and gives the required result.
• C.It generates an error because CASE cannot be used with group functions.
• D.It generates an error because NULL cannot be specified as a return value.

Comment: *

Name: *

Email: *

Verification: *

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
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_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
• B.The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
• C.The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
• D.The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

Comment: *

Name: *

Email: *

Verification: *

Evaluate the following SELECTstatement 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, 'c'indicates a check constraint.
• B.The STATUScolumn indicates whether the table is currently in use.
• C.The column DELETE_RULEdecides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
• D.The R_CONSTRAINT_NAMEcolumn gives the alternative name for the constraint.

Comment: *

Name: *

Email: *

Verification: *

Examine this description of the PRODUCTS table:
You successfully execute this command:
CREATE TALE new_prices(prod_id NUBER(2),price NUMBER(8,2));
Which two statements execute without errors?

• A.MERGE INTO new_prices n
  USING (SELECT * FROM products WHERE cost>150) p
  ON (n.prod_id=p.prod_id)
  WHEN NATCHED THEN
  UPDATE SET n.price=p.cost*.01
  DELETE WHERE (p.cost<200);
• B.MERGE INTO new_prices n
  USING(SELECT*FROM product WHERE cost>150) p
  ON (n.prod_id=p.prod_id)
  WHEN NATCHED THEN
  DELETE WHERE(p.cost<200)
  WHEN NOT MATCHED THEN
  INSERT (n.prod_id,n.price)VALUES (p.prod_id,p.cost*.01);
• C.MERGE INTO new_prices n
  USING(SELECT*FROM products)p
  WHEN MATECHED THEN
  UPDATE SET n.price=p.cost*.01
  WHEN NOT MATCHED THEN
  INSERT(n.prod_id,n.price)VALUES (p.prod_id,cost*01)
  WHERE(p.cost<200);
• D.MERGE INTO new_prices n
  USING products p
  WHEN NOT NATCHED THEN
  INSERT (n.prod_id, n.price)VALUES (p.prod_id,cost*.01)
  WHERE (p.cost <200);

Comment: *

Name: *

Email: *

Verification: *

View the Exhibit and examine the structure of the CUSTOMERStable.

You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
UPPER (cust_last_name) LIKE 'B%' OR UPPER (cust_last_name) LIKE 'C%')
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN 'A' AND 'C'
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?

• A.Only the second query gives the correct result
• B.Both execute successfully and give the same result
• C.Only the first query gives the correct result
• D.Both execute successfully but do not give the required result

Comment: *

Name: *

Email: *

Verification: *