sql ass

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbmslab10          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

mysql> use dbmslab10;
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> show tables;
+---------------------+
| Tables_in_dbmslab10 |
+---------------------+
| Abhishek10          |
| Employee            |
| department          |
| employee            |
| project             |
| work_project        |
+---------------------+
6 rows in set (0.00 sec)

mysql> select * from Employee;
+--------+------+-----------+-------------+-----------------------+------------+------+--------+
| emp_id | d_id | e_name    | phone       | email                 | birthday   | sex  | salary |
+--------+------+-----------+-------------+-----------------------+------------+------+--------+
|      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com      | 1987-02-19 | M    |  30000 |
|      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com        | 1986-02-20 | M    |  35000 |
|      3 |    4 | Gajendra  | 0343-234565 | gajendra@gmail.com    | 1983-02-09 | M    |  40000 |
|      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com   | 1987-10-28 | M    |  31000 |
|      5 |    6 | Rakesh    | 9878765432  | rakesh@rediffmail.com | 1982-09-29 | M    |  29000 |
|      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com      | 1986-11-26 | F    |  32500 |
|      7 |    2 | Bikash    | 8965755467  | bikash@gmail.com      | 1983-04-28 | M    | 401400 |
|     10 |    3 | Nilesh    | 9854562123  | NULL                  | 1983-10-12 | M    |  39800 |
|     11 |    7 | Tapas     | 0343-234345 | tapas@gmail.com       | 1985-10-15 | M    |  25500 |
|     12 |    8 | Abhisekh  | 9834323456  | abhisekh@yahoo.com    | 1982-03-20 | M    |  34000 |
+--------+------+-----------+-------------+-----------------------+------------+------+--------+
10 rows in set (0.00 sec)

mysql> select * from project;
+------+--------------------------------+------------+------------+
| p_id | p_name                         | s_date     | e_date     |
+------+--------------------------------+------------+------------+
|   11 | Campus Fire Alarm System       | 2010-01-12 | 2011-02-08 |
|   12 | Guest House Renovation         | 2012-02-07 | 2013-08-21 |
|   13 | Robotics And Autonomous System | 2011-06-23 | 2013-06-04 |
|   14 | INFORMATION SECURITY           | 2009-07-28 | 2012-02-29 |
+------+--------------------------------+------------+------------+
4 rows in set (0.00 sec)

mysql> select * from department;
+---------+----------------------+----------+
| dept_id | dept_name            | location |
+---------+----------------------+----------+
|       1 | PLANNING             | L1       |
|       2 | INFORMATION CENTRE   | L2       |
|       3 | DEVELOPMENT CENTRE   | L3       |
|       4 | MANUFACTURING SYSTEM | L4       |
|       5 | ADMINISTRATION SYSTE | L5       |
|       6 | SUPPORT SERVICES     | L6       |
|       7 | OPERATIONS           | L7       |
|       8 | SOFTWARE SUPPORT     | L7       |
+---------+----------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from work_project;
+------+--------+
| eid  | pro_id |
+------+--------+
|    1 |     12 |
|    3 |     11 |
|   10 |     14 |
|   10 |     13 |
|    6 |     14 |
|    5 |     11 |
|    6 |     13 |
|   11 |     11 |
|    4 |     12 |
|   12 |     14 |
|    7 |     14 |
+------+--------+
11 rows in set (0.00 sec)

mysql> select * from employee;
+------+--------+-----------+------------+------------+---------+------------+----------+-------------+
| id   | f_name | last_name | startdate  | enddate    | salary  | phone      | CITY     | deSTINATION |
+------+--------+-----------+------------+------------+---------+------------+----------+-------------+
|    1 | Jason  | Martin    | 1996-07-25 | 2006-07-25 | 2234.56 | 99999999   | Toronto  | Programmin  |
|    2 | Alison | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | 99999999   | Vancover | Tester      |
|    4 | Celia  | Rice      | 1982-10-24 | 1999-04-21 | 3344.78 | 99999999   | Vancover | Manager     |
|    5 | Robert | Black     | 1984-01-15 | 1998-08-08 | 3344.78 | 9999999999 | Vancover | Tester      |
|    6 | Linda  | Green     | 1987-07-30 | 1996-01-04 | 5322.78 | 99999999   | Newyork  | Tester      |
|    7 | David  | Larry     | 1990-12-31 | 1998-02-12 | 5322.78 | 99999999   | Newyork  | Manager     |
+------+--------+-----------+------------+------------+---------+------------+----------+-------------+
6 rows in set (0.02 sec)

