Introduction to SQL

SQL Basics

Nonprocedural Nature

SQL is a nonprocedural language, meaning users specify what data to retrieve, not how to retrieve it.
Users describe the desired result, and the underlying Database Management System (DBMS) handles the retrieval process.

SQL Usage

SQL itself does not function as a standalone database management system (DBMS).
It acts as a medium for communication between users and the DBMS.
SQL is utilized in two primary ways: Interactive SQL and Programmatic SQL.
Interactive SQL involves typing SQL commands directly into a command line interface, with immediate execution and results display.
Programmatic SQL involves embedding SQL statements within host languages like COBOL, FORTRAN, or C.

Features of SQL

SQL serves as a powerful data access language that allows users to manipulate and manage database content.
It's based on relational tuple calculus, which provides a foundation for working with structured data.
SQL is considered a standard relational database management language used across various database systems.
The first commercial DBMS to support SQL was Oracle in 1979, marking a significant milestone in database technology.
SQL's nonprocedural or declarative nature emphasizes describing what you want, leaving the details to the DBMS.

Working with SQL

Choose a Database Management System (DBMS):

A DBMS is software that helps you manage databases. There are various options available, such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database. Choose the one that best fits your needs.

Install the DBMS:

Download and install the chosen DBMS on your computer or server. Follow the installation instructions provided by the DBMS.

Access the DBMS:

Depending on the DBMS, you might access it through a command-line interface (CLI) or a graphical user interface (GUI). For example, MySQL can be accessed through the MySQL Command Line Client or various GUI tools like phpMyAdmin.

MySQL

I'm choosing MySQL as my preferred Database Management System (DBMS).
MySQL is a popular and robust open-source relational DBMS that provides powerful tools for managing and manipulating structured data.
With MySQL, I'll be able to create and maintain databases, store data in organized tables, perform efficient data queries, and manage data relationships. Its versatility and widespread adoption make it an excellent choice for a wide range of applications, from small projects to large-scale systems.

SQL vs MySQL

SQL (Structured Query Language) is a language used for managing and querying databases. It serves as a medium for communication between users and various Database Management Systems (DBMS), such as MySQL, PostgreSQL, SQLite, and more. MySQL, on the other hand, is a specific implementation of a DBMS that uses SQL as its query language. It's important to differentiate between the language (SQL) and the specific system (MySQL) when discussing database-related topics.

Inside MySQL shell or GUI, we are going to start using commands.

MySQL Data types

Understanding CHAR and VARCHAR in Databases

CHAR: Fixed-Length Text

CHAR is like a fixed-size box: When you use CHAR, you tell the database that you want to store a certain number of characters, no matter how long the actual text is. It's like getting a box that can hold exactly 10 letters. So, if you put the word "apple" in it, it will take up 10 spaces, and 6 of them will be empty.

Example: If you store "apple" in a CHAR(10) column, it will be stored as "apple " (6 empty spaces).
VARCHAR: Variable-Length Text

VARCHAR is more flexible: With VARCHAR, the database will only use as much space as needed for the text you want to store. It's like getting a stretchy box that adjusts its size to fit what you put inside.

Example: If you store "apple" in a VARCHAR(10) column, it will be stored as "apple" (no empty spaces).

Now, why is VARCHAR generally better:

Efficiency: VARCHAR is usually more efficient because it doesn't waste memory by adding empty spaces. It only uses as much space as necessary to store your data.
Flexibility: VARCHAR is flexible, so if you need to store different-length words or phrases, it can adapt without any wasted space.

So, for most situations, especially when you're not sure how long your text might be, VARCHAR is the preferred choice because it's efficient and flexible. It helps your database use resources wisely and keeps your data storage optimized.

Learning MySQL commands

We are going to learn according to the following sructure.

MySQL Databases

Create a Database

Once you're in the MySQL command-line interface, you can create a new database using the CREATE DATABASE command. Replace mydatabase with the name you want to give to your database:

                    
CREATE DATABASE mydatabase;

You can use "IF NOT EXIST" clause or condition while creating your database as this is used to create a new database with the specified name (db-name) if it doesn't already exist. This statement is commonly used to ensure that a database is created only if it hasn't been created before, preventing any potential errors or conflicts that might arise from attempting to create a database that already exists. ↓

                    
CREATE DATABASE IF NOT EXISTS db-name;

Listing Databases

Following command shows all the existing databases in your system ↓

                    
SHOW DATABASES;

Selecting Database

After creating the database, you need to switch to it to perform operations within it. Use the USE command:

                    
USE mydatabase;

Deleting Databases

We can also delete a database which is done by command "DROP DATABASE".
The DROP DATABASE statement is used to drop an existing SQL database.

                    
DROP DATABASE databasename;

From here we will learn all the other operations like create table,...

MySQL Tables

Creating Tables

We use the following syntax to create a table ↓

                    
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
    PRIMARY KEY (one_or_more_columns)
);

Using this syntax to create EMPLOYEE table. Make sure you are inside a database.

                
CREATE TABLE EMPLOYEE (
    Empno VARCHAR(6) PRIMARY KEY,
    Ename VARCHAR(20) NOT NULL,
    Job CHAR(10),
    Hiredate DATE,
    Basic_Sal DECIMAL(9,2),
    Comm DECIMAL(7,2),
    Dept_no VARCHAR(4)
);

Primary Key: Uniquely identifies each row in a table.
NOT NULL: Requires a column to have a value (cannot be empty or NULL).

Viewing Table Structure

To view the structure of a table in MySQL, you can use the DESCRIBE statement or the SHOW COLUMNS statement. Here's how to do it:

Using DESCRIBE:

                    
mysql> DESCRIBE EMPLOYEE;


