Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.40 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database cnc;
Query OK, 1 row affected (0.01 sec)
mysql> use cnc;
Database changed
mysql> create table employee (eid int,ename varchar(50),eloc varchar(50),esal
float);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into employee values(1,'Ajay','Nerul',67000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(2,'Roahn','Nerul',47000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee values(3,'Vishal','Vashi',89000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(4,'Pankaj','Vashi',49000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from employee;
+------+--------+-------+-------+
| eid | ename | eloc | esal |
+------+--------+-------+-------+
| 1 | Ajay | Nerul | 67000 |
| 2 | Roahn | Nerul | 47000 |
| 3 | Vishal | Vashi | 89000 |
| 4 | Pankaj | Vashi | 49000 |
+------+--------+-------+-------+
4 rows in set (0.00 sec)
mysql> select eloc from employee;
+-------+
| eloc |
+-------+
| Nerul |
| Nerul |
| Vashi |
| Vashi |
+-------+
4 rows in set (0.00 sec)
mysql> select eloc from employee group by eloc;
+-------+
| eloc |
+-------+
| Nerul |
| Vashi |
+-------+
2 rows in set (0.00 sec)
mysql> select * from employee where esal>60000;
+------+--------+-------+-------+
| eid | ename | eloc | esal |
+------+--------+-------+-------+
| 1 | Ajay | Nerul | 67000 |
| 3 | Vishal | Vashi | 89000 |
+------+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from employee where esal>60000 order by esal asc;
+------+--------+-------+-------+
| eid | ename | eloc | esal |
+------+--------+-------+-------+
| 1 | Ajay | Nerul | 67000 |
| 3 | Vishal | Vashi | 89000 |
+------+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from employee where esal>60000 order by esal desc;
+------+--------+-------+-------+
| eid | ename | eloc | esal |
+------+--------+-------+-------+
| 3 | Vishal | Vashi | 89000 |
| 1 | Ajay | Nerul | 67000 |
+------+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> select max(esal) from employee;
+-----------+
| max(esal) |
+-----------+
| 89000 |
+-----------+
1 row in set (0.00 sec)
mysql> select max(esal) Total from employee;
+-------+
| Total |
+-------+
| 89000 |
+-------+
1 row in set (0.00 sec)
mysql> select min(esal) Total from employee;
+-------+
| Total |
+-------+
| 47000 |
+-------+
1 row in set (0.00 sec)
mysql> select sum(esal) Total from employee;
+--------+
| Total |
+--------+
| 252000 |
+--------+
1 row in set (0.00 sec)
mysql> select avg(esal) Total from employee;
+-------+
| Total |
+-------+
| 63000 |
+-------+
1 row in set (0.00 sec)
mysql> select count(esal) Total from employee;
+-------+
| Total |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)
mysql> select count(eloc) from employee where eloc="nerul";
+-------------+
| count(eloc) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> select eloc,count(eloc) from employee group by eloc;
+-------+-------------+
| eloc | count(eloc) |
+-------+-------------+
| Nerul | 2 |
| Vashi | 2 |
+-------+-------------+
2 rows in set (0.00 sec)
mysql> select * from employee where esal=max(esal);
ERROR 1111 (HY000): Invalid use of group function
mysql> select * from employee where esal=(select max(esal) from employee);
+------+--------+-------+-------+
| eid | ename | eloc | esal |
+------+--------+-------+-------+
| 3 | Vishal | Vashi | 89000 |
+------+--------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from employee where esal>(select avg(esal) from employee);
+------+--------+-------+-------+
| eid | ename | eloc | esal |
+------+--------+-------+-------+
| 1 | Ajay | Nerul | 67000 |
| 3 | Vishal | Vashi | 89000 |
+------+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from employee where ename like 'A%';
+------+-------+-------+-------+
| eid | ename | eloc | esal |
+------+-------+-------+-------+
| 1 | Ajay | Nerul | 67000 |
+------+-------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from employee where ename like '%y';
+------+-------+-------+-------+
| eid | ename | eloc | esal |
+------+-------+-------+-------+
| 1 | Ajay | Nerul | 67000 |
+------+-------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from employee where ename like '%a%';
+------+--------+-------+-------+
| eid | ename | eloc | esal |
+------+--------+-------+-------+
| 1 | Ajay | Nerul | 67000 |
| 2 | Roahn | Nerul | 47000 |
| 3 | Vishal | Vashi | 89000 |
| 4 | Pankaj | Vashi | 49000 |
+------+--------+-------+-------+
4 rows in set (0.00 sec)
mysql>