Unit -1

SQL Query
Functions 1.1. Numeric, String and Date functions
1.2. Aggregate Functions
1.3. Sorting and Grouping the data

Prepared By:-Darshana V.Halatwala 1

 Aggregate function

 SQL aggregation function is used to perform the

calculations on multiple rows of a single column of a
table.
 It returns a single value.
 It is also used to summarize the data.

Prepared By:-Darshana V.Halatwala 2

Aggregate functions:
These functions are used to do operations from the values of the
column and a single value is returned.

 AVG()
 COUNT()

 FIRST()

 LAST()

 MAX()

 MIN()

 SUM()

Prepared By:-Darshana V.Halatwala 3

COUNT ()
 COUNT function is used to Count the number of rows in a database
table. It can work on numeric
 COUNT function uses the COUNT(*) that returns the count of all the
rows in a specified table. COUNT(*) considers duplicate and Null.
 Syntax:-
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )

Prepared By:-Darshana V.Halatwala 4

Example
SSN c-id grade
123 15-415 4
234 15-415 3

▪ Find total number of students in 15-415

select count(*) from takes

where c-id=‘15-415’

Prepared By:-Darshana V.Halatwala 5

AVG()
 The AVG function is used to calculate the average value of the numeric
type. AVG function returns the average of all non-Null values.
 Syntax
AVG()
or
AVG( [ALL|DISTINCT] expression )
 Example:
SELECT AVG(COST)
FROM PRODUCT_MAST;

Prepared By:-Darshana V.Halatwala 6

Example

SSN c-id grade

123 15-413 4
234 15-413 3
▪ Find average grade, across all students

select avg(grade)
from students

Prepared By:-Darshana V.Halatwala 7

Sum ()

Sum function is used to calculate the sum of all selected columns.

It works on numeric fields only.
 Syntax
SUM( [ALL|DISTINCT] expression )
 Example: SUM()
SELECT SUM(COST)
FROM PRODUCT_MAST;

Prepared By:-Darshana V.Halatwala 8

 MAX ()
 MAX function is used to find the maximum value of a certain column.
This function determines the largest value of all selected values of a
column.
Syntax:
MAX( [ALL|DISTINCT] expression )
 Example:
SELECT MAX(RATE)
FROM PRODUCT_MAST;

Prepared By:-Darshana V.Halatwala 9

 MIN ()
 MIN function is used to find the minimum value of a certain column.
This function determines the smallest value of all selected values of a
column.
 Syntax
MIN( [ALL|DISTINCT] expression )
 Example:
SELECT MIN(RATE)
FROM PRODUCT_MAST;

Prepared By:-Darshana V.Halatwala 10

 First()
 FIRST(): The FIRST() function returns the first value of the selected
column.
 Syntax:
 SELECT FIRST(column_name) FROM table_name;
Example:-
 Fetching marks of first student from the Students table.
 SELECT FIRST(MARKS) FROM Students;

Prepared By:-Darshana V.Halatwala 11

Conti…. With Example

 Students-Table
For Example:-
Marks:-
90
70
50
60
Ans:-90

Prepared By:-Darshana V.Halatwala 12

 Last()
 LAST(): The LAST() function returns the last value of the selected
column.
 Syntax:
 SELECT LAST(column_name) FROM table_name;
 Example:-
 SELECT LAST(MARKS) FROM Students;

Prepared By:-Darshana V.Halatwala 13

Character functions

Character functions are of the following two types:

1. Case:-Manipulative Functions (LOWER, UPPER and

INITCAP)
2. Character-Manipulative Functions (CONCAT, LENGTH,
SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE)

Prepared By:-Darshana V.Halatwala 14

Scalar functions:
These functions are based on user input, these too
returns single value.
 UCASE()
 LCASE()
 MID()
 LEN()
 ROUND()
 NOW()
 FORMAT()

Prepared By:-Darshana V.Halatwala 15

Conti… with Example
 Lower()
 SELECT lower(‘ename’) FROM emp1;
