module 7

The GROUP BY Clause is used to collect data from multiple records and group the result by one or more column. It is generally used in a SELECT statement.

You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc. on the grouped column.

Syntax:

SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

The Group by Clause with count function

Let's count repetitive number of WORKING_HOURS in the column WORKING_HOURS.

- SELECT WORKING_HOURS, COUNT(*)
- FROM EMPLOYEE
- GROUP BY WORKING_HOURS;

The Group by Clause with sum function

The following query will GROUP BY the example using the SUM function and return the emp_name and total working hours of each employee.

SELECT emp_name, SUM(working_hours) AS "Total working hours"
FROM employees
GROUP BY emp_name;

The Group by Clause with min function

The following example specifies the minimum working hours of the employees form the table "employees".

Syntax:

SELECT emp_name, MIN(working_hours) AS "Minimum working hour"
FROM employees
GROUP BY emp_name;

The Group by Clause with max function

The following example specifies the maximum working hours of the employees form the table "employees".

SELECT emp_name, MAX (working_hours) AS "Minimum working hour"
FROM employees
GROUP BY emp_name;

The Group by Clause with avg function

The following example specifies the average working hours of the employees form the table "employees".

Syntax:

- SELECT emp_name, AVG(working_hours) AS
- "Average working hour"
- FROM employees
- GROUP BY emp_name;

Joins

JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed whenever you need to fetch records from two or more tables.

There are three types of MYSQL joins:

MySQL INNER JOIN (or sometimes called simple join)
MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

Inner Joins

The Inner Join keyword is used with the SELECT statement and must be written after the FROM clause.

Inner Join Syntax:

SELECT columns
FROM table1
INNER JOIN table2 ON condition1
INNER JOIN table3 ON condition2 ...;

Inner Joins

To select records from both tables, execute the following query:

SELECT STUDENT.STUDENT_FNAME, STUDENT.STUDENT_LNAME, STUDENT.CITY, TECHNOLOGY.TECHNOLOGY
FROM STUDENT
INNER JOIN TECHNOLOGY
ON STUDENTS.STUDENT_ID = TECHNOLOGY.TECH_ID;

Inner Joins with Group by clause

The Inner Join can also be used with the GROUP BY clause.

SELECT STUDENTS.STUDENT_ID, TECHNOLOGY.INST_NAME, STUDENTS.CITY, TECHNOLOGY.TECHNOLOGY
FROM STUDENT
INNER JOIN TECHNOLOGY
ON STUDENTS.STUDENT_ID = TECHNOLOGY.TECH_ID GROUP BY INST_NAME;

Inner Joins with where clause

The WHERE clause enables you to return the filter result. The following example illustrates this clause with Inner Join:

SELECT TECH_ID, INST_NAME, CITY, TECHNOLOGY
FROM STUDENTS
INNER JOIN TECHNOLOGY
USING (STUDENT_ID) WHERE TECHNOLOGY = "JAVA";

Left Join

The Left Join in MySQL is used to query records from multiple tables. This clause is similar to the Inner Join clause that can be used with a SELECT statement immediately after the FROM keyword.

When we use the Left Join clause, it will return all the records from the first (left-side) table, even no matching records found from the second (right side) table. If it will not find any matches record from the right side table, then returns null.

Left Join

LEFT JOIN Syntax:

The following syntax explains the Left Join clause to join the two or more tables:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON Join_Condition;

Left Join with Group By clause

The Left Join can also be used with the GROUP BY clause. The following statement returns customer id, customer name, qualification, price, and date using the Left Join clause with the GROUP BY clause.

SELECT customers.customer_id, customers.customer_name, customers.qualification, orders.price, orders.date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id;

Right Join

The Right Join is used to joins two or more tables and returns all rows from the right-hand table, and only those results from the other table that fulfilled the join condition.

If it finds unmatched records from the left side table, it returns Null value. It is similar to the Left Join, except it gives the reverse result of the join tables. It is also known as Right Outer Join. So, Outer is the optional clause used with the Right Join.

