· 4 years ago · May 10, 2021, 01:40 PM
1DROP SCHEMA IF EXISTS tukidb;
2CREATE SCHEMA tukidb;
3USE tukidb;
4
5
6
7CREATE TABLE asiakasKone (
8 asiakasKoneID INT PRIMARY KEY AUTO_INCREMENT UNIQUE,
9 kayttojarjestelma VARCHAR(30),
10 prosessori VARCHAR(30),
11 emolevy VARCHAR(30)
12);
13
14CREATE TABLE asiakas (
15 htun VARCHAR(11) PRIMARY KEY NOT NULL UNIQUE,
16 asiakasID INT AUTO_INCREMENT UNIQUE,
17 asiakasKoneID INT,
18 etunimi VARCHAR(30) NOT NULL,
19 sukunimi VARCHAR(30) NOT NULL,
20 osoite VARCHAR(30),
21 kunta VARCHAR(30),
22 postinro VARCHAR(5),
23 puhelin VARCHAR(10),
24 kieli VARCHAR(30) DEFAULT 'suomi',
25 CHECK (postinro <= 99999),
26 FOREIGN KEY (asiakasKoneID) REFERENCES asiakasKone(asiakasKoneID)
27);
28
29CREATE TABLE vika (
30vikaluokka INT PRIMARY KEY AUTO_INCREMENT UNIQUE,
31vikakuvaus VARCHAR(30)
32);
33CREATE TABLE tuote (
34tuoteID INT PRIMARY KEY AUTO_INCREMENT UNIQUE,
35tuoteluokka VARCHAR(30),
36tuotenimi VARCHAR(30),
37tuotenumero VARCHAR(30)
38);
39
40CREATE TABLE tyontekija (
41tyontekijaID INT PRIMARY KEY AUTO_INCREMENT UNIQUE,
42etunimi VARCHAR(30),
43sukunimi VARCHAR(30),
44kieli VARCHAR(30)
45);
46
47
48CREATE TABLE tiketti (
49 tikettiID INT PRIMARY KEY AUTO_INCREMENT UNIQUE,
50 otsikko VARCHAR(30) NOT NULL,
51 asiakasID INT,
52 vikaluokka INT,
53 tuoteID INT,
54 tyontekijaID INT,
55 prioriteetti VARCHAR(30),
56 aikaluotu datetime,
57 aikaratkaistu datetime,
58 FOREIGN KEY (asiakasID) REFERENCES asiakas(asiakasID),
59 FOREIGN KEY (vikaluokka) REFERENCES vika(vikaluokka),
60 FOREIGN KEY (tuoteID) REFERENCES tuote(tuoteID),
61 FOREIGN KEY (tyontekijaID) REFERENCES tyontekija(tyontekijaID)
62 );
63
64CREATE TABLE tiketinHenkilot (
65tikettiID INT,
66tyontekijaID INT,
67FOREIGN KEY (tikettiID) REFERENCES tiketti(tikettiID),
68FOREIGN KEY (tyontekijaID) REFERENCES tyontekija(tyontekijaID)
69);
70
71INSERT INTO asiakasKone (kayttojarjestelma, prosessori, emolevy)
72VALUES
73('winukka', 'ryzen', 'emolevy1'),
74('winukka2', 'ryzen2', 'emolevy2'),
75('winukka3', 'ryzen3', 'emolevy3'),
76('winukka4', 'ryzen4', 'emolevy4'),
77('winukka5', 'ryzen5', 'emolevy5'),
78('linux', 'intel', 'emolevy6'),
79('linux2', 'intel2', 'emolevy7'),
80('linux3', 'intel3', 'emolevy8'),
81('linux4', 'intel4', 'emolevy9'),
82('linux5', 'intel5', 'emolevy10');
83
84INSERT INTO asiakas (htun, etunimi, sukunimi, osoite, kunta, puhelin)
85VALUES
86('110195-111A', 'pena', 'perusjatka','Rovastinkatu A 1','Kuopio', '0401111111'),
87('110195-111B','matti', 'nykanen','Rovastinkatu A 2','Nykaskyla', '0402222222'),
88('110195-111C', 'anne', 'meikalainen', 'Rovastinkatu A 3','Kuopio','040333333'),
89('110195-111D', 'Odens', 'jantunen', 'Rovastinkatu A 4','Kuopio', '040444444'),
90('110195-111E', 'ettan' ,'los', 'Rovastinkatu A 5','Kuopio', '040555555'),
91('110295-111A', 'risto' ,'rappaaja','Rovastinkatu A 6', 'Kuopio', '040666666'),
92('110295-111B', 'pate', 'pennanen', 'Rovastinkatu A 7','Kuopio', '0407777777'),
93('110295-111C', 'tero', 'laitinen', 'Rovastinkatu A 8', 'Kuopio', '0408888888'),
94('110295-111D', 'jonne', 'energyshopper', 'Rovastinkatu A 9', 'Kuopio', '0409999999'),
95('110295-111E', 'pete', 'kivinen', 'Rovastinkatu A 10','Kuopio', '0401234567');
96
97INSERT INTO vika (vikakuvaus)
98VALUES
99('ei kaynnisty'),
100('prossu rikki'),
101('kayttojarjestelma jumittaa'),
102('emolevy rikki'),
103('tuuletin hajalla'),
104('takuuhuolto'),
105('tietokone sammuu itsestaan'),
106('emolevy raksuttaa'),
107('troijalainen'),
108('BIOS ei avaudu');
109
110INSERT INTO tuote (tuoteluokka,tuotenimi, tuotenumero)
111VALUES
112('tuoteluokka_1', 'laite_1', '111'),
113('tuoteluokka_2', 'laite_2', '222'),
114('tuoteluokka_3', 'laite_3', '333'),
115('tuoteluokka_4', 'laite_4', '444'),
116('tuoteluokka_5', 'laite_5', '555'),
117('tuoteluokka_6', 'laite_6', '666'),
118('tuoteluokka_7', 'laite_7', '777'),
119('tuoteluokka_8', 'laite_8', '888'),
120('tuoteluokka_9', 'laite_9', '999'),
121('tuoteluokka_10', 'laite_10', '123');
122
123INSERT INTO tyontekija (etunimi,sukunimi,kieli)
124VALUES
125('jonni', 'paavola', 'suomi'),
126('olli', 'tanninen', 'suomi'),
127('pasi', 'nielikainen', 'suomi'),
128('mikko', 'holopainen', 'suomi'),
129('topias', 'pohjola', 'suomi'),
130('anssi', 'ranimaki', 'suomi'),
131('keksi', 'monsteri', 'suomi'),
132('vaiski', 'vemmelsaari', 'ruotsi'),
133('dimitri', 'klimov', 'venaja'),
134('zing', 'zong', 'kiina');
135
136INSERT INTO tiketti (otsikko,tuoteID,prioriteetti,aikaluotu,aikaratkaistu)
137VALUES
138('tiketti_1','0001','kiireinen','2021-09-05 11:00:00', '2021-10-05 11:15:11'),
139('tiketti_2', '0002', 'ei kiireinen',' 2021-07-05 9:32:12', '2021-08-05 8:00:15'),
140('tiketti_3', '0003', 'melko kiireinen','2021-07-05 9:30:00','2021-07-05 10:12:07'),
141('tiketti_4', '0004', 'kiireinen', '2021-06-05 8:03:54', '2021-06-05 08:33:32'),
142('tiketti_5', '0005', 'ei kiireinen', '2021-01-05 12:13:00', '2021-02-05 9:15:00'),
143('tiketti_6', '0006', 'ei kiireinen', '2021-01-05 9:01:10', '2021-02-05 8:10:42'),
144('tiketti_7', '0007', 'melko kiireinen', '2021-01-05 10:02:01', '2021-01-05 11:30:15'),
145('tiketti_8', '0008', 'kiireinen', '2021-01-05 14:15:27', '2021-01-05 17:30:00'),
146('tiketti_9', '0009', 'kiireinen', '2021-01-05 12:12:12', '2021-01-05 13:00:09'),
147('tiketti_10', '0010', 'kiireinen', '2021-01-05 10:50:37', '2021-01-05 11:59:00');
148
149
150
151
152
153SELECT * FROM asiakasKone;
154CREATE VIEW asia_view AS
155SELECT kayttojarjestelma, prosessori, emolevy
156FROM asiakasKone
157WHERE prosessori IS NOT NULL;
158SELECT * FROM asia_view;
159
160SELECT * FROM asiakas;
161CREATE VIEW as_view AS
162SELECT htun, asiakasID, asiakasKoneID, etunimi, sukunimi, osoite, kunta, postinro, puhelin, kieli
163FROM asiakas
164WHERE etunimi IS NOT NULL;
165SELECT * FROM as_view;
166
167SELECT * FROM vika;
168CREATE VIEW vika_view AS
169SELECT vikaluokka, vikakuvaus
170FROM vika
171WHERE vikaluokka IS NOT NULL;
172SELECT * FROM vika_view;
173
174SELECT * FROM tuote;
175CREATE VIEW tuote_view AS
176SELECT tuoteID, tuoteluokka, tuotenimi, tuotenumero
177FROM tuote
178WHERE tuoteID IS NOT NULL;
179SELECT * FROM tuote_view;
180
181SELECT * FROM tyontekija;
182CREATE VIEW tyon_view AS
183SELECT tyontekijaID, etunimi, sukunimi, kieli
184FROM tyontekija
185WHERE tyontekijaID IS NOT NULL;
186SELECT * FROM tyon_view;
187
188SELECT * FROM tiketti;
189CREATE VIEW tiketti_view AS
190SELECT tikettiID, otsikko, asiakasID, vikaluokka, tuoteID, tyontekijaID, prioriteetti, aikaluotu, aikaratkaistu
191FROM tiketti
192WHERE tikettiID IS NOT NULL;
193SELECT * FROM tiketti_view;
194
195SELECT * FROM tiketinHenkilot;
196CREATE VIEW Henkilot_view AS
197SELECT tikettiID, tyontekijaID
198FROM tiketti
199 INNER JOIN tiketti
200 ON tiketti.tikettiID = tiketti.id
201 INNER JOIN tyontekija
202 ON tyontekija.tyontekijaID = tyontekija.id
203WHERE tyontekijaID IS NOT NULL;
204SELECT * FROM Henkilot_view;
205