MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.23


CREATE TABLE author(
id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth DATE,
preferred_royalties DECIMAL(9,2));

INSERT INTO author VALUES
(1, 'joe','james','1975-08-21',15),
(2, 'jane','scully','1980-01-01',17),
(3, 'dimitriy','volkov','1961-12-07',21);

CREATE TABLE book(
id INT,
NAME VARCHAR(50),
date_published DATE,
msrp DECIMAL(9,2),
copies_sold INT,
author_id INT,
publisher_id INT);

INSERT INTO book VALUES
(1, 'MySQL Book 1','2001-01-19','400','100',1,1),
(2, 'MariaDB vs MySQL','2005-12-03','500','200',2,2),
(3, 'The art of Kungfu','1991-07-29','600','999',3,3),
(4, 'The art of Judo','2007-09-27','405','150',3,3),
(5, 'The art of Capoeira','2011-08-09','785','90',3,3);

CREATE TABLE publisher(
id INT, 
NAME VARCHAR(50),
standard_royalties DECIMAL(9,2));

INSERT INTO publisher VALUES
(1, 'Book Publisher inc',17),
(2, 'The Book co',15),
(3, 'Martial Books',20);
select * from author;
select * from book;
select * from publisher;


id
first_name
last_name
date_of_birth
preferred_royalties


1
joe
james
1975-08-21
15.00

2
jane
scully
1980-01-01
17.00

3
dimitriy
volkov
1961-12-07
21.00




id
NAME
date_published
msrp
copies_sold
author_id
publisher_id


1
MySQL Book 1
2001-01-19
400.00
100
1
1

2
MariaDB vs MySQL
2005-12-03
500.00
200
2
2

3
The art of Kungfu
1991-07-29
600.00
999
3
3

4
The art of Judo
2007-09-27
405.00
150
3
3

5
The art of Capoeira
2011-08-09
785.00
90
3
3




id
NAME
standard_royalties


1
Book Publisher inc
17.00

2
The Book co
15.00

3
Martial Books
20.00


SELECT * FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id


id
first_name
last_name
date_of_birth
preferred_royalties
id
NAME
date_published
msrp
copies_sold
author_id
publisher_id
id
NAME
standard_royalties


1
joe
james
1975-08-21
15.00
1
MySQL Book 1
2001-01-19
400.00
100
1
1
1
Book Publisher inc
17.00

2
jane
scully
1980-01-01
17.00
2
MariaDB vs MySQL
2005-12-03
500.00
200
2
2
2
The Book co
15.00

3
dimitriy
volkov
1961-12-07
21.00
5
The art of Capoeira
2011-08-09
785.00
90
3
3
3
Martial Books
20.00

3
dimitriy
volkov
1961-12-07
21.00
4
The art of Judo
2007-09-27
405.00
150
3
3
3
Martial Books
20.00

3
dimitriy
volkov
1961-12-07
21.00
3
The art of Kungfu
1991-07-29
600.00
999
3
3
3
Martial Books
20.00


SELECT first_name, last_name,
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       copies_sold ,
       YEAR(CURDATE())-YEAR(date_published) Years
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id


first_name
last_name
EarningByBook
copies_sold
Years


joe
james
1700.00
100
20

jane
scully
3400.00
200
16

dimitriy
volkov
1890.00
90
10

dimitriy
volkov
3150.00
150
14

dimitriy
volkov
20979.00
999
30


SELECT first_name, last_name, 
       SUM(EarningByBook) AS 'Lifetime Earning', 
       SUM(copies_sold) AS 'Copies Sold',
       SUM(years) as 'Total Years',
       SUM(EarningByBook)/SUM(Years) AS 'AVG earning per year'
FROM
(SELECT first_name, last_name,
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       copies_sold ,
       YEAR(CURDATE())-YEAR(date_published) Years
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id) V
GROUP BY 
 first_name, last_name;


first_name
last_name
Lifetime Earning
Copies Sold
Total Years
AVG earning per year


joe
james
1700.00
100
20
85.000000

jane
scully
3400.00
200
16
212.500000

dimitriy
volkov
26019.00
1239
54
481.833333


SELECT name, standard_royalties 
FROM publisher P


name
standard_royalties


Book Publisher inc
17.00

The Book co
15.00

Martial Books
20.00


