Examine the description of the PROMOTIONS table: You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.)
A.
SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;
B.
SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
C.
SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
D.
SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
E.
SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
One could understand the task specification as asking to list only those categories for which the value of promo_cost was unique. That is, if several rows of promo_id, promo_category had the same promo_cost, this row was not listed. Such a task specification is noticeably more complex, it is not in the offered answers, and the corresponding SELECT would look like this:
SELECT promo_category, promo_cost
FROM (
SELECT promo_id, promo_name, promo_category, promo_cost,
COUNT(*) OVER (PARTITION BY promo_category, promo_cost) as cost_count
FROM promotions
)
WHERE cost_count = 1;
A - false; the query returns all rows
B - false; using of second DISTINCT returns an error ORA-00936 missing expression
C - true; returns category name and promo_cost, where each promo_cost for given cathegory is returned only once
D - false; using of DISTINCT returns an error ORA-00936 missing expression
E - true; same answer as C
The correct answers are D and E
A is incorrect because it does not show the unique promotion costs for each promotion category
B is incorrect because it is concatenating varchar and numeric data types without doing data conversion
C is incorrect because it is concatenating varchar and numeric data types without doing data conversion
D provides the correct data
And by discarding the previous ones that are wrong, you can give the solution
A. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1; -- FALSE: No distinct
B. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1; -- FALSE: Distinct just once, next to SELECT
C. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1; -- TRUE
D. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2; -- FALSE: Distinct just once, next to SELECT
E. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; -- TRUE
A is wrong. No DISTINCT clause used.
B is wrong. DISTINCT can be used only once in a select clause at the beginning.
C is correct.
D is wrong. DISTINCT can be used only once in a select clause at the beginning.
E is correct.
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
you1234
Highly Voted 3 years, 6 months agoEkos
3 years, 1 month agoSharif1
Highly Voted 3 years, 5 months agonautil2
Most Recent 2 months, 4 weeks agonautil2
2 months, 3 weeks agonautil2
2 months, 4 weeks agoOracle2020
4 months, 2 weeks agoauwia
6 months, 1 week agoacr23cd
10 months, 1 week agoFranky_T
1 year, 8 months agocasfdsaf
1 year, 10 months agoGuhborges
1 year, 11 months agoryuah
2 years ago