SQL FUNCTION:
Functions are the objects in sql
which take one or more input from user take operation on input and produce one
or more output
SQL PROVIDE TWO TYPES OF FUNCTIONS
AGGREGATE FUNCTION/GROUP
FUNCTION/MULTI ROW FUNCTION
Aggregate functions works on record of table and produce single output
that means if your table contains N number of records then it produces single
output.
TYPES OF SINGLE ROW FUNCTION
Character function
Number function
Date function
Conversion function
General function
Time stamp function
TYPES OF MULTI ROW FUNCTIONS
Count()
Sum()
Avg()
Max()
Min()
Stddev()
Varience()
1.Desc emp;
EID NUMBER
ENAME VARCHAR(10)
ESAL NUMBER
EADD VARCHAR(10)
2.SELECT * FROM EMP;
EID ENAME ESAL EADD
10 ankist 25000 jemshedpur
20 akhilesh 15000 jemshedpur
30 anish 20000 lakhnow
Ankush 35000 ballari
3. Select count(EID) from emp;
Output:
Count(EID)
3
4. Select count(*) from emp;
Count(*)
4
5. Select max(esal) from emp;
OUTPUT:
MAX(ESAL)
35000
6. Select min(esal) from emp;
OUTPUT:
MIN(ESAL)
15000
7.Select sum(ESAL) from emp;
OUTPUT:
SUM(ESAL)
95000
8. Select avg(ESAL) from emp;
OUTPUT:
AVG(ESAL)
23750
GROUP BY CLAUSE
Group by clause form a group on the basis of similar types data and
produce a separate table.
Note: the group by clause perform its
operation in buffer not in database
EMP TABLE:
|
EID |
DNAME |
ESAL |
|
1 |
sale |
10000 |
|
2
|
hr |
15000 |
|
3
|
sale |
20000 |
|
4
|
sale |
10000 |
|
5 |
hr |
23000 |
|
6 |
product |
25000 |
select dname,count(*) from emp where esal<=20000 group by dname;
9. select dname from emp group by dname;
OUTPUT:
DNAME
SALE
HR
PRODUCT
10. select dname,count(*) from emp group by dname;
OUTPUT:
DNAME COUNT(*)
SALE 4
HR 2
PRODUCT 1
11.select dname,count(*) from emp where
esal<=20000 from group by dname;
OUTPUT:
DNAME COUNT(*)
SALE 3
HR 1
12. select dname, max(esal) from
emp group by dname;
OUTPUT:
DNAME MAX(ESAL)
SALE 25000
HR 23000
PRODUCT 25000
13. select dname, min(esal) from
emp group by dname;
OUTPUT:
DNAME MAX(ESAL)
SALE 10000
HR 15000
PRODUCT 25000
14. select dname, sum(esal),
avg(esal) from emp group by dname;
OUTPUT:
DNAME SUM(ESAL) AVG(ESAL)
SALE 65000 16250
HR 38000 19000
PRODUCT 25000 25000
HAVING CLAUSE
Having clause retrieving the group
Having clause will always use with group by clause and evaluated after
forming groups
It eliminates the non matching groups
15.select * from emp;
|
EID |
ENAME |
ESAL |
|
1 |
Sale |
10000 |
|
2 |
Hr |
15000 |
|
3 |
sale |
20000 |
|
4 |
Sale |
10000 |
|
5 |
Hr |
23000 |
|
6 |
Product |
25000 |
|
7 |
Sale |
25000 |
|
8 |
Product |
12000 |
|
9 |
sale |
18000 |
|
10 |
Sale |
37000 |
|
11 |
Hr |
63000 |
16. select dname from emp group by dname;
OUTPUT:
DNAME
SALE
HR
PRODUCT
17. select dname,count(*) from emp
group by dname;
OUTPUT:
DNAME COUNT(*)
SALE 6
HR 3
PRODUCT 2
18. select dname, count(*) from emp
having count(*) >=30000 group by dname;
OUTPUT:
DNAME COUNT(*)
SALE 37000
HR 63000
19. select dname, esal count(*) from
emp having esal >=25000 group by dname, esal;
OUTPUT:
DNAME ESAL COUNT(*)
PRODUCT 25000 1
SALE 37000 1
HR 63000 1
SALE 25000 1
20. select dname, esal, count(*) from emp where esal>20000 having sum(esal)
>=25000 group by dname, order by dname;
OUTPUT:
DNAME ESAL COUNT(*)
HR 63000 1
PRODUCT 25000 1
SALE 25000 1
SALE 37000 1
PRIORITY OF CLAUSE
WHERE
GROUP BY
HAVING
ORDER BY
ORDER BY CLAUSE
We use order by clause to arrange the
records of one or more columns either ascending or descending order
Note: by default it sorts the records
in ascending order
SYNTAX:
21. select * from tablename order by
columnname asc/desc;
select * form emp;
EID ENAME
EINCOME
DOJ
10 ANKIT 20000 15-AUG-2000
20 SURAJ 10000 22-SEP-2000
30 AKHILESH 15000 29-JAN-2000
15 ANISH 20000 15-AUG-2000
25 RAJU 5000 01-JAN-1975
22. select * form emp order by eid asc;
OUTPUT:
EID ENAME
EINCOME
DOJ
10 ANKIT 20000 15-AUG-2000
15 ANISH 20000 15-AUG-2000
20 SURAJ 10000 22-SEP-2000
30 AKHILESH 15000 29-JAN-2000
25 RAJU 5000 01-JAN-1975
30 AKHILESH 15000 29-JAN-2000
23. select * form emp order by ename desc;
20 SURAJ 10000 22-SEP-2000
25 RAJU 5000 01-JAN-1975
10 ANKIT 20000 15-AUG-2000
15 ANISH 20000 15-AUG-2000
30 AKHILESH 15000 29-JAN-2000
24. select * form emp order by eincome asc, ename desc;
EINCOME ENAME
5000 SURAJ
10000 RAJU
15000 ANKIT
20000 ANISH
20000 AKHILESH
SINGLE ROW FUNCTION
Single row function accept arguments
and return one value. It ask on each row and return one result per row.
· The single row function can be nested
up to any level
· It can be apply with select, where
and order by clause
· Character function
· Number function
· Date function
· Conversion function
· General function
· Time stamp function
Case manipulation function
· Upper
· Lower
· initcap
STRING MANIPULATION FUNCTION
· Length
· Concat
· Lpad
· Rpad
· Ltrim
· Rtrim
· Trim
· Translate
· Replace
· Substr
· instr
· reverse
Character function: [DUAL IS DUMMY TABLE/BUFFER STORAGE]
Upper:
25.Select upper (‘ankit’) from dual;
Output:
Upper:
ANKIT
26. Select lower (‘ANKUSH’) from dual;
Output:
lower:
ankush
27. Select lower (‘ANKUSH’) as name from dual;
Output:
NAME:
Ankush
Output:
NAME:
Altaf
29. Select length (‘ankush’) from dual;
Output:
LENGTH(‘ANKUSH’)
6
30. Select concat
(‘ankush’,’kumar’) from dual;
Output:
CONCAT (‘ANKUSH’,’KUMAR’)
Ankushkumar
Syntax:
31. Select LPAD(value,size) from
dual;
Select lpad (‘ankush’,10,’*’) from dual;
Output:
LPAD (‘ANKUSH’,’10’,’*’)
****ANKUSH
32. Select rpad
(‘ankush’,10,’*’) from dual;
Output:
RPAD (‘ANKUSH’,’10’,’*’)
ANKUSH****
33. Select ltrim (‘oracle’,’o’) from dual;
Output:
LTRIM (‘ORACLE’,’O’)
racle
34.Select ltrim (‘oracle’,’ora’) from dual;
Output:
LTRIM (‘ORACLE’,’ORA’)
Cle
35. Select rtrim (‘oracle’,’cle’) from dual;
Output:
RTRIM (‘ORACLE’,’cle’)
ora
36. Select rtrim (‘oracle’,’le’) from dual;
Output:
RTRIM (‘ORACLE’,’le’)
Orac
TRIM:
IT TRIM ONLY LEFT AND RIGHT VALUES
37.Select trim (‘o’ from ooooracleoooo’) from dual;
Output:
TRIM (‘o’ from ‘ooooracleoooo’)
racle
TRANSLATE:
IT TAKES 3 PARAMETERS
38. Select translate (‘akhilesh’,
‘isk’,379) from dual;
Output:
TRANLATE(‘akhilesh’, ‘isk’,379)
|
TRANSLATE('AKHILESH','ISK',379) |
|
a9h3le7h |
REPLACE:
IT TAKES 3 PARAMETERS AND IT TAKES THE VALUE IN SEQUENCE
39. Select replace (‘akhilesh’,
‘iles’,379) from dual;
Output:
|
TRANSLATE('AKHILESH','ILES',379) |
|
akh379h |
40.Select replace (‘akhilesh’,
‘iles’,3797) from dual;
Output:
|
TRANSLATE('AKHILESH','ILES',3797) |
|
akh3797h |
41.Select replace (‘akhilesh’,
‘iles’,3) from dual;
Output:
|
TRANSLATE('AKHILESH','ILES',3) |
|
akh3h |
42. Select replace (‘akhilesh’,
‘hsh’,3) from dual;
Output:
It
is not in sequence so same value we get
|
REPLACE('AKHILESH','HSH',3) |
|
akhilesh |
SUBSTR:
(STRING,STARTING VALUE,END VALUE)
43. Select substr (‘ankit kumar’,
3,7) from dual;
Output:
|
SUBSTR('ANKITKUMAR',3,7) |
|
kit kum |
INSTR:
IT TAKES 4 PARAMETERS ,searches the string and return the value position
44. Select instr(‘oracle server’,
‘e’)from dual;
Output:
|
INSTR('ORACLESERVER','E') |
|
6 |
45. Select instr(‘oracle server’,
‘e’,7)from dual;
Output:
|
INSTR('ORACLESERVER','E',7) |
|
9 |
46. Select instr(‘oracle server’, ‘e’,7,2)from
dual;
Output:
|
INSTR('ORACLESERVER','E',7,2) |
|
12 |
REVERSE:IT
REVERSE THE STRING
47.Select reverse(‘akhilesh’)from
dual;
Output:
|
REVERSE('AKHILESH') |
|
hselihka |
No comments:
Post a Comment