MySql - zadania

Technologia MySql, zestaw zadań na zaliczenie.

Zadanie 1

I) Konfiguracja środowiska do pracy: Online lub Offline
OFFLINE (Preferowana):
https://www.apachefriends.org/pl/index.html
Instalujemy oprogramowanie które umożliwia pracę z relacyjną bazą danych.


ONLINE:
Zakładamy konto na stronie https://db4free.net/

W celu rejestracji należy potwierdzić swój adres e-mail klikając w link w otrzymanej wiadomości e-mail (Uwaga: Otrzymane wiadomości mogą znaleźć się w spamie).

db4free.net jest serwisem przeznaczonym dla programistów i testerów. Mamy możliwość korzystania z najnowszej wersji aplikacji mySQL. Niestety serwis nie gwarantuje że bazy danych nie zostaną usunięte. Możliwe jest również, że administratorzy usuną konta użytkowników. Dlatego zaleca się export stworzonych projektów do plików i przechowanie kopii na własnym komputerze.
UWAGA!
Istnieje więcej alternatyw dla pracy w bazie danych mySQL:
Inne darmowe, dostępne strony: https://www.freemysqlhosting.net/ ; /www.remotemysql.com ;

II) Logujemy się do phpMyAdmin : https://db4free.net/phpMyAdmin/

Jest to darmowa aplikacja webowa napisana w PHP, wykorzystywana do administracji mySQL.

III) Zadanie :
Należy stworzyć bazę danych opisaną w poniższych krokach. Korzystamy tylko i wyłącznie z komend SQL.
(!) W celu lepszego poznania i zrozumienia polecam i zachęcam rozwiązać poniższe zadanie sposobem "klikanym" i przeanalizować wszystkie komendy.
Następnie wykonać to zadanie ponownie korzystając już tylko i wyłącznie z zakładki "SQL" i poleceń.

Rozwiązanie:
Rozwiązaniem zadania jest jeden plik zawierający listę komend SQL. Po wklejeniu zawartości tego pliku do zakładki "SQL" i kliknięciu wykonaj otrzymamy rozwiązanie na wszystkie poniższe kroki. Tak przygotowany plik tekstowy należy zamieścić na platformie. Plik ten stanowi rozwiązanie zadania numer 1 i będzie podlegał ocenie.

IV) Projekt bazy danych do wykonania:

1. Tworzymy tabele "Pracownicy" zawierającą listę pracowników pewnej firmy, Tabela ta zawiera dane pracownikow imie i nazwisko oraz oraz numer pokoju.
Tabela ta składa się z 4 kolumn : IDpracownika, Imie, Nazwisko oraz Pokoj.
IDpracownika jest kluczem głównym tabeli (PRIMARY KEY) oraz jest automatycznie inkrementowany przez SQL (AUTO_INCREMENT).
Dodatkowo Imię oraz Nazwisko jest typu tekstowego, natomiast IDpracownika oraz Pokoj są typu liczbowego.

2. Tworzymy tabele "Telefony" zawierającą listę numerów telefonów każdym z pokoi. Tabela ta zawiera dwie kolumny: Pokoj, oraz Numer. Pokoj zapisujemy jako liczbe natomiast Numer jest polem tekstowym.
Dodatkowo należy zapewnić unikalność kolumny Numer podając odpowiedni argument dla kolumny przy tworzeniu tabeli.

3. Dodajemy pracownikow do tabeli z pracownikami (Uwaga. IDpracownika powinno byc automatycznie ustawiane przez baze danych, nie musimy recznie wpisywac wartosci tego pola)

IDPrac Imie Nazwisko Pokoj
1 Jan Kowalski 200
2 Andrzej Martwy 201
3 Grzegorz Nowak 202
4 Jan Pielucha 307

4. Dodajemy numery telefonow i numer pokoju w ktorym sie znajduja do drugiej tabeli

Pokoj Numer
200 123-123-123
202 666-555-666


5. Wykorzystując instrukcję SELECT sformułuj zapytanie które wyświetli tabelę zawierającą 4 kolumny:
Imie Nazwisko Pokoj Numer


Przy poprawnym wykonaniu zadania otrzymamy listę dwóch pracowników posiadających telefon w biurze.

Nazwisko Imie Pokoj Numer
Kowalski Jan 200 123-123-123
Nowak Grzegorz 202 666-555-666

------------------------------------------------------------------------------------------------------------------

Zadanie 2

