T8Lq9UNZ

11. CREATE DATABASE `DB-Electronics`;
2CREATE USER '227537'@'localhost' IDENTIFIED BY 'kamil537';
3GRANT SELECT ON *.* TO '227537'@'localhost';
4GRANT UPDATE ON *.* TO '227537'@'localhost';
5GRANT DELETE ON *.* TO '227537'@'localhost';
6GRANT INSERT ON *.* TO '227537'@'localhost';
7
82. CREATE TABLE Procesory( ID int not null auto_increment, model varchar(50) not null, rdzenie int not null, czestotliwosc float not null, PRIMARY KEY (ID), CHECK (rdzenie>0), CHECK (czestotliwosc>0) ); 
9ALTER TABLE Procesory AUTO_INCREMENT=3000;
10CREATE TABLE Matryce( ID int not null auto_increment, przekatna decimal not null, sRGB float not null, aRGB float not null, PRIMARY KEY (ID), CHECK (sRGB>0), CHECK (sRGB<100), CHECK (aRGB>0), CHECK (aRGB<100), CHECK (sRGB >= aRGB ) );
11CREATE TABLE Producenci( ID int not null auto_increment, nazwa varchar(50) not null, siedziba char(20) , PRIMARY KEY (ID) );
12
13CREATE TABLE Matryce( model varchar(30) not null, producent int not null, procesor int not null, matryca int not null, typ ENUM ("biurowy", "gaming", "ultrabook", "inny") NOT NULL, PRIMARY KEY (model), FOREIGN KEY (producent) REFERENCES Producenci(ID), FOREIGN KEY (procesor) REFERENCES Procesory(ID) , FOREIGN KEY (matryca) REFERENCES Matryce(ID) ; 
144.
15
16DELIMITER //
17CREATE PROCEDURE p4()
18BEGIN
19
20DECLARE i INT UNSIGNED DEFAULT 1;
21
22
23WHILE i < 1200 DO                    
24    INSERT INTO Laptopy (model, producent, procesor, matryca, typ) VALUES ( SUBSTRING(MD5(RAND()) FROM 1 FOR 10) , (SELECT ID FROM Producenci ORDER BY RAND() LIMIT 1), (SELECT ID FROM Procesory ORDER BY RAND() LIMIT 1), (Select ID FROM Matryce ORDER BY RAND() LIMIT 1), (SELECT typ FROM Laptopy GROUP BY typ ORDER BY RAND() LIMIT 1) );
25    SET i = i + 1;
26END WHILE;
27END //
28DELIMITER ;
29
30
315. SELECT Laptopy.model, Matryce.przekatna FROM Laptopy INNER JOIN Matryce ON matryca = ID WHERE PRODUCENT = 6 AND przekatna =( SELECT MAX(Matryce.przekatna) FROM Laptopy INNER JOIN Matryce ON matryca = ID WHERE PRODUCENT = 6);
32
33
34DELIMITER //
35CREATE PROCEDURE L5(IN x int)
36 BEGIN
37 SELECT Laptopy.model 
38 FROM Laptopy INNER JOIN Matryce ON matryca = ID
39 WHERE PRODUCENT = x AND przekatna =( SELECT MAX(Matryce.przekatna) FROM Laptopy INNER JOIN Matryce ON matryca = ID WHERE PRODUCENT = x);
40 END //
41DELIMITER ;
42
436.
44DELIMITER //
45CREATE TRIGGER zad6 
46BEFORE INSERT ON Laptopy 
47FOR EACH ROW
48BEGIN
49IF NOT EXISTS (SELECT 1 FROM Producenci WHERE ID = NEW.producent) THEN
50    INSERT INTO Producenci (ID)
51    VALUES (NEW.producent);
52END IF;
53END //
54DELIMITER ;
55
56
577.SELECT COUNT(Laptopy.model) AS "Ilosc" FROM Laptopy INNER JOIN Procesory ON procesor = ID WHERE Procesory.ID = 3015;
58
59DELIMITER //
60CREATE FUNCTION L7(PID INT) RETURNS INT
61DETERMINISTIC
62BEGIN
63 DECLARE ILOSC INT DEFAULT 0;
64 SELECT COUNT(Laptopy.model) INTO ILOSC FROM Laptopy INNER JOIN Procesory ON procesor = ID WHERE Procesory.ID = PID;
65 RETURN ILOSC;
66END//
67DELIMITER ;
68
69SELECT *, L7(ID) from Procesory;
70
718. 
72DELIMITER //
73CREATE TRIGGER zad8
74 AFTER DELETE ON Laptopy
75 FOR EACH ROW
76 BEGIN 
77  DELETE FROM Procesory WHERE Procesory.ID = old.procesor AND (select count(model) from Laptopy where procesor = old.procesor) = 0;
78
79END;//
80DELIMITER ;
81
829. SELECT Laptopy.model, Laptopy.typ, Procesory.rdzenie, Procesory.czestotliwosc, Matryce.przekatna, Matryce.srgb from Laptopy INNER JOIN Procesory ON procesor = Procesory.ID INNER JOIN Matryce ON matryca = Matryce.ID INNER JOIN Producenci ON producent = Producenci.ID WHERE Producenci.siedziba <> "USA";
83
84CREATE VIEW zad9 AS
85    SELECT Laptopy.model, Laptopy.typ, Procesory.rdzenie, Procesory.czestotliwosc, Matryce.przekatna, Matryce.srgb
86    FROM Laptopy INNER JOIN Procesory ON procesor = Procesory.ID INNER JOIN Matryce ON matryca = Matryce.ID INNER JOIN Producenci ON producent = Producenci.ID
87    WHERE Producenci.siedziba <> "USA";
88    
8910. SELECT Laptopy.model, Producenci.nazwa, Producenci.siedziba from Laptopy INNER JOIN Producenci ON producent = Producenci.ID;
90
91CREATE VIEW zad10 AS
92    SELECT Laptopy.model, Producenci.nazwa, Producenci.siedziba
93    FROM Laptopy INNER JOIN Producenci ON producent = Producenci.ID;
94
95DELETE l.* FROM Laptopy l
96INNER JOIN Producenci p ON l.producent = p.ID
97WHERE (p.siedziba = "Taiwan");