-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL_Functions_and_GroupBy
168 lines (150 loc) · 6.87 KB
/
SQL_Functions_and_GroupBy
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
----------------------------------------------------------------------------------------------------------------------------------------------
String function:
//for uppercase:
select upper('ramesh') from dual;
//for lowercase:
select lower('ramesh') from dual;
//for capital letter in 1st position:
select initcap('ramesh') from dual;
select upper('ramesh')"Upper Case",lower('ramesh')"Lower Case", initcap('ramesh')"Initial Capital" from dual;
//for finding length of string:
select first_name,length(first_name),upper(first_name),lower(first_name),initcap(first_name) from employees where employee_id between 199 and 206;
//trimming gap:
select length(trim('abc ')),length(ltrim(' abc')),length(rtrim('abc ')) from dual;
//to add 2 strings together:
select concat('abc','xyz') from dual;
select concat(first_name,last_name) from employees;
//check for beginning and ending of string:
SELECT * FROM Emp WHERE name LIKE 's%a';
(returns emp details where name starts with s and ends with a'
"-" is used for character matching:
//to find name with exactly 3 letters
SELECT * FROM Emp WHERE name LIKE '_ _ _';
//to find name with atleast 3 letters
SELECT * FROM Emp WHERE name LIKE '_ _ _%';
//to find name starts with s and followed by exactly 3 letters
SELECT * FROM Emp WHERE name LIKE 's_ _ _';
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate Functions:
//to view total number:
select count(employee_id) from employees where salary=10000;
//to view max value:
//to view min value:
//to view avg:
//to view sum:
//use of all aggregate functions:
select count(department_id),max(salary),min(salary),avg(salary),sum(salary) from employees;
COUNT(DEPARTMENT_ID) MAX(SALARY) MIN(SALARY) AVG(SALARY) SUM(SALARY)
-------------------- ----------- ----------- ----------- -----------
106 24000 2100 6461.68224 691400
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Group by clause:
//select department_id,count(employee_id) from employees group by department_id;
output:
DEPARTMENT_ID COUNT(EMPLOYEE_ID)
------------- ------------------
100 6
30 6
1
90 3
20 2
70 1
110 2
50 45
80 34
40 1
60 5
----------------------------------------------------------------------------------------------------------------------------------------------------------------
//select department_id,count(employee_id) from employees group by department_id order by department_id;
DEPARTMENT_ID COUNT(EMPLOYEE_ID)
------------- ------------------
10 1
20 2
30 6
40 1
50 45
60 5
70 1
80 34
90 3
100 6
110 2
1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
// select department_id,count(employee_id),max(salary),min(salary),avg(salary),sum(salary) from employees group by department_id order by department_id;
DEPARTMENT_ID COUNT(EMPLOYEE_ID) MAX(SALARY) MIN(SALARY) AVG(SALARY) SUM(SALARY)
------------- ------------------ ----------- ----------- ----------- -----------
10 1 4400 4400 4400 4400
20 2 13000 6000 9500 19000
30 6 11000 2500 4150 24900
40 1 6500 6500 6500 6500
50 45 8200 2100 3475.55556 156400
60 5 9000 4200 5760 28800
70 1 10000 10000 10000 10000
80 34 14000 6100 8955.88235 304500
90 3 24000 17000 19333.3333 58000
100 6 12000 6900 8600 51600
110 2 12000 8300 10150 20300
1 7000 7000 7000 7000
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
//select salary,count(employee_id) from employees group by salary order by salary;
SALARY COUNT(EMPLOYEE_ID)
---------- ------------------
2100 1
2200 2
2400 2
2500 6
2600 4
2700 2
2800 4
2900 3
3000 2
3100 4
3200 4
3300 2
3400 1
3500 1
3600 2
3800 1
3900 1
4000 1
4100 1
4200 2
4400 1
4800 2
5800 1
6000 2
6100 1
6200 2
6400 1
6500 2
6800 1
6900 1
7000 3
7200 1
7300 1
7400 1
7500 2
7700 1
7800 1
7900 1
8000 3
8200 2
8300 1
8400 1
8600 1
8800 1
9000 4
9500 3
9600 1
10000 4
10500 2
11000 3
11500 1
12000 3
13000 1
13500 1
14000 1
17000 2
24000 1
===================================================================================================================================================================