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’;
why A is correct? why C is not correct?
it seem that the ans has changed.
good job!
Why d is wrong
D is wrong because of ORA-25154: column part of USING clause cannot have qualifier
Meaning you cannot use c.cust_id in the select clause instead it should just be cust_id without the qualifier “c”.
In C shouldn’t be WHERE used instead of AND?
Also column “c.cust_id” in “D” was not requested
I am an idiot
In C shouldn’t be WHERE used instead of AND?
A and D are same
A and D are same but D is not the requirement according to the question.
B) is not good because it has a wrong expression JOIN JOIN JOIN then the columns columns column, but it should be this format:
SELECT bc.firstname,
bc.lastname,
b.title,
TO_CHAR(bo.orderdate, ‘MM/DD/YYYY’) “Order Date”,
p.publishername
FROM BOOK_CUSTOMER bc
INNER JOIN books b
ON b.BOOK_ID = bc.BOOK_ID
INNER JOIN book_order bo
ON bo.BOOK_ID = b.BOOK_ID
INNER JOIN publisher p
ON p.PUBLISHER_ID = b.PUBLISHER_ID
WHERE p.publishername = ‘PRINTING IS US’;