+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Empno     | varchar(6)   | NO   | PRI | NULL    |       |
| Ename     | varchar(20)  | NO   |     | NULL    |       |
| Job       | char(10)     | YES  |     | NULL    |       |
| Hiredate  | date         | YES  |     | NULL    |       |
| Basic_Sal | decimal(9,2) | YES  |     | NULL    |       |
| Comm      | decimal(7,2) | YES  |     | NULL    |       |
| Dept_no   | varchar(4)   | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
7 rows in set (0.06 sec)

Using SHOW COLUMNS:

                    
mysql> SHOW COLUMNS FROM EMPLOYEE;


+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Empno     | varchar(6)   | NO   | PRI | NULL    |       |
| Ename     | varchar(20)  | NO   |     | NULL    |       |
| Job       | char(10)     | YES  |     | NULL    |       |
| Hiredate  | date         | YES  |     | NULL    |       |
| Basic_Sal | decimal(9,2) | YES  |     | NULL    |       |
| Comm      | decimal(7,2) | YES  |     | NULL    |       |
| Dept_no   | varchar(4)   | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

Altering Tables

Modifying table after creation.

Adding Columns

Use the ALTER TABLE statement to add a new column to an existing table.

Example:

                                
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

Dropping Columns

You can remove a column from a table using the ALTER TABLE statement.

Example:

                                
ALTER TABLE employees
DROP COLUMN email;

Modifying Data Types

Change the data type of an existing column with the ALTER TABLE statement.

Example:

                                
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10, 2);

Renaming Columns

You can rename a column using the ALTER TABLE statement.

Example:

                                
ALTER TABLE employees
CHANGE COLUMN old_column_name new_column_name VARCHAR(50);

Adding Constraints - Primary Key

To add a primary key to an existing table, you can use the ALTER TABLE statement with the ADD PRIMARY KEY clause.

Syntax:

                        
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Example:

                        
-- Add a primary key to the "Students" table for the "student_id" column
ALTER TABLE Students
ADD PRIMARY KEY (student_id);

Adding Constrainsts - Foreign Key

To add a foreign key to an existing table, you can use the ALTER TABLE statement with the ADD FOREIGN KEY clause.
It is good practice for the data type of both the foreign key and the primary key in another table to be the same or compatible.

Syntax for Adding a Foreign Key:

                        
ALTER TABLE table_name
ADD CONSTRAINT foreign_key_name
FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column);

Example:

                        
-- Add a foreign key constraint to the "CourseEnrollments" table linking it to the "Students" table
ALTER TABLE CourseEnrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id) REFERENCES Students(student_id);

Adding Constraints - NOT NULL

When it comes to enforcing the NOT NULL constraint in a table, the SQL syntax differs from that of primary keys or foreign keys. To apply the NOT NULL constraint, you use the ALTER TABLE statement with the MODIFY clause.

                        
ALTER TABLE EMPLOYEE
MODIFY Job char(10) NOT NULL;

Remove a Primary Key Constraint:

                        
ALTER TABLE your_table_name 
DROP PRIMARY KEY;

Remove a Foreign Key Constraint:

                        
ALTER TABLE your_table_name 
DROP FOREIGN KEY your_fk_constraint_name;

Deleting Tables

Use the DROP TABLE statement to delete an existing table and all its data.

Example:

                            
DROP TABLE employees;

Inserting Data

Now we know how table is created, now we will enter data into it.

Inserting Single Rows

Use the INSERT INTO statement to add a single row of data into a table.

Example:

                            
INSERT INTO employees (id, name, salary)
VALUES (1, 'John', 50000);

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

                    
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Inserting Multiple Rows

You can insert multiple rows of data at once using the INSERT INTO statement.

Example:

                            
INSERT INTO employees (id, name, salary)
VALUES
    (2, 'Alice', 55000),
    (3, 'Bob', 60000),
    (4, 'Eva', 48000);

Example ↓

                    
INSERT INTO EMPLOYEE (Empno, Ename, Job, Hiredate, Basic_Sal, Comm, Dept_no)
VALUES
    ('E0001', 'Kim', 'Manager', '2002-12-15', 5000, 500, 'D001'),
    ('E0002', 'Bruce', 'Analyst', '1999-04-24', 4000, 400, 'D002'),
    ('E0003', 'Amold', 'Clerk', '2001-01-10', 2500, 250, 'D004'),
    ('E0004', 'Holvfield', 'Tester', '2001-10-10', 3000, 300, 'D002'),
    ('E0005', 'Kelly', 'Admin', '1999-04-11', 2000, 200, 'D003');

Inserting Data into Specific Columns

Specify the columns you want to insert data into when using the INSERT INTO statement.

Example:

                            
INSERT INTO employees (name, salary)
VALUES ('Jane', 52000);

Querying Data

The SELECT Statement

The SELECT statement is used to retrieve data from a database table.

Syntax:

Use the following syntax to select specific columns:

                        
SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, etc., represent the names of the fields (columns) you want to retrieve from the table.

If you want to select all the fields available in the table, you can use the following syntax:

                        
SELECT * FROM table_name;

Example:

Suppose you have a table called "Customers." To retrieve the customer names and cities from this table, you can use the following SQL query:

                        
SELECT CustomerName, City
FROM Customers;

WHERE Clause

The WHERE clause is used to filter records based on specific conditions.

Syntax:

                        
SELECT column1, column2, ...
FROM table_name WHERE condition;

Here, condition represents the filtering condition you want to apply.

Operators Used in WHERE Clause

The WHERE clause can use various operators for conditions:
=: Equal
>: Greater than
<: Less than
>=: Greater than or equal
<=: Less than or equal
<> or !=: Not equal (Note: In some versions of SQL, the not equal operator may be written as !=).

Let's use Operators for the following table.

