Skip to main content

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;

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

Comments

Popular posts from this blog

Add, remove, search an item in listview in C#

Below is the C# code which will help you to add, remove and search operations on listview control in C#. Below is the design view of the project: Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace Treeview_control_demo {     public partial class Form2 : Form     {         public Form2()         {             InitializeComponent();             listView1.View = View.Details;                   }         private void button1_Click(object sender, EventArgs e)         {             if (textBox1.Text.Trim().Length == 0)...

Add worklog in Jira using Python

 Below is the Python code to add the worklog in Jira. You need to install a request library for this. Here is the code: import requests from requests.auth import HTTPBasicAuth import json url = "https://your jira address here/rest/api/2/issue/ticket_number/worklog" auth = HTTPBasicAuth("username", "jira access token") headers = {     "Accept": "application/json",     "Content-Type": "application/json" } payload = json.dumps({     "comment": {         "content": [             {                 "content": [                     {                         "text": "This is for QA Testing",                         "type": "text"                     } ...

Some GUI examples in Python using customtkinter

 Some GUI examples in Python using customtkinter import customtkinter import os from PIL import Image class ScrollableCheckBoxFrame(customtkinter.CTkScrollableFrame):     def __init__(self, master, item_list, command=None, **kwargs):         super().__init__(master, **kwargs)         self.command = command         self.checkbox_list = []         for i, item in enumerate(item_list):             self.add_item(item)     def add_item(self, item):         checkbox = customtkinter.CTkCheckBox(self, text=item)         if self.command is not None:             checkbox.configure(command=self.command)         checkbox.grid(row=len(self.checkbox_list), column=0, pady=(0, 10))         self.checkbox_list.append(checkbox)     def remove_item(self, it...