sql-dml-operation.sql

mysql> CREATE DATABASE ASSIGNMENT3;
Query OK, 1 row affected (0.01 sec)

mysql> USE ASSIGNMENT3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM EMPLOYEE;
+--------+------------+-----------+--------+--------------+------------+
| EMP_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+--------+------------+-----------+--------+--------------+------------+
|      2 | AMIT       | KUMAR     |  85000 | 2015-10-11   | COMPUTER   |
|     25 | MAYURESH   | PATKAR    |  75000 | 2018-07-17   | IT         |
|     45 | MEGHRAJ    | DESHMUKH  |  55000 | 2012-12-17   | IT         |
|      6 | AVIRAJ     | SARKAR    |  85000 | 2010-11-21   | EnTC       |
|     40 | INDRANEEL  | GHOTEKAR  |  75000 | 2016-07-09   | EnTC       |
|      4 | GAURAV     | SHARMA    |  78000 | 2016-01-09   | COMPUTER   |
+--------+------------+-----------+--------+--------------+------------+
6 rows in set (0.01 sec)

mysql> SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE;
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| AMIT       | KUMAR     |
| MAYURESH   | PATKAR    |
| MEGHRAJ    | DESHMUKH  |
| AVIRAJ     | SARKAR    |
| INDRANEEL  | GHOTEKAR  |
| GAURAV     | SHARMA    |
+------------+-----------+
6 rows in set (0.00 sec)

mysql> 
mysql> SELECT DISTINCT DEPARTMENT FROM EMPLOYEE;
+------------+
| DEPARTMENT |
+------------+
| COMPUTER   |
| IT         |
| EnTC       |
+------------+
3 rows in set (0.00 sec)

mysql> SELECT FIRST_NAME, YEAR(JOINING_DATE) AS JOINING_YEAR, MONTH(JOINING_DATE) AS JOINING_MONTH, DAY(JOINING_DATE) AS JOINING_DAY FROM EMPLOYEE;
+------------+--------------+---------------+-------------+
| FIRST_NAME | JOINING_YEAR | JOINING_MONTH | JOINING_DAY |
+------------+--------------+---------------+-------------+
| AMIT       |         2015 |            10 |          11 |
| MAYURESH   |         2018 |             7 |          17 |
| MEGHRAJ    |         2012 |            12 |          17 |
| AVIRAJ     |         2010 |            11 |          21 |
| INDRANEEL  |         2016 |             7 |           9 |
| GAURAV     |         2016 |             1 |           9 |
+------------+--------------+---------------+-------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME DESC ;
+--------+------------+-----------+--------+--------------+------------+
| EMP_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+--------+------------+-----------+--------+--------------+------------+
|     45 | MEGHRAJ    | DESHMUKH  |  55000 | 2012-12-17   | IT         |
|     25 | MAYURESH   | PATKAR    |  75000 | 2018-07-17   | IT         |
|     40 | INDRANEEL  | GHOTEKAR  |  75000 | 2016-07-09   | EnTC       |
|      4 | GAURAV     | SHARMA    |  78000 | 2016-01-09   | COMPUTER   |
|      6 | AVIRAJ     | SARKAR    |  85000 | 2010-11-21   | EnTC       |
|      2 | AMIT       | KUMAR     |  85000 | 2015-10-11   | COMPUTER   |
+--------+------------+-----------+--------+--------------+------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME ASC , SALARY DESC ;
+--------+------------+-----------+--------+--------------+------------+
| EMP_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+--------+------------+-----------+--------+--------------+------------+
|      2 | AMIT       | KUMAR     |  85000 | 2015-10-11   | COMPUTER   |
|      6 | AVIRAJ     | SARKAR    |  85000 | 2010-11-21   | EnTC       |
|      4 | GAURAV     | SHARMA    |  78000 | 2016-01-09   | COMPUTER   |
|     40 | INDRANEEL  | GHOTEKAR  |  75000 | 2016-07-09   | EnTC       |
|     25 | MAYURESH   | PATKAR    |  75000 | 2018-07-17   | IT         |
|     45 | MEGHRAJ    | DESHMUKH  |  55000 | 2012-12-17   | IT         |
+--------+------------+-----------+--------+--------------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE EMPLOYEE SET SALARY = SALARY + 5000 WHERE SALARY < 200000;
Query OK, 6 rows affected (0.06 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT * FROM EMPLOYEE;+--------+------------+-----------+--------+--------------+------------+
| EMP_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+--------+------------+-----------+--------+--------------+------------+
|      2 | AMIT       | KUMAR     |  90000 | 2015-10-11   | COMPUTER   |
|     25 | MAYURESH   | PATKAR    |  80000 | 2018-07-17   | IT         |
|     45 | MEGHRAJ    | DESHMUKH  |  60000 | 2012-12-17   | IT         |
|      6 | AVIRAJ     | SARKAR    |  90000 | 2010-11-21   | EnTC       |
|     40 | INDRANEEL  | GHOTEKAR  |  80000 | 2016-07-09   | EnTC       |
|      4 | GAURAV     | SHARMA    |  83000 | 2016-01-09   | COMPUTER   |
+--------+------------+-----------+--------+--------------+------------+
6 rows in set (0.00 sec)

mysql> DELETE FROM EMPLOYEE WHERE EMP_ID = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM EMPLOYEE WHERE EMP_ID = 4;
Query OK, 1 row affected (0.08 sec)

mysql> SELECT DEPARTMENT, MAX(SALARY) AS MAX_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT;
+------------+------------+
| DEPARTMENT | MAX_SALARY |
+------------+------------+
| COMPUTER   |      90000 |
| EnTC       |      90000 |
| IT         |      80000 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT DEPARTMENT, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT  DESC ;
+------------+------------+
| DEPARTMENT | AVG_SALARY |
+------------+------------+
| IT         | 70000.0000 |
| EnTC       | 85000.0000 |
| COMPUTER   | 90000.0000 |
+------------+------------+
3 rows in set (0.00 sec)