To retrieve employees with a salary of $50,000, you can use the following SQL query:

                        
SELECT * FROM Employees WHERE Salary = 50000;

Example 2: Greater Than Operator (>)

To retrieve employees with a salary greater than $55,000, you can use the following SQL query:

                        
SELECT * FROM Employees WHERE Salary > 55000;

Example 3: Less Than Operator (<)

To retrieve employees with a salary less than $60,000, you can use the following SQL query:

                        
SELECT * FROM Employees WHERE Salary < 60000;

Example 4: Greater Than or Equal Operator (>=)

To retrieve employees with a salary greater than or equal to $55,000, you can use the following SQL query:

                        
SELECT * FROM Employees WHERE Salary >= 55000;

Example 5: Less Than or Equal Operator (<=)

To retrieve employees with a salary less than or equal to $55,000, you can use the following SQL query:

                        
SELECT * FROM Employees WHERE Salary <= 55000;

Example 6: Not Equal Operator (<> or !=)

To retrieve employees with a salary not equal to $60,000, you can use either of the following SQL queries:

                        
SELECT * FROM Employees WHERE Salary <> 60000;

                        
SELECT * FROM Employees WHERE Salary != 60000;

Using AND, OR, and NOT Operators

The WHERE clause can be combined with logical operators to create complex conditions:
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax:

                        
SELECT column1, column2, ...
FROM table_name WHERE condition1 AND condition2 AND condition3 ...;

                        
SELECT column1, column2, ...
FROM table_name WHERE condition1 OR condition2 OR condition3 ...;

                        
SELECT column1, column2, ...
FROM table_name WHERE NOT condition;

Use parentheses to control the order of conditions in complex queries.

Examples:

Example 1: Selecting customers from Mexico:

                            
SELECT * FROM Customers WHERE Country='Mexico';

Example 2: Selecting customers from India and Japan:

                            
SELECT * FROM Customers WHERE Country='India' AND City='Japan';

Example 3: Selecting customers from America, India, or Korea:

                                
SELECT * FROM Customers WHERE Country='America' AND (City='India' OR City='Korea');

DISTINCT Keyword

The DISTINCT keyword is used to remove duplicate rows from query results.
Syntax:

                        
SELECT DISTINCT column1, column2 FROM table_name;

Here, column1, column2, etc., represent the columns from which you want to retrieve distinct values.

Example:

Suppose we have a table called "Orders" with the following data:

                    
+---------+-------------+---------+
| OrderID | CustomerName| Product |
+---------+-------------+---------+
| 1       | John Doe    | Widget  |
| 2       | Jane Smith  | Gadget  |
| 3       | John Doe    | Widget  |
+---------+-------------+---------+

To retrieve a list of distinct products ordered, you can use the following SQL query:

                    
SELECT DISTINCT Product FROM Orders;

The result will only include unique product names:

                    
+---------+
| Product |
+---------+
| Widget  |
| Gadget  |
+---------+

LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:

The percent sign (%) represents zero, one, or multiple characters.
The underscore sign (_) represents one, single character.

Examples:

Suppose we have a table called "Employees" with the following data:

                    
+----+-----------+--------------+
| ID | Firstname | Lastname     |
+----+-----------+--------------+
| 1  | John      | Doe          |
| 2  | Jane      | Smith        |
| 3  | James     | Johnson      |
| 4  | Alex      | Anderson     |
| 5  | Robert    | Robinson     |
+----+-----------+--------------+

To retrieve employees whose first names start with "J," you can use the following SQL query:

                    
SELECT * FROM Employees WHERE Firstname LIKE 'J%';

This query will return employees with first names starting with "J":

                    
+----+-----------+--------------+
| ID | Firstname | Lastname     |
+----+-----------+--------------+
| 1  | John      | Doe          |
| 2  | Jane      | Smith        |
| 3  | James     | Johnson      |
+----+-----------+--------------+

To retrieve employees whose last names end with "son," you can use the following SQL query:

                    
SELECT * FROM Employees WHERE Lastname LIKE '%son';

This query will return employees with last names ending with "son":

                    
+----+-----------+--------------+
| ID | Firstname | Lastname     |
+----+-----------+--------------+
| 1  | John      | Doe          |
| 3  | James     | Johnson      |
| 5  | Robert    | Robinson     |
+----+-----------+--------------+

You can also use the underscore (_) wildcard to match single characters:

                    
SELECT * FROM Employees WHERE Firstname LIKE '_a%';

This query will return employees with first names that have "a" as the second character:

                    
+----+-----------+--------------+
| ID | Firstname | Lastname     |
+----+-----------+--------------+
| 4  | Alex      | Anderson     |
| 5  | Robert    | Robinson     |
+----+-----------+--------------+

These are just a few examples of how the LIKE operator can be used to search for patterns in data.

IN Operator

The IN operator is used in a WHERE clause to filter results based on a list of values.
Syntax:

                        
SELECT * FROM table_name WHERE column_name IN (value1, value2, value3, ...);

Here, column_name is the column you want to filter, and value1, value2, etc., are the values to match.

Example:

Suppose we have a table called "Products" with the following data:

                    
+----+----------+-------------+
| ID | Product  | Category_ID |
+----+----------+-------------+
| 1  | Laptop   | 1           |
| 2  | Phone    | 2           |
| 3  | Tablet   | 1           |
| 4  | Monitor  | 3           |
| 5  | Printer  | 4           |
+----+----------+-------------+

To retrieve products from categories 1 and 3, you can use the following SQL query:

                    
SELECT * FROM Products WHERE Category_ID IN (1, 3);

The result will include products from categories 1 and 3:

                    
+----+---------+-------------+
| ID | Product | Category_ID |
+----+---------+-------------+
| 1  | Laptop  | 1           |
| 3  | Tablet  | 1           |
| 4  | Monitor | 3           |
+----+---------+-------------+

