Kategoria: Oracle SQL

Zadanie 3.

a) Dla każdego klienta  pokaż datę oraz numer pierwszego złożonego zamówienia.

b) Policz, ile zamówień złożył każdy z klientów.

c) Pokaż klientów, którzy złożyli tylko jedno zamówienie – rozwiąż to na dwa sposoby.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_id, order_date, Wynik: a) 47 rows b) 47 rows c) 29 rows

Rozwiązanie

a) SELECT customer_id, MIN(order_id), MIN(order_date)
FROM orders
GROUP BY customer_id;

b) SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id;

c) SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) = 1;

lub:

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING MIN(order_date) = MAX(order_date);

[collapse]

Zadanie 2.

Sprawdź, którzy pracownicy „są bliżej niż dalej” pensji maksymalnej dla swojego stanowiska (tzn. przekroczyli próg połowy sumy będącej różnicą między płacą minimalną a maksymalną).

Schemat: HR, Tabele: Employees, Jobs, Kolumny: first_name, last_name, job_title, min_salary, max_salary, salary, Wynik: 25 rows

p2

 

 

Rozwiązanie

SELECT e.first_name, e.last_name, j.job_title, j.min_salary, e.salary, j.max_salary
FROM employees e INNER JOIN jobs j USING (job_id)
WHERE e.salary > j.min_salary + (j.max_salary - j.min_salary)/2;

[collapse]

Zadanie 1.

a) Policz średnią długość stażu pracy dla poszczególnych stanowisk.

b) W którym departamencie staż pracy jest najniższy, a w którym najwyższy? (średnie)

c) Znajdź pracowników, którzy mają więcej niż jeden okres zatrudnienia.

Schemat: HR, Tabela: Job_History, Kolumny: job_id, employee_id, start_date, end_date, department_id, Wynik: a) 8 rows b) 6 rows c) 3 rows

Rozwiązanie

a) SELECT job_id, AVG(end_date - start_date) as AVGHireDays
FROM Job_History
GROUP BY job_id
ORDER BY AVGHireDays DESC;

b) SELECT department_id, AVG(end_date - start_date) as AVGHireDays
FROM Job_History
GROUP BY department_id
ORDER BY AVGHireDays DESC;

c) SELECT employee_id, COUNT(*)
FROM Job_History
GROUP BY employee_id
HAVING COUNT (*) > 1;

[collapse]

Zadanie 10.

Z tabeli Customers (OE) wybierz:

a) klientów urodzonych w czerwcu, pochodzących z Ameryki

b) wszystkie zamężne włoszki (Italy)

c) klientów których nazwisko nie zaczyna się na L, ale zawiera ‚ea’

Schemat: OE, Tabela: Customers, Kolumny: Date_Of_Birth, nls_territory, gender, marital_status, cust_last_name, Wynik: a) 17 rows b) 8 rows c) 3 rows

Rozwiązanie

a) SELECT *
FROM customers
WHERE Extract(month from date_of_birth) = '06' AND nls_territory = 'AMERICA';

b) SELECT *
FROM customers
WHERE nls_territory = 'ITALY' AND gender = 'F' AND marital_status = 'married';

c) SELECT *
FROM customers
WHERE cust_last_name NOT LIKE 'L%' AND cust_last_name LIKE '%ea%';

[collapse]

 

Zadanie 9.

Tabela Employees (HR).

a) Policz wartość wynagrodzenia powiększoną o premię (commission_pct). Weź pod uwagę, że niektórzy nie mają premii (wtedy commission_pct ma wartość NULL).

b) Policz, który pracownik ile dostaje premii (jak wyżej – bierzemy pod uwagę istnienie NULL)

c) Do zestawienia z punktu b) dodaj obliczony staż pracy w latach (by wyniki były jednolite zakładamy, że wszyscy pracowali do 1 sierpnia 2008).

Schemat: HR, Tabela: Employees, Kolumny: First_Name, Last_Name, Salary, Commission_pct, Hire_Date, Wynik: 107 rows (a, b, c)

