Korzystając z tabeli Employees (HR), znajdź właściciela firmy przy użyciu kolumny manager_id.
Rozwiązanie
SELECT *
FROM Employees
WHERE Manager_Id IS NULL;
[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
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]
Zadanie 3.
Z tabeli Locations wybierz:
a) Wszystkie adresy w Londynie
b) Wszystkie lokalizacje które są w państwach o id IT, US i CH
c) Lokalizacje, w których nazwa miasta nie zaczyna się na literę S ale ma tą literę w nazwie
d) Lokacje, które nie mają regionu. Pole region powinno zawierać informację ‘<brak regionu>’.
Schemat: HR, Tabela: Locations, Kolumny: City, Country-ID, State_Province Wynik: a) 1 row b) 8 rows c) 2 rows d) 6 rows
Rozwiązanie
a) SELECT *
FROM LOCATIONS
WHERE City = 'London';
b) SELECT *
FROM LOCATIONS
WHERE COUNTRY_ID IN('IT', 'US', 'CH');
c) SELECT *
FROM LOCATIONS
WHERE City NOT LIKE 'S%' AND City LIKE '_%s%';
d) SELECT STREET_ADDRESS, POSTAL_CODE, CITY, NVL(STATE_PROVINCE, '<brak regionu>'), COUNTRY_ID
FROM LOCATIONS
WHERE STATE_PROVINCE IS NULL;
[collapse]
Zadanie 2.
Z tabeli Employees wybierz:
a) wszystkich o job_id równym SA_REP
b) z wynagrodzeniem pomiędzy 2000 a 5000
c) zatrudnieni przed rokiem 2003
d) tych, którzy nie pracują w departamentach o ID 50 oraz 80
e) tych, których numer telefonu zawiera dwie cyfry 7 (w dowolnym miejscu)
Schemat: HR, Tabela: Employees, Kolumny: Job_ID, Salary, Hire_Date, Department_ID, Phone_Number, Wynik: a) 30 rows, b) 49 rows c) 8 rows d) 27 rows e) 6 rows
Rozwiązanie
a) SELECT *
FROM EMPLOYEES
WHERE job_id = 'SA_REP';
b) SELECT *
FROM EMPLOYEES
WHERE salary BETWEEN 2000 AND 5000;
c) SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE < '03/01/01'; --RR/MM/DD
d) SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID <> 50 AND DEPARTMENT_ID <> 80;
e) SELECT *
FROM EMPLOYEES
WHERE PHONE_NUMBER LIKE '%7%7%';
[collapse]
Zadanie 1.
Napisz powitanie Hello World. Kolumna powinna nazywać się “Powitanie”.
Schemat: – , Tabela: dual, Kolumny: -, Wynik: 1 row
Rozwiązanie
SELECT 'Hello World' as Powitanie
FROM dual;
[collapse]
Zadanie 6.
1. Stwórz tabelę zawierającą pola: Imię, Nazwisko, Pesel, Data, gdzie Pesel będzie kluczem głównym; żadne z pól nie może być puste, z wyjątkiem Daty. Dobierz odpowiednie typy danych.
2. Uzupełnij tabelę danymi (dane w pełni fikcyjne, wymyślone na potrzeby ćwiczenia):
('Anna', 'Braun', '70010112365'),
('Celia', 'Donut', '74011112365'),
('Ellie', 'Fine', '85101512365'),
('Gloria', 'Hint', '79051412369'),
('Ian', 'Johnson', '82021685214'),
('Kelly', 'Long', '64081725874'),
('Merry', 'Nice', '89120425896');
3. Wypełnij kolumnę Data danymi – ma się w niej znaleźć data urodzenia danej osoby, ustalona na podstawie numeru pesel.
Baza: Northwind, Tabela: PeselTab (Twoja tabela), kolumny: Imie, Nazwisko, Pesel, Data, Wynik: 7 rows
Rozwiązanie
1. USE Northwind;
CREATE TABLE PeselTab (
Imie VARCHAR(20) NOT NULL,
Nazwisko VARCHAR(25) NOT NULL,
Pesel VARCHAR(11) PRIMARY KEY,
Data DATE );
2. INSERT INTO PeselTab(Imie, Nazwisko, Pesel) VALUES
('Anna', 'Braun', '70010112365'),
('Celia', 'Donut', '74011112365'),
('Ellie', 'Fine', '85101512365'),
('Gloria', 'Hint', '79051412369'),
('Ian', 'Johnson', '82021685214'),
('Kelly', 'Long', '64081725874'),
('Merry', 'Nice', '89120425896');
3. UPDATE PeselTab
SET Data = CAST(SUBSTRING(Pesel, 1, 6) as DATE);
[collapse]
Zadanie 5.
1. Utwórz tabelę ProductDiscount (ProductID, UnitPrice, Discount). Klucz główny ustaw na ProductID, a pole Discount domyślnie ma wartość 0.
Utwórz również tabelę ArchiwumPD (ProductID, OldDiscount, NewDiscount).
2. Wypełnij tabelę ProductDiscount danymi z tabeli Products (ProductID, UnitPrice).
3. Ustal rabat 9% dla 10 najdroższych produktów, oraz 6% dla kolejnych pięciu. Zarówno nowe jak i stare wartości rabatu wraz z id produktu przenieś do tabeli ArchiwumPD.
4. Dodaj kolumnę NewPrice, która przechowywać będzie cenę produktu po rabacie. Jeśli produkt nie ma przyznanego rabatu, w polu NewPrice widnieć powinno zero.
5. Przywróć wcześniejsze wartości rabatów dla produktów 7-12 (wzgl. ceny).
Baza: Northwind, Tabele: dbo.ProductDiscount, dbo.ArchiwumPD, Kolumny: ProductID, UnitPrice, Discount, NewPrice, OldDiscount, NewDiscount
Rozwiązanie
1. CREATE TABLE dbo.ProductDiscount (
ProductID INT PRIMARY KEY,
UnitPrice INT NOT NULL,
Discount NUMERIC(4,3) DEFAULT 0
);
CREATE TABLE dbo.ArchiwumPD (
ProductID INT PRIMARY KEY,
OldDiscount NUMERIC(4,3),
NewDiscount NUMERIC(4,3)
);
2. INSERT INTO dbo.ProductDiscount (ProductID, UnitPrice)
SELECT ProductID, UnitPrice
FROM dbo.Products;
3. Dla pierwszych 10:
WITH CTE AS (
SELECT ProductID, UnitPrice, Discount
FROM dbo.ProductDiscount
ORDER BY UnitPrice DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY
)
UPDATE CTE
SET Discount = 0.09
OUTPUT
deleted.ProductID,
deleted.Discount,
inserted.Discount
INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount);
I kolejne 5:
WITH CTE AS (
SELECT ProductID, UnitPrice, Discount
FROM dbo.ProductDiscount
ORDER BY UnitPrice DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
)
UPDATE CTE
SET Discount = 0.06
OUTPUT
deleted.ProductID,
deleted.Discount,
inserted.Discount
INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount);
Lub z wykorzystaniem tabeli pochodnej (analogicznie będzie przy 10 najdroższych produktach):
UPDATE dbo.ProductDiscount
SET Discount = 0.06
OUTPUT
deleted.ProductID,
deleted.Discount,
inserted.Discount
INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount)
WHERE ProductID IN (SELECT ProductID
FROM dbo.ProductDiscount
ORDER BY UnitPrice DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY);
4. ALTER TABLE dbo.ProductDiscount
ADD NewPrice NUMERIC(5,2);
UPDATE dbo.ProductDiscount
SET NewPrice = UnitPrice*(1 - Discount);
UPDATE dbo.ProductDiscount
SET NewPrice = 0
WHERE Discount = 0;
5. WITH CTE AS (
SELECT PD.Discount as Disc, APD.OldDiscount as OldDisc
FROM dbo.ProductDiscount as PD LEFT JOIN dbo.ArchiwumPD as APD
on PD.ProductID = APD.ProductID
ORDER BY UnitPrice DESC
OFFSET 6 ROWS FETCH NEXT 6 ROWS ONLY
)
UPDATE CTE
SET Disc = OldDisc;
I aktualizujemy ceny w kolumnie NewPrice:
UPDATE dbo.ProductDiscount
SET NewPrice = UnitPrice*(1 - Discount);
UPDATE dbo.ProductDiscount
SET NewPrice = 0
WHERE Discount = 0;
[collapse]