This allows you to filter results based on a list of specific values in a column.

BETWEEN Operator

The BETWEEN operator is used in a WHERE clause to filter results within a specified range.
Syntax:

                        
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

Here, column_name is the column you want to filter, and value1 and value2 define the range.

Example:

Suppose we have a table called "Orders" with the following data:

                    
+----+------------+------------+
| ID | Order_Date | Order_Amt  |
+----+------------+------------+
| 1  | 2023-02-15 | 500.00     |
| 2  | 2023-04-10 | 750.00     |
| 3  | 2023-05-20 | 300.00     |
| 4  | 2023-03-05 | 900.00     |
| 5  | 2023-06-25 | 600.00     |
+----+------------+------------+

To retrieve orders placed between January 1, 2023, and June 30, 2023, you can use the following SQL query:

                    
SELECT * FROM Orders WHERE Order_Date BETWEEN '2023-01-01' AND '2023-06-30';

The result will include orders placed within the specified date range:

                    
+----+------------+------------+
| ID | Order_Date | Order_Amt  |
+----+------------+------------+
| 1  | 2023-02-15 | 500.00     |
| 2  | 2023-04-10 | 750.00     |
| 3  | 2023-05-20 | 300.00     |
| 5  | 2023-06-25 | 600.00     |
+----+------------+------------+

This allows you to filter results within a specified range of values in a column.

IS NULL Operator

The IS NULL operator is used in a WHERE clause to check for NULL values in a column.
Syntax:

                        
SELECT * FROM table_name WHERE column_name IS NULL;

Here, column_name is the column you want to check for NULL values.

Example:

Suppose we have a table called "Customers" with the following data:

                    
+----+-----------+--------------------+
| ID | FirstName | Email              |
+----+-----------+--------------------+
| 1  | John      | john@example.com   |
| 2  | Jane      | NULL               |
| 3  | James     | james@example.com  |
| 4  | Alex      | NULL               |
| 5  | Robert    | robert@example.com |
+----+-----------+--------------------+

To retrieve customers with NULL email addresses, you can use the following SQL query:

                    
SELECT * FROM Customers WHERE Email IS NULL;

The result will include customers with NULL email addresses:

                    
+----+-----------+--------------+
| ID | FirstName | Email        |
+----+-----------+--------------+
| 2  | Jane      | NULL         |
| 4  | Alex      | NULL         |
+----+-----------+--------------+

This allows you to filter rows based on whether a specific column contains NULL values.

AS Keyword

The AS keyword is used in a SELECT statement to rename columns or expressions in query results.
Syntax:

                        
SELECT column_name AS new_name FROM table_name;

Here, column_name is the original column name, and new_name is the name you want to assign to it in the query results.

Example:

Suppose we have a table called "Employees" with the following data:

                    
+----+-----------+------------+
| ID | FirstName | LastName   |
+----+-----------+------------+
| 1  | John      | Doe        |
| 2  | Jane      | Smith      |
| 3  | James     | Johnson    |
| 4  | Alex      | Anderson   |
| 5  | Robert    | Robinson   |
+----+-----------+------------+

To retrieve the first name and last name of employees with renamed columns "First Name" and "Last Name," you can use the following SQL query:

                    
SELECT FirstName AS "First Name", LastName AS "Last Name" FROM Employees;

The result will include the specified columns with the new names:

                    
+-----------+------------+
| First Name| Last Name  |
+-----------+------------+
| John      | Doe        |
| Jane      | Smith      |
| James     | Johnson    |
| Alex      | Anderson   |
| Robert    | Robinson   |
+-----------+------------+

This allows you to make query results more readable and descriptive by assigning custom names to columns or expressions.

ORDER BY Clause

The ORDER BY clause allows you to sort the result set of a query based on one or more columns.
Basic Syntax:

                        
SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

By default, it sorts in ascending order (smallest to largest).

Ascending and Descending Order:

By default, the ORDER BY clause sorts in ascending order (smallest to largest).
You can explicitly specify descending order using the DESC keyword.
Example:

                        
SELECT product_name, price FROM products ORDER BY price DESC;

Sorting by Multiple Columns:

You can sort by multiple columns by listing them sequentially in the ORDER BY clause.
Rows are first sorted based on the first column, and for rows with equal values, subsequent columns are used for further sorting.
Example:

                        
SELECT first_name, last_name FROM employees ORDER BY last_name, first_name;

Sorting by Expressions:

It's possible to sort by calculated expressions, not just column values.
Example:

                        
SELECT product_name, price, price * 1.1 AS discounted_price FROM products ORDER BY discounted_price;

Sorting NULL Values:

By default, NULL values are considered the smallest in ascending order and the largest in descending order.
You can control the sorting behavior of NULL values using the NULLS FIRST or NULLS LAST options.
Example:

                        
SELECT column_name FROM table_name ORDER BY column_name NULLS LAST;

Sorting by Position:

Instead of specifying column names, you can sort by column positions in the ORDER BY clause.
Example:

                        
SELECT product_name, price FROM products ORDER BY 2 DESC, 1 ASC;

GROUP BY Clause

The GROUP BY clause in SQL is used to group rows from a table based on one or more columns.
Syntax:

                        
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

Here, column1 is the column by which you want to group the rows.

Aggregation Functions:

Aggregation functions (e.g., COUNT, SUM, AVG, MAX, MIN) are often used with GROUP BY to calculate values for each group.
Example:

                        
SELECT department, AVG(salary) FROM employees GROUP BY department;

Grouping by Multiple Columns:

You can group by multiple columns by listing them in the GROUP BY clause.
This creates a hierarchical grouping based on the specified columns.
Example:

                        
SELECT department, gender, AVG(salary) FROM employees GROUP BY department, gender;

