-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmysql
187 lines (158 loc) · 5.53 KB
/
mysql
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
create database school;
use school;
create table student(adm_no int primary key,name varchar(100),avg int,sex varchar(100),s_code int);
insert into student values(501,'R.Jain',98,'M',111);
insert into student values(545,'Kavitha',73,'F',333);
insert into student values(754,'Rahul Goel',60,'M',444);
insert into student values(892,'Sahil Jain',78,'M',333);
insert into student values(935,'Rohan Saini',85,'M',222);
insert into student values(955,'Anjali',64,'F',444);
insert into student values(983,'Sneha Aggarwal',80,'F',222);
1) select * from student;
+--------+----------------+------+------+--------+
| adm_no | name | avg | sex | s_code |
+--------+----------------+------+------+--------+
| 501 | R.Jain | 98 | M | 111 |
| 545 | Kavitha | 73 | F | 333 |
| 705 | K.Rashika | 85 | F | 111 |
| 754 | Rahul Goel | 60 | M | 444 |
| 892 | Sahil Jain | 78 | M | 333 |
| 935 | Rohan Saini | 85 | M | 222 |
| 955 | Anjali | 64 | F | 444 |
| 983 | Sneha Aggarwal | 80 | F | 222 |
+--------+----------------+------+------+--------+
2) select * from student where(name="Rohan Saini");
+--------+-------------+------+------+--------+
| adm_no | name | avg | sex | s_code |
+--------+-------------+------+------+--------+
| 935 | Rohan Saini | 85 | M | 222 |
+--------+-------------+------+------+--------+
3) select count(name) from student;
+-------------+
| count(name) |
+-------------+
| 8 |
+-------------+
4) select count(sex) from student where(sex="M");
+------------+
| count(sex) |
+------------+
| 4 |
+------------+
4) select count(sex) from student where(sex="F");
+------------+
| count(sex) |
+------------+
| 4 |
+------------+
5) select * from student order by name;
+--------+----------------+------+------+--------+
| adm_no | name | avg | sex | s_code |
+--------+----------------+------+------+--------+
| 955 | Anjali | 64 | F | 444 |
| 705 | K.Rashika | 85 | F | 111 |
| 545 | Kavitha | 73 | F | 333 |
| 501 | R.Jain | 98 | M | 111 |
| 754 | Rahul Goel | 60 | M | 444 |
| 935 | Rohan Saini | 85 | M | 222 |
| 892 | Sahil Jain | 78 | M | 333 |
| 983 | Sneha Aggarwal | 80 | F | 222 |
+--------+----------------+------+------+--------+
6) select * from student order by avg desc;
+--------+----------------+------+------+--------+
| adm_no | name | avg | sex | s_code |
+--------+----------------+------+------+--------+
| 501 | R.Jain | 98 | M | 111 |
| 705 | K.Rashika | 85 | F | 111 |
| 935 | Rohan Saini | 85 | M | 222 |
| 983 | Sneha Aggarwal | 80 | F | 222 |
| 892 | Sahil Jain | 78 | M | 333 |
| 545 | Kavitha | 73 | F | 333 |
| 955 | Anjali | 64 | F | 444 |
| 754 | Rahul Goel | 60 | M | 444 |
+--------+----------------+------+------+--------+
7) select name from student where name like 'K%';
+-----------+
| name |
+-----------+
| Kavitha |
| K.Rashika |
+-----------+
8) select name from student where name like '%i';
+-------------+
| name |
+-------------+
| Rohan Saini |
| Anjali |
+-------------+
9) select adm_no,name,avg*5 as total_marks from student;
+--------+----------------+-------------+
| adm_no | name | total_marks |
+--------+----------------+-------------+
| 501 | R.Jain | 490 |
| 545 | Kavitha | 365 |
| 705 | K.Rashika | 425 |
| 754 | Rahul Goel | 300 |
| 892 | Sahil Jain | 390 |
| 935 | Rohan Saini | 425 |
| 955 | Anjali | 320 |
| 983 | Sneha Aggarwal | 400 |
+--------+----------------+-------------+
10) select * from student where avg between 80 and 90;
+--------+----------------+------+------+--------+
| adm_no | name | avg | sex | s_code |
+--------+----------------+------+------+--------+
| 705 | K.Rashika | 85 | F | 111 |
| 935 | Rohan Saini | 85 | M | 222 |
| 983 | Sneha Aggarwal | 80 | F | 222 |
+--------+----------------+------+------+--------+
11) select * from student where avg>80 and s_code=333;
Empty set (0.00 sec)
12) select name,avg from student where s_code=222 or s_code=333;
+----------------+------+
| name | avg |
+----------------+------+
| Kavitha | 73 |
| Sahil Jain | 78 |
| Rohan Saini | 85 |
| Sneha Aggarwal | 80 |
+----------------+------+
13) select sum(avg) from student;
+----------+
| sum(avg) |
+----------+
| 623 |
+----------+
14) select max(avg) from student;
+----------+
| max(avg) |
+----------+
| 98 |
+----------+
15) select min(avg) from student;
+----------+
| min(avg) |
+----------+
| 60 |
+----------+
16) select avg(avg) from student;
+----------+
| avg(avg) |
+----------+
| 77.8750 |
+----------+
17) select max(avg),min(avg),sum(avg) from student where s_code=333;
+----------+----------+----------+
| max(avg) | min(avg) | sum(avg) |
+----------+----------+----------+
| 78 | 73 | 151 |
+----------+----------+----------+
18) select s_code,count(name) from student group by s_code;
+--------+-------------+
| s_code | count(name) |
+--------+-------------+
| 111 | 2 |
| 333 | 2 |
| 444 | 2 |
| 222 | 2 |
+--------+-------------+