POLECENIE 1
Utworzyć tabelę Studenci w dialekcie języka SQL Oracle zawierającą nast. Pola:
Nr studenta zawiera l.naturalne, klucz głowny tabeli
Nazwisko i imie – pola tekstowe o MAX rozmiarze 15, nie mogą zawierac wartości pustej
Data ur – pole zawierające daty
Plec – może zawierac tylko 2 wartosci K lub M

A) utworzyć tabelę studenci bez nazywania ograniczeń

CREATE TABLE Studenci(
nr_studenta NUMBER(6) PRIMARY KEY,
nazwisko  VARCHAR2(15) NOT NULL,
imie VARCHAR2(15) NOT NULL,
data_ur DATE,
plec CHAR(1) CHECK(plec='K' OR plec='M'));

B) utworzyc tabele studenci nazywajac wszystkie ograniczenia

CREATE TABLE Studenci(
nr_studenta NUMBER(6) CONSTRAINT nr_studenta_pk PRIMARY KEY,
nazwisko  VARCHAR2(15) CONSTRAINT nazwisko_nn NOT NULL,
imie VARCHAR2(15) CONSTRAINT imie_nn NOT NULL,
data_ur DATE,
plec CHAR(1) CONSTRAINT plec_CH CHECK(plec='K' OR plec='M'));

c) utworzyc tabele
id_oceny zawiera l.n. , klucz glowny
nr studenta zawiera liczby naturalne, klucz obcy łączący tabelę oceny studenta z z tabelą studenci polem nr_studenta
data_oceny pole zawiera daty , nie moze byc NULL,
ocena - zawiera zawiera oceny w postaci punktowej,
slownie - zawiera oceni w postaci slownej


CREATE TABLE oceny_studentow (
id_oceny NUMBER CONSTRAINT id_oceny_PK PRIMARY KEY,
nr_stud NUMBER,
data_oceny DATE CONSTRAINT data_oceny_nn NOT NULL,
ocena NUMBER(2,1), CONSTRAINT ocena_CH CHECK (ocena=2 OR ocena=2,5 OR ocena=3 OR ocena=3,5 OR ocena=4 OR ocena=4,5 OR ocena=5),
slownie VARCHAR2(15) CONSTRAINT slownie_CH CHECK (slownie='Ndst' OR slownie='Ndst+' OR slownie='Dst' OR slownie='Dst+' OR slownie='Db' OR slownie='Db+' OR slownie='Bdb'),
CONSTRAINT nr_stud_FK FOREIGN KEY (nr_stud) REFERENCES Studenci(nr_studenta)
);

RENAME oceny_studentow TO oceny;

Podczas tworzenia tabeli studenci utworzono pole nazwisko o MAX rozmiarze 15 znaków. okazało sie zbyt małe, jego MAX rozmiar powinien wynosic 30 znaków. Skorygować pomyłkę bez usuwania tabeli.


ALTER TABLE Studenci MODIFY(nazwisko VARCHAR2(30));

Podczas tworzenia tabelki studenci pominieto pole telefon, które zawiera numery telefonów, składające się z 6 znaków.Skorygować pomyłkę bez usuwania tabeli.

ALTER TABLE Studenci ADD(telefon CHAR(6));

Okazuje sie jednak ze pole telefon jest zbyteczne i nalezy je usunac. -||-

ALTER TABLE Studenci DROP COLUMN telefon;

Okazuje sie wiekszosc studentow w tablece studenci stanowią mezczyzni. Uwzglednic ten fakt w strukturze\definicji tabelki Studenci.

ALTER TABLE Studenci MODIFY(plec DEFAULT 'M');

1.Zmienić kolejność pól w tabeli Studenci tak, aby pole plec BYło przed polem data_ur. 2. Zmienić kolejność ... aby pole data_ur BYło przed polem plec.

ALTER TABLE Studenci DROP COLUMN data_ur;

ALTER TABLE Studenci ADD(data_ur DATE); -- 1