SELECT author.first_name,
author.last_name,
book.name, MIN(book.date_published) AS 'MostRecentBook'
FROM author
INNER JOIN book ON book.author_id = author.id 
Group By author.first_name, author.last_name, book.name


first_name
last_name
name
MostRecentBook


joe
james
MySQL Book 1
2001-01-19

jane
scully
MariaDB vs MySQL
2005-12-03

dimitriy
volkov
The art of Kungfu
1991-07-29

dimitriy
volkov
The art of Judo
2007-09-27

dimitriy
volkov
The art of Capoeira
2011-08-09


SELECT author.first_name,
author.last_name,
book.name, book.date_published
FROM author
JOIN book ON book.author_id = author.id
WHERE book.date_published = (SELECT min(date_published) FROM book)



first_name
last_name
name
date_published


dimitriy
volkov
The art of Kungfu
1991-07-29


SELECT *,
CASE WHEN date_published BETWEEN '1990-01-01' AND '1999-12-31' THEN '1990'
WHEN date_published BETWEEN '2000-01-01' AND '2009-12-31' THEN '2000'
WHEN date_published BETWEEN '2010-01-01' AND '2019-12-31' THEN '2010'
ELSE 'Void'
END AS 'Decade', copies_sold
FROM book


id
NAME
date_published
msrp
copies_sold
author_id
publisher_id
Decade
copies_sold


1
MySQL Book 1
2001-01-19
400.00
100
1
1
2000
100

2
MariaDB vs MySQL
2005-12-03
500.00
200
2
2
2000
200

3
The art of Kungfu
1991-07-29
600.00
999
3
3
1990
999

4
The art of Judo
2007-09-27
405.00
150
3
3
2000
150

5
The art of Capoeira
2011-08-09
785.00
90
3
3
2010
90


SELECT 
SUM('Decade') AS 'Decade Range',
SUM(sold books) AS 'Books Sold',
SUM(msrp*sold books) AS 'Total Sales',
SUM(msrp/sold books) AS 'Avg MSRP'

FROM
(SELECT *,
CASE WHEN date_published BETWEEN '1990-01-01' AND '1999-12-31' THEN '1990'
WHEN date_published BETWEEN '2000-01-01' AND '2009-12-31' THEN '2000'
WHEN date_published BETWEEN '2010-01-01' AND '2019-12-31' THEN '2010'
ELSE 'Void'
END AS 'Decade', copies_sold as 'sold books'
FROM book) b


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'books) AS 'Books Sold',
SUM(msrp*sold books) AS 'Total Sales',
SUM(msrp/sold boo' at line 3

SELECT
    10*FLOOR(YEAR(date_published)/10) AS Decade,
    SUM(copies_sold) AS 'Copies Sold',
    SUM(msrp*copies_sold) AS 'Total Sales',
    SUM(msrp/copies_sold) AS 'Avg MSRP'
FROM book
WHERE date_published > '1989-12-31'
GROUP BY 1


Decade
Copies Sold
Total Sales
Avg MSRP


2000
450
200750.00
9.200000

1990
999
599400.00
0.600601

2010
90
70650.00
8.722222


SELECT 'Decade'
SUM(copies_sold) AS 'Books Sold',
SUM(msrp*copies_sold) AS 'Total Sales',
SUM(msrp/copies_sold) AS 'Avg MSRP'

FROM
(SELECT *,
CASE WHEN date_published BETWEEN '1990-01-01' AND '1999-12-31' THEN '1990'
WHEN date_published BETWEEN '2000-01-01' AND '2009-12-31' THEN '2000'
WHEN date_published BETWEEN '2010-01-01' AND '2019-12-31' THEN '2010'
ELSE 'Void'
END AS 'Decade' , copies sold as 'Sales'
FROM book) V

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(copies_sold) AS 'Books Sold',
SUM(msrp*copies_sold) AS 'Total Sales',
SUM(ms' at line 2

SELECT first_name, last_name,
From author
INNER JOIN book
ON author.id = book.author_id)
(SELECT Datediff(book.date_published,author.date_of_birth)/8766 AS 'age'
FROM author
INNER JOIN book
ON author.id = book.author_id)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From author
INNER JOIN book
ON author.id = book.author_id)
(SELECT Datediff(book' at line 2

Select first_name, last_name, date_of_birth, 
TIMESTAMPDIFF(YEAR, date_of_birth,date_published) AS age
FROM author
INNER JOIN book
ON author.id = book.author_id
LIMIT 1


first_name
last_name
date_of_birth
age


joe
james
1975-08-21
25


Select
SUM(book.msrp) AS 'Total Revenue',
SUM(EarningByBook) AS 'Total Payouts',
SUM(book.msrp)-SUM(EarningByBook) AS 'Net Profit'
FROM
(SELECT 
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       copies_sold , 
       YEAR(CURDATE())-YEAR(date_published) Years
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id 

(SELECT publisher.name, book.msrp 
From Publisher 
INNER JOIN book ON book.publisher_id)) Z
GROUP BY 
 name;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT publisher.name, book.msrp 
From Publisher 
INNER JOIN book ON book.publis' at line 17

Select name,
SUM(book.msrp) AS 'Total Revenue',
SUM(EarningByBook) AS 'Total Payouts',
SUM(book.msrp)-SUM(EarningByBook) AS 'Net Profit'
FROM
(SELECT 
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       copies_sold , 
       YEAR(CURDATE())-YEAR(date_published) Years
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id 

SELECT publisher.name, book.msrp 
From publisher 
INNER JOIN book ON publisher.id = book.publisher_id)) Z
GROUP BY 
 name;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT publisher.name, book.msrp 
From publisher 
INNER JOIN book ON publisher.i' at line 17

SELECT publisher.name, book.msrp 
From publisher 
INNER JOIN book ON publisher.id = book.publisher_id


name
msrp


Book Publisher inc
400.00

The Book co
500.00

Martial Books
600.00

Martial Books
405.00

Martial Books
785.00


Select name,
SUM(book.msrp) AS 'Total Revenue',
SUM(EarningByBook) AS 'Total Payouts',
SUM(book.msrp)-SUM(EarningByBook) AS 'Net Profit'
FROM
(SELECT publisher.name, book.msrp,
(SELECT 
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       copies_sold , 
       YEAR(CURDATE())-YEAR(date_published) Years
INNER JOIN book ON publisher.id = book.publisher_id)
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id)
From publisher
INNER JOIN book ON publisher.id = book.publisher_id) Z
GROUP BY 
 name;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN book ON publisher.id = book.publisher_id)
