|
|
Numerowana lista pracowników
SELECT
RowNum AS LP,
employees.FIRST_NAME,
employees.LAST_NAME,
employees.SALARY
FROM
employees
ORDER BY LP
SELECT
COUNT(*) Over (Order By CONCAT(first_name, last_name)) AS LP,
employees.FIRST_NAME,
employees.LAST_NAME,
employees.SALARY
FROM
employees
ORDER
BY LP
SELECT
COUNT(*) Over (Order By employees.SALARY DESC) AS LP,
employees.FIRST_NAME,
employees.LAST_NAME,
employees.SALARY
FROM employees
ORDER
BY employees.SALARY DESC
SELECT
(SELECT COUNT(*)
FROM EMPLOYEES std
WHERE std.FIRST_NAME <= EMPLOYEES.FIRST_NAME ) AS LP,
EMPLOYEES.FIRST_NAME, EMPLOYEES.SALARY
FROM EMPLOYEES
ORDER BY 1
SELECT
COUNT(EMPLOYEES.FIRST_NAME) Over (Order By EMPLOYEES.FIRST_NAME) AS LP,
EMPLOYEES.FIRST_NAME, EMPLOYEES.SALARY,
EMPLOYEES.LAST_NAME
FROM EMPLOYEES
ORDER BY 1
select count(*) over (order by FIRST_NAME) as LP,
FIRST_NAME,
SALARY from
employees
order by 1
Od pracownika do dytektora
select
LAST_NAME from employees
start with LAST_NAME = 'Chen'
connect by prior
MANAGER_ID = EMPLOYEE_ID
Informacja o płacach pracowników
SELECT SUM(EMPLOYEES.SALARY) AS "Suma płac",
MIN(EMPLOYEES.SALARY) AS "Płaca minimalna",
ROUND(AVG(EMPLOYEES.SALARY), 2) AS "Płaca średnia",
MAX(EMPLOYEES.SALARY) AS "Płaca maksymalna"
FROM EMPLOYEES
Dział, liczba pracowników, imię i nazwisko szefa, suma płac, płaca średnia
SELECT
DEPARTMENTS.DEPARTMENT_NAME AS Dział,
COUNT(Pracownicy.EMPLOYEE_ID) AS "Liczba prac",
Szefowie.FIRST_NAME
AS "Szef imię",
Szefowie.LAST_NAME
AS "Szef nazwisko",
SUM(Pracownicy.SALARY)
AS "Suma pałc",
ROUND(AVG(Pracownicy.SALARY), 2) AS "Płaca średnia"
FROM
DEPARTMENTS INNER JOIN EMPLOYEES Szefowie
ON Szefowie.EMPLOYEE_ID = DEPARTMENTS.MANAGER_ID
INNER JOIN EMPLOYEES Pracownicy
ON DEPARTMENTS.DEPARTMENT_ID = Pracownicy.DEPARTMENT_ID
GROUP BY
DEPARTMENTS.DEPARTMENT_NAME,
Szefowie.FIRST_NAME,
Szefowie.LAST_NAME
ORDER BY
DEPARTMENTS.DEPARTMENT_NAME
Podsumawanie Płac i Pracowników według regionów krajów i miast
SELECT
REGIONS.REGION_NAME AS Region,
COUNTRIES.COUNTRY_NAME AS Kraj,
LOCATIONS.CITY AS Miasto,
COUNT(EMPLOYEES.EMPLOYEE_ID) AS "Liczba pracownikow",
ROUND(AVG(EMPLOYEES.SALARY)) AS "Srednia placa"
FROM
REGIONS INNER JOIN COUNTRIES
ON REGIONS.REGION_ID = COUNTRIES.REGION_ID
INNER JOIN
LOCATIONS
ON COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID
INNER JOIN DEPARTMENTS
ON LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID
INNER JOIN EMPLOYEES
ON
DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
GROUP BY
REGIONS.REGION_NAME,
COUNTRIES.COUNTRY_NAME,
LOCATIONS.CITY
ORDER BY ROUND(AVG(EMPLOYEES.SALARY)) DESC
|