MySQL Exercise 2: 10 SQL SELECT Statement with Questions and Answers

MySQL Exercise 2: 10 SQL SELECT Statement with Questions and Answers

This is another 10 SQL questions and answers on Library Database Example. This Excercise number 2 comes with complex questions include INNER JOIN and Sub Query.
លំហាត់ MySQL SQL SELECT Exercise: #1 | #2 | #3

Check the videos explain in Khmer below for more detail.

Download Library Data and How to Use Data Here
This is the EER Diagram (Table Relationship) to help you to understand more.

Learn More Introduction to Database (SQL) in Khmer with videos: Chapter 1 | Chapter 2 | Chapter 3

Below are 10 SQL SELECT Questions and Answers

#1 Show total number of books and set column title as 'Total Book'

SELECT count(*) as 'Total Book' FROM books;

#2 Show total number of books which have 200 and above.

SELECT count(*) as 'Total Book' FROM books
WHERE pagecount >= 200;

#3 Show all student name who borrowed book more than 30 times

SELECT s.name, count(b.studentID) as Counts 
FROM students s INNER JOIN borrows b ON s.studentID = b.studentID
GROUP BY s.name HAVING Counts > 30;

#4 Show total number of books have been borrowed in 2016

SELECT count(*) from borrows WHERE year(takeDate) = 2016;

#5 Show total number of books borrowed each year.

SELECT year(takeDate) as Years, count(*) as Counts 
FROM borrows
GROUP BY Years;

EP10 សំនួរចម្លើយប្រើ SQL SELECT ផ្នែតទី១ លំហាត់ទី២
This video explain from question from 1 to 5.

Free Khmer Ebook Download (PDF): Database | Microsoft Access | Python Programming

#6 Show total number of students who borrowed book

SELECT count(DISTINCT studentID) FROM borrows;

#7 Show all student information who is not borrowed book (using Sub Query)

SELECT * FROM students s WHERE s.studentID NOT IN 
(SELECT studentID FROM borrows);

#8. show total number (count) of books with type as Comics

SELECT count(*) FROM books b INNER JOIN types t
ON b.typeID = t.typeID WHERE t.name = 'comics';

#9. show total number (count) of books with type as Comics (use Sub Query)

SELECT count(*) FROM books WHERE typeID IN 
(SELECT typeID From types WHERE name='comics');

#10 Show type (cateogry) of the book and total count each type

SELECT t.name, count(b.typeID) as Counts
FROM types t INNER JOIN books b ON t.typeID = b.typeID
GROUP BY t.name;

EP11 សំនួរចម្លើយប្រើ SQL SELECT ផ្នែតទី២​ លំហាត់ទី២
This video explain from question from 6 to 10.