Output:
Small letter column ‘ename’
Upper()
SELECT upper(‘DataBase’) FROM DUAL;
Output:
DATABASE
Concat()
 SELECT CONCAT('computer' ,'science’) FROM DUAL;
Output:
Computerscience
Mid()
SELECT MID(Name, 3, 2) FROM Employee;
Prepared By:-Darshana V.Halatwala 16
 Conti…

Select length(‘learning’) from dual;

Output:
8
SELECT ROUND(Marks) FROM Student;

S. No. Stu_ID Name Marks

1. 213 abhay 81.68
2. 214 Aakash 78.98
3. 215 bittu 64.45
4. 216 Ravi 70

Prepared By:-Darshana V.Halatwala 17

Conti…
SUBSTR : This function returns a portion of a string from a given start point to
an end point. If a substring length is not given, then SUBSTR returns all the
characters till the end of string (from the starting position specified).

Syntax:
SUBSTR('String',start-index,length_of_extracted_string)
Example:
SELECT SUBSTR('Database Management System', 9)
FROM DUAL;
Output:
Management System

Prepared By:-Darshana V.Halatwala 18

Conti…
INSTR :
This function returns numeric position of a character or a string in a given string.
Optionally, you can provide a position m to start searching, and the
occurrence n of string.
Also, if the starting position is not given, then it starts search from index 1, by
default. If after searching in the string, no match is found then, INSTR function
returns 0.
Syntax:
INSTR(Column|Expression], 'String', [m], [n])
Example:
SELECT INSTR('Google apps are great applications','app',1,2)
FROM DUAL;
Output:
23

Prepared By:-Darshana V.Halatwala 19

Conti…

Now()
SELECT Name, NOW() AS DateTime FROM Employee;

Format()
SELECT NAME, FORMAT(DATE, DD-MM-YYYY) FROM Employee;

Prepared By:-Darshana V.Halatwala 20

Date functions
SYSDATE:-
→SELECT SYSDATE FROM DUAL;
Returns current date
select sysdate+10 from dual;
select sysdate-10 from dual
ADD Month()
→SELECT ADD_MONTHS(DATE '2024-01-01', 3) FROM DUAL;
◼ Adds 3 months
To char()
→SELECT TO_CHAR(DATE '2024-12-12', 'Month DD, YYYY') FROM DUAL;
◼ Returns 'December 12, 2024’
◼ To_date()
-→SELECT TO_DATE('12-12-2024', 'DD-MM-YYYY') FROM DUAL;
-- Converts string to date

Prepared By:-Darshana V.Halatwala 21

Conti…
Last_day()
-→SELECT LAST_DAY(DATE '2024-12-12') FROM DUAL;
◼ Returns '2024-12-31’
◼ Next_day()
SELECT NEXT_DAY(SYSDATE,'MONDAY') FROM DUAL;

Prepared By:-Darshana V.Halatwala 22

Conti…
 DATE_FORMAT(): It formats a date as specified by a format mask.
Syntax: SELECT DATE_FORMAT(‘2018-06-15’, ‘%Y’);
Output: 2018
 DAY(): It returns the day portion of a date value.
Syntax: SELECT DAY(‘2018-07-16’);
Output: 16
DATE_PART():
Example:-
SELECT DATE_PART('Year', admitted_date) FROM student;

Prepared By:-Darshana V.Halatwala 23

 DAYNAME(): It returns the weekday name for a date.
Syntax: SELECT DAYNAME('2008-05-15');
Output: Thursday
DAYWEEK(): It returns the weekday index for a date value.
Syntax: SELECT DAYWEEK(‘2018-07-16’);
Output: 0
DAYOFYEAR(): It returns the day of the year for a date value.
 For example −
 SELECT DAYOFYEAR('2018-02-15');
46

Prepared By:-Darshana V.Halatwala 24

HOUR(): It returns the hour portion of a date value.
Syntax: SELECT HOUR(‘2018-07-16 09:34:00’);
Output: 9
find records that are less than, greater than, or equal to the day before today
SELECT CURRENT_DATE,
TRUNC(CURRENT_DATE) ,
TRUNC(CURRENT_DATE)-1 FROM dual;

Prepared By:-Darshana V.Halatwala 25

 Conti…

Find the Number of Months Between Two Dates

