API tools faq
paste
View difference between Paste ID: TtL05teE and wqFHYT7k
SHOW: | | - or go back to the newest paste.
1
-- Tworzenie tabeli urządzeń OZE
2
3
CREATE TABLE Renewable_Energy_Devices (
4
    device_id NVARCHAR(26) PRIMARY KEY,
5
    device_status INT CHECK (device_status >= 0 AND device_status <= 6),
6
	device_power DECIMAL(18,0),
7
	company_id INT
8
);
9
10
-- Wstawianie danych do tabeli urządzeń OZE
11
INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id)
12
VALUES
13
('00000000000000000000000000',0,	1000,1),
14
('00000000000000000000000001',0,	1000,1),
15
('00000000000000000000000002',0,	1000,1),
16-
('00000000000000000000000003',0,	2000,1),
16+
('00000000000000000000000003',1,	2000,1),
17
('00000000000000000000000004',0,	1000,1),
18
('00000000000000000000000005',0,	1000,1),
19
('00000000000000000000000006',0,	1000,1),
20
('00000000000000000000000007',3,	1000,1),
21
('00000000000000000000000008',0,	5000,1),
22
('00000000000000000000000009',0,	1000,1),
23-
('00000000000000000000000010',0,	1000,1),
23+
('00000000000000000000000010',2,	1000,1),
24
('00000000000000000000000011',0,	1000,1),
25-
('00000000000000000000000012',0,	1000,1),
25+
('00000000000000000000000012',4,	1000,1),
26
('00000000000000000000000013',0,	1000,1),
27
('00000000000000000000000014',0,	1000,1),
28
('00000000000000000000000015',2,	4000,1),
29
('00000000000000000000000016',1,	1000,1),
30
('00000000000000000000000017',0,	1000,1),
31
('00000000000000000000000018',0,	1000,1),
32
('00000000000000000000000019',0,	1000,1),
33-
('00000000000000000000000020',0,	7000,1),
33+
('00000000000000000000000020',6,	7000,1),
34
('00000000000000000000000021',0,	1000,1),
35
('00000000000000000000000022',0,	1000,1),
36
('00000000000000000000000023',0,	1000,1),
37-
('00000000000000000000000024',0,	1000,1),
37+
('00000000000000000000000024',4,	1000,1),
38
('00000000000000000000000025',0,	1000,1),
39
('00000000000000000000000026',0,	1000,1),
40
('00000000000000000000000027',0,	1000,1),
41
('00000000000000000000000028',0,	1000,1),
42
('00000000000000000000000029',0,	1000,1),
43
('00000000000000000000000030',0,	1000,1),
44-
('00000000000000000000000031',0,	8000,1),
44+
('00000000000000000000000031',2,	8000,1),
45
('00000000000000000000000032',0,	1000,1),
46-
('00000000000000000000000033',0,	1000,1),
46+
('00000000000000000000000033',3,	1000,1),
47
('00000000000000000000000034',0,	1000,1),
48
('00000000000000000000000035',0,	1000,1),
49-
('00000000000000000000000036',0,	1000,1),
49+
('00000000000000000000000036',4,	1000,1),
50
('00000000000000000000000037',0,	1000,1),
51
('00000000000000000000000038',0,	1000,1),
52
('00000000000000000000000039',0,	1000,1),
53
('00000000000000000000000040',0,	1000,1),
54-
('00000000000000000000000041',0,	1000,1),
54+
('00000000000000000000000041',5,	1000,1),
55
('00000000000000000000000042',0,	1000,1),
56
('00000000000000000000000043',0,	1000,1),
57-
('00000000000000000000000044',0,	1000,1),
57+
('00000000000000000000000044',4,	1000,1),
58
('00000000000000000000000045',0,	1000,1),
59-
('00000000000000000000000046',0,	9000,1),
59+
('00000000000000000000000046',3,	9000,1),
60
('00000000000000000000000047',0,	1000,1),
61-
('00000000000000000000000048',0,	1000,1),
61+
('00000000000000000000000048',2,	1000,1),
62
('00000000000000000000000049',0,	1000,1),
63
('00000000000000000000000050',3,	7000,1);
64
65
66
-- Tworzenie tabeli zgłoszeń awarii
67
68
CREATE TABLE Device_Failures (
69
    failure_id INT PRIMARY KEY IDENTITY,
70
    device_id NVARCHAR(26),
71
    shift_number INT,
72
    failure_start DATETIME,
73
    failure_end DATETIME,
74
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
75
);
76
77
-- Wstawianie danych do tabeli zgłoszeń awarii
78
79
DECLARE @i INT = 0
80
WHILE @i <= 9
81
BEGIN
82
    INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
