ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product

<< Data Manipulation Language

Inner Join, Outer Join, Semi Join, Self Join, Subquery, >>

Database Management System (CS403)

Lecture No. 30

Reading Material

"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,

McGraw-Hill

Overview of Lecture

Data Manipulation Language

Functions in SQL

In the previous lecture we have discussed different operators of SQL, which are used

in different commands. By the end of previous lecture we were discussing ORDER

BY clause, which is basically used to bring the output in ascending or descending

order. In this lecture we will see some examples of this clause.

ORDER BY Clause

The ORDER BY clause allows you to sort the records in your result set. The ORDER

BY clause can only be used in SELECT statements. The ORDER BY clause sorts the

result set based on the columns specified. If the ASC or DESC value is omitted, the

system assumed ascending order. We will now see few examples of this clause

SELECT supplier_city

FROM supplier

WHERE supplier_name = 'IBM'

ORDER BY supplier_city;

This would return all records sorted by the supplier_city field in ascending order.

SELECT supplier_city

FROM supplier

220

Database Management System (CS403)

WHERE supplier_name = 'IBM'

ORDER BY supplier_city DESC;

This would return all records sorted by the supplier_city field in descending order.

Functions in SQL

A function is a special type of command. Infact, functions are one-word command

that return a single value. The value of a function can be determined by input

parameters, as with a function that averages a list of database values. But many

functions do not use any type of input parameter, such as the function that returns the

current system time, CURRENT_TIME. There are normally two types of functions.

First is Built in, which are provided by any specific tool or language. Second is user

defined, which are defined by the user. The SQL supports a number of useful

functions.. In addition, each database vendor maintains a long list of their own

internal functions that are outside of the scope of the SQL standard.

Categories of Functions:

These categories of functions are specific to SQL Server. Depending on the

arguments and the return value, functions are categorized as under:

Mathematical (ABS, ROUND, SIN, SQRT)

String (LOWER, UPPER, SUBSTRING, LEN)

Date (DATEDIFF, DATEPART, GETDATE ())

System (USER, DATALENGTH, HOST_NAME)

Conversion (CAST, CONVERT)

We will now see an example using above-mentioned functions:

SELECT upper (stName), lower (stFName), stAdres, len(convert(char, stAdres)),

FROM student

In this example student name will be displayed in upper case whereas father name

will be displayed in lower case. The third function is of getting the length of student

address. It has got nesting of functions, first address is converted into character and

then its length will be displayed.

Aggregate Functions

These functions operate on a set of rows and return a single value. If used among

many other expressions in the item list of a SELECT statement, the SELECT must

221

Database Management System (CS403)

have a GROUP BY clause. No GROUP BY clause is required if the aggregate

function is the only value retrieved by the SELECT statement. Following are some of

the aggregate functions:

Function

Usage

AVG(expression)

Computes

average value of a column by the

expression

COUNT(expression)

Counts the rows defined by the expression

COUNT(*)

Counts all rows in the specified table or view

MIN(expression)

Finds the minimum value in a column by the

expression

MAX(expression)

Finds the maximum value in a column by the

expression

SUM(expression)

Computes the sum of column values by the expression

SELECT avg(cgpa) as 'Average CGPA', max(cgpa) as 'Maximum CGPA'

from student

GROUP BY Clause

The GROUP BY clause can be used in a SELECT statement to collect data across

multiple records and group the results by one or more columns. It is added to SQL

because aggregate functions (like SUM) return the aggregate of all column values

every time they are called, and without the GROUP BY function it is impossible to

find the sum for each individual group of column values.

The syntax for the GROUP BY clause is:

SELECT column1, column2, ... column_n, aggregate_function

(expression)

FROM tables

WHERE predicates

GROUP BY column1, column2, ... column_n;

Aggregate function can be a function such as SUM, COUNT, MIN or MAX

222

Database Management System (CS403)

Example using the SUM function

For example, the SUM function can be used to return the name of the department and

the total sales (in the associated department).

SELECT department, SUM (sales) as "Total sales"

FROM order_details

GROUP BY department;

In this example we have listed one column in the SELECT statement that is not

encapsulated in the SUM function, so we have used a GROUP BY clause. The

department field must, therefore, be listed in the GROUP BY section.

Example using the COUNT function

We can also use the COUNT function to return the name of the department and the

number of employees (in the associated department) that make over Rs 25,000 / year.

SELECT department, COUNT (*) as "Number of employees"

FROM employees

WHERE salary > 25000

GROUP BY department;

HAVING Clause

The HAVING clause is used in combination with the GROUP BY clause. It can be

used in a SELECT statement to filter the records that a GROUP BY returns. At times

we want to limit the output based on the corresponding sum (or any other aggregate

functions). For example, we might want to see only the stores with sales over Rs

1,500. Instead of using the WHERE clause in the SQL statement, though, we need to

use the HAVING clause, which is reserved for aggregate functions. The HAVING

clause is typically placed near the end of the SQL statement, and a SQL statement

with the HAVING clause may or may not include the GROUP BY clause. The syntax

for the HAVING clause is:

SELECT column1, column2, ... column_n, aggregate_function

(expression)

FROM tables

WHERE predicates

GROUP BY column1, column2, ... column_n

HAVING condition1 ... condition_n;

Aggregate function can be a function such as SUM, MIN or MAX.

We will now see few examples of HAVING Clause.

Example using the SUM function

223

Database Management System (CS403)

We can use the SUM function to return the name of the department and the total sales

(in the associated department). The HAVING clause will filter the results so that only

departments with sales greater than Rs 1000 will be returned.

SELECT department, SUM (sales) as "Total sales"

FROM order_details

GROUP BY department

HAVING SUM (sales) > 1000;

Example using the COUNT function

For example, you could use the COUNT function to return the name of the

department and the number of employees (in the associated department) that make

over $25,000 / year. The HAVING clause will filter the results so that only

departments with at least 25 employees will be returned.

SELECT department, COUNT (*) as "Number of employees"

FROM employees

WHERE salary > 25000

GROUP BY department

HAVING COUNT (*) > 10;

Accessing Multiple Tables:

Until now we have been accessing data through one table only. But there can be

occasions where we have to access the data from different tables. So depending

upon different requirements data can be accessed from different tables.

Referential integrity constraint plays an important role in gathering data from

multiple tables. Following are the methods of accessing data from different

tables:

Cartesian Product

· Inner join

Outer Join

Full outer join

Semi Join

Natural JoinWe will now discuss them one by one.

Cartesian product:

A Cartesian join gives a Cartesian product. A Cartesian join is when you join every

row of one table to every row of another table. You can also get one by joining every

row of a table to every row of itself. No specific command is used just Select is used

to join two tables. Simply the names of the tables involved are given and Cartesian

product is produced. It produces m x n rows in the resulting table. We will now see

few examples of Cartesian product.

Select * from program, course

Now in this example all the attributes of program and course are selected and the total

number of rows would be number of rows of program x number of rows of course.In

Cartesian product certain columns can be selected, same column name needs to be

224

Database Management System (CS403)

qualified. Similarly it can be applied to more than one table, and even can be applied

on the same table .For Example

SELECT * from Student, class, program

Summary

In today's lecture we have seen certain important functions of SQL, which are more

specific to SQL Server. We studied some mathematical, string and conversion

functions, which are used in SQL Commands. We also studied Aggregate functions,

which are applied on a entire table or a set of rows and return one value. We also

studied Group By clause which is used in conjunction with aggregate functions. In the

end we saw how to extract data from different tables and in that we studied Cartesian

product. We will see rest of the methods in our coming lectures.

225

Table of Contents: