MarathwadaShikshanPrasarakMandal’s
Deogiri Institute of Engineering and Management Studies, Aurangabad
Department of Computer Science and Engineering
Practical Experiment Instruction Sheet
Class: TE (CSE) Lab No.: 3
Subject: Database System
Semester: I Version: I
Experiment No.: 5
Aim: Study & Implementation of Numeric, Character, Aggregate Functions and Group
by functions.
Theory :
1] Single Row functions :- A single row function or scalar fun. Returns only one value for
every row queried in the table. Single Row functions are broadly classified as:-
A] Date functions
B] Numeric functions
C] Character functions
D]Conversions functions.
A] Date functions:-
select sysdate from dual;
SYSDATE
27-OCT-21
select sysdate+5 from dual;
SYSDATE+5
01-NOV-21
select sysdate-5 from dual;
SYSDATE- 5
22-OCT-21
select sysdate +14/24 from dual;
SYSDATE+14/24
28-OCT-21
1] Add_Months
Syntax:-Add_months (d,n)
Where d is date & n represents no. of months.
select add_months('27-oct-21','2') from dual;
ADD_MONTHS('27- OCT-21','2')
27-DEC-21
2] Last_Day
Syntax:-Last_Day(d)
select last_day('27-nov-21') from dual;
LAST_DAY('27- NOV-21')
30-NOV-21
3] Months_between
Syntax:-Months_between(d1,d2)
select months_between('27-oct-21','27-jan-21') from dual;
MONTHS_BETWEEN('27- OCT-21','27-JAN- 21')
9
4] Next_Day
Syntax:-Next_Day(d,day)
Where d represents date & day implies any week day.
select next_day('27-oct-21','tuesday') from dual;
NEXT_DAY('27-OCT-21','TUESDAY')
02-NOV-21
B] Numeric functions:-
Abs=absolute
select abs(-23.201),abs(-34),abs(43) from dual;
ABS(-23.201) ABS(- 34) ABS(43)
23.201 34 43
select sqrt(4) from dual;
SQRT(4)
2
select greatest(23,456,67,78,987,4,35,2) from dual;
GREATEST(23,456,67,78,987,4,35,2)
987
select least(23,456,67,78,987,4,35,2) from dual;
LEAST(23,456,67,78,987,4,35,2)
2
Numeric functions accept numeric input & returns numeric output.
Ex.
Functions Input Output
Ceil(n) Select ceil(42.78) from dual; 43
Floor(n) Select Floor(100.2) from dual; 100
Power(m,n) Select Power (4,2) from dual; 16
CEIL(42.78)
43
FLOOR(100.2)
100
POWER(4,2)
16
Round and Truncate
select round(5.553) from dual;
ROUND(5.553)
6
select round(5.553,2) from dual;
ROUND(5.553,2)
5.55
select round(5.553,1) from dual;
ROUND(5.553,1)
5.6
select trunc(5.553) from dual;
TRUNC(5.553)
5
select trunc(5.553,2) from dual;
TRUNC(5.553,2)
5.55
select trunc(5.553,1) from dual;
TRUNC(5.553,1)
5.5
select sign(235)from dual;
SIGN(235)
1
select sign(-235)from dual;
SIGN(-235)
-1
C] Character Functions:-
1]Lower(char);
select lower('GOOD MORNING') from dual;
LOWER('GOODMORNING')
good morning
2]Upper(char);
select upper('good morning') from dual;
UPPER('GOODMORNING')
GOOD MORNING
D] Conversion Functions:-
1] To_char(d,fmt):-
Where d is date &fmt is the format . This function converts date to a value of varchar2
in a form specified by date format fmt.
SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY') FROM dual;
TO_CHAR(SYSDATE,'DD- MM-YYYY')
29-10-2021
2]To_Date(char,fmt):-
Where char is character &fmt is format. This function converts varchar2 to a date.
Fmt specifies form of character.
CREATE TABLE mytest
(ID NUMBER(12,0) NOT NULL,
End_Year DATE DEFAULT TO_DATE('31-12-2007','DD-MM-YYYY') NOT
NULL);
Table Created
2] Group Functions or Aggregate Functions:-
select *from instructor;
ID NAME DEPT SALARY COLLEGE
15151 Mozart Music 40000 diems
32343 Ul said History 60000 -
58583 califieri History 62000 -
sociolog
54121 smith 35000 diems
y
A group function returns the result based on the group of rows . These are as follows:-
A] Avg Function:-
This Function return the average of values of the column specified in the argument of
column.
Syntax:-Avg(Col_name);
select avg(salary) from instructor;
AVG(SALARY)
49250
B] Min Functions :-
The function will give the least of all values of the column present in argument.
Syntax:-Min(Col_name);
select min(salary) from instructor;
MIN(SALARY)
35000
C] Max Function :-
To perform an operation which gives the maximum of a set of values it is used.
Syntax:-Max(Col_name);
select max(salary) from instructor;
MAX(SALARY)
62000
D] Sum Function:-
Sum Function can be used to obtain the sum of range of values of record set .
Syntax:-Sum(Col_name);
select salary from instructor;
SALARY
40000
60000
62000
35000
select sum(salary) from instructor;
SUM(SALARY)
197000
Grouping Rows with Group By Clause
To this point, we've used aggregate functions to summarize all the values in a column or
just those values that matched a WHERE search condition. You can use the GROUP BY
clause to divide a table into logical groups (categories) and calculate aggregate statistics for
each group.
The GROUP BY clause's important characteristics are:
1 The GROUP BY clause comes after the WHERE clause and before the ORDER BY
clause.
1 No columns from the input table can appear in an aggregate query's SELECT clause
unless they're also included in the GROUP BY clause. A column can have different
values in different rows, so there's no way to decide which of these values to include
in the result if you're generating a single new row from the table as a whole.
A GROUP BY clause, which groups rows, often is used with a HAVING clause,
which filters groups.
Problem:
1] Retrieve how many no. of employees are working as a salesman in each department.
2] Retrieve those deptno. For which sum of salary of that dept. employees is greater than
10000.
3] retrieve those dept. for which more than one emp are as a analyst.
4] select count of employees in each dept. for which count is greater than 3.
Conclusion :
Hence we have studied Numeric, Character, Aggregate Functions and Group by
functions.
Date of performance by Date of Assessment by Staff Signature Remark
Student Staff
