MySQL practical Tutorials part 5- SQL count function, group by clause, MIN and MAX function

 Below are the practical queries for count function and group by clause in mysql databases. Please refer previous tutorials to get how to create database and tables and fill data into it and then refer below practical queries. when you go step by steps then you will get the flow of tutorials. Thank you.

=======================================================================================================================

CODE: The Count Function

SELECT COUNT(*) FROM books;

 

SELECT COUNT(author_fname) FROM books;

 

SELECT COUNT(DISTINCT author_fname) FROM books;

 

SELECT COUNT(DISTINCT author_lname) FROM books;

 

SELECT COUNT(DISTINCT author_lname, author_fname) FROM books;

 

SELECT title FROM books WHERE title LIKE '%the%';

 

SELECT COUNT(*) FROM books WHERE title LIKE '%the%';

=======================================================================================================================

CODE: The Joys of Group By

SELECT title, author_lname FROM books

GROUP BY author_lname;

 

SELECT author_lname, COUNT(*) 

FROM books GROUP BY author_lname;

 

SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname;

 

SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname, author_fname;

 

SELECT released_year, COUNT(*) FROM books GROUP BY released_year;

 

SELECT CONCAT('In ', released_year, ' ', COUNT(*), ' book(s) released') AS year FROM books GROUP BY released_year;

=======================================================================================================================

CODE: MIN and MAX Basics

SELECT MIN(released_year) FROM books;

 

SELECT MIN(pages) FROM books;

 

SELECT MAX(pages) 

FROM books;

 

SELECT MAX(released_year) 

FROM books;

======================================================================================================================

SELECT * FROM books 

WHERE pages = (SELECT Min(pages) 

                FROM books); 

 

SELECT title, pages FROM books 

WHERE pages = (SELECT Max(pages) 

                FROM books); 

 

SELECT title, pages FROM books 

WHERE pages = (SELECT Min(pages) 

                FROM books); 

 

SELECT * FROM books 

ORDER BY pages ASC LIMIT 1;

 

SELECT title, pages FROM books 

ORDER BY pages ASC LIMIT 1;

 

SELECT * FROM books 

ORDER BY pages DESC LIMIT 1;

====================================================================