1. Examine the structures of the PRODUCTS, SALES AND CUSTOMERS table.

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

Table PRODUCTS
Name Null? Type
PROD_ID NOT NULL NUMBER
PROD_NAME NOT NULL VARCHAR2(20)
PROD_DESC NOT NULL NUMBER
PROD_CATEGORY NOT NULL VARCHAR2(30)
PROD_CATEGORY_ID NOT NULL NUMBER
PROD_UNIT_OF_MEASURE VARCHAR2(20)
SUPPLIER_ID NOT NULL NUMBER(6)
PROD_STATUS NOT NULL VARCHAR2(20)
PROD_LIST_PRICE NOT NULL NUMBER(8,2)
PROD_MIN_PRICE NOT NULL NUMBER(8,2)

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)

Table CUSTOMERS
Name Null? Type
CUST_ID NOT NULL NUMBER A.
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
COUNTRY_ID NOT NULL NUMBER
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
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)
WHERE c.cust_city='Tokyo';

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

2. Which two statements are true regarding the USING and ON clauses in table joins?
(Choose two.)

A. Both USING and ON clause can be used for equijoins and nonequijoins

B. A maximum of one pair of columns can be joined between two tables using the ON

clause

C. The ON clause can be used to join tables on columns that have different names but

compatible data types

D. The WHERE clause can be used to apply additional conditions in SELECT statement

containing the ON or the USING clause

3.

Table PRODUCTS
Name Null? Type
PROD_ID NOT NULL NUMBER
PROD_NAME NOT NULL VARCHAR2(20)
PROD_DESC NOT NULL NUMBER
PROD_CATEGORY NOT NULL VARCHAR2(30)
PROD_CATEGORY_ID NOT NULL NUMBER
PROD_UNIT_OF_MEASURE VARCHAR2(20)
SUPPLIER_ID NOT NULL NUMBER(6)
PROD_STATUS NOT NULL VARCHAR2(20)
PROD_LIST_PRICE NOT NULL NUMBER(8,2)
PROD_MIN_PRICE NOT NULL NUMBER(8,2)

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)

Table CUSTOMERS
Name Null? Type
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
 CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
COUNTRY_ID NOT NULL NUMBER
CUST_INCOME_LEVEL VARCHAR2(30)
You
CUST_CREDIT_LIMIT NUMBER
issue
CUST_EMAIL VARCHAR2(30)
the
following query:

SQL> SELECT p.prod_id, prod_name,prod_list_price,

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

4.

Table CUSTOMERS
Name Null? Type
 CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
COUNTRY_ID NOT NULL NUMBER
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)

Using the CUSTOMERS table, you need to generate a report that shown the average credit

limit for customers in WASHINGTON and NEW YORK.

Which SQL statement would produce the required result?

A.
SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit,cust_city;

B.
SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;

C.
SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;

D.
SELECT cust_city,AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')