S_Madanska

9.2.РЕШЕНИЯ

Feb 9th, 2022 (edited)
155
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -----------------------------------------------------------------------------------------
  2. --------------------------------Homework-------------------------------------------------
  3. -----------------------------------------------------------------------------------------
  4.     -- Задача
  5.     -- Имена, заплата и длъжност на служителите, които работят в отдел 80 и не са
  6.     --обработвали поръчки до момента;
  7.  
  8.     --1
  9.     SELECT E.FNAME,  E.LNAME,
  10.            E.SALARY,  
  11.            E.JOB_ID,      
  12.            E.DEPARTMENT_ID
  13.       FROM EMPLOYEES E LEFT JOIN ORDERS O
  14.         ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
  15.      WHERE E.DEPARTMENT_ID = 80 AND ORDER_ID IS NULL
  16.  
  17.     --2 С HAVING
  18.       SELECT E.FNAME, E.LNAME,
  19.              E.SALARY,
  20.              E.JOB_ID,
  21.              E.DEPARTMENT_ID,
  22.              COUNT(O.ORDER_ID)
  23.         FROM EMPLOYEES E LEFT JOIN ORDERS O
  24.           ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
  25.        WHERE E.DEPARTMENT_ID = 80
  26.     GROUP BY E.FNAME, E.LNAME, E.SALARY, E.JOB_ID, E.DEPARTMENT_ID
  27.       HAVING COUNT(O.ORDER_ID) = 0
  28.  
  29.     -- Задача 4-9.
  30.     -- Да се изведат имената на всички клиенти и id на поръчките им.
  31.     -- В резултатния набор да участват и клиентите, които все още не са правили поръчки.
  32.  
  33.     SELECT C.FNAME, LNAME, O.ORDER_ID
  34.           -- ,ISNULL(CAST(O.ORDER_ID AS VARCHAR), '-') AS ПОРЪЧКИ
  35.     FROM CUSTOMERS C LEFT JOIN ORDERS O
  36.     ON O.CUSTOMER_ID=C.CUSTOMER_ID
  37.     ORDER BY ORDER_ID
  38.  
  39.     -- Задача 4-11.
  40.     -- Да се изведат имената на всички клиенти, които са от държави в регион „Западна Европа“
  41.    SELECT CU.FNAME + ' ' + CU.LNAME AS CUSTOMER,
  42.           CO.NAME AS COUNTRY,
  43.           R.NAME AS REGION
  44.      FROM CUSTOMERS CU, COUNTRIES CO, REGIONS R
  45.     WHERE CU.COUNTRY_ID=CO.COUNTRY_ID
  46.           AND R.REGION_ID=CO.REGION_ID
  47.           AND R.NAME = 'Западна Европа'
  48.  
  49. -----------------------------------------------------------------------------------------
  50. --------------------------------- Задачи ОТ СЕДМИЦА 6:  ---------------------------------
  51. -----------------------------------------------------------------------------------------
  52.     --Зад 0.4
  53.     --Клиентите, които са поръчвали през 2000 година
  54.     SELECT C.FNAME, C.LNAME,
  55.            O.ORDER_ID,
  56.            O.ORDER_DATE
  57.            --,DATENAME(MONTH, O.ORDER_DATE) + ' ' + CAST (YEAR(O.ORDER_DATE) AS VARCHAR) AS DATE
  58.     FROM  CUSTOMERS C JOIN  ORDERS O
  59.     ON C.CUSTOMER_ID = O.CUSTOMER_ID
  60.     WHERE YEAR(O.ORDER_DATE) = 2000
  61.     --AND MONTH(O.ORDER_dATE) = 7
  62.     ORDER BY MONTH(O.ORDER_DATE)
  63.  
  64.     --ЗАД 0.5
  65.     --Служителите, които са обработили повече от 5 поръчки
  66.     SELECT COUNT(O.ORDER_ID) ORD_COUNT,
  67.            E.FNAME, E.LNAME,
  68.            E.EMPLOYEE_ID
  69.     FROM EMPLOYEES E JOIN ORDERS O
  70.     ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
  71.     GROUP BY E.FNAME, E.LNAME, E.EMPLOYEE_ID
  72.     HAVING COUNT(O.ORDER_ID) > 5
  73.  
  74.   -----------------------------------------------------------------------------------------
  75.   ----------------------------------ПРЕПИТКА 2---------------------------------------------
  76.   -----------------------------------------------------------------------------------------
  77.         --ЗАД 1.
  78.         --Клиентите, които са поръчали само веднъж. Сортирай по малко име във възходящ ред
  79.          SELECT COUNT(ORDER_ID) AS ПОРЪЧКИ,
  80.                C.CUSTOMER_ID,
  81.                C.FNAME,C.LNAME
  82.         FROM CUSTOMERS C JOIN ORDERS O
  83.         ON C.CUSTOMER_ID = O.CUSTOMER_ID
  84.         GROUP BY C.CUSTOMER_ID, C.FNAME, C.LNAME
  85.         HAVING  COUNT(ORDER_ID) = 1
  86.         ORDER BY FNAME
  87.  
  88.          --ЗАД 2.
  89.         --Изведи минималната заплата на длъжностите, в които има назначени повече от 10 служителя
  90.          SELECT COUNT(E.JOB_ID) AS СЛУЖИТЕЛИ,
  91.                J.JOB_TITLE,
  92.                J.MIN_SALARY
  93.         FROM EMPLOYEES E JOIN JOBS J
  94.         ON J.JOB_ID = E.JOB_ID
  95.         GROUP BY J.MIN_SALARY, J.JOB_TITLE
  96.         HAVING COUNT(E.JOB_ID) > 10
  97.  
  98.          --ЗАД 3.
  99.         --Изведи длънжостите, на които няма назначени служители
  100.          SELECT COUNT(E.JOB_ID) AS СЛУЖИТЕЛИ,
  101.                J.JOB_ID,
  102.                J.JOB_TITLE
  103.         FROM EMPLOYEES E RIGHT JOIN JOBS J
  104.         ON J.JOB_ID = E.JOB_ID
  105.         GROUP BY J.MIN_SALARY, J.JOB_TITLE,  J.JOB_ID
  106.         HAVING COUNT(E.JOB_ID) = 0
  107.  
  108.          --ЗАД 4.
  109.         --В кой град се намира отдела, чийто служители получават най-голяма средна заплата
  110.          SELECT TOP 1 AVG(E.SALARY),
  111.                       E.DEPARTMENT_ID,
  112.                       D.CITY
  113.         FROM EMPLOYEES E JOIN DEPARTMENTS D
  114.         ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
  115.         GROUP BY E.DEPARTMENT_ID, D.CITY
  116.         ORDER BY 1 DESC
  117.  
  118. -----------------------------------------------------------------------------------------
  119. ---------------------------------4.7.2. OFFSET и FETCH ----------------------------------
  120. -----------------------------------------------------------------------------------------
  121.     --ЗАД 4. OT ПРЕПИТКА 2 МОЖЕ ДА БЪДЕ РЕШЕНА И С OFFSET и FETCH
  122.     --В кой град се намира отдела, чийто служители получават най-голяма средна заплата
  123.         SELECT     AVG(E.SALARY) AS S,
  124.                    E.DEPARTMENT_ID,  D.CITY
  125.         FROM EMPLOYEES E  JOIN DEPARTMENTS D
  126.         ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
  127.         GROUP BY E.DEPARTMENT_ID, D.CITY
  128.         ORDER BY 1 DESC
  129.         OFFSET 0 ROW                      ---ПРЕМАХВАНЕ НА БР.РЕДОВЕ
  130.         FETCH NEXT 1 ROW ONLY             ---ИЗВЕЖДАНЕ НА СЛЕДВАЩИТЕ БР.РЕДОВЕ
  131.  
  132.  
  133.     --Пример 4-21.
  134.     --Всички служители, подредени по дата на постъпване.
  135.      SELECT *  
  136.      FROM EMPLOYEES
  137.      ORDER BY HIRE_DATE
  138.  
  139.     -- Петимата служители, започвайки от 10-ти ред, подредени по дата на постъпване.
  140.      SELECT *
  141.      FROM EMPLOYEES
  142.      ORDER BY HIRE_DATE
  143.      OFFSET 9 ROWS
  144.      FETCH NEXT 5 ROWS ONLY
  145.  
  146.     --Задача 4-12.
  147.     --вторите 10 най-добре платени служители (подредени по заплата низходящо).  */
  148.     SELECT *
  149.     FROM EMPLOYEES
  150.     ORDER BY SALARY DESC
  151.     OFFSET 10 ROWS
  152.     FETCH NEXT 10 ROWS ONLY
  153.  
  154. -----------------------------------------------------------------------------------------
  155. --------------------------------Homework-------------------------------------------------
  156. -----------------------------------------------------------------------------------------
  157.   /* Задача 4-13.
  158.    Да се изведат име, фамилия и пол на клиентите, направили последните 5 поръчки.*/
  159.  
  160. ----------------------------------------------------------------------------------------
  161. -----------------------------Изгледи = Views--------------------------------------------
  162. -----------------------------Създаване на изгледи-CREATE VIEW---------------------------
  163.  
  164.     --Пример 5-1.
  165.     --Да се създаде изглед, който
  166.     --съдържа име и фамилия на клиентите, както и
  167.     --номер и дата на поръчките, които те са направили.
  168.     CREATE VIEW MY_FIRST_VIEW
  169.     AS
  170.     SELECT C.FNAME + ' ' + C.LNAME AS CUSTOMER, --- ВСИЧКИ КОЛОНИ ТРЯБВА ДА ИМАТ ИМЕНА
  171.            O.ORDER_ID,
  172.            O.ORDER_DATE
  173.     FROM CUSTOMERS C JOIN ORDERS O
  174.     ON O.CUSTOMER_ID = C.CUSTOMER_ID
  175.  
  176.     SELECT * FROM MY_FIRST_VIEW
RAW Paste Data Copied