83
    VALUES (CONCAT('0000000000000000000000000', @i), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
84
    SET @i = @i + 1
85
END
86
87
DECLARE @j INT = 10
88
WHILE @j <= 50
89
BEGIN
90
    INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
91
    VALUES (CONCAT('000000000000000000000000', @j), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()));
92
    SET @j = @j + 1
93
END
94
95
96
-- Tworzenie tabeli klientów
97
98
CREATE TABLE Customers (
99
    customer_id INT PRIMARY KEY,
100
    name VARCHAR(255),
101
    email VARCHAR(255),
102
    phone VARCHAR(20),
103
    address VARCHAR(255)
104
);
105
106
-- Wstawianie danych do tabeli klientów
107
INSERT INTO Customers (customer_id, name, email, phone, address)
108
VALUES
109
(1, 'John Doe', '[email protected]', '123456789', '123 Main St'),
110
(2, 'Jane Smith', '[email protected]', '987654321', '456 Elm St'),
111
(3, 'Michael Johnson', '[email protected]', '555123456', '789 Oak St'),
112
(4, 'Emily Brown', '[email protected]', '444987654', '321 Pine St'),
113
(5, 'William Wilson', '[email protected]', '789456123', '654 Birch St'),
114
(6, 'Olivia Taylor', '[email protected]', '987123456', '987 Maple St'),
115
(7, 'James Martinez', '[email protected]', '321654987', '159 Cedar St'),
116
(8, 'Emma Anderson', '[email protected]', '666333999', '753 Walnut St'),
117
(9, 'Noah Thomas', '[email protected]', '222333444', '852 Spruce St'),
118
(10, 'Ava Hernandez', '[email protected]', '111222333', '369 Cherry St'),
119
(11, 'Sophia Lopez', '[email protected]', '777888999', '741 Elm St'),
120
(12, 'Matthew Gonzalez', '[email protected]', '222111333', '258 Oak St'),
121
(13, 'Isabella Perez', '[email protected]', '666777888', '951 Pine St'),
122
(14, 'Jacob Robinson', '[email protected]', '999888777', '753 Maple St'),
123
(15, 'Ethan Lee', '[email protected]', '111222333', '852 Cedar St'),
124
(16, 'Oliver Walker', '[email protected]', '444555666', '963 Walnut St'),
125
(17, 'Daniel Hall', '[email protected]', '888777666', '357 Birch St'),
126
(18, 'Liam Young', '[email protected]', '999888777', '159 Spruce St'),
127
(19, 'Charlotte Hernandez', '[email protected]', '333222111', '456 Cherry St'),
128
(20, 'Amelia Moore', '[email protected]', '222333444', '789 Elm St'),
129
(21, 'Benjamin Nelson', '[email protected]', '444555666', '357 Pine St'),
130
(22, 'Lucas Hill', '[email protected]', '111222333', '852 Maple St'),
131
(23, 'Mia King', '[email protected]', '555666777', '963 Cedar St'),
132
(24, 'Harper Baker', '[email protected]', '777888999', '357 Walnut St'),
133
(25, 'Evelyn Adams', '[email protected]', '999888777', '456 Birch St');
134
135
INSERT INTO Customers (customer_id, name, email, phone, address)
136
VALUES (26, 'John Doe', '[email protected]', '123456789', '556 Main St'),
137
       (27, 'Jane Smith', '[email protected]', '987654321', '431 Elm St');
138
139
140
-- Tworzenie tabeli zamówień
141
142
CREATE TABLE Customers_Orders (

143
    customer_order_id INT PRIMARY KEY,

144
	customer_id INT,

145
	customer_order_date_start DATETIME,

146
	customer_order_date_end DATETIME,

147
	customer_order_amount_of_energy DECIMAL(18,0),

148
	FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

149
);

150
151
-- Wstawianie danych do tabeli zamówień

152
153
INSERT INTO Customers_Orders (customer_order_id, customer_id, customer_order_date_start, customer_order_date_end, customer_order_amount_of_energy)

154
VALUES

155
(1, 1, '2024-04-01 08:00:00', '2024-04-01 12:00:00', 100),

