Paweł Kopeć, Grupa 23A, nr indeksu 98002

1

Sprawozdanie

1.

Założenia:

a.

Baza danych będzie opierała się na działalności Hurtowni artykułów kosmetycznych.

b.

Wszystkie operacje tworzące bazę danych, jak i jej późniejszą obsługę wykonane zostaną

w programie SQL developer dostarczonym przez firmę Oracle.

c.

Język pisania bazy danych to Oracle SQL

2.

Tworzenie tabel:

a.

Tabela Typy:

CREATE TABLE

TYPY(

ID_TYPU NUMBER(4) CONSTRAINT PK_ID_TYPU PRIMARY KEY,
NAZWA VARCHAR(15)
);

b.

Tabela Sklepy:

CREATE TABLE

SKLEPY(

ID_SKLEPU NUMBER(4) CONSTRAINT PK_ID_SKLEPU PRIMARY KEY,
NAZWA VARCHAR(15) NOT NULL,
NIP NUMBER(10) NOT NULL,
ULICA VARCHAR(15) NOT NULL,
NUMER NUMBER(3),
TELEFON NUMBER(9),
MIASTO VARCHAR(20)
);

c.

Tabela Produkty:

CREATE TABLE

PRODUKTY(

ID_PRODUKTU NUMBER(4) CONSTRAINT PK_ID_PRODUKTU PRIMARY KEY,
ID_TYPU NUMBER(4) CONSTRAINT FK_ID_TYPU REFERENCES TYPY(ID_TYPU),
NAZWA VARCHAR(15) NOT NULL,
CENA NUMBER(5) NOT NULL
);

d.

Tabela Stanowiska2:

CREATE TABLE

STANOWISKA2(

ID_STANOWISKA NUMBER(4) CONSTRAINT PK_ID_STANOWISKA PRIMARY KEY,
NAZWA VARCHAR(15),
PLACA NUMBER(5) DEFAULT 1200
);

e.

Tabela Pracownicy2:

CREATE TABLE

PRACOWNICY2(

ID_PRACOWNIKA NUMBER(4) CONSTRAINT PK_ID_PRACOWNIKA PRIMARY KEY,
IMIE VARCHAR(15),
NAZWISKO VARCHAR(15) NOT NULL,
PESEL NUMBER(11) NOT NULL,
ID_STANOWISKA NUMBER(4) CONSTRAINT FK_ID_STANOWISKA REFERENCES
STANOWISKA2(ID_STANOWISKA)
);

f.

Tabela Zamowienia:

Paweł Kopeć, Grupa 23A, nr indeksu 98002

CREATE TABLE
ID_ZAMOWIENIA
DATA_ZAMOWIENIA
ID_PRACOWNIKA
PRACOWNICY2(ID_PRACOWNIKA),
ID_SKLEPU
SKLEPY(ID_SKLEPU)
);

g.

Tabela Szczegoly_zamowienia:

CREATE TABLE
ID_SZCZEGOLU
ID_PRODUKTU
PRODUKTY(ID_PRODUKTU),
ILOSC NUMBER
CENA NUMBER
);

3.

Układ relacji pomiędzy tabelami wygląda następująco:

Paweł Kopeć, Grupa 23A, nr indeksu 98002

2

CREATE TABLE

