DS - cv2

CREATE TABLE Klient
(
  r_cislo  CHAR(11) NOT NULL,
  jmeno VARCHAR(20) NOT NULL,
  ulice VARCHAR(20),
  mesto VARCHAR(20),
  PRIMARY KEY (r_cislo)
);
CREATE UNIQUE INDEX Iklient ON  Klient (r_cislo);

CREATE TABLE Pobocka
(
  nazev VARCHAR(20) NOT NULL,
  jmeni INTEGER,
  PRIMARY KEY (nazev)
);
CREATE UNIQUE INDEX Ipobocka ON Pobocka (nazev);

CREATE TABLE Ucet
(
  c_uctu  INTEGER NOT NULL,
  stav DECIMAL(10,2),
  r_cislo CHAR(11) NOT NULL,
  pobocka VARCHAR(20),
  PRIMARY KEY (c_uctu),
  FOREIGN KEY (r_cislo) REFERENCES Klient ON DELETE CASCADE,
  FOREIGN KEY (pobocka) REFERENCES Pobocka
);
CREATE UNIQUE INDEX Iucet ON  Ucet (c_uctu);

CREATE TABLE Transakce
(
  c_uctu  INTEGER NOT NULL,
  c_transakce INTEGER NOT NULL,
  datum DATETIME,
  castka DECIMAL(7,2),
  PRIMARY KEY (c_uctu, c_transakce),
  FOREIGN KEY (c_uctu) REFERENCES Ucet ON DELETE CASCADE
);
CREATE UNIQUE INDEX Itransakce ON Transakce (c_uctu, c_transakce);

INSERT INTO Klient
VALUES('440726/0672','Jan Novák','Cejl 8','Brno');
INSERT INTO Klient
VALUES('530610/4532','Petr Veselý','Podzimní 28','Brno');
INSERT INTO Klient
VALUES('601001/2218','Ivan Zeman ','Cejl 8','Brno');
INSERT INTO Klient
VALUES('510230/048','Pavel Tomek','Tomkova 34','Brno');
INSERT INTO Klient
VALUES('580807/9638','Josef Mádr','Svatoplukova 15','Brno');
INSERT INTO Klient
VALUES('625622/6249','Jana Malá','Brněnská 56','Vyškov');

INSERT INTO Pobocka
VALUES('Jánská',10000000);
INSERT INTO Pobocka
VALUES('Palackého',5000000);

INSERT INTO Ucet
VALUES(4320286,52000,'440726/0672','Jánská');
INSERT INTO Ucet
VALUES(2348531,10000,'530610/4532','Jánská');
INSERT INTO Ucet
VALUES(1182648,10853,'530610/4532','Palackého');
INSERT INTO Ucet
VALUES(2075752,26350,'440726/0672','Palackého');
INSERT INTO Ucet
VALUES(98123245,52000, '625622/6249','Jánská');

INSERT INTO Transakce
VALUES(4320286,1,'19981010',3000);
INSERT INTO Transakce
VALUES(4320286,2,'19981012',-5000);
INSERT INTO Transakce
VALUES(2075752,1,'19981014',-2000);
INSERT INTO Transakce
VALUES(2075752,2,'19981014',10000);


DROP TABLE Transakce
DROP TABLE Ucet
DROP TABLE Pobocka
DROP TABLE Klient

CREATE TABLE Typ_karty(
typ_karty_id INT IDENTITY(1,1),
nazov VARCHAR(20)
);
GO

INSERT INTO Typ_karty VALUES ('Kreditna karta')
INSERT INTO Typ_karty VALUES ('Debetna karta')
GO

SELECT * FROM platebni_karta
DROP TABLE platebni_karta
SELECT * FROM ucet

CREATE TABLE Platebni_karta(
c_karty VARCHAR(20) NOT NULL,
typ_karty_id INT NOT NULL,
c_uctu INTEGER NOT NULL,
platnost DATE NOT NULL,
PRIMARY KEY (c_karty),
FOREIGN KEY (c_uctu) REFERENCES Ucet ON DELETE CASCADE
);
GO

INSERT INTO Platebni_karta VALUES (4916543140217,2,4320286,'2016-01-31')
GO
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
SELECT DISTINCT mesto FROM klient
GO

SELECT DISTINCT k.*
FROM klient k, ucet u
WHERE k.r_cislo=u.r_cislo
AND u.pobocka='Jásnká'
GO

SELECT k.jmeno, k.r_cislo, COUNT(*) AS p, SUM(stav) AS c
FROM klient k, ucet u
WHERE k.r_cislo=u.r_cislo
GROUP BY k.r_cislo, k.jmeno
GO

SELECT COUNT(*) AS pocet
FROM klient
GO
SELECT * FROM klient
GO

--Ktorí klienti majú na účtoch viac ako 50 000kč?--
SELECT k.jmeno, k.r_cislo, SUM(stav) AS celkom
FROM klient k, ucet u
WHERE k.r_cislo=u.r_cislo
GROUP BY k.r_cislo, k.jmeno
HAVING SUM(stav)>50000
GO

--Ktorí klienti majú účet len u pobočky Jánská?--
SELECT DISTINCT k.*
FROM klient k, ucet u
WHERE k.r_cislo=u.r_cislo AND u.pobocka='Jánská' AND
NOT EXISTS (SELECT * FROM ucet u WHERE k.r_cislo=u.r_cislo AND u.pobocka<>'Jánská')
GO

--Samostatná úloha--
--Ktorí klienti z Brna majú na svojich účtoch viac ako 20 000Kč celkovo?
--Ktorí klienti vlastnia viac ako jeden účet a koľko ich je?

--1. uloha--
SELECT DISTINCT k.*
FROM klient k, ucet u
WHERE u.r_cislo=k.r_cislo AND k.mesto='Brno' AND u.stav>20000
ORDER BY k.jmeno
GO
SELECT DISTINCT k.jmeno, SUM(u.stav) AS celkovo
FROM klient k, ucet u
WHERE k.r_cislo=u.r_cislo AND k.mesto='Brno'
GROUP BY k.jmeno
HAVING SUM(u.stav)>20000
GO
--2.uloha--
SELECT k.jmeno, COUNT(u.c_uctu) AS pocet FROM klient k, ucet u
WHERE k.r_cislo=u.r_cislo
GROUP BY jmeno
HAVING COUNT(u.c_uctu)>1
GO