· 5 years ago · Apr 21, 2020, 07:20 PM
1USE [master]
2GO
3/****** Object: Database [Sypachov] Script Date: 13.04.2020 20:10:57 ******/
4CREATE DATABASE [Sypachov]
5 CONTAINMENT = NONE
6 ON PRIMARY
7( NAME = N'Sypachov', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Sypachov.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
8 LOG ON
9( NAME = N'Sypachov_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Sypachov_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
10GO
11ALTER DATABASE [Sypachov] SET COMPATIBILITY_LEVEL = 120
12GO
13IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
14begin
15EXEC [Sypachov].[dbo].[sp_fulltext_database] @action = 'enable'
16end
17GO
18ALTER DATABASE [Sypachov] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [Sypachov] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [Sypachov] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [Sypachov] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [Sypachov] SET ARITHABORT OFF
27GO
28ALTER DATABASE [Sypachov] SET AUTO_CLOSE OFF
29GO
30ALTER DATABASE [Sypachov] SET AUTO_SHRINK OFF
31GO
32ALTER DATABASE [Sypachov] SET AUTO_UPDATE_STATISTICS ON
33GO
34ALTER DATABASE [Sypachov] SET CURSOR_CLOSE_ON_COMMIT OFF
35GO
36ALTER DATABASE [Sypachov] SET CURSOR_DEFAULT GLOBAL
37GO
38ALTER DATABASE [Sypachov] SET CONCAT_NULL_YIELDS_NULL OFF
39GO
40ALTER DATABASE [Sypachov] SET NUMERIC_ROUNDABORT OFF
41GO
42ALTER DATABASE [Sypachov] SET QUOTED_IDENTIFIER OFF
43GO
44ALTER DATABASE [Sypachov] SET RECURSIVE_TRIGGERS OFF
45GO
46ALTER DATABASE [Sypachov] SET DISABLE_BROKER
47GO
48ALTER DATABASE [Sypachov] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
49GO
50ALTER DATABASE [Sypachov] SET DATE_CORRELATION_OPTIMIZATION OFF
51GO
52ALTER DATABASE [Sypachov] SET TRUSTWORTHY OFF
53GO
54ALTER DATABASE [Sypachov] SET ALLOW_SNAPSHOT_ISOLATION OFF
55GO
56ALTER DATABASE [Sypachov] SET PARAMETERIZATION SIMPLE
57GO
58ALTER DATABASE [Sypachov] SET READ_COMMITTED_SNAPSHOT OFF
59GO
60ALTER DATABASE [Sypachov] SET HONOR_BROKER_PRIORITY OFF
61GO
62ALTER DATABASE [Sypachov] SET RECOVERY SIMPLE
63GO
64ALTER DATABASE [Sypachov] SET MULTI_USER
65GO
66ALTER DATABASE [Sypachov] SET PAGE_VERIFY CHECKSUM
67GO
68ALTER DATABASE [Sypachov] SET DB_CHAINING OFF
69GO
70ALTER DATABASE [Sypachov] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
71GO
72ALTER DATABASE [Sypachov] SET TARGET_RECOVERY_TIME = 0 SECONDS
73GO
74ALTER DATABASE [Sypachov] SET DELAYED_DURABILITY = DISABLED
75GO
76EXEC sys.sp_db_vardecimal_storage_format N'Sypachov', N'ON'
77GO
78ALTER DATABASE [Sypachov] SET QUERY_STORE = OFF
79GO
80USE [Sypachov]
81GO
82/****** Object: UserDefinedFunction [dbo].[Avg_mark_student] Script Date: 13.04.2020 20:10:57 ******/
83SET ANSI_NULLS ON
84GO
85SET QUOTED_IDENTIFIER ON
86GO
87
88CREATE FUNCTION [dbo].[Avg_mark_student]
89(
90 -- Add the parameters for the function here
91 @id_stud INT
92)
93RETURNS REAL
94AS
95BEGIN
96 -- Declare the return variable here
97 DECLARE @avg_mark REAL
98
99 -- Add the T-SQL statements to compute the return value here
100 SELECT @avg_mark = AVG(CAST(Mark AS REAL)) FROM Exam WHERE Id_Student = @id_stud
101
102 -- Return the result of the function
103 RETURN @avg_mark
104
105END
106GO
107/****** Object: UserDefinedFunction [dbo].[Fun2] Script Date: 13.04.2020 20:10:57 ******/
108SET ANSI_NULLS ON
109GO
110SET QUOTED_IDENTIFIER ON
111GO
112
113CREATE FUNCTION [dbo].[Fun2]
114(
115 -- Add the parameters for the function here
116 @group VARCHAR(50)
117)
118RETURNS
119@res TABLE (FioStud VARCHAR(50), Avg_Mark REAL)
120AS
121BEGIN
122 INSERT INTO @res SELECT Fio, AVG(CAST(Mark AS REAL))
123 FROM Exam JOIN Student ON Exam.Id_Student = Student.Id_Student
124 WHERE NameGroup = @group GROUP BY FIO
125 RETURN
126END
127GO
128/****** Object: UserDefinedFunction [dbo].[Func2_4] Script Date: 13.04.2020 20:10:57 ******/
129SET ANSI_NULLS ON
130GO
131SET QUOTED_IDENTIFIER ON
132GO
133CREATE FUNCTION [dbo].[Func2_4]
134(
135 @group VARCHAR(50)
136)
137RETURNS REAL
138AS
139
140BEGIN
141 DECLARE @quant REAL
142 SELECT @quant = (SELECT COUNT(*) FROM Func1(@group))
143 RETURN @quant
144
145END
146GO
147/****** Object: Table [dbo].[Exam] Script Date: 13.04.2020 20:10:57 ******/
148SET ANSI_NULLS ON
149GO
150SET QUOTED_IDENTIFIER ON
151GO
152CREATE TABLE [dbo].[Exam](
153 [Id_Student] [int] NOT NULL,
154 [Subject] [varchar](50) NOT NULL,
155 [Mark] [smallint] NULL,
156 [Exam_Date] [date] NOT NULL,
157 [Id_Lect] [smallint] NOT NULL,
158 CONSTRAINT [PK_Exam_1] PRIMARY KEY CLUSTERED
159(
160 [Id_Student] ASC,
161 [Subject] ASC
162)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
163) ON [PRIMARY]
164GO
165/****** Object: Table [dbo].[Student] Script Date: 13.04.2020 20:10:57 ******/
166SET ANSI_NULLS ON
167GO
168SET QUOTED_IDENTIFIER ON
169GO
170CREATE TABLE [dbo].[Student](
171 [Id_Student] [int] NOT NULL,
172 [FIO] [varchar](50) NOT NULL,
173 [Birthday] [date] NOT NULL,
174 [Gender] [char](10) NOT NULL,
175 [NameGroup] [varchar](50) NOT NULL,
176 [Stip] [int] NULL,
177 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
178(
179 [Id_Student] ASC
180)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
181) ON [PRIMARY]
182GO
183/****** Object: UserDefinedFunction [dbo].[Fun1] Script Date: 13.04.2020 20:10:57 ******/
184SET ANSI_NULLS ON
185GO
186SET QUOTED_IDENTIFIER ON
187GO
188
189CREATE FUNCTION [dbo].[Fun1]
190(
191 -- Add the parameters for the function here
192 @group VARCHAR (20)
193)
194RETURNS TABLE
195AS
196RETURN
197(
198 -- Add the SELECT statement with parameter references here
199 SELECT Fio, AVG(CAST(Mark AS REAL)) AS [Средний балл]
200 FROM Exam JOIN Student ON Exam.Id_Student = Student.Id_Student
201 WHERE NameGroup = @group GROUP BY FIO
202)
203GO
204/****** Object: UserDefinedFunction [dbo].[Func1] Script Date: 13.04.2020 20:10:57 ******/
205SET ANSI_NULLS ON
206GO
207SET QUOTED_IDENTIFIER ON
208GO
209CREATE FUNCTION [dbo].[Func1]
210(@group VARCHAR(50))
211RETURNS TABLE
212AS
213RETURN
214(
215 SELECT Subject FROM Exam
216 INNER JOIN Student ON Exam.Id_Student = Student.Id_Student WHERE Student.NameGroup=@group
217 GROUP BY Subject
218)
219GO
220/****** Object: UserDefinedFunction [dbo].[IZ_5_8] Script Date: 13.04.2020 20:10:57 ******/
221SET ANSI_NULLS ON
222GO
223SET QUOTED_IDENTIFIER ON
224GO
225CREATE FUNCTION [dbo].[IZ_5_8]
226(
227 @group VARCHAR(50)
228)
229RETURNS TABLE
230AS
231RETURN
232(
233 SELECT Id_Student, FIO FROM Student WHERE Id_Student IN (SELECT Id_Student FROM Exam WHERE Exam.Mark > 4 GROUP BY Id_Student) AND NameGroup=@group
234)
235
236
237
238
239GO
240/****** Object: UserDefinedFunction [dbo].[IZ58] Script Date: 13.04.2020 20:10:57 ******/
241SET ANSI_NULLS ON
242GO
243SET QUOTED_IDENTIFIER ON
244GO
245CREATE FUNCTION [dbo].[IZ58]
246(
247 @group VARCHAR(50)
248)
249RETURNS TABLE
250AS
251RETURN
252(
253 SELECT Id_Student, FIO FROM Student WHERE Id_Student IN (SELECT Id_Student FROM Exam WHERE Exam.Mark > 4 GROUP BY Id_Student) AND NameGroup=@group
254)
255
256
257
258
259GO
260/****** Object: View [dbo].[ListOfGroup] Script Date: 13.04.2020 20:10:57 ******/
261SET ANSI_NULLS ON
262GO
263SET QUOTED_IDENTIFIER ON
264GO
265CREATE VIEW [dbo].[ListOfGroup]
266AS
267SELECT TOP (100) PERCENT NameGroup AS Группа, Id_Student AS [Шифр студента], FIO AS [Фамилия студента]
268FROM dbo.Student
269ORDER BY Группа, [Фамилия студента]
270
271GO
272/****** Object: View [dbo].[Exam_A-18-06] Script Date: 13.04.2020 20:10:57 ******/
273SET ANSI_NULLS ON
274GO
275SET QUOTED_IDENTIFIER ON
276GO
277CREATE VIEW [dbo].[Exam_A-18-06]
278AS
279SELECT dbo.ListOfGroup.[Фамилия студента] AS Expr1, COUNT(*) AS [Сдано экзаменов], MAX(dbo.Exam.Mark) AS [Лучшая оценка]
280FROM dbo.ListOfGroup INNER JOIN
281 dbo.Exam ON dbo.ListOfGroup.[Шифр студента] = dbo.Exam.Id_Student
282WHERE (dbo.ListOfGroup.Группа = 'А-18-06') AND (dbo.Exam.Mark > 2)
283GROUP BY dbo.ListOfGroup.[Фамилия студента]
284
285GO
286/****** Object: View [dbo].[IZ36] Script Date: 13.04.2020 20:10:57 ******/
287SET ANSI_NULLS ON
288GO
289SET QUOTED_IDENTIFIER ON
290GO
291CREATE VIEW [dbo].[IZ36]
292AS
293SELECT dbo.Student.FIO AS ФИО, dbo.Exam.Id_Student AS [Номер студента], dbo.Exam.Mark AS Оценка, dbo.Student.NameGroup
294FROM dbo.Exam INNER JOIN
295 dbo.Student ON dbo.Exam.Id_Student = dbo.Student.Id_Student
296WHERE (dbo.Exam.Subject = 'Операционные системы')
297GO
298/****** Object: View [dbo].[IZ36B] Script Date: 13.04.2020 20:10:57 ******/
299SET ANSI_NULLS ON
300GO
301SET QUOTED_IDENTIFIER ON
302GO
303CREATE VIEW [dbo].[IZ36B]
304AS
305SELECT COUNT(Оценка) AS Expr1, NameGroup
306FROM dbo.IZ36
307WHERE (Оценка = 5)
308GROUP BY NameGroup
309
310GO
311/****** Object: Table [dbo].[Lecturer] Script Date: 13.04.2020 20:10:57 ******/
312SET ANSI_NULLS ON
313GO
314SET QUOTED_IDENTIFIER ON
315GO
316CREATE TABLE [dbo].[Lecturer](
317 [Id_Lect] [smallint] NOT NULL,
318 [FIO] [varchar](50) NOT NULL,
319 [Stage] [smallint] NULL,
320 [Kafedra] [varchar](50) NOT NULL,
321 CONSTRAINT [PK_Lecturer] PRIMARY KEY CLUSTERED
322(
323 [Id_Lect] ASC
324)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
325) ON [PRIMARY]
326GO
327/****** Object: View [dbo].[LectExam] Script Date: 13.04.2020 20:10:57 ******/
328SET ANSI_NULLS ON
329GO
330SET QUOTED_IDENTIFIER ON
331GO
332CREATE VIEW [dbo].[LectExam] (Код, Фамилия, Стаж)
333AS
334SELECT Id_Lect,FIO,Stage FROM Lecturer WHERE Kafedra = 'ИС'
335AND Id_Lect IN (SELECT DISTINCT Id_Lect FROM Exam)
336GO
337/****** Object: Table [dbo].[ExamNew] Script Date: 13.04.2020 20:10:57 ******/
338SET ANSI_NULLS ON
339GO
340SET QUOTED_IDENTIFIER ON
341GO
342CREATE TABLE [dbo].[ExamNew](
343 [ID_Student] [int] NOT NULL,
344 [Subj] [varchar](30) NOT NULL,
345 [Mark] [int] NULL,
346 [Exam_Date] [date] NOT NULL,
347 [ID_Lect] [int] NOT NULL,
348 CONSTRAINT [PK_ExamNew] PRIMARY KEY CLUSTERED
349(
350 [ID_Student] ASC,
351 [Subj] ASC
352)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
353) ON [PRIMARY]
354GO
355/****** Object: Table [dbo].[ExamTest] Script Date: 13.04.2020 20:10:57 ******/
356SET ANSI_NULLS ON
357GO
358SET QUOTED_IDENTIFIER ON
359GO
360CREATE TABLE [dbo].[ExamTest](
361 [ID_Student] [int] NOT NULL,
362 [Subj] [varchar](30) NOT NULL,
363 [Mark] [int] NULL,
364 [Exam_Date] [date] NOT NULL,
365 CONSTRAINT [PK_ExamNew1] PRIMARY KEY CLUSTERED
366(
367 [ID_Student] ASC,
368 [Subj] ASC
369)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
370) ON [PRIMARY]
371GO
372/****** Object: Table [dbo].[LecturerNew] Script Date: 13.04.2020 20:10:57 ******/
373SET ANSI_NULLS ON
374GO
375SET QUOTED_IDENTIFIER ON
376GO
377CREATE TABLE [dbo].[LecturerNew](
378 [ID_Lect] [int] NOT NULL,
379 [FIO] [varchar](30) NOT NULL,
380 [Stage] [int] NULL,
381 [Kafedra] [varchar](10) NULL,
382PRIMARY KEY CLUSTERED
383(
384 [ID_Lect] ASC
385)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
386) ON [PRIMARY]
387GO
388/****** Object: Table [dbo].[Rating] Script Date: 13.04.2020 20:10:57 ******/
389SET ANSI_NULLS ON
390GO
391SET QUOTED_IDENTIFIER ON
392GO
393CREATE TABLE [dbo].[Rating](
394 [Id_Student] [int] NOT NULL,
395 [avg_mark] [real] NOT NULL,
396 CONSTRAINT [PK_Rating] PRIMARY KEY CLUSTERED
397(
398 [Id_Student] ASC
399)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
400) ON [PRIMARY]
401GO
402/****** Object: Table [dbo].[StudentNew] Script Date: 13.04.2020 20:10:57 ******/
403SET ANSI_NULLS ON
404GO
405SET QUOTED_IDENTIFIER ON
406GO
407CREATE TABLE [dbo].[StudentNew](
408 [ID_Student] [int] NOT NULL,
409 [FIO] [varchar](30) NOT NULL,
410 [Birthday] [date] NULL,
411 [Gender] [char](1) NOT NULL,
412 [NameGroup] [varchar](10) NOT NULL,
413 [Stip] [int] NULL,
414PRIMARY KEY CLUSTERED
415(
416 [ID_Student] ASC
417)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
418) ON [PRIMARY]
419GO
420/****** Object: Table [dbo].[StudGroup] Script Date: 13.04.2020 20:10:57 ******/
421SET ANSI_NULLS ON
422GO
423SET QUOTED_IDENTIFIER ON
424GO
425CREATE TABLE [dbo].[StudGroup](
426 [NameGroup] [varchar](50) NOT NULL,
427 [Course] [smallint] NULL,
428 [Kafedra] [varchar](50) NULL,
429 CONSTRAINT [PK_StudGroup] PRIMARY KEY CLUSTERED
430(
431 [NameGroup] ASC
432)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
433) ON [PRIMARY]
434GO
435/****** Object: Table [dbo].[StudGroupNew] Script Date: 13.04.2020 20:10:57 ******/
436SET ANSI_NULLS ON
437GO
438SET QUOTED_IDENTIFIER ON
439GO
440CREATE TABLE [dbo].[StudGroupNew](
441 [NameGroup] [varchar](10) NOT NULL,
442 [Kurs] [smallint] NULL,
443 [Kafedra] [varchar](10) NULL,
444PRIMARY KEY CLUSTERED
445(
446 [NameGroup] ASC
447)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
448) ON [PRIMARY]
449GO
450/****** Object: Table [dbo].[StudGroupTest] Script Date: 13.04.2020 20:10:57 ******/
451SET ANSI_NULLS ON
452GO
453SET QUOTED_IDENTIFIER ON
454GO
455CREATE TABLE [dbo].[StudGroupTest](
456 [NameGroup] [varchar](10) NOT NULL,
457 [Curator] [int] NULL,
458 [Kurs] [smallint] NULL,
459 [Kafedra] [varchar](10) NULL,
460 [Starosta] [int] NULL,
461PRIMARY KEY CLUSTERED
462(
463 [NameGroup] ASC
464)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
465) ON [PRIMARY]
466GO
467/****** Object: Table [dbo].[SubjectLect] Script Date: 13.04.2020 20:10:57 ******/
468SET ANSI_NULLS ON
469GO
470SET QUOTED_IDENTIFIER ON
471GO
472CREATE TABLE [dbo].[SubjectLect](
473 [ID_Lecturer] [int] NOT NULL,
474 [NameSubject] [varchar](30) NOT NULL,
475 CONSTRAINT [PK_SubjectLect] PRIMARY KEY CLUSTERED
476(
477 [ID_Lecturer] ASC,
478 [NameSubject] ASC
479)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
480) ON [PRIMARY]
481GO
482INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117162, N'Архитектура ЭВМ', 4, CAST(N'2016-01-15' AS Date), 13)
483INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117162, N'БЖД', 4, CAST(N'2016-02-15' AS Date), 13)
484INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117162, N'Операционные системы', 4, CAST(N'2020-04-09' AS Date), 14)
485INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117162, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
486INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117163, N'Операционные системы', NULL, CAST(N'2019-01-12' AS Date), 14)
487INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117163, N'Управление Данными', 5, CAST(N'2019-01-11' AS Date), 13)
488INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117164, N'Операционные системы', 4, CAST(N'2019-01-12' AS Date), 14)
489INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117164, N'Управление Данными', 3, CAST(N'2019-01-11' AS Date), 13)
490INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117165, N'Операционные системы', 5, CAST(N'2019-01-12' AS Date), 14)
491INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117165, N'Управление Данными', 2, CAST(N'2019-01-11' AS Date), 13)
492INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117166, N'Операционные системы', 4, CAST(N'2019-01-12' AS Date), 14)
493INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117166, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
494INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117201, N'Операционные системы', 2, CAST(N'2019-01-12' AS Date), 14)
495INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117201, N'Управление Данными', NULL, CAST(N'2019-01-11' AS Date), 13)
496INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117202, N'Операционные системы', 5, CAST(N'2019-01-12' AS Date), 14)
497INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117202, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
498INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117203, N'Операционные системы', 3, CAST(N'2019-01-12' AS Date), 14)
499INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117203, N'Управление Данными', 3, CAST(N'2019-01-11' AS Date), 13)
500INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117204, N'Операционные системы', 4, CAST(N'2019-01-12' AS Date), 14)
501INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117204, N'Управление Данными', 5, CAST(N'2019-01-11' AS Date), 13)
502INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117205, N'Операционные системы', 5, CAST(N'2019-01-12' AS Date), 14)
503INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117205, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
504INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117301, N'Операционные системы', 4, CAST(N'2019-01-12' AS Date), 14)
505INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117301, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
506INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117302, N'Операционные системы', 4, CAST(N'2019-01-12' AS Date), 14)
507INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117302, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
508INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117303, N'Операционные системы', 4, CAST(N'2019-01-12' AS Date), 14)
509INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117303, N'Управление Данными', 5, CAST(N'2019-01-11' AS Date), 13)
510INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117304, N'Операционные системы', 5, CAST(N'2019-01-12' AS Date), 14)
511INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117304, N'Управление Данными', 3, CAST(N'2019-01-11' AS Date), 13)
512INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117305, N'Операционные системы', 2, CAST(N'2019-01-12' AS Date), 14)
513INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117305, N'Управление Данными', 2, CAST(N'2019-01-11' AS Date), 13)
514INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117401, N'Операционные системы', 3, CAST(N'2019-01-12' AS Date), 14)
515INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117401, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
516INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117402, N'Операционные системы', 5, CAST(N'2019-01-12' AS Date), 14)
517INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117402, N'Управление Данными', 3, CAST(N'2019-01-11' AS Date), 13)
518INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117403, N'Операционные системы', 4, CAST(N'2019-01-12' AS Date), 14)
519INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117403, N'Управление Данными', 5, CAST(N'2019-01-11' AS Date), 13)
520INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117404, N'Операционные системы', 5, CAST(N'2019-01-12' AS Date), 14)
521INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117405, N'Архитектура ЭВМ', 4, CAST(N'2016-01-15' AS Date), 13)
522INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117405, N'Операционные системы', 3, CAST(N'2019-01-12' AS Date), 14)
523INSERT [dbo].[Exam] ([Id_Student], [Subject], [Mark], [Exam_Date], [Id_Lect]) VALUES (117405, N'Управление Данными', 4, CAST(N'2019-01-11' AS Date), 13)
524INSERT [dbo].[Lecturer] ([Id_Lect], [FIO], [Stage], [Kafedra]) VALUES (11, N'Никитин Эдуард Вадимович', 15, N'ПМ')
525INSERT [dbo].[Lecturer] ([Id_Lect], [FIO], [Stage], [Kafedra]) VALUES (12, N'Лукашенко Эрик Иванович', 8, N'ПМ')
526INSERT [dbo].[Lecturer] ([Id_Lect], [FIO], [Stage], [Kafedra]) VALUES (13, N'Шарапов Давид Дмитриевич', 10, N'ИС')
527INSERT [dbo].[Lecturer] ([Id_Lect], [FIO], [Stage], [Kafedra]) VALUES (14, N'Виноградов Сергей Виталиевич', NULL, N'ИС')
528INSERT [dbo].[Lecturer] ([Id_Lect], [FIO], [Stage], [Kafedra]) VALUES (15, N'Батейко Андрей Сергеевич', 9, N'Ин. Яз.')
529INSERT [dbo].[LecturerNew] ([ID_Lect], [FIO], [Stage], [Kafedra]) VALUES (2, N'Вася1', 8, N'ИС')
530INSERT [dbo].[LecturerNew] ([ID_Lect], [FIO], [Stage], [Kafedra]) VALUES (13, N'Вася', 10, N'ИС')
531INSERT [dbo].[Rating] ([Id_Student], [avg_mark]) VALUES (117162, 4)
532INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117162, N'Сыпачев Андрей Константинович', CAST(N'1999-10-11' AS Date), N'М ', N'ИДБ-17-06', 2400)
533INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117163, N'Сичинава Марк Валерьянович', CAST(N'1999-05-30' AS Date), N'М ', N'ИДБ-17-07', 2000)
534INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117164, N'Смирнов Глеб Дмитриевич', CAST(N'1999-11-18' AS Date), N'М ', N'ИДБ-17-07', 2000)
535INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117165, N'Семенов Антон Иванович', CAST(N'1999-01-01' AS Date), N'М ', N'ИДБ-17-07', 4000)
536INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117166, N'Дергачева Ирина Серегеевна', CAST(N'1998-07-13' AS Date), N'Ж ', N'ИДБ-17-07', 2160)
537INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117170, N'Путин Владимир Владимирович', CAST(N'1966-10-11' AS Date), N'М ', N'ИДБ-17-07', 4000)
538INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117201, N'Сулоев Антон Владимирович', CAST(N'1998-07-14' AS Date), N'М ', N'ИДБ-17-06', 2000)
539INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117202, N'Шабалин Дмитрий Иванович', CAST(N'1999-08-21' AS Date), N'М ', N'ИДБ-17-06', 4000)
540INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117203, N'Старостина Алёна Андреевна', CAST(N'1999-05-15' AS Date), N'Ж ', N'ИДБ-17-06', 1500)
541INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117204, N'Пахомов Адрей Сергеевич', CAST(N'1999-01-19' AS Date), N'М ', N'ИДБ-17-06', 2400)
542INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117205, N'Безруков Антон Анатольевич', CAST(N'1998-02-23' AS Date), N'М ', N'ИДБ-17-06', 4000)
543INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117301, N'Твердовская Софья Игоревна', CAST(N'1999-03-11' AS Date), N'Ж ', N'ИДБ-17-05', 2400)
544INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117302, N'Черникова Евгения Дмитриевна', CAST(N'1999-06-06' AS Date), N'Ж ', N'ИДБ-17-05', 4000)
545INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117303, N'Панин Егор Владимирович', CAST(N'1999-05-17' AS Date), N'М ', N'ИДБ-17-05', 1800)
546INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117304, N'Козлов Илья Сергееич', CAST(N'1999-03-19' AS Date), N'М ', N'ИДБ-17-05', 2000)
547INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117305, N'Ланге Никита Андреевич', CAST(N'1998-03-28' AS Date), N'М ', N'ИДБ-17-05', 1500)
548INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117401, N'Лепехин Сергей Николаевич', CAST(N'1999-06-13' AS Date), N'М ', N'А-18-06', 2000)
549INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117402, N'Кудрина Ксения Игоревна', CAST(N'1999-08-08' AS Date), N'Ж ', N'А-18-06', 1500)
550INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117403, N'Коваленок Никита Сергеевич', CAST(N'1999-09-19' AS Date), N'М ', N'А-18-06', 2400)
551INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117404, N'Давлюдова Екатерина Андреевна', CAST(N'1999-12-03' AS Date), N'Ж ', N'А-18-06', 1800)
552INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (117405, N'Синишин Евгений Григорьевич', CAST(N'1998-07-15' AS Date), N'М ', N'А-18-06', 4000)
553INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (119404, N'Романов Павел Евгеньевич', CAST(N'1993-02-05' AS Date), N'м ', N'ИДБ-17-07', 4200)
554INSERT [dbo].[Student] ([Id_Student], [FIO], [Birthday], [Gender], [NameGroup], [Stip]) VALUES (120000, N'Иванов Борис Петрович', CAST(N'1998-01-01' AS Date), N'М ', N'ИДБ-17-06', 1500)
555INSERT [dbo].[StudGroup] ([NameGroup], [Course], [Kafedra]) VALUES (N'А-18-06', 2, N'ПМ')
556INSERT [dbo].[StudGroup] ([NameGroup], [Course], [Kafedra]) VALUES (N'ИДБ-17-05', 3, N'ИС')
557INSERT [dbo].[StudGroup] ([NameGroup], [Course], [Kafedra]) VALUES (N'ИДБ-17-06', 3, N'ИС')
558INSERT [dbo].[StudGroup] ([NameGroup], [Course], [Kafedra]) VALUES (N'ИДБ-17-07', 3, N'ИС')
559/****** Object: Index [UQ__ExamNew__5E3C27AA75BA09DB] Script Date: 13.04.2020 20:10:57 ******/
560ALTER TABLE [dbo].[ExamNew] ADD UNIQUE NONCLUSTERED
561(
562 [ID_Student] ASC,
563 [Exam_Date] ASC
564)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
565GO
566/****** Object: Index [UQ__ExamTest__5E3C27AA08942BD6] Script Date: 13.04.2020 20:10:57 ******/
567ALTER TABLE [dbo].[ExamTest] ADD UNIQUE NONCLUSTERED
568(
569 [ID_Student] ASC,
570 [Exam_Date] ASC
571)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
572GO
573SET ANSI_PADDING ON
574GO
575/****** Object: Index [UQ__Lecturer__C1BEAA5C8E9D185A] Script Date: 13.04.2020 20:10:57 ******/
576ALTER TABLE [dbo].[LecturerNew] ADD UNIQUE NONCLUSTERED
577(
578 [FIO] ASC
579)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
580GO
581SET ANSI_PADDING ON
582GO
583/****** Object: Index [UQ__StudentN__88E0A233691BD682] Script Date: 13.04.2020 20:10:57 ******/
584ALTER TABLE [dbo].[StudentNew] ADD UNIQUE NONCLUSTERED
585(
586 [NameGroup] ASC,
587 [FIO] ASC
588)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
589GO
590ALTER TABLE [dbo].[ExamNew] ADD DEFAULT ((25)) FOR [Mark]
591GO
592ALTER TABLE [dbo].[ExamNew] ADD DEFAULT (getdate()) FOR [Exam_Date]
593GO
594ALTER TABLE [dbo].[ExamTest] ADD DEFAULT ((25)) FOR [Mark]
595GO
596ALTER TABLE [dbo].[ExamTest] ADD DEFAULT (getdate()) FOR [Exam_Date]
597GO
598ALTER TABLE [dbo].[StudentNew] ADD DEFAULT ('m') FOR [Gender]
599GO
600ALTER TABLE [dbo].[StudGroupNew] ADD DEFAULT ((1)) FOR [Kurs]
601GO
602ALTER TABLE [dbo].[StudGroupTest] ADD DEFAULT ((1)) FOR [Kurs]
603GO
604ALTER TABLE [dbo].[Exam] WITH CHECK ADD CONSTRAINT [FK_Exam_Lecturer] FOREIGN KEY([Id_Lect])
605REFERENCES [dbo].[Lecturer] ([Id_Lect])
606GO
607ALTER TABLE [dbo].[Exam] CHECK CONSTRAINT [FK_Exam_Lecturer]
608GO
609ALTER TABLE [dbo].[Exam] WITH CHECK ADD CONSTRAINT [FK_Exam_Student] FOREIGN KEY([Id_Student])
610REFERENCES [dbo].[Student] ([Id_Student])
611GO
612ALTER TABLE [dbo].[Exam] CHECK CONSTRAINT [FK_Exam_Student]
613GO
614ALTER TABLE [dbo].[ExamNew] WITH CHECK ADD FOREIGN KEY([ID_Lect])
615REFERENCES [dbo].[LecturerNew] ([ID_Lect])
616GO
617ALTER TABLE [dbo].[ExamNew] WITH CHECK ADD FOREIGN KEY([ID_Student])
618REFERENCES [dbo].[StudentNew] ([ID_Student])
619GO
620ALTER TABLE [dbo].[ExamTest] WITH CHECK ADD FOREIGN KEY([ID_Student])
621REFERENCES [dbo].[StudentNew] ([ID_Student])
622ON UPDATE CASCADE
623ON DELETE CASCADE
624GO
625ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_StudGroup] FOREIGN KEY([NameGroup])
626REFERENCES [dbo].[StudGroup] ([NameGroup])
627GO
628ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_StudGroup]
629GO
630ALTER TABLE [dbo].[StudentNew] WITH CHECK ADD FOREIGN KEY([NameGroup])
631REFERENCES [dbo].[StudGroupNew] ([NameGroup])
632GO
633ALTER TABLE [dbo].[StudGroupTest] WITH CHECK ADD FOREIGN KEY([Curator])
634REFERENCES [dbo].[LecturerNew] ([ID_Lect])
635ON UPDATE CASCADE
636ON DELETE SET NULL
637GO
638ALTER TABLE [dbo].[StudGroupTest] WITH CHECK ADD FOREIGN KEY([Starosta])
639REFERENCES [dbo].[StudentNew] ([ID_Student])
640ON DELETE SET NULL
641GO
642ALTER TABLE [dbo].[SubjectLect] WITH CHECK ADD FOREIGN KEY([ID_Lecturer])
643REFERENCES [dbo].[LecturerNew] ([ID_Lect])
644GO
645ALTER TABLE [dbo].[ExamNew] WITH CHECK ADD CHECK (([Mark]>(24) AND [Mark]<=(100)))
646GO
647ALTER TABLE [dbo].[ExamTest] WITH CHECK ADD CHECK (([Mark]>(24) AND [Mark]<=(100)))
648GO
649ALTER TABLE [dbo].[LecturerNew] WITH CHECK ADD CHECK (([Stage]<=(80)))
650GO
651ALTER TABLE [dbo].[StudentNew] WITH CHECK ADD CHECK (([Gender]='m' OR [Gender]='f'))
652GO
653ALTER TABLE [dbo].[StudGroupNew] WITH CHECK ADD CHECK (([Kurs]>(0) AND [Kurs]<=(6)))
654GO
655ALTER TABLE [dbo].[StudGroupTest] WITH CHECK ADD CHECK (([Kurs]>(0) AND [Kurs]<=(6)))
656GO
657/****** Object: StoredProcedure [dbo].[beststudents] Script Date: 13.04.2020 20:10:57 ******/
658SET ANSI_NULLS ON
659GO
660SET QUOTED_IDENTIFIER ON
661GO
662CREATE PROCEDURE [dbo].[beststudents]
663AS
664BEGIN
665
666DECLARE mark_cursor CURSOR LOCAL FORWARD_ONLY STATIC
667 FOR SELECT Student.FIO, Student.NameGroup, AVG(Exam.mark) as avg_mark
668FROM Student
669 INNER JOIN Exam ON Student.Id_Student = Exam.Id_Student
670GROUP BY Student.NameGroup, Student.Id_Student, Student.FIO HAVING Student.NameGroup = 'ИДБ-17-07'
671ORDER BY avg_mark DESC
672
673OPEN mark_cursor
674DECLARE @n INT, @Counter INT
675SET @n = 19
676SET @Counter = 0
677
678DECLARE @TOP_STUDENTS TABLE (fio VARCHAR(40), res INT)
679DECLARE @fio VARCHAR(40), @res INT, @Gr VARCHAR(40)
680
681WHILE @Counter < @n
682BEGIN
683
684 SET @Counter = @Counter + 1
685 INSERT INTO @TOP_STUDENTS VALUES (@fio, @res)
686 FETCH NEXT FROM mark_cursor INTO @fio, @Gr, @res
687END
688SET @counter = 0
689SELECT * FROM @TOP_STUDENTS
690CLOSE mark_cursor
691DEALLOCATE mark_cursor
692
693END
694
695EXEC beststudents
696GO
697/****** Object: StoredProcedure [dbo].[NewExam] Script Date: 13.04.2020 20:10:57 ******/
698SET ANSI_NULLS ON
699GO
700SET QUOTED_IDENTIFIER ON
701GO
702-- =============================================
703-- Author: <Author,,Name>
704-- Create date: <Create Date,,>
705-- Description: <Description,,>
706-- =============================================
707CREATE PROCEDURE [dbo].[NewExam]
708 -- Add the parameters for the stored procedure here
709 @id_stud INT, @subject VARCHAR(20), @mark INT, @ex_date DATE, @id_lect INT,
710 @count_exam INT OUTPUT
711AS
712BEGIN
713 -- Insert statements for procedure here
714 INSERT INTO Exam VALUES (@id_stud, @subject, @mark, @ex_date, @id_lect)
715 SELECT @count_exam = COUNT(*) FROM Exam WHERE Id_Student = @id_lect
716
717END
718GO
719/****** Object: StoredProcedure [dbo].[Result] Script Date: 13.04.2020 20:10:57 ******/
720SET ANSI_NULLS ON
721GO
722SET QUOTED_IDENTIFIER ON
723GO
724CREATE PROCEDURE [dbo].[Result]
725 @fio VARCHAR(50), @group VARCHAR(50), @mark INT, @subj VARCHAR(50), @lect VARCHAR(50), @date DATE
726AS
727BEGIN
728 DECLARE @Id_Student INT, @id_Lect INT
729 SELECT @Id_Student = Id_Student FROM Student WHERE FIO = @fio
730 SELECT @id_Lect = id_Lect FROM Lecturer WHERE FIO = @lect
731 IF EXISTS (SELECT * FROM Exam WHERE Subject = @subj AND Id_Student = @Id_Student)
732 UPDATE Exam SET Mark=@mark, Exam_Date=@date, @id_Lect=id_Lect WHERE Id_Student = @Id_Student AND Subject=@subj
733 ELSE
734 INSERT INTO Exam VALUES(@Id_Student, @subj, @mark, @date, @id_Lect)
735END
736GO
737/****** Object: StoredProcedure [dbo].[Take_my_money] Script Date: 13.04.2020 20:10:57 ******/
738SET ANSI_NULLS ON
739GO
740SET QUOTED_IDENTIFIER ON
741GO
742
743CREATE PROCEDURE [dbo].[Take_my_money]
744@Stip INT, @Perc REAL
745AS
746BEGIN
747
748UPDATE Student
749 SET Stip = (@Perc+100)/100*Stip WHERE Id_Student IN (SELECT DISTINCT Id_Student FROM Exam EXCEPT
750 SELECT Id_Student FROM Exam WHERE Mark < 4 OR Mark IS NULL)
751UPDATE STUDENT
752 SET Stip = @Stip WHERE Id_Student IN (SELECT DISTINCT Id_Student FROM Student WHERE Stip IS NULL)
753
754END
755GO
756/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 13.04.2020 20:10:57 ******/
757SET ANSI_NULLS ON
758GO
759SET QUOTED_IDENTIFIER ON
760GO
761CREATE PROCEDURE [dbo].[Transfer]
762 @fio VARCHAR(50), @group VARCHAR(50), @res VARCHAR(60) OUTPUT
763AS
764BEGIN
765 IF EXISTS (SELECT FIO FROM Student WHERE FIO=@fio)
766 BEGIN
767 IF EXISTS (SELECT NameGroup FROM StudGroup WHERE NameGroup=@group)
768 BEGIN
769 UPDATE Student set NameGroup=@group WHERE FIO=@fio
770 SET @res=('Студент переведён в группу: ') + @group
771 END
772 ELSE
773 SET @res=('Группа не найдена')
774 END
775 ELSE
776 SET @res=('Студент не найден')
777END
778
779
780GO
781/****** Object: StoredProcedure [dbo].[Transfer2] Script Date: 13.04.2020 20:10:57 ******/
782SET ANSI_NULLS ON
783GO
784SET QUOTED_IDENTIFIER ON
785GO
786CREATE PROCEDURE [dbo].[Transfer2]
787 @fio VARCHAR(50), @group VARCHAR(50)
788AS
789BEGIN
790 IF EXISTS (SELECT FIO FROM Student WHERE FIO=@fio)
791 BEGIN
792 IF EXISTS (SELECT NameGroup FROM StudGroup WHERE NameGroup=@group)
793 BEGIN
794 UPDATE Student set NameGroup=@group WHERE FIO=@fio
795 PRINT('Студент переведён в группу: ') + @group
796 END
797 ELSE
798 PRINT('Группа не найдена')
799 END
800 ELSE
801 PRINT('Студент не найден')
802END
803
804
805GO
806EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
807Begin DesignProperties =
808 Begin PaneConfigurations =
809 Begin PaneConfiguration = 0
810 NumPanes = 4
811 Configuration = "(H (1[40] 4[20] 2[20] 3) )"
812 End
813 Begin PaneConfiguration = 1
814 NumPanes = 3
815 Configuration = "(H (1 [50] 4 [25] 3))"
816 End
817 Begin PaneConfiguration = 2
818 NumPanes = 3
819 Configuration = "(H (1 [50] 2 [25] 3))"
820 End
821 Begin PaneConfiguration = 3
822 NumPanes = 3
823 Configuration = "(H (4 [30] 2 [40] 3))"
824 End
825 Begin PaneConfiguration = 4
826 NumPanes = 2
827 Configuration = "(H (1 [56] 3))"
828 End
829 Begin PaneConfiguration = 5
830 NumPanes = 2
831 Configuration = "(H (2 [66] 3))"
832 End
833 Begin PaneConfiguration = 6
834 NumPanes = 2
835 Configuration = "(H (4 [50] 3))"
836 End
837 Begin PaneConfiguration = 7
838 NumPanes = 1
839 Configuration = "(V (3))"
840 End
841 Begin PaneConfiguration = 8
842 NumPanes = 3
843 Configuration = "(H (1[56] 4[18] 2) )"
844 End
845 Begin PaneConfiguration = 9
846 NumPanes = 2
847 Configuration = "(H (1 [75] 4))"
848 End
849 Begin PaneConfiguration = 10
850 NumPanes = 2
851 Configuration = "(H (1[66] 2) )"
852 End
853 Begin PaneConfiguration = 11
854 NumPanes = 2
855 Configuration = "(H (4 [60] 2))"
856 End
857 Begin PaneConfiguration = 12
858 NumPanes = 1
859 Configuration = "(H (1) )"
860 End
861 Begin PaneConfiguration = 13
862 NumPanes = 1
863 Configuration = "(V (4))"
864 End
865 Begin PaneConfiguration = 14
866 NumPanes = 1
867 Configuration = "(V (2))"
868 End
869 ActivePaneConfig = 0
870 End
871 Begin DiagramPane =
872 Begin Origin =
873 Top = 0
874 Left = 0
875 End
876 Begin Tables =
877 Begin Table = "ListOfGroup"
878 Begin Extent =
879 Top = 6
880 Left = 38
881 Bottom = 119
882 Right = 228
883 End
884 DisplayFlags = 280
885 TopColumn = 0
886 End
887 Begin Table = "Exam"
888 Begin Extent =
889 Top = 4
890 Left = 308
891 Bottom = 134
892 Right = 482
893 End
894 DisplayFlags = 280
895 TopColumn = 0
896 End
897 End
898 End
899 Begin SQLPane =
900 End
901 Begin DataPane =
902 Begin ParameterDefaults = ""
903 End
904 Begin ColumnWidths = 9
905 Width = 284
906 Width = 1500
907 Width = 1500
908 Width = 1500
909 Width = 1500
910 Width = 1500
911 Width = 1500
912 Width = 1500
913 Width = 1500
914 End
915 End
916 Begin CriteriaPane =
917 Begin ColumnWidths = 12
918 Column = 1440
919 Alias = 900
920 Table = 1170
921 Output = 720
922 Append = 1400
923 NewValue = 1170
924 SortType = 1350
925 SortOrder = 1410
926 GroupBy = 1350
927 Filter = 1350
928 Or = 1155
929 Or = 1350
930 Or = 1350
931 End
932 End
933End
934' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Exam_A-18-06'
935GO
936EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Exam_A-18-06'
937GO
938EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
939Begin DesignProperties =
940 Begin PaneConfigurations =
941 Begin PaneConfiguration = 0
942 NumPanes = 4
943 Configuration = "(H (1[41] 4[25] 2[16] 3) )"
944 End
945 Begin PaneConfiguration = 1
946 NumPanes = 3
947 Configuration = "(H (1 [50] 4 [25] 3))"
948 End
949 Begin PaneConfiguration = 2
950 NumPanes = 3
951 Configuration = "(H (1 [50] 2 [25] 3))"
952 End
953 Begin PaneConfiguration = 3
954 NumPanes = 3
955 Configuration = "(H (4 [30] 2 [40] 3))"
956 End
957 Begin PaneConfiguration = 4
958 NumPanes = 2
959 Configuration = "(H (1 [56] 3))"
960 End
961 Begin PaneConfiguration = 5
962 NumPanes = 2
963 Configuration = "(H (2 [66] 3))"
964 End
965 Begin PaneConfiguration = 6
966 NumPanes = 2
967 Configuration = "(H (4 [50] 3))"
968 End
969 Begin PaneConfiguration = 7
970 NumPanes = 1
971 Configuration = "(V (3))"
972 End
973 Begin PaneConfiguration = 8
974 NumPanes = 3
975 Configuration = "(H (1[56] 4[18] 2) )"
976 End
977 Begin PaneConfiguration = 9
978 NumPanes = 2
979 Configuration = "(H (1 [75] 4))"
980 End
981 Begin PaneConfiguration = 10
982 NumPanes = 2
983 Configuration = "(H (1[66] 2) )"
984 End
985 Begin PaneConfiguration = 11
986 NumPanes = 2
987 Configuration = "(H (4 [60] 2))"
988 End
989 Begin PaneConfiguration = 12
990 NumPanes = 1
991 Configuration = "(H (1) )"
992 End
993 Begin PaneConfiguration = 13
994 NumPanes = 1
995 Configuration = "(V (4))"
996 End
997 Begin PaneConfiguration = 14
998 NumPanes = 1
999 Configuration = "(V (2))"
1000 End
1001 ActivePaneConfig = 0
1002 End
1003 Begin DiagramPane =
1004 Begin Origin =
1005 Top = 0
1006 Left = 0
1007 End
1008 Begin Tables =
1009 Begin Table = "Exam"
1010 Begin Extent =
1011 Top = 15
1012 Left = 43
1013 Bottom = 188
1014 Right = 213
1015 End
1016 DisplayFlags = 280
1017 TopColumn = 1
1018 End
1019 Begin Table = "Student"
1020 Begin Extent =
1021 Top = 9
1022 Left = 333
1023 Bottom = 187
1024 Right = 507
1025 End
1026 DisplayFlags = 280
1027 TopColumn = 1
1028 End
1029 End
1030 End
1031 Begin SQLPane =
1032 End
1033 Begin DataPane =
1034 Begin ParameterDefaults = ""
1035 End
1036 Begin ColumnWidths = 9
1037 Width = 284
1038 Width = 1500
1039 Width = 1500
1040 Width = 1500
1041 Width = 1500
1042 Width = 1500
1043 Width = 1500
1044 Width = 1500
1045 Width = 1500
1046 End
1047 End
1048 Begin CriteriaPane =
1049 Begin ColumnWidths = 11
1050 Column = 2100
1051 Alias = 1845
1052 Table = 1170
1053 Output = 720
1054 Append = 1400
1055 NewValue = 1170
1056 SortType = 2055
1057 SortOrder = 1890
1058 GroupBy = 1350
1059 Filter = 1350
1060 Or = 1350
1061 Or = 1350
1062 Or = 1350
1063 End
1064 End
1065End
1066' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'IZ36'
1067GO
1068EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'IZ36'
1069GO
1070EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
1071Begin DesignProperties =
1072 Begin PaneConfigurations =
1073 Begin PaneConfiguration = 0
1074 NumPanes = 4
1075 Configuration = "(H (1[40] 4[20] 2[20] 3) )"
1076 End
1077 Begin PaneConfiguration = 1
1078 NumPanes = 3
1079 Configuration = "(H (1 [50] 4 [25] 3))"
1080 End
1081 Begin PaneConfiguration = 2
1082 NumPanes = 3
1083 Configuration = "(H (1 [50] 2 [25] 3))"
1084 End
1085 Begin PaneConfiguration = 3
1086 NumPanes = 3
1087 Configuration = "(H (4 [30] 2 [40] 3))"
1088 End
1089 Begin PaneConfiguration = 4
1090 NumPanes = 2
1091 Configuration = "(H (1 [56] 3))"
1092 End
1093 Begin PaneConfiguration = 5
1094 NumPanes = 2
1095 Configuration = "(H (2 [66] 3))"
1096 End
1097 Begin PaneConfiguration = 6
1098 NumPanes = 2
1099 Configuration = "(H (4 [50] 3))"
1100 End
1101 Begin PaneConfiguration = 7
1102 NumPanes = 1
1103 Configuration = "(V (3))"
1104 End
1105 Begin PaneConfiguration = 8
1106 NumPanes = 3
1107 Configuration = "(H (1[56] 4[18] 2) )"
1108 End
1109 Begin PaneConfiguration = 9
1110 NumPanes = 2
1111 Configuration = "(H (1 [75] 4))"
1112 End
1113 Begin PaneConfiguration = 10
1114 NumPanes = 2
1115 Configuration = "(H (1[66] 2) )"
1116 End
1117 Begin PaneConfiguration = 11
1118 NumPanes = 2
1119 Configuration = "(H (4 [60] 2))"
1120 End
1121 Begin PaneConfiguration = 12
1122 NumPanes = 1
1123 Configuration = "(H (1) )"
1124 End
1125 Begin PaneConfiguration = 13
1126 NumPanes = 1
1127 Configuration = "(V (4))"
1128 End
1129 Begin PaneConfiguration = 14
1130 NumPanes = 1
1131 Configuration = "(V (2))"
1132 End
1133 ActivePaneConfig = 0
1134 End
1135 Begin DiagramPane =
1136 Begin Origin =
1137 Top = 0
1138 Left = 0
1139 End
1140 Begin Tables =
1141 Begin Table = "IZ36"
1142 Begin Extent =
1143 Top = 6
1144 Left = 38
1145 Bottom = 136
1146 Right = 212
1147 End
1148 DisplayFlags = 280
1149 TopColumn = 0
1150 End
1151 End
1152 End
1153 Begin SQLPane =
1154 End
1155 Begin DataPane =
1156 Begin ParameterDefaults = ""
1157 End
1158 Begin ColumnWidths = 9
1159 Width = 284
1160 Width = 1500
1161 Width = 1500
1162 Width = 1500
1163 Width = 1500
1164 Width = 1500
1165 Width = 1500
1166 Width = 1500
1167 Width = 1500
1168 End
1169 End
1170 Begin CriteriaPane =
1171 Begin ColumnWidths = 12
1172 Column = 1440
1173 Alias = 900
1174 Table = 1170
1175 Output = 720
1176 Append = 1400
1177 NewValue = 1170
1178 SortType = 1350
1179 SortOrder = 1410
1180 GroupBy = 1350
1181 Filter = 1350
1182 Or = 1350
1183 Or = 1350
1184 Or = 1350
1185 End
1186 End
1187End
1188' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'IZ36B'
1189GO
1190EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'IZ36B'
1191GO
1192EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
1193Begin DesignProperties =
1194 Begin PaneConfigurations =
1195 Begin PaneConfiguration = 0
1196 NumPanes = 4
1197 Configuration = "(H (1[40] 4[20] 2[20] 3) )"
1198 End
1199 Begin PaneConfiguration = 1
1200 NumPanes = 3
1201 Configuration = "(H (1 [50] 4 [25] 3))"
1202 End
1203 Begin PaneConfiguration = 2
1204 NumPanes = 3
1205 Configuration = "(H (1 [50] 2 [25] 3))"
1206 End
1207 Begin PaneConfiguration = 3
1208 NumPanes = 3
1209 Configuration = "(H (4 [30] 2 [40] 3))"
1210 End
1211 Begin PaneConfiguration = 4
1212 NumPanes = 2
1213 Configuration = "(H (1 [56] 3))"
1214 End
1215 Begin PaneConfiguration = 5
1216 NumPanes = 2
1217 Configuration = "(H (2 [66] 3))"
1218 End
1219 Begin PaneConfiguration = 6
1220 NumPanes = 2
1221 Configuration = "(H (4 [50] 3))"
1222 End
1223 Begin PaneConfiguration = 7
1224 NumPanes = 1
1225 Configuration = "(V (3))"
1226 End
1227 Begin PaneConfiguration = 8
1228 NumPanes = 3
1229 Configuration = "(H (1[56] 4[18] 2) )"
1230 End
1231 Begin PaneConfiguration = 9
1232 NumPanes = 2
1233 Configuration = "(H (1 [75] 4))"
1234 End
1235 Begin PaneConfiguration = 10
1236 NumPanes = 2
1237 Configuration = "(H (1[66] 2) )"
1238 End
1239 Begin PaneConfiguration = 11
1240 NumPanes = 2
1241 Configuration = "(H (4 [60] 2))"
1242 End
1243 Begin PaneConfiguration = 12
1244 NumPanes = 1
1245 Configuration = "(H (1) )"
1246 End
1247 Begin PaneConfiguration = 13
1248 NumPanes = 1
1249 Configuration = "(V (4))"
1250 End
1251 Begin PaneConfiguration = 14
1252 NumPanes = 1
1253 Configuration = "(V (2))"
1254 End
1255 ActivePaneConfig = 0
1256 End
1257 Begin DiagramPane =
1258 Begin Origin =
1259 Top = 0
1260 Left = 0
1261 End
1262 Begin Tables =
1263 Begin Table = "Student"
1264 Begin Extent =
1265 Top = 6
1266 Left = 38
1267 Bottom = 136
1268 Right = 212
1269 End
1270 DisplayFlags = 280
1271 TopColumn = 0
1272 End
1273 End
1274 End
1275 Begin SQLPane =
1276 End
1277 Begin DataPane =
1278 Begin ParameterDefaults = ""
1279 End
1280 Begin ColumnWidths = 9
1281 Width = 284
1282 Width = 1500
1283 Width = 1500
1284 Width = 1500
1285 Width = 1500
1286 Width = 1500
1287 Width = 1500
1288 Width = 1500
1289 Width = 1500
1290 End
1291 End
1292 Begin CriteriaPane =
1293 Begin ColumnWidths = 11
1294 Column = 1440
1295 Alias = 900
1296 Table = 1170
1297 Output = 720
1298 Append = 1400
1299 NewValue = 1170
1300 SortType = 1350
1301 SortOrder = 1410
1302 GroupBy = 1350
1303 Filter = 1350
1304 Or = 1350
1305 Or = 1350
1306 Or = 1350
1307 End
1308 End
1309End
1310' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ListOfGroup'
1311GO
1312EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ListOfGroup'
1313GO
1314USE [master]
1315GO
1316ALTER DATABASE [Sypachov] SET READ_WRITE
1317GO