PCE1tJJc

1mysql> CREATE TABLE IF NOT EXISTS students
2 -> ( admno integer NOT NULL PRIMARY KEY ,
3 -> name char(30) NOT NULL ,
4 -> libcardno integer NOT NULL UNIQUE ,
5 -> sex char(1) NOT NULL ,
6 -> fees decimal CHECK (fees>1000) ,
7 -> class integer NOT NULL ,
8 -> section char(2) NOT NULL
9 -> ) ;
10Query OK, 0 rows affected (0.22 sec)
11mysql> ALTER TABLE students
12 -> ADD percent DECIMAL;
13Query OK, 0 rows affected (0.20 sec)
14mysql> CREATE TABLE IF NOT EXISTS student2
15 -> ( admno integer NOT NULL PRIMARY KEY ,
16 -> name char(30) NOT NULL ,
17 -> libcardno integer NOT NULL UNIQUE ,
18 -> sex char(1) NOT NULL ,
19 -> fees decimal CHECK (fees>1000) ,
20 -> class integer NOT NULL ,
21 -> section char(2) NOT NULL,
22 -> percent DECIMAL
23 -> ) ;
24Query OK, 0 rows affected (0.06 sec)
25mysql> INSERT INTO students
26 -> (admno, name, libcardno, sex, fees, class, section, percent )
27 -> VALUES
28 -> ('1', 'Ajay', '1', 'M', '5000', '12', 'F', '86' ),
29 -> ('2', 'Suresh', '2', 'M', '4500', '12', 'A', '76' ),
30 -> ('3', 'Rhea', '3', 'F', '4500', '11', 'B', '45' ),
31 -> ('4', 'Charmi', '4', 'F', '5000', '11', 'C', '59' ),
32 -> ('5', 'Jamal', '5', 'M', '3200', '10', 'D', '97' ),
33 -> ('6', 'Lily', '6', 'F', '1000', '9', 'E', '95' ),
34 -> ('7', 'Fatima', '7', 'F', '6300', '8', 'G', '32' );
35Query OK, 7 rows affected (0.03 sec)
36Records: 7 Duplicates: 0 Warnings: 0
37mysql> SELECT * FROM students;
38+-------+--------+-----------+-----+------+-------+---------+---------+
39| admno | name | libcardno | sex | fees | class | section | percent |
40+-------+--------+-----------+-----+------+-------+---------+---------+
41| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
42| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
43| 3 | Rhea | 3 | F | 4500 | 11 | B | 45 |
44| 4 | Charmi | 4 | F | 5000 | 11 | C | 59 |
45| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
46| 6 | Lily | 6 | F | 1000 | 9 | E | 95 |
47| 7 | Fatima | 7 | F | 6300 | 8 | G | 32 |
48+-------+--------+-----------+-----+------+-------+---------+---------+
497 rows in set (0.00 sec)
50mysql> SELECT * FROM students ORDER BY percent;
51+-------+--------+-----------+-----+------+-------+---------+---------+
52| admno | name | libcardno | sex | fees | class | section | percent |
53+-------+--------+-----------+-----+------+-------+---------+---------+
54| 7 | Fatima | 7 | F | 6300 | 8 | G | 32 |
55| 3 | Rhea | 3 | F | 4500 | 11 | B | 45 |
56| 4 | Charmi | 4 | F | 5000 | 11 | C | 59 |
57| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
58| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
59| 6 | Lily | 6 | F | 1000 | 9 | E | 95 |
60| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
61+-------+--------+-----------+-----+------+-------+---------+---------+
627 rows in set (0.00 sec)
63mysql> SELECT DISTINCT fees FROM students;
64+------+
65| fees |
66+------+
67| 5000 |
68| 4500 |
69| 3200 |
70| 1000 |
71| 6300 |
72+------+
735 rows in set (0.03 sec)
74mysql> SELECT * FROM students WHERE section >= 'C';
75+-------+--------+-----------+-----+------+-------+---------+---------+
76| admno | name | libcardno | sex | fees | class | section | percent |
77+-------+--------+-----------+-----+------+-------+---------+---------+
78| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
79| 4 | Charmi | 4 | F | 5000 | 11 | C | 59 |
80| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
81| 6 | Lily | 6 | F | 1000 | 9 | E | 95 |
82| 7 | Fatima | 7 | F | 6300 | 8 | G | 32 |
83+-------+--------+-----------+-----+------+-------+---------+---------+
845 rows in set (0.02 sec)
85mysql> SELECT * FROM students WHERE (NOT sex = 'M' AND (fees >= '5000' OR admno <> '4'));
86+-------+--------+-----------+-----+------+-------+---------+---------+
87| admno | name | libcardno | sex | fees | class | section | percent |
88+-------+--------+-----------+-----+------+-------+---------+---------+
89| 3 | Rhea | 3 | F | 4500 | 11 | B | 45 |
90| 4 | Charmi | 4 | F | 5000 | 11 | C | 59 |
91| 6 | Lily | 6 | F | 1000 | 9 | E | 95 |
92| 7 | Fatima | 7 | F | 6300 | 8 | G | 32 |
93+-------+--------+-----------+-----+------+-------+---------+---------+
944 rows in set (0.00 sec)
95mysql> SELECT * FROM students WHERE fees BETWEEN '3200' AND '5000' ORDER BY section;
96+-------+--------+-----------+-----+------+-------+---------+---------+
97| admno | name | libcardno | sex | fees | class | section | percent |
98+-------+--------+-----------+-----+------+-------+---------+---------+
99| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
100| 3 | Rhea | 3 | F | 4500 | 11 | B | 45 |
101| 4 | Charmi | 4 | F | 5000 | 11 | C | 59 |
102| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
103| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
104+-------+--------+-----------+-----+------+-------+---------+---------+
1055 rows in set (0.00 sec)
106mysql> SELECT * FROM students WHERE fees NOT BETWEEN '3200' AND '5000' ORDER BY percent;
107+-------+--------+-----------+-----+------+-------+---------+---------+
108| admno | name | libcardno | sex | fees | class | section | percent |
109+-------+--------+-----------+-----+------+-------+---------+---------+
110| 7 | Fatima | 7 | F | 6300 | 8 | G | 32 |
111| 6 | Lily | 6 | F | 1000 | 9 | E | 95 |
112+-------+--------+-----------+-----+------+-------+---------+---------+
1132 rows in set (0.00 sec)
114mysql> SELECT * FROM students WHERE ((name LIKE "%i%" OR fees LIKE "_00_" ) AND fees NOT LIKE
115"_30_");
116+-------+--------+-----------+-----+------+-------+---------+---------+
117| admno | name | libcardno | sex | fees | class | section | percent |
118+-------+--------+-----------+-----+------+-------+---------+---------+
119| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
120| 4 | Charmi | 4 | F | 5000 | 11 | C | 59 |
121| 6 | Lily | 6 | F | 1000 | 9 | E | 95 |
122+-------+--------+-----------+-----+------+-------+---------+---------+
1233 rows in set (0.00 sec)
124mysql> SELECT * FROM students WHERE percent IS NULL;
125Empty set (0.00 sec)
126mysql> SELECT 5*4 FROM dual;
127+-----+
128| 5*4 |
129+-----+
130| 20 |
131+-----+
1321 row in set (0.00 sec)
133mysql> SELECT SUM(percent) FROM students WHERE class = '12' OR '11';
134+--------------+
135| SUM(percent) |
136+--------------+
137| 490 |
138+--------------+
1391 row in set (0.02 sec)
140mysql> SELECT AVG(percent) FROM students WHERE fees = '5000';
141+--------------+
142| AVG(percent) |
143+--------------+
144| 72.5000 |
145+--------------+
1461 row in set (0.00 sec)
147mysql> SELECT name, COUNT(*) FROM students GROUP BY class HAVING COUNT(*)<2;
148+--------+----------+
149| name | COUNT(*) |
150+--------+----------+
151| Fatima | 1 |
152| Lily | 1 |
153| Jamal | 1 |
154+--------+----------+
1553 rows in set (0.00 sec)
156mysql> SELECT name, 'has percentage', percent, '%' FROM students ORDER BY PERCENT;
157+--------+----------------+---------+---+
158| name | has percentage | percent | % |
159+--------+----------------+---------+---+
160| Fatima | has percentage | 32 | % |
161| Rhea | has percentage | 45 | % |
162| Charmi | has percentage | 59 | % |
163| Suresh | has percentage | 76 | % |
164| Ajay | has percentage | 86 | % |
165| Lily | has percentage | 95 | % |
166| Jamal | has percentage | 97 | % |
167+--------+----------------+---------+---+
168SELECT admno, name, sex, class, percent INTO poor
169FROM students
170WHERE percent < '80';
1717 rows in set (0.00 sec)
172mysql> CREATE TABLE scorer AS
173 -> (SELECT admno, name, sex, class, percent
174 -> FROM students
175 -> WHERE percent > '80');
176Query OK, 3 rows affected (0.09 sec)
177Records: 3 Duplicates: 0 Warnings: 0
178mysql> SELECT * FROM scorer;
179+-------+-------+-----+-------+---------+
180| admno | name | sex | class | percent |
181+-------+-------+-----+-------+---------+
182| 1 | Ajay | M | 12 | 86 |
183| 5 | Jamal | M | 10 | 97 |
184| 6 | Lily | F | 9 | 95 |
185+-------+-------+-----+-------+---------+
1863 rows in set (0.00 sec)
187mysql> INSERT INTO student2
188 -> SELECT * FROM students
189 -> WHERE fees > 1000;
190Query OK, 6 rows affected (0.03 sec)
191Records: 6 Duplicates: 0 Warnings: 0
192mysql> SELECT * FROM student2;
193+-------+--------+-----------+-----+------+-------+---------+---------+
194| admno | name | libcardno | sex | fees | class | section | percent |
195+-------+--------+-----------+-----+------+-------+---------+---------+
196| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
197| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
198| 3 | Rhea | 3 | F | 4500 | 11 | B | 45 |
199| 4 | Charmi | 4 | F | 5000 | 11 | C | 59 |
200| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
201| 7 | Fatima | 7 | F | 6300 | 8 | G | 32 |
202+-------+--------+-----------+-----+------+-------+---------+---------+
2036 rows in set (0.00 sec)
204mysql> DELETE FROM student2
205 -> WHERE percent < 60;
206Query OK, 3 rows affected (0.03 sec)
207mysql> SELECT * FROM student2;
208+-------+--------+-----------+-----+------+-------+---------+---------+
209| admno | name | libcardno | sex | fees | class | section | percent |
210+-------+--------+-----------+-----+------+-------+---------+---------+
211| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
212| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
213| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
214+-------+--------+-----------+-----+------+-------+---------+---------+
2153 rows in set (0.00 sec)
216mysql> UPDATE students
217 -> set admno = admno*7
218 -> WHERE (percent < '60' OR fees <'1500');
219Query OK, 4 rows affected (0.03 sec)
220Rows matched: 4 Changed: 4 Warnings: 0
221mysql> SELECT * FROM STUDENTS;
222+-------+--------+-----------+-----+------+-------+---------+---------+
223| admno | name | libcardno | sex | fees | class | section | percent |
224+-------+--------+-----------+-----+------+-------+---------+---------+
225| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
226| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
227| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
228| 21 | Rhea | 3 | F | 4500 | 11 | B | 45 |
229| 28 | Charmi | 4 | F | 5000 | 11 | C | 59 |
230| 42 | Lily | 6 | F | 1000 | 9 | E | 95 |
231| 49 | Fatima | 7 | F | 6300 | 8 | G | 32 |
232+-------+--------+-----------+-----+------+-------+---------+---------+
2337 rows in set (0.00 sec)
234AS SELECT * FROM stude' at line 1
235mysql> CREATE VIEW board (admno, name, rno)
236 -> AS SELECT admno, name, libcardno*42
237 -> FROM students
238 -> where admno > '30';
239Query OK, 0 rows affected (0.02 sec)
240mysql> SELECT * FROM board;
241+-------+--------+-----+
242| admno | name | rno |
243+-------+--------+-----+
244| 42 | Lily | 252 |
245| 49 | Fatima | 294 |
246+-------+--------+-----+
2472 rows in set (0.00 sec)
248mysql> SELECT upper("hello") FROM dual;
249+----------------+
250| upper("hello") |
251+----------------+
252| HELLO |
253+----------------+
254mysql> SELECT substr("Pointer", 3, 2) FROM dual;
255+-------------------------+
256| substr("Pointer", 3, 2) |
257+-------------------------+
258| in |
259+-------------------------+
2601 row in set (0.00 sec)
261mysql> SELECT * FROM students
262 -> UNION
263 -> SELECT * FROM student2;
264+-------+--------+-----------+-----+------+-------+---------+---------+
265| admno | name | libcardno | sex | fees | class | section | percent |
266+-------+--------+-----------+-----+------+-------+---------+---------+
267| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
268| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
269| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
270| 21 | Rhea | 3 | F | 4500 | 11 | B | 45 |
271| 28 | Charmi | 4 | F | 5000 | 11 | C | 59 |
272| 42 | Lily | 6 | F | 1000 | 9 | E | 95 |
273| 49 | Fatima | 7 | F | 6300 | 8 | G | 32 |
274+-------+--------+-----------+-----+------+-------+---------+---------+
2757 rows in set (0.01 sec)
276mysql> SELECT * FROM students
277 -> UNION ALL
278 -> SELECT * FROM student2;
279+-------+--------+-----------+-----+------+-------+---------+---------+
280| admno | name | libcardno | sex | fees | class | section | percent |
281+-------+--------+-----------+-----+------+-------+---------+---------+
282| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
283| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
284| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
285| 21 | Rhea | 3 | F | 4500 | 11 | B | 45 |
286| 28 | Charmi | 4 | F | 5000 | 11 | C | 59 |
287| 42 | Lily | 6 | F | 1000 | 9 | E | 95 |
288| 49 | Fatima | 7 | F | 6300 | 8 | G | 32 |
289| 1 | Ajay | 1 | M | 5000 | 12 | F | 86 |
290| 2 | Suresh | 2 | M | 4500 | 12 | A | 76 |
291| 5 | Jamal | 5 | M | 3200 | 10 | D | 97 |
292+-------+--------+-----------+-----+------+-------+---------+---------+
29310 rows in set (0.00 sec)
294mysql> DELETE FROM student2;
295Query OK, 3 rows affected (0.02 sec)
296mysql> DROP TABLE student2;
297Query OK, 0 rows affected (0.03 sec)