CREATE TABLE auto(
autoID int not null Primary key AUTO_INCREMENT,
regNumber char(6) UNIQUE,
markID int,
varv varchar(20),
v_aasta int,
kaigukastID int,
km decimal(6,2)
);
SELECT * FROM auto
CREATE TABLE mark(
markID int not null Primary key AUTO_INCREMENT,
autoMark varchar(30) UNIQUE
);
INSERT INTO mark(autoMark)
VALUES ('Ziguli');
INSERT INTO mark(autoMark)
VALUES ('Lambordzini');
INSERT INTO mark(autoMark)
VALUES ('BMW');
SELECT * FROM mark;
CREATE TABLE kaigukast(
kaigukastID int not null Primary key AUTO_INCREMENT,
kaigukast varchar(30) UNIQUE
);
INSERT INTO kaigukast(kaigukast)
VALUES ('Automaat');
INSERT INTO kaigukast(kaigukast)
VALUES ('Manual');
SELECT * FROM kaigukast;
ALTER TABLE auto
ADD FOREIGN KEY (markID) REFERENCES mark(markID);
ALTER TABLE auto
ADD FOREIGN KEY (kaigukastID) REFERENCES kaigukast(kaigukastID);
CREATE TABLE klient(
klientID int not null primary key AUTO_INCREMENT,
kliendiNimi varchar(50),
telefon varchar(20),
aadress varchar(50),
soiduKogemus varchar(30)
)
CREATE TABLE rendiLeping(
lepingID int not null primary key AUTO_INCREMENT,
rendiAlgus date,
rendiLopp date,
klientID int,
regNumber varchar(6),
rendiKestvus int,
hindKokku decimal(5,2),
tootajaID int
)
CREATE TABLE tootaja(
tootajaID int not null primary key AUTO_INCREMENT,
tootajaNimi varchar(50),
ametID int)
ALTER TABLE rendiLeping add FOREIGN KEY (klientID) REFERENCES klient(klientID)
ALTER TABLE rendiLeping add FOREIGN KEY (tootajaID) REFERENCES tootaja(tootajaID)
ALTER TABLE rendileping add FOREIGN KEY (regNumber) REFERENCES auto(regNumber)
CREATE TABLE Amet(
ametID int not null PRIMARY KEY AUTO_INCREMENT,
ametNimi varchar(20))
ALTER TABLE tootaja add FOREIGN KEY (ametID) REFERENCES amet(ametID)
SELECT auto.regNumber, kaigukast.kaigukast
FROM auto, kaigukast where kaigukast.kaigukastID=auto.kaigukastID
SELECT auto.regnumber, mark.automark
FROM auto INNER JOIN mark ON mark.markID=auto.markID
SELECT klient.kliendiNimi, rendileping.lepingID, auto.regNumber FROM rendileping
INNER JOIN klient ON rendileping.klientID=klient.klientID
INNER JOIN auto on rendileping.regNumber=auto.regNumber
SELECT kaigukast.kaigukast, Count(auto.autoID) AS kogus
FROM auto, kaigukast
where auto.kaigukastID=kaigukast.kaigukastID GROUP BY kaigukast.kaigukast
SELECT auto.regNumber, tootaja.tootajaNimi FROM auto,tootaja,rendileping where tootaja.tootajaID=rendileping.tootajaID and rendileping.regNumber=auto.regNumber
SELECT auto.regNumber, tootaja.tootajaNimi FROM auto,tootaja,rendileping where tootaja.tootajaID=rendileping.tootajaID and rendileping.regNumber=auto.regNumber and (rendileping.rendiAlgus BETWEEN '2022-05-02' and '2022-05-12')
SELECT COUNT(auto.autoID) AS Autod, SUM(rendileping.hindKokku) AS Hind from rendiLeping, auto WHERE rendiAlgus between '2022-05-01' and '2022-10-01'
BEGIN
INSERT INTO mark(autoMark)
VALUES (uusAutoMark);
SELECT * FROM mark;
END
BEGIN
SELECT autoMark FROM mark
WHERE autoMark LIKE CONCAT (Taht, '%');
END
BEGIN
DELETE FROM mark WHERE markID=id;
SELECT * FROM mark;
END
BEGIN
UPDATE mark SET autoMark=uusAutoMark
WHERE markID=id;
SELECT * FROM mark;
END
BEGIN
DELETE FROM rendileping WHERE lepingID=id;
SELECT * FROM rendileping;
END
BEGIN
INSERT INTO amet(ametNimi)
VALUES (lisaAmet);
UPDATE amet SET ametNimi=uundadaAmet WHERE ametID=id;
SELECT * FROM amet;
END
