Proste polecenia języka SQL

Cele ćwiczenia

bulletZapoznanie  z programami administracyjnymi serwera PostgreSQL.
bulletZapoznanie z podstawowymi instrukcjami języka SQL.
bulletZapoznanie 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

bulletOtwórz program pgAdmin III lub aplikację sieciową http://argo.am.gdynia.pl/phpPgAdmin/.
bulletPołącz się z bazą danych studenci.
bulletUsuń wszystkie znajdujące się w bazie danych tabele i indeksy. Do tego celu zastosuj następujące polecenia języka SQL:
bulletDROP TABLE nazwa_tabeli;
bulletna przykład: DROP TABLE oceny;
bulletDROP INDEX nazwa_indeksu;
bulletUtwórz tabelę studenci.
bulletTabela powinna zawierać następujące kolumny:
bulletid_studenta - identyfikator studenta , 4 znaki, klucz główny,
bullet nazwisko - nazwisko studenta, 25 znaków, wartość wymagana (NOT NULL),
bulletimie - imię studenta, 25 znaków,
bullet imie_ojca - imię ojca, 25 znaków,
bulletdodatkowe informacje, które uznasz za potrzebne.
bulletDo utworzenia tabeli studenci można posłużyć się następującym poleceniem języka SQL:
bulletCREATE TABLE studenci
(id_studenta   CHAR(4) PRIMARY KEY,
nazwisko      VARCHAR(25) NOT NULL,
imie          VARCHAR(25),
imie_ojca     VARCHAR(25));
bulletUtwórz tabelę oceny.
bulletTabela powinna zawierać następujące kolumny:
bullet id_studenta - identyfikator studenta, który otrzymał ocenę, 4 znaki, klucz obcy - odwołanie do studenci(id_studenta),
bulletnazwa przedmiotu - 25 znaków,
bulletocena - 15 znaków,
bulletdata - data wystawienia oceny, wartość domyślna CURRENT_DATE,
bulletidentyfikator studenta i nazwa przedmiotu tworzą klucz główny tabeli.
bulletDo utworzenia tabeli oceny można posłużyć się następującym poleceniem języka SQL:
bulletCREATE 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

bulletUtwórz indeks dla kolumny nazwisko w tabeli studenci
bulletCREATE INDEX "index studenci po nazwisku" ON studenci(nazwisko);

Wprowadzanie danych do tabel

bulletWprowadź dane do obu tabel posługując się instrukcją: INSERT INTO nazwa_tabeli VALUES (wartość1, wartość2, ...... ).
bulletPrzykłady:
bulletINSERT INTO studenci VALUES
('0001', 'Papkin', 'Jan', 'Jan');
bulletINSERT INTO oceny VALUES
('0001', 'Odwaga', 'bdb', '2005-12-23');
bulletINSERT INTO oceny VALUES
('0001', 'Fizyka', 'bdb', NULL);
bulletINSERT INTO oceny VALUES
('0001', 'WF', 'dst', DEFAULT);
bulletProszę wprowadzić co najmniej jeden wiersz do tabeli studenci i kilka, powiązanych z nim wierszy, do tabeli oceny.
bulletWprowadzone dane powinny identyfikować wykonującego ćwiczenie w celu sprawdzenia poprawności.
bulletPraca 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

bulletPosługując się instrukcją SELECT uzyskaj następujące dane:
bulletPełna listę danych osobowych studentów
bulletSELECT * FROM studenci;
bulletListę ocen studenta o identyfikatorze '0001'
bulletSELECT * FROM oceny
WHERE id_studenta = '0001';
bulletWykonaj kolejno następujące zapytania - zinterpretuj uzyskane wyniki
bulletIloczyn kartezjański tabel studenci i oceny
bulletSELECT * FROM studenci, oceny;
bulletZłączenie tabel studenci i oceny, przez kolumnę id_studenta
bulletSELECT * FROM studenci JOIN oceny USING(id_studenta);
bulletNaturalne złączenie tabel studenci i oceny
bulletSELECT * FROM studenci NATURAL JOIN oceny;
bulletNaturalne lewostronne wewnętrzne złączenie tabel studenci i oceny
bulletSELECT * FROM studenci NATURAL LEFT JOIN oceny;
bulletLewostronne zewnętrzne złączenie tabel studenci i oceny, przez kolumnę id_studenta
bulletSELECT * FROM studenci LEFT JOIN oceny USING(id_studenta);
bulletPrzykłady różnych zapytań - wykonaj je kolejno i zinterpretuj uzyskane wyniki
bulletSELECT studenci.nazwisko, oceny.* FROM studenci, oceny;
bulletSELECT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data
FROM studenci, oceny;
bulletSELECT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data
FROM studenci NATURAL LEFT JOIN oceny;
bulletSELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data
FROM studenci NATURAL LEFT JOIN oceny;
bulletSELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data
FROM studenci, oceny
WHERE studenci.nazwisko = 'Papkin';
bulletSELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.nazwa_przedmiotu, oceny.ocena, oceny.data
FROM studenci NATURAL LEFT JOIN oceny
WHERE studenci.nazwisko = 'Papkin';
bulletSELECT 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

bulletUtwórz perspektywy lista ocen po nazwisku i lista ocen przedmiotami
bulletCREATE 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;
bulletCREATE 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;
bulletPrzejrzyj treść perspektyw
bulletSELECT * FROM "lista ocen po nazwisku";
bulletSELECT * FROM "lista ocen przedmiotami";
bulletSamodzielnie utwórz perspektywy zawierające
bulletlistę ocen jednego studenta ze wszystkich przedmiotów (przedmioty uporządkowane alfabetycznie),
bulletlistę ocen wszystkich studentów z jednego przedmiotu (studenci uporządkowani alfabetycznie).
bulletNazwij 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

bulletZmień oceny dowolnych studentów z dowolnych przedmiotów
bulletprzykład: zmiana oceny studenta '0001' z przedmiotu Odwaga
bulletUPDATE oceny SET ocena = 'fatalna'
WHERE nazwa_przedmiotu = 'Odwaga' AND id_studenta = '0001';
bulletprzykład: zmiana ocen wszystkich studentów z przedmiotu Odwaga
bulletUPDATE oceny SET ocena = 'lwia'
WHERE nazwa_przedmiotu = 'Odwaga';

Usuwanie wierszy z tabel

bulletUsuń wybrane wiersze z tabeli oceny
bulletprzykład: usunięcie wiersza z oceną studenta '0001' z przedmiotu Odwaga
bulletDELETE FROM oceny
WHERE nazwa_przedmiotu = 'Odwaga' AND id_studenta = '0001';

Program administracyjny pgAdmin III

bulletPrzy 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

bulletPołącz się z witryną http://wekrmpc15.am.gdynia.pl/testwww,
bulletzapoznaj się z działaniem aplikacji,
bulletznajdź wprowadzone przez siebie dane,
bulletdokonaj zmian w danych wykorzystując odpowiednie strony WWW.