Laboratorium

Informatyka II: Bazy danych

- 1 -

1. Tworzenie i usuwanie tabeli

CREATE TABLE klient
(
klient_id serial NOT NULL,
imie character varying(16) NOT NULL,
nazwisko character varying(32) NOT NULL,
CONSTRAINT klient_pk PRIMARY KEY (klient_id)
);

CREATE TABLE zamowienie
(
zamowienie_id serial NOT NULL,
klient_id integer NOT NULL,
data date NOT NULL,
kwota numeric(9,2) NOT NULL,
CONSTRAINT zamowienie_pk PRIMARY KEY (zamowienie_id)
);

DROP TABLE klient;

2. Modyfikacja tabeli

CREATE TABLE klient
(
klient_id serial NOT NULL,
imie character varying(16) NOT NULL
);

2.1. Dodanie kolumny

ALTER TABLE klient ADD COLUMN kod char(5);

2.2. Zmiana nazwy kolumny

ALTER TABLE klient RENAME COLUMN kod TO kod_pocztowy;

2.3. Zmiana typu kolumny

ALTER TABLE klient ALTER COLUMN kod_pocztowy TYPE char(6);

Laboratorium

Informatyka II: Bazy danych

- 2 -

2.4. Ustawienie wartości domyślnej kolumny

ALTER TABLE klient ALTER COLUMN kod_pocztowy SET DEFAULT '00000';

2.5. Usunięcie wartości domyślnej dla kolumny

ALTER TABLE klient ALTER COLUMN kod_pocztowy DROP DEFAULT;

2.6. Dodanie klucza głównego tabeli

ALTER TABLE klient ADD CONSTRAINT klient_pk PRIMARY KEY (klient_id);

2.7. Dodanie klucza obcego tabeli

ALTER TABLE zamowienie ADD CONSTRAINT klient_fk FOREIGN KEY (klient_id) REFERENCES
klient(klient_id);

wówczas tabela zamowienie będzie miała definicję

CREATE TABLE zamowienie
(
zamowienie_id serial NOT NULL,
klient_id integer NOT NULL,
data date NOT NULL,
kwota numeric(9,2) NOT NULL,
CONSTRAINT zamowienie_pk PRIMARY KEY (zamowienie_id),
CONSTRAINT klient_fk FOREIGN KEY (klient_id) REFERENCES klient(klient_id)
);

2.8. Usunięcie klucza obcego

ALTER TABLE zamowienie DROP CONSTRAINT klient_fk;

2.9. Usunięcie klucza głównego

ALTER TABLE zamowienie DROP CONSTRAINT zamowienie_pk;

2.10. Usunięcie kolumny

Laboratorium

Informatyka II: Bazy danych

- 3 -

ALTER TABLE zamowienie DROP COLUMN kwota;

3. Wprowadzanie danych do tabel

3.1. Wprowadzanie danych dla pojedynczego rekordu

INSERT INTO klient (klient_id, imie, kod_pocztowy) VALUES (DEFAULT, 'Jan', '21-122');

3.2. Wprowadzanie danych dla kilku rekordów

INSERT INTO klient (klient_id, imie, kod_pocztowy) VALUES
(DEFAULT, 'Piotr', '12345'),
(DEFAULT, 'Ewa', '12345'),
(DEFAULT, 'Jakub', '11111');

3.3. Wprowadzanie danych dla kilku rekordów i wybranych kolumn

INSERT INTO klient (imie, kod_pocztowy) VALUES
('Jan', '21122'),
('Olga', '67098'),
('Maja', '55002');

3.4. Usuwanie wybranych rekordów

DELETE FROM klient WHERE imie='Piotr';

3.5. Usunięcie wszystkich rekordów z tabeli

DELETE FROM klient;

4. Pobieranie danych z tabel

CREATE TABLE osoba
(
osoba_id SERIAL,
imie VARCHAR(20),
nazwisko VARCHAR(35),

Laboratorium

Informatyka II: Bazy danych

- 4 -

rok_urodz SMALLINT,
miejsce_urodz VARCHAR (35),
CONSTRAINT osoba_pk PRIMARY KEY (osoba_id)
);

