SQL QUERIES CLASS 12 CS

mysql> select * from employee;
+-----+-------+--------+----------------+--------+------------+
| eno | ename | phone  | email          | salary | doj        |
+-----+-------+--------+----------------+--------+------------+
|   1 | ramu  |  98390 | abc@gmail.com  |   2000 | 2000-12-12 |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 |
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 |
+-----+-------+--------+----------------+--------+------------+
4 rows in set (0.00 sec)
mysql> select * from employee order by salary;
+-----+-------+--------+----------------+--------+------------+
| eno | ename | phone  | email          | salary | doj        |
+-----+-------+--------+----------------+--------+------------+
|   1 | ramu  |  98390 | abc@gmail.com  |   2000 | 2000-12-12 |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 |
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 |
+-----+-------+--------+----------------+--------+------------+
4 rows in set (0.03 sec)
mysql> select * from employee order by salary asc;
+-----+-------+--------+----------------+--------+------------+
| eno | ename | phone  | email          | salary | doj        |
+-----+-------+--------+----------------+--------+------------+
|   1 | ramu  |  98390 | abc@gmail.com  |   2000 | 2000-12-12 |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 |
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 |
+-----+-------+--------+----------------+--------+------------+
4 rows in set (0.00 sec)
mysql> select * from employee order by salary desc;
+-----+-------+--------+----------------+--------+------------+
| eno | ename | phone  | email          | salary | doj        |
+-----+-------+--------+----------------+--------+------------+
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 |
|   1 | ramu  |  98390 | abc@gmail.com  |   2000 | 2000-12-12 |
+-----+-------+--------+----------------+--------+------------+
4 rows in set (0.00 sec)
mysql> select * from employee where salary between 2500 and 3000 order by salary
 desc;