156
(2, 1, '2024-04-02 10:00:00', '2024-04-02 14:00:00', 120),

157
(3, 2, '2024-04-03 12:00:00', '2024-04-03 16:00:00', 150),

158
(4, 2, '2024-04-04 09:00:00', '2024-04-04 13:00:00', 130),

159
(5, 3, '2024-04-05 11:00:00', '2024-04-05 15:00:00', 110),

160
(6, 3, '2024-04-06 08:00:00', '2024-04-06 12:00:00', 140),

161
(7, 4, '2024-04-07 10:00:00', '2024-04-07 14:00:00', 125),

162
(8, 4, '2024-04-08 12:00:00', '2024-04-08 16:00:00', 105),

163
(9, 5, '2024-04-09 07:00:00', '2024-04-09 11:00:00', 115),

164
(10, 5, '2024-04-10 11:00:00', '2024-04-10 15:00:00', 135),

165
(11, 6, '2024-04-11 09:00:00', '2024-04-11 13:00:00', 155),

166
(12, 6, '2024-04-12 08:00:00', '2024-04-12 12:00:00', 125),

167
(13, 7, '2024-04-13 10:00:00', '2024-04-13 14:00:00', 145),

168
(14, 7, '2024-04-14 11:00:00', '2024-04-14 15:00:00', 120),

169
(15, 8, '2024-04-15 12:00:00', '2024-04-15 16:00:00', 130),

170
(16, 8, '2024-04-16 08:00:00', '2024-04-16 12:00:00', 110),

171
(17, 9, '2024-04-17 09:00:00', '2024-04-17 13:00:00', 140),

172
(18, 9, '2024-04-18 10:00:00', '2024-04-18 14:00:00', 150),

173
(19, 10, '2024-04-19 08:00:00', '2024-04-19 12:00:00', 120),

174
(20, 10, '2024-04-20 11:00:00', '2024-04-20 15:00:00', 130),

175
(21, 11, '2024-04-21 10:00:00', '2024-04-21 14:00:00', 140),

176
(22, 11, '2024-04-22 12:00:00', '2024-04-22 16:00:00', 160),

177
(23, 12, '2024-04-23 09:00:00', '2024-04-23 13:00:00', 125),

178
(24, 12, '2024-04-24 07:00:00', '2024-04-24 11:00:00', 135),

179
(25, 13, '2024-04-25 11:00:00', '2024-04-25 15:00:00', 115),

180
(26, 13, '2024-04-26 12:00:00', '2024-04-26 16:00:00', 105),

181
(27, 14, '2024-04-27 10:00:00', '2024-04-27 14:00:00', 145),

182
(28, 14, '2024-04-28 08:00:00', '2024-04-28 12:00:00', 125),

183
(29, 15, '2024-04-29 11:00:00', '2024-04-29 15:00:00', 135),

184
(30, 15, '2024-04-30 09:00:00', '2024-04-30 13:00:00', 155),

185
(31, 16, '2024-05-01 08:00:00', '2024-05-01 12:00:00', 125),

186
(32, 16, '2024-05-02 10:00:00', '2024-05-02 14:00:00', 145),

187
(33, 17, '2024-05-03 12:00:00', '2024-05-03 16:00:00', 130),

188
(34, 17, '2024-05-04 09:00:00', '2024-05-04 13:00:00', 140),

189
(35, 18, '2024-05-05 11:00:00', '2024-05-05 15:00:00', 150),

190
(36, 18, '2024-05-06 08:00:00', '2024-05-06 12:00:00', 120),

191
(37, 19, '2024-05-07 10:00:00', '2024-05-07 14:00:00', 110),

192
(38, 19, '2024-05-08 12:00:00', '2024-05-08 16:00:00', 130),

193
(39, 20, '2024-05-09 07:00:00', '2024-05-09 11:00:00', 140),

194
(40, 20, '2024-05-10 11:00:00', '2024-05-10 15:00:00', 150),

195
(41, 21, '2024-05-11 09:00:00', '2024-05-11 13:00:00', 125),

196
(42, 21, '2024-05-12 08:00:00', '2024-05-12 12:00:00', 135),

197
(43, 22, '2024-05-13 10:00:00', '2024-05-13 14:00:00', 115),

198
(44, 22, '2024-05-14 11:00:00', '2024-05-14 15:00:00', 105),

199
(45, 23, '2024-05-15 12:00:00', '2024-05-15 16:00:00', 145),