FROM author A 
JOIN book B ' at line 14

Select name,
SUM(book.msrp) AS 'Total Revenue',
SUM(EarningByBook) AS 'Total Payouts',
SUM(book.msrp)-SUM(EarningByBook) AS 'Net Profit'
FROM
(SELECT 
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       copies_sold , 
       YEAR(CURDATE())-YEAR(date_published) Years
       (SELECT publisher.name, book.msrp 
From publisher 
INNER JOIN book ON publisher.id = book.publisher_id)
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id ) Z
GROUP BY 
 name;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT publisher.name, book.msrp 
From publisher 
INNER JOIN book ON publisher.' at line 13

show warnings;


Level
Code
Message


Error
1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT publisher.name, book.msrp 
From publisher 
INNER JOIN book ON publisher.' at line 13
id	first_name	last_name	date_of_birth	preferred_royalties
1	joe	james	1975-08-21	15.00
2	jane	scully	1980-01-01	17.00
3	dimitriy	volkov	1961-12-07	21.00
id	NAME	date_published	msrp	copies_sold	author_id	publisher_id
1	MySQL Book 1	2001-01-19	400.00	100	1	1
2	MariaDB vs MySQL	2005-12-03	500.00	200	2	2
3	The art of Kungfu	1991-07-29	600.00	999	3	3
4	The art of Judo	2007-09-27	405.00	150	3	3
5	The art of Capoeira	2011-08-09	785.00	90	3	3
id	NAME	standard_royalties
1	Book Publisher inc	17.00
2	The Book co	15.00
3	Martial Books	20.00
first_name	last_name	EarningByBook	copies_sold	Years
joe	james	1700.00	100	20
jane	scully	3400.00	200	16
dimitriy	volkov	1890.00	90	10
dimitriy	volkov	3150.00	150	14
dimitriy	volkov	20979.00	999	30
first_name	last_name	Lifetime Earning	Copies Sold	Total Years	AVG earning per year
joe	james	1700.00	100	20	85.000000
jane	scully	3400.00	200	16	212.500000
dimitriy	volkov	26019.00	1239	54	481.833333
Decade	Copies Sold	Total Sales	Avg MSRP
2000	450	200750.00	9.200000
1990	999	599400.00	0.600601
2010	90	70650.00	8.722222