86 lines
2.5 KiB
MySQL
86 lines
2.5 KiB
MySQL
|
CREATE DATABASE autoberles CHARACTER SET = "utf8" COLLATE = "utf8_hungarian_ci";
|
||
|
|
|
||
|
|
use autoberles;
|
||
|
|
|
||
|
|
CREATE TABLE berlok (id INT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
nev VARCHAR(100) NOT NULL,
|
||
|
|
jogositvany VARCHAR(15) NOT NULL,
|
||
|
|
telefonszam VARCHAR(20)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE autok(
|
||
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
rendszam VARCHAR(6) UNIQUE NOT NULL,
|
||
|
|
tipus VARCHAR(100) NOT NULL,
|
||
|
|
evjarat YEAR,
|
||
|
|
szin VARCHAR(30)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE kolcsonzes(
|
||
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
berloid INT NOT NULL,
|
||
|
|
autoid INT NOT NULL,
|
||
|
|
berletkezdete DATE NOT NULL,
|
||
|
|
napokszama INT,
|
||
|
|
napidij FLOAT(12,3) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE kolcsonzes ADD CONSTRAINT
|
||
|
|
kolcsonzes_berlok_fk FOREIGN KEY (berloid)
|
||
|
|
REFERENCES berlok(id) ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE kolcsonzes ADD CONSTRAINT
|
||
|
|
kolcsonzes_autok_fk FOREIGN KEY (autoid)
|
||
|
|
REFERENCES autok(id) ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
|
||
|
|
INSERT INTO autok (rendszam, tipus, evjarat, szin) VALUES
|
||
|
|
("ABC456", "Ford Ka", 2003, "Pink"),
|
||
|
|
("ABC123", "Volkswagen Golf", 2011, "Fehér"),
|
||
|
|
("ABC157", "Ford Mondeo", 2015, "Fekete"),
|
||
|
|
("ABC448", "Volkswagen Golf", 2012, "Kék")
|
||
|
|
;
|
||
|
|
|
||
|
|
INSERT INTO berlok (nev, jogositvany, telefonszam) VALUES
|
||
|
|
("Kandúr Károly", "LR337157", "06-41-334112"),
|
||
|
|
("Gipsz Jakab", "VE445112", "06-41-555223")
|
||
|
|
;
|
||
|
|
|
||
|
|
INSERT INTO kolcsonzes (berloid, autoid, berletkezdete, napokszama, napidij) VALUES
|
||
|
|
((SELECT id FROM berlok WHERE nev LIKE "Kandúr Károly"), (SELECT id FROM autok WHERE rendszam LIKE "ABC157") ,"2017.04.23", NULL, 12500),
|
||
|
|
((SELECT id FROM berlok WHERE nev LIKE "Gipsz Jakab"), (SELECT id FROM autok WHERE rendszam LIKE "ABC123") ,"2017.04.25", NULL, 9999)
|
||
|
|
;
|
||
|
|
|
||
|
|
UPDATE kolcsonzes SET napokszama = 6
|
||
|
|
WHERE berloid IN (SELECT id FROM berlok WHERE nev LIKE "Kandúr Károly");
|
||
|
|
|
||
|
|
CREATE VIEW Kandur_Karoly_fizetendo AS
|
||
|
|
SELECT berlok.nev, SUM(kolcsonzes.napidij * kolcsonzes.napokszama) AS `Fizetendo`
|
||
|
|
FROM kolcsonzes
|
||
|
|
INNER JOIN berlok ON kolcsonzes.berloid = berlok.id
|
||
|
|
GROUP BY kolcsonzes.berloid
|
||
|
|
HAVING kolcsonzes.berloid IN
|
||
|
|
(SELECT id FROM berlok WHERE berlok.nev LIKE "Kandúr Károly");
|
||
|
|
|
||
|
|
CREATE VIEW kolcsonzes_alatt_levo_autok AS
|
||
|
|
SELECT berlok.nev AS `Nev`,
|
||
|
|
autok.rendszam AS `Rendszam`
|
||
|
|
FROM kolcsonzes
|
||
|
|
INNER JOIN berlok ON kolcsonzes.berloid = berlok.id
|
||
|
|
INNER JOIN autok ON kolcsonzes.autoid = autok.id
|
||
|
|
WHERE kolcsonzes.napokszama IS NULL;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
CREATE USER IF NOT EXISTS titkar;
|
||
|
|
|
||
|
|
SELECT user from mysql.user;
|
||
|
|
|
||
|
|
GRANT SELECT (berloid) ON kolcsonzes TO titkar;
|
||
|
|
GRANT UPDATE (Fizetendo) ON Kandur_Karoly_fizetendo TO titkar WITH GRANT OPTION;
|
||
|
|
GRANT DELETE ON kolcsonzes_alatt_levo_autok TO titkar;
|
||
|
|
|
||
|
|
REVOKE
|
||
|
|
|
||
|
|
|