200
(46, 23, '2024-05-16 08:00:00', '2024-05-16 12:00:00', 125),

201
(47, 24, '2024-05-17 09:00:00', '2024-05-17 13:00:00', 135),

202
(48, 24, '2024-05-18 10:00:00', '2024-05-18 14:00:00', 145),

203
(49, 25, '2024-05-19 08:00:00', '2024-05-19 12:00:00', 155),

204
(50, 25, '2024-05-20 11:00:00', '2024-05-20 15:00:00', 125);

205
206
207
208
-- Tworzenie tabeli firm serwisowych

209
 

210
CREATE TABLE Servicing_Companies (

211
    company_id INT PRIMARY KEY,

212
    name VARCHAR(255),

213
    working_hours VARCHAR(255)

214
);

215
 

216
-- Wstawianie danych do tabeli firm serwisowych

217
 

218
INSERT INTO Servicing_Companies (company_id, name, working_hours)

219
VALUES (1, 'Company Y', '6:00-14:00, 14:00-22:00, 22:00-6:00');

220
 

221
222-
############################

222+
----------

223
ALTER TABLE dbo.Renewable_Energy_Devices

224
ADD CONSTRAINT FK_OZE_SC FOREIGN KEY (company_id) REFERENCES Servicing_Companies(company_id);

225-
############################ 

225+
----------

226
 

227
-- Tworzenie tabeli zmian serwisowych

228
 

229
CREATE TABLE Servicing_Shifts (

230
    shift_number INT PRIMARY KEY IDENTITY,

231
    shift_date DATE,

232
    shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),

233
    start_time TIME,

234
    end_time TIME

235
);

236
 

237
-- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)

238
INSERT INTO Servicing_Shifts (shift_date, shift_type, start_time, end_time) VALUES

239
('2024-04-01', 1, '06:00:00', '14:00:00'),

240
('2024-04-01', 2, '14:00:00', '22:00:00'),

241
('2024-04-01', 3, '22:00:00', '06:00:00'),

242
('2024-04-02', 1, '06:00:00', '14:00:00'),

243
('2024-04-02', 2, '14:00:00', '22:00:00'),

244
('2024-04-02', 3, '22:00:00', '06:00:00'),

245
('2024-04-03', 1, '06:00:00', '14:00:00'),

246
('2024-04-03', 2, '14:00:00', '22:00:00'),

247
('2024-04-03', 3, '22:00:00', '06:00:00'),

248
('2024-04-04', 1, '06:00:00', '14:00:00'),

249
('2024-04-04', 2, '14:00:00', '22:00:00'),

250
('2024-04-04', 3, '22:00:00', '06:00:00'),

251
('2024-04-05', 1, '06:00:00', '14:00:00'),

252
('2024-04-05', 2, '14:00:00', '22:00:00'),

253
('2024-04-05', 3, '22:00:00', '06:00:00'),

254
('2024-04-06', 1, '06:00:00', '14:00:00'),

255
('2024-04-06', 2, '14:00:00', '22:00:00'),

256
('2024-04-06', 3, '22:00:00', '06:00:00'); 

257
 

258
 

259
 

260
-- Tworzenie tabeli napraw

261
 

262
 CREATE TABLE Repairs (

263
    repair_id INT PRIMARY KEY IDENTITY,

264
    device_id NVARCHAR(26),

265
    repair_start DATETIME,

266
    repair_end DATETIME,

267
    repair_description NVARCHAR(255),

268
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)

269
);

270
 

271
-- Wstawianie danych do tabeli napraw  (można kilka razy)

272
 

273
DECLARE @i INT = 0

274
WHILE @i <= 9

275
BEGIN

276
    INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)

277
    VALUES (CONCAT('0000000000000000000000000', @i), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');

278
    SET @i = @i + 1

279
END

280
 

281
DECLARE @j INT = 10

282
WHILE @j <= 50

283
BEGIN

284
    INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)

285
    VALUES (CONCAT('000000000000000000000000', @j), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()), 'Naprawa urządzenia');

286
    SET @j = @j + 1

287-
END 

287+
END
        

    


            


                                


        

            



                
    

        Public Pastes
    

    
            

    

                    

        

    





    


    
    
    
    
    
    
    
    




    


    



                

            We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.             OK, I Understand
        

    
                

            

                
                    
                
            

            

                Not a member of Pastebin yet?

                Sign Up, it unlocks many cool features!