join-sub-queries-and-views.sql

mysql> use students;
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 | HIRE_DATE  | SALARY | JOB_TITLE | MANAGER_ID | DEPT_ID |
+--------+------------+-----------+------------+--------+-----------+------------+---------+
|      1 | GANESH     | SINGH     | 2015-02-01 |  50000 | MANAGER   |          4 |       1 |
|      2 | AMIT       | KUMAR     | 2014-06-10 | 200000 | HR        |          2 |       2 |
|      3 | ARVIND     | RANJAN    | 2017-10-13 |  10000 | TEACHER   |          6 |       3 |
|      4 | GAURAV     | SHARMA    | 2014-08-06 |  60000 | ENGG      |          8 |       2 |
|      6 | JONES      | BOND      | 2018-12-26 |  80000 | WORKER    |          2 |       9 |
+--------+------------+-----------+------------+--------+-----------+------------+---------+
5 rows in set (0.00 sec)
            

mysql> select * from DEPARTMENTS;
+---------+-----------+------------+-------------+
| DEPT_ID | DEPT_NAME | MANAGER_ID | LOCATION_ID |
+---------+-----------+------------+-------------+
|       1 | COMP      |          2 |          66 |
|       2 | IT        |          3 |          99 |
|       3 | ENTC      |          2 |          10 |
+---------+-----------+------------+-------------+
3 rows in set (0.00 sec)
           
mysql> SELECT * from LOCATION;
+--------+----------------+-------------+-----------+------------+------------+
| LOC_ID | STREET_ADDRESS | POSTAL_CODE | CITY      | STATE      | COUNTRY_ID |
+--------+----------------+-------------+-----------+------------+------------+
|      1 | HINJEWADI      |      411057 | PUNE      | MAHARASTRA |         41 |
|      2 | GAIGHAT        |      611089 | PATNA     | BIHAR      |         62 |
|      9 | HANUMAN ROAD   |      873572 | NEW DELHI | DELHI      |         83 |
|     10 | AREA 11        |      200021 | USA       | C.USA      |         20 |
+--------+----------------+-------------+-----------+------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT FIRST_NAME, LAST_NAME, SALARY FROM students.EMPLOYEE WHERE SALARY > (SELECT SALARY FROM students.EMPLOYEE WHERE LAST_NAME='SINGH');
+------------+-----------+--------+
| FIRST_NAME | LAST_NAME | SALARY |
+------------+-----------+--------+
| AMIT       | KUMAR     | 200000 |
| GAURAV     | SHARMA    |  60000 |
| JONES      | BOND      |  80000 |
+------------+-----------+--------+
3 rows in set (0.00 sec)

mysql> SELECT EMPLOYEE.MANAGER_ID, DEPT_NAME FROM DEPARTMENTS INNER JOIN EMPLOYEE ON DEPARTMENTS.MANAGER_ID = EMPLOYEE.MANAGER_ID;
+------------+-----------+
| MANAGER_ID | DEPT_NAME |
+------------+-----------+
|          2 | COMP      |
|          2 | ENTC      |
+------------+-----------+
4 rows in set (0.02 sec)

mysql> SELECT EMPLOYEE.FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPARTMENTS WHERE DEPT_NAME='IT');
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| AMIT       | KUMAR     |
| GAURAV     | SHARMA    |
+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT FIRST_NAME, LAST_NAME FROM students.EMPLOYEE WHERE SALARY > (SELECT AVG(SALARY) FROM students.EMPLOYEE);
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| AMIT       | KUMAR     |
+------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT FIRST_NAME, LAST_NAME FROM students.EMPLOYEE INNER JOIN DEPARTMENTS WHERE (SALARY > (SELECT AVG(SALARY) FROM students.EMPLOYEE) ) AND EMPLOYEE.DEPT_ID = (SELECT DEPT_ID FROM DEPARTMENTS WHERE DEPT_NAME='IT');
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| AMIT       | KUMAR     |
+------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT FIRST_NAME, LAST_NAME from students.EMPLOYEE join DEPARTMENTS on EMPLOYEE.DEPT_ID = DEPARTMENTS.DEPT_ID and EMPLOYEE.SALARY = (select MIN(SALARY) from students.EMPLOYEE); 
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| ARVIND     | RANJAN    |
+------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT EMP_ID, FIRST_NAME, LAST_NAME , SALARY FROM EMPLOYEE JOIN DEPARTMENTS ON  EMPLOYEE.DEPT_ID = DEPARTMENTS.DEPT_ID AND EMPLOYEE.SALARY > (SELECT AVG(EMPLOYEE.SALARY) FROM EMPLOYEE GROUP BY DEPARTMENTS.DEPT_NAME);
+--------+------------+-----------+--------+
| EMP_ID | FIRST_NAME | LAST_NAME | SALARY |
+--------+------------+-----------+--------+
|      2 | AMIT       | KUMAR     | 200000 |
+--------+------------+-----------+--------+
1 row in set (0.01 sec)

mysql> SELECT EMP_ID, LAST_NAME,EMPLOYEE.MANAGER_ID FROM EMPLOYEE JOIN DEPARTMENTS ON EMPLOYEE.DEPT_ID = DEPARTMENTS.DEPT_ID;
+--------+-----------+------------+
| EMP_ID | LAST_NAME | MANAGER_ID |
+--------+-----------+------------+
|      1 | SINGH     |          4 |
|      2 | KUMAR     |          2 |
|      3 | RANJAN    |          6 |
|      4 | SHARMA    |          8 |
+--------+-----------+------------+
4 rows in set (0.00 sec)

mysql> SELECT e.FIRST_NAME, e.LAST_NAME FROM EMPLOYEE e JOIN EMPLOYEE emp ON (emp.LAST_NAME = 'BOND') WHERE (emp.HIRE_DATE > e.HIRE_DATE);
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| GANESH     | SINGH     |
| AMIT       | KUMAR     |
| ARVIND     | RANJAN    |
| GAURAV     | SHARMA    |
+------------+-----------+
4 rows in set (0.00 sec)

mysql> select d.DEPT_NAME, count(e.DEPT_ID) from DEPARTMENTS d join EMPLOYEE e on d.DEPT_ID = e.DEPT_ID group by d.DEPT_NAME;
+-----------+------------------+
| DEPT_NAME | count(e.DEPT_ID) |
+-----------+------------------+
| COMP      |                1 |
| ENTC      |                1 |
| IT        |                2 |
+-----------+------------------+
3 rows in set (0.00 sec)