· 8 years ago · Nov 27, 2017, 08:14 AM
1#2.1 ////////////////////////////////////////////////////////////////////
2CREATE DATABASE `DB-Electronics`;
3CREATE USER '236606'@'localhost' IDENTIFIED BY 'kac606';
4GRANT SELECT, INSERT, DELETE ON `DB-Electronics`.* TO '236606'@'localhost';
5
6#2.2 ////////////////////////////////////////////////////////////////////
7CREATE TABLE Procesory(
8ID int NOT NULL AUTO_INCREMENT,
9model varchar(30) NOT NULL,
10rdzenie int unsigned NOT NULL,
11czestotliwosc float unsigned NOT NULL,
12PRIMARY KEY(ID)
13)AUTO_INCREMENT=3000;
14
15CREATE TABLE Matryce(
16ID int NOT NULL AUTO_INCREMENT,
17przekatna decimal(3,1) unsigned,
18sRGB float unsigned,
19aRGB float unsigned,
20CHECK(sRGB <= 100),
21CHECK(aRGB <= sRGB),
22PRIMARY KEY(ID)
23);
24
25
26DELIMITER $$
27CREATE TRIGGER matryce_before_insert
28 BEFORE INSERT ON Matryce FOR EACH ROW
29 BEGIN
30 IF New.sRGB > 100 OR New.aRGB > New.sRGB THEN
31 SIGNAL SQLSTATE '12345'
32 SET MESSAGE_TEXT = 'check constraints!';
33 END IF;
34 END$$
35
36DELIMITER ;
37
38CREATE TABLE Producenci(
39ID int NOT NULL AUTO_INCREMENT,
40nazwa varchar(50) DEFAULT NULL,
41siedziba char(20) DEFAULT NULL,
42PRIMARY KEY(ID)
43);
44
45CREATE TABLE Laptopy(
46model varchar(30) NOT NULL,
47producent int,
48procesor int,
49matryca int,
50typ enum('biurowy','gaming','ultrabook','inny'),
51PRIMARY KEY(model),
52FOREIGN KEY(producent) REFERENCES Producenci(ID),
53FOREIGN KEY(procesor) REFERENCES Procesory(ID),
54FOREIGN KEY(matryca) REFERENCES Matryce(ID)
55);
56
57ALTER TABLE Procesory AUTO_INCREMENT=3000;
58ALTER TABLE Producenci AUTO_INCREMENT=16;
59
60#2.3 ////////////////////////////////////////////////////////////////////
61INSERT INTO `DB-Electronics`.`Procesory` (`model`, `rdzenie`, `czestotliwosc`) VALUES ('i7-6700HQ', '4', '2.6'), ('i7-8700K', '6', '3.7'),
62('i7-8650U', '4', '1.9'), ('i5-8600K', '6', '3.6'), ('i7-6970', '4', '2.8'), ('6870-HQ', '4', '2.7'), ('i7-6900K', '8', '3.2'),
63('i7-5930K', '6', '3.5'), ('4930K', '6', '3.4'),
64('A12-9730P', '4', '2.8'), ('A6-9210', '4', '2.6'), ('FX 9830P', '4', '3.7'), ('6970-HQ', '4', '2.8'), ('i7-6950K', '8', '3.4');
65
66INSERT INTO `DB-Electronics`.`Matryce` (`przekatna`, `sRGB`, `aRGB`) VALUES ('15.6', '100', '85'), ('13.7', '90', '75'),
67 ('17.3', '100', '100'), ('13.7', '90.5', '75.5'), ('12.6', '100', '70'), ('17.3', '100', '90'), ('19.2', '90', '70'), ('15.6', '90.5', '75'),
68 ('12.6', '70', '50'), ('19.2', '100', '80.5'), ('13.7', '90.5', '80.5'), ('20.0', '100', '95');
69
70INSERT INTO `DB-Electronics`.`Producenci` (`nazwa`, `siedziba`) VALUES ('Lenovo', 'China'), ('Asus', 'Taiwan'), ('Acer', 'Taiwan'), ('Apple Inc.', 'USA'),
71('Dell', 'USA'), ('HP Inc.', 'USA'), ('AORUS', 'Taiwan'), ('Microsoft', 'USA'), ('Medion', ''), ('', 'India'),
72('Maingear', 'USA'), ('Founder', 'China'), ('Walton', 'Bangladesh'), ('Clevo', 'Taiwan'), ('Eurocom', 'Canada');
73
74
75#2.4 ////////////////////////////////////////////////////////////////////
76DELIMITER $$
77CREATE PROCEDURE Generate(i INT)
78BEGIN
79 DECLARE nazwa varchar(30);
80 DECLARE prodID int;
81 DECLARE procID int;
82 DECLARE matrID int;
83 DECLARE typ ENUM('biurowy', 'gaming', 'ultrabook', 'inny');
84 WHILE (i > 0)
85 DO
86
87 SET prodID = (SELECT (FLOOR(RAND() * (SELECT COUNT(ID) FROM Producenci)) ) + 1);
88 SET procID = (SELECT (FLOOR(RAND() * (SELECT COUNT(ID) FROM Procesory)) ) + 3000);
89 SET matrID = (SELECT (FLOOR(RAND() * (SELECT COUNT(ID) FROM Matryce)) ) + 1);
90 SET typ = (SELECT (FLOOR(RAND() * 3))+ 1);
91 SET nazwa = concat('Laptop ', CAST(i AS char));
92
93 INSERT `DB-Electronics`.`Laptopy` (`model`, `producent`, `procesor`, `matryca`, `typ`) VALUES
94 (nazwa, prodID, procID, matrID, typ);
95
96 SET i = i - 1;
97 END WHILE;
98END $$
99
100DELIMITER ;
101
102DROP PROCEDURE IF EXISTS Generate;
103
104CALL Generate(1200);
105
106#2.5 ////////////////////////////////////////////////////////////////////
107DELIMITER $$
108CREATE FUNCTION test (prodID int)
109 RETURNS varchar(30)
110BEGIN
111 DECLARE answer varchar(30);
112 SET answer = (SELECT model FROM Laptopy INNER JOIN Producenci ON Laptopy.producent = Producenci.ID INNER JOIN Matryce ON Matryce.ID = Laptopy.matryca
113 WHERE prodID = Laptopy.producent ORDER BY Matryce.przekatna DESC LIMIT 1);
114 RETURN answer;
115END;
116$$
117DELIMITER ;
118
119DROP FUNCTION IF EXISTS test;
120
121SELECT test(2);
122
123#2.6 ////////////////////////////////////////////////////////////////////
124DELIMITER $$
125
126CREATE TRIGGER insertModel
127BEFORE INSERT ON Laptopy
128FOR EACH ROW
129BEGIN
130 IF ((SELECT COUNT(ID) FROM Producenci WHERE ID = NEW.producent) = 0)
131 THEN
132 INSERT INTO Producenci (`ID`) VALUES (NEW.producent);
133 END IF;
134 UPDATE Producenci SET liczba_modeli = (liczba_modeli + 1) WHERE ID = NEW.producent;
135END;
136$$
137DELIMITER ;
138
139DROP TRIGGER IF EXISTS insertModel;
140
141INSERT INTO Laptopy (`model`, `producent`, `procesor`, `matryca`, `typ`) VALUES ('Tests', '17', '3004', '2', 'inny');
142INSERT INTO Producenci (`nazwa`, `siedziba`) VALUES ('test', 'test');
143
144#2.7 ////////////////////////////////////////////////////////////////////
145DELIMITER $$
146CREATE FUNCTION amountOfProcessors (procID INT)
147 RETURNS INT
148 BEGIN
149 DECLARE answer INT;
150 SET answer = (SELECT COUNT(model) FROM Laptopy WHERE procesor = procID);
151 RETURN answer;
152 END;
153$$
154DELIMITER ;
155
156DROP FUNCTION IF EXISTS amountOfProcessors;
157
158SELECT amountOfProcessors(3002);
159
160
161#2.8 ////////////////////////////////////////////////////////////////////
162DELIMITER $$
163CREATE TRIGGER deleteUnused
164AFTER DELETE ON Laptopy
165FOR EACH ROW
166BEGIN
167 IF(OLD.procesor NOT IN (SELECT procesor FROM Laptopy))
168 THEN
169 DELETE FROM Procesory WHERE ID = OLD.procesor;
170 END IF;
171
172 UPDATE Producenci SET liczba_modeli = (liczba_modeli - 1) WHERE ID = OLD.producent;
173END $$
174DELIMITER ;
175
176DROP TRIGGER IF EXISTS deleteUnused;
177INSERT INTO `DB-Electronics`.`Procesory` (`ID`, `model`, `rdzenie`, `czestotliwosc`) VALUES ('3014', 'test', '4', '4');
178INSERT INTO `DB-Electronics`.`Laptopy` (`model`, `producent`, `procesor`, `matryca`, `typ`) VALUES ('Laptop test', '4', '3014', '4', 'inny');
179
180DELETE FROM Laptopy WHERE model LIKE 'Laptop 10';
181
182#2.9 ////////////////////////////////////////////////////////////////////
183CREATE VIEW newView AS
184SELECT l.model, l.typ, proc.rdzenie, proc.czestotliwosc, mat.przekatna, mat.sRGB, prod.siedziba FROM Laptopy l
185INNER JOIN Matryce mat ON l.matryca = mat.ID
186INNER JOIN Procesory proc ON l.procesor = proc.ID
187INNER JOIN Producenci prod ON l.producent = prod.ID
188WHERE NOT prod.siedziba LIKE 'USA';
189
190DROP VIEW IF EXISTS newView;
191SELECT * FROM newView;
192
193#2.10 ////////////////////////////////////////////////////////////////////
194CREATE VIEW noTaiwanView AS
195SELECT prod.nazwa, prod.siedziba, l.model FROM Producenci prod
196INNER JOIN Laptopy l ON l.producent = prod.ID;
197
198DELETE FROM Laptopy WHERE producent IN (SELECT ID FROM Producenci WHERE siedziba LIKE 'Taiwan');
199
200#2.11 ////////////////////////////////////////////////////////////////////
201ALTER TABLE Producenci ADD COLUMN liczba_modeli INT UNSIGNED AFTER siedziba;
202ALTER TABLE `DB-Electronics`.`Producenci`
203CHANGE COLUMN `liczba_modeli` `liczba_modeli` INT(10) UNSIGNED NULL DEFAULT 0 ;
204
205DELIMITER $$
206CREATE PROCEDURE calculate()
207BEGIN
208 DECLARE n INT;
209 DECLARE i INT;
210 SELECT COUNT(*) FROM Producenci INTO n;
211
212 SET i = 1;
213
214 WHILE i<=n
215 DO
216 UPDATE Producenci SET liczba_modeli = (SELECT COUNT(model) FROM Laptopy WHERE Laptopy.producent = i) WHERE ID = i;
217 SET i = i + 1;
218 END WHILE;
219END;
220$$
221DELIMITER ;
222
223DROP PROCEDURE IF EXISTS calculate;
224CALL calculate();
225
226DELIMITER $$
227CREATE TRIGGER changeProducer
228BEFORE UPDATE ON Laptopy
229FOR EACH ROW
230BEGIN
231 IF (OLD.producent NOT LIKE NEW.producent)
232 THEN
233 UPDATE Producenci SET liczba_modeli = (liczba_modeli - 1) WHERE ID = OLD.producent;
234 UPDATE Producenci SET liczba_modeli = (liczba_modeli + 1) WHERE ID = NEW.producent;
235 END IF;
236END $$
237DELIMITER ;
238
239DROP TRIGGER IF EXISTS changeProducer;