MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `books` (
  `id` int(10) UNSIGNED NOT NULL,
  `prid` int(10) UNSIGNED NOT NULL,
  `title` varchar(100) NOT NULL
);

INSERT INTO `books` (`id`, `prid`, `title`) VALUES
(1, 1, 'Physics'), (2, 2, 'Chemistry'), (3, 3, 'Maths');

CREATE TABLE `person` (
  `id` int(10) UNSIGNED NOT NULL,
  `fullname` varchar(100) NOT NULL
);

INSERT INTO `person` (`id`, `fullname`) VALUES
(1, 'Jade'), (2, 'Jack'), (3, 'John');

CREATE TABLE `promotes` (
  `bkid` int(11) UNSIGNED NOT NULL,
  `prid` int(11) UNSIGNED NOT NULL,
  `enroll` tinyint(1) DEFAULT 1,
  `point` tinyint(1) DEFAULT NULL
);

INSERT INTO `promotes` (`bkid`, `prid`, `enroll`, `point`) VALUES
    (1, 1, 1, 2), (2, 1, 1, 3), (2, 2, 1, 1),
    (3, 1, NULL, 0), (3, 2, 0, NULL), (3, 3, 1, NULL);

CREATE TABLE `reviews` (
  `id` int(10) NOT NULL,
  `prid` int(10) UNSIGNED NOT NULL,
  `bkid` int(10) UNSIGNED NOT NULL,
  `review` varchar(500) NOT NULL
);

INSERT INTO `reviews` (`id`, `prid`, `bkid`, `review`) VALUES
(1, 1, 2, 'Nice'), (2, 1, 3, 'Thinking'), (3, 2, 2, 'Try it');

ALTER TABLE `books` ADD PRIMARY KEY (`id`),
ADD KEY `book_prid_fk` (`prid`);

ALTER TABLE `person` ADD PRIMARY KEY (`id`);

ALTER TABLE `promotes` ADD PRIMARY KEY (`bkid`,`prid`),
ADD KEY `promotes_prid_fk` (`prid`);

ALTER TABLE `reviews` ADD PRIMARY KEY (`id`),
ADD KEY `reviews_prid_fk` (`prid`), ADD KEY `reviews_bkid_fk` (`bkid`);

ALTER TABLE `books`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
  
ALTER TABLE `person`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;  
  
ALTER TABLE `reviews`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
  
ALTER TABLE `books`
  ADD CONSTRAINT `book_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);
  
ALTER TABLE `promotes`
  ADD CONSTRAINT `promotes_bkid_fk` FOREIGN KEY (`bkid`) REFERENCES `books` (`id`),
  ADD CONSTRAINT `promotes_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);
  
ALTER TABLE `reviews`
  ADD CONSTRAINT `reviews_bkid_fk` FOREIGN KEY (`bkid`) REFERENCES `books` (`id`),
  ADD CONSTRAINT `reviews_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);

Records: 3  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 1



Records: 3  Duplicates: 0  Warnings: 1



Records: 3  Duplicates: 0  Warnings: 1



Records: 3  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


select * from books;
select * from person;
select * from promotes;
select * from reviews;


id
prid
title


1
1
Physics

2
2
Chemistry

3
3
Maths




id
fullname


1
Jade

2
Jack

3
John




bkid
prid
enroll
point


1
1
1
2

2
1
1
3

2
2
1
1

3
1
null
0

3
2
0
null

3
3
1
null




id
prid
bkid
review


1
1
2
Nice

2
1
3
Thinking

3
2
2
Try it


SELECT b.id, 
       b.prid, 
       b.title,
       p.fullname,
       m.enroll, 
       m.point,
       m2.enrolls, 
       m2.points,
       COUNT(DISTINCT r.id) reviewal
FROM books b
JOIN person p ON p.id = b.prid
LEFT JOIN promotes m ON m.bkid = b.id AND m.prid = p.id
LEFT JOIN  (select bkid,
                   SUM(enroll) enrolls,
                   COUNT(point) points
            from promotes
            group by bkid 
           ) as m2 ON m2.bkid = b.id
LEFT JOIN reviews r ON r.bkid = b.id
GROUP BY b.id 
ORDER BY b.id ASC



id
prid
title
fullname
enroll
point
enrolls
points
reviewal


1
1
Physics
Jade
1
2
1
1
0

2
2
Chemistry
Jack
1
1
2
2
2

3
3
Maths
John
1
null
1
1
1