MySQL - Aggregate Functions 중간문제

Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

149강 중간문제.

use book_shop;
select database();
-- 1 Print the number of books in the database;
SELECT COUNT(*)
FROM books;

-- 2 Print out how many books were released in each year
SELECT released_year,COUNT(*)
FROM books
GROUP BY released_year
ORDER BY released_year;

-- 3 Print out the total number of books in stock
SELECT SUM(stock_quantity)
FROM books;

-- 4 Find the average released_year for each author
SELECT author_fname,author_lname,AVG(released_year)
FROM books
GROUP BY author_fname, author_lname;


select * from books;
-- 5 Find the Full name of the author who wrote the longest book
SELECT 
	CONCAT(author_fname, ' ',author_lname),
    pages
FROM books
ORDER BY pages DESC
LIMIT 1;

SELECT
	CONCAT(author_fname, ' ', author_lname),
    pages
FROM books
WHERE pages = (SELECT MAX(pages) FROM books);

-- 6 
SELECT released_year AS 'year', COUNT(*) AS 'books', AVG(pages) as 'avg pages'
FROM books
GROUP BY released_year
ORDER BY released_year;