ALTER TABLE Studenci ADD(plec CHAR(1) DEFAULT 'M'
CONSTRAINT plec_CH CHECK ( plec='M' OR plec='K')); --2


Wprowadzic przykładowe rekordy DO tabeli studenci
1. Tkaczyk Jerzy 12/10/1999 M
2. Krawczyk Monika 19/05/1998 K
3. Maczyk Maria 09/07/1997 K
4. Balcerek Janusz 02/09/1999 M  .....



INSERT INTO Studenci(nr_studenta,nazwisko,imie,data_ur,plec) VALUES(1,'Tkaczyk','Jerzy',TO_DATE('12/10/1999','DD/MM/YYYY'),'M');

INSERT INTO Studenci(nr_studenta,nazwisko,imie,data_ur,plec) VALUES(2,'Krawczyk','Monika',TO_DATE('19/05/1998','DD/MM/YYYY'),'K');

INSERT INTO Studenci(nr_studenta,nazwisko,imie,data_ur,plec) VALUES(3,'Maczyk','Maria',TO_DATE('09/07/1997','DD/MM/YYYY'),'K');

INSERT INTO Studenci(nr_studenta,nazwisko,imie,data_ur,plec) VALUES(4,'Balcerek','Janusz',TO_DATE('02/09/1999','DD/MM/YYYY'),'M');

INSERT INTO oceny(id_oceny,nr_stud,data_oceny,ocena,slownie) VALUES(1,1,TO_DATE('15/10/2017','DD/MM/YYYY'),5,'Bdb');

INSERT INTO oceny(id_oceny,nr_stud,data_oceny,ocena,slownie) VALUES(2,1,TO_DATE('17/10/2017','DD/MM/YYYY'),3,'Dst');

INSERT INTO oceny(id_oceny,nr_stud,data_oceny,ocena,slownie) VALUES(3,2,TO_DATE('15/10/2017','DD/MM/YYYY'),4.5,'Db+');

INSERT INTO oceny(id_oceny,nr_stud,data_oceny,ocena,slownie) VALUES(4,3,TO_DATE('19/10/2017','DD/MM/YYYY'),3.5,'Dst+');

INSERT INTO oceny(id_oceny,nr_stud,data_oceny,ocena,slownie) VALUES(5,4,TO_DATE('20/10/2017','DD/MM/YYYY'),2,'Ndst');

Okazało się, że niepoprawnie wprowadzono nazwisko i imie studentki Maczyk Marii, poprawne TO Marczyk Marta. Skorygować błąd bez usuwania rekordu.

UPDATE Studenci
SET imie='Marta',
nazwisko='Marczyk'
WHERE nr_studenta=3;

Utworzyc dwie sekwencje DO wprowadzania wartości w polach kluczy głównych tabel studenci i oceny:
sekw_student : krok1, MIN.1 MAX 999
sekw_ocena : krok1, MIN .1 MAX 9999

Wproawdzenie rekordów ze swoimi danymi.

CREATE SEQUENCE sekw_student INCREMENT BY 1 START WITH 5 MINVALUE 1 MAXVALUE 999;

CREATE SEQUENCE sekw_ocena INCREMENT BY 1 START WITH 6 MINVALUE 1 MAXVALUE 9999;


INSERT INTO Studenci
VALUES(sekw_student.NEXTVAL,'Pawelec','Aleksandra',TO_DATE('28/08/1998','DD/MM/YYYY'),'K' );

INSERT INTO oceny
VALUES(sekw_student.NEXTVAL,5,TO_DATE('28/08/1998','DD/MM/YYYY'),3,'dst');


Utyworzyc indeks student w tabeli studenci na polach nazwisko i imie posortowanych alfabetycznie.

CREATE  INDEX student ON Studenci (nazwisko ASC, imie ASC );

------------------------------------