ZAMOWIENIA(

ID_ZAMOWIENIA NUMBER(4) CONSTRAINT PK_ID_ZAMOWIENIA
DATA_ZAMOWIENIA DATE NOT NULL,
ID_PRACOWNIKA NUMBER(4) CONSTRAINT FK_ID_PRACOWNIKA
PRACOWNICY2(ID_PRACOWNIKA),
ID_SKLEPU NUMBER(4) CONSTRAINT FK_ID_SKLEPU REFERENCES
SKLEPY(ID_SKLEPU)

Tabela Szczegoly_zamowienia:

CREATE TABLE

SZCZEGOLY_ZAMOWIENIA(

ID_SZCZEGOLU NUMBER(4) CONSTRAINT PK_ID_SZCZEGOLU
ID_PRODUKTU NUMBER(4) CONSTRAINT FK_ID_PRODUKTU
PRODUKTY(ID_PRODUKTU),

NUMBER

(3),

NUMBER

(5)

Układ relacji pomiędzy tabelami wygląda następująco:

PK_ID_ZAMOWIENIA PRIMARY KEY,

FK_ID_PRACOWNIKA REFERENCES

REFERENCES

PK_ID_SZCZEGOLU PRIMARY KEY,

T FK_ID_PRODUKTU REFERENCES

Paweł Kopeć, Grupa 23A, nr indeksu 98002

3

4.

Wstawienie danych do tabel:
INSERT INTO

SKLEPY VALUES(1, 'MAJA', 6553452232, 'PODCHORAZYCH', 15, 126554534,

'KRAKOW', 'MALOPOLSKIE');
INSERT INTO

SKLEPY VALUES(2, 'ZABKA', 6553472254, 'MALINOWSKIEGO', 45, 126457784,

'KRAKOW', 'MALOPOLSKIE');
INSERT INTO

SKLEPY VALUES(3, 'COSMETICS', 6541124434, 'TRAUGUTTA', 1, 414432355,

'KIELCE', 'SWIETOKRZYSKIE');
INSERT INTO

SKLEPY VALUES(4, 'BARNABA', 6541226757, 'TOWAROWA', 5, 145542232,

'TARNOW', 'MALOPOLSKIE');
INSERT INTO

TYPY VALUES(1, 'SZCZOTKI');

INSERT INTO

TYPY VALUES(2, 'MANICURE');

INSERT INTO

TYPY VALUES(3, 'PERFUMY');

INSERT INTO

TYPY VALUES(4, 'HIGIENA');

INSERT INTO

TYPY VALUES(5, 'MAKIJAZ');

INSERT INTO

PRODUKTY VALUES(1, 5, 'KREDKA DO OCZU', 3.00);

INSERT INTO

PRODUKTY VALUES(2, 5, 'SZMINKA', 6.99);

INSERT

INTO PRODUKTY VALUES(3, 4, 'PLYN DO KAPIELI', 12.49);

INSERT

INTO PRODUKTY VALUES(4, 3, 'ADDIDAS ONCE', 54.99);

INSERT

INTO PRODUKTY VALUES(5, 3, 'NIVEA CLASSIC', 16.99);

INSERT

INTO PRODUKTY VALUES(6, 2, 'LAKIER', 7.39);

INSERT

INTO PRODUKTY VALUES(7, 2, 'NOZYCZKI', 2.99);

INSERT

INTO PRODUKTY VALUES(8, 1, 'SZCZOTKA OKRAGLA', 11.00);

INSERT

INTO PRODUKTY VALUES(9, 1, 'SZCZOTKA PLASKA', 9.99);

INSERT

INTO PRODUKTY VALUES(10, 1, 'GRZEBIEN', 5.50);

INSERT

INTO STANOWISKA2 VALUES(1, 'PREZES', 10000);

INSERT

INTO STANOWISKA2 VALUES(2, 'KSIEGOWA', 6000);

INSERT

INTO STANOWISKA2 VALUES(3, 'MAGAZYNIER', 3000);

INSERT

INTO STANOWISKA2 VALUES(4, 'KIEROWCA', 1800);

INSERT

INTO PRACOWNICY2 VALUES(1, 'ANTONIO', 'BOTICCO', 44052386194, 1);

INSERT

INTO PRACOWNICY2 VALUES(2, 'JANINA', 'KOWALSKA', 56011198525, 2);

INSERT

INTO PRACOWNICY2 VALUES(3, 'JOZEF', 'FUS', 76123187891, 3);

INSERT

INTO PRACOWNICY2 VALUES(4, 'ANNA', 'ZAPODAJLO', 88040788563, 4);

INSERT

INTO PRACOWNICY2 VALUES(5, 'ADAM', 'SPRZEDAJLO', 87070412874, 4);

INSERT

INTO ZAMOWIENIA VALUES(1, TO_DATE('2013/04/15'), 4, 2);

INSERT

INTO ZAMOWIENIA VALUES(2, TO_DATE('2013/04/16'), 5, 4);

INSERT

INTO ZAMOWIENIA VALUES(3, TO_DATE('2012/12/22'), 4, 1);

INSERT

INTO ZAMOWIENIA VALUES(4, TO_DATE('2013/02/02'), 4, 2);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(1,2,3,1);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(2,1,2,1);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(3,4,1,1);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(4,1,10,2);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(5,10,2,3);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(6,9,4,3);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(7,8,1,3);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(8,6,5,3);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(9,5,3,4);

INSERT

INTO SZCZEGOLY_ZAMOWIENIA VALUES(10,4,4,4);

Paweł Kopeć, Grupa 23A, nr indeksu 98002

5.

Podczas wstawiania danych i wykonywania ćwiczenia okazało się, że niektóre z kolumn
ustawione zostały nieprawidłowo. Na szczęście Oracle SQL udostępnia możliwość modyfikacji
tabel, także nie trzeba było tworzyć całej tabeli na nowo. Wykonane modyfikacje:

a.

Dodanie pola Wojewodztwo w tabeli Sklepy:

ALTER TABLE
ADD

WOJEWODZTWO

b.

Pole Cena w tabeli Produkty

ALTER TABLE
MODIFY

CENA

c.

Pole Pesel w tabeli Pracownicy2:

ALTER TABLE
MODIFY

PESEL

6.

Do tabeli, oraz kolumn dodano także następujące komentarze:

COMMENT ON TABLE

ZAMOWIENIA

COMMENT ON TABLE

PRACOWNICY2

HURTOWNI';
COMMENT ON COLUMN

SZCZEGOLY_ZAMOWIENIA.ID_PRODUKTU

JAKI JEST NA FAKTURZE'
COMMENT ON COLUMN

SZCZEGOLY_ZAMOWIENIA.ILOSC

FAKTURZE';
COMMENT ON COLUMN

SZCZEGOLY_ZAMOWIENIA.ID_ZAMOWIENIA

7.

Wykonane Selecty:

a.

Wyświetl wszystkie dane z tabeli Produkty

SELECT * FROM PRODUKTY;

Paweł Kopeć, Grupa 23A, nr indeksu 98002

4

Podczas wstawiania danych i wykonywania ćwiczenia okazało się, że niektóre z kolumn

zostały nieprawidłowo. Na szczęście Oracle SQL udostępnia możliwość modyfikacji

tabel, także nie trzeba było tworzyć całej tabeli na nowo. Wykonane modyfikacje:

Dodanie pola Wojewodztwo w tabeli Sklepy:

ALTER TABLE

SKLEPY

WOJEWODZTWO VARCHAR(20)

ena w tabeli Produkty:

ALTER TABLE

PRODUKTY

CENA NUMBER(5,2) NOT NULL

Pole Pesel w tabeli Pracownicy2:

ALTER TABLE

PRACOWNICY2

PESEL VARCHAR(11) NOT NULL

Do tabeli, oraz kolumn dodano także następujące komentarze:

ZAMOWIENIA IS 'WYKAZ FAKTUR';
PRACOWNICY2 IS 'WYKAZ PRACOWNIKOW ZATRUDNIONYCH W

SZCZEGOLY_ZAMOWIENIA.ID_PRODUKTU IS 'NUMER ID PRODUKTU

SZCZEGOLY_ZAMOWIENIA.ILOSC IS 'ILOSC PRODUKTOW NA

SZCZEGOLY_ZAMOWIENIA.ID_ZAMOWIENIA IS 'NUMER FAKTURY';

Wyświetl wszystkie dane z tabeli Produkty

SELECT * FROM PRODUKTY;

Podczas wstawiania danych i wykonywania ćwiczenia okazało się, że niektóre z kolumn

zostały nieprawidłowo. Na szczęście Oracle SQL udostępnia możliwość modyfikacji

tabel, także nie trzeba było tworzyć całej tabeli na nowo. Wykonane modyfikacje:

'WYKAZ PRACOWNIKOW ZATRUDNIONYCH W

'NUMER ID PRODUKTU

'ILOSC PRODUKTOW NA

'NUMER FAKTURY';

Paweł Kopeć, Grupa 23A, nr indeksu 98002

b.

Wyświetl nazwę, N

SELECT

NAZWA, NIP, MIASTO

'MALOPOLSKIE';

c.

Wypisz nazwę, NIP i miasto
kiedykolwiek zamówienia w hurtowni

SELECT

DISTINCT NAZWA, NIP, MIASTO, WOJEWODZTWO

WHERE

zamowienia.id_sklepu = sklepy.id_sklepu;

d.

Wyświetl imię, nazwisko

którzy zarabiają mniej niż średnia płaca w firmie

SELECT

IMIE, NAZWISKO, PESEL, STANOWISKA2.NAZWA, STANOWISKA2.PLACA

STANOWISKA2, PRACOWNICY2
pracownicy2.id_stanowiska

e.

Wyświetl wszystkie dane na temat sklepów, mających nazwę żabka

SELECT

* FROM SKLEPY

Paweł Kopeć, Grupa 23A, nr indeksu 98002

5

Wyświetl nazwę, NIP i miasto dla wszystkich sklepów z małopolski

NAZWA, NIP, MIASTO FROM SKLEPY WHERE WOJEWODZTWO

Wypisz nazwę, NIP i miasto i województwo dla wszystkich sklepów, które złożyły
kiedykolwiek zamówienia w hurtowni

NAZWA, NIP, MIASTO, WOJEWODZTWO FROM SKLEPY, ZAMOWIENIA

zamowienia.id_sklepu = sklepy.id_sklepu;

nazwisko, pesel pracowników, nazwę stanowiska

zarabiają mniej niż średnia płaca w firmie

IMIE, NAZWISKO, PESEL, STANOWISKA2.NAZWA, STANOWISKA2.PLACA

STANOWISKA2, PRACOWNICY2 WHERE stanowiska2.id_stanowiska =
pracownicy2.id_stanowiska AND PLACA < (SELECT AVG(PLACA) FROM STANOWISKA2);

Wyświetl wszystkie dane na temat sklepów, mających nazwę żabka

SKLEPY WHERE NAZWA = 'ZABKA';

WOJEWODZTWO LIKE

dla wszystkich sklepów, które złożyły

SKLEPY, ZAMOWIENIA

ka oraz płacę dla tych

IMIE, NAZWISKO, PESEL, STANOWISKA2.NAZWA, STANOWISKA2.PLACA FROM

PLACA < (SELECT AVG(PLACA) FROM STANOWISKA2);

Wyświetl wszystkie dane na temat sklepów, mających nazwę żabka

Paweł Kopeć, Grupa 23A, nr indeksu 98002

f.

Wyświetl nazwę produktu, oraz jego typ
„Lakier do paznokci

SELECT

REPLACE(PRODUKTY.NAZWA, 'LAKIER', 'LAKIER DO PAZNOKCI')

TYPY.NAZWA AS TYP, CENA
TYPY.ID_TYPU;

g.

Wyświetl całkowity koszt dla

SELECT

ZAMOWIENIA.ID_ZAMOWIENIA, SUM(SZCZEGOLY_ZAMOWIENIA.ILOSC *

PRODUKTY.CENA)
PRODUKTY WHERE
SZCZEGOLY_ZAMOWIENIA.ID_ZAMOWIENIA group by zamowienia.id_zamowienia
by

id_zamowienia

Paweł Kopeć, Grupa 23A, nr indeksu 98002

6

yświetl nazwę produktu, oraz jego typ i cenę. Jeżeli nazwa jest „Lakier

Lakier do paznokci”

(PRODUKTY.NAZWA, 'LAKIER', 'LAKIER DO PAZNOKCI')

TYP, CENA FROM PRODUKTY, TYPY WHERE PRODUKTY.ID_TYPU =

Wyświetl całkowity koszt dla każdego zamówienia

ZAMOWIENIA.ID_ZAMOWIENIA, SUM(SZCZEGOLY_ZAMOWIENIA.ILOSC *

PRODUKTY.CENA) AS KOSZT FROM SZCZEGOLY_ZAMOWIENIA, ZAMOWIENIA,

WHERE

ZAMOWIENIA.ID_ZAMOWIENIA =

SZCZEGOLY_ZAMOWIENIA.ID_ZAMOWIENIA group by zamowienia.id_zamowienia

id_zamowienia

Lakier”, zamień ją na

(PRODUKTY.NAZWA, 'LAKIER', 'LAKIER DO PAZNOKCI') AS NAZWA,

PRODUKTY.ID_TYPU =

ZAMOWIENIA.ID_ZAMOWIENIA, SUM(SZCZEGOLY_ZAMOWIENIA.ILOSC *

AMOWIENIA, ZAMOWIENIA,

SZCZEGOLY_ZAMOWIENIA.ID_ZAMOWIENIA group by zamowienia.id_zamowienia order

Paweł Kopeć, Grupa 23A, nr indeksu 98002

h.

Wyświetl imiona i nazwiska wszystkich kobiet zatrudnionych w hurtowni. Imiona i
nazwiska mają zaczynać się z wielkiej litery.

SELECT

INITCAP(IMIE),

SUBSTR

(PESEL,10,1) IN (0,2,4,6,8);

i.

Wyświetl te zamówienia, które zalegają ponad 2

SELECT

* FROM ZAMOWIENIA

j.

Wyświetl wszystkich pracowników którzy
nazwisku „zapodajlo

SELECT

* FROM PRACOWNICY2

FROM

PRACOWNICY2

k.

Wyświetl województwa w których
województwa)

SELECT

DISTINCT IMIE, NAZWISKO, WOJEWODZTWO

ZAMOWIENIA WHERE
ZAMOWIENIA.ID_SKLEPU = SKLEPY.ID_SKLEPU;

Paweł Kopeć, Grupa 23A, nr indeksu 98002

7

Wyświetl imiona i nazwiska wszystkich kobiet zatrudnionych w hurtowni. Imiona i
nazwiska mają zaczynać się z wielkiej litery.

(IMIE), INITCAP(NAZWISKO) FROM PRACOWNICY2

(PESEL,10,1) IN (0,2,4,6,8);

Wyświetl te zamówienia, które zalegają ponad 2 miesiące

ZAMOWIENIA WHERE (SYSDATE - data_zamowienia) > 60;

Wyświetl wszystkich pracowników którzy są na tym samym stanowisku, co pracownik o

zapodajlo”

PRACOWNICY2 WHERE ID_STANOWISKA = (SELECT

PRACOWNICY2 WHERE NAZWISKO = 'ZAPODAJLO');

Wyświetl województwa w których sprzedają pracownicy (Imię, nazwisko i nazwę

IMIE, NAZWISKO, WOJEWODZTWO FROM PRACOWNICY2, SKLEPY,

WHERE

zamowienia.id_pracownika = pracownicy2.id_pracownika

ZAMOWIENIA.ID_SKLEPU = SKLEPY.ID_SKLEPU;

Wyświetl imiona i nazwiska wszystkich kobiet zatrudnionych w hurtowni. Imiona i

PRACOWNICY2 WHERE

data_zamowienia) > 60;

wisku, co pracownik o

SELECT

ID_STANOWISKA

pracownicy (Imię, nazwisko i nazwę

PRACOWNICY2, SKLEPY,

zamowienia.id_pracownika = pracownicy2.id_pracownika AND

Paweł Kopeć, Grupa 23A, nr indeksu 98002

l.

Wyświetl różnicę pomiędzy maksymalną i minimalną ceną jednego artykułu

SELECT

(MAX(CENA)

m.

Wyświetl nazwę sklepu, oraz ilość zamówień jakie złożył

SELECT

SKLEPY.NAZWA,

SKLEPY, ZAMOWIENIA
BY

SKLEPY.NAZWA;

n.

Wyświetl produkty
zamówione w co najmniej

SELECT

PRODUKTY.NAZWA, TYPY.NAZWA

szczegoly_zamowienia.ilosc
SZCZEGOLY_ZAMOWIENIA
produkty.id_produktu
> 9 AND SZCZEGOLY_ZAMOWIENIA.ILOSC >= 3;

Paweł Kopeć, Grupa 23A, nr indeksu 98002

8

Wyświetl różnicę pomiędzy maksymalną i minimalną ceną jednego artykułu

(CENA) - MIN(CENA)) AS ROZNICA FROM PRODUKTY;

nazwę sklepu, oraz ilość zamówień jakie złożył

SKLEPY.NAZWA, COUNT(ZAMOWIENIA.ID_ZAMOWIENIA) AS

SKLEPY, ZAMOWIENIA WHERE SKLEPY.ID_SKLEPU = ZAMOWIENIA.ID_SKLEPU

SKLEPY.NAZWA;

Wyświetl produkty(nazwę i typ) których cena jest większa od 9 oraz które były
zamówione w co najmniej 3 sztukach na jednej fakturze

PRODUKTY.NAZWA, TYPY.NAZWA AS TYP, produkty.cena AS

szczegoly_zamowienia.ilosc AS ILOSC FROM PRODUKTY, TYPY,
SZCZEGOLY_ZAMOWIENIA WHERE szczegoly_zamowienia.id_produktu =
produkty.id_produktu AND PRODUKTY.ID_TYPU = TYPY.ID_TYPU AND

SZCZEGOLY_ZAMOWIENIA.ILOSC >= 3;

Wyświetl różnicę pomiędzy maksymalną i minimalną ceną jednego artykułu

PRODUKTY;

AS

ILOSC FROM

SKLEPY.ID_SKLEPU = ZAMOWIENIA.ID_SKLEPU GROUP

az które były

AS

CENA,

mowienia.id_produktu =

AND

PRODUKTY.CENA

Paweł Kopeć, Grupa 23A, nr indeksu 98002

o.

Wyświetl podstawowe dane na temat struktury tabeli Klienci:

SELECT

TABLE_NAME, COLUMN_NAME, DATA_TYPE

WHERE

TABLE_NAME = 'KLIENCI';

p.

Wyświetl wszystkie komentarze ustawione do każdej z

SELECT

TABLE_NAME, COMMEN

IS NOT NULL;

q.

Wyświetl wszystkie produkty (Nazwę

SELECT

PRODUKTY.NAZWA

ROUND

((CENA/1.23),2)

PRODUKTY, TYPY WHERE

Paweł Kopeć, Grupa 23A, nr indeksu 98002

9

Wyświetl podstawowe dane na temat struktury tabeli Klienci:

TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS

TABLE_NAME = 'KLIENCI';

Wyświetl wszystkie komentarze ustawione do każdej z tabel

TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS

Wyświetl wszystkie produkty (Nazwę i cenę) oraz podział na cenę brutto, netto

PRODUKTY.NAZWA AS NAZWA, TYPY.NAZWA AS TYP, CENA

((CENA/1.23),2) AS CENA_NETTO, ROUND((CENA*0.23/1.23),2)

WHERE

produkty.id_typu=TYPY.ID_TYPU;

USER_TAB_COLUMNS

USER_TAB_COMMENTS WHERE COMMENTS

i cenę) oraz podział na cenę brutto, netto I vat.

TYP, CENA AS CENA_BRUTTO,

((CENA*0.23/1.23),2) AS VAT FROM

Paweł Kopeć, Grupa 23A, nr indeksu 98002

r.

Wyświetl datę następnego poniedziałku, po dacie zamówienia

SELECT

ID_zamowienia,

NASTEPNY_PONIEDZIALEK

s.

Przykładowy skrypt aktualizujący, podnoszący wszystkim pracownikom płacę o 15%.

UPDATE

STANOWISKA2

Zapytanie wykonane po wykonaniu aktualizacji:

Paweł Kopeć, Grupa 23A, nr indeksu 98002

10

Wyświetl datę następnego poniedziałku, po dacie zamówienia

ID_zamowienia, NEXT_DAY(DATA_ZAMOWIENIA,1) AS

NASTEPNY_PONIEDZIALEK FROM ZAMOWIENIA;

Przykładowy skrypt aktualizujący, podnoszący wszystkim pracownikom płacę o 15%.

STANOWISKA2 SET PLACA = PLACA*1.10;

Zapytanie wykonane po wykonaniu aktualizacji:

Przykładowy skrypt aktualizujący, podnoszący wszystkim pracownikom płacę o 15%.