SHARE
TWEET

Untitled

a guest Oct 21st, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --Выбирите СУБД Oracle для выполнения лабораторной.
  2. --Cкопируйте файл  FPMI-stud\SUBFACULTY\кафИСУ\Исаченко\МДиСУБДII\EDU1.sql в каталог C:\TEMP .
  3. --Раскройте файл и ознакомтесь со скриптом создания и заполнения таблиц для выполнения лабораторной.
  4. -- Произведите запуск SQLPlus. или PLSQLDeveloper. или другого инструментария Oracle b соеденитесь с БД.  Запустите скрипт EDU.sql на выполнение.
  5. --Вставте в эту строку Ваши ФИО, номер группы, курса. Курч Дмитрий Леонидович, группа 3, курс 4.      
  6. --Файл с отчётом о выполнении лабораторной создаётся путём вставки соответсвующего select-предложения после строки с текстом задания.
  7. --Файл отчёта именуется фамилией студента  в английской транскрипции, с расширением .txt и сохраняется в каталог    fpmi-serv604\common-stud\Исаченко\Лаб1\гр_                        .
  8. --Тексты заданий:
  9. --1.  Выдать информацию об адресе отдела продаж (Sales) компании.
  10. SELECT deptaddress
  11. FROM dept
  12. WHERE deptname = 'Sales';
  13.  
  14. --2.  Выдать информацию обо всех работниках, родившихся не ранее 1 января 1985 года.
  15. SELECT *
  16. FROM emp
  17. WHERE birthdate >= to_date('01-01-1985', 'dd-mm-yyyy');
  18.  
  19. --3.  Найти минимальный оклад, предусмотренный для водителя  (Driver).
  20. SELECT minsalary
  21. FROM job
  22. WHERE jobname = 'Driver';
  23.  
  24. --4.  Подсчитать число работников, работавших в компании после  31 мая 2017 года хотя бы один день (31 мая 2017 года не включается).
  25. SELECT COUNT(DISTINCT empno)
  26. FROM career
  27. WHERE (startdate > to_date('31-05-2017','dd-mm-yyyy')
  28.        AND (startdate < enddate
  29.             OR enddate IS NULL));
  30.  
  31. --5.  Найти минимальные премии, начисленные в 2014, 2015, 2016, 2017 годах (указать год и минимальную. премию в хронологическом порядке).
  32. SELECT YEAR,
  33.        MIN(bonvalue)
  34. FROM bonus
  35. WHERE (YEAR > 2013
  36.        AND YEAR < 2018)
  37. GROUP BY YEAR
  38. ORDER BY YEAR;  
  39.  
  40. --6.  Выдать информацию о кодах всех должностей,  на которых работала работник Nina Tihanovich. Если Nina Tihanovich работает в настоящее время - должность также включается в искомый список.
  41. SELECT DISTINCT career.jobno
  42. FROM career
  43. INNER JOIN emp ON emp.empno = career.empno
  44. WHERE (emp.empname = 'Nina Tihanovich');
  45.  
  46. --7.  Выдать информацию о названиях должностей,  на которых работали работники Richard Martin и Jon Martin. Если один из них или оба  работают в настоящее время - должность также включается в искомый список. Должность выдаётся вместе с ФИО (empname) работника.
  47. SELECT DISTINCT career.jobno,
  48.                 job.jobname,
  49.                 emp.empname
  50. FROM career
  51. INNER JOIN emp ON emp.empno = career.empno
  52. INNER JOIN job ON job.jobno = career.jobno
  53. INNER JOIN emp ON emp.empno = career.empno
  54. WHERE (emp.empname = 'Richard Martin'
  55.        OR emp.empname = 'Jon Martin');
  56.  
  57. -- 8.  Найти фамилии, коды должностей и периоды работы (даты приема и даты увольнения) для всех клерков (Clerk) и водителей (Driver), работавших или работающих в компании. Для работающих дата увольнения для периода неопределена и при выводе либо отсутсвует, либо определяется как Null.
  58. SELECT emp.empname,
  59.        job.jobno,
  60.        career.startdate,
  61.        career.enddate
  62. FROM career
  63. INNER JOIN emp ON emp.empno = career.empno
  64. INNER JOIN job ON job.jobno = career.jobno
  65. WHERE (job.jobname = 'Clerk'
  66.        OR job.jobname = 'Driver');
  67.  
  68. -- 9.  Найти фамилии, названия должностей и периоды работы (даты приема и даты увольнения) для бухгалтеров (Accountant) и исполнительных директоров (Executive Director),  работавших или работающих в компании. Для работающих дата увольнения для периода неопределена и при выводе либо отсутсвует, либо определяется как Null.
  69. SELECT emp.empname,
  70.        job.jobname,
  71.        career.startdate,
  72.        career.enddate
  73. FROM career
  74. INNER JOIN emp ON emp.empno = career.empno
  75. INNER JOIN job ON job.jobno = career.jobno
  76. WHERE (job.jobname = 'Accountant'
  77.        OR job.jobname = 'Executive Director');
  78.  
  79.  
  80. -- 10.  Найти количество различных работников, работавших в отделе B02 в период с 01.01.2014 по 31.12.2017 хотя бы один день.
  81. SELECT COUNT(DISTINCT empno)
  82. FROM career
  83. WHERE ( deptid = 'B02'
  84.        AND startdate >= to_date('01.01.2014','dd.mm.yyyy')
  85.        AND enddate <= to_date('31.12.2017','dd.mm.yyyy')
  86.        AND startdate < enddate);
  87.  
  88. -- 11.  Найти фамилии этих работников.
  89. SELECT DISTINCT emp.empname
  90. FROM emp
  91. INNER JOIN career ON emp.empno = career.empno
  92. WHERE ( deptid = 'B02'
  93.        AND startdate >= to_date('01.01.2014','dd.mm.yyyy')
  94.        AND enddate <= to_date('31.12.2017','dd.mm.yyyy')
  95.        AND startdate < enddate);
  96.  
  97. --12.  Найти номера и названия отделов, в которых не было ни одного работника в период с 01.01.2015 по 31.12.2015.
  98. SELECT DISTINCT dept.deptid,
  99.                 dept.deptname
  100. FROM dept
  101. INNER JOIN career ON career.deptid = dept.deptid
  102. WHERE
  103.     ( SELECT COUNT(*)
  104.      FROM career
  105.      WHERE ( career.deptid = dept.deptid
  106.             AND career.startdate >= to_date('01.01.2015','dd-mm-yyyy')
  107.             AND career.enddate <= to_date('31.12.2015','dd-mm-yyyy') ) ) = 0;
  108.  
  109. --13.  Найти информацию о работниках (номер, фамилия), для которых нет начислений премии в период с 01.01. 2014 по  31.12.2015.
  110. SELECT DISTINCT emp.empno,
  111.                 emp.empname
  112. FROM emp
  113. LEFT OUTER JOIN bonus ON bonus.empno = emp.empno
  114. WHERE
  115.     ( SELECT COUNT(*)
  116.      FROM bonus
  117.      WHERE bonus.empno = emp.empno
  118.        AND bonus.YEAR BETWEEN 2014 AND 2015 ) = 0;
  119.  
  120. --14.  Найти количество работников, никогда не работавших  ни в исследовательском  (Research) отделе, ни в отделе поддержки (Support).
  121. SELECT COUNT(empno)
  122. FROM emp
  123. WHERE emp.empno NOT IN(
  124. SELECT empno
  125. FROM career
  126. JOIN dept ON career.deptid = dept.deptid
  127. WHERE dept.deptname = 'Research'
  128. OR dept.deptname = 'Support');
  129.  
  130. -- 15.  Найти коды и фамилии работников, работавших в двух и более отделах. Если работник работает в настоящее время, то отдел также учитывается.
  131. SELECT DISTINCT emp.empno,
  132.                 emp.empname
  133. FROM emp
  134. INNER JOIN career ON career.empno = emp.empno
  135. WHERE
  136.     ( SELECT COUNT(DISTINCT deptid)
  137.      FROM career
  138.      WHERE career.empno = emp.empno ) >= 2;
  139.  
  140. -- 16.  Найти коды и фамилии работников, работавших на двух и более должностях. Если работник работает в настоящее время, то должность также учитывается.
  141. SELECT DISTINCT emp.empno,
  142.                 emp.empname
  143. FROM emp
  144. INNER JOIN career ON career.empno = emp.empno
  145. WHERE
  146.     ( SELECT COUNT(DISTINCT jobno)
  147.      FROM career
  148.      WHERE career.empno = emp.empno ) >= 2;
  149.  
  150. -- 17.  Найти коды  и фамилии работников, суммарный стаж работы которых в компании не менее 4 лет.
  151. SELECT emp.empno, empname
  152. FROM emp
  153. JOIN career ON emp.empno = career.empno
  154. GROUP BY emp.empno, empname
  155. HAVING SUM(MONTHS_BETWEEN(NVL(enddate, CURRENT_DATE), startdate)) >= 4 * 12;
  156.  
  157. -- 18.  Найти всех работников (коды и фамилии), увольнявшихся хотя бы один раз.
  158. SELECT DISTINCT emp.empno,
  159.                 emp.empname
  160. FROM emp
  161. INNER JOIN career ON career.empno = emp.empno
  162. WHERE
  163.     ( SELECT COUNT(*)
  164.      FROM career
  165.      WHERE career.empno = emp.empno
  166.        AND enddate IS NOT NULL ) > 0;
  167.  
  168.  
  169. --19.  Найти среднии премии, начисленные за период в два 2014, 2015 года, и за период в два 2015, 2016 года, в разрезе работников (т.е. для работников, имевших начисления хотя бы в одном месяце двугодичного периода). Вывести id, имя и фимилию работника, период, среднюю премию.
  170. SELECT avg(bonvalue), '2014, 2015' AS years
  171. FROM bonus
  172. WHERE YEAR IN (2014, 2015) UNION
  173.     ( SELECT avg(bonvalue), '2015, 2016' AS years
  174.     FROM bonus
  175.     WHERE YEAR IN (2015, 2016) );
  176.  
  177. --20.  Найти отделы (id, название, адрес), в которых есть начисления премий в феврале 2017 года.
  178. SELECT DISTINCT dept.deptid, dept.deptname, dept.deptaddress FROM dept
  179. JOIN career ON career.deptid = dept.deptid JOIN bonus ON career.empno = bonus.empno
  180. WHERE bonus.YEAR = 2017
  181.     AND bonus.MONTH=2
  182.     AND (career.startdate <= to_date('28-02-2017','dd-mm-yyyy')
  183.     AND (career.enddate >= to_date('01-02-2017','dd-mm-yyyy') OR career.enddate IS NULL));
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