1.Wyłączyć ograniczenie dla pola plec w tabeli studenci  - sprawdzić jego wyłączenie.
2. Wprowadzić rekord DO tabeli Studenci
Kowalski Jan 12/07/1998 A
3. Spróbować właczyc ograniczenie dla pola plec . Zmodyfikować (bez usuwania) powyższy rekord tak, aby ograniczenie
udało się włączyć.


1. ALTER TABLE Studenci DISABLE CONSTRAINT PLEC_CH;
2. INSERT INTO Studenci(nr_studenta,nazwisko,imie,data_ur,plec) VALUES(5,'Kowalski','Jan',TO_DATE('12/07/1998','DD/MM/YYYY'),'A');
3.UPDATE
Studenci
SET
plec = 'M'
WHERE nr_studenta= 5;

ALTER TABLE Studenci ENABLE CONSTRAINT PLEC_CH;

4. Utworzyć tabelę Studentki, która zawiera wszystkie rekordy z tabeli Studenci opisujące studentki.
( 2 sposoby)

1 sposob -
CREATE TABLE Studentki1 AS
SELECT * FROM Studenci WHERE plec = 'K';

2 sposob -
CREATE TABLE Studentki2  (
nr_studentki NUMBER(6) CONSTRAINT nr_studentki_pk PRIMARY KEY,
nazwisko  VARCHAR2(15) CONSTRAINT nazwisko_nn_k NOT NULL,
imie VARCHAR2(15) CONSTRAINT imie_nn_k NOT NULL,
data_ur DATE,
plec CHAR(1) CONSTRAINT plec_CH_k CHECK(plec='K'));

INSERT INTO Studentki2
SELECT * FROM Studenci WHERE plec='K';


UZYTKOWNIK KADRY:

1. Wyswietlić listę alfabetyczną osób z tabeli Osoby ( 3 sposoby)

ID_OS NAZWISKO_OS IMIE_OS
15       Duda       Barbara
20       Nowak      Adam
5        Nowak      Maria

1 -
SELECT id_os, INITCAP(nazwisko) nazwisko, INITCAP(imie1) imie1
FROM osoby
ORDER BY INITCAP(nazwisko) ASC, INITCAP(imie1) ASC;

2-
ORDER BY nazwisko ASC, imie1 ASC;

3-
ORDER BY 2 ASC, 3 ASC;

2. - || -
ID_OS OSOBA
15    Duda Barbara
20    Nowak Adam
5     Nowak Maria

SELECT id_os, INITCAP(nazwisko)||' '||INITCAP(imie1) PRAC
FROM osoby
ORDER BY 2 ASC;

3. Napisac skrypt zwracający nastepujący wydruk

WYDZIAŁ        LITERA
-------     ------
FIZYKA      F
MATEMATYKA  M
PRAWO       P


SELECT UPPER(nazwa) wydzial, UPPER(SUBSTR(nazwa,1,1)) litera
FROM wydzialy
ORDER BY 1 ASC;


4. Wyświetlić dla poszczególnych osób ich aktulaną pensję i pensję zaokrągloną DO pełnych tysięcy

PRACOWNIK       PENSJA      PELNE_TYS
5           4500        4
8           4500        4
10          4000        4

SELECT id_os PRACOWNIK, PENSJA, TRUNC(PENSJA/1000) PELNE_TYS
FROM zatrudnienia
WHERE DO IS NULL
ORDER BY pensja DESC, id_os ASC;

5. Wyświetlić listę id tych osób, które gdziekolwiek bądź kiedykolwiek BYły zatrudnione lub są nadal zatrudnione.
SELECT DISTINCT id_os pracownik
FROM zatrudnienia
ORDER BY 1 ASC;

6. Wyświetlić dla każdej osoby z tabeli osoby następujący komunikat:
Pan Czech Adam-data urodzenia 71/04/11.
Pani Drops Barbara-data urodzenia 63/05/22.
Pani Duda Barbara-data urodzenia 78/10/21.
Pan Dudek Jan-data urodzenia 48/10/22.


SELECT DECODE(masa,’kg’, ‘kilogram’,’dag’,’dekagram’,’g’,’gram’,’??)  jednostki_masy FROM dual;

