· 8 years ago · Dec 14, 2017, 09:16 AM
1#2
2CREATE DATABASE IF NOT EXISTS Bilete_18;
3
4#2.1
5USE Bilete_18;
6
7CREATE TABLE IF NOT EXISTS Darbinieki (
8 ID INT(6) AUTO_INCREMENT PRIMARY KEY,
9 VARDS VARCHAR(50) NOT NULL,
10 UZVARDS VARCHAR(50) NOT NULL,
11 TALRUNIS VARCHAR(10) NOT NULL
12 );
13
14CREATE TABLE IF NOT EXISTS Uznemums (
15 ID INT(6) AUTO_INCREMENT PRIMARY KEY,
16 NOSAUKUMS VARCHAR(50) NOT NULL,
17 PARSTAVIS VARCHAR(50) NOT NULL,
18 PARSTAVJA_TALRUNIS VARCHAR(50) NOT NULL
19 );
20
21
22CREATE TABLE IF NOT EXISTS Ligums (
23 NR INT(6) AUTO_INCREMENT PRIMARY KEY,
24 PRIEKSMETS VARCHAR(50) NOT NULL,
25 DATUMS DATE NOT NULL,
26 SUMMA FLOAT(10,2) NOT NULL,
27 UZNEMUMA_ID INT(6) NOT NULL,
28 DARBINIEKA_ID INT(6) NOT NULL,
29 FOREIGN KEY (UZNEMUMA_ID)
30 REFERENCES Uznemums(ID)
31 ON DELETE CASCADE,
32 FOREIGN KEY (DARBINIEKA_ID)
33 REFERENCES Darbinieki(ID)
34 ON DELETE CASCADE
35 );
36
37#3
38
39INSERT INTO Darbinieki (VARDS, UZVARDS, TALRUNIS) VALUES
40("Inga", "Herca", "5023333"),
41("Zane", "Aiviekste", "9563487"),
42("Uldis", "Briedis", "5689124");
43
44INSERT INTO Uznemums (NOSAUKUMS, PARSTAVIS, PARSTAVJA_TALRUNIS) VALUES
45("SIA \'ATEA\'", "Svetlana", "236-556-78"),
46("SIA \'Zvaigzne\'", "MÄris", "123-456-87"),
47("SIA \'Celeron\'", "Sergejs", "269-369-15");
48
49INSERT INTO Ligums (PRIEKSMETS, DATUMS, SUMMA, UZNEMUMA_ID, DARBINIEKA_ID) VALUES
50("pirkums", "2017-06-07", 3223.00, 1, 1),
51("pakalpojuma sniegsana", "2017-05-31", 1425.00, 2, 2),
52("pirkums", "2017-05-25", 3500.00, 3, 3),
53("pakalpojuma sniegsana", "2017-06-19", 1600.00, 2, 1),
54("pirkums", "2017-06-02", 2505.00, 1, 2),
55("pirkums", "2017-05-22", 3506.00, 1, 3);
56
57#4
58
59SELECT
60CONCAT(Darbinieki.VARDS, ' ', Darbinieki.UZVARDS, ',tÄlr.', Darbinieki.TALRUNIS) AS "Atbildigais darbinieks",
61Ligums.DATUMS AS "Liguma noslegsanas datums",
62Ligums.PRIEKSMETS AS "Liguma prieksments",
63Ligums.SUMMA AS "Liguma summa",
64Uznemums.NOSAUKUMS AS "Uzvenmums ar kuru noslegts ligums",
65CONCAT(Uznemums.PARSTAVIS, ", ", Uznemums.PARSTAVJA_TALRUNIS) AS "Uznemuma parstavis"
66FROM Darbinieki
67 INNER JOIN Ligums ON DARBINIEKI.id = Ligums.DARBINIEKA_ID
68 INNER JOIN Uznemums ON Ligums.UZNEMUMA_ID = Uznemums.ID;
69
70#5
71
72delimiter //
73
74CREATE PROCEDURE videja_summa(darbinieka_vards VARCHAR(50), darbinieka_uzvards VARCHAR(50))
75 BEGIN
76 DECLARE USER_ID INT;
77 SET @USER_ID = (SELECT ID FROM DARBINIEKI WHERE VARDS = darbinieka_vards AND UZVARDS = darbinieka_uzvards);
78 SELECT CONCAT("VidÄ“jÄ ", darbinieka_vards, " " , darbinieka_uzvards, " ligumu summa : ", ROUND(AVG(Ligums.SUMMA),2)) AS "Proceduras rezultats"
79 FROM ligums WHERE DARBINIEKA_ID = @USER_ID;
80 END//
81
82delimiter ;
83
84call videja_summa("Zane", "Aiviekste");
85
86delimiter //
87
88CREATE FUNCTION kopeja_summa (darbinieka_kods INT, menesis INT) RETURNS FLOAT(10,2)
89BEGIN
90 DECLARE k_summa FLOAT;
91 SELECT SUM(SUMMA)
92 from ligums where DARBINIEKA_ID = darbinieka_kods AND MONTH(DATUMS) = menesis INTO k_summa;
93 RETURN ROUND(k_summa);
94END//
95delimiter ;
96
97select CONCAT(VARDS, " ", UZVARDS) AS "Darbinieks", kopeja_summa(ID, 6) AS "Kopeja liguma summa" from darbinieki;