SELECT employee_id, hire_date, ROUND(MONTHS_BETWEEN(SYSDATE,

HIRE_DATE) FROM employee;

Get the First Day of the Month

SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;

Prepared By:-Darshana V.Halatwala 26

Numeric Functions
 ABS()
 CEIL()
 FLOOR()
 MOD()
 POWER()
 ROUND()
 TRUNC()

Prepared By:-Darshana V.Halatwala 27

Conti…
 1) ABS() Function
 This Numeric Function in Oracle is used to convert (-VE)
value into (+VE) value. That means the ABS function in
Oracle returns the absolute value of n.
 Syntax:
ABS(number)
 Example:
SELECT ABS(-12) FROM DUAL;
Output:
12

Prepared By:-Darshana V.Halatwala 28

 Conti…
2) CEIL() Function
This Numeric Function in Oracle is used to return a value that is
greater than or equal to the given value.
Syntax: CEIL(NUMBER)
 Example:
SELECT CEIL(9.0) FROM DUAL;
Output:
 9
 Example:
SELECT CEIL(9.3) FROM DUAL;
Output:
 10

Prepared By:-Darshana V.Halatwala 29

 Conti…
 3) FLOOR() Function
This numeric function is used to return the largest integer equal to
or less than n.
 Syntax: FLOOR(NUMBER)
 Example:
SELECT FLOOR(9.0) FROM DUAL;
Output:
 9
 Example:
SELECT FLOOR(9.8) FROM DUAL;
Output:
 9

Prepared By:-Darshana V.Halatwala 30

Conti…
 4) MOD() Function
 The Oracle MOD() function is used to return the remainder value.
 Syntax: MOD(m, n)
 Example:
SELECT MOD(10,2) FROM DUAL;
 Output:
 0
 Example:
SELECT MOD(15,4) FROM DUAL;
Output:
3

Prepared By:-Darshana V.Halatwala 31

Conti..

 5) POWER() Function
 This numeric function is used to return the power of a given
expression.
 Syntax: POWER(m, n)
 Example:
SELECT POWER(2, 3) FROM DUAL;
Output:
 8

Prepared By:-Darshana V.Halatwala 32

 Conti..
 6) ROUND() Function
 This numeric function returns the nearest value of the given
expression. The ROUND function in oracle returns n rounded to
integer places to the right of the decimal point.
 Syntax: ROUND(NUMBER,[DECIMAL PLACES])
 Example: The following example rounds a number to one decimal
point
SELECT ROUND(15.253, 1) FROM DUAL;
Output:
 15.3
 SELECT ROUND(2.5), ROUND(3.5) FROM DUAL;
 3,4

Prepared By:-Darshana V.Halatwala 33

 Conti…
 7) TRUNC() Function
 The TRUNC (number) function in Oracle is used to return n1
truncated to n2 decimal places. If n2 is omitted, then n1 is truncated
to 0 places.
 Syntax: TRUNC (NUMBER, DECIMAL PLACES)
 Example:
SELECT TRUNC(5.50) FROM DUAL;
Output:
 5
 SELECT TRUNC(32.456,2) FROM DUAL;
 32.45

Prepared By:-Darshana V.Halatwala 34

Group by clause

 The GROUP BY Clause is utilized in SQL with the

SELECT statement to organize similar data into
groups.
 It combines the multiple records in single or more
columns using some functions.

Prepared By:-Darshana V.Halatwala 35

Prepared By:-Darshana V.Halatwala 36
Conti….

 In the split phase, It divides the groups with its values.

 In the apply phase, It applies the aggregate function and generates
a single value.
 In the combiner phase, It combines the groups with single values
into a single value.

Prepared By:-Darshana V.Halatwala 37

 Conti…
 Points to Remember:
 GROUP BY Clause is utilized with the SELECT statement.
 GROUP BY aggregates the results on the basis of selected column:
COUNT, MAX, MIN, SUM, AVG, etc.
 GROUP BY returns only one result per group of data.
 GROUP BY Clause always follows the WHERE Clause.
 GROUP BY Clause always precedes the ORDER BY Clause

Prepared By:-Darshana V.Halatwala 38

 GROUP BY: Table is grouped based on the DeptID
column and Salary is aggregated department-wise.