SELECT DECODE(plec='M','Pan','K','Pani')
nazwisko||' '||imie1,||'-data urodzenia'||d_w||'.'
KOMUNIKAT FROM osoby
ORDER BY 1 ASC;


--------------------------------------------------------------
1.Obliczyć dla poszczególnych osób liczbe pełnych lat, jakie każda z nich przepracowała w aktualnym miejescu zatrudnienia.

ID_OS       Lata pracy
2       15
7       12
9       12
15      12
3       9
21      9


SELECT id_os, TRUNC(MONTHS_BETWEEN(SYSDATE,od)/12) LATA_PRACY
FROM zatrudnienia
WHERE DO IS NULL
ORDER BY 2 DESC, 1 ASC;



2.Wyświetlić alfabetyczną listę osób, których pierwsze imię zawiera choć jedną literę 'a'.

SELECT id_os, INITCAP(imie1)||' '||INITCAP(nazwisko) pracownik
FROM osoby
WHERE LOWER(imie1) LIKE '%a%'
ORDER BY 2 ASC;

3. Wyświetlić dane takich osób, których nazwisko zaczyna się na literę K lub L i urodziły się w czerwcu,
listopadzie i grudniu lub te kobiety, które mają drugie imię.

SELECT id_os, INITCAP(nazwisko)||' '||INITCAP(imie1)osoba, d_ur, plec
FROM osoby
WHERE ((SUBSTR(INITCAP(nazwisko),1,1)='K' OR SUBSTR(INITCAP(nazwisko),1,1)='L')
AND (TO_CHAR(d_ur,'MM')='06' OR TO_CHAR(d_ur,'MM')='11' OR TO_CHAR(d_ur,'MM')='12'))
OR (plec='K' AND imie2 IS NOT NULL);

-||-
WHERE(SUBSTR(INITCAP(nazwisko),1,1) IN ('K','L') AND TO_CHAR(d_ur,'MM') IN ('06','11','12'))
OR (plec='K' AND imie2 IS NOT NULL);

4.Wyświetlić liczbę osób zarejestrowanych w bazie.

SELECT COUNT(id_os) liczba_osob
FROM osoby;


5. Wyświetlić liczbę osób z każdej płci.

SELECT COUNT(id_os) liczba_osob
FROM osoby
GROUP BY plec;

SELECT plec, COUNT(id_os) liczba_osob
FROM osoby
GROUP BY plec
ORDER BY 2 DESC;


6.Dla poszczególnych osób ich aktualne pensje( 2 sposoby )
ID_OS   OSOBA       PENSJA
2   Duda Barbara    4500
10  Nowak Adam  4000
21  Nowak Michał   4000
15  Pielecki Marek  3800

SELECT o.id_os, INITCAP(z.imie1)||' '||INITCAP(o.nazwisko) osoba, z.pensja
FROM osoby o,zatrudnienia z
WHERE o.id_os=z.id_os AND z.DO IS NULL
ORDER BY z.pensja DESC, o.imie ASC;

FROM osoby o JOIN zatrudnienia ON o.id_os=z.id_os
WHERE z.DO IS NULL;


7.Wyswietlić  te osoby, które DO tej pory nie BYły zatrudnione.

SELECT o.id_os, INITCAP(o.imie1)||' '||INITCAP(o.nazwisko) osoba
FROM osoby o LEFT JOIN zatrudnienia z ON o.id_os=z.id_os
WHERE z.DO IS NULL;


8.Wyświetlić te wydziały, które zatrudniają aktualnie przynajmniej 2 osoby.

SELECT INITCAP(w.nazwa) wydzial, COUNT(z.id_os) liczba_osob
FROM wydzialy w JOIN zatrudnienia z ON w.id_w=z.id_w
WHERE z.DO IS NULL
GROUP BY INITCAP(w.nazwa)
HAVING COUNT (z.id_os)>1
ORDER BY 2 DESC, 1 ASC;

