Ćwiczenie 1

I. Środowisko pracy SQL*Plus

Po zalogowaniu się w systemie UNIX, użytkownik uruchamia środowisko pracy SQL*Plus pisząc polecenie:

$ sqlplus identyfikator_uzytkownika

Po podaniu hasła użytkownika pojawia się znak gotowości środowiska SQL :

sql>

Od tego momentu użytkownik kontaktuje się z serwerem bazy danych wyłącznie poprzez polecenia języka SQL. W dowolnym miejscu polecenia SQL można przejść do nowej linii i edytować polecenie w nowej linii. Środowisko numeruje kolejne linie polecenia. Polecenie kończone jest znakiem średnika „;” i dlatego po tym znaku i przyciśnięciu klawisza ENTER środowisko podejmuje próbę wykonania polecenia SQL. W przypadku gdy w poleceniu występują błędy, na ekran wyprowadzane są komunikaty o błędach.

Ostatnio wprowadzone polecenie SQL przechowywane jest w buforze. Edycja ostatnio wprowadzonego polecenia możliwa jest w następujący sposób:

- zapisanie w pliku zawartości bufora (plik zapisywany w katalogu domowym użytkownika w systemie UNIX): sql>save plik (plik na dysku posiada rozszerzenie .sql);

- uruchomienie systemowego edytora tekstu (np. vi , lub pico): sql>!pico plik.sql;

- dokonanie zmian w pliku i zapisanie zmian;

- pobranie do bufora zmienionego polecenia: sql> get plik;

- wykonanie polecenia po dokonanych zmianach: sql> run.

W przypadku, gdy zestaw poleceń języka SQL mamy zapisany w pliku (skrypcie), to uruchomienie skryptu SQL odbywa się w następujący sposób:

sql> @nazwa_skryptu

Strukturę tabeli występującej w bazie danych można wyświetlić poleceniem:

sql> desc nazwa_tabeli

Wyświetlenie nazw tabel, które występują w bazie danych można wykonać dzięki zapytaniu do tabeli systemowej (perspektywy) o nazwie: USER_ALL_TABLES:

sql> select table_name from user_all_tables;

II. Definiowanie relacji

ZADANIE: Zdefiniować relacje ZESP, ETAT, PRAC o schematach przedstawionych poniżej. Zdefiniować również odpowiednie ograniczenia integralnościowe.

ZESP
	ID_ZESP			Number(2)	Klucz podstawowy
	NAZWA			Varchar2(20)	Atrybut obowiązkowy
	ADRES			Varchar2(20)

ETAT
	NAZWA			Varchar2(10)	Klucz podstawowy
	PLACA_MIN			Number(6,2)	Atrybut obowiązkowy wartość >0
	PLACA_MAX			Number(6,2)

PRAC
	ID_PRAC			Number(4)	Klucz podstawowy
	NAZWISKO			Varchar2(15)	Atrybut obowiązkowy
	ETAT			Varchar2(10)	Referencja do atrybutu NAZWA relacji ETAT
	SZEF			Number(4)	Referencja do atrybutu ID_PRAC relacji PRAC
	ZATRUDNIONY			date
	PLACA_POD			Number(6,2)	wartość większa od 100
	PLACA_DOD			Number(6,2)	domyślna wartość 0
	ID_ZESP			Number(2)	Referencja do atrybutu ID_ZESP relacji ZESP

2. Utworzyć tabele ZESP, ETAT, PRAC:

CREATE TABLE ZESP (

ID_ZESP Number(2) primary key,

Nazwa Varchar2(20) not null,

Adres Varchar2(20));

CREATE TABLE ETAT (

Nazwa Varchar2(10) primary key,

Placa_min Number(6,2) not null,

Placa_max Number(6,2));

CREATE TABLE PRAC (

ID_PRAC Number(4) primary key,

NAZWISKO Varchar2(15) not null,

ETAT Varchar2(10) references Etat(Nazwa),

SZEF Number(4) references Prac(Id_prac),

ZATRUDNIONY date,

PLACA_POD Number(6,2) check (Placa_pod > 100),

PLACA_DOD Number(6,2) default 0,

ID_ZESP Number(2) references ZESP(ID_ZESP));

3. Sprawdzić struktury utworzonych tablic:

desc etat

desc prac

desc zesp

4. Wstawić wartości do tabeli ZESP:

INSERT INTO ZESP VALUES (10,'ADMINISTRACJA', 'MIKOLAJCZYKA 5');

INSERT INTO ZESP VALUES (20,'INFORMATYKA', 'SOSNKOWSKIEGO 31');

INSERT INTO ZESP VALUES (30,'BUDOWNICTWO', 'KATOWICKA 14');

INSERT INTO ZESP VALUES (40,'AUTOMATYKA', 'SOSNKOWSKIEGO 31');

INSERT INTO ZESP VALUES (50,'ELEKTROTECHNIKA', 'LUBOSZYCKA 9');

5. Utworzyć kopię tablicy ZESP o nazwie ZESP_KOPIA:

CREATE TABLE ZESP_KOPIA AS SELECT * FROM ZESP;

6. Zmienić nazwę tablicy ZESP_KOPIA na ZESP_BIS:

rename zesp_kopia to zesp_bis;

6. Utworzyć kopię tablicy ZESP zawierającą tylko kolumny ID_ZESP i NAZWA:

CREATE TABLE ZESP_KOPIA AS SELECT ID_ZESP, NAZWA FROM ZESP;

7. Do tablicy ZESP_KOPIA dodać kolumnę ADRES:

ALTER TABLE ZESP_KOPIA /*ALTER TABLE zmienia właściwości istniejącej tabeli stworzonej instrukcją CREATE TABLE*/

ADD (ADRES VARCHAR2(10));

8. Zmienić format kolumny ADRES W tablicy ZESP_KOPIA na 20-znakowy:

ALTER TABLE ZESP_KOPIA

MODIFY (ADRES VARCHAR(20));

Uwaga! Wewnętrzne zabezpieczenia bazy nie pozwalają na przedefiniowanie kolumn sprzeczne ze strukturą wprowadzonych już danych.

9. Usunąć tabelę ZESP_BIS:

DROP TABLE ZESP_BIS;

Uwaga! Usunięcie nieodwracalne

10. Usunąć wszystkie rekordy z tabeli ZESP_KOPIA:

DELETE FROM ZESP_KOPIA; (odwracalne poleceniem ROLLBACK)

lub TRUNCATE TABLE ZESP_KOPIA; (nieodwracalne)

11. Napisać skrypt wstawiający wartości do tabeli ETAT

INSERT INTO ETAT VALUES ('PROFESOR' ,2800.00, 4500.00);

INSERT INTO ETAT VALUES ('ADIUNKT' ,1510.00, 2750.00);

INSERT INTO ETAT VALUES ('ASYSTENT' ,1300.00, 1500.00);

INSERT INTO ETAT VALUES ('STAZYSTA' ,650.00, 1250.00);

INSERT INTO ETAT VALUES ('SEKRETARKA',970.00, 1450.00);

INSERT INTO ETAT VALUES ('DYREKTOR' ,2280.00, 3100.00);

12. Napisać skrypt wstawiający wartości do tabeli PRAC

INSERT INTO PRAC VALUES (100,'ABACKI' ,'DYREKTOR', NULL,to_date('01-01-68','DD-MM-RR'),2730.00,420.50,10);

INSERT INTO PRAC VALUES (110,'BABACKI' ,'PROFESOR' ,100 ,to_date('01-05-73','DD-MM-RR'),3350.00,210.00,40);

INSERT INTO PRAC VALUES (120,'CABACKI' ,'PROFESOR' ,100 ,to_date('01-09-77','DD-MM-RR'),3070.00, NULL,30);

INSERT INTO PRAC VALUES (130,'DABACKI' ,'PROFESOR' ,100 ,to_date('01-07-68','DD-MM-RR'), 2960.00, NULL,20);

INSERT INTO PRAC VALUES (140,'EBACKI' ,'PROFESOR' ,130 ,to_date('15-09-75','DD-MM-RR'), 2830.00,105.00,20);

INSERT INTO PRAC VALUES (150,'FABACKI','ADIUNKT' ,130 ,to_date('01-09-77','DD-MM-RR'), 1645.50, NULL,20);

INSERT INTO PRAC VALUES (160,'GABACKI' ,'ADIUNKT' ,130 ,to_date('01-03-85','DD-MM-RR'), 1590.00, NULL,20);

INSERT INTO PRAC VALUES (170,'HABACKI' ,'ASYSTENT' ,130 ,to_date('01-10-92','DD-MM-RR'), 1439.70, 80.50,20);

INSERT INTO PRAC VALUES (190,'IBACKI' ,'ASYSTENT' ,140 ,to_date('01-09-93','DD-MM-RR'), 1371.00, NULL,20);

INSERT INTO PRAC VALUES (180,'JOTBACKA' ,'SEKRETARKA',100 ,to_date('20-02-85','DD-MM-RR'), 1410.20, NULL,10);

INSERT INTO PRAC VALUES (200,'KABACKI' ,'STAZYSTA' ,140 ,to_date('15-07-94','DD-MM-RR'), 1208.00, NULL,30);

INSERT INTO PRAC VALUES (210,'LABACKI' ,'STAZYSTA' ,130 ,to_date('15-10-93','DD-MM-RR'), 1250.00,170.60,30);

INSERT INTO PRAC VALUES (220,'MABACKI' ,'ASYSTENT' ,110 ,to_date('01-10-93','DD-MM-RR'), 1480.00, NULL,20);

INSERT INTO PRAC VALUES (230,'NABACKI' ,'ASYSTENT' ,120 ,to_date('01-09-92','DD-MM-RR'), 1480.00, 90.00,30);

13. Wyświetlić wszystkie rekordy z tablicy ZESP:

SELECT * FROM ZESP;

14. Wyświetlić rekordy z tablicy PRAC posortowane wg daty zatrudnienia

SELECT * FROM PRAC ORDER BY ZATRUDNIONY;/* sortowanie odwrotne desc;*/

15. Wyświetlić posortowane nazwiska, etaty i roczne dochody pracowników (opatrzone nagłówkiem "ROCZNY_ DOCHOD"):

Uwaga: Jeśli nagłówek kolumny zawiera w nazwie spację, to musi być ujęty w cudzysłów.

SELECT NAZWISKO, ETAT, (PLACA_POD + NVL(placa_dod,0))*12 || ' PLN' ROCZNY_DOCHOD

FROM PRAC ORDER BY NAZWISKO;

LABORATORIUM z BAZ DANYCH

ORACLE

4

---