how to do a few queries MYSQL

this is my homework, i am searching. total 17 question.I couldn’t make 5 questions but i am trying, trying..

1. list the number of books each student reads. But next to those who do not read the book write 0.
2. Find the most read book.
3. List students who have never barrow a book.
4. List the book number, name and the number of times the books are borrowed in ascending order according to the book numbers. 5 List the book number of the received books, how many times the book has been borrowed (write zero “0” next to not borrowed books).

1)select s.stnname, s.stnsurname, sum(b.bookname) from student s LEFT JOIN process p on s.stnno=p.stnno LEFT JOIN book b on b.bookno=p.bookno GROUP BY s.stnname,s.stnsurname; FALSE

Answer

Consider the following. I’ve deliberately simplified the design, which has the effect of prohibiting the possibility that a student may borrow the same book twice…

DROP TABLE IF EXISTS student_book;

CREATE TABLE student_book
(student_id INT NOT NULL
,book_id INT NOT NULL
,PRIMARY KEY(student_id,book_id)
);

DROP TABLE IF EXISTS student;

CREATE TABLE student
(student_id SERIAL NOT NULL PRIMARY KEY
,student_name VARCHAR(30) NOT NULL
);

DROP TABLE IF EXISTS book;

CREATE TABLE book
(book_id SERIAL NOT NULL PRIMARY KEY
,book_title VARCHAR(30) NOT NULL
);

INSERT INTO student VALUES 
(1,'John'),
(2,'Paul'),
(3,'George'),
(4,'Ringo');

INSERT INTO book VALUES
(101,'In Search of Lost Time'),
(102,'Don Quixote'),
(103,'Ulysses'),
(104,'The Great Gatsby');

INSERT INTO student_book VALUES 
(1,101),
(1,102),
(2,102),
(3,101),
(3,102),
(3,103);


1. 

   SELECT s.*
        , COUNT(sb.book_id) total 
     FROM student s 
     LEFT 
     JOIN student_book sb 
       ON sb.student_id = s.student_id 
    GROUP 
       BY s.student_id;
+------------+--------------+-------+
| student_id | student_name | total |
+------------+--------------+-------+
|          1 | John         |     2 |
|          2 | Paul         |     1 |
|          3 | George       |     3 |
|          4 | Ringo        |     0 |
+------------+--------------+-------+

2. -- This solution ignores the possibility of ties

SELECT b.* 
  FROM book b 
  JOIN student_book sb 
    ON sb.book_id = b.book_id 
 GROUP 
    BY book_id 
 ORDER 
    BY COUNT(*) DESC 
 LIMIT 1;
+---------+-------------+
| book_id | book_title  |
+---------+-------------+
|     102 | Don Quixote |
+---------+-------------+

3. 

SELECT s.* 
  FROM student s 
  LEFT 
  JOIN student_book sb 
    ON sb.student_id = s.student_id 
 WHERE sb.book_id IS NULL;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|          4 | Ringo        |
+------------+--------------+

4. 

SELECT b.*
     , COUNT(sb.student_id) times_borrowed -- actually 'borrowed by how many distinct students!!
  FROM book b 
  LEFT 
  JOIN student_book sb  
    ON sb.book_id = b.book_id 
 GROUP 
    BY b.book_id 
 ORDER 
    BY b.book_id;
+---------+------------------------+----------------+
| book_id | book_title             | times_borrowed |
+---------+------------------------+----------------+
|     101 | In Search of Lost Time |              2 |
|     102 | Don Quixote            |              3 |
|     103 | Ulysses                |              1 |
|     104 | The Great Gatsby       |              0 |
+---------+------------------------+----------------+