-
Notifications
You must be signed in to change notification settings - Fork 58
/
Day-22_SQL_FUNCTIONS_SESSION_2.txt
223 lines (126 loc) · 8.4 KB
/
Day-22_SQL_FUNCTIONS_SESSION_2.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
"Welcome To Ashok IT"
"Oracle Database"
Topic : SQL Functions-Session2
Date : 22/12/2022
(Session - 22)
_____________________________________________________________________________________________________________________________
Important Information
*********************
>> Oracle Class Notes ::: https://github.com/ashokitschool/ORACLE_CLASS_NOTES
>> Class Recording ::: Will be available through Ashok IT Portal
>> Class Related Updates "Join In WhatsApp Group" check with Admin Team.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL Functions
+++++++++++++
1) Single Row Functions
=======================
These Functions will work on every record in the Database table and return more than one value.
******************
a) String Functions
******************
1) length(): This function is used to find the length of given characters which include white spaces
Ex: select length('Welcome To Hyderabad') from dual;
select ename,length(ename) from emp;
2) initcap() : This function is used to convert the first letter of every word into capital letter
Ex: select initcap('Welcome to ashok it hyderabad') from dual;
O/P: Welcome To Ashok It Hyderabad
3) chr(): This function return the high level language code from ASCII Value
Ex: select chr(122) from dual;
O/P :z
4) ascii(): This function return the ascii value form high level language code
Ex: select ascii('a') from dual
5) upper() : This function is used to convert into upper case from given case of letters
Ex: select upper('mahesh') from dual
6) lower(): This function is used to convert into lower case from given case of letters(uppers)
Ex: select lower('MAHESH') form dual;
7) reverse(): This function is used to reverse of given string
Ex: select reverse('Mahesh') from dual;
8) trim(): This function removes the white spaces from both left and right hand side of the given string
Ex: select trim(' Welcomt To Ashok IT Hyderabad ') from dual;
select length(' Welcomt To Ashok IT Hyderabad '),length(trim(' Welcomt To Ashok IT Hyderabad ')) from dual;
9) ltrim(): This function only removes the white spaces form left hand side of given string
Ex: select ltrim(' Welcome To Ashok IT ') from dual
10) rtrim(): This function only removes the white spaces from right hand side of given string
Ex: select rtrim(' Welcome To Ashok IT ') from dual;
11) lpad() : This function add the specificed charaters from the left hand side of the given string
Ex: select lpad('Ashok IT',15,'*') from dual;
12) rpad() : This function add the specificed charaters from the right hand side of the given string
Ex: select rpad('Ashok IT',15,'*') from dual;
13) translate(): This function replaces the given string with another string with only one character
Ex: select translate('JACK','J','B') from dual; O/P: BACK
select translate('JACKJ','J','B') from dual; O/P: BACKB
select translate('JACK','J','HI') from dual; O/P : HACK
14) replace() : This function is replaces the given string with another string and it replaces more than one character
Ex: SELECT REPLACE('This is a test', 'is', 'IS' ) FROM dual; O/P : ThIS IS atest
select replace('Welcome To Hyd','Hyd','Hyderabad')from dual; O/P: Welcome To Hyderabad
15) instr(): This function searches for a substring in a string and returns the position of the substring in a string.
Ex: SELECT INSTR('This is a playlist','is') substring_location FROM dual; O/P :: 3
SELECT INSTR( 'This is a playlist', 'is', 1, 2 ) second_occurrence,
INSTR( 'This is a playlist', 'is', 1, 3 ) third_occurrence FROM dual; O/P: 6 16
SELECT INSTR( 'This is a playlist', 'are' ) substring_location FROM dual; O/P: 0
SELECT INSTR( 'This is a playlist', 'is',-1 ) substring_location FROM dual; O/P:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Math Functions
++++++++++++++
1) abs(): This Function converts the negative number into positive number
Ex: select abs(-19) from dual;
2) round(): This function rounds the given number into nearest integer
Ex: select round(9.5) from dual; O/P: 10
select round(9.4) from dual; O/P: 9
3) floor(): This functions rounds the given number into least integer
Ex: select floor(9.5) from dual; O/P: 9
select floor(9.6) from dual; O/P: 9
select floor(9.9) from dual; O/P: 9
4) ceil() : This function round the given number into maxmimium integer
Ex: select ceil(9.1) from dual; O/P:10
select ceil(9.95) from dual; O/P: 10
5) greatest(): This function return the maximumn value from the list of numbers
Ex: select greatest(5,7,3,2,6,1) from dual O/P:7
select greatest(10.25,25.68,89.47,25.14)as "Max" from dual; O/P:89.47
6) least(): This function return the minimium value from list of numbers
Ex: select least(5,7,3,2,6,1) from dual O/P:1
select least(10.25,25.68,89.47,25.14)as "Max" from dual; O/P:10.25
7) mod(): This function returns remainder values from the given numbers
Ex: select mod(10,2) from dual; O/P: 0
select mod(16,5) from dual; O/p :1
8) sqrt() : This function returns the square root of the given number
Ex: select sqrt(100) from dual; O/P:10
9) power() : This function returns the power of given number
Ex: select power(5,3) from dual; O/P: 125
10) log(): This function returns alogrithmic value fo the given number
Ex: select log(10,10) from dual;
11) sign(): This function returns the sign value of the given number
12) sin(): This functions return sin value of the given number.
13) cos(): This function retuns cos value of the given number.
14) tan(): This function returns tan value of the given number.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Date Functions
==============
1) add_months(): This function adds the given number of months to the given date.
Ex: select sysdate from dual;
select add_months(sysdate,12) from dual;
2) months_between(): This function counts the no of months between two dates
Ex: select months_between(sysdate,'22-DEC-23') from dual;
3) last_day(): This function returns the last day of the given date
Ex: select last_day(sysdate) from dual
4) next_day(): This function returns the next day from the given date
Ex: select next_day(sysdate,'SUNDAY') from dual;
5) extract(): This function extracts the date,month,year from the given date
Ex: select extract(year from sysdate) from dual;
select extract(day from sysdate) from dual;
select extract(month from sysdate) from dual;
Banking Domain >>>>> 01/Jan/2022 - 31/Dec/2022 >>> Hitorical data
RollForward Process
01/Jan/2023 - 31/Dec/2023 >>> Current Data
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Conversion Functions
====================
1) to_char(): This function converts the normal date into character format and the format must be specified in single quotes
Syntax: to_char(date,'FORMAT')
Ex: select to_char(sysdate,'DD/MM/YYYY') from dual;
select to_char(sysdate,'DD-MON-YYYY') from dual;
select to_char(sysdate,'DAY') from dual;
select to_char(sysdate,'MONTH') from dual;
select to_char(sysdate,'DD-MON-YYYY HH:MM::SS') from dual;
select to_char(sysdate,'YYYY') from dual;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++