HAVING Clause:

The HAVING clause is used with GROUP BY to filter groups based on aggregate function results.
It's similar to the WHERE clause but operates on grouped data.
Example:

                        
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

Combining GROUP BY and ORDER BY:

You can use both GROUP BY and ORDER BY in the same query to control the order of grouped results.
Example:

                        
SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY COUNT(*) DESC;

Aggregate Functions Overview

Aggregate functions are used to perform calculations on groups of rows or entire result sets.
They provide insights into data by summarizing and processing information.

Common Aggregate Functions

COUNT(): Counts the number of rows in a group or result set.
SUM(): Calculates the sum of numeric values in a group or result set.
AVG(): Computes the average of numeric values in a group or result set.
MAX(): Finds the maximum value in a group or result set.
MIN(): Retrieves the minimum value in a group or result set.

Sample Table: Sales

                    
+---------+----------+----------+-------------+
| OrderID | Product  | Quantity | TotalAmount |
+---------+----------+----------+-------------+
| 1       | Widget A | 10       | 2500.00     |
| 2       | Widget B | 5        | 1000.00     |
| 3       | Widget A | 8        | 2000.00     |
| 4       | Widget C | 12       | 3600.00     |
| 5       | Widget B | 7        | 1400.00     |
+---------+----------+----------+-------------+

Examples:

COUNT() Example:

                        
SELECT COUNT(*) FROM Sales;

Result: 5 (Count of rows in the "Sales" table)

SUM() Example:

                        
SELECT SUM(TotalAmount) FROM Sales;

Result: 10500.00 (Sum of the "TotalAmount" column)

AVG() Example:

                        
SELECT AVG(Quantity) FROM Sales;

Result: 8.4 (Average quantity of products sold)

MAX() Example:

                        
SELECT MAX(TotalAmount) FROM Sales;

Result: 3600.00 (Maximum total amount)

MIN() Example:

                        
SELECT MIN(Quantity) FROM Sales;

Result: 5 (Minimum quantity of products sold)

Updating, Deleting, and Truncating Data

Updating Existing Records (UPDATE)

The UPDATE statement is used to modify existing records in a table.

Syntax ↓

        
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

        
UPDATE Customers
SET City = 'New York'
WHERE CustomerID = 1;

Deleting Records (DELETE)

The DELETE statement is used to remove records from a table.

Syntax ↓

        
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Orders WHERE OrderID = 100;

Delete All Rows

To delete all rows from a table, use the DELETE statement without a WHERE clause.

Syntax ↓

        
DELETE FROM table_name;
Example:
DELETE FROM Customers;

Delete Single/Multiple Rows

To delete single or multiple rows based on specific conditions, use the DELETE statement with a WHERE clause.

Syntax ↓

        
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Employees WHERE Salary < 30000;

Truncating a Table (TRUNCATE)

The TRUNCATE statement is used to remove all records from a table quickly.

Syntax ↓

        
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE Customers;

Difference Between DELETE and TRUNCATE

While both DELETE and TRUNCATE can be used to remove data from a table, there are key differences:

DELETE is a DML (Data Manipulation Language) command, while TRUNCATE is a DDL (Data Definition Language) command.
DELETE allows for the removal of specific rows based on a condition, whereas TRUNCATE removes all rows from a table.
DELETE operations are slower than TRUNCATE, especially on large tables, as DELETE logs individual row deletions and can be rolled back, while TRUNCATE doesn't log individual row deletions.
DELETE can activate triggers, while TRUNCATE cannot.
Remember: By DELETE & TRUNCATE there is no changes on the structure while if we use the operation DROP, it removes the whole structure of a table along with all its data. Specifically, you use the DROP TABLE statement in SQL to delete an entire table, including its structure, columns, constraints, and any associated data.

Database Transactions, Commit, Rollback and Autocommit in MySQL:

A transaction is a sequence of one or more SQL statements executed as a single unit. Transactions ensure data integrity and consistency in a database.
Commit: Makes all changes made during the current transaction permanent. Once committed, changes are saved to the database, marking the transaction as successful.
Rollback: Undoes all changes made during the current transaction. It reverts the database to its state before the transaction started, useful for handling errors or canceling transactions.
Autocommit: In MySQL, autocommit is the default mode where each SQL statement is treated as a transaction and automatically committed. Disabling autocommit allows manual control over transactions.

Autocommit in MySQL:

Default Autocommit:

                    
-- Default autocommit mode (each statement is a transaction)
SET autocommit = ON;

Disable Autocommit:

                    
-- Disable autocommit (requires explicit commit or rollback)
SET autocommit = OFF;

Example Transaction in MySQL with commit:

                    
-- Disable autocommit
SET autocommit = OFF;

-- Start a transaction
START TRANSACTION;

-- SQL statements within the transaction
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- Commit the transaction
COMMIT;

Example Transaction in MySQL with Rollback:

                    
-- Disable autocommit
SET autocommit = OFF;

-- Start a transaction
START TRANSACTION;

-- SQL statements within the transaction
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- Rollback the transaction (simulate an error or cancellation)
ROLLBACK;

Commit Limitations:

Irreversible Changes: Once committed, changes become permanent, and there's no built-in mechanism to revert them directly.
Data Consistency: Committing is suitable when you are certain about the correctness of the changes.

Best Practices:

Always use transactions when performing multiple related database operations.
Handle exceptions appropriately and perform a rollback in case of errors.
Choose between autocommit and manual control based on the requirements of your application.

Primary Key & Foreign Key

Primary Key

A Primary Key always contains unique data within a table.
Uniqueness in a Primary Key is achieved using a unique constraint. However, a significant distinction is that a Primary Key does not allow null values, unlike a Unique Key.
Each table can have only one Primary Key constraint, and it plays a crucial role in uniquely identifying each record in the table.