+-----+-------+--------+----------------+--------+------------+
| eno | ename | phone  | email          | salary | doj        |
+-----+-------+--------+----------------+--------+------------+
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 |
+-----+-------+--------+----------------+--------+------------+
3 rows in set (0.01 sec)
mysql> select * from employee where salary between 2500 and 3000;
+-----+-------+--------+----------------+--------+------------+
| eno | ename | phone  | email          | salary | doj        |
+-----+-------+--------+----------------+--------+------------+
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 |
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 |
+-----+-------+--------+----------------+--------+------------+
3 rows in set (0.00 sec)
mysql> alter table employee add column grade char(30);
Query OK, 4 rows affected (0.19 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from employee;
+-----+-------+--------+----------------+--------+------------+-------+
| eno | ename | phone  | email          | salary | doj        | grade |
+-----+-------+--------+----------------+--------+------------+-------+
|   1 | ramu  |  98390 | abc@gmail.com  |   2000 | 2000-12-12 | NULL  |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 | NULL  |
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 | NULL  |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 | NULL  |
+-----+-------+--------+----------------+--------+------------+-------+
4 rows in set (0.00 sec)
mysql> update employee set grade='E1' where eno=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from employee;
+-----+-------+--------+----------------+--------+------------+-------+
| eno | ename | phone  | email          | salary | doj        | grade |
+-----+-------+--------+----------------+--------+------------+-------+
|   1 | ramu  |  98390 | abc@gmail.com  |   2000 | 2000-12-12 | E1    |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 | NULL  |
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 | NULL  |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 | NULL  |
+-----+-------+--------+----------------+--------+------------+-------+
4 rows in set (0.00 sec)
mysql> source d:\\1.sql;
ERROR:
Unknown command '\\'.
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from employee;
+-----+-------+--------+----------------+--------+------------+-------+
| eno | ename | phone  | email          | salary | doj        | grade |
+-----+-------+--------+----------------+--------+------------+-------+
|   1 | ramu  |  98390 | abc@gmail.com  |   2000 | 2000-12-12 | E1    |
|   2 | rayu  | 990390 | akkc@gmail.com |   2900 | 2070-12-12 | E2    |
|   3 | ranu  | 990770 | auuc@gmail.com |   2908 | 2070-12-18 | E3    |
|   4 | jhamu |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 | E2    |
+-----+-------+--------+----------------+--------+------------+-------+
4 rows in set (0.00 sec)
mysql> select grade from employee;
+-------+
| grade |
+-------+
| E1    |
| E2    |
| E3    |
| E2    |
+-------+
4 rows in set (0.00 sec)
mysql> select distinct grade from employee;
+-------+
| grade |
+-------+
| E1    |
| E2    |
| E3    |
+-------+
3 rows in set (0.01 sec)
mysql> alter table employee modify grade char(3);
Query OK, 4 rows affected (0.16 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eno    | int(11)     | NO   | PRI | NULL    |       |
| ename  | char(30)    | NO   |     | NULL    |       |
| phone  | int(11)     | NO   | UNI | NULL    |       |
| email  | varchar(30) | NO   | UNI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
| doj    | date        | YES  |     | NULL    |       |
| grade  | char(3)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> insert into employee values(5,'Karan',941523,'dbc@cbd.com',5000,'2002-09-
11','E1');
Query OK, 1 row affected (0.02 sec)
mysql> insert into employee values(6,'Kumaran',941235,'cde@cbd.com',7000,'2003-0
9-11','E1');
Query OK, 1 row affected (0.03 sec)
mysql> select * from employee;
+-----+---------+--------+----------------+--------+------------+-------+
| eno | ename   | phone  | email          | salary | doj        | grade |
+-----+---------+--------+----------------+--------+------------+-------+
|   1 | ramu    |  98390 | abc@gmail.com  |   2000 | 2000-12-12 | E1    |
|   2 | rayu    | 990390 | akkc@gmail.com |   2900 | 2070-12-12 | E2    |
|   3 | ranu    | 990770 | auuc@gmail.com |   2908 | 2070-12-18 | E3    |
|   4 | jhamu   |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 | E2    |
|   5 | Karan   | 941523 | dbc@cbd.com    |   5000 | 2002-09-11 | E1    |
|   6 | Kumaran | 941235 | cde@cbd.com    |   7000 | 2003-09-11 | E1    |
+-----+---------+--------+----------------+--------+------------+-------+
6 rows in set (0.00 sec)
mysql> select sum(salary) from employee;
+-------------+
| sum(salary) |
+-------------+
|       22688 |
+-------------+
1 row in set (0.01 sec)
mysql> select sum(salary) from employee where grade='E1';
+-------------+
| sum(salary) |
+-------------+
|       14000 |
+-------------+
1 row in set (0.00 sec)
mysql> select sum(salary),Grade from employee group by grade;
+-------------+-------+
| sum(salary) | Grade |
+-------------+-------+
|       14000 | E1    |
|        5780 | E2    |
|        2908 | E3    |
+-------------+-------+
3 rows in set (0.00 sec)
mysql> select avg(salary),Grade from employee group by grade;
+------------------+-------+
| avg(salary)      | Grade |
+------------------+-------+
| 4666.66666666667 | E1    |
|             2890 | E2    |
|             2908 | E3    |
+------------------+-------+
3 rows in set (0.08 sec)
mysql> select count(salary),Grade from employee group by grade;
+---------------+-------+
| count(salary) | Grade |
+---------------+-------+
|             3 | E1    |
|             2 | E2    |
|             1 | E3    |
+---------------+-------+
3 rows in set (0.00 sec)
mysql> select count(salary),Grade from employee;
+---------------+-------+
| count(salary) | Grade |
+---------------+-------+
|             6 | E1    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select count(),Grade from employee;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '),Gra
de from employee' at line 1
mysql> select count(*),Grade from employee;
+----------+-------+
| count(*) | Grade |
+----------+-------+
|        6 | E1    |
+----------+-------+
1 row in set (0.02 sec)
mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)
mysql> select * from employee where grade='E1' or grade='E2';
+-----+---------+--------+----------------+--------+------------+-------+
| eno | ename   | phone  | email          | salary | doj        | grade |
+-----+---------+--------+----------------+--------+------------+-------+
|   1 | ramu    |  98390 | abc@gmail.com  |   2000 | 2000-12-12 | E1    |
|   2 | rayu    | 990390 | akkc@gmail.com |   2900 | 2070-12-12 | E2    |
|   4 | jhamu   |  98377 | yyyc@gmail.com |   2880 | 2000-12-17 | E2    |
|   5 | Karan   | 941523 | dbc@cbd.com    |   5000 | 2002-09-11 | E1    |
|   6 | Kumaran | 941235 | cde@cbd.com    |   7000 | 2003-09-11 | E1    |
+-----+---------+--------+----------------+--------+------------+-------+
5 rows in set (0.00 sec)
mysql> select * from employee where grade='E1' and salary>5000;
+-----+---------+--------+-------------+--------+------------+-------+
| eno | ename   | phone  | email       | salary | doj        | grade |
+-----+---------+--------+-------------+--------+------------+-------+
|   6 | Kumaran | 941235 | cde@cbd.com |   7000 | 2003-09-11 | E1    |
+-----+---------+--------+-------------+--------+------------+-------+
1 row in set (0.00 sec)
mysql> select * from employee where grade='E1' and salary>=5000;
+-----+---------+--------+-------------+--------+------------+-------+
| eno | ename   | phone  | email       | salary | doj        | grade |
+-----+---------+--------+-------------+--------+------------+-------+
|   5 | Karan   | 941523 | dbc@cbd.com |   5000 | 2002-09-11 | E1    |
|   6 | Kumaran | 941235 | cde@cbd.com |   7000 | 2003-09-11 | E1    |
+-----+---------+--------+-------------+--------+------------+-------+
2 rows in set (0.00 sec)
mysql> select count(grade) from employee;
+--------------+
| count(grade) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)
mysql> select count(distinct grade) from employee;
+-----------------------+
| count(distinct grade) |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)
MySQL>