Scribd
Upload
123 views

Introduction To Databricks SQL Answer Guide

This document provides summaries of 6 labs covering topics like working with tables and views in Databricks SQL, ingesting data, basic SQL, data visualizations and dashboards, notifying stakeholders, and a final lab assignment. It includes questions and answers for each lab to test understanding.

Uploaded by

A Noraznizam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
123 views

Introduction To Databricks SQL Answer Guide

This document provides summaries of 6 labs covering topics like working with tables and views in Databricks SQL, ingesting data, basic SQL, data visualizations and dashboards, notifying stakeholders, and a final lab assignment. It includes questions and answers for each lab to test understanding.

Uploaded by

A Noraznizam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

Introduction to DB SQL

Answer Guide

DAWD 01-5 - Lab: Tables and Views on Databricks SQL

DAWD 02-3 - Lab: Ingesting Data

DAWD 02-6 - Lab: Basic SQL

DAWD 03-3 - Lab: Data Visualizations and Dashboards

DAWD 03-5 - Lab: Notifying Stakeholders

DAWD 03-6 - Lab: Final Lab Assignment


DAWD 01-5 - Lab: Tables and Views on Databricks SQL
Question/Answer 1
USE replace_with_your_schema_name;
DROP TABLE IF EXISTS sales_external;
CREATE EXTERNAL TABLE sales_external USING DELTA
LOCATION 'wasbs://courseware@dbacademy.blob.core.windows.net/data-analysis-with-databricks/v01/sales/';
SELECT * FROM sales_external ORDER BY customer_id;

What is the value of customer_id in the first row?


12096776

Question/Answer 2 - REQUIRES RENAMING THE TABLE


USE replace_with_your_schema_name;
CREATE TABLE sales_renamed USING DELTA AS
SELECT * FROM sales_external;
DESCRIBE EXTENDED sales_renamed;

What is the value of Owner?


root

Question/Answer 3
Under History, what was the operation associated with Version 0 of the table?
CREATE OR REPLACE TABLE AS SELECT is HTML right answer
CREATE TABLE AS SELECT is incorrectly shown as the correct answer

Question/Answer 4
Did the 'DESCRIBE' command fail? (yes or no)
YES

DAWD 02-3 - Lab: Ingesting Data


Question/Answer 1
How many rows are in the customers table? (type only numbers)
28813

Question/Answer 2
USE replace_with_your_schema_name;
CREATE OR REPLACE TABLE sales_stream (5minutes STRING,
clicked_items ARRAY<ARRAY<STRING>>,
customer_id STRING,
customer_name STRING,
datetime STRING,
hour BIGINT,
minute BIGINT,
number_of_line_items STRING,
order_datetime STRING,
order_number BIGINT,
ordered_products ARRAY<ARRAY<STRING>>,
sales_person DOUBLE,
ship_to_address STRING
);
COPY INTO sales_stream
FROM
'wasb://courseware@dbacademy.blob.core.windows.net/data-analysis-with-databricks/v01/sales_stream.json'
FILEFORMAT = JSON;
SELECT * FROM sales_stream ORDER BY customer_id;

What is the value of customer_id in the first row?


10060379

Question/Answer 3
How many privileges does the group "users" have on sales_stream?
1

Question/Answer 4
How many privileges have you been granted to you on your schema?
6

DAWD 02-6 - Lab: Basic SQL


Question/Answer 1
USE replace_with_your_schema_name;
SELECT loyalty_segment, count(loyalty_segment) AS Count
FROM customers
GROUP BY loyalty_segment
ORDER BY loyalty_segment;

How many customers are in loyalty_segment 0?


11097

Question/Answer 2
USE replace_with_your_schema_name;
SELECT initcap(customer_name) AS Customer_Name
FROM customers
ORDER BY customer_name DESC;

What is the last name of the last customer (alphabetically ) in the table? Ensure answer is in lower case
zyskowski

Question/Answer 3
USE replace_with_your_schema_name;
UPDATE customers SET customer_name = initcap(customer_name);
SELECT * FROM customers;

In which city is Bittner Engineering, Inc. located?


RANDOLPH