Rozwiązanie

a) SELECT first_name, last_name, salary * (1 + NVL(COMMISSION_PCT, 0)) as FullSalary
FROM Employees;

b) SELECT first_name, last_name, salary * NVL(COMMISSION_PCT, 0) as Premia
FROM Employees;

c) SELECT first_name, last_name, salary * NVL(COMMISSION_PCT, 0) as Premia,
TRUNC((to_date('2008-08-01', 'YYYY-MM-DD') - Hire_Date) / 365, 1) as Staz_Pracy_Lata
FROM Employees
ORDER BY Premia DESC, Staz_Pracy_Lata DESC;

[collapse]

Zadanie 7.

Korzystając z tabeli Employees (HR), stwórz zestawienie pracowników, tak jak przedstawiono na screenie, dla pracowników zarabiających między 10 a 14 tysięcy; wynik posortuj według zarobków (malejąco).

Schemat: HR, Tabela: Employees, Kolumny: First_Name, Last_name, Hire_Date, Salary, Wynik: 16 rows

07

 

 

Rozwiązanie

SELECT first_name||' '||last_name||' jest z nami od '||to_char(hire_date, 'DD.MM.YYYY')||' i zarabia '||salary as Zestawienie
FROM Employees
WHERE Salary BETWEEN 10000 AND 15000
ORDER BY Salary DESC;

[collapse]

Zadanie 6.

Z tabeli Orders (OE) wybierz zamówienia:

a) złożone w marcu 2007 roku

b) złożone w trybie innym niż direct i o statusie 4 lub 8

c) 10 zamówień o najwyższej wartości

Schemat: OE, Tabela: Orders, Kolumny: Order_Date, Order_Mode, Order-Status, Order_Total, Wynik: a) 7 rows b) 12 rows c) 10 rows

Rozwiązanie

a) SELECT *
FROM Orders
WHERE Order_Date >= To_Date('2007-03-01') AND Order_Date < To_Date('2007-04-01');

b) SELECT *
FROM Orders
WHERE Order_Mode <> 'direct' AND (Order_Status = 4 OR Order_Status = 8);

c) SELECT *
FROM Orders
ORDER BY Order_Total DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

[collapse]

Zadanie 5.

Korzystając z tabeli Jobs, znajdź:

a) największą płacę minimalną

b) najmniejszą płacę maksymalną

c) dla każdego stanowiska określ różnicę między płacą minimalną a maksymalną

Schemat: HR, Kolumny: Min_Salary, Max_Salary, Job_Title, Wynik: a) 1 row b) 1 row c) 19 rows

Rozwiązanie

a) SELECT max(min_salary)
FROM Jobs;

b) SELECT min(max_salary)
FROM Jobs;

c) SELECT Job_Title, (Max_salary - Min_Salary) as "Max - Min"
FROM Jobs;

[collapse]

Zadanie 4.

Z tabeli Job_History wybierz:

a) pracowników, którzy zostali zatrudnieni między rokiem 2005 a 2006

b) pracowników, którzy odeszli przed 2002 rokiem

c) pracowników o job_id równym AC_ACCOUNT lub ST_CLERK nie należą do departamentu o numerze 110

Schemat: HR, Tabela:  Job-History, Kolumny: Start_Date, End_Date, Job_ID, Department_ID, Wynik: a) 2 rows b) 2 rows c) 3 rows

Rozwiązanie

a) SELECT *
FROM job_history
WHERE START_DATE BETWEEN '05/01/01' AND '06/12/31' ; --RR/MM/DD

b) SELECT *
FROM job_history
WHERE END_DATE <= '01/12/31' ;

c) SELECT *
FROM job_history
WHERE (JOB_ID  = 'AC_ACCOUNT' OR JOB_ID = 'ST_CLERK') AND DEPARTMENT_ID <> 110;

[collapse]
« Starsze wpsiy Recent Entries »