Right Join

RIGHT JOIN Syntax

SELECT column_list
FROM Table1
RIGHT [OUTER] JOIN Table2
ON join_condition;

Right Join with Where clause

MySQL uses the WHERE clause to provide the filter result from the table.

Cross Join

CROSS JOIN is used to combine all possibilities of the two or more tables and returns the result that contains every row from all contributing tables.

The CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian product of all associated tables. The Cartesian product can be explained as all rows present in the first table multiplied by all rows present in the second table. It is similar to the Inner Join, where the join condition is not available with this clause.

Cross Join

The CROSS JOIN keyword is always used with the SELECT statement and must be written after the FROM clause.

CROSS JOIN Syntax:

SELECT column-lists
FROM table1
CROSS JOIN table2;

Self Join

A SELF JOIN is a join that is used to join a table with itself.

There is a need to combine data with other data in the same table itself.

SELF JOIN Syntax:

SELECT s1.col_name, s2.col_name...
FROM table1 s1, table1 s2
WHERE s1.common_col_name = s2.common_col_name;

Union Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order

Union Operator

UNION Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Union Operator

UNION ALL Syntax:

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Union Operator

UNION Example

The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers;

Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers;

Aggregate Functions

MySQL's aggregate function is used to perform calculations on multiple values and return the result in a single value like the average of all values, the sum of all values, and maximum & minimum value among certain groups of values.

We mostly use the aggregate functions with SELECT statements in the data query languages.

Aggregate Functions

Syntax:

The following are the syntax to use aggregate functions in MySQL:

function_name (DISTINCT | ALL expression)

Count Function

MySQL count() function returns the total number of values in the expression. This function produces all rows or only some rows of the table based on a specified condition, and its return type is BIGINT.

It returns zero if it does not find any matching rows. It can work with both numeric and non-numeric data types.

Suppose we want to get the total number of employees in the employee table, we need to use the count() function as shown in the following query:

Syntax:

SELECT COUNT(name) FROM employee;

SUM() Function

The MySQL sum() function returns the total summed (non-NULL) value of an expression. It returns NULL if the result set does not have any rows. It works with numeric data type only.

Suppose we want to calculate the total number of working hours of all employees in the table, we need to use the sum() function as shown in the following query:

SYNTAX:

SELECT SUM(working_hours) AS "Total working hours"
FROM employee;

AVG() Function

MySQL AVG() function calculates the average of the values specified in the column. Similar to the SUM() function, it also works with numeric data type only.

Suppose we want to get the average working hours of all employees in the table, we need to use the AVG() function as shown in the following query:

Syntax:

SELECT AVG(working_hours) AS "Average working hour"
FROM employee;

Min() Function

MySQL MIN() function returns the minimum (lowest) value of the specified column. It also works with numeric data type only.

Suppose we want to get minimum working hours of an employee available in the table, we need to use the MIN() function as shown in the following query:

Syntax:

SELECT MIN(working_hours) AS Minimum_working_hours
FROM employee;

Max() Function

MySQL MAX() function returns the maximum (highest) value of the specified column. It also works with numeric data type only.

Suppose we want to get maximum working hours of an employee available in the table, we need to use the MAX() function as shown in the following query:

Syntax:

SELECT MAX(working_hours) AS Maximum_working_hours
FROM employee;

First() Function

This function returns the first value of the specified column. To get the first value of the column, we must use the LIMIT clause.

Suppose we want to get the first working date of an employee available in the table, we need to use the following query:

Syntax:

SELECT working_date
FROM employee LIMIT 1;

Last() Function

This function returns the last value of the specified column. To get the last value of the column, we must use the ORDER BY and LIMIT clause.

Suppose we want to get the last working hour of an employee available in the table, we need to use the following query:

Syntax:

SELECT working_hours FROM employee
ORDER BY name
DESC LIMIT 1;