Projekt zaliczeniowy Sprawozdanie

Sprawozdanie

  1. Założenia:

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

    2. Wszystkie operacje tworzące bazę danych, jak i jej późniejszą obsługę wykonane zostaną w programie SQL developer dostarczonym przez firmę Oracle.

    3. Język pisania bazy danych to Oracle SQL

  2. Tworzenie tabel:

    1. Tabela Typy:

CREATE TABLE TYPY(

ID_TYPU NUMBER(4) CONSTRAINT PK_ID_TYPU PRIMARY KEY,

NAZWA VARCHAR(15)

);

  1. 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)

);

  1. 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

);

  1. Tabela Stanowiska2:

CREATE TABLE STANOWISKA2(

ID_STANOWISKA NUMBER(4) CONSTRAINT PK_ID_STANOWISKA PRIMARY KEY,

NAZWA VARCHAR(15),

PLACA NUMBER(5) DEFAULT 1200

);

  1. 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)

);

  1. Tabela Zamowienia:

CREATE TABLE ZAMOWIENIA(

ID_ZAMOWIENIA NUMBER(4) CONSTRAINT PK_ID_ZAMOWIENIA PRIMARY KEY,

DATA_ZAMOWIENIA DATE NOT NULL,

ID_PRACOWNIKA NUMBER(4) CONSTRAINT FK_ID_PRACOWNIKA REFERENCES PRACOWNICY2(ID_PRACOWNIKA),

ID_SKLEPU NUMBER(4) CONSTRAINT FK_ID_SKLEPU REFERENCES SKLEPY(ID_SKLEPU)

);

  1. Tabela Szczegoly_zamowienia:

CREATE TABLE SZCZEGOLY_ZAMOWIENIA(

ID_SZCZEGOLU NUMBER(4) CONSTRAINT PK_ID_SZCZEGOLU PRIMARY KEY,

ID_PRODUKTU NUMBER(4) CONSTRAINT FK_ID_PRODUKTU REFERENCES PRODUKTY(ID_PRODUKTU),

ILOSC NUMBER(3),

CENA NUMBER(5)

);

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

  1. 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);

  1. 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:

    1. Dodanie pola Wojewodztwo w tabeli Sklepy:

ALTER TABLE SKLEPY

ADD WOJEWODZTWO VARCHAR(20)

  1. Pole Cena w tabeli Produkty:

ALTER TABLE PRODUKTY

MODIFY CENA NUMBER(5,2) NOT NULL

  1. Pole Pesel w tabeli Pracownicy2:

ALTER TABLE PRACOWNICY2

MODIFY PESEL VARCHAR(11) NOT NULL

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

COMMENT ON TABLE ZAMOWIENIA IS 'WYKAZ FAKTUR';

COMMENT ON TABLE PRACOWNICY2 IS 'WYKAZ PRACOWNIKOW ZATRUDNIONYCH W HURTOWNI';

COMMENT ON COLUMN SZCZEGOLY_ZAMOWIENIA.ID_PRODUKTU IS 'NUMER ID PRODUKTU JAKI JEST NA FAKTURZE'

COMMENT ON COLUMN SZCZEGOLY_ZAMOWIENIA.ILOSC IS 'ILOSC PRODUKTOW NA FAKTURZE';

COMMENT ON COLUMN SZCZEGOLY_ZAMOWIENIA.ID_ZAMOWIENIA IS 'NUMER FAKTURY';

  1. Wykonane Selecty:

    1. Wyświetl wszystkie dane z tabeli Produkty

SELECT * FROM PRODUKTY;

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

SELECT NAZWA, NIP, MIASTO FROM SKLEPY WHERE WOJEWODZTWO LIKE 'MALOPOLSKIE';

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

