• Najnowsze pytania
  • Bez odpowiedzi
  • Zadaj pytanie
  • Kategorie
  • Tagi
  • Zdobyte punkty
  • Ekipa ninja
  • IRC
  • FAQ
  • Regulamin
  • Książki warte uwagi

SQL Zadanie (Sprawdzenie + pomoc)

VPS Starter Arubacloud
0 głosów
1,040 wizyt
pytanie zadane 27 marca 2021 w SQL, bazy danych przez NoelQ Nowicjusz (190 p.)

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
 

Zaloguj lub zarejestruj się, aby odpowiedzieć na to pytanie.

Podobne pytania

0 głosów
1 odpowiedź 2,123 wizyt
pytanie zadane 23 marca 2020 w SQL, bazy danych przez Remi15 Nowicjusz (130 p.)
0 głosów
0 odpowiedzi 424 wizyt
0 głosów
1 odpowiedź 692 wizyt
pytanie zadane 19 czerwca 2021 w SQL, bazy danych przez Arek04 Użytkownik (700 p.)

92,455 zapytań

141,263 odpowiedzi

319,099 komentarzy

61,854 pasjonatów

Motyw:

Akcja Pajacyk

Pajacyk od wielu lat dożywia dzieci. Pomóż klikając w zielony brzuszek na stronie. Dziękujemy! ♡

Oto polecana książka warta uwagi.
Pełną listę książek znajdziesz tutaj.

Akademia Sekuraka

Akademia Sekuraka 2024 zapewnia dostęp do minimum 15 szkoleń online z bezpieczeństwa IT oraz dostęp także do materiałów z edycji Sekurak Academy z roku 2023!

Przy zakupie możecie skorzystać z kodu: pasja-akademia - użyjcie go w koszyku, a uzyskacie rabat -30% na bilety w wersji "Standard"! Więcej informacji na temat akademii 2024 znajdziecie tutaj. Dziękujemy ekipie Sekuraka za taką fajną zniżkę dla wszystkich Pasjonatów!

Akademia Sekuraka

Niedawno wystartował dodruk tej świetnej, rozchwytywanej książki (około 940 stron). Mamy dla Was kod: pasja (wpiszcie go w koszyku), dzięki któremu otrzymujemy 10% zniżki - dziękujemy zaprzyjaźnionej ekipie Sekuraka za taki bonus dla Pasjonatów! Książka to pierwszy tom z serii o ITsec, który łagodnie wprowadzi w świat bezpieczeństwa IT każdą osobę - warto, polecamy!

...