Example:

                    
+-----------------------------------------+
|  Id |     Name        |  Age  |  City   |
+-----------------------------------------+
|  1  |  Ram Kumar      |   19  |  Agra   |
|  2  |  Salman Khan    |   18  |  Bhopal |
|  3  |  Meera Khan     |   19  |  Agra   |
|  4  |  Sarita Kumari  |   21  |  Delhi  |
+-----------------------------------------+

For a column to serve as a Primary Key, it must be unique. In this example, 'Id' is chosen as the Primary Key since it meets the criteria of uniqueness, whereas 'Name,' 'Age,' and 'City' may contain duplicate values.

Create Table with PRIMARY KEY Syntax

                        
CREATE TABLE table_name (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(10) NOT NULL,
    PRIMARY KEY(id)
);

The AUTO_INCREMENT attribute for the 'id' column ensures that the database system automatically assigns a unique value to 'id' for each new record. This eliminates the need for manually specifying the 'id' value during insertion.

Alter Table with PRIMARY KEY Syntax

When a table has already been created without a specified primary key, you can alter it to add a primary key constraint afterward.

                        
ALTER TABLE table_name 
ADD PRIMARY KEY(id);

This ALTER TABLE statement adds a primary key constraint to the 'id' column of the existing table.

Foreign Key

A FOREIGN KEY is a crucial database concept used to establish a connection between two tables.
It acts as a link between tables by referencing a PRIMARY Key in one table as a FOREIGN Key in another table.

Example:

                    
Student Table 
+-----------------------------------------+
|  Id |     Name        |  Age  |  City   |
+-----------------------------------------+
|  1  |  Ram Kumar      |   19  |   1     |
|  2  |  Salman Khan    |   18  |   2     |
|  3  |  Meera Khan     |   19  |   1     |
|  4  |  Sarita Kumari  |   21  |   3     |
+-----------------------------------------+

City Table 
+--------------+
| Cid | City   |
+--------------+
|  1  | Agra   |
|  2  | Bhopal |
|  3  | Delhi  |
+--------------+

In the given example, 'Id' in the Student table serves as the primary key, 'City' in the Student table acts as a foreign key, and 'Cid' in the City table is the primary key.
These tables can be linked using the foreign key relationship.
Using foreign keys helps in optimizing the database structure. By storing only the primary key of another table as a foreign key, the table becomes lightweight. This optimization is especially beneficial when dealing with large datasets, resulting in faster loading times and improved search performance, especially in scenarios where searching is done based on city-wise criteria.

Create Table with FOREIGN KEY Syntax

                        
CREATE TABLE student(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(10) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(city) REFERENCES City(cid);
)

Alter Table with FOREIGN KEY Syntax

                        
ALTER TABLE table_name 
ADD FOREIGN KEY(city) REFERENCES City(cid);

Practical:

Creating City Table:

                        
mysql> CREATE TABLE City(cid INT NOT NULL AUTO_INCREMENT, cityname VARCHAR(50) NOT NULL, PRIMARY KEY (cid));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO City(cityname) VALUES('Agra'), ('Dehli'), ('Bhopal'), ('Jaipur'), ('Noida');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from City;
+-----+----------+
| cid | cityname |
+-----+----------+
|   1 | Agra     |
|   2 | Dehli    |
|   3 | Bhopal   |
|   4 | Jaipur   |
|   5 | Noida    |
+-----+----------+
5 rows in set (0.00 sec)

