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]

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 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

z6

 

 

 

 

 

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

z5

 

 

 

 

 

 

 

 

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]