Summarizing data

Question 1

What are the aggregate functions in sql?

Answer 1

MAX, MIN, AVG, SUM, COUNT

Question 2

What is this query returning?

SELECT MAX(invoice\_total)
FROM invoices

Answer 2

the largest invoice total on the invoices table

Question 3

What is this sql query returning?

Answer 3

the max, min, and avg values as highest, lowest, average of the invoice total on the invoices table

Question 4

True or False

Aggregate functions can be placed on non-numeric columns as well?

Answer 4

True like a string date

Question 5

In this query, payment date is referring to what?

Answer 5

the latest payment date

Question 6

In this query the SUM line is returning what?

Answer 6

summing the invoice_total for the column

Question 7

In this query the COUNT line is returning what?

Answer 7

the total number of invoices on the table

Question 8

True or False

Aggregate functions will operate on NULL values?

Answer 8

False

NULL values will be ignored

Question 9

COUNT(*) returns what?

Answer 9

ALL records, NULL or not

Question 10

What is the order of operations on this line of code?

SUM(invoice_total * 1.1) AS total

Answer 10

parenthesis first

each row of invoice_total will be multiplied by 1.1 then SUM will be applied.

Question 11

True or False

Because of the WHERE clause each of the agg functions will be calculated on every row on the invoices table

Answer 11

False

Only those matching the WHERE clause, filtering the number of records being returned

Question 12

If you want to COUNT unique values (like cleint_id) what statement must also be used?

Answer 12

DISTINCT

Question 13

Query this result

Answer 13

same SELECT statments with three UNION statements altering the date range in the BETWEEN clause

Question 14

How many rows are being returned by these multiple SELECT, UNION statements?

Answer 14

3 rows are being queried here.

Question 15

What is the issue with this query?

Answer 15

the total_sales haven’t been calculated before GROUP BY, use the HAVING statement

Question 16

How many select and union statements will return this table?

Answer 16

3 SELECT statements

joined by 2 UNION statements

SELECT
‘First half of 2019’ AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expected
FROM invoices
WHERE invoice_date
BETWEEN ‘2019-01-01’ AND ‘2019-06-30’ – first half or year
UNION…(cont)

Question 17

True or False

In sql the HAVING statement must follow the GROUP BY statement

Answer 17

True

Question 18

In this query what is the HAVING statement accomplishing?

SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500

Answer 18

It is singling out the rows greater than 500 of the total_sales column

Question 19

True or False

WHERE clause filters data before the rows are grouped?

WHERE
GROUP BY
HAVING

Answer 19

True

Question 20

True or False

HAVING clause filters data after the rows are grouped?

WHERE
GROUP BY
HAVING

Answer 20

True

Question 21

True or False

Using the HAVING clause requires that the column is also present in the SELECT clause

Answer 21

True

Question 22

Use the sql.store database to complete this query

• • Get the customers
• • located in Virginia
• • who have spent more than $100

Answer 22

USE sql_store;
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(oi.quantity * oi.unit_price) AS total_price
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE state =’VA’
GROUP BY
c.customer_id,
c.first_name,
c.last_name
HAVING total_price > 100

Question 23

In this sql query, what is the WITH ROLLUP clause performing

SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP

Answer 23

It’s summing up the total_sales column

Question 24

True or False

The WITH ROLLUP clause only applies to aggregated values not something like a customer_id

Answer 24

True

notice client_id is NULL

Question 25

In this query the WITH ROLLUP clause is not only summing the total_sales but is also summing total_sales based on?

SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP

Answer 25

city and state

Question 26

Exercise

write a query to produce this report

Answer 26

SELECT
pm.name AS payment_method,
SUM(amount) AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP