VEHNsnuk

1-- USE master
2-- GO
3
4-- IF NOT EXISTS (
5--     SELECT name
6--         FROM sys.databases
7--         WHERE name = N'MyShop'
8-- )
9
10-- CREATE DATABASE MyShop
11-- GO
12
13-- IF OBJECT_ID('Position', 'U') IS NOT NULL
14-- DROP TABLE Position
15-- GO
16
17-- IF OBJECT_ID('Worker', 'U') IS NOT NULL
18-- DROP TABLE Worker
19-- GO
20
21-- IF OBJECT_ID('WorkPlace', 'U') IS NOT NULL
22-- DROP TABLE WorkPlace
23-- GO
24
25-- IF OBJECT_ID('Cashbox', 'U') IS NOT NULL
26-- DROP TABLE Cashbox
27-- GO
28
29-- IF OBJECT_ID('Purchase', 'U') IS NOT NULL
30-- DROP TABLE Purchase
31-- GO
32
33-- IF OBJECT_ID('Product', 'U') IS NOT NULL
34-- DROP TABLE Product
35-- GO
36
37-- CREATE TABLE Position (
38--     ID_Position int IDENTITY PRIMARY KEY NOT NULL,
39--     Position_Name VARCHAR(20) NOT NULL,
40--     Salary int NOT NULL,
41--     Experience int NOT NULL,
42-- );
43
44-- INSERT INTO Position 
45-- (Position_Name, Salary, Experience)
46-- VALUES
47--  ('Seller', 500, 2),
48--  ('Security', 400, 1),
49--  ('Cashier', 600, 3);
50
51--  SELECT * FROM Position
52
53-- CREATE TABLE Worker(
54--     ID_Worker int IDENTITY PRIMARY KEY NOT NULL,
55--     ID_Position int NOT NULL,
56--     Surname VARCHAR(20) NOT NULL,
57--     First_Name VARCHAR(20) NOT NULL,
58--     Second_Name VARCHAR(20) NOT NULL,
59--     Date_Of_Birth DATE NOT NULL,
60--     Sex VARCHAR(6) NOT NULL,
61-- );
62
63-- INSERT INTO Worker 
64-- (ID_Position, Surname, First_Name, Second_Name, Date_Of_Birth, Sex)
65--  VALUES
66--  (1,'Mikhailovskii', 'Sergei', 'Ivanovich', '2000-07-31', 'Male'),
67--  (1,'Zaycev', 'Bogdan', 'Bogdanovich', '1999-01-01', 'Male'),
68--  (3,'Latun', 'Savely', 'Savelyevich', '2000-02-02', 'Male'),
69--  (3,'Latyshonok', 'Dmitriy', 'Dmitrievich', '2000-04-04', 'Male'),
70--  (3,'Mishuto', 'Dmitriy', 'Dmitrievich', '2000-03-03', 'Male');
71
72-- SELECT * FROM Worker
73
74-- CREATE TABLE Cashbox(
75--     ID_Cashbox int IDENTITY PRIMARY KEY NOT NULL,
76--     Cashbox_Mark VARCHAR(20) NOT NULL,
77-- );
78
79-- INSERT INTO Cashbox 
80-- (Cashbox_Mark)
81-- VALUES
82-- ('Intoteks'),
83-- ('Avtocombi');
84
85-- SELECT * FROM Cashbox
86
87-- CREATE TABLE WorkPlace(
88--     ID_Cashbox int IDENTITY FOREIGN KEY REFERENCES Cashbox(ID_Cashbox) NOT NULL,
89--     ID_Worker int FOREIGN KEY REFERENCES Worker(ID_Worker) NOT NULL,
90-- );
91
92-- INSERT INTO WorkPlace
93-- (ID_Worker)
94-- VALUES
95-- (3),
96-- (4);
97
98-- SELECT * FROM WorkPlace
99
100-- CREATE TABLE Purchase(
101--     ID_Purchase int IDENTITY PRIMARY KEY NOT NULL,
102--     Sum int NOT NULL,
103-- );
104
105-- INSERT INTO Purchase
106-- (Sum)
107-- VALUES 
108-- (120),
109-- (200),
110-- (500);
111
112-- SELECT * FROM Purchase
113
114-- CREATE TABLE Product(
115--     ID_Product int IDENTITY PRIMARY KEY NOT NULL,
116--     Cost int NOT NULL,
117--     Barcode VARCHAR(10) NOT NULL,
118-- );
119
120-- INSERT INTO Product 
121-- (Cost, Barcode)
122-- VALUES
123-- (10, 111111),
124-- (15, 101010),
125-- (5, 110011);
126
127-- ALTER TABLE Worker
128-- DROP COLUMN Date_Of_Birth
129
130-- SELECT * FROM Worker
131
132-- ALTER TABLE Worker
133-- ADD Date_Of_Birth DATE NOT NULL DEFAULT '01-01-2000'
134
135-- SELECT * FROM Worker
136
137-- ALTER TABLE Worker
138-- ADD Date_Of_Supplying DATE NOT NULL DEFAULT '01-01-2019'
139
140-- ALTER TABLE Worker 
141-- ADD FOREIGN KEY (ID_Position) REFERENCES Position(ID_Position)
142
143-- SELECT * FROM Worker
144
145------------------------------------------------------------
146
147Лаб.4
148
149------------------------------------------------------------
150
151-- CREATE TRIGGER Position_Insert ON Position
152-- AFTER INSERT 
153-- AS
154-- BEGIN
155-- INSERT INTO Cashbox (Cashbox_Mark)
156-- VALUES ('Mark_name')
157-- END
158
159-- CREATE TRIGGER Cashbox_insert ON Cashbox
160-- AFTER INSERT
161-- AS
162-- SELECT * FROM Cashbox
163
164------------------------------------------------------------
165
166DECLARE Cashbox_cursor CURSOR
167FOR SELECT * FROM Cashbox
168
169OPEN Cashbox_cursor
170
171
172SELECT @@ROWCOUNT FROM Cashbox
173
174FETCH FROM Cashbox_cursor
175
176
177CLOSE Cashbox_cursor
178
179------------------------------------------------------------
180
181-- INSERT INTO Cashbox (Cashbox_Mark)
182-- VALUES ('Mark_name_1')
183
184
185-- DROP TRIGGER Position_Insert
186
187-- INSERT INTO Position (Position_Name, Salary, Experience)
188-- VALUES 
189-- ('Seller_1', 500, 2)
190
191
192-- SELECT * FROM Cashbox
193
194------------------------------------------------------------
195
196CREATE PROCEDURE CashboxProc 
197AS
198SELECT * FROM Cashbox
199
200EXEC CashboxProc
201
202CREATE PROCEDURE ShowAllTables
203AS
204BEGIN
205    SELECT * FROM Position
206    SELECT * FROM Worker
207    SELECT * FROM Cashbox
208    SELECT * FROM WorkPlace
209    SELECT * FROM Purchase
210END
211
212EXEC ShowAllTables
213
214
215CREATE PROCEDURE InsertPurchase
216AS
217INSERT INTO Purchase (Sum)
218VALUES
219(1000)
220
221EXEC InsertPurchase
222
223CREATE PROCEDURE DeleteSeller
224AS
225DELETE FROM Position
226WHERE Position_Name = 'Seller_1'
227
228EXEC DeleteSeller
229
230------------------------------------------------------------
231
232CREATE FUNCTION dbo.ExFunc()
233RETURNS INT
234AS
235BEGIN
236    DECLARE @maxSalary INT
237    SELECT @maxSalary = MAX(Salary) FROM Position
238    RETURN @maxSalary
239END
240
241SELECT dbo.ExFunc() AS MAX_SALARY