DBMS_LAB_AS3

1. select sum(salary) from employee where MONTH(birthday)=2;
+-------------+
| sum(salary) |
+-------------+
|      105000 |
+-------------+
1 row in set (0.00 sec)


2. select emp_id,d_id,e_name,phone,email,birthday,sex,salary,dept_name,max(salary) from department D LEFT JOIN employee E ON D.dept_id=E.d_id group by dept_name order by dept_id;
+--------+------+-----------+-------------+-----------------------+------------+------+--------+-----------------------+-------------+
| emp_id | d_id | e_name    | phone       | email                 | birthday   | sex  | salary | dept_name             | max(salary) |
+--------+------+-----------+-------------+-----------------------+------------+------+--------+-----------------------+-------------+
|      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com      | 1987-02-19 | M    |  30000 | PLANNING              |       30000 |
|      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com        | 1986-02-20 | M    |  35000 | INFORMATION CENTER    |       40100 |
|     10 |    3 | Nilesh    | 9854562123  |                       | 1983-10-12 | M    |  39800 | DEVELOPMENT CENTER    |       39800 |
|      3 |    4 | Gajendra  | 0343-234565 | gajendra@gmail.com    | 1983-02-09 | M    |  40000 | MANUFACTURING SYSTEM  |       40000 |
|      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com   | 1987-10-28 | M    |  31000 | ADMINISTRATION SYSTEM |       31000 |
|      5 |    6 | Rakesh    | 9878765432  | rakesh@radiffmail.com | 1982-09-29 | M    |  29000 | SUPPORT SERVICES      |       29000 |
|     11 |    7 | Tapas     | 0343-234345 | tapas@gmail.com       | 1985-10-15 | M    |  25500 | OPERATIONS            |       25500 |
|      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com      | 1986-11-26 | F    |  32500 | SOFTWARE SUPPORT      |       34000 |
+--------+------+-----------+-------------+-----------------------+------------+------+--------+-----------------------+-------------+
8 rows in set (0.00 sec)

3. select p_name from project P LEFT JOIN work_project W ON P.p_id=W.pro_id LEFT JOIN employee E ON W.eid=E.emp_id where email='';
+--------------------------------+
| p_name                         |
+--------------------------------+
| Robotics and Autonomous System |
| Information Security           |
+--------------------------------+
2 rows in set (0.00 sec)

4. select p_name from project P LEFT JOIN work_project W ON P.p_id=W.pro_id LEFT JOIN employee E ON W.eid=E.emp_id where sex='F';

+--------------------------------+
| p_name                         |
+--------------------------------+
| Robotics and Autonomous System |
| Information Security           |
+--------------------------------+
2 rows in set (0.00 sec)

5. select e_name,p_name from project P LEFT JOIN work_project W ON P.p_id=W.pro_id LEFT JOIN employee E ON W.eid=E.emp_id where CURDATE()> e_date;
+-----------+--------------------------------+
| e_name    | p_name                         |
+-----------+--------------------------------+
| Gajendra  | Campus Fire Alarm System       |
| Rakesh    | Campus Fire Alarm System       |
| Tapas     | Campus Fire Alarm System       |
| Aadarsh   | Guest House Renovation         |
| Chiranjit | Guest House Renovation         |
| Nilesh    | Robotics and Autonomous System |
| Anjali    | Robotics and Autonomous System |
| Nilesh    | Information Security           |
| Anjali    | Information Security           |
| Abhishek  | Information Security           |
| Bikash    | Information Security           |
+-----------+--------------------------------+
11 rows in set (0.00 sec)

6. select * from employee where d_id in(select dept_id from department where location="L1" or location="L3");
+--------+------+---------+------------+------------------+------------+------+--------+
| emp_id | d_id | e_name  | phone      | email            | birthday   | sex  | salary |
+--------+------+---------+------------+------------------+------------+------+--------+
|      1 |    1 | Aadarsh | 9878765432 | adarsh@gmail.com | 1987-02-19 | M    |  30000 |
|     10 |    3 | Nilesh  | 9854562123 |                  | 1983-10-12 | M    |  39800 |
+--------+------+---------+------------+------------------+------------+------+--------+
2 rows in set (0.00 sec)

7. select e_name from employee E INNER JOIN work_project W ON E.emp_id=W.eid INNER JOIN project P on P.p_id=W.pro_id where DATEDIFF(e_date,s_date) = (select max(DATEDIFF(e_date,s_date)) from project);
+----------+
| e_name   |
+----------+
| Nilesh   |
| Anjali   |
| Abhishek |
| Bikash   |
+----------+
4 rows in set (0.00 sec)

8. select dept_name from department D LEFT JOIN employee E ON D.dept_id=E.d_id where e_name like "A%" or e_name like "G%";
+----------------------+
| dept_name            |
+----------------------+
| PLANNING             |
| INFORMATION CENTER   |
| MANUFACTURING SYSTEM |
| SOFTWARE SUPPORT     |
| SOFTWARE SUPPORT     |
+----------------------+
5 rows in set (0.00 sec)

9. select * from employee where salary = (select min(salary) from employee where salary > (select min(salary) from employee));
+--------+------+--------+------------+-----------------------+------------+------+--------+
| emp_id | d_id | e_name | phone      | email                 | birthday   | sex  | salary |
+--------+------+--------+------------+-----------------------+------------+------+--------+
|      5 |    6 | Rakesh | 9878765432 | rakesh@radiffmail.com | 1982-09-29 | M    |  29000 |
+--------+------+--------+------------+-----------------------+------------+------+--------+
1 row in set (0.00 sec)

10. select p_name,count(*) from employee E INNER JOIN work_project W ON E.emp_id=W.eid INNER JOIN project P on P.p_id=W.pro_id group by p_name;
+--------------------------------+----------+
| p_name                         | count(*) |
+--------------------------------+----------+
| Campus Fire Alarm System       |        3 |
| Guest House Renovation         |        2 |
| Information Security           |        4 |
| Robotics and Autonomous System |        2 |
+--------------------------------+----------+
4 rows in set (0.01 sec)