INSERT INTO osoba (imie, nazwisko, rok_urodz, miejsce_urodz) VALUES
('Adam', 'Kowalski', 1964, 'Bydgoszcz'),
('Adam', 'Nowak', 1972, 'Szczecin'),
('Andrzej', 'Kowalski', 1986, 'Nidzica'),
('Arkadiusz', 'Malinowski', 1986, 'Wrocław'),
('Andrzej', 'Malinowski', 1989, 'Wrocław'),
('Krzysztof', 'Nowicki', 1986, 'Bydgoszcz'),
('Kacper', 'Adamczyk', 1971, 'Wrocław'),
('Kamil', 'Andrzejczak', 1971, 'Szczecin'),
('Krzysztof', 'Kowalski', 1984, 'Cieszyn'),
('Kamil', 'Borowski', 1975, 'Kudowa Zdrój'),
('Piotr', 'Nowicki', 1977, 'Szczecin');

CREATE TABLE towar
(
towar_id serial NOT NULL,
nazwa character varying(35),
cena numeric(9,2),
CONSTRAINT "towar_PK" PRIMARY KEY (towar_id)
);
INSERT INTO towar (towar_id, nazwa, cena) VALUES
(DEFAULT, 'towar 1', 100.99),
(DEFAULT, 'towar 2', 50.00),
(DEFAULT, 'towar 3', 1029.00);

Laboratorium

Informatyka II: Bazy danych

- 5 -

CREATE TABLE zamowienie
(
zamowienie_id serial,
osoba_id integer,
towar_id integer,
data date,
ilosc_szt smallint,
CONSTRAINT zamowienie_PK PRIMARY KEY (zamowienie_id),
CONSTRAINT zamowienie_osoba_FK FOREIGN KEY (osoba_id) REFERENCES osoba(osoba_id),
CONSTRAINT zamowienie_towar_FK FOREIGN KEY (towar_id) REFERENCES towar(towar_id)
);
INSERT INTO zamowienie (zamowienie_id, osoba_id, towar_id, data, ilosc_szt) VALUES
(DEFAULT,1,1, '2009-10-10',1),
(DEFAULT,2,1,'2009-10-10', 2),
(DEFAULT,2,2, '2009-11-10',5),
(DEFAULT,2,2, '2009-11-09',5),
(DEFAULT,2,3, '2009-10-10',2), (DEFAULT,5,3, '2009-01-10',3),
(DEFAULT,5,3, '2009-08-10',1), (DEFAULT, 6,1, '2009-05-10',1), (DEFAULT,3,1, '2009-10-10',1),
(DEFAULT,1,1, '2009-10-10',1);

Składnia polecenia SELECT
SELECT kolumna1, kolumna2, ..., kolumnaN
FROM nazwa_tabeli
[WHERE warunek]
[ORDER BY kolumna1, kolumna2, ..., kolumnaN [ASC|DESC]];

Laboratorium

Informatyka II: Bazy danych

- 6 -

4.1. Wyświetlenie całej zawartości tabeli

SELECT * FROM osoba;

4.2. Wyświetlenie całej zawartości tabeli posortowanej wg wybranych kolumn

SELECT * FROM osoba ORDER BY nazwisko, imie;

4.3. Wyświetlenie określonych kolumn tabeli posortowanej wg wybranych kolumn

SELECT imie,nazwisko FROM osoba ORDER BY nazwisko;

Laboratorium

Informatyka II: Bazy danych

- 7 -

SELECT nazwisko,imie FROM osoba ORDER BY nazwisko;

4.4. Wyświetlenie określonych kolumn tabeli wraz ze zmianą nazw wyświetlanych kolumn

SELECT imie AS "Imię",nazwisko AS "Nazwisko", rok_urodz AS "Rok urodzenia",
miejsce_urodz AS "Miejsce urodzenia" FROM osoba ORDER BY nazwisko;

Laboratorium

Informatyka II: Bazy danych

- 8 -

4.5. Wyświetlenie wybranych rekordów tabeli spełniających warunek równości

SELECT * FROM osoba WHERE nazwisko='Kowalski';

4.6. Wyświetlenie wybranych rekordów tabeli spełniających warunek większości

SELECT * FROM osoba WHERE rok_urodz > 1985;

4.7. Wyświetlenie wybranych rekordów tabeli spełniających dwa warunki

SELECT * FROM osoba WHERE osoba_id >= 3 AND osoba_id <= 6;