Creating Personal Table

                        
mysql> CREATE TABLE Personal(id INT NOT NULL, name VARCHAR(50) NOT NULL, percentage INT NOT NULL, age INT NOT NULL, gender VARCHAR(1) NOT NULL, city INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (city) REFERENCES City (cid));
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO Personal VALUES (1, "Ram Kumar", 45, 19, "M", 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Personal VALUES (2, "Sarita Kumari", 55, 22, "F", 2), (3, "Salman Khan", 62, 20, "M", 1), (4, "Juhi Chawla", 47, 19, "F", 3), (5, "Anil Kapoor", 74, 22, "M", 1), (6, "John Abrahm", 64, 21, "M", 2), (7, "Shahid Kapoor", 52, 20, "M", 1);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Personal;
+----+---------------+------------+-----+--------+------+
| id | name          | percentage | age | gender | city |
+----+---------------+------------+-----+--------+------+
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |
+----+---------------+------------+-----+--------+------+
7 rows in set (0.00 sec)

Types of JOINS

INNER JOIN
LEFT JOIN
RIGHT JOIN
CROSS JOIN

INNER JOIN

In the context of relational databases, the INNER JOIN operation is used to retrieve data that exists in common between two tables, typically denoted as Table A and Table B.
This type of join selects records where there are matching values in the specified columns of both tables.
For the INNER JOIN operation to work seamlessly, the tables involved must have a relationship established through the concepts of foreign keys and primary keys.

INNER JOIN Syntax

                        
SELECT columns 
FROM table1 
INNER JOIN table2
ON table1.column_name = table2.column_name;

The syntax above demonstrates the structure of an INNER JOIN query. It specifies the columns to be selected and the tables involved. The condition for the join is defined using the ON keyword, where the common columns (usually a foreign key in one table and its corresponding primary key in the other) are specified.

Practical:

                        
mysql> SELECT * FROM Personal INNER JOIN City ON Personal.city = City.cid;
+----+---------------+------------+-----+--------+------+-----+----------+
| id | name          | percentage | age | gender | city | cid | cityname |
+----+---------------+------------+-----+--------+------+-----+----------+
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |   1 | Agra     |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |   1 | Agra     |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |   1 | Agra     |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |   1 | Agra     |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |   2 | Dehli    |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |   2 | Dehli    |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |   3 | Bhopal   |
+----+---------------+------------+-----+--------+------+-----+----------+
7 rows in set (0.00 sec)

We can also give alias name like this ↓

                        
SELECT * FROM Personal p INNER JOIN City c ON p.city = c.cid;

Now to remove some column on inner join we can do the following:

                        
mysql> SELECT Personal.id, Personal.name, City.cityname FROM Personal INNER JOIN City ON Personal.id = City.cid;
+----+---------------+----------+
| id | name          | cityname |
+----+---------------+----------+
|  1 | Ram Kumar     | Agra     |
|  2 | Sarita Kumari | Dehli    |
|  3 | Salman Khan   | Bhopal   |
|  4 | Juhi Chawla   | Jaipur   |
|  5 | Anil Kapoor   | Noida    |
+----+---------------+----------+
5 rows in set (0.00 sec)

We can also use WHERE Clause

                        
 mysql> SELECT p.id, p.name, p.percentage, p.age, c.cityname FROM Personal p INNER JOIN City c ON
p.city = c.cid WHERE c.cityname = 'Agra';
+----+---------------+------------+-----+----------+
| id | name          | percentage | age | cityname |
+----+---------------+------------+-----+----------+
|  1 | Ram Kumar     |         45 |  19 | Agra     |
|  3 | Salman Khan   |         62 |  20 | Agra     |
|  5 | Anil Kapoor   |         74 |  22 | Agra     |
|  7 | Shahid Kapoor |         52 |  20 | Agra     |
+----+---------------+------------+-----+----------+
4 rows in set (0.00 sec)

LEFT JOIN

A LEFT JOIN retrieves data that is common between two tables as well as all the data from the left table, even if there is no matching data in the right table.
If there is no common data, the result will still display all the records from the left table, with corresponding NULL values for columns from the right table.

Example:

                    
Student Table (A)
+----------------------------------------------+
|  Id |     Name        |  Age  |  City (F.K)  |
+----------------------------------------------+
|  1  |  Ram Kumar      |   19  |       1      |
|  2  |  Salman Khan    |   18  |       2      |
|  3  |  Meera Khan     |   19  |              |
|  4  |  Sarita Kumari  |   21  |       3      |
+----------------------------------------------+

City Table (B)
+--------------------+
| Cid (P.K) | City   |
+--------------------+
|     1     | Agra   |
|     2     | Bhopal |
|     3     | Delhi  |
|     4     | Noida  |
+--------------------+

Left Join Result
+--------------------------------------------------------------+
|  Id |     Name        |  Age  |  City (F.K)  | Cid  |  City  |
+--------------------------------------------------------------+
|  1  |  Ram Kumar      |   19  |       1      |  1   |  Agra  |
|  2  |  Salman Khan    |   18  |       2      |  2   | Bhopal |
|  3  |  Meera Khan     |   19  |              |      |        |
|  4  |  Sarita Kumari  |   21  |       3      |  3   | Delhi  |
+--------------------------------------------------------------+

LEFT JOIN Syntax

                        
SELECT columns 
FROM table1 
LEFT JOIN table2
ON table.column_name = table2.column_name;

The syntax above illustrates the structure of a LEFT JOIN query, where data from the left table is selected along with any matching data from the right table based on the specified column relationship.

Practical:

                        
mysql> SELECT * FROM Personal LEFT JOIN City ON Personal.city = City.cid;
+----+---------------+------------+-----+--------+------+------+----------+
| id | name          | percentage | age | gender | city | cid  | cityname |
+----+---------------+------------+-----+--------+------+------+----------+
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |    1 | Agra     |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |    2 | Dehli    |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |    1 | Agra     |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |    3 | Bhopal   |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |    1 | Agra     |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |    2 | Dehli    |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |    1 | Agra     |
+----+---------------+------------+-----+--------+------+------+----------+

RIGHT JOIN

A RIGHT JOIN retrieves data that is common between two tables along with all the data from the right table, even if there is no matching data in the left table.

RIGHT JOIN Syntax

                        
SELECT columns 
FROM table1 
RIGHT JOIN table2 
ON table1.column_name = table3.column_name;
             |                      |
           (F.K)                  (P.K)

Practical:

                        
mysql> SELECT * FROM Personal RIGHT JOIN City ON Personal.city = City.cid;
+------+---------------+------------+------+--------+------+-----+----------+
| id   | name          | percentage | age  | gender | city | cid | cityname |
+------+---------------+------------+------+--------+------+-----+----------+
|    1 | Ram Kumar     |         45 |   19 | M      |    1 |   1 | Agra     |
|    3 | Salman Khan   |         62 |   20 | M      |    1 |   1 | Agra     |
|    5 | Anil Kapoor   |         74 |   22 | M      |    1 |   1 | Agra     |
|    7 | Shahid Kapoor |         52 |   20 | M      |    1 |   1 | Agra     |
|    2 | Sarita Kumari |         55 |   22 | F      |    2 |   2 | Dehli    |
|    6 | John Abrahm   |         64 |   21 | M      |    2 |   2 | Dehli    |
|    4 | Juhi Chawla   |         47 |   19 | F      |    3 |   3 | Bhopal   |
| NULL | NULL          |       NULL | NULL | NULL   | NULL |   4 | Jaipur   |
| NULL | NULL          |       NULL | NULL | NULL   | NULL |   5 | Noida    |
+------+---------------+------------+------+--------+------+-----+----------+
9 rows in set (0.00 sec)

CROSS JOIN

A CROSS JOIN is a type of join operation that results in the combination of every row from the first table with every row from the second table, creating a Cartesian product.
Unlike INNER JOIN, LEFT JOIN, or RIGHT JOIN, there is no specific condition specified for the join. It simply combines all rows from one table with all rows from another table.

Example:

                    
Table A
+---------+
|  Column |
+---------+
|   A1    |
|   A2    |
+---------+

Table B
+---------+
|  Column |
+---------+
|   B1    |
|   B2    |
+---------+

Cross Join Result
+---------------------+
|  ColumnA  | ColumnB |
+---------------------+
|     A1    |   B1    |
|     A1    |   B2    |
|     A2    |   B1    |
|     A2    |   B2    |
+---------------------+

CROSS JOIN Syntax

                        
SELECT * 
FROM table1 
CROSS JOIN table2;

The CROSS JOIN syntax involves selecting all columns from both tables without specifying any condition for the join.

Practical:

                        
mysql> SELECT * FROM Personal CROSS JOIN City;
+----+---------------+------------+-----+--------+------+-----+----------+
| id | name          | percentage | age | gender | city | cid | cityname |
+----+---------------+------------+-----+--------+------+-----+----------+
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |   5 | Noida    |
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |   4 | Jaipur   |
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |   3 | Bhopal   |
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |   2 | Dehli    |
|  1 | Ram Kumar     |         45 |  19 | M      |    1 |   1 | Agra     |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |   5 | Noida    |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |   4 | Jaipur   |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |   3 | Bhopal   |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |   2 | Dehli    |
|  2 | Sarita Kumari |         55 |  22 | F      |    2 |   1 | Agra     |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |   5 | Noida    |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |   4 | Jaipur   |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |   3 | Bhopal   |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |   2 | Dehli    |
|  3 | Salman Khan   |         62 |  20 | M      |    1 |   1 | Agra     |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |   5 | Noida    |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |   4 | Jaipur   |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |   3 | Bhopal   |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |   2 | Dehli    |
|  4 | Juhi Chawla   |         47 |  19 | F      |    3 |   1 | Agra     |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |   5 | Noida    |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |   4 | Jaipur   |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |   3 | Bhopal   |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |   2 | Dehli    |
|  5 | Anil Kapoor   |         74 |  22 | M      |    1 |   1 | Agra     |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |   5 | Noida    |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |   4 | Jaipur   |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |   3 | Bhopal   |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |   2 | Dehli    |
|  6 | John Abrahm   |         64 |  21 | M      |    2 |   1 | Agra     |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |   5 | Noida    |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |   4 | Jaipur   |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |   3 | Bhopal   |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |   2 | Dehli    |
|  7 | Shahid Kapoor |         52 |  20 | M      |    1 |   1 | Agra     |
+----+---------------+------------+-----+--------+------+-----+----------+
35 rows in set (0.01 sec)

GROUP BY Clause

The GROUP BY clause is used in conjunction with the SELECT statement and Aggregate functions to group rows together by common column values.
Remember is it not necessary to have two tables

Example:

                
Student Table 
+----------------------------------------------+
|  Id |     Name        |  Age  |  City (F.K)  |
+----------------------------------------------+
|  1  |  Ram Kumar      |   19  |       1      |
|  2  |  Salman Khan    |   18  |       2      |
|  3  |  Meera Khan     |   19  |              |
|  4  |  Sarita Kumari  |   21  |       3      |
+----------------------------------------------+

City Table
+--------------------+
| Cid (P.K) | City   |
+--------------------+
|     1     | Agra   |
|     2     | Bhopal |
|     3     | Delhi  |
|     4     | Noida  |
+--------------------+

Result we want:
+-----------------------------+
|   City   | Total Students   |
+-----------------------------+
|    Agra  |       2          |
|  Bhopal  |       1          |
|   Delhi  |       1          |
+-----------------------------+

SELECT with GROUP BY Syntax

                    
SELECT columns
FROM table_name 
WHERE condition 
GROUP BY column_name(s);

If we are getting data from two tables and we want to use GROUP BY.

                    
SELECT columns
FROM table1 INNER JOIN table2 
ON table1.column_name = table2.column_name 
WHERE condition 
GROUP BY column_name(s);

Practical

                    
mysql> SELECT city, COUNT(city) FROM Personal GROUP BY city;
+------+-------------+
| city | COUNT(city) |
+------+-------------+
|    1 |           4 |
|    2 |           2 |
|    3 |           1 |
+------+-------------+
3 rows in set (0.01 sec)

mysql> SELECT City.cityname, COUNT(Personal.city) AS Total_student FROM Personal;
+----------+---------------+
| cityname | Total_student |
+----------+---------------+
| Agra     |             4 |
| Dehli    |             2 |
| Bhopal   |             1 |
+----------+---------------+
3 rows in set (0.00 sec)

Using WHERE Clause

We put WHERE clause before using GROUP BY

                    
mysql> SELECT City.cityname, COUNT(Personal.city) AS Total_student FROM Personal
 INNER JOIN City ON Personal.city = City.cid WHERE Personal.age >= 20 GROUP BY Personal.city;
+----------+---------------+
| cityname | Total_student |
+----------+---------------+
| Dehli    |             2 |
| Agra     |             3 |
+----------+---------------+
2 rows in set (0.00 sec)

GROUP BY with HAVING Clause

Now if we want those cities from where there are more than 5 students. so this condition will work on total students it is done by HAVING Clause.

SELECT with GROUP BY & HAVING Syntax

                        
SELECT columns 
FROM table_name 
GROUP BY column_name(s)
HAVING condition;

Practical

                        
 mysql> SELECT City.cityname, COUNT(Personal.city) AS Total_student FROM Personal INNER JOIN City ON Personal.city = City.cid GROUP BY Personal.city HAVING COUNT(Personal.city) > 3;
+----------+---------------+
| cityname | Total_student |
+----------+---------------+
| Agra     |             4 |
+----------+---------------+
1 row in set (0.00 sec)