Bazy danych

create database TanieButy;
use TanieButy;

create table pracownicy (
id_pracownika int AUTO_INCREMENT PRIMARY KEY,
imie varchar(30),
nazwisko varchar(30),
aktywny bool
);

insert into pracownicy (id_pracownika,imie,nazwisko,aktywny) values
(1, 'Lucjan', 'Kolebka', false),
(2, 'Marcin', 'Warzocha', true),
(3, 'Konstanty', 'Stankiewicz', false),
(4, 'Gracjan', 'Roztocki', true),
(5, 'Fiodor', 'Mazur', true),
(6, 'Pawel', 'Glaz', false);

create table klienci (
id_klient int not null,
login varchar(50) not null,
haslo varchar(50) not null,
adres varchar (200),
telefon varchar(9),
ostatnie_logowanie datetime(0),
primary key(id_klient)
);

insert into klienci (id_klient, login, haslo, adres, telefon, ostatnie_logowanie) values
(1,'ania22','qwerty','Lublin','513724517','2019-11-21 21:23:12'),
(2,'lolita23','qwerty','Lublin','513724517','2019-11-21 22:23:12'),
(3,'krzysztof99','qwerty','Lublin','513724517','2019-11-21 21:23:12'),
(4,'jacek233','qwerty','Warszawa','513724517','2016-11-21 21:23:12'),
(5,'pawel65','qwerty','Lublin','513724517','2019-11-21 21:23:12'),
(6,'robert44','qwerty','Lublin','513724517','2019-02-21 21:23:12');

create table produkt (
id_produktu int AUTO_INCREMENT,
nazwa_produktu varchar(200),
numer_katalogowy int,
ilosc_sztuk int,
cena dec(6,2),
primary key(id_produktu)
);

insert into produkt(id_produktu,nazwa_produktu,numer_katalogowy,ilosc_sztuk,cena) values
(1,'Nike',3221,4,211.99),
(2,'Reebok',3232,12,400.00),
(3,'Puma',2312,22,399.99),
(4,'New Balance',4323,0,299.99),
(5,'Jordan',7765,2,549.99),
(6,'Kappa',3422,50,99.99),
(7,'Lacoste',4533,25,49.99),
(8,'Yezzy',3234,1,499.99),
(9,'Zara',8859,130,130.00),
(10,'Kubota',6434,0,129.99)
;

create table zamowienie (
id_zamowienia int auto_increment,
id_klient int,
id_produktu int,
data_kupna date,
id_pracownika int,
primary key(id_zamowienia),
foreign key(id_klient) references klienci(id_klient),
foreign key(id_produktu) references produkt(id_produktu),
foreign key(id_pracownika) references pracownicy(id_pracownika)
);

insert into zamowienie(id_zamowienia,id_klient,id_produktu,data_kupna,id_pracownika) values
(16,3,2,'2015-02-19',1),
(2,5,6,'2019-12-01',2),
(3,2,9,'2018-11-21',4),
(4,1,1,'2019-04-13',4),
(5,2,4,'2019-06-16',5),
(6,4,3,'2019-02-18',3),
(7,4,10,'2019-12-03',1),
(8,5,2,'2019-09-06',2),
(9,1,5,'2019-05-06',4),
(10,4,4,'2019-03-01',5),
(11,3,1,'2019-11-19',1),
(15,3,3,'2019-09-22',1),
(13,5,5,'2019-10-25',5),
(14,6,1,'2018-12-19',5);


create table reklamacje (
id_reklamacji int AUTO_INCREMENT PRIMARY KEY,
id_zamowienia int REFERENCES zamowienie(id_zamowienia)
ON UPDATE CASCADE ON DELETE NO ACTION,
powod varchar(255),
data_zgloszenia date,
data_realizacji date,
wynik bool);

