zad. 3

INSERT INTO sklep.producenci
VALUES ('1', 'TIMEX', 'timex@gmail.com', '509847594'),
('2', 'TIBIS', 'tibis@gmail.com', '854854689'),
('3', 'GROMEX', 'gromex@gmail.com', '256985453'),
('4', 'JORDAN', 'jordan@gmail.com', '512468597'),
('5', 'PASTOL', 'pastol@gmail.com', '586521354'),
('6', 'GUCCI', 'gucci@gmail.com', '654235652'),
('7', 'MIGOS', 'migos@gmail.com', '685245958'),
('8', 'MACHINE', 'machine@gmail.com', '698523654'),
('9', 'FLORIA', 'floria@gmail.com', '754256896'),
('10', 'MAGICALA', 'magicala@gmail.com', '586523654')

INSERT INTO sklep.produkty
VALUES ('1', 'Zegarek', 50.40, '1'),
('2', 'Burger wołowy', 12.50, '2'),
('3', 'Blachotrapez', 8.40, '3'),
('4', 'Piłka', 100.30, '4'),
('5', 'Chleb razowy', 2.70, '5'),
('6', 'Plecak', 250, '6'),
('7', 'Dres sportowy', 400, '7'),
('8', 'Kawa rozpuszczalna', 15.50, '8'),
('9', 'Panel podłogowy', 80, '9'),
('10', 'Krzesło', 100, '10')

INSERT INTO sklep.zamowienia
VALUES ('1', '1', '1', 1),
('2', '2', '2', 3),
('3', '3', '3', 2),
('4', '4', '4', 1),
('5', '5', '4', 3),
('6', '6', '4', 5),
('7', '7', '7', 1),
('8', '8', '2', 4),
('9', '9', '2', 3),
('10', '10', '10', 1),
('11', '1', '1', 1),
('12', '2', '2', 2),
('13', '3', '3', 3),
('14', '4', '4', 2),
('15', '5', '5', 2),
('16', '6', '6', 2),
('17', '7', '7', 2),
('18', '8', '8', 2),
('19', '9', '9', 1),
('20', '10', '10', 3)

zad. 7-9
select pg_terminate_backend(pid) from pg_stat_activity where datname='s287060';

drop database s287060

alter database backup_287060
rename to s287060

zad. 10

a)
select pi.nazwa_producenta producent, sum(zam.ilosc_produktow) liczba_zamowien, sum(zam.ilosc_produktow)*py.cena wartosc_zamowienia from sklep.producenci pi
join sklep.zamowienia zam on zam.id_producenta = pi.id_producenta
join sklep.produkty py on zam.id_produktu = py.id_produktu
group by pi.nazwa_producenta, py.cena
b)
select py.nazwa_produktu produkt, count(zam.id_produktu) from sklep.produkty py
join sklep.zamowienia zam on zam.id_producenta = py.id_producenta
group by py.nazwa_produktu
c)
select * from sklep.produkty
natural join sklep.zamowienia
d)
alter table sklep.zamowienia
add column data date

update sklep.zamowienia
set data = '2018-09-20'
where id_zamowienia > 0

update sklep.zamowienia
set data = '2018-09-28'
where id_zamowienia > 10

update sklep.zamowienia
set data = '2018-01-20'
where id_zamowienia > 15
e)
select * from sklep.zamowienia where DATE_PART('month', data) = 1
f)
select * from
(
select to_char(data, 'day') as dzien_tygodnia, count(*) as ilosc_zamowien
from sklep.zamowienia
group by to_char(data, 'day')
)z
order by z.ilosc_zamowien desc
g)
select * from
(
select p.id_produktu, p.nazwa_produktu, count(*) as ilosc_zamowionych from sklep.zamowienia z
join sklep.produkty p on z.id_produktu = p.id_produktu
group by p.id_produktu, p.nazwa_produktu
)z
order by z.ilosc_zamowionych desc

zad. 11

a)
select concat('Produkt ', p.nazwa_produktu, ', którego producentem jest ', pr.nazwa_producenta,
', zamówiono ', count(z.*),' razy') as opis
from sklep.zamowienia z
join sklep.produkty p on p.id_produktu = z.id_produktu
join sklep.producenci pr on pr.id_producenta = z.id_producenta
group by p.nazwa_produktu, pr.nazwa_producenta
order by count(z.*) desc

