· 6 years ago · May 25, 2019, 02:10 PM
1CREATE TABLE `employees` (
2 `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
3 `FirstName` VARCHAR(30) NOT NULL,
4 `LastName` VARCHAR(5) NOT NULL,
5 `PESEL` INT(11) NOT NULL
6);
7
8CREATE TABLE `regions` (
9 `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
10 `Name` VARCHAR(30)
11 );
12
13CREATE TABLE `agency` (
14 `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
15 `Name` VARCHAR(50) NOT NULL,
16 `RegionId` INT(11) NOT NULL,
17 Provision DECIMAL(3,2) NOT NULL,
18 FOREIGN KEY (RegionId) REFERENCES regions(ID)
19);
20
21CREATE TABLE `agents` (
22 `ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
23 `EmployeeId` INT(11) NOT NULL,
24 `AgencyId` INT(11) NOT NULL,
25 `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
26 FOREIGN KEY (EmployeeId) REFERENCES employees(ID),
27 FOREIGN KEY (AgencyId) REFERENCES agency(ID)
28 );
29
30CREATE TABLE `products` (
31 `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
32 `Name` VARCHAR(255) NOT NULL,
33 `Price` DECIMAL NOT NULL
34);
35
36CREATE TABLE `clients` (
37 `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
38 `Name` VARCHAR(50) NOT NULL
39);
40
41CREATE TABLE `transactions` (
42 `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
43 `ProductId` INT(11) NOT NULL,
44 `ClientId` INT(11) NOT NULL,
45 `AgentId` INT(11) NOT NULL,
46 `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
47 FOREIGN KEY (ProductId) REFERENCES products(ID),
48 FOREIGN KEY (AgentId) REFERENCES agents(ID),
49 FOREIGN KEY (ClientId) REFERENCES clients(ID)
50);
51
52
53
54
55INSERT INTO `regions` (`Name`) VALUES
56 ('pomorskie'),
57 ('kujawsko-pomorskie'),
58 ('lubelskie'),
59 ('lubuskie'),
60 ('warmińsko-mazurskie');
61
62
63INSERT INTO `agency` (`Name`, `RegionId`, `Provision`) VALUES
64 ('Ubezpieka', 1, 20.00),
65 ('Bezpieczne ubezpieczenie', 3, 15.00),
66 ('NoRisk', 4, 30.00),
67 ('Ub3zpiecz0ny', 2, 25.50),
68 ('Ubyzpieczyciele', 5, 27.50);
69
70INSERT INTO `employees` (`FirstName`, `LastName`, `PESEL`) VALUES
71 (`Marian`, `Pazdzioch`, `53121042957`),
72 (`Ferdynand`, `Kiepski`, `54111542957`),
73 (`Halina`, `Kiepska`, `581210420679),
74 (`Waldemar`, `Kiepski`, `78050691334`),
75 (`Arnold`, `Boczek`, `65072412354`);
76
77INSERT INTO `agents` (`EmployeeId`, `AgencyId`) VALUES
78 (1, 3),
79 (2,5),
80 (3,1),
81 (4,2),
82 (5,4);
83
84INSERT INTO `products` (`Name`, `Price`) VALUES
85 (`Ubezpieczenie OC`, 850.00),
86 (`Ubezpieczenie AC`, 1850.00),
87 (`Ubezpieczenie OC + AC`, 2500.00),
88 (`Ubezpieczenie NNW`, 100.00),
89 (`Ubezpieczenie ASS`, 250.00);
90
91INSERT INTO `clients` (`Name`) VALUES
92 ('Mariola Testowa'),
93 ('Genowefa Pigwa'),
94 ('Teodor Motor'),
95 ('Mariusz Kajetanowicz'),
96 ('Oliwia Oliwska');
97
98INSERT INTO `transactions` (`ProductId`, `ClientId`, `AgentId`) VALUES
99 (1, 3, 5),
100 (2, 1, 2),
101 (3, 2, 1),
102 (3, 1, 3),
103 (4, 4, 4),
104 (5, 4, 2),
105 (5, 2, 5),
106 (2, 3, 4);
107
108
109
110
111DROP TABLE IF EXISTS `regions`;
112DROP TABLE IF EXISTS `agency`;
113DROP TABLE IF EXISTS `employees`;
114DROP TABLE IF EXISTS `agents`;
115DROP TABLE IF EXISTS `clients`;
116DROP TABLE IF EXISTS `products`;
117DROP TABLE IF EXISTS `transactions`;
118
119
120SELECT * FROM `transactions` WHERE `Client` != 2;
121SELECT * FROM `products` WHERE `Price` > 800.00;
122
123SELECT count(*) FROM `transactions` GROUP BY `AgentId`;
124SELECT SUM(*) FROM `transactions`;
125
126SELECT * FROM `employees` ORDER BY `LastName` ASC;
127SELECT * FROM `transactions` ORDER BY `ID` DESC;
128
129SELECT `transactions`.`ID` as idKlienta, `transactions`.`AgentId` as idAgenta, `products`.`Prize` as cena, `agency`.`Provision` as prowizja
130 FROM `transactions`
131 LEFT JOIN `products` ON `products`.`ID` = `transactions`.`ProductId`
132 LEFT JOIN `agents` ON `agents`.`ID` = `transactions`.`AgentId`
133 LEFT JOIN `agency` ON `agency`.`ID` = `agents`.`AgencyId`;
134
135CREATE VIEW `widoczek` AS SELECT `transactions`.`ID` as idKlienta, `transactions`.`AgentId` as idAgenta, `products`.`Prize` as cena, `agency`.`Provision` as prowizja
136 FROM `transactions`
137 LEFT JOIN `products` ON `products`.`ID` = `transactions`.`ProductId`
138 LEFT JOIN `agents` ON `agents`.`ID` = `transactions`.`AgentId`
139 LEFT JOIN `agency` ON `agency`.`ID` = `agents`.`AgencyId`;