1008 Alfa 3 4750

Prepared By:-Darshana V.Halatwala 39
 Having Clause
 HAVING Clause utilized in SQL as a conditional Clause with GROUP BY
Clause.
 This conditional clause returns rows where aggregate function results
matched with given conditions only.
 It added in the SQL because WHERE Clause cannot be combined with
aggregate results, so it has a different purpose.
 The primary purpose of the WHERE Clause is to deal with non-
aggregated or individual records.

Prepared By:-Darshana V.Halatwala 40

Conti..

 HAVING Clause always utilized in combination with GROUP BY Clause.

 HAVING Clause restricts the data on the group records rather than
individual records.
 WHERE and HAVING can be used in a single query.

Prepared By:-Darshana V.Halatwala 41

 Table is grouped based on DeptID column and these grouped rows filtered
using HAVING Clause with condition AVG(Salary) > 3000.

Prepared By:-Darshana V.Halatwala 42

Prepared By:-Darshana V.Halatwala 43
ORDER BY clause
 The ORDER BY clause in SQL will help us to sort the records based on
the specific column of a table. This means that all the values stored
in the column on which we are applying ORDER BY clause will be
sorted, and the corresponding column values will be displayed in the
sequence in which we have obtained the values in the earlier step.
 Using the ORDER BY clause:
 The records will be sorted in ascending order whenever the ASC
keyword is used with ORDER by clause.
 DESC keyword will sort the records in descending order.

Prepared By:-Darshana V.Halatwala 44

Ascending order

 Write a query to sort the records in the ascending

order of the customer names stored in the customers
table.

SELECT *
FROM customers
ORDER BY Name ASC;

Prepared By:-Darshana V.Halatwala 45

Descending order

 Write a query to sort the records in the descending

order of the customer salary stored in the customers
table.
 Query:
SELECT *
FROM customers
ORDER BY Salary DESC;

Prepared By:-Darshana V.Halatwala 46

The ORDER BY Clause

▪ Find student records, sorted in name order by reverse

ssn

select *
from student
order by name, ssn desc

Prepared By:-Darshana V.Halatwala 47

 More Examples
▪ Find the total number of students in each course_id

SSN c-id grade

123 15-413 4
234 15-413 3

select count(*)
from takes
where ???

Prepared By:-Darshana V.Halatwala 48

 More Examples
▪ Find the total number of students in each course_id

SSN c-id grade c-id count

123 15-413 4 15-413 2
234 15-413 3

select c-id, count(*)

from takes
group by c-id

Prepared By:-Darshana V.Halatwala 49

 More Examples
▪ Find total number of students in each course_id,
and sort by in decreasing order

SSN c-id grade

123 15-413 4
234 15-413 3

select c-id, count(*)

from takes
group by c-id
order by c-id desc
Prepared By:-Darshana V.Halatwala 50
Rename Table
 Syntax:-
rename table old_table to new_table;

To Find Unique Column Values From Table

Syntax:-
SELECT DISTINCT column_name
FROM table_name;
SQL query to find unique values of
the department from the employee table.

SELECT DISTINCT DEPARTMENT FROM emp;

Prepared By:-Darshana V.Halatwala 51

 Difference Between ORDER BY and GROUP BY
Clause
GROUP BY ORDER BY

1)Whereas Order by statement sort

1)Group by statement is used to
the result-set either in ascending or
group the rows that have the same
descending order.
value.
2)While in the select statement, it is
2)In select statements, it is always
always used after the group by
used before the order by keyword.
keyword.
3)In group by clause, the tuples are
3) Whereas in order by clause, the
grouped based on the similarity
result set is sorted based on
between the attribute values of
ascending or descending order.
tuples.
4) While order by clause controls the
4)Group by controls the presentation
presentation of columns.
of tuples(rows).
Prepared By:-Darshana V.Halatwala 52
Example More Queries…
1) Find Top 2 records from Employee Tables.
Ans:-select * from emp1
where rownum<=2;
2) Find Top 2 records from Employee Tables in descending
order.
Ans:-select * from emp1
where rownum<=2
order by ename desc;

Prepared By:-Darshana V.Halatwala 53