Lab # 10

Database management system

Fall 2021

Instructor

Student Name

CMSID

Department

Semester
Lesson Set Group by, Having, Subquery with Exist,
Union and Union all
10
Purpose 1. To get a basic awareness Group by clause
2. To understand the usage of having clause
3. How and where we can use subquery with exist and not exist
4. To practice and use union and union all with two tables

Procedure 1. Students should read the Pre-lab Reading assignment before coming to the
lab.
2. Students should complete the Pre-lab Writing assignment before coming to
the lab.
3. Students should complete Labs 10.1 through 10.2 in sequence in the lab.
Your instructor will give further instructions on grading and completing the
lab.
4. Students should complete the set of lab tasks before the next lab and get
them checked by their lab instructor.

Contents Pre-requisites Completion Page

Time Number

Pre-lab Reading Assignment - 20 min 3

Pre-lab Writing Assignment Pre-lab Reading 10 min 5

Lab 10

Lab 10.1 Pre-lab reading 30 min 5

Recalling the SQL syntax

Lab 10.2 Awareness of - 6

Lab Tasks Syntax Rules

2|Page
PRE-LAB READING ASSIGNMENT

Group By

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.

SQL GROUP BY Syntax

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

Having clause

The HAVING Clause enables you to specify conditions that filter which group results
appear in the results.
The WHERE clause places conditions on the selected columns, whereas the HAVING
clause places conditions on groups created by the GROUP BY clause.

Syntax
The HAVING clause must follow the GROUP BY clause in a query and must also
precede the ORDER BY clause if used. The following code block has the syntax of the
SELECT statement including the HAVING clause −
SELECT column1, column2
FROM table1, table2
WHERE conditions
GROUP BY column1, column2
HAVING conditions
ORDER BY column1, column2

Subquery

A subquery in MySQL is a query, which is nested into another SQL query and embedded
with SELECT, INSERT, UPDATE or DELETE statement along with the various
operators. We can also nest the subquery with another subquery. A subquery is known
as the inner query, and the query that contains subquery is known as the outer query.
The inner query executed first gives the result to the outer query, and then the
main/outer query will be performed. MySQL allows us to use subquery anywhere, but it
must be closed within parenthesis. All subquery forms and operations supported by the
SQL standard will be supported in MySQL also.

The following are the rules to use subqueries:

 Subqueries should always use in parentheses.
 If the main query does not have multiple columns for subquery, then a subquery

3|Page
 can have only one column in the SELECT command.
 We can use various comparison operators with the subquery, such as >, <, =,
IN, ANY, SOME, and ALL. A multiple-row operator is very useful when the
subquery returns more than one row.
 We cannot use the ORDER BY clause in a subquery, although it can be used
inside the main query.
 If we use a subquery in a set function, it cannot be immediately enclosed in a set
function.

The following are the advantages of using subqueries:

 The subqueries make the queries in a structured form that allows us to isolate
each part of a statement.
 The subqueries provide alternative ways to query the data from the table;
otherwise, we need to use complex joins and unions.
 The subqueries are more readable than complex join or union statements.
 AD

MySQL Subquery Syntax

The following is the basic syntax to use the subquery in MySQL:
SELECT column_list (s) FROM table_name
WHERE column_name OPERATOR
(SELECT column_list (s) FROM table_name [WHERE])

Union & Union All

Union:
Union means joining two or more data sets into a single set. In SQL Server, Union is
used to combine two queries into a single result set using the select statements. Union
extracts all the rows that are described in the query.

Syntax
SELECT City FROM student
UNION
SELECT City FROM student2
ORDER BY City;

Union All:
A union is used for extracting rows using the conditions specified in the query while
Union All is used for extracting all the rows from a set of two tables.

Syntax
SELECT City FROM student
UNION
SELECT City FROM student2
ORDER BY City;

4|Page
Pre-lab writing assignment

Fill in the blanks 1. summarizing data from the database is called ______________.

2. The Having clause is placed after ______________

3. A subquery is known as the _____________ query, and the query that

contains subquery is known as the ____________ query.

4. Union is used to combine _________ queries into a single result set

using the _________ statements

5|Page
 Lab 10.2 Lab Tasks

1. Use the Lab 9 database and write query to show the number of students coming from each city.

2. Use the Lab 9 database and write query to show the number of students that enrolled in database
course.

3. Write a query to display the total number of students that belongs to Quetta. Use Aliases to make
table more meaningful.

4. Create new table with name lecturer and add 3 columns (id, name, city) with constraints.

5. Write a query to show id, name, city from both student table and lecturer table.

6|Page