Programme : M.

S SE Semester : Summer I - 2018

Database Management Systems
Course : Code : SWE304
Lab
Faculty : Prof. M. Premalatha Slot : L1+L4+L5+L8+L9+L11

Ex. No. 2

SQL- Aggregate Functions and Built In Functions

1. Find the total books in terms of quantity

2. Count the number of books wrote by each author
3. Find the average cost of all the books
4. Count the number of java books
5. Add 1005, Java 123, 32.22, 21 into books relation
6. Add 1006, Java ABC, 22.12, 21 into books relation
7. Add 4, 1005 into book_authors relation
8. Add 3, 1006 into book_authors relation
 9. Count the number of books by each author if the count is > 2
10. Find the total cost of books for each title
11. Find the total cost of books for each title if the cost is >11
12. Find the number of books for each title which has <22 as cost
13. Find the number of authors who has character ‘t’ in their name.
14. Display the first 3 characters in their names for each author
15. List the number of characters present in the author names
16. Display the cost with no decimal points
17. Consider a date (dd/mmm/yyyy) and display the month after
3 months from the given date.
18. Display the email id of authors in caps

Aggregation Function in SQL

AVG MIN
CHECKSUM_AGG SUM
COUNT STDEV
COUNT_BIG STDEVP
GROUPING VAR
GROUPING_ID VARP
MAX

Built-in Functions in SQL

Numberic Functions

Function Input Argument Value Returned

ABS ( m ) m = value Absolute value of m
MOD ( m, n ) m = value, n = divisor Remainder of m divided by n
POWER ( m,
m = value, n = exponent m raised to the nth power
n)
ROUND ( m [, m = value, n = number of decimal
m rounded to the nth decimal place
n]) places, default 0
TRUNC ( m [, m = value, n = number of decimal
m truncated to the nth decimal place
n]) places, default 0
SIN ( n ) n = angle expressed in radians sine (n)
COS ( n ) n = angle expressed in radians cosine (n)
TAN ( n ) n = angle expressed in radians tan (n)
ASIN ( n ) n is in the range -1 to +1 arc sine of n in the range -π/2 to +π/2
ACOS ( n ) n is in the range -1 to +1 arc cosine of n in the range 0 to π
ATAN ( n ) n is unbounded arc tangent of n in the range -π/2 to + π/2
SINH ( n ) n = value hyperbolic sine of n
COSH ( n ) n = value hyperbolic cosine of n
TANH ( n ) n = value hyperbolic tangent of n
SQRT ( n ) n = value positive square root of n
EXP ( n ) n = value e raised to the power n
LN ( n ) n>0 natural logarithm of n
base n2 any positive value other than 0
LOG ( n2, n1 ) logarithm of n1, base n2
or 1, n1 any positive value
CEIL ( n ) n = value smallest integer greater than or equal to n
FLOOR ( n ) n = value greatest integer smaller than or equal to n
SIGN ( n ) n = value -1 if n < 0, 0 if n = 0, and 1 if n > 0

Here are some examples of the use of some of these numeric functions:

select round (83.28749, 2) from dual;

select sqrt (3.67) from dual;

select power (2.512, 5) from dual;

String Functions

Function Input Argument Value Returned

First letter of each word is changed to
INITCAP ( s ) s = character string uppercase and all other letters are in
lower case.
LOWER ( s ) s = character string All letters are changed to lowercase.
UPPER ( s ) s = character string All letters are changed to uppercase.
Concatenation of s1 and s2. Equivalent
CONCAT ( s1, s2 ) s1 and s2 are character strings
to s1 || s2
LPAD ( s1, n [, s2] ) s1 and s2 are character strings and n Returns s1 right justified and padded
 is an integer value left with n characters from s2; s2
defaults to space.
Returns s1 left justified and padded
s1 and s2 are character strings and n
RPAD ( s1, n [, s2] ) right with n characters from s2; s2
is an integer value
defaults to space.
Returns s with characters removed up
s is a character string and set is a set
LTRIM ( s [, set ] ) to the first character not in set; defaults
of characters
to space
Returns s with final characters removed
s is a character string and set is a set
RTRIM ( s [, set ] ) after the last character not in set;
of characters
defaults to space
REPLACE ( s, s = character string, search_s = Returns s with every occurrence of
search_s [, replace_s target string, replace_s = search_s in s replaced by replace_s;
]) replacement string default removes search_s
Returns a substring from s, beginning
SUBSTR ( s, m [, n ] s = character string, m = beginning
in position m and n characters long;
) position, n = number of characters
default returns to end of s.
LENGTH ( s ) s = character string Returns the number of characters in s.
s1 and s2 are character strings, m = Returns the position of the nth
INSTR ( s1, s2 [, m
beginning position, n = occurrence occurrence of s2 in s1, beginning at
[, n ] ] )
of s2 in s1 position m, both m and n default to 1.

Here are some examples of the use of String functions:

select concat ('Alan', 'Turing') as "NAME" from dual;

select 'Alan' || 'Turing' as "NAME" from dual;