insert into reklamacje(id_reklamacji,id_zamowienia,powod,data_zgloszenia,data_realizacji,wynik) VALUES
(1,4,'Wada fabryczna','2019-04-24','2019-05-02',true),
(2,5,'Pekniete szwy','2019-10-11','2019-10-23',false),
(3,6,'Brak logo','2019-02-19','2019-02-25',true),
(4,9,'Rozpuszczony klej','2019-06-12','2019-06-20',false),
(5,10,'Brak uszkodzen','2019-04-01','2019-04-13',false),
(6,16,'Obtarcia','2016-02-04','2019-02-12',false);



-- 1.Wyświetl login najstarszego klienta, produkt, który zakupił oraz datę zakupu.
SELECT login, nazwa_produktu, MIN(data_kupna) as 'Data zakupu'
FROM klienci
JOIN zamowienie USING (id_klient)
JOIN produkt USING (id_produktu);

-- 2.Wyświetl łączną kwotę wydaną przez poszczeglnych klientów
SELECT login, SUM(cena) as 'Wydano lacznie' FROM klienci
JOIN zamowienie USING (id_klient)
JOIN produkt USING (id_produktu)
GROUP BY login
ORDER BY SUM(cena) DESC;

-- 3.Napisz zapytanie które wyświetli loginy osób (bez duplikatów), które dokonały zamówień u aktualnie dostępnych pracowników
SELECT login FROM klienci
JOIN zamowienie USING (id_klient)
JOIN pracownicy USING (id_pracownika)
WHERE pracownicy.aktywny='true'
GROUP BY login;

-- 4.Napisz zapytanie, które wyświetli czas do końca gwarancji poszczególnych produktów każdego urzytkownika

SELECT login,nazwa_produktu, datediff(date_add(data_kupna, interval 2 year), CURDATE()) AS 'Dni do końca gwarancji'
FROM klienci
JOIN zamowienie USING (id_klient)
JOIN produkt USING (id_produktu);

-- 5.Napisz zapytanie, które wyświetli produkty, których ilość jest mniejsza niż 5.

SELECT nazwa_produktu, ilosc_sztuk FROM produkt
WHERE ilosc_sztuk<5;

-- 6.Napisz zapytanie, które wyświetli liczbę zamówień zrealizowanych przez klientposzczególnych klientów

SELECT login, COUNT(id_zamowienia) AS 'Ilosc zamowien' FROM klienci
JOIN zamowienie USING (id_klient)
GROUP BY login
HAVING COUNT(id_zamowienia)>1;

-- 7.Napisz zapytanie, które wyświetli klientów, którzy kupili produkt nike przed 11.11.2019.

SELECT login, nazwa_produktu FROM klienci
JOIN zamowienie USING (id_klient)
JOIN produkt USING (id_produktu)
WHERE nazwa_produktu='Nike' AND data_kupna<'2019-11-11';

-- 8.Dodaj kolumnę email do tabeli klienci.

ALTER TABLE klienci
ADD email varchar(30);

-- 9.Napisz zapytanie, które wyświetli reklamacje przyjete między 06.06.2019 a 06.10.2019

SELECT * from reklamacje
WHERE data_zgloszenia between '2019-06-06' and '2019-10-06';

-- 10.Napisz zapytanie, które wyświetli średnią kwotę wydawaną przez klientów

SELECT avg(cena) AS 'Srednia wydana kwota' from produkt
JOIN zamowienie USING (id_produktu);

-- 11.Napisz zapytanie, które zwróci ciąg: <login> kupil <nazwa_produktu> <data_kupna>.

SELECT CONCAT(login,' kupil ',nazwa_produktu,' ',data_kupna) AS ''
FROM klienci
JOIN zamowienie USING (id_klient)
JOIN produkt USING (id_produktu);

-- 12.Napisz zapytanie, które wyświetli liczbę zgłazanych reklamacji przez każdego klienta.

SELECT login, count(id_reklamacji) AS 'Liczba reklamacji' from klienci
JOIN zamowienie USING (id_klient)
JOIN reklamacje USING (id_zamowienia)
GROUP BY id_reklamacji HAVING COUNT(id_reklamacji)
;