Используемые SQL запросы
INNER JOIN. TeachersGroupsSubjects] AS ON. GroupId] =. GroupId]. INNER JOIN. TableOfGrades] AS ON. TableOfGradeId] =. Id]. INNER JOIN. TableOfGrades] AS ON. TableOfGradeId] =. Id]. INNER JOIN. TableOfGrades] AS ON. Id] =. SemesterId]. INNER JOIN. TableOfGrades] AS ON. Id] =. SemesterId]. WHERE N’Journal' =. TypeOfKnowledgeControl]) AS. INNER JOIN. Students] AS ON. GroupId] =. GroupId]. INNER… Читать ещё >
Используемые SQL запросы (реферат, курсовая, диплом, контрольная)
Для запросов к субд в данном приложении используется расширения языка Linq, который сам формирует необходимый SQL запрос, поэтому в коде нету привычного SQL кода, но тем не менее можно посмотреть созданный SQL запрос. Только внутри них указываются параметры вида «@p__linq__1».
Select запросы:
1) Запрос для выборки Оценок для Таблицы оценок у преподавателя.
SELECT.
[Extent1]. Id] AS [Id],.
[Extent1]. TableOfGradeId] AS [TableOfGradeId],.
[Extent1]. Date] AS [Date],.
[Extent1]. Value] AS [Value],.
[Extent1]. StudentId] AS [StudentId].
FROM [dbo]. TableEntry] AS [Extent1].
INNER JOIN [dbo]. TableOfGrades] AS [Extent2] ON [Extent1]. TableOfGradeId] = [Extent2]. Id].
INNER JOIN [dbo]. Semester] AS [Extent3] ON [Extent2]. SemesterId] = [Extent3]. Id].
INNER JOIN [dbo]. TeachersGroupsSubjects] AS [Extent4] ON [Extent2]. TeachersGroupsSubjectId] = [Extent4]. Id].
WHERE ([Extent4]. GroupId] = '1') AND ([Extent4]. TeacherId] = '1') AND ([Extent4]. SubjectId] = '1') AND ([Extent3]. Number] = '2') AND ([Extent3]. Year] = '2016|2017') AND ([Extent2]. TypeOfKnowledgeControl] = 'Exam').
2) Пример запроса для выборки Студентов для преподавателя что бы составлять таблицы с оценками.
SELECT.
[Filter1]. Id1] AS [Id].
FROM (SELECT [Extent1]. Id] AS [Id1], [Extent2]. GroupId] AS [GroupId1], [Extent2]. SubjectId] AS [SubjectId], [Extent2]. TeacherId] AS [TeacherId], [Extent3]. SemesterId] AS [SemesterId].
FROM [dbo]. Students] AS [Extent1].
INNER JOIN [dbo]. TeachersGroupsSubjects] AS [Extent2] ON [Extent1]. GroupId] = [Extent2]. GroupId].
INNER JOIN [dbo]. TableOfGrades] AS [Extent3] ON [Extent2]. Id] = [Extent3]. TeachersGroupsSubjectId].
WHERE N’Journal' = [Extent3]. TypeOfKnowledgeControl]) AS [Filter1].
INNER JOIN [dbo]. Semester] AS [Extent4] ON [Filter1]. SemesterId] = [Extent4]. Id].
WHERE ([Filter1]. TeacherId] = '1') AND ([Filter1]. GroupId1] = '1') AND ([Filter1]. SubjectId] = '1006') AND ([Extent4]. Number] = '2') AND ([Extent4]. Year] = '2016|2017').
3) Пример запроса для выборки Семестров у студента.
SELECT.
[Extent1]. Id] AS [Id],.
[Extent1]. BeginningDate] AS [BeginningDate],.
[Extent1]. EndDate] AS [EndDate],.
[Extent1]. Number] AS [Number],.
[Extent1]. Year] AS [Year].
FROM [dbo]. Semester] AS [Extent1].
INNER JOIN [dbo]. TableOfGrades] AS [Extent2] ON [Extent1]. Id] = [Extent2]. SemesterId].
INNER JOIN [dbo]. TeachersGroupsSubjects] AS [Extent3] ON [Extent2]. TeachersGroupsSubjectId] = [Extent3]. Id].
INNER JOIN [dbo]. Students] AS [Extent4] ON [Extent3]. GroupId] = [Extent4]. GroupId].
WHERE [Extent4]. Id] = '1'.
4) Пример запроса для выборки названия Дисциплин у студента.
SELECT.
[Extent1]. Id] AS [Id],.
[Extent1]. BeginningDate] AS [BeginningDate],.
[Extent1]. EndDate] AS [EndDate],.
[Extent1]. Number] AS [Number],.
[Extent1]. Year] AS [Year].
FROM [dbo]. Semester] AS [Extent1].
INNER JOIN [dbo]. TableOfGrades] AS [Extent2] ON [Extent1]. Id] = [Extent2]. SemesterId].
INNER JOIN [dbo]. TeachersGroupsSubjects] AS [Extent3] ON [Extent2]. TeachersGroupsSubjectId] = [Extent3]. Id].
INNER JOIN [dbo]. Students] AS [Extent4] ON [Extent3]. GroupId] = [Extent4]. GroupId].
WHERE [Extent4]. Id] = '1'.
5) Пример запроса для выборки Оценок у студента.
SELECT.
[Extent1]. Id] AS [Id],.
[Extent1]. Value] AS [Value],.
[Extent1]. Date] AS [Date],.
[Extent1]. TableOfGradeId] AS [TableOfGradeId],.
[Extent1]. StudentId] AS [StudentId].
FROM [dbo]. TableEntry] AS [Extent1].
INNER JOIN [dbo]. TableOfGrades] AS [Extent2] ON [Extent1]. TableOfGradeId] = [Extent2]. Id].
INNER JOIN [dbo]. Semester] AS [Extent3] ON [Extent2]. SemesterId] = [Extent3]. Id].
INNER JOIN [dbo]. TeachersGroupsSubjects] AS [Extent4] ON [Extent2]. TeachersGroupsSubjectId] = [Extent4]. Id].
WHERE ([Extent1]. StudentId] = @p__linq__0) AND ([Extent4]. SubjectId] = @p__linq__1) AND ([Extent3]. Number] = @p__linq__2) AND ([Extent3]. Year] = @p__linq__3).
Запрос Insert.
1) Создание Студента.
INSERT [dbo]. Students]([Number], [FullName], [YearOfBirth], [GroupId], [Photo], [UserName], [Email]).
VALUES ('12 415 641', 'Кириллов Анатолий Павлович', '1995', '12', NULL, NULL, ' Этот адрес e-mail защищен от спам-ботов. Чтобы увидеть его, у Вас должен быть включен Java-Script ').
SELECT [Id].
FROM [dbo]. Students].
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity ().
2) Создание Группы.
INSERT [dbo]. Groups]([Number], [YearOfAdmission], [Faculty], [Degree], [FormOfStudy], [MonitorId]).
VALUES ('1190', '2014', '2', 'Бакалавриат', 'Очная', NULL).
SELECT [Id].
FROM [dbo]. Groups].
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity ().
Запрос Delete.
1) Удаление Группы.
DELETE [dbo]. Groups].
WHERE ([Id] = '13').
2) Удаление Студента.
DELETE [dbo]. Students].
WHERE ([Id] = @0).
Запрос Update.
1) Обновление Группы.
UPDATE [dbo]. Groups].
SET [Number] = '1110', [YearOfAdmission] = '2012', [Faculty] = 'ФКиО', [Degree] = 'Бакалавриат', [FormOfStudy] = 'Очная', [MonitorId] = NULL.
WHERE ([Id] = '11').
2) Обновление Студента.
UPDATE [dbo]. Groups].
SET [Number] = '1110', [YearOfAdmission] = '2012', [Faculty] = 'ФКиО', [Degree] = 'Бакалавриат', [FormOfStudy] = 'Очная', [MonitorId] = NULL.
WHERE ([Id] = '11').
3) Обновление Оценки у преподавателя в таблице.
UPDATE [dbo]. TableEntry].
SET [Value] = '5'.
WHERE ([Id] = '116').