Proszę zaproponować model encyjno-relacyjny (model związków encji) dla poniżej zaprezentowanych tabel. Rysunek może być wykonany dowolnie (odręcznie / cyfrowo). (Ciekawe narzędzie do tworzenia różnych schematów : https://www.draw.io/).

Uwaga. Aby poniższy kod wykonał się w narzędziu z którego korzystamy (db4free.net). Należy odpowiednio wstawić nazwę Państwa bazy danych zamiast wpisanego poniżej "TWOJA_BAZA_DANYCH"

CREATE TABLE `TWOJA_BAZA_DANYCH`.`ID_ubezpieczyciel` (

`idlicencja` INT NOT NULL AUTO_INCREMENT,
`nazwa` TEXT NOT NULL,
`adres` TEXT NOT NULL ,
PRIMARY KEY (idlicencja)
);

CREATE TABLE `TWOJA_BAZA_DANYCH`.`ID_klient` (
`pesel` BIGINT (11) UNSIGNED NOT NULL,
`imie` TEXT NOT NULL,
`nazwisko` TEXT NOT NULL ,
`adres` TEXT NOT NULL ,
`plec` TEXT NOT NULL ,
`wiek` INT NOT NULL ,
PRIMARY KEY (pesel)
);

CREATE TABLE `TWOJA_BAZA_DANYCH`.`ID_ubezpieczenie` (
`idubezpiczenia` INT NOT NULL AUTO_INCREMENT,
`nazwaUbezpieczenia` TEXT NOT NULL,
`wartosc` INT NOT NULL,
PRIMARY KEY (idubezpiczenia)
);

CREATE TABLE `TWOJA_BAZA_DANYCH`.`ID_polisa` (
`idpolisa` INT NOT NULL AUTO_INCREMENT,
`pesel` BIGINT(11) UNSIGNED NOT NULL,
`idlicencja` INT NOT NULL,
`idubezpiczenia` INT NOT NULL,
`sumaubezpieczenia` INT NOT NULL,
PRIMARY KEY (idpolisa)
);

OCENA: Max 100 punktów. Zaliczenie od 80 punktów

--------------------------------------------------------------------------------------------------------------------

Zadanie 3

Do stworzonych w zadaniu 2 tablic proszę o wstawienie poniższych danych:

INSERT INTO `ID_ubezpieczyciel` (`idlicencja`, `nazwa`, `adres`) VALUES

(NULL, 'PZU', 'Warszawa'),
(NULL, 'UNIQA', 'Łódź'),
(NULL, 'AVIVA', 'Katowice');

INSERT INTO `ID_klient` (`pesel`, `imie`, `nazwisko`, `adres`, `plec`, `wiek`) VALUES
('47021302554', 'Andrzej', 'Opolczyk', 'Włókiennicza 12', 'Mezczyzna', '71'),
('64011307894', 'Łukasz', 'Pawlak', 'Zgierska 13', 'Mezczyzna', '54'),
('53011478456', 'Helena', 'Galazka', 'Rzgowska 45', 'Kobieta', '65'),
('92012175987', 'Katarzyna', 'Zdunek', 'Piotrkowska 12', 'Kobieta', '26');

INSERT INTO `ID_ubezpieczenie`(`idubezpiczenia` , `nazwaUbezpieczenia`, `wartosc`) VALUES
(NULL,'Na zycie','3'),
(NULL,'OC samochod','5'),
(NULL,'Wycieczka','15');

INSERT INTO `ID_polisa` (`idpolisa` , `pesel`, `idlicencja`, `idubezpiczenia`, `sumaubezpieczenia`) VALUES (NULL,'47021302554','1','1','2000'),
(NULL,'47021302554','2','2','2100'),
(NULL,'53011478456','3','1','1200'),
(NULL,'92012175987','1','3','3100');

Podaj przykład zapytania korzystając z funkcji SELECT, tak aby rezultatem zapytania była:

1) lista wszystkich ubezpieczonych w wieku powyżej 50 lat - zrobione

2) Średnia wieku wszystkich ubezpieczonych (Funkcja: AVG)

3)Wiek najstarszego ubezpieczonego oraz jego imię i nazwisko

4) Poniższa tabela:
idpolisa sumaubezpieczenia/(wartosc*12)
1 55.5556
2 35.0000
3 33.3333
4 17.2222

UWAGA. W tym wypadku obliczamy wartość zwróconą przez zapytanie. Może to być miesięczna składka ubezpieczenia obliczona formułą:

SumaUbezpieczenia / (Wartość * 12 miesiecy)

OCENA: Max 100 punktów (25 punktów za każdy podpunkt). Zaliczenie od 75 punktów

-------------------------------------------------------------------------------------------------------------------

Zadanie 4
Zadanie 4
1. Stwórz dwie tabele według przesłanego obrazu. Podaj polecenie SQL konieczne do utworzenia tabeli i wypełnienia ich danymi

2. Wyświetl iloczyn kartezjański dla obu tabel

3. Napisz zapytanie wyświetlające tabelę o dwóch kolumnach : tytuł oraz imię

Tytuł jest tytułem Bajki, natomiast imie jest odpowiednią nazwą postaci.
Wyświetlane sa tylko tytuły bajek, dla których istnieje postać
Dodatkowo wyswietlona tabela powinna być wyswietlona w porzadku alfabetycznym dla kolumny tytul.
Skorzystaj z JOIN, ORDER BY

4. Napisz zapytanie wyświetlające tabelę skłądającą się z kolumn :

Bajka.tytul, Postac.imie
Gdzie wszystkie elementy z tabela bajka sa wyswietlone wraz z odpowiednimi postaciami. Bajki dla ktorych nie zdefiniowano postaci wyswietlaja sie z wartosciami NULL. (korzystamy z LEFT JOIN)


5. Napisz zapytanie wyświetlające tabelę skłądającą się z kolumn :

Bajka.tytul, Postac.imie,
Gdzie wszystkie elementy z tabela postac sa wyswietlone wraz z odpowiednimi tytłami bajek. Postaci dla ktorych nie zdefiniowano tytułu bajki wyswietlaja sie z wartosciami NULL. (korzystamy z RIGHT JOIN)
6. Podaj polecenie SQL które pozwala na zmianę imienia postaci.(UPDATE)

7. Podaj polecenie SQL które pozwala na zmianę atrybutów kolumny (zmień nazwę kolumny "imie" na "Imie").(ALTER)

8. Podaj polecenie SQL które usuwa tablice Bajka oraz Postac (DROP)

9. Podaj zapytanie SQL które wyświetli tabelę skłądającą się z dwóch kolumn: Bajka.tytul oraz kolumny zawierającej liczbę wszystkich postaci dla tej bajki (GROUP BY)

Zapraszam osoby zainteresowane na mail

• Pobierz folder
• Zachomikuj folder