mysql> insert into Employee values(15,8,"Malay","9435687456","malay2020@yahoo.com","1985-04-12","M",36000);
Query OK, 1 row affected (0.05 sec)

mysql> select * from Employee;
+--------+------+-----------+-------------+-----------------------+------------+------+--------+
| emp_id | d_id | e_name    | phone       | email                 | birthday   | sex  | salary |
+--------+------+-----------+-------------+-----------------------+------------+------+--------+
|      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com      | 1987-02-19 | M    |  30000 |
|      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com        | 1986-02-20 | M    |  35000 |
|      3 |    4 | Gajendra  | 0343-234565 | gajendra@gmail.com    | 1983-02-09 | M    |  40000 |
|      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com   | 1987-10-28 | M    |  31000 |
|      5 |    6 | Rakesh    | 9878765432  | rakesh@rediffmail.com | 1982-09-29 | M    |  29000 |
|      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com      | 1986-11-26 | F    |  32500 |
|      7 |    2 | Bikash    | 8965755467  | bikash@gmail.com      | 1983-04-28 | M    | 401400 |
|     10 |    3 | Nilesh    | 9854562123  | NULL                  | 1983-10-12 | M    |  39800 |
|     11 |    7 | Tapas     | 0343-234345 | tapas@gmail.com       | 1985-10-15 | M    |  25500 |
|     12 |    8 | Abhisekh  | 9834323456  | abhisekh@yahoo.com    | 1982-03-20 | M    |  34000 |
|     15 |    8 | Malay     | 9435687456  | malay2020@yahoo.com   | 1985-04-12 | M    |  36000 |
+--------+------+-----------+-------------+-----------------------+------------+------+--------+
11 rows in set (0.03 sec)

mysql> insert into work_project values(13,14);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbmslab10`.`work_project`, CONSTRAINT `work_project_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `Employee` (`emp_id`))
mysql> insert into work_project values("13","14");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbmslab10`.`work_project`, CONSTRAINT `work_project_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `Employee` (`emp_id`))
mysql> select * from Employee where emp_id not in (select eid from work_project)
    -> ;
+--------+------+--------+------------+---------------------+------------+------+--------+
| emp_id | d_id | e_name | phone      | email               | birthday   | sex  | salary |
+--------+------+--------+------------+---------------------+------------+------+--------+
|      2 |    2 | Aalam  | 8976784356 | alam@yahoo.com      | 1986-02-20 | M    |  35000 |
|     15 |    8 | Malay  | 9435687456 | malay2020@yahoo.com | 1985-04-12 | M    |  36000 |
+--------+------+--------+------------+---------------------+------------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from Employee where d_id in(select dept_id from department where dept_name="INFORMATION CENTRE");
+--------+------+--------+------------+------------------+------------+------+--------+
| emp_id | d_id | e_name | phone      | email            | birthday   | sex  | salary |
+--------+------+--------+------------+------------------+------------+------+--------+
|      2 |    2 | Aalam  | 8976784356 | alam@yahoo.com   | 1986-02-20 | M    |  35000 |
|      7 |    2 | Bikash | 8965755467 | bikash@gmail.com | 1983-04-28 | M    | 401400 |
+--------+------+--------+------------+------------------+------------+------+--------+
2 rows in set (0.00 sec)

mysql> select dept_name from Employee,department where Employee.d_id=department.dept_id and Employee.e_name="Aadarsh";
+-----------+
| dept_name |
+-----------+
| PLANNING  |
+-----------+
1 row in set (0.00 sec)

mysql> select * from Employee order by birthday desc limit 4;
+--------+------+-----------+-------------+---------------------+------------+------+--------+
| emp_id | d_id | e_name    | phone       | email               | birthday   | sex  | salary |
+--------+------+-----------+-------------+---------------------+------------+------+--------+
|      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com | 1987-10-28 | M    |  31000 |
|      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com    | 1987-02-19 | M    |  30000 |
|      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com    | 1986-11-26 | F    |  32500 |
|      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com      | 1986-02-20 | M    |  35000 |
+--------+------+-----------+-------------+---------------------+------------+------+--------+
4 rows in set (0.02 sec)

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

mysql> select * from Employee where MONTH(birthday)=MONTH(CURRENT_DATE);
+--------+------+----------+-------------+--------------------+------------+------+--------+
| emp_id | d_id | e_name   | phone       | email              | birthday   | sex  | salary |
+--------+------+----------+-------------+--------------------+------------+------+--------+
|      1 |    1 | Aadarsh  | 9878765432  | adarsh@gmail.com   | 1987-02-19 | M    |  30000 |
|      2 |    2 | Aalam    | 8976784356  | alam@yahoo.com     | 1986-02-20 | M    |  35000 |
|      3 |    4 | Gajendra | 0343-234565 | gajendra@gmail.com | 1983-02-09 | M    |  40000 |
+--------+------+----------+-------------+--------------------+------------+------+--------+
3 rows in set (0.00 sec)

mysql> select e_name from Employee,department where Employee.d_id=department.dept_id and department.dept_name="SOFTWARE SUPPORT" order by Employee.birthday desc limit 2;
+--------+
| e_name |
+--------+
| Anjali |
| Malay  |
+--------+
2 rows in set (0.00 sec)

mysql> select distinct e_name from Employee where emp_id in (select eid from work_project,project where pro_id=p_id and P_name="Robotics And Autonomous System");
+--------+
| e_name |
+--------+
| Anjali |
| Nilesh |
+--------+
2 rows in set (0.00 sec)

mysql> select datediff(e_date,curdate()) from project where p_name="Guest House Renovation";
+----------------------------+
| datediff(e_date,curdate()) |
+----------------------------+
|                      -2368 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select p_name,count(distinct eid) from work_project,project where work_project.pro_id=project.p_id group by p_name;
+--------------------------------+---------------------+
| p_name                         | count(distinct eid) |
+--------------------------------+---------------------+
| Campus Fire Alarm System       |                   3 |
| Guest House Renovation         |                   2 |
| INFORMATION SECURITY           |                   4 |
| Robotics And Autonomous System |                   2 |
+--------------------------------+---------------------+
4 rows in set (0.02 sec)

mysql> select e_name,p_name,salary from Employee left join work_project on emp_id=eid left join project on p_id=pro_id order by salary
    -> desc limit 1;
+--------+----------------------+--------+
| e_name | p_name               | salary |
+--------+----------------------+--------+
| Bikash | INFORMATION SECURITY | 401400 |
+--------+----------------------+--------+
1 row in set (0.00 sec)

mysql> select p_name,e_name from Employee left join work_project on emp_id=eid left join project on p_id=pro_id where e_date<current_date();
+--------------------------------+-----------+
| p_name                         | e_name    |
+--------------------------------+-----------+
| Campus Fire Alarm System       | Gajendra  |
| Campus Fire Alarm System       | Rakesh    |
| Campus Fire Alarm System       | Tapas     |
| Guest House Renovation         | Aadarsh   |
| Guest House Renovation         | Chiranjit |
| Robotics And Autonomous System | Nilesh    |
| Robotics And Autonomous System | Anjali    |
| INFORMATION SECURITY           | Nilesh    |
| INFORMATION SECURITY           | Anjali    |
| INFORMATION SECURITY           | Abhisekh  |
| INFORMATION SECURITY           | Bikash    |
+--------------------------------+-----------+
11 rows in set (0.00 sec)

mysql> select p_name from work_project inner join project on pro_id=p_id where eid in(select emp_id from Employee where sex="F");
+--------------------------------+
| p_name                         |
+--------------------------------+
| Robotics And Autonomous System |
| INFORMATION SECURITY           |
+--------------------------------+
2 rows in set (0.00 sec)

mysql> select e_name,p_name,e_date-s_date as duration from Employee left join work_project on emp_id=eid left join project on pro_id=p_id order by duration desc limit 1;
+--------+----------------------+----------+
| e_name | p_name               | duration |
+--------+----------------------+----------+
| Bikash | INFORMATION SECURITY |    29501 |
+--------+----------------------+----------+
1 row in set (0.00 sec)

mysql> select * from Employee order by salary desc limit 1,1;
+--------+------+----------+-------------+--------------------+------------+------+--------+
| emp_id | d_id | e_name   | phone       | email              | birthday   | sex  | salary |
+--------+------+----------+-------------+--------------------+------------+------+--------+
|      3 |    4 | Gajendra | 0343-234565 | gajendra@gmail.com | 1983-02-09 | M    |  40000 |
+--------+------+----------+-------------+--------------------+------------+------+--------+
1 row in set (0.00 sec)

mysql>