Cloudera Exam CCA175 Topic 5 Question 6 Discussion

Actual exam question for Cloudera's CCA175 exam

Question #: 6
Topic #: 5

[All CCA175 Questions]

Problem Scenario 85 : In Continuation of previous question, please accomplish following activities.

1. Select all the columns from product table with output header as below. productID AS ID

code AS Code name AS Description price AS 'Unit Price'

2. Select code and name both separated by ' -' and header name should be Product Description'.

3. Select all distinct prices.

4. Select distinct price and name combination.

5. Select all price data sorted by both code and productID combination.

6. count number of products.

7. Count number of products for each code.

ASolution :
Step 1 : Select all the columns from product table with output header as below. productID AS ID code AS Code name AS Description price AS 'Unit Price'
val results = sqlContext.sql(......SELECT productID AS ID, code AS Code, name AS Description, price AS Unit Price' FROM products ORDER BY ID'''
results.show()
Step 2 : Select code and name both separated by ' -' and header name should be 'Product Description.
val results = sqlContext.sql(......SELECT CONCAT(code,' -', name) AS Product Description, price FROM products''' )
results.showQ
Step 3 : Select all distinct prices.
val results = sqlContext.sql(......SELECT DISTINCT price AS Distinct Price' FROM products......)
results.show()
Step 4 : Select distinct price and name combination.
val results = sqlContext.sql(......SELECT DISTINCT price, name FROM products''' )
results. showQ
Step 5 : Select all price data sorted by both code and productID combination.
val results = sqlContext.sql('.....SELECT' FROM products ORDER BY code, productID'.....)
results.show()
Step 6 : count number of products.
val results = sqlContext.sql(......SELECT COUNT(') AS 'Count' FROM products......)
results.show()
Step 7 : Count number of products for each code.
val results = sqlContext.sql(......SELECT code, COUNT('} FROM products GROUP BY code......)
results. showQ
val results = sqlContext.sql(......SELECT code, COUNT('} AS count FROM products GROUP BY code ORDER BY count DESC......)
results. showQ

BSolution :
Step 1 : Select all the columns from product table with output header as below. productID AS ID code AS Code name AS Description price AS 'Unit Price'
val results = sqlContext.sql(......SELECT productID AS ID, code AS Code, name AS Description, price AS Unit Price' FROM products ORDER BY ID'''
results.show()
Step 2 : Select code and name both separated by ' -' and header name should be 'Product Description.
val results = sqlContext.sql(......SELECT CONCAT(code,' -', name) AS Product Description, price FROM products''' )
results.showQ
Step 3 : Select all distinct prices.
val results = sqlContext.sql(......SELECT DISTINCT price AS Distinct Price' FROM products......)
results.show()
Step 4 : count number of products.
val results = sqlContext.sql(......SELECT COUNT(') AS 'Count' FROM products......)
results.show()
Step 5 : Count number of products for each code.
val results = sqlContext.sql(......SELECT code, COUNT('} FROM products GROUP BY code......)
results. showQ
val results = sqlContext.sql(......SELECT code, COUNT('} AS count FROM products GROUP BY code ORDER BY count DESC......)
results. showQ

Show Suggested Answer

Suggested Answer: A

by Jose at May 06, 2022, 08:28 PM

Limited Time Offer

25%

Off

Get Premium CCA175 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Currently there are no comments in this discussion, be the first to comment!