DBMS LAB 5

1. mysql> select count(e_name), location from employee, department where d_id=dept_id group by location;
+---------------+----------+
| count(e_name) | location |
+---------------+----------+
|             1 | L1       |
|             2 | L2       |
|             1 | L3       |
|             1 | L4       |
|             1 | L5       |
|             1 | L6       |
|             1 | L7       |
|             3 | L8       |
+---------------+----------+
8 rows in set (0.00 sec)

2. mysql> select count(e_name) from employee inner join work_project inner join project on work_project.pro_id=project.p_id where eid=employee.emp_id AND email LIKE '%@gmail%' AND p_name="Campus Fire Alarm System";
+---------------+
| count(e_name) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

3.
mysql> select * from employee where email NOT LIKE '%@gmail%' and email NOT LIKE '%@yahoo%';
+--------+------+--------+------------+-----------------------+------------+------+--------+
| emp_id | d_id | e_name | phone      | email                 | birthday   | sex  | salary |
+--------+------+--------+------------+-----------------------+------------+------+--------+
|      5 |    6 | Rakesh | 9878765432 | rakesh@rediffmail.com | 1982-09-29 | M    |  29000 |
|     10 |    3 | Nilesh | 9854562123 |                       | 1983-10-12 | M    |  39800 |
|     13 |    8 | Malay  |            |                       | 0000-00-00 | M    |      0 |
+--------+------+--------+------------+-----------------------+------------+------+--------+
3 rows in set (0.00 sec)

4. mysql> select e_name from employee where birthday LIKE '%9%';
+-----------+
| e_name    |
+-----------+
| Aadarsh   |
| Aalam     |
| Gajendra  |
| Chiranjit |
| Rakesh    |
| Anjali    |
| Bikash    |
| Nilesh    |
| Tapas     |
| Abhishek  |
+-----------+
10 rows in set, 1 warning (0.00 sec)

5. mysql> select e_name from employee where month(birthday) not in (02,10);
+----------+
| e_name   |
+----------+
| Rakesh   |
| Anjali   |
| Bikash   |
| Abhishek |
| Malay    |
+----------+
5 rows in set (0.00 sec)

6. mysql> select e_name, phone, email, sex, salary from employee inner join work_project on employee.emp_id=work_project.eid inner join project on work_project.pro_id=project.p_id where year(s_date)=2012;
+-----------+-------------+---------------------+------+--------+
| e_name    | phone       | email               | sex  | salary |
+-----------+-------------+---------------------+------+--------+
| Aadarsh   | 9878765432  | adarsh@gmail.com    | M    |  30000 |
| Chiranjit | 033-2345434 | chiranjit@yahoo.com | M    |  31000 |
+-----------+-------------+---------------------+------+--------+
2 rows in set (0.00 sec)

7. mysql> select sum(salary) from employee inner join department on dept_id=d_id where location in ("L1","L4");
+-------------+
| sum(salary) |
+-------------+
|       70000 |
+-------------+
1 row in set (0.00 sec)

8. mysql> select * from employee where salary>30000 and salary<35000;
+--------+------+-----------+-------------+---------------------+------------+------+--------+
| emp_id | d_id | e_name    | phone       | email               | birthday   | sex  | salary |
+--------+------+-----------+-------------+---------------------+------------+------+--------+
|      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com | 1987-10-28 | M    |  31000 |
|      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com    | 1986-11-26 | F    |  32500 |
|     12 |    8 | Abhishek  | 9834323456  | abhishek@yahoo.com  | 1982-03-20 | M    |  34000 |
+--------+------+-----------+-------------+---------------------+------------+------+--------+
3 rows in set (0.00 sec)

9. mysql> select datediff(curdate(), birthday)/365 as YEARS_OLD from employee where e_name="Tapas";
+-----------+
| YEARS_OLD |
+-----------+
|   34.4137 |
+-----------+
1 row in set (0.00 sec)


10.mysql> select * from employee where email LIKE '%gmail%';
+--------+------+----------+-------------+--------------------+------------+------+--------+
| emp_id | d_id | e_name   | phone       | email              | birthday   | sex  | salary |
+--------+------+----------+-------------+--------------------+------------+------+--------+
|      1 |    1 | Aadarsh  | 9878765432  | adarsh@gmail.com   | 1987-02-19 | M    |  30000 |
|      3 |    4 | Gajendra | 0343-234565 | gajendra@gmail.com | 1983-02-09 | M    |  40000 |
|      7 |    2 | Bikash   | 8965755467  | bikash@gmail.com   | 1983-04-28 | M    |  40100 |
|     11 |    7 | Tapas    | 0343-234345 | tapas@gmail.com    | 1985-10-15 | M    |  25500 |
+--------+------+----------+-------------+--------------------+------------+------+--------+
4 rows in set (0.00 sec)