select initcap ("now is the time for all good men to come to the aid of the
party") as "SLOGAN" from dual;

select substr ('Alan Turing', 1, 4) as "FIRST" from dual;

String / Number Conversion Functions

Function Input Argument Value Returned

NANVL ( n2, n1 ) n1, n2 = value if (n2 = NaN) returns n1 else returns n2
m = numeric value, Number m converted to character string as
TO_CHAR ( m [, fmt ] )
fmt = format specified by the format
TO_NUMBER ( s [, fmt ] s = character string, Character string s converted to a number as
) fmt = format specified by the format
Formats for TO_CHAR Function

Symbol Explanation
9 Each 9 represents one digit in the result
0 Represents a leading zero to be displayed
$ Floating dollar sign printed to the left of number
L Any local floating currency symbol
. Prints the decimal point
, Prints the comma to represent thousands

Group Functions

Function Input Argument Value Returned

AVG ( [ DISTINCT |
col = column name The average value of that column
ALL ] col )
Number of rows returned including
COUNT ( * ) none
duplicates and NULLs
COUNT ( [ DISTINCT | Number of rows where the value of the
col = column name
ALL ] col ) column is not NULL
MAX ( [ DISTINCT |
col = column name Maximum value in the column
ALL ] col )
MIN ( [ DISTINCT | ALL
col = column name Minimum value in the column
] col )
SUM ( [ DISTINCT |
col = column name Sum of the values in the column
ALL ] col )
e1 and e2 are column Correlation coefficient between the two
CORR ( e1, e2 )
names columns after eliminating nulls
Middle value in the sorted column,
MEDIAN ( col ) col = column name
interpolating if necessary
STDDEV ( [ DISTINCT | Standard deviation of the column ignoring
col = column name
ALL ] col ) NULL values
VARIANCE ( [ Variance of the column ignoring NULL
col = column name
DISTINCT | ALL ] col ) values

Date and Time Functions

Function Input Argument Value Returned

ADD_MONTHS ( d, n ) d = date, n = number of months Date d plus n months
LAST_DAY ( d ) d = date Date of the last day of the
 month containing d
MONTHS_BETWEEN ( Number of months by which e
d and e are dates
d, e ) precedes d
d = date, a = time zone (char), b = The date and time in time zone
NEW_TIME ( d, a, b )
time zone (char) b when date d is for time zone a
Date of the first day of the week
NEXT_DAY ( d, day ) d = date, day = day of the week
after d
SYSDATE none Current date and time
GREATEST ( d1, d2, ...,
d1 ... dn = list of dates Latest of the given dates
dn )
LEAST ( d1, d2, ..., dn ) d1 ... dn = list of dates Earliest of the given dates

Date Conversion Functions

Function Input Argument Value Returned

d = date value, fmt = format for The date d converted to a string
TO_CHAR ( d [, fmt ] )
string in the given format
s = character string, fmt = format for
TO_DATE ( s [, fmt ] ) String s converted to a date value
date
d = date value, fmt = format for Date d rounded as specified by
ROUND ( d [, fmt ] )
string the format
d = date value, fmt = format for Date d truncated as specified by
TRUNC ( d [, fmt ] )
string the format

Date Formats

Format Code Description Range of Values

DD Day of the month 1 - 31
DY Name of the day in 3 uppercase letters SUN, ..., SAT
Complete name of the day in uppercase,
DAY SUNDAY, ..., SATURDAY
padded to 9 characters
MM Number of the month 1 - 12
MON Name of the month in 3 uppercase letters JAN, ..., DEC
Name of the month in uppercase padded to a JANUARY, ...,
MONTH
length of 9 characters DECEMBER
RM Roman numeral for the month I, ..., XII
YY or YYYY Two or four digit year 71 or 1971
HH:MI:SS Hours : Minutes : Seconds 10:28:53
HH 12 or HH
Hour displayed in 12 or 24 hour format 1 - 12 or 1 - 24
24
MI Minutes of the hour 0 - 59
SS Seconds of the minute 0 - 59
AM or PM Meridian indicator AM or PM
A suffix that forces the number to be spelled e.g. TWO THOUSAND
SP
out. NINE
A suffix meaning that the ordinal number is
TH e.g. 1st, 2nd, 3rd, ...
to be added
Prefix to DAY or MONTH or YEAR to e.g. MONDAY with no
FM
suppress padding extra spaces at the end

Here are some examples of the use of the Date functions:

select to_char ( sysdate, 'MON DD, YYYY' ) from dual;

select to_char ( sysdate, 'HH12:MI:SS AM' ) from dual;

select to_char ( new_time ( sysdate, 'CDT', 'GMT'), 'HH24:MI' ) from dual;

select greatest ( to_date ( 'JAN 19, 2000', 'MON DD, YYYY' ),

to_date ( 'SEP 27, 1999', 'MON DD, YYYY' ),
to_date ( '13-Mar-2009', 'DD-Mon-YYYY' ) )
from dual;

select next_day ( sysdate, 'FRIDAY' ) from dual;

select last_day ( add_months ( sysdate, 1 ) ) from dual;