Cześć mam kilka zadań z SQL i chciałbym się dowiedzieć czy dobrze zrobiłem i o jakąś podpowiedź do dwóch przykładów:
DANE DO OBU LIST:
DROP TABLE PRACOWNICY;
DROP TABLE ZESPOLY;
DROP TABLE ETATY;
CREATE TABLE ZESPOLY
(ID_ZESP NUMERIC(2) CONSTRAINT PK_ZESP PRIMARY KEY,
NAZWA CHARACTER VARYING(20),
ADRES CHARACTER VARYING(20) );
CREATE TABLE ETATY
( NAZWA CHARACTER VARYING(15) CONSTRAINT PK_ETAT PRIMARY KEY,
PLACA_OD NUMERIC(6,2),
PLACA_DO NUMERIC(6,2));
CREATE TABLE PRACOWNICY
(ID_PRAC NUMERIC(4) CONSTRAINT PK_PRAC PRIMARY KEY,
NAZWISKO CHARACTER VARYING(15),
IMIE CHARACTER VARYING(15),
ETAT CHARACTER VARYING(15) CONSTRAINT FK_ETAT REFERENCES ETATY(NAZWA),
ID_SZEFA NUMERIC(4) CONSTRAINT FK_ID_SZEFA REFERENCES PRACOWNICY(ID_PRAC),
ZATRUDNIONY DATE,
PLACA_POD NUMERIC(6,2) CONSTRAINT MIN_PLACA_POD CHECK(PLACA_POD>100),
PLACA_DOD NUMERIC(6,2),
ID_ZESP NUMERIC(2) CONSTRAINT FK_ID_ZESP REFERENCES ZESPOLY(ID_ZESP));
INSERT INTO ZESPOLY VALUES (10,'ADMINISTRACJA', 'PIOTROWO 2');
INSERT INTO ZESPOLY VALUES (20,'SYSTEMY ROZPROSZONE','PIOTROWO 3A');
INSERT INTO ZESPOLY VALUES (30,'SYSTEMY EKSPERCKIE', 'STRZELECKA 14');
INSERT INTO ZESPOLY VALUES (40,'ALGORYTMY', 'WIENIAWSKIEGO 16');
INSERT INTO ZESPOLY VALUES (50,'BADANIA OPERACYJNE', 'MIELZYNSKIEGO 30');
INSERT INTO ETATY VALUES ('PROFESOR' ,3000.00, 4000.00);
INSERT INTO ETATY VALUES ('ADIUNKT' ,2510.00, 3000.00);
INSERT INTO ETATY VALUES ('ASYSTENT' ,1500.00, 2100.00);
INSERT INTO ETATY VALUES ('DOKTORANT' ,800.00, 1000.00);
INSERT INTO ETATY VALUES ('SEKRETARKA',1470.00, 1650.00);
INSERT INTO ETATY VALUES ('DYREKTOR' ,4280.00,5100.00);
INSERT INTO PRACOWNICY VALUES (100,'Marecki','Jan' ,'DYREKTOR' ,NULL,to_date('01-01-1968','DD-MM-YYYY'),4730.00,980.50,10);
INSERT INTO PRACOWNICY VALUES (110,'Janicki','Karol' ,'PROFESOR' ,100 ,to_date('01-05-1973','DD-MM-YYYY'),3350.00,610.00,40);
INSERT INTO PRACOWNICY VALUES (120,'Nowicki','Pawel' ,'PROFESOR' ,100 ,to_date('01-09-1977','DD-MM-YYYY'),3070.00, NULL,30);
INSERT INTO PRACOWNICY VALUES (130,'Nowak','Piotr' ,'PROFESOR' ,100 ,to_date('01-07-1968','DD-MM-YYYY'), 3960.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (140,'Kowalski','Krzysztof','PROFESOR' ,130 ,to_date('15-09-1975','DD-MM-YYYY'), 3230.00,805.00,20);
INSERT INTO PRACOWNICY VALUES (150,'Grzybowska','Maria','ADIUNKT' ,130 ,to_date('01-09-1977','DD-MM-YYYY'), 2845.50, NULL,20);
INSERT INTO PRACOWNICY VALUES (160,'Krakowska','Joanna', 'SEKRETARKA' ,130 ,to_date('01-03-1985','DD-MM-YYYY'), 1590.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (170,'Opolski','Roman' ,'ASYSTENT' ,130 ,to_date('01-10-1992','DD-MM-YYYY'), 1839.70, 480.50,20);
INSERT INTO PRACOWNICY VALUES (190,'Kotarski','Konrad', 'ASYSTENT' ,140 ,to_date('01-09-1993','DD-MM-YYYY'), 1971.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (180,'Makowski', 'Marek', 'ADIUNKT',100 ,to_date('20-02-1985','DD-MM-YYYY'), 2610.20, NULL,10);
INSERT INTO PRACOWNICY VALUES (200,'Przywarek','Leon' ,'DOKTORANT' ,140 ,to_date('15-07-1994','DD-MM-YYYY'), 900.00, NULL,30);
INSERT INTO PRACOWNICY VALUES (210,'Kotlarczyk','Stefan','DOKTORANT' ,130 ,to_date('15-10-1993','DD-MM-YYYY'), 900.00,570.60,30);
INSERT INTO PRACOWNICY VALUES (220,'Siekierski', 'Mateusz','ASYSTENT' ,110 ,to_date('01-10-1993','DD-MM-YYYY'), 1889.00, NULL,20);
INSERT INTO PRACOWNICY VALUES (230,'Dolny', 'Tomasz' ,'ASYSTENT' ,120 ,to_date('01-09-1992','DD-MM-YYYY'), 1850.00, 390.00,NULL);
COMMIT;
Lista 1:
Zadanie 1. Zwróć nazwisko oraz roczny dochód opisany w wyniku jako "Dochód" osób nie podejmujących pracy dodatkowej, które mają nazwisko zaczynające się od litery w drugiej połowie alfabetu, oraz wszystkich pracowników których dochód nie zawiera się w przedziale od 20.000 do 40.000. Wynik ma być posortowany po dochodzie, a w przypadku takich samych dochdów po nazwisku od końca alfabetu.
Zadanie 2. Zwrócić nazwy zespołów o długości większej niż 9 znaków. Należy to zrobić w taki sposób, aby nazwy były pisane pierwszą dużą literą oraz skrócone dla wszystkich nazw o więcej niż 9 znakach do pierwszych dziewięciu znaków zakończonych kropką.
Zadanie 3. Wyświetl nazwisko, datę zatrudnienia oraz staż pracy pracowników zatrudnionych w latach 90-tych w roku przestępnym w kolejności rosnącego stażu pracy. Staż ma być w miesiącach (bez części ułamkowej).
Zadanie 4. Wyświetl to, co w poprzednim zadaniu, a dla pozostałych pracowników wyświetl jako staż napis "Długi", jeśli staż przekracza 317 miesięcy, lub krótki, jeżeli jest mniejszy niż 316 miesięcy. Wykorzystaj zagnieżdżoną komendę CASE.
Zadanie 5. Wykonaj zadanie 4 łącząc wyniki 3 zapytań SELECT (zachowując kolejność wyświetlania wg stażu)
MOJE ROZWIĄZANIA:
3)
SELECT nazwisko, zatrudniony, TRUNC(MONTHS_BETWEEN(CURRENT_DATE, zatrudniony),'0') AS "Staż pracy"
FROM pracownicy
WHERE EXTRACT(YEAR FROM zatrudniony) BETWEEN 1990 AND 1999
ORDER BY "Staż pracy" DESC;
4)
SELECT nazwisko, zatrudniony,
CASE
WHEN EXTRACT(YEAR FROM zatrudniony) BETWEEN 1990 AND 1999
THEN CAST(TRUNC(MONTHS_BETWEEN(CURRENT_DATE, zatrudniony),'0') AS VARCHAR(5))
WHEN TRUNC(MONTHS_BETWEEN(CURRENT_DATE, zatrudniony),'0') > 317
THEN 'DŁUGI'
ELSE 'KRÓTKI'
END AS "Staż pracy"
FROM pracownicy
ORDER BY "Staż pracy" DESC;
2)
select substr(initcap(nazwa), 1, 9) || '.' from zespoly;
1)
select
nazwisko, (placa_pod+nvl(placa_dod,0))*12 AS "Dochód",
placa_dod from pracownicy
where
placa_dod is null
and
REGEXP_LIKE(UPPER(nazwisko), '^[N-Z]')
or
(placa_pod+nvl(placa_dod,0))*12 not between 20000 and 40000
order by "Dochód", nazwisko desc;
LISTA 2
Zadanie 1. Wyświetl:
• identyfikator zespołu,
• nazwę zespołu,
• maksymalne wynagrodzenie podstawowe,
• średnie wynagrodzenie dodatkowe,
• liczbę osób zatrudnionych na etacie w zespole, dla tych zespołów i etatów, które spełniają warunki:
• na danym etacie pracuje w danym zespole 2-3 osoby,
• średnie wynagrodzenie dodatkowe na tym etacie w tym zespole jest równe co najmniej 10% maksymalnego wynagrodzenia podstawowego w tym zespole na tym etacie, ale nie większe niż 1/3 tego maksymalnego wynagrodzenia.
Wyświetl dane w kolejności alfabetycznej według nazwy etatu. Czy zmiana z 10% na 8% zmieni wynik?
Zadanie 2. Wyświetl nazwisko i płacę podstawową dla pracowników zarabiających mniej niż połowę górnej stawki przewidzianej na etacie dyrektora.
Zadanie 3. Wyświetl wszystkie pary nazwisk pracowników wraz z płacami dla tych pracowników, których zarobki nie różnią się więcej niż 10% (każda para nazwisk ma się pojawić jednokrotnie w wyniku, bez par o tym samym nazwisku).
Zadanie 4. Wypisz osoby mające szefa w innym zespole lub nie mające szefa lub nie będące w zespole lub z szefem nie należącym do żadnego zespołu
MOJE ROZWIĄZANIA:
ZAD 1
SELECT z.ID_ZESP, z.NAZWA, MAX(p.PLACA_POD) as MAKSYMALNE_WYNAGRODZENIE_PODSTAWOWE, AVG(COALESCE(placa_dod,0)) as SREDNIE_WYNAGRODZENIE_DODATKOWE, COUNT(*) as LICZBA_OSOB_NA_ETATCIE_W_ZESPOLE
FROM PRACOWNICY p
INNER JOIN ZESPOLY z
ON p.ID_ZESP = z.ID_ZESP
INNER JOIN ETATY e
ON p.ETAT = e.NAZWA
GROUP BY
z.ID_ZESP,
z.NAZWA,
p.ETAT
HAVING COUNT(*) BETWEEN 2 AND 3
AND AVG(COALESCE(placa_dod,0)) BETWEEN (MAX(p.PLACA_POD) * 0.1) AND (MAX(p.PLACA_POD) / 3)
ORDER BY p.ETAT ASC
ZMIANA Z 10% NA 8% ZMIENI WYNIK
ZAD 2
SELECT NAZWISKO, PLACA_POD
FROM PRACOWNICY
JOIN ZESPOLY ON ZESPOLY.ID_ZESP = PRACOWNICY.ID_ZESP
JOIN ETATY ON ETATY.NAZWA = PRACOWNICY.ETAT
WHERE PLACA_POD + PLACA_DOD < (SELECT 0.5 * PLACA_DO FROM PRACOWNICY
JOIN ETATY ON ETATY.NAZWA = PRACOWNICY.ETAT WHERE ETAT = 'DYREKTOR');
Prosiłbym o pomoc z drugą listą z zadaniem 3 i 4