Complex Retrieval Queries

using Group By
GROUP BY and HAVING Clause
• The GROUP BY clause is a SQL command that is used to group rows
that have the same values.
• The GROUP BY clause is used in the SELECT statement.
• Optionally it is used in conjunction with aggregate functions to
produce summary reports from the database.
• That’s what it does, summarizing data from the database.
• The queries that contain the GROUP BY clause are called grouped
queries and only return a single row for every grouped item.
• Examples:
• Use GROUP BY on single column
• GROUP BY on multiple columns
• Use GROUP BY with ORDER BY
• GROUP BY with HAVING clause
• Use GROUP BY with JOINS
GROUP BY on single column

• Example:
• Find no. of employees per city.

• Query:
SELECT COUNT(EmpID), City
FROM Employees
GROUP BY City;
GROUP BY with ORDER BY
• When we use the SQL GROUP BY statement with the ORDER BY
clause, the values get sorted either in ascending or descending order.

• Example:
• Write a query to retrieve the number of employees in each city,
sorted in descending order.
SELECT COUNT(EmpID), City
FROM Employees
GROUP BY City
ORDER BY COUNT(EmpID) DESC;
 GROUP BY with HAVING clause
• The SQL GROUP BY statement is used with ‘HAVING’ clause to mention conditions
on groups.
• Also, since we cannot use the aggregate functions with the WHERE clause, we
have to use the ‘HAVING’ clause to use the aggregate functions with GROUP BY.

• Example:
• Write a query to retrieve the number of employees in each city, having salary >
15000
SELECT COUNT(EmpID), City
FROM Employees
GROUP BY City
HAVING SALARY > 15000;
(Since all are records in the Employee table have a salary > 15000,
we will see the following table as output)
GROUP BY on multiple columns
• Example:
• Write a query to retrieve the number of employees having different
salaries in each city.

SELECT City, Salary, Count(*)

FROM Employees
GROUP BY City, Salary;
JOIN
• SQL Join statement is used to combine data or rows from two or
more tables based on a common field between them.
• Different types of Joins are as follows:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
INNER JOIN
• Returns records that have matching values in both tables.
• We can also write JOIN instead of INNER JOIN. JOIN is same as INNER
JOIN.
• This keyword will create the result-set by combining all rows from
both the tables where the condition satisfies
• i.e value of the common field will be the same.
• Syntax:
Student Course

SELECT Course.COURSE_ID, Student.NAME, Student.AGE FROM Student

INNER JOIN Course
ON Student.ROLL_NO = Course.ROLL_NO;
LEFT JOIN
• This join returns all the rows of the table on the left side of the join
and matches rows for the table on the right side of the join.
• For the rows for which there is no matching row on the right side, the
result-set will contain null.
• LEFT JOIN is also known as LEFT OUTER JOIN.
• Syntax:
Example
• SELECT Student.NAME,Course.COURSE_ID FROM Student
• LEFT JOIN Course
• ON Course.ROLL_NO = Student.ROLL_NO;
RIGHT JOIN
• This join returns all the rows of the table on the right side of the join
and matching rows for the table on the left side of the join.
• For the rows for which there is no matching row on the left side, the
result-set will contain null.
• RIGHT JOIN is also known as RIGHT OUTER JOIN.
• Syntax:
Example:
SELECT Student.NAME, Course.COURSE_ID
FROM Student
RIGHT JOIN Course
ON Course.ROLL_NO = Student.ROLL_NO;
FULL JOIN
• FULL JOIN creates the result-set by combining results of both LEFT
JOIN and RIGHT JOIN.
• The result-set will contain all the rows from both tables.
• For the rows for which there is no matching, the result-set will
contain NULL values.

• SELECT table1
Example:
SELECT Student.NAME, Course.COURSE_ID
FROM Student
FULL JOIN Course
ON Course.ROLL_NO = Student.ROLL_NO;
Example 2: full join
Logical Operators
LIKE condition
• LIKE condition is used to perform pattern matching to find the correct
result.
• It is used in SELECT, INSERT, UPDATE and DELETE statement with the
combination of WHERE clause.
• Syntax:
• expression LIKE pattern [ ESCAPE 'escape_character' ]
• Parameters
• expression: It specifies a column or field.
• pattern: It is a character expression that contains pattern matching.
• escape_character: It is optional. It allows you to test for literal instances of
a wildcard character such as % or _. If you do not provide the
escape_character, MySQL assumes that "\" is the escape_character.
Different LIKE operators with '%' and '_'
wildcards:
Examples
• 1) Using % (percent) Wildcard:

• 2) Using _ (Underscore) Wildcard:

• 3) using not like:

IN Condition
• The IN operator allows you to specify multiple values in a WHERE clause.
• The IN operator is a shorthand for multiple OR conditions.
Syntax
• SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
OR
• SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Examples
AND, OR and NOT Operators
• The WHERE clause can be combined with AND, OR,
and NOT operators.
• The AND and OR operators are used to filter records based on more
than one condition:
• 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.
BETWEEN Operator
• The BETWEEN operator selects values within a given range. The
values can be numbers, text, or dates.
• The BETWEEN operator is inclusive: begin and end values are
included.
• Syntax
• SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Nested Queries in SQL
• Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along
with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
• There are a few rules that subqueries must follow −
• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple columns are
in the main query for the subquery to compare its selected columns.
• An ORDER BY command cannot be used in a subquery, although the main query can use
an ORDER BY.
• The GROUP BY command can be used to perform the same function as the ORDER BY in
a subquery.
• Subqueries that return more than one row can only be used with multiple value
operators such as the IN operator.
• The BETWEEN operator cannot be used with a subquery. However, the BETWEEN
operator can be used within the subquery.
Nested
Queries
• In nested queries, a query is
written inside a query. The
result of inner query is used
in execution of outer query.
• Here we use STUDENT &
city tables for understanding
nested queries.
Subqueries with the
SELECT Statement
Subqueries with the INSERT Statement
• The INSERT statement uses the data returned from the subquery to
insert into another table.
• The selected data in the subquery can be modified with any of the
character, date or number functions.
• Syntax:
• INSERT INTO Student1 SELECT * FROM Student2;
• To delete students from Student2 table whose rollno is same as that
in Student1 table and having location as ‘Chennai’.
Example 2:
Subqueries with the
UPDATE Statement
• The subquery can be used in conjunction with
the UPDATE statement.
• Either single or multiple columns in a table can
be updated when using a subquery with the
UPDATE statement.
• Syntax:
Example
• Consider the following employee database.
• Employee(emp_name, street,city,date_of_joining)
• Works(emp_name,company_name,salary)
• Company(company_name,city)
• Manages(emp_name,manager_name)
• Write SQL queries for following:
1. Modify the database so that ‘Deepa’lives in ‘Pune’;
2. Give all employees of ‘Aarya corporation’ a 10% rise in salary.
3. Display all employees who joined in the month of ‘March’;
4. Find all employees who earn more than average salary of all employees of their
company.