Proste polecenia języka SQL

Cele ćwiczenia

	Zapoznanie z programami administracyjnymi serwera PostgreSQL.
	Zapoznanie z podstawowymi instrukcjami języka SQL.
	Zapoznanie z pracą w środowisku wielu użytkowników.

Sposoby polaczenia z bazą danych

Wykorzystywana w ćwiczeniu baza danych studenci znajduje się na serwerze baz danych PostgreSQL o nazwie argo.am.gdynia.pl. Ćwiczenie można wykonać łącząc się z bazą danych dowolnym programem posiadającym interfejs SQL bazy danych PostgreSQL. W laboratorium do dyspozycji jest program pgAdmin III - standardowy program administracyjny serwera PostgreSQL dostarczany przez producenta. Drugim sposobem wykonania ćwiczenia jest połączenie się przez przeglądarkę z internetowym programem administracyjnym phpPgAdmin, dostępnym pod adresem http://argo.am.gdynia.pl/phpPgAdmin/. Aby uzyskać dostęp do bazy danych, należy znać nazwę użytkownika i hasło, które poda prowadzący zajęcia.

Wewnętrzna sieć Akademii Morskiej znajduję się za zaporą i serwer baz danych PostgreSQL jest widoczny tylko w tej strefie, dlatego programem pgAdmin III można się łączyć z bazą danych tylko wewnątrz Akademii. Sewer WWW, na którym znajduje sie aplikacja phpPgAdmin, jest widoczny w sieci Internet poza Akademią i z aplikacji można korzystać z dowolnego miejsca.

Na rys. 1 przedstawiono diagram bazy danych, która zostanie utworzona i wykorzystywana w bieżącym ćwiczeniu. Diagram został wykonany programem MS Access, w celu ujednolicenia notacji diagramów we wszystkich ćwiczeniach.

studenci-oceny
Rys. 1. Diagram tworzonej bazy danych w notacji MS Access

Dialekt języka SQL używany przez serwer PostgreSQL jest jednym z dialektów najbliższych standardowi języka SQL.

Tworzenie nowych i usuwanie starych tabel

Otwórz program pgAdmin III lub aplikację sieciową http://argo.am.gdynia.pl/phpPgAdmin/.

Połącz się z bazą danych studenci.

Usuń wszystkie znajdujące się w bazie danych tabele i indeksy. Do tego celu zastosuj następujące polecenia języka SQL:

DROP TABLE nazwa_tabeli;

na przykład: DROP TABLE oceny;

DROP INDEX nazwa_indeksu;

Utwórz tabelę studenci.

Tabela powinna zawierać następujące kolumny:

	id_studenta - identyfikator studenta , 4 znaki, klucz główny,
	nazwisko - nazwisko studenta, 25 znaków, wartość wymagana (NOT NULL),
	imie - imię studenta, 25 znaków,
	imie_ojca - imię ojca, 25 znaków,
	dodatkowe informacje, które uznasz za potrzebne.

Do utworzenia tabeli studenci można posłużyć się następującym poleceniem języka SQL:

CREATE TABLE studenci
(id_studenta   CHAR(4) PRIMARY KEY,
nazwisko      VARCHAR(25) NOT NULL,
imie          VARCHAR(25),
imie_ojca     VARCHAR(25));

Utwórz tabelę oceny.

Tabela powinna zawierać następujące kolumny:

	id_studenta - identyfikator studenta, który otrzymał ocenę, 4 znaki, klucz obcy - odwołanie do studenci(id_studenta),
	nazwa przedmiotu - 25 znaków,
	ocena - 15 znaków,
	data - data wystawienia oceny, wartość domyślna CURRENT_DATE,
	identyfikator studenta i nazwa przedmiotu tworzą klucz główny tabeli.

Do utworzenia tabeli oceny można posłużyć się następującym poleceniem języka SQL:

CREATE TABLE oceny
(id_studenta      CHAR(4) REFERENCES studenci(id_studenta),
nazwa_przedmiotu  VARCHAR(25),
ocena             VARCHAR(15),
data              DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (id_studenta, nazwa_przedmiotu));

Uwaga: Ponieważ ćwiczenie wykonywane jest w sieci, na jednej i tej samej bazie danych, tylko z jednego stanowiska można usunąć konkretną tabelę i tylko z jednego stanowiska można utworzyć tabele studenci i oceny.

Tworzenie indeksów

Utwórz indeks dla kolumny nazwisko w tabeli studenci

CREATE INDEX "index studenci po nazwisku" ON studenci(nazwisko);

Wprowadzanie danych do tabel

Wprowadź dane do obu tabel posługując się instrukcją: INSERT INTO nazwa_tabeli VALUES (wartość1, wartość2, ...... ).

Przykłady:

	INSERT INTO studenci VALUES ('0001', 'Papkin', 'Jan', 'Jan');
	INSERT INTO oceny VALUES ('0001', 'Odwaga', 'bdb', '2005-12-23');
	INSERT INTO oceny VALUES ('0001', 'Fizyka', 'bdb', NULL);
	INSERT INTO oceny VALUES ('0001', 'WF', 'dst', DEFAULT);

