ADATB/20241120/database.sql

CREATE DATABASE proba CHARACTER SET ="utf8" COLLATE="utf8_hungarian_ci";

CREATE TABLE dolgozok(id INT PRIMARY KEY AUTO_INCREMENT, vnev VARCHAR(255) NOT NULL, knev VARCHAR(255) NOT NULL, irszam INT(4) NOT NULL COMMENT "A telepulesek tabla elsodleges kulcsara hivatkozik", kozterulet_neve VARCHAR(255) NOT NULL COMMENT "Ide kerul a kozterulet neve", kozterulet_tipusa INT(4) COMMENT "A kozteruletek tabla elsodleges kulcsara hivatkozik", hazszam_emelet VARCHAR(255) NOT NULL, szul_datum DATE DEFAULT "1989-08-20" COMMENT "A szuletesu datum YYYY-mm-dd formatumban", szul_hely INT(4) COMMENT "A telepulesek tabla elsodleges kulcsara hivatkozik");

CREATE TABLE telepulesek (irszam INT(4) PRIMARY KEY, telepules_neve VARCHAR(255) NOT NULL);

ALTER TABLE dolgozok ADD CONSTRAINT dolgozok_telepulesek_lakhely_fk FOREIGN KEY (irszam) REFERENCES telepulesek(irszam) ON UPDATE CASCADE;

/*TODO*/
ALTER TABLE dolgozok ADD CONSTRAINT dolgozok_telepulesek_szul_hely_fk FOREIGN KEY (szul_hely) REFERENCES telepulesek(irszam) ON UPDATE CASCADE;

CREATE TABLE kozteruletek(id INT PRIMARY KEY AUTO_INCREMENT, kozterulet_neve VARCHAR(255) NOT NULL);

INSERT INTO  kozteruletek(kozterulet_neve) VALUES ("út"),("utca"),("tér");

INSERT INTO telepulesek VALUES (5600, "Békéscsaba"), (3300, "Eger");

DESCRIBE dolgozok;

INSERT INTO dolgozok (vnev, knev, irszam, kozterulet_neve, kozterulet_tipusa, hazszam_emelet, szul_datum, szul_hely)
VALUES ("Kiss", "Aladár", 5600, "Petőfi", 2, "25", "1989-10-24", 3300),
("Nagy", "Kelemen", 3300, "Karak", 1, "13/B", "1978-03-15", 5600),
("Károlyi", "József", 3300, "Levendula", 3, "2.emelet 12-es lakas", "2004-01-12", 3300);


SELECT 
dolgozok.id AS `Dolgozoi azonosito`,
CONCAT(dolgozok.vnev, " ", dolgozok.knev) AS `Dolgozo neve`,
CONCAT(dolgozok.irszam, " ", telepulesek.telepules_neve, ", ", dolgozok.kozterulet_neve, kozteruletek.kozterulet_neve, "hazszam emelet", ".") 
AS  `Lakhely`,
CONCAT(telepulesek.telepules_neve, ", ", dolgozok.szul_datum) AS `Szuletes helye es ideje`
FROM dolgozok
INNER JOIN telepulesek ON telepulesek.irszam = dolgozok.irszam
INNER JOIN kozteruletek ON kozteruletek.id = dolgozok.kozterulet_tipusa; 


CREATE VIEW dolgozok_adata AS SELECT 
dolgozok.id AS `Dolgozoi azonosito`,
CONCAT(dolgozok.vnev, " ", dolgozok.knev) AS `Dolgozo neve`,
CONCAT(dolgozok.irszam, " ", telepulesek.telepules_neve, ", ", dolgozok.kozterulet_neve, kozteruletek.kozterulet_neve, "hazszam emelet", ".") 
AS  `Lakhely`,
CONCAT(telepulesek.telepules_neve, ", ", dolgozok.szul_datum) AS `Szuletes helye es ideje`
FROM dolgozok
INNER JOIN telepulesek ON telepulesek.irszam = dolgozok.irszam
INNER JOIN kozteruletek ON kozteruletek.id = dolgozok.kozterulet_tipusa; 

SELECT * FROM dolgozok_adata;

CREATE TABLE fizetesek (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "A kiutalas egyedi azonositoja a rendszerben",
    dolgozo INT NOT NULL,
    fizetes FLOAT (9,3) NOT NULL,
    kiutalas_datuma DATETIME DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE fizetesek 
ADD CONSTRAINT fizetesek_dolgozok_fk
FOREIGN KEY (dolgozo) REFERENCES dolgozok(id) ON UPDATE CASCADE;

INSERT INTO fizetesek (dolgozo, fizetes)
VALUES (1, 15000.125),
(2, 7800),
(1, 3600),
(1, 560.75);


CREATE VIEW kifizetesek AS
SELECT fizetesek.id as `kiutalas azonositoja`,
CONCAT(dolgozok.vnev, " ", dolgozok.knev) AS `dolgozo neve`,
CONCAT(fizetesek.fizetes, " $")  AS `fizetes`,
kiutalas_datuma AS `kiutalas idopontja`
FROM fizetesek INNER JOIN dolgozok ON dolgozok.id = fizetesek.dolgozo;

SELECT * FROM fizetesek WHERE id NOT IN (SELECT id FROM dolgozok);

/*Károlyi József*/
SELECT vnev, knev, fizetes FROM dolgozok 
LEFT JOIN fizetesek ON dolgozok.id = fizetesek.dolgozo
WHERE fizetes IS NULL;

/*EMPTY SET*/
SELECT vnev, knev, fizetes FROM dolgozok 
RIGHT JOIN fizetesek ON dolgozok.id = fizetesek.dolgozo
WHERE fizetes IS NULL;


SELECT * FROM kifizetesek
INNER JOIN dolgozok_adata ON dolgozok_adata.`Dolgozo neve` = kifizetesek.`dolgozo neve`;