Question/Answer 4
USE replace_with_your_schema_name;
INSERT INTO loyalty_segments
(loyalty_segment_id, loyalty_segment_description, unit_threshold, valid_from, valid_to)
VALUES
(5, 'level_5', 130, current_date(), Null);
SELECT * FROM loyalty_segments;

How many rows are in the table now?


5
If you did the optional lab on Basic SQL, the correct answer is 6

Question/Answer 5
USE replace_with_your_schema_name;
CREATE OR REPLACE TABLE high_value_CA_customers AS
SELECT *
FROM customers
WHERE state = 'CA'
AND loyalty_segment = 3;
SELECT * FROM high_value_CA_customers;

How many rows are in the view?


949

Question/Answer 6
USE replace_with_your_schema_name;
SELECT customers.state, sum(total_price) AS Total FROM customers
JOIN sales
ON customers.customer_id = sales.customer_id
GROUP BY customers.state
ORDER BY Total DESC;

Which state has the highest sales?


OR

Question/Answer 7
USE replace_with_your_schema_name;
SELECT min(total_price)
FROM sales;

What is the lowest price? (numbers only)


18

DAWD 03-3 - Lab: Data Visualizations and Dashboards


Question/Answer 1
Which product category has the highest upper fence?
Opple

Question/Answer 2 !!!NEED TO INCLUDE THE % SIGN AS PART OF ANSWER!!!


What is the %Max for Orders?
6.77%

Question/Answer 3
Which product category has the lowest minimum price?
Rony

DAWD 03-5 - Lab: Notifying Stakeholders


Question/Answer 1
What is the shortest amount of time we can configure for query refreshes?
1 minute

Question/Answer 2
What is the total amount of income (in whole dollars) currently in the sales_stream table? (type only numbers)
249003 or 265759

Question/Answer 3
After sharing the dashboard with the "All Users" group, how many rows of users and groups are in the "Manage
permissions" dialog?
2

Question/Answer 4
When you click "Advanced" under the "Refresh" drop down, how many minutes are selected by default? (enter only
numbers)
10

DAWD 03-6 - Lab: Final Lab Assignment


Question/Answer 1
USE replace_with_your_schema_name;
DROP TABLE IF EXISTS sales_orders_json;
CREATE TABLE sales_orders_json
USING JSON
OPTIONS (
path 'wasb://courseware@dbacademy.blob.core.windows.net/data-analysis-with-databricks/v01/sales_orders',
header "true",
inferSchema "true"
);
DROP TABLE IF EXISTS sales_orders;
CREATE TABLE sales_orders AS
SELECT * FROM sales_orders_json;
DROP TABLE IF EXISTS sales_orders_json;
SELECT * FROM sales_orders;

How many rows are in the sales_orders table? (type only numbers)
4074

Question/Answer 2
USE replace_with_your_schema_name;
WITH order_info AS
(SELECT customer_id, explode(ordered_products) AS product_info FROM sales_orders),
total_sales AS
(SELECT customer_id, sum(product_info["price"] * product_info["qty"]) AS total_spent FROM order_info GROUP
BY customer_id)
SELECT customer_name, total_spent FROM total_sales
JOIN customers
ON customers.customer_id = total_sales.customer_id
WHERE total_spent > 175000
ORDER BY total_spent DESC;

How much has Bradsworth Digital Solutions, Inc spent?


209533

Question/Answer 3
USE replace_with_your_schema_name;
WITH sold_products AS
(SELECT *, explode(ordered_products) as products FROM sales_orders
JOIN customers
ON customers.customer_id = sales_orders.customer_id
JOIN loyalty_segments
ON customers.loyalty_segment = loyalty_segments.loyalty_segment_id)
SELECT loyalty_segment_description, sum(products['price'] * products['qty']) AS sales FROM sold_products
GROUP BY loyalty_segment_description;

What is the sales amount for customers in loyalty segment 3?


9910450

Question/Answer 4
What is the name of the right-most tab in the Bar visualization editor?
Data labels

Question/Answer 5
Will this Alert trigger even though there is no refresh schedule configured for the query itself?
Yes

Question/Answer 6
If you wanted to configure a set interval to refresh this dashboard automatically, what button would you click?
Schedule

You might also like