SELECT DISTINCT NAZWA, NIP, MIASTO, WOJEWODZTWO FROM SKLEPY, ZAMOWIENIA WHERE zamowienia.id_sklepu = sklepy.id_sklepu;

  1. Wyświetl imię, nazwisko, pesel pracowników, nazwę stanowiska oraz płacę dla tych którzy zarabiają mniej niż średnia płaca w firmie

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

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

SELECT * FROM SKLEPY WHERE NAZWA = 'ZABKA';

  1. Wyświetl nazwę produktu, oraz jego typ i cenę. Jeżeli nazwa jest „Lakier”, zamień ją na „Lakier do paznokci”

SELECT REPLACE(PRODUKTY.NAZWA, 'LAKIER', 'LAKIER DO PAZNOKCI') AS NAZWA, TYPY.NAZWA AS TYP, CENA FROM PRODUKTY, TYPY WHERE PRODUKTY.ID_TYPU = TYPY.ID_TYPU;

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

SELECT ZAMOWIENIA.ID_ZAMOWIENIA, SUM(SZCZEGOLY_ZAMOWIENIA.ILOSC * PRODUKTY.CENA) AS KOSZT FROM SZCZEGOLY_ZAMOWIENIA, ZAMOWIENIA, PRODUKTY WHERE ZAMOWIENIA.ID_ZAMOWIENIA = SZCZEGOLY_ZAMOWIENIA.ID_ZAMOWIENIA group by zamowienia.id_zamowienia order by id_zamowienia

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

SELECT INITCAP(IMIE), INITCAP(NAZWISKO) FROM PRACOWNICY2 WHERE SUBSTR(PESEL,10,1) IN (0,2,4,6,8);

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

SELECT * FROM ZAMOWIENIA WHERE (SYSDATE - data_zamowienia) > 60;

  1. Wyświetl wszystkich pracowników którzy są na tym samym stanowisku, co pracownik o nazwisku „zapodajlo”

SELECT * FROM PRACOWNICY2 WHERE ID_STANOWISKA = (SELECT ID_STANOWISKA FROM PRACOWNICY2 WHERE NAZWISKO = 'ZAPODAJLO');

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

SELECT DISTINCT IMIE, NAZWISKO, WOJEWODZTWO FROM PRACOWNICY2, SKLEPY, ZAMOWIENIA WHERE zamowienia.id_pracownika = pracownicy2.id_pracownika AND ZAMOWIENIA.ID_SKLEPU = SKLEPY.ID_SKLEPU;

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

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

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

SELECT SKLEPY.NAZWA, COUNT(ZAMOWIENIA.ID_ZAMOWIENIA) AS ILOSC FROM SKLEPY, ZAMOWIENIA WHERE SKLEPY.ID_SKLEPU = ZAMOWIENIA.ID_SKLEPU GROUP BY SKLEPY.NAZWA;

  1. 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

SELECT PRODUKTY.NAZWA, TYPY.NAZWA AS TYP, produkty.cena AS CENA, 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 PRODUKTY.CENA > 9 AND SZCZEGOLY_ZAMOWIENIA.ILOSC >= 3;

  1. Wyświetl podstawowe dane na temat struktury tabeli Klienci:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'KLIENCI';

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

SELECT TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE COMMENTS IS NOT NULL;

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

SELECT PRODUKTY.NAZWA AS NAZWA, TYPY.NAZWA AS TYP, CENA AS CENA_BRUTTO, ROUND((CENA/1.23),2) AS CENA_NETTO, ROUND((CENA*0.23/1.23),2) AS VAT FROM PRODUKTY, TYPY WHERE produkty.id_typu=TYPY.ID_TYPU;

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

SELECT ID_zamowienia, NEXT_DAY(DATA_ZAMOWIENIA,1) AS NASTEPNY_PONIEDZIALEK FROM ZAMOWIENIA;

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

UPDATE STANOWISKA2 SET PLACA = PLACA*1.10;

Zapytanie wykonane po wykonaniu aktualizacji:


Wyszukiwarka