Section 9 - The Magic of Aggregate Functions Flashcards

1
Q

Give a few examples of aggregate data.

A
  • Finding averages
  • Sum a bunch of different things
  • Grouping things by author, title, genre, sales numbers, class size, etc
  • Average page count per author, etc.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What does the COUNT function do?

A

It does pretty much what you’d expect it to do (it COUNTs things) - at least in its basic form.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What would the following function do?

SELECT COUNT(*) from sports_cars;

A

This would COUNT all the sports_cars in our database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What function counts things?

A

The COUNT function.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What would the following function do?

SELECT COUNT(repair_guides) FROM help_you_fix;

A

This would COUNT all repair_guides (column) from the help_you_fix database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What might be incorrect about the following function?

SELECT COUNT(car_manufacturer) FROM cars;

A

This would count ALL instances of car_manufacturers FROM our cars database.

BUT, what if you had 20, 30, 40+ models from each manufacturer? This COUNT function would could each instance separately, so if you had, say, 20 different Toyota models, it would count Toyota 20 different times.

With this in mind, use the DISTINCT function, as in the following:

SELECT COUNT(DISTINCT car_manufacturer) FROM cars;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Write a function that does the following:

  • Finds the number of unique car_models in a database
  • The database is named all_things_cars
  • Print the total number of unique car models as “This is the total number of cars in the All Things Car database: ____ (
A

SELECT CONCAT(‘This is the total number of cars in the All Things Car database:’, ‘ ‘, COUNT(DISTINCT car_models)) AS ‘Here’s the Result of Our Cars Query’ FROM all_things_car;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What does GROUP BY do?

A

It summarizes (or “aggregates”) identical data into single rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What are a few examples of functions you could run with GROUP BY?

A
  • GROUP BY authors who released a book in 2017
  • GROUP BY classes whose sizes are 30 students
  • Group all of our books by genre
  • Group all of our tea bags by tea type and then sort them by sales numbers
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What do the MIN and MAX functions do?

A

They help identify minimum and maximum values in a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Write a function that finds the oldest (lowest) released_year from a table called mazda_cars.

A

SELECT MIN(released_year) FROM mazda_cars;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Write a function that finds the longest book (book_pages = column name) from a table named borders. Print back both the title and the page count. Use the MAX function to do so.

A

Will the below result give you the longest book title?

SELECT MAX(title) AS ‘This will take you awhile to read’ FROM borders;

NO, it will NOT! It will only give you the last title if the titles were in alphabetical order.

One solution is to run a subquery. You can find this below. (This is the long solution, but you’ll learn the more concise solution later. )

SELECT title, book_pages FROM borders WHERE book_pages = (SELECT MAX(book_pages) FROM borders);

Note that in the above query, you need the FROM statement twice.

Another way to find the same result would be as follows:

SELECT title, book_pages FROM borders ORDER BY book_pages DESC LIMIT 1;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Write the following query:

  • Find the most recently released car_model from each car_make in the the cars table.
  • Print only the most recently released car_model from each car manufacturer/car make. Print car_make, car_model, and car_model_released_year, in that order.
A

SELECT
car_make, car_model,
MIN(released_year) FROM cars
GROUP BY car_make, car_model;

I think the above is accurate.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Give some examples of things you could do with the SUM function.

A
  • SUM the pages all the books in the database.
  • SUM the sales for all yellow yoyos within the database
  • SUM the sales totals for all orders under $100 places between January and February 2019.
  • SUM together the payroll of all operations personal for a month.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Write a query that sums together all the pages in a book_shop database.

A

SELECT SUM(pages) FROM book_shop;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Write a query that does the following:

  • Columns: author_firstname, author_lastname, pages
  • SUM the total number of pages each author has written.
  • Print the author’s first name, last name, and the total pages.
A

SELECT author_firstname, author_lastname, SUM(pages) FROM books GROUP BY author_lastname, author_firstname;

17
Q

What is the function for adding things together?

A

The SUM function

18
Q

What does the SUM function do?

A

It adds things together.

19
Q

What does the AVG function do?

A

It averages things together.

20
Q

What function averages things together?

A

The AVG function.

21
Q

Write a function that calculates the average released_year across our entire book_shop table.

A

SELECT AVG(released_year) FROM book_shop;

22
Q

Write a function that does the following:
Finds and prints the average released_year from each of the authors in a table named book_shop.
The function should print out the author_firstname, the author_lastname, and the released_year.

A

SELECT author_firstname, author_lastname, AVG(released_year) FROM book_shop GROUP BY author_lastname, author_firstname;

23
Q

Write the following function:

  • Print the total number of books released each year from a book_shop table.
  • columns are released_year, title
A

SELECT released_year, COUNT(*) FROM book_shop GROUP BY released_year;

You could also do:

SELECT released_year, COUNT(title) FROM book_shop GROUP BY released_year;

OR perhaps:

SELECT released_year, COUNT(released_year) FROM book_shop GROUP BY released_year;

24
Q

Write the following function:

1) Print the total number of books in stock from a book_shop table.
2) Columns are title, stock_quantity, author_firstname, author_lastname, released_date.

A

Will the following function work?

SELECT COUNT(stock_quantity) FROM book_shop;

NO NO NO! :)
This would just add up the stock_quantity field appears in the table. (ie how many rows the stock_quantity field appears in).

You would need to use the SUM function.

SELECT SUM(stock_quantity) FROM book_shop;

25
Q

Write the following function:

  • You have a table named book_shop
  • Columns include author_firstname, author_lastname, released_year, title, pages, stock_quantity
  • Print the author’s first name, then last name, and the average released year for each of those author’s books
A

SELECT author_firstname, author_lastname, AVG(released_year) FROM books GROUP BY author_lastname, author_firstname;

26
Q

Write the following function. Write it 2 different ways:

  • You have a table named book_shop
  • Columns include author_firstname, author_lastname, released_year, title, pages, stock_quantity
  • Print the full name of the author who wrote the longest book.
A

SELECT CONCAT(author_fname, ‘ ‘, author_lname) FROM books ORDER BY pages DESC LIMIT 1;

OR, use the MAX function:

SELECT CONCAT(author_firstname, ‘ ‘, author_lastname) FROM book_shop WHERE pages = (SELECT MAX(pages) FROM book_shop);