SHARE
TWEET

sql ass

a guest Feb 14th, 2020 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. mysql> show databases;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. | dbmslab10          |
  7. | mysql              |
  8. | performance_schema |
  9. +--------------------+
  10. 4 rows in set (0.01 sec)
  11.  
  12. mysql> use dbmslab10;
  13. Reading table information for completion of table and column names
  14. You can turn off this feature to get a quicker startup with -A
  15.  
  16. Database changed
  17. mysql> show tables;
  18. +---------------------+
  19. | Tables_in_dbmslab10 |
  20. +---------------------+
  21. | Abhishek10          |
  22. | Employee            |
  23. | department          |
  24. | employee            |
  25. | project             |
  26. | work_project        |
  27. +---------------------+
  28. 6 rows in set (0.00 sec)
  29.  
  30. mysql> select * from Employee;
  31. +--------+------+-----------+-------------+-----------------------+------------+------+--------+
  32. | emp_id | d_id | e_name    | phone       | email                 | birthday   | sex  | salary |
  33. +--------+------+-----------+-------------+-----------------------+------------+------+--------+
  34. |      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com      | 1987-02-19 | M    |  30000 |
  35. |      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com        | 1986-02-20 | M    |  35000 |
  36. |      3 |    4 | Gajendra  | 0343-234565 | gajendra@gmail.com    | 1983-02-09 | M    |  40000 |
  37. |      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com   | 1987-10-28 | M    |  31000 |
  38. |      5 |    6 | Rakesh    | 9878765432  | rakesh@rediffmail.com | 1982-09-29 | M    |  29000 |
  39. |      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com      | 1986-11-26 | F    |  32500 |
  40. |      7 |    2 | Bikash    | 8965755467  | bikash@gmail.com      | 1983-04-28 | M    | 401400 |
  41. |     10 |    3 | Nilesh    | 9854562123  | NULL                  | 1983-10-12 | M    |  39800 |
  42. |     11 |    7 | Tapas     | 0343-234345 | tapas@gmail.com       | 1985-10-15 | M    |  25500 |
  43. |     12 |    8 | Abhisekh  | 9834323456  | abhisekh@yahoo.com    | 1982-03-20 | M    |  34000 |
  44. +--------+------+-----------+-------------+-----------------------+------------+------+--------+
  45. 10 rows in set (0.00 sec)
  46.  
  47. mysql> select * from project;
  48. +------+--------------------------------+------------+------------+
  49. | p_id | p_name                         | s_date     | e_date     |
  50. +------+--------------------------------+------------+------------+
  51. |   11 | Campus Fire Alarm System       | 2010-01-12 | 2011-02-08 |
  52. |   12 | Guest House Renovation         | 2012-02-07 | 2013-08-21 |
  53. |   13 | Robotics And Autonomous System | 2011-06-23 | 2013-06-04 |
  54. |   14 | INFORMATION SECURITY           | 2009-07-28 | 2012-02-29 |
  55. +------+--------------------------------+------------+------------+
  56. 4 rows in set (0.00 sec)
  57.  
  58. mysql> select * from department;
  59. +---------+----------------------+----------+
  60. | dept_id | dept_name            | location |
  61. +---------+----------------------+----------+
  62. |       1 | PLANNING             | L1       |
  63. |       2 | INFORMATION CENTRE   | L2       |
  64. |       3 | DEVELOPMENT CENTRE   | L3       |
  65. |       4 | MANUFACTURING SYSTEM | L4       |
  66. |       5 | ADMINISTRATION SYSTE | L5       |
  67. |       6 | SUPPORT SERVICES     | L6       |
  68. |       7 | OPERATIONS           | L7       |
  69. |       8 | SOFTWARE SUPPORT     | L7       |
  70. +---------+----------------------+----------+
  71. 8 rows in set (0.00 sec)
  72.  
  73. mysql> select * from work_project;
  74. +------+--------+
  75. | eid  | pro_id |
  76. +------+--------+
  77. |    1 |     12 |
  78. |    3 |     11 |
  79. |   10 |     14 |
  80. |   10 |     13 |
  81. |    6 |     14 |
  82. |    5 |     11 |
  83. |    6 |     13 |
  84. |   11 |     11 |
  85. |    4 |     12 |
  86. |   12 |     14 |
  87. |    7 |     14 |
  88. +------+--------+
  89. 11 rows in set (0.00 sec)
  90.  
  91. mysql> select * from employee;
  92. +------+--------+-----------+------------+------------+---------+------------+----------+-------------+
  93. | id   | f_name | last_name | startdate  | enddate    | salary  | phone      | CITY     | deSTINATION |
  94. +------+--------+-----------+------------+------------+---------+------------+----------+-------------+
  95. |    1 | Jason  | Martin    | 1996-07-25 | 2006-07-25 | 2234.56 | 99999999   | Toronto  | Programmin  |
  96. |    2 | Alison | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | 99999999   | Vancover | Tester      |
  97. |    4 | Celia  | Rice      | 1982-10-24 | 1999-04-21 | 3344.78 | 99999999   | Vancover | Manager     |
  98. |    5 | Robert | Black     | 1984-01-15 | 1998-08-08 | 3344.78 | 9999999999 | Vancover | Tester      |
  99. |    6 | Linda  | Green     | 1987-07-30 | 1996-01-04 | 5322.78 | 99999999   | Newyork  | Tester      |
  100. |    7 | David  | Larry     | 1990-12-31 | 1998-02-12 | 5322.78 | 99999999   | Newyork  | Manager     |
  101. +------+--------+-----------+------------+------------+---------+------------+----------+-------------+
  102. 6 rows in set (0.02 sec)
  103.  
  104. mysql> insert into Employee values(15,8,"Malay","9435687456","malay2020@yahoo.com","1985-04-12","M",36000);
  105. Query OK, 1 row affected (0.05 sec)
  106.  
  107. mysql> select * from Employee;
  108. +--------+------+-----------+-------------+-----------------------+------------+------+--------+
  109. | emp_id | d_id | e_name    | phone       | email                 | birthday   | sex  | salary |
  110. +--------+------+-----------+-------------+-----------------------+------------+------+--------+
  111. |      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com      | 1987-02-19 | M    |  30000 |
  112. |      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com        | 1986-02-20 | M    |  35000 |
  113. |      3 |    4 | Gajendra  | 0343-234565 | gajendra@gmail.com    | 1983-02-09 | M    |  40000 |
  114. |      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com   | 1987-10-28 | M    |  31000 |
  115. |      5 |    6 | Rakesh    | 9878765432  | rakesh@rediffmail.com | 1982-09-29 | M    |  29000 |
  116. |      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com      | 1986-11-26 | F    |  32500 |
  117. |      7 |    2 | Bikash    | 8965755467  | bikash@gmail.com      | 1983-04-28 | M    | 401400 |
  118. |     10 |    3 | Nilesh    | 9854562123  | NULL                  | 1983-10-12 | M    |  39800 |
  119. |     11 |    7 | Tapas     | 0343-234345 | tapas@gmail.com       | 1985-10-15 | M    |  25500 |
  120. |     12 |    8 | Abhisekh  | 9834323456  | abhisekh@yahoo.com    | 1982-03-20 | M    |  34000 |
  121. |     15 |    8 | Malay     | 9435687456  | malay2020@yahoo.com   | 1985-04-12 | M    |  36000 |
  122. +--------+------+-----------+-------------+-----------------------+------------+------+--------+
  123. 11 rows in set (0.03 sec)
  124.  
  125. mysql> insert into work_project values(13,14);
  126. 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`))
  127. mysql> insert into work_project values("13","14");
  128. 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`))
  129. mysql> select * from Employee where emp_id not in (select eid from work_project)
  130.     -> ;
  131. +--------+------+--------+------------+---------------------+------------+------+--------+
  132. | emp_id | d_id | e_name | phone      | email               | birthday   | sex  | salary |
  133. +--------+------+--------+------------+---------------------+------------+------+--------+
  134. |      2 |    2 | Aalam  | 8976784356 | alam@yahoo.com      | 1986-02-20 | M    |  35000 |
  135. |     15 |    8 | Malay  | 9435687456 | malay2020@yahoo.com | 1985-04-12 | M    |  36000 |
  136. +--------+------+--------+------------+---------------------+------------+------+--------+
  137. 2 rows in set (0.00 sec)
  138.  
  139. mysql> select * from Employee where d_id in(select dept_id from department where dept_name="INFORMATION CENTRE");
  140. +--------+------+--------+------------+------------------+------------+------+--------+
  141. | emp_id | d_id | e_name | phone      | email            | birthday   | sex  | salary |
  142. +--------+------+--------+------------+------------------+------------+------+--------+
  143. |      2 |    2 | Aalam  | 8976784356 | alam@yahoo.com   | 1986-02-20 | M    |  35000 |
  144. |      7 |    2 | Bikash | 8965755467 | bikash@gmail.com | 1983-04-28 | M    | 401400 |
  145. +--------+------+--------+------------+------------------+------------+------+--------+
  146. 2 rows in set (0.00 sec)
  147.  
  148. mysql> select dept_name from Employee,department where Employee.d_id=department.dept_id and Employee.e_name="Aadarsh";
  149. +-----------+
  150. | dept_name |
  151. +-----------+
  152. | PLANNING  |
  153. +-----------+
  154. 1 row in set (0.00 sec)
  155.  
  156. mysql> select * from Employee order by birthday desc limit 4;
  157. +--------+------+-----------+-------------+---------------------+------------+------+--------+
  158. | emp_id | d_id | e_name    | phone       | email               | birthday   | sex  | salary |
  159. +--------+------+-----------+-------------+---------------------+------------+------+--------+
  160. |      4 |    5 | Chiranjit | 033-2345434 | chiranjit@yahoo.com | 1987-10-28 | M    |  31000 |
  161. |      1 |    1 | Aadarsh   | 9878765432  | adarsh@gmail.com    | 1987-02-19 | M    |  30000 |
  162. |      6 |    8 | Anjali    | 89654534    | anjali@yahoo.com    | 1986-11-26 | F    |  32500 |
  163. |      2 |    2 | Aalam     | 8976784356  | alam@yahoo.com      | 1986-02-20 | M    |  35000 |
  164. +--------+------+-----------+-------------+---------------------+------------+------+--------+
  165. 4 rows in set (0.02 sec)
  166.  
  167. mysql> select e_name,phone from Employee where phone like "%-%";
  168. +-----------+-------------+
  169. | e_name    | phone       |
  170. +-----------+-------------+
  171. | Gajendra  | 0343-234565 |
  172. | Chiranjit | 033-2345434 |
  173. | Tapas     | 0343-234345 |
  174. +-----------+-------------+
  175. 3 rows in set (0.00 sec)
  176.  
  177. mysql> select * from Employee where MONTH(birthday)=MONTH(CURRENT_DATE);
  178. +--------+------+----------+-------------+--------------------+------------+------+--------+
  179. | emp_id | d_id | e_name   | phone       | email              | birthday   | sex  | salary |
  180. +--------+------+----------+-------------+--------------------+------------+------+--------+
  181. |      1 |    1 | Aadarsh  | 9878765432  | adarsh@gmail.com   | 1987-02-19 | M    |  30000 |
  182. |      2 |    2 | Aalam    | 8976784356  | alam@yahoo.com     | 1986-02-20 | M    |  35000 |
  183. |      3 |    4 | Gajendra | 0343-234565 | gajendra@gmail.com | 1983-02-09 | M    |  40000 |
  184. +--------+------+----------+-------------+--------------------+------------+------+--------+
  185. 3 rows in set (0.00 sec)
  186.  
  187. 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;
  188. +--------+
  189. | e_name |
  190. +--------+
  191. | Anjali |
  192. | Malay  |
  193. +--------+
  194. 2 rows in set (0.00 sec)
  195.  
  196. 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");
  197. +--------+
  198. | e_name |
  199. +--------+
  200. | Anjali |
  201. | Nilesh |
  202. +--------+
  203. 2 rows in set (0.00 sec)
  204.  
  205. mysql> select datediff(e_date,curdate()) from project where p_name="Guest House Renovation";
  206. +----------------------------+
  207. | datediff(e_date,curdate()) |
  208. +----------------------------+
  209. |                      -2368 |
  210. +----------------------------+
  211. 1 row in set (0.00 sec)
  212.  
  213. mysql> select p_name,count(distinct eid) from work_project,project where work_project.pro_id=project.p_id group by p_name;
  214. +--------------------------------+---------------------+
  215. | p_name                         | count(distinct eid) |
  216. +--------------------------------+---------------------+
  217. | Campus Fire Alarm System       |                   3 |
  218. | Guest House Renovation         |                   2 |
  219. | INFORMATION SECURITY           |                   4 |
  220. | Robotics And Autonomous System |                   2 |
  221. +--------------------------------+---------------------+
  222. 4 rows in set (0.02 sec)
  223.  
  224. 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
  225.     -> desc limit 1;
  226. +--------+----------------------+--------+
  227. | e_name | p_name               | salary |
  228. +--------+----------------------+--------+
  229. | Bikash | INFORMATION SECURITY | 401400 |
  230. +--------+----------------------+--------+
  231. 1 row in set (0.00 sec)
  232.  
  233. 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();
  234. +--------------------------------+-----------+
  235. | p_name                         | e_name    |
  236. +--------------------------------+-----------+
  237. | Campus Fire Alarm System       | Gajendra  |
  238. | Campus Fire Alarm System       | Rakesh    |
  239. | Campus Fire Alarm System       | Tapas     |
  240. | Guest House Renovation         | Aadarsh   |
  241. | Guest House Renovation         | Chiranjit |
  242. | Robotics And Autonomous System | Nilesh    |
  243. | Robotics And Autonomous System | Anjali    |
  244. | INFORMATION SECURITY           | Nilesh    |
  245. | INFORMATION SECURITY           | Anjali    |
  246. | INFORMATION SECURITY           | Abhisekh  |
  247. | INFORMATION SECURITY           | Bikash    |
  248. +--------------------------------+-----------+
  249. 11 rows in set (0.00 sec)
  250.  
  251. 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");
  252. +--------------------------------+
  253. | p_name                         |
  254. +--------------------------------+
  255. | Robotics And Autonomous System |
  256. | INFORMATION SECURITY           |
  257. +--------------------------------+
  258. 2 rows in set (0.00 sec)
  259.  
  260. 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;
  261. +--------+----------------------+----------+
  262. | e_name | p_name               | duration |
  263. +--------+----------------------+----------+
  264. | Bikash | INFORMATION SECURITY |    29501 |
  265. +--------+----------------------+----------+
  266. 1 row in set (0.00 sec)
  267.  
  268. mysql> select * from Employee order by salary desc limit 1,1;
  269. +--------+------+----------+-------------+--------------------+------------+------+--------+
  270. | emp_id | d_id | e_name   | phone       | email              | birthday   | sex  | salary |
  271. +--------+------+----------+-------------+--------------------+------------+------+--------+
  272. |      3 |    4 | Gajendra | 0343-234565 | gajendra@gmail.com | 1983-02-09 | M    |  40000 |
  273. +--------+------+----------+-------------+--------------------+------------+------+--------+
  274. 1 row in set (0.00 sec)
  275.  
  276. mysql>
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top