MYSQL QUERIES
mysql> create database students;
Query OK, 1 row affected (0.21 sec)
mysql> use students;
Database changed
mysql> create table student
-> (
-> admno int,
-> name varchar(30),
-> class int,
-> sec char,
-> rno int,
-> address varchar(30)
-> );
Query OK, 0 rows affected (1.07 sec)
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| admno | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| class | int | YES | | NULL | |
| sec | char(1) | YES | | NULL | |
| rno | int | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.41 sec)
mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| student |
+--------------------+
1 row in set (0.13 sec)
mysql> insert into student values(1234,"aditi sharma",9,"A",4,"SJE");
Query OK, 1 row affected (0.12 sec)
mysql> insert into student values(2605,"shreya nagpal",10,"D",7,"Jor bagle");
Query OK, 1 row affected (0.10 sec)
mysql> insert into student values(3712,"tanya verma",11,"C",21,"malriya nagar");
Query OK, 1 row affected (0.06 sec)
mysql> insert into student values(6523,"zayu malik",11,"E",40,"Kohini");
Query OK, 1 row affected (0.08 sec)
mysql> insert into student values(9031,"shivani mehta",9,"A",33,"hang khas");
Query OK, 1 row affected (0.09 sec)
mysql> select * from student;
+-------+---------------+-------+------+------+---------------+
| admno | name | class | sec | rno | address |
+-------+---------------+-------+------+------+---------------+
| 1234 | aditi sharma | 9 | A | 4 | SJE |
| 2605 | shreya nagpal | 10 | D | 7 | Jor bagle |
| 3712 | tanya verma | 11 | C | 21 | malriya nagar |
| 6523 | zayu malik | 11 | E | 40 | Kohini |
| 9031 | shivani mehta | 9 | A | 33 | hang khas |
+-------+---------------+-------+------+------+---------------+
5 rows in set (0.03 sec)
mysql> select name,class from student;
+---------------+-------+
| name | class |
+---------------+-------+
| aditi sharma | 9|
| shreya nagpal | 10 |
| tanya verma | 11 |
| zayu malik | 11 |
| shivani mehta | 9|
+---------------+-------+
5 rows in set (0.00 sec)
mysql> select distinct sec from student;
+------+
| sec |
+------+
|A |
|D |
|C |
|E |
+------+
4 rows in set (0.00 sec)
mysql> select name,class from student where class>9;
+---------------+-------+
| name | class |
+---------------+-------+
| shreya nagpal | 10 |
| tanya verma | 11 |
| zayu malik | 11 |
+---------------+-------+
3 rows in set (0.03 sec)
mysql> select * from student order by class,name;
+-------+---------------+-------+------+------+---------------+
| admno | name | class | sec | rno | address |
+-------+---------------+-------+------+------+---------------+
| 1234 | aditi sharma | 9 | A | 4 | SJE |
| 9031 | shivani mehta | 9 | A | 33 | hang khas |
| 2605 | shreya nagpal | 10 | D | 7 | Jor bagle |
| 3712 | tanya verma | 11 | C | 21 | malriya nagar |
| 6523 | zayu malik | 11 | E | 40 | Kohini |
+-------+---------------+-------+------+------+---------------+
5 rows in set (0.01 sec)
mysql> update student set class=10 where name="shreya nagpal";
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> alter table student add marks int;
Query OK, 0 rows affected (1.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select name,class from student where name like 's%';
+---------------+-------+
| name | class |
+---------------+-------+
| shreya nagpal | 10 |
| shivani mehta | 9|
+---------------+-------+
2 rows in set (0.09 sec)
mysql> select max(class), min(admno) from student;
+------------+------------+
| max(class) | min(admno) |
+------------+------------+
| 11 | 1234 |
+------------+------------+
1 row in set (0.05 sec)
mysql> select min(class), name from student group by name;
+------------+---------------+
| min(class) | name |
+------------+---------------+
| 9 | aditi sharma |
| 10 | shreya nagpal |
| 11 | tanya verma |
| 11 | zayu malik |
| 9 | shivani mehta |
+------------+---------------+
5 rows in set (0.00 sec)
mysql> select avg(admno),class from student group by class having avg(admno)>200;
+------------+-------+
| avg(admno) | class |
+------------+-------+
| 5132.5000 | 9|
| 2605.0000 | 10 |
| 5117.5000 | 11 |
+------------+-------+
3 rows in set (0.04 sec)
mysql> select sum(class), sec from student group by class order by class desc;
+------------+------+
| sum(class) | sec |
+------------+------+
| 22 | C |
| 10 | D |
| 18 | A |
+------------+------+
3 rows in set (0.00 sec)
mysql> update student set marks=80 where rno=4;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set marks=70 where rno=33;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set marks=78 where rno=40;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set marks=55 where rno=7;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set marks=62 where rno=21;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select avg(marks), name, class from student where class in(10,11) group by name having
avg(marks)<2000;
+------------+---------------+-------+
| avg(marks) | name | class |
+------------+---------------+-------+
| 55.0000 | shreya nagpal | 10 |
| 62.0000 | tanya verma | 11 |
| 78.0000 | zayu malik | 11 |
+------------+---------------+-------+
3 rows in set (0.00 sec)
mysql> create table sports
-> (
-> admno int,
-> game char(15),
-> coachname varchar(20),
-> grade char
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> insert into sports values(6523,"football","narendra","A");
Query OK, 1 row affected (0.14 sec)
mysql> insert into sports values(4031,"cricket","s k mehta","A");
Query OK, 1 row affected (0.09 sec)
mysql> insert into sports values(3712,"cricket","s k mehta","B");
Query OK, 1 row affected (0.10 sec)
mysql> insert into sports values(5612,"volleyball","priyanka","C");
Query OK, 1 row affected (0.12 sec)
mysql> insert into sports values(1234,"cricket","s k mehta","B");
Query OK, 1 row affected (0.07 sec)
mysql> insert into sports values(2605,"volleyball","priyanka","A");
Query OK, 1 row affected (0.09 sec)
mysql> select * from student,sports where student.admno=sports.admno;
+-------+---------------+-------+------+------+---------------+-------+-------+------------+-----------+-------+
| admno | name | class | sec | rno | address | marks | admno | game | coachname |
grade |
+-------+---------------+-------+------+------+---------------+-------+-------+------------+-----------+-------+
| 6523 | zayu malik | 11 | E | 40 | Kohini | 78 | 6523 | football | narendra | A |
| 3712 | tanya verma | 11 | C | 21 | malriya nagar | 62 | 3712 | cricket | s k mehta | B |
| 1234 | aditi sharma | 9 | A | 4 | SJE | 80 | 1234 | cricket | s k mehta | B |
| 2605 | shreya nagpal | 10 | D | 7 | Jor bagle | 55 | 2605 | volleyball | priyanka | A |
+-------+---------------+-------+------+------+---------------+-------+-------+------------+-----------+-------+
4 rows in set (0.07 sec)
mysql> select avg(marks),grade from student,sports where student.admno=sports.admno group by
grade;
+------------+-------+
| avg(marks) | grade |
+------------+-------+
| 66.5000 | A |
| 71.0000 | B |
+------------+-------+
2 rows in set (0.00 sec)
mysql> select name,grade from student,sports where student.admno=sports.admno and
sports.coachname="s k mehta";
+--------------+-------+
| name | grade |
+--------------+-------+
| aditi sharma | B |
| tanya verma | B |
+--------------+-------+
2 rows in set (0.00 sec)
mysql> select sum(marks),grade from student,sports where student.admno=sports.admno group by
address order by sum(marks) desc;
+------------+-------+
| sum(marks) | grade |
+------------+-------+
| 80 | B |
| 78 | A |
| 62 | B |
| 55 | A |
+------------+-------+
4 rows in set (0.00 sec)
mysql> select name,class from student where marks>10 group by class;
+---------------+-------+
| name | class |
+---------------+-------+
| aditi sharma | 9|
| shreya nagpal | 10 |
| tanya verma | 11 |
+---------------+-------+
3 rows in set (0.00 sec)
mysql> alter table student add primary key(admno);
Query OK, 0 rows affected (1.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| admno | int | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| class | int | YES | | NULL | |
| sec | char(1) | YES | | NULL | |
| rno | int | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
| marks | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.07 sec)
mysql> alter table student drop rno;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-------+---------------+-------+------+---------------+-------+
| admno | name | class | sec | address | marks |
+-------+---------------+-------+------+---------------+-------+
| 1234 | aditi sharma | 9 | A | SJE | 80 |
| 2605 | shreya nagpal | 10 | D | Jor bagle | 55 |
| 3712 | tanya verma | 11 | C | malriya nagar | 62 |
| 6523 | zayu malik | 11 | E | Kohini | 78 |
| 9031 | shivani mehta | 9 | A | hang khas | 70 |
+-------+---------------+-------+------+---------------+-------+
5 rows in set (0.00 sec)
mysql> alter table student drop primary key;
Query OK, 5 rows affected (0.82 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| admno | int | NO | | NULL | |
| name | varchar(30) | YES | | NULL | |
| class | int | YES | | NULL | |
| sec | char(1) | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
| marks | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.13 sec)
mysql> update student set marks=marks+10;
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from student;
+-------+---------------+-------+------+---------------+-------+
| admno | name | class | sec | address | marks |
+-------+---------------+-------+------+---------------+-------+
| 1234 | aditi sharma | 9 | A | SJE | 90 |
| 2605 | shreya nagpal | 10 | D | Jor bagle | 65 |
| 3712 | tanya verma | 11 | C | malriya nagar | 72 |
| 6523 | zayu malik | 11 | E | Kohini | 88 |
| 9031 | shivani mehta | 9 | A | hang khas | 80 |
+-------+---------------+-------+------+---------------+-------+
5 rows in set (0.00 sec)
mysql> alter table student modify marks decimal(8,2);
Query OK, 5 rows affected (1.42 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-------+---------------+-------+------+---------------+-------+
| admno | name | class | sec | address | marks |
+-------+---------------+-------+------+---------------+-------+
| 1234 | aditi sharma | 9 | A | SJE | 90.00 |
| 2605 | shreya nagpal | 10 | D | Jor bagle | 65.00 |
| 3712 | tanya verma | 11 | C | malriya nagar | 72.00 |
| 6523 | zayu malik | 11 | E | Kohini | 88.00 |
| 9031 | shivani mehta | 9 | A | hang khas | 80.00 |
+-------+---------------+-------+------+---------------+-------+
5 rows in set (0.00 sec)
mysql> drop table sports;
Query OK, 0 rows affected (0.34 sec)
mysql> select * from sports;
ERROR 1146 (42S02): Table 'students.sports' doesn't exist
