Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Aggregate Functions

memorize.ai (lvl 286)

(0)

11 cards

()

Section 1

Preview this deck

Why is this method problematic?

Front

1 / 11

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (11)

Section 1

(11 cards)

Why is this method problematic?

Front

We work with 2 queries which decreases the speed. SELECT title, pages FROM books WHERE pages=(SELECT MAX(pages) FROM books);

Back

Grouping by release year in one column.

Front

mysql> SELECT CONCAT('In ', release_year ,' ', COUNT(*), ' book(s) released.') AS 'info' FROM books GROUP BY release_year; info | +-----------------------------+ | In 1945 1 book(s) released. | | In 1981 1 book(s) released. | | In 1985 1 book(s) released. | | In 1989 1 book(s) released. | | In 1996 1 book(s) released. | | In 2000 1 book(s) released. | | In 2001 3 book(s) released. | | In 2003 2 book(s) released. | | In 2004 1 book(s) released. | | In 2005 1 book(s) released. | | In 2010 1 book(s) released. | | In 2012 1 book(s) released. | | In 2013 1 book(s) released. | | In 2014 1 book(s) released. | | In 2016 1 book(s) released. | | In 2017 1 book(s) released.

Back

Number of pages by author.

Front

SELECT author_lname, author_fname, SUM(pages) FROM books GROUP BY author_lname, author_fname;

Back

SELECT COUNT(DISTINCT author_fname, author_lname) FROM books;

Front

Why is better this way?

Back

A better way?

Front

SELECT MAX(pages),title FROM books ORDER BY pages DESC LIMIT 1;

Back

MIN&MAX

Front

SELECT MAX(released_year) FROM books;

Back

AVG number of pages per release_year

Front

SELECT release_year, AVG(pages) FROM books GROUP BY release_year;

Back

COUNT

Front

SELECT COUNT(*) FROM books;

Back

Group authors by minimum release year.

Front

SELECT author_fname, author_lname, Min(released_year) FROM books GROUP BY author_lname, author_fname;

Back

What to do with duplicates?

Front

SELECT COUNT(DISTINCT author_fname) FROM books;

Back

Count titles with artice the.

Front

SELECT COUNT(*) FROM books WHERE title LIKE '%the%';

Back