Proszę wprowadzić co najmniej jeden wiersz do tabeli studenci i kilka, powiązanych z nim wierszy, do tabeli oceny.

	Wprowadzone dane powinny identyfikować wykonującego ćwiczenie w celu sprawdzenia poprawności.
	Praca odbywa się w sieci środowisku wielu użytkowników i mogą pojawiać się konflikty, gdy ktoś spróbuje nadać studentowi wcześniej użyty identyfikator.

Wyszukiwanie danych w tabelach

Posługując się instrukcją SELECT uzyskaj następujące dane:

Pełna listę danych osobowych studentów

SELECT * FROM studenci;

Listę ocen studenta o identyfikatorze '0001'

SELECT * FROM oceny
WHERE id_studenta = '0001';

Wykonaj kolejno następujące zapytania - zinterpretuj uzyskane wyniki

Iloczyn kartezjański tabel studenci i oceny

SELECT * FROM studenci, oceny;

Złączenie tabel studenci i oceny, przez kolumnę id_studenta

SELECT * FROM studenci JOIN oceny USING(id_studenta);

Naturalne złączenie tabel studenci i oceny

SELECT * FROM studenci NATURAL JOIN oceny;

Naturalne lewostronne wewnętrzne złączenie tabel studenci i oceny

SELECT * FROM studenci NATURAL LEFT JOIN oceny;

Lewostronne zewnętrzne złączenie tabel studenci i oceny, przez kolumnę id_studenta

SELECT * FROM studenci LEFT JOIN oceny USING(id_studenta);

Przykłady różnych zapytań - wykonaj je kolejno i zinterpretuj uzyskane wyniki

	SELECT studenci.nazwisko, oceny.* FROM studenci, oceny;
	SELECT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data FROM studenci, oceny;
	SELECT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data FROM studenci NATURAL LEFT JOIN oceny;
	SELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data FROM studenci NATURAL LEFT JOIN oceny;
	SELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data FROM studenci, oceny WHERE studenci.nazwisko = 'Papkin';
	SELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data FROM studenci NATURAL LEFT JOIN oceny WHERE studenci.nazwisko = 'Papkin';
	SELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data FROM studenci, oceny WHERE oceny.nazwa_przedmiotu = 'Odwaga' AND studenci.id_studenta = oceny.id_studenta ORDER BY studenci.nazwisko;

Tworzenie i przeglądanie perspektyw

Utwórz perspektywy lista ocen po nazwisku i lista ocen przedmiotami

	CREATE VIEW "lista ocen po nazwisku" AS SELECT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data FROM studenci NATURAL LEFT JOIN oceny ORDER BY nazwisko;
	CREATE VIEW "lista ocen przedmiotami" AS SELECT oceny.nazwa_przedmiotu, studenci.nazwisko, studenci.imie, oceny.ocena, oceny.data FROM studenci NATURAL LEFT JOIN oceny ORDER BY nazwa_przedmiotu;

Przejrzyj treść perspektyw

	SELECT * FROM "lista ocen po nazwisku";
	SELECT * FROM "lista ocen przedmiotami";

Samodzielnie utwórz perspektywy zawierające

	listę ocen jednego studenta ze wszystkich przedmiotów (przedmioty uporządkowane alfabetycznie),
	listę ocen wszystkich studentów z jednego przedmiotu (studenci uporządkowani alfabetycznie).

Nazwij utworzone przez siebie perspektywy w sposób jednoznacznie identyfikujący ich twórcę.

Uwaga: Ponieważ ćwiczenie wykonywane jest w sieci, na jednej i tej samej bazie danych, perspektywom należy nadać unikalne nazwy, nieużywane przez innych studentów.

Aktualizacja danych

Zmień oceny dowolnych studentów z dowolnych przedmiotów

przykład: zmiana oceny studenta '0001' z przedmiotu Odwaga

UPDATE oceny SET ocena = 'fatalna'
WHERE nazwa_przedmiotu = 'Odwaga' AND id_studenta = '0001';

przykład: zmiana ocen wszystkich studentów z przedmiotu Odwaga

UPDATE oceny SET ocena = 'lwia'
WHERE nazwa_przedmiotu = 'Odwaga';

Usuwanie wierszy z tabel

Usuń wybrane wiersze z tabeli oceny

przykład: usunięcie wiersza z oceną studenta '0001' z przedmiotu Odwaga

DELETE FROM oceny
WHERE nazwa_przedmiotu = 'Odwaga' AND id_studenta = '0001';

Program administracyjny pgAdmin III

Przy pomocy programu pgAdmin III zapoznaj się z zawartością bazy danych studenci - będącej efektem pracy Twojej i pozostałych uczestników ćwiczenia.

Prezentacja danych na stronach WWW

Połącz się z witryną http://wekrmpc15.am.gdynia.pl/testwww,

	zapoznaj się z działaniem aplikacji,
	znajdź wprowadzone przez siebie dane,
	dokonaj zmian w danych wykorzystując odpowiednie strony WWW.