b)
select * from (select zam.id_zamowienia, py.cena*sum(zam.ilosc_produktow) cena from sklep.zamowienia zam
join sklep.produkty py on zam.id_produktu = py.id_produktu
group by zam.id_zamowienia, py.cena
order by cena
offset 3) q
order by cena desc

c)
create table sklep.klienci
(id_klienta bigint PRIMARY KEY, email varchar, telefon varchar)

alter table sklep.zamowienia
add column id_klienta bigint

alter table sklep.zamowienia
add foreign key (id_klienta)
references sklep.klienci(id_klienta)
d)

insert into sklep.klienci values
('1', 'dabrowski@gmail.com', '758745698'),
('2', 'stojanowski@gmail.com', '645879658'),
('3', 'szymierski@gmail.com', '548595687'),
('4', 'dabek@gmail.com', '548521365'),
('5', 'naklicki@gmail.com', '568598745'),
('6', 'janowski@gmail.com', '654858959'),
('7', 'wojciechowski@gmail.com', '652357458'),
('8', 'rozsadny@gmail.com', '546859532'),
('9', 'kijek@gmail.com', '655875452'),
('10', 'debowski@gmail.com', '545896587')

e)
select k.*, p.nazwa_produktu, count(z.*) as ilość_sztuk, sum(p.cena) as wartość_zamówienia from sklep.zamowienia z
natural join sklep.klienci k
join sklep.produkty p on p.id_produktu = z.id_produktu
join sklep.producenci pr on pr.id_producenta = z.id_producenta
group by k.id_klienta, k.email, k.telefon, p.nazwa_produktu

f)
select
case when q.ilość_zamówień =
(
select count(z.*) as ilość_zamówień from sklep.zamowienia z
natural join sklep.klienci k
group by k.id_klienta
order by 1 asc
limit 1
)
then 'NAJRZADZIEJ ZAMAWIAJĄCY'
when q.ilość_zamówień =
(
select count(z.*) as ilość_zamówień from sklep.zamowienia z
natural join sklep.klienci k
group by k.id_klienta
order by 1 desc
limit 1
)
then 'NAJCZĘŚCIEJ ZAMAWIAJĄCY'
else null end as info,
* from
(
select k.*, count(z.*) as ilość_zamówień from sklep.zamowienia z
natural join sklep.klienci k
group by k.id_klienta, k.email, k.telefon
)q
group by q.id_klienta, q.email, q.telefon, q.ilość_zamówień

g)
delete from sklep.produkty
where id_produktu in (
select id_produktu from sklep.produkty p
where id_produktu not in (
select distinct z.id_produktu from sklep.zamowienia z
left join sklep.produkty p on p.id_produktu = z.id_produktu
))

12.
a)
CREATE TABLE sklep.numer (liczba int CONSTRAINT liczba_CHK CHECK (liczba < 10000))
b)
CREATE SEQUENCE liczba_seq START 100 minvalue 0 maxvalue 125 increment by 5 CYCLE
c)
INSERT INTO sklep.numer VALUES
(nextval('liczba_seq')),
(nextval('liczba_seq')),
(nextval('liczba_seq')),
(nextval('liczba_seq')),
(nextval('liczba_seq')),
(nextval('liczba_seq')),
(nextval('liczba_seq'))
d)
ALTER SEQUENCE liczba_seq increment by 6
e)
SELECT last_value FROM liczba_seq;
f)
SELECT last_value, last_value + increment_by FROM liczba_seq;
g)
DROP SEQUENCE liczba_seq
d)

13.
a)
SELECT u.usename AS "Role name",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
b)
create user Superuser287060 SUPERUSER

create user guest287060
grant select on all tables in schema public, firma, sklep to guest287060
c)
alter user Superuser287060
rename to student287060
REVOKE ALL PRIVILEGES ON DATABASE s287060 FROM student287060
grant select on all tables in schema public, firma, sklep to student287060

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public, firma, sklep FROM guest287060
DROP USER guest287060

14.
a)
BEGIN;
UPDATE sklep.produkty set cena = cena + 10;
COMMIT;
b)
BEGIN;
UPDATE sklep.produkty set cena = cena + 0.1*cena where id_produktu = 3;
SAVEPOINT S1;
UPDATE sklep.produkty set cena = cena*1.25;
SAVEPOINT S2;
delete from sklep.klienci where id_klienta =
(select id_klienta from sklep.zamowienia z
natural join sklep.klienci k
group by id_klienta
order by count(z.*) desc
limit 1);
ROLLBACK TO S1;
ROLLBACK TO S2;
ROLLBACK;