4.8. Wyświetlenie wybranych rekordów tabeli spełniających warunek „BETWEEN”

SELECT * FROM osoba WHERE osoba_id BETWEEN 1 AND 6;

4.9. Wyświetlenie wybranych rekordów tabeli spełniających warunek „IN”

SELECT * FROM osoba WHERE osoba_id IN (3,5,7);

Laboratorium

Informatyka II: Bazy danych

- 9 -

4.10. Wyświetlenie wybranych rekordów tabeli spełniających warunek „NOT IN”

SELECT * FROM osoba WHERE osoba_id NOT IN (3,5,7);
Powyższe polecenie jest równoważne:
SELECT * FROM osoba WHERE osoba_id <>3 AND osoba_id<>5 AND osoba_id<>7;

4.11. Wyświetlenie wybranych rekordów tabeli spełniających warunek „LIKE”

SELECT * FROM osoba WHERE imie LIKE 'Ka%';

4.12. Wyświetlenie wybranych rekordów tabeli spełniających kilka warunków

SELECT * FROM osoba WHERE imie LIKE 'A%' AND rok_urodz>1970 AND miejsce_urodz IN
('Wrocław', 'Szczecin');

4.13. Selektywne wyświetlanie rekordów oraz kolumn „SELECT DISTINCT”

SELECT DISTINCT nazwisko FROM osoba ORDER BY nazwisko;

Laboratorium

Informatyka II: Bazy danych

- 10 -

4.14. Selektywne wyświetlenie określonej liczby kolejnych pierwszych rekordów tabeli „LIMIT”

SELECT * FROM osoba ORDER BY rok_urodz DESC LIMIT 3;

4.15. Selektywne wyświetlenie określonej liczby kolejnych pierwszych rekordów tabeli „LIMIT” oraz

„OFFSET”

SELECT * FROM osoba ORDER BY osoba_id LIMIT 3 OFFSET 3;

4.16. Wyświetlanie połączonych danych z 2 tabel

SELECT zamowienie_id, imie, nazwisko, data
FROM osoba, zamowienie
WHERE zamowienie.osoba_id=osoba.osoba_id;

Laboratorium

Informatyka II: Bazy danych

- 11 -

co jest równorzędne z poleceniem

SELECT zamowienie_id, imie, nazwisko, data
FROM zamowienie INNER JOIN osoba
ON zamowienie.osoba_id=osoba.osoba_id;

4.17. Wyświetlanie połączonych danych z 3 tabel

SELECT zamowienie_id AS "nr zamówienia", data, imie, nazwisko, nazwa, cena AS "cena
jednostkowa", ilosc_szt AS "ilość sztuk"
FROM osoba, zamowienie, towar
WHERE zamowienie.osoba_id=osoba.osoba_id AND zamowienie.towar_id=towar.towar_id
ORDER BY data DESC;

5. Widoki

5.1. Tworzenie widoku o nazwie zamowienie_osoba1

CREATE VIEW zamowienie_osoba1
AS SELECT * FROM zamowienie WHERE osoba_id=1;

5.2. Pobieranie danych z widoku zamowienie_osoba1

SELECT * FROM zamowienie_osoba1;

Laboratorium

Informatyka II: Bazy danych

- 12 -

5.3. Usuwanie widoku zamowienie_osoba1

DROP VIEW zamowienie_osoba1;

5.4. Widoki przechowują dynamicznie dane o rekordach w tabeli

5.4.1. Dodaj nowy rekord do tabeli z zamówieniem osoby o id = 1
5.4.2. Pobierz dane z widoku zamowienie_osoba1

6. Agregacja danych

6.1. Obliczenie ilości rekordów w tabeli zamówienieI:

SELECT COUNT (*) FROM zamowienie;

6.1.1. Obliczenie ilości rekordów w tabeli zamówienie i przedstawienie ich z odpowiednim

nagłówkiem:

SELECT COUNT (*) AS "liczba zamówień" FROM zamowienie;

6.2. Obliczenie ilości rekordów tabeli zamówienie spełniających warunek:

SELECT COUNT (*) AS "liczba zamówień powyżej 2" FROM zamowienie
WHERE ilosc_szt>2;

6.3. Obliczenie wartości średniej z kolumny wartość:

SELECT AVG (wartosc) AS "Średnia wartość zamówienia" FROM zamowienie;

Laboratorium

Informatyka II: Bazy danych

- 13 -

6.4. Obliczenie wartości minimalnej kolumny wartość:

SELECT MIN (wartosc) AS "Minimalna wartość zamówienia" FROM zamowienie;

6.5. Obliczenie wartości maksymalnej kolumny wartość:

SELECT MAX (wartosc) AS "Maksymalna wartość zamówienia" FROM zamowienie;

6.6. Obliczenie sumy wartości umieszczonych w kolumnie wartość:

SELECT SUM (wartosc) AS "Sumaryczna wartość zamówień" FROM zamowienie;

6.7. Zestawienie wartości średniej, minimalnej, maksymalnej, oraz sumy wartości zleceń wg kolumny

wartość z tabeli zamowienie (statystyka wszystkich zamówień)

SELECT AVG (wartosc) AS " Średnia wartość zamówienia ",
MIN (wartosc) AS " Minimalna wartość zamówienia ",
MAX (wartosc) AS " Maksymalna wartość zamówienia ",
SUM (wartosc) AS "Sumaryczna wartość zamówień"
FROM zamowienie;

7. Grupowanie danych i agregacja danych

Laboratorium

Informatyka II: Bazy danych

- 14 -

7.1. Zestawienie wartości średnich zamówień wg klientów. Grupowanie wyników zapytania dla tabeli

zamowienie wg kolumny osoba_id

SELECT osoba_id AS "Identyfikator klienta" , AVG (wartosc) AS "Średnia wartość zamówienia"
FROM zamowienie
GROUP BY osoba_id;

7.2. Zestawienie wartości średnich zamówień wg klientów. Połączenie wyników zapytania dla dwóch

tabel zamowienie oraz osoba, w celu wyświetlenia imienia oraz nazwiska klienta w miejscu
identyfikatora . Grupowanie wyników zapytania dla tabel zamowienie oraz osoba wg kolumny
imie, nazwisko z tabeli osoba.

SELECT nazwisko, imie, AVG (wartosc) AS " Średnia wartość zamówienia"
FROM zamowienie INNER JOIN osoba
ON zamowienie.osoba_id=osoba.osoba_id
GROUP BY nazwisko, imie
ORDER BY AVG (wartosc) DESC;

7.3. Zestawienie wartości statystyki zamówień (wartości średnie, min., max, suma zamówień) wg

klientów. Połączenie wyników zapytania dla dwóch tabel zamowienie oraz osoba, w celu
wyświetlenia imienia oraz nazwiska klienta w miejscu identyfikatora . Grupowanie wyników
zapytania dla tabel zamowienie oraz osoba wg kolumn imie, nazwisko z tabeli osoba.

SELECT nazwisko, imie, AVG (wartosc) AS " Średnia wartość zamówienia ",
MIN (wartosc) AS "Minimalna wartość zamówienia",
MAX (wartosc) AS "Maksymalna wartość zamówienia",
SUM (wartosc) AS "Sumaryczna wartość zamówienia"
FROM zamowienie INNER JOIN osoba
ON zamowienie.osoba_id=osoba.osoba_id
GROUP BY nazwisko, imie
ORDER BY AVG (wartosc) DESC;

Laboratorium

Informatyka II: Bazy danych

- 15 -

7.4. Utworzenie widoku zawierającego wyniki zapytania z agregacją oraz grupowaniem danych na

podstawie zapytania z pkt 7.3

CREATE VIEW statystyka_zamowien
AS SELECT nazwisko, imie, AVG (wartosc) AS " Średnia wartość zamówienia ",
MIN (wartosc) AS "Minimalna wartość zamówienia",
MAX (wartosc) AS "Maksymalna wartość zamówienia",
SUM (wartosc) AS "Sumaryczna wartość zamówienia"
FROM zamowienie INNER JOIN osoba
ON zamowienie.osoba_id=osoba.osoba_id
GROUP BY nazwisko, imie
ORDER BY AVG (wartosc) DESC;

Wyświetlenie widoku statystyka_zamowien

SELECT * FROM statystyka_zamowien;

umożliwia prostsze zdefiniowanie polecenia do prezentacji wyników statystycznej analizy zamówień
których dane zostały zapisane w tabeli zamowienie.