Kategoria: Poziom zaawansowany

Zadanie 4.

Korzystamy z tabel stworzonych w Zadaniu 1 (i zmodyfikowanych w Zadaniach 2 oraz 3):

Szef przysłał nowy wykaz premii. Jest on w pliku tekstowym, wygląda tak:

Wykaz premii

1;1500
2;110
3;800
4;500
5;450
6;450
7;300
8;375
9;300
10;500
11;400
12;400
13;600

[collapse]

Gdzie 1..13 to id pracownika, a liczba po średniku to nowa wartość premii.
Naszym zadaniem jest zaktualizowanie danych w bazie, jednak nie chcemy pisać ręcznie kilkunastu poleceń update.
Jak można to zautomatyzować?

Rozwiązanie

Dane z pliku kopiujemy i wklejamy do pustego arkusza Excela. Następnie rozdzielamy je, tak by w jednej kolumnie było id pracownika, a w drugiej odpowiadająca mu wartość premii.
Następnie w kolejnej kolumnie wpisujemy formułkę postaci: =”update pracownicy set premia=”&B1&” where id_prac=”&A1&”;” (zakładając, że wartość premii mamy w kolumnie B, a id pracowników w A). Teraz wystarczy przeciągnąć formułkę na pozostałe pola (lub kliknąć dwa razy na mały czarny kwadrat w prawym dolnym rogu zaznaczonej komórki) i reszta poleceń wygeneruje się automatycznie.
z4

 

 

 

 

Jedyne co nam pozostaje to przekopiować otrzymane zapytania do SQL Developera i uruchomić je. Pamiętajmy o zatwierdzeniu transakcji (po ostatnim zapytaniu dopisać commit lub kliknąć w odpowiednią ikonkę powyżej okna edytora; domyślnie F11).

W ten sposób możemy wygenerować właściwie dowolną liczbę kwerend.

[collapse]

Zadanie 3.

Korzystamy z tabel stworzonych w Zadaniu 1 (i zmodyfikowanych w Zadaniu 2):

1. Święta idą – wszystkie płace podstawowe rosną o 10%. Uaktualnij dane.

2. Dokupiono nowy samochód (id 6, biała Toyota Yaris, DBC 1587). Wprowadź go do bazy i przypisz temu, kto zarabia najwięcej z osób nieposiadających jeszcze auta firmowego (podstawa plus premia).

3. Stwórz widok, w którym zostanie wyświetlone podsumowanie firmy: imię i nazwisko pracownika, dział oraz stanowisko, pensja (podstawa plus premia) oraz czy posiada firmowy samochód (tak/nie) – screen:

z3

 

 

 

 

Rozwiązanie

1. UPDATE dzialy
SET placa_podstawowa = placa_podstawowa * 1.1;

 

2. Wprowadzenie auta do bazy:

INSERT INTO Flota (id_auta, marka, model_auta, kolor, rejestracja, id_prac)
VALUES (6, 'Toyota', 'Yaris', 'bialy', 'DBC 1587', null);

 

Sprawdzenie, kto zarabia najwięcej:

SELECT pr.id_prac, pr.imie, pr.nazwisko, (dz.placa_podstawowa + pr.premia) AS zarobki
FROM pracownicy pr
INNER JOIN dzialy dz USING(id_dzialu)
WHERE pr.id_auta IS NULL
ORDER BY zarobki DESC
FETCH FIRST row only;

 

Przypisanie samochodu:

UPDATE Pracownicy
SET id_auta = 6 WHERE id_prac = 3;
UPDATE Flota
SET id_prac = 3 WHERE id_auta = 6;

W powyższym zapytaniu zamiast konkretnych wartości można podstawić podzapytania.

Zamiast id_prac (3) w pierwszym UPDATE można wstawić zmodyfikowaną wersję zapytania sprawdzającego kto najwięcej zarabia:

SELECT pr.id_prac
FROM pracownicy pr
INNER JOIN dzialy dz USING(id_dzialu)
WHERE pr.id_auta IS NULL
ORDER BY (dz.placa_podstawowa + pr.premia) DESC
FETCH FIRST row only;

A zamiast id_auta (6) w drugim można podstawić:

SELECT id_auta
FROM Flota
WHERE id_prac IS NULL;

 

3. CREATE VIEW Podsumowanie AS
SELECT pr.imie ||' '||pr.nazwisko as pracownik, dz.nazwa_dzialu, dz.stanowisko, (dz.placa_podstawowa + pr.premia) as zarobki,
CASE
WHEN pr.id_auta IS NULL THEN 'Nie'
ELSE 'Tak'
END AS Auto_firmowe
FROM Pracownicy pr inner join Dzialy dz
using (id_dzialu);

[collapse]

Zadanie 2.

Korzystając z tabel stworzonych w Zadaniu 1:

1. Stwórzmy nowy dział – IT. Dwa stanowiska – Administrator (ID działu – 500) oraz Programista (ID działu – 550). Płaca podstawowa dla obu 3500.

2. Nowy dział trzeba „zaludnić”. Administratorem będzie Andrzej Szyszka (id 12, zatrudniony 5 listopada 2015, urodzony 10 września 1973, nadal pracuje, telefon 667667667, premia 500). Programistą zaś został Adrian Klon (id 13, zatrudniony 2 listopada 2015, urodzony 14 marca 1984, nadal pracuje, telefon 665665665, premia 700).

3. Administratorowi przypisz wolny samochód, jeśli taki jest.

4. Nowa polityka firmy mówi, że nikt nie może dostawać premii niższej niż 350. Uaktualnij dane tak, by były zgodne z tą polityką.

5. Tabela Flota. Każdemu samochodowi przypisz odpowiadający mu numer pracownika (id_prac).

 

Rozwiązanie


1. INSERT ALL
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES (500, 'IT', 'Administrator', 3500)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(550, 'IT', 'Programista', 3500)
SELECT * FROM dual;

 

2.  INSERT ALL
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (12, 'Andrzej', 'Szyszka', 500, null, 500, '2015-11-05', '1973-09-10', null, '667667667')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (13, 'Adrian', 'Klon', 550, null, 700, '2015-11-02', '1984-03-14', null, '665665665')
SELECT * FROM dual;

 

3. Najpierw sprawdzamy, czy mamy na stanie wolne auto:

SELECT *
FROM Flota
WHERE id_auta not in (SELECT id_auta FROM Pracownicy WHERE id_auta is not null);

Mamy jedno wolne auto, więc możemy je przypisać Adminowi:

UPDATE Pracownicy
SET id_auta = '9'
WHERE id_dzialu = '500';

 

4. UPDATE Pracownicy
SET premia = 350
WHERE premia < 350;

 

5. UPDATE Flota
SET id_prac = 1 WHERE id_auta = 12;
UPDATE Flota
SET id_prac = 2 WHERE id_auta = 11;
UPDATE Flota
SET id_prac = 5 WHERE id_auta = 10;
UPDATE Flota
SET id_prac = 12 WHERE id_auta = 9;
UPDATE Flota
SET id_prac = 7 WHERE id_auta = 8;
UPDATE Flota
SET id_prac = 6 WHERE id_auta = 7;

[collapse]

Zadanie 1.

Na potrzeby kolejnych zadań stwórzmy sobie mini-bazę, składającą się z trzech tabel: Pracownicy (dane pracowników), Działy (informacje o działach firmy) i Flota (samochody w firmie).

Dane do stworzenia tabel – poniżej.

Ja stworzyłam je w schemacie HR.

Screen przedstawia dane wynikowe po połączeniu wszystkich trzech tabel:

z1

 

 

Tworzenie tabel


-- najpierw tworzymy tabelę Dzialy:

CREATE TABLE Dzialy (
id_dzialu INTEGER CONSTRAINT id_dzialu_pk PRIMARY KEY,
nazwa_dzialu VARCHAR2(20) NOT NULL,
stanowisko VARCHAR2(30) NOT NULL,
placa_podstawowa INTEGER NOT NULL
);

-- potem tworzymy tabelę Flota:

CREATE TABLE Flota (
id_auta INTEGER CONSTRAINT id_auta_pk PRIMARY KEY,
marka VARCHAR2(20) NOT NULL,
model_auta VARCHAR2(20) NOT NULL,
kolor VARCHAR2(20),
rejestracja VARCHAR2(10)
);

-- teraz czas na Pracowników:

CREATE TABLE Pracownicy (

id_prac INTEGER CONSTRAINT id_prac_pk PRIMARY KEY,
imie VARCHAR2(20) NOT NULL,
nazwisko VARCHAR2(20) NOT NULL,
data_zatrudnienia DATE NOT NULL,
data_urodzenia DATE NOT NULL,
data_zakonczenia DATE,
telefon VARCHAR2(9),
premia INTEGER DEFAULT '0',
id_dzialu INTEGER,
id_auta INTEGER,
CONSTRAINT id_dzialu_fk FOREIGN KEY (id_dzialu) REFERENCES Dzialy(id_dzialu),
CONSTRAINT id_auta_fk FOREIGN KEY (id_auta) REFERENCES Flota(id_auta)
);

-- modyfikujemy Flotę dodając powiązania z tabelą Pracowników:

ALTER TABLE Flota
ADD id_prac INTEGER;

ALTER TABLE Flota
ADD CONSTRAINT id_prac_fk FOREIGN KEY (id_prac) REFERENCES Pracownicy(id_prac);

--...i na koniec wypelniamy wszystkie trzy tabelki danymi:

INSERT ALL
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES (100, 'Zarząd', 'Prezes', 7000)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(150, 'Zarząd', 'Zastępca Prezesa', 5000)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(200, 'Kadry', 'Glówny Specjalista', 3500)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(250, 'Kadry', 'Mlodszy Specjalista', 2600)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(300, 'Sprzedaż', 'Manager Sprzedaży', 3000)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(350, 'Sprzedaż', 'Handlowiec', 2650)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(400, 'Obsluga', 'Ochrona', 2500)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(450, 'Obsluga', 'Sprzątanie', 2300)
SELECT * FROM dual;

INSERT ALL
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (7, 'Toyota', 'Yaris', 'biay', 'ABC 1258')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (8, 'Toyota', 'Yaris', 'biay', 'ABC 2423')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (9, 'Toyota', 'Yaris', 'czarny', 'ABB 3537')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (10, 'Toyota', 'Auris', 'srebrny', 'ABA 2587')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (11, 'Suzuki', 'SX4', 'srebrny', 'CBC 3285')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (12, 'Suzuki', 'SX4', 'czerwony', 'ABA 8327')
SELECT * FROM dual;

INSERT ALL
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (1, 'Anna', 'Kowalska', 100, 12, 1600, '2005-10-19', '1975-12-03', null, '776776776')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (2, 'Jaromir', 'Cieśla', 150, 11, 1300, '2005-10-19', '1978-10-05', null, '775775775')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (3, 'Janina', 'Nowak', 200, null, 950, '2005-10-19', '1966-02-15', null, '774774774')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (4, 'Amelia', 'Beryl', 250, null, 600, '2007-06-23', '1987-07-13', null, '773773773')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (5, 'Jacek', 'Onyks', 300, 10, 500, '2005-10-30', '1982-08-09', null, '772772772')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (6, 'Ernest', 'Opal', 350, 7, 500, '2009-05-01', '1976-07-05', null, '771771771')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (7, 'Anna', 'Nowakowska', 350, 8, 400, '2010-06-05', '1985-11-04', null, '770770770')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (8, 'Jan', 'Rębacz', 350, null, 0, '2015-10-01', '1973-05-12', null, '779779779')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (9, 'Roman', 'Kowal', 400, null, 300, '2008-08-01', '1953-07-09', null, '778778778')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (10, 'Stefan', 'Rolnik', 400, null, 350, '2009-09-01', '1966-08-17', null, '778778778')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (11, 'Renata', 'Gąbka', 450, null, 400, '2011-06-15', '1968-09-09', null, '765765765')
SELECT * FROM dual;

[collapse]