SHARE
TWEET

DBMS_LAB_AS3

realsdx Feb 14th, 2020 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 1. select sum(salary) from employee where MONTH(birthday)=2;
  2. +-------------+
  3. | sum(salary) |
  4. +-------------+
  5. |      105000 |
  6. +-------------+
  7. 1 row in set (0.00 sec)
  8.  
  9.  
  10. 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;
  11. +--------+------+-----------+-------------+-----------------------+------------+------+--------+-----------------------+-------------+
  12. | emp_id | d_id | e_name    | phone       | email                 | birthday   | sex  | salary | dept_name             | max(salary) |
  13. +--------+------+-----------+-------------+-----------------------+------------+------+--------+-----------------------+-------------+
  14. |      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com      | 1987-02-19 | M    |  30000 | PLANNING              |       30000 |
  15. |      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com        | 1986-02-20 | M    |  35000 | INFORMATION CENTER    |       40100 |
  16. |     10 |    3 | Nilesh    | 9854562123  |                       | 1983-10-12 | M    |  39800 | DEVELOPMENT CENTER    |       39800 |
  17. |      3 |    4 | Gajendra  | 0343-234565 | gajendra@gmail.com    | 1983-02-09 | M    |  40000 | MANUFACTURING SYSTEM  |       40000 |
  18. |      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com   | 1987-10-28 | M    |  31000 | ADMINISTRATION SYSTEM |       31000 |
  19. |      5 |    6 | Rakesh    | 9878765432  | rakesh@radiffmail.com | 1982-09-29 | M    |  29000 | SUPPORT SERVICES      |       29000 |
  20. |     11 |    7 | Tapas     | 0343-234345 | tapas@gmail.com       | 1985-10-15 | M    |  25500 | OPERATIONS            |       25500 |
  21. |      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com      | 1986-11-26 | F    |  32500 | SOFTWARE SUPPORT      |       34000 |
  22. +--------+------+-----------+-------------+-----------------------+------------+------+--------+-----------------------+-------------+
  23. 8 rows in set (0.00 sec)
  24.  
  25. 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='';
  26. +--------------------------------+
  27. | p_name                         |
  28. +--------------------------------+
  29. | Robotics and Autonomous System |
  30. | Information Security           |
  31. +--------------------------------+
  32. 2 rows in set (0.00 sec)
  33.  
  34. 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';
  35.  
  36. +--------------------------------+
  37. | p_name                         |
  38. +--------------------------------+
  39. | Robotics and Autonomous System |
  40. | Information Security           |
  41. +--------------------------------+
  42. 2 rows in set (0.00 sec)
  43.  
  44. 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;
  45. +-----------+--------------------------------+
  46. | e_name    | p_name                         |
  47. +-----------+--------------------------------+
  48. | Gajendra  | Campus Fire Alarm System       |
  49. | Rakesh    | Campus Fire Alarm System       |
  50. | Tapas     | Campus Fire Alarm System       |
  51. | Aadarsh   | Guest House Renovation         |
  52. | Chiranjit | Guest House Renovation         |
  53. | Nilesh    | Robotics and Autonomous System |
  54. | Anjali    | Robotics and Autonomous System |
  55. | Nilesh    | Information Security           |
  56. | Anjali    | Information Security           |
  57. | Abhishek  | Information Security           |
  58. | Bikash    | Information Security           |
  59. +-----------+--------------------------------+
  60. 11 rows in set (0.00 sec)
  61.  
  62. 6. select * from employee where d_id in(select dept_id from department where location="L1" or location="L3");
  63. +--------+------+---------+------------+------------------+------------+------+--------+
  64. | emp_id | d_id | e_name  | phone      | email            | birthday   | sex  | salary |
  65. +--------+------+---------+------------+------------------+------------+------+--------+
  66. |      1 |    1 | Aadarsh | 9878765432 | adarsh@gmail.com | 1987-02-19 | M    |  30000 |
  67. |     10 |    3 | Nilesh  | 9854562123 |                  | 1983-10-12 | M    |  39800 |
  68. +--------+------+---------+------------+------------------+------------+------+--------+
  69. 2 rows in set (0.00 sec)
  70.  
  71. 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);
  72. +----------+
  73. | e_name   |
  74. +----------+
  75. | Nilesh   |
  76. | Anjali   |
  77. | Abhishek |
  78. | Bikash   |
  79. +----------+
  80. 4 rows in set (0.00 sec)
  81.  
  82. 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%";
  83. +----------------------+
  84. | dept_name            |
  85. +----------------------+
  86. | PLANNING             |
  87. | INFORMATION CENTER   |
  88. | MANUFACTURING SYSTEM |
  89. | SOFTWARE SUPPORT     |
  90. | SOFTWARE SUPPORT     |
  91. +----------------------+
  92. 5 rows in set (0.00 sec)
  93.  
  94. 9. select * from employee where salary = (select min(salary) from employee where salary > (select min(salary) from employee));
  95. +--------+------+--------+------------+-----------------------+------------+------+--------+
  96. | emp_id | d_id | e_name | phone      | email                 | birthday   | sex  | salary |
  97. +--------+------+--------+------------+-----------------------+------------+------+--------+
  98. |      5 |    6 | Rakesh | 9878765432 | rakesh@radiffmail.com | 1982-09-29 | M    |  29000 |
  99. +--------+------+--------+------------+-----------------------+------------+------+--------+
  100. 1 row in set (0.00 sec)
  101.  
  102. 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;
  103. +--------------------------------+----------+
  104. | p_name                         | count(*) |
  105. +--------------------------------+----------+
  106. | Campus Fire Alarm System       |        3 |
  107. | Guest House Renovation         |        2 |
  108. | Information Security           |        4 |
  109. | Robotics and Autonomous System |        2 |
  110. +--------------------------------+----------+
  111. 4 rows in set (0.01 sec)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top