-----------------------------------------------------
1. Wyswietlić dane ( identyfikator, nazwisko, imie ,datę urodzenia) najstarszej osoby z tabeli osoby.

SELECT id_os, nazwisko, imie1, d_ur
FROM osoby
WHERE d_ur=(SELECT MIN(d_ur) FROM osoby) ;

2. Wyswietlić dane najstarszego mężczyny:

SELECT id_os, nazwisko, imie1, d_ur
FROM osoby
WHERE d_ur=(SELECT MIN(d_ur) FROM osoby
WHERE plec='M');

3. Wyswietlić dane najstarszego mężczyny i najstarszej kobiety.

SELECT o.plec, o.id_os, o.nazwisko, o.imie1, o.d_ur
FROM osoby o
WHERE o.d_ur=(SELECT MIN (o1.d_ur)
FROM osoby o1
WHERE o1.plec=o.plec);

4. Wyswietlić tę płeć, z której więcej osób jest wpisanych DO tabeli osoby.(2 sposoby)

PLEC        LICZBA_OSOB
---     -----------
K       18

Widok słownikowy:
CREATE VIEW max_plec_os AS
SELECT plec, COUNT(id_os) liczba_os
FROM osoby
GROUP BY plec
ORDER BY 2 DESC;

SELECT plec, liczba_os
FROM max_plec_os
WHERE liczba_os=(SELECT MAX(liczba_os)
FROM max_plec_os);

Widok tymczasowy:
WITH plec_os_max AS
(SELECT plec, COUNT(id_os) liczba_osob
FROM osoby
GROUP BY plec)
SELECT plec, liczba_osob
FROM plec_os_max
WHERE liczba_osob=(SELECT MAX(liczba_osob)
FROM plec_os_max);

5. (2 sposoby) Wyswietlić dla poszczególnych wydziałów tę płeć, z której aktualnie jest zatrudnionych więcej osób na każdym z nich.

WYDZIAL     PLEC        LICZBA_OSOB
-------     ----        -----------
Matematyka  K       5
Fizyka      M       4
Prawo       K       4


1 sposób:
CREATE VIEW wydzial_max_plec AS
SELECT INITCAP(w.nazwa) wydzial, o.plec, COUNT(o.id_os) liczba_osob
FROM osoby o JOIN zatrudnienia z ON o.id_os=z.id_os
JOIN wydzialy w ON w.id_w=z.id_w
WHERE z.DO IS NULL
GROUP BY INITCAP(w.nazwa), o.plec
ORDER BY 3 DESC;

SELECT wmp.wydzial, wmp.plec, wmp.liczba_osob
FROM wydzial_max_plec wmp
WHERE wmp.liczba_osob=(SELECT MAX(wmp1.liczba_osob)
FROM wydzial_max_plec wmp1
WHERE wmp1.wydzial=wmp.wydzial)
ORDER BY 3 DESC, 1 ASC;

2 sposób:
WITH max_plec_wydzial AS
(SELECT INITCAP(w.nazwa) wydzial, o.plec, COUNT(o.id_os) liczba_osob
FROM osoby o, zatrudnienia z , wydzialy w
WHERE o.id_os=z.id_os AND w.id_w=z.id_w AND z.DO IS NULL
GROUP BY INITCAP(w.nazwa), o.plec)
SELECT mpw.wydzial, mpw.plec, mpw.liczba_osob
FROM max_plec_wydzial mpw
WHERE mpw.liczba_osob=(SELECT MAX(mpw1.liczba_osob)
FROM max_plec_wydzial mpw1
WHERE mpw1.wydzial=mpw.wydzial)
ORDER BY 3 DESC, 1 ASC;

6.Wyświetlić osobę/osoby o najdłuższym nazwisku.

SELECT INITCAP(nazwisko), INITCAP(imie1)
LENGHT(nazwisko)
FROM osoby
WHERE LENGHT(nazwisko)=(
SELECT MAX(LENGHT(nazwisko))
FROM osoby);