DBMS_LAB2_AS3

Q1. Malay has joined in “SOFTWARE SUPPORT” Department. Insert appropriate data into the related Tables.
A1.
mysql> insert into employee values(13,8,"Malay","96969696969","malay69@yahoo.com","1987-12-13","M",36900);
Query OK, 1 row affected (0.30 sec)

Q2. Assign “Malay” to “INFORMATION SECURITY” Project.
A2.
mysql> insert into work_project(13,14);
Query OK, 1 row affected (0.30 sec)

Q3. List all Employees who have not assigned to any Project.
A3.
mysql> select e_name
    -> from employee
    -> where emp_id not in
    -> (select eid from work_project);
+--------+
| e_name |
+--------+
| Aalam  |
+--------+
1 row in set (0.04 sec)

Q4. Display Employee details who are working in the Department of “INFORMATION CENTER”.
A4.
mysql> select e_name
    -> from employee
    -> where emp_id in
    -> ( select eid
    -> from work_project
    -> where pro_id in
    -> (select p_id
    -> from project
    -> where p_name="INFORMATION SECURITY"));
+----------+
| e_name   |
+----------+
| Anjali   |
| Bikash   |
| Nilesh   |
| Abhishek |
| Malay    |
+----------+
5 rows in set (0.00 sec)


Q5. In which Department “Aadarsh” is working?
A5.
mysql> select dept_name
    -> from department
    -> where dept_id in
    -> ( select d_id
    -> from employee
    -> where e_name = "Aadarsh");
+-----------+
| dept_name |
+-----------+
| PLANNING  |
+-----------+
1 row in set (0.00 sec)

Q6. Who are the senior most Employees?
A6.
mysql> select e_name
    -> from employee
    -> order by birthday
    -> limit 5;
+----------+
| e_name   |
+----------+
| Abhishek |
| Rakesh   |
| Gajendra |
| Bikash   |
| Nilesh   |
+----------+
5 rows in set (0.00 sec)

7. mysql> select phone, e_name from employee where phone LIKE "03%";
+-------------+-----------+
| phone       | e_name    |
+-------------+-----------+
| 0343-234565 | Gajendra  |
| 033-2345434 | Chiranjit |
| 0343-234345 | Tapas     |
+-------------+-----------+
3 rows in set (0.00 sec)

8. mysql> select e_name from employee where month(birthday)=2;
+----------+
| e_name   |
+----------+
| Aadarsh  |
| Aalam    |
| Gajendra |
| Malay    |
+----------+
4 rows in set (0.00 sec)

9. mysql> select e_name from employee where d_id=8 order by birthday limit 1;
+----------+
| e_name   |
+----------+
| Abhishek |
+----------+
1 row in set (0.00 sec)

10. mysql> select e_name from employee , work_project where emp_id=eid and pro_id=13;
+--------+
| e_name |
+--------+
| Nilesh |
| Anjali |
+--------+
2 rows in set (0.00 sec)

11. mysql> select DATEDIFF(e_date,s_date) from project where p_id=12;
+-------------------------+
| DATEDIFF(e_date,s_date) |
+-------------------------+
|                     561 |
+-------------------------+
1 row in set (0.00 sec)

12. mysql> select count(eid), pro_id  from project, work_project group by pro_id;
+------------+--------+
| count(eid) | pro_id |
+------------+--------+
|         12 |     11 |
|          8 |     12 |
|          8 |     13 |
|         20 |     14 |
+------------+--------+
4 rows in set (0.00 sec)

13. mysql> select e_name, p_name from employee, project, work_project where salary=(select MAX(salary) from employee) AND emp_id=eid AND pro_id=p_id;
+--------+----------------------+
| e_name | p_name               |
+--------+----------------------+
| Bikash | INFORMATION SECURITY |
+--------+----------------------+
1 row in set (0.00 sec)

14. mysql> select e_name, p_name from project, work_project, employee  where DATEDIFF('2020-02-14', e_date) > 0 AND pro_id=p_id AND eid=emp_id;
+-----------+--------------------------------+
| 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           |
| Malay     | INFORMATION SECURITY           |
+-----------+--------------------------------+
12 rows in set (0.00 sec)


15. mysql> select p_name from project, work_project, employee  where sex="F" AND pro_id=p_id AND eid=emp_id;
+--------------------------------+
| p_name                         |
+--------------------------------+
| Robotics and Autonomous System |
| INFORMATION SECURITY           |
+--------------------------------+
2 rows in set (0.00 sec)

16. mysql> select p_name, datediff(e_date, s_date) as df from project where datediff(e_date, s_date)=(select MAX(datediff(e_date, s_date)) from project);
+----------------------+------+
| p_name               | df   |
+----------------------+------+
| INFORMATION SECURITY |  946 |
+----------------------+------+
1 row in set (0.00 sec)


17. mysql> select e_name from employee order by salary DESC limit 1,1;
+----------+
| e_name   |
+----------+
| Gajendra |
+----------+
1 row in set (0.01 sec)