|
| aa1 |
| aa2 |
| aa3 |
SQL Server
1. Назначение SQL Server 7.0 (OLTP, OLAP)
2. Масштабируемость, динамическое самоуправление ресурсами SQL Server 7.0
3. Службы и утилиты администрирования SQL Server 7.0
4. Архитектура “клиент-сервер” СУБД SQL Server 7.0 3
5. Аутентификация и права доступа в SQL Server 7.0
6. Система безопасности SQL Server 7.0
7. Системные базы данных SQL Server 7.0
8. Логическая и физическая архитектура базы данных SQL Server 7.0 (объекты базы данных, файловая структура базы данных) 6
9. Журнал транзакций SQL Server 7.0 7
10. Создание базы данных в SQL Server 7.0 (объяснить синтаксис команды, перечислить способы создания базы данных) 8
11. Создание таблицы в SQL Server 7.0 (объяснить синтаксис команды, перечислить способы создания таблиц) 10
12. Ограничения на значения колонок 11
13. PRIMARY KEY как ограничение на столбец 11
14. FOREIGN KEY как ограничение на столбец 12
15. Уникальность данных как ограничение на столбец 12
16. Определение идентификационной колонки IDENTITY. 13
17. Определение проверочных ограничений и значений по умолчанию 13
18. Индексы 14
19. Составные части SQL (встроенный и интерактивный, DML, DDL) 14
20. Отличие языка SQL от процедурных языков программирования 15
21. Оператор SELECT (объяснить синтаксис команды) 15
22. Агрегирующие функции 16
23. Оператор EXISTS 16
24. Операторы ALL,ANY,BETWEEN,IN 17
25. Вложенные подзапросы 17
26. Связанные подзапросы 18
27. Оператор UNION 18
28. Команды INSERT, UPDATE, DELETE. 19
29. Соединение таблиц с помощью оператора JOIN. 20
30. Управляющие конструкции Transact -SQL. 21
31. Представления 22
32. Хранимые процедуры. 24
33. Триггеры 26
Создание триггера 26
Как работает триггер 26
Применение триггеров 27
Полезные сведения о триггерах 27
Примеры создания триггеров 27
Аналогичный триггер, только реагирующий на удаление 27
34. Курсоры 28
________________________
34. Курсоры
Запрос может возвращать большой объем данных. И клиентское приложение, которое вызывает этот запрос не всегда способно справиться с таким объемом, т.к. для хранения может понадобиться большой объем памяти, решением этой проблемы могут быть курсоры. Курсор представляет собой механизм обмена данными между клиентом и сервером, который позволяет клиентским приложениям работать не с полным набором данных, а лишь с одной, либо несколькими строками. SQL Server 7.0 поддерживает 3 вида курсоров: • Курсоры Transact SQL. Используются в основном внутри триггеров, хранимых процедур или сценариев. Управление курсорами осуществляется средствами Transact SQL. • Курсоры Server’a, действуют на сервере и реализуют программный интерфейс приложений ODBC. Клиент отправляет на сервер запрос на выполнение одной из доступных операций курсора. Запрос обрабатывается сервером и выполняется. • Курсоры клиента. Реализуются на самом клиенте. Курсоры клиента выбирают весь результирующий набор строк из сервера, сохраняют его локально и работают с ним, это позволяет ускорить операцию обработки данных. Один курсор может базироваться на нескольких таблицах. Операция считывания определенных в курсоре данных называется выборкой (fetch) Также курсоры подразделяются на: • Статические (курсоры моментального снимка) – сервер выбирает все данные и сохраняет результирующий набор в системной базе tempdb. За время выборки данных никакие изменения в базовых таблицах невозможны. После считывания данных из базовых таблиц даже если в них будут вносится изменения они не повлияют на содержимое курсора. • Динамические курсоры – отображают изменения в базовых таблицах, т.е. всякий раз при вызове курсора будет производиться динамическая выборка данных из таблиц. • Последовательные курсоры – считывают данные только от начала к концу, строки считываются из БД, как только они выбираются в курсоре, это позволяет динамически отражать все изменения вносимые пользователем в БД. При работе с курсорами можно выделить следующие основные действия: 1. создание (объявление) курсора – под курсор отводится область памяти в БД. 2. Открытие курсора – наполнение его данными, которые сохраняются в области памяти. 3. Выборка из курсора и изменение строк данных с помощью курсора 4. Закрытие курсора. Освобождение его от данных после чего курсор становится недоступным для других программ. 5. Освобождение курсора, т.е. удаление его из области памяти. Создание курсора: Declare имя_курсора [Insensitive] [Scroll] cursor For Select_Statments [For {Read Only| Update [of имя_столбца [,…]]}] Insensitive – будет создан статический курсор, полный результирующий набор копируется в БД tempdb, через курсор не разрешаются изменения данных и не отображаются изменения сделанные другими пользователями. Если это слово не используется, то создается динамических курсор. Scroll – созданный курсор можно будет прокручивать в любом направлении. ReadOnly – курсор только для чтения, UpDate - позволяет производить изменения. Открытие курсора: Open cursor_name – заполняет курсор данными. Работа с данными: Для выборки из курсора используется команда Fetch FETCH {Next | Prior | First | Last | Absolute n | Relative n} FROM имя_курсора [INTO @имя_переменной [,…n]] First – идет обращение на самую первую строку результирующего набора Last – на самую последнюю строку Next – к следующей строке за текущей Prior – к предыдущей Absolute n – возвращает строку по ее абсолютному номеру в результирующем наборе, еcли n>0 отсчет идет от начала, если n<0 от конца Relative n – возвращает строку находящуюся через n после текущей, еcли n>0 и до текущей, если n<0. INTO @имя_переменной – будут сохраняться значения полей, возвращаемых колонок. Удаление данных: Delete table_name WHERE current of cursor_name Закрытие курсора: Close cursor_name – очищает от данных Освобождение курсора: Deallocate cursor_name – удаляет как объект БД Пример: DECLARE mycursor SCROLL cursor FOR SELECT surname, name, kurs, city ,stipend FROM Student OPEN mycursor DECLARE @surname varchar(20), @name varchar(20), @city varhcar(20), @kurs int, @stipend int, @count int, @str varchar(100) SET @count=1 WHILE @count<<@@cursor_rows BEGIN IF count=1 FETCH ABSOLUTE 1 FROM mycursor INTO @surname, @name, @kurs, @city, @stipend ELSE FETCH NEXT FROM mycursor INTO @surname, @name, @kurs, @city, @stipend @str=@surname+@name+@city PRINT @str PRINT @kurs PRINT @stipend SET @count=@count+1 CONTINUE END CLOSE mycursor DEALLOCATE mycursor ПРИМЕРЫ РЕШЕНИЯ ЗАДАЧ
• Добавили учетную запись на сервер Exec sp_addlogin 'SP', 'SP', 'My_DB'
• Удалить пользователя сервера Exec sp_addlogin 'SP2', 'SP2', 'My_DB' EXEC sp_droplogin 'SP2'
• Изменить пароль учетной записи sp_password 'SP','SP_NEW','SP'
• Запретить/разрешить доступ учетной записи на сервер sp_denylogin 'HOME-B4F551E03C\SP3' sp_grantlogin 'HOME-B4F551E03C\SP3'
• Осуществить просмотр имен пользователей sp_helplogins
• Создали базу данных на сервере, так чтобы ее файлы хранились (для последующих занятий) в созданном вами персональном каталоге; CREATE DATABASE My_DB2 ON ( NAME = My_DB2, FILENAME = 'C:\My_SQL_DB\My_DB2.mdf', SIZE = 10, MAXSIZE = 100, FILEGROWTH = 5 ) LOG ON ( NAME = 'My_DB2_log', FILENAME = 'C:\My_SQL_DB\My_DB2_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )
• Создать пользователя базы данных USE My_DB2 Exec sp_adduser 'SP','SP_DB'
• С помощью команды CREATE TABLE создайте запросы для формирования таблиц учебной базы данных с указанием первичных ключей, но без указания внешних ключей. USE My_DB
-- Создание таблицы студентов CREATE TABLE Student ( Student_id INT NOT NULL, Surname VARCHAR(20) NOT NULL, Name_ VARCHAR(10) NOT NULL, Stipend INT NOT NULL, Kurs INT NOT NULL, City VARCHAR(15) NOT NULL, Birthday DATETIME NOT NULL, Univ_id INT NOT NULL, PRIMARY KEY (Student_id) )
-- Создание таблицы преподователей CREATE TABLE Lecturer ( Lecturer_id INT NOT NULL, Surname VARCHAR(20) NOT NULL, Name_ VARCHAR(10) NOT NULL, City VARCHAR(15) NOT NULL, Univ_id INT NOT NULL, PRIMARY KEY (Lecturer_id) )
-- Создание таблицы предметов CREATE TABLE Subject ( Subj_id INT NOT NULL, Subj_name VARCHAR(30) NOT NULL, Hour_ INT NOT NULL, Semester INT NOT NULL, PRIMARY KEY (Subj_id) )
-- Создание таблицы университетов CREATE TABLE University ( Univ_id INT NOT NULL, Univ_name VARCHAR(30) NOT NULL, Rating INT NOT NULL, City VARCHAR(15) NOT NULL, PRIMARY KEY (Univ_id) )
-- Создание таблицы оценок по экзаменам CREATE TABLE Exam_marks ( Exam_id INT NOT NULL, Student_id INT NOT NULL, Subj_id INT NOT NULL, Mark INT NOT NULL, Exam_date DATETIME NOT NULL, PRIMARY KEY (Exam_id,Student_id) )
-- Создание таблицы преподователей и предметов CREATE TABLE Subj_lect ( Lecturer_id INT NOT NULL, Subj_id INT NOT NULL, PRIMARY KEY (Lecturer_id,Subj_id) )
• C помощью команды ALTER TABLE определить для сформированных таблиц (там, где это необходимо):Внешние ключи, Ограничения на значения колонок, Умолчания USE My_DB
-- Таблица студентов ALTER TABLE Student ADD CONSTRAINT SV1 FOREIGN KEY(Univ_id) REFERENCES University(Univ_id) ALTER TABLE Student DROP COLUMN Stipend ALTER TABLE Student ADD Stipend INT DEFAULT 0 check(Stipend >0) NOT NULL
-- Таблица преподователей ALTER TABLE Lecturer ADD CONSTRAINT SV2 FOREIGN KEY(Univ_id) REFERENCES University(Univ_id)
-- Таблица предметов ALTER TABLE Subject ADD CONSTRAINT f3 CHECK (Hour_ >= 0)
-- Таблица университетов ALTER TABLE University ADD CONSTRAINT f4 CHECK (Rating >= 0)
-- Таблица оценок по экзаменам ALTER TABLE Exam_marks ADD CONSTRAINT f5 CHECK ((Mark >= 2) AND (Mark <= 5)), CONSTRAINT SV6 FOREIGN KEY (Student_id) REFERENCES Student(Student_id), CONSTRAINT SV5 FOREIGN KEY (Subj_id) REFERENCES Subject(Subj_id)
-- Таблица предметов и преподователей ALTER TABLE Subj_lect ADD CONSTRAINT SV3 FOREIGN KEY(Lecturer_id) REFERENCES Lecturer(Lecturer_id), CONSTRAINT SV4 FOREIGN KEY (Subj_id) REFERENCES Subject(Subj_id)
1. Предположим, что стипендия всем студентам увеличена на 20%. Напишите запрос к таблице STUDENT, выполняющий вывод номера студента, фамилию студента и величину увеличенной стипендии. Выходные данные упорядочить по значению последнего столбца.
SELECT Student_id , Surname ,stipend*20./100+stipend AS Stipend FROM Student ORDER BY stipend
2. Напишите запрос, выводящий имена и идентификаторы студентов, у которых стипендия совпадает с максимальным значением стипендии для города, в котором живет студент.
SELECT Name_,Student_id FROM Student as A WHERE stipend=(SELECT MAX(stipend) FROM Student as B WHERE A.City=B.City )
3. Напишите запрос с EXISTS, выбирающий сведения обо всех студентах, для которых в том же городе, где живет студент, существуют университеты, в которых он не учится.
SELECT A.* FROM Student AS A WHERE EXISTS ( SELECT city FROM University AS B WHERE A.City=B.City AND A.Univ_id<>B.Univ_id )
4. Напишите запрос, использующий ANY или ALL, выполняющий выборку данных о студентах, у которых в городе их постоянного места жительства нет университета.
SELECT A.*
FROM Student AS A WHERE A.City <> ALL (SELECT B.City FROM University AS B ) 5. Создайте объединение двух запросов, которые выдают значения полей UNIV_NAME, CITY, RATING для всех университетов. Те из них, у которых рейтинг равен или выше 300, должен иметь комментарий ‘Высокий’, все остальные – ‘Низкий’. SELECT Univ_name,City ,Rating, CASE WHEN Rating >300 THEN 'HIGH' WHEN Rating <=300 THEN 'LOW' END AS Koment FROM University
6. Написать запрос, выполняющий вывод количества студентов, имеющих только отличные оценки.
SELECT SUM(Student_id) FROM Student AS A WHERE 5 <= (SELECT MIN(mark) FROM Exam_marks AS B WHERE A.Student_id=B.Student_id )
7. Написать запрос, выводящий список студентов, средняя оценка у которых не превышает 4 баллов.
SELECT Student_id FROM Student AS A WHERE 4 >( SELECT AVG(mark) FROM Exam_marks AS B WHERE A.Student_id=B.Student_id )
8. Написать вывод фамилий преподавателей, учебная нагрузка которых (количество учебных часов) превышает нагрузку преподавателя Николаева.
SELECT A.Surname, B.Hour_ FROM Lecturer AS A,Subject AS B,Subj_lect AS C WHERE A.Lecturer_id=C.Lecturer_id AND B.Subj_id=C.Subj_id AND Hour_ > (SELECT B.Hour_ FROM Lecturer AS A,Subject AS B,Subj_lect AS C WHERE A.Lecturer_id=C.Lecturer_id AND B.Subj_id=C.Subj_id AND A.Surname='Николаев')
9. Написать запрос, выполняющий вывод количества часов занятий, проводимых преподавателем Ивановым.
SELECT SUM(B.Hour_) FROM Lecturer AS A,Subject AS B,Subj_lect AS C WHERE A.Lecturer_id=C.Lecturer_id AND B.Subj_id=C.Subj_id AND A.Surname='Иванов'
10. Написать запрос, выполняющий вывод фамилий преподавателей, читающих такие же предметы обучения, что и преподаватель Сорокин.
SELECT DISTINCT A.Surname FROM Lecturer AS A WHERE A.Lecturer_id in (SELECT DISTINCT C.Lecturer_id FROM Lecturer AS A,Subj_lect AS C WHERE C.Subj_id in (SELECT C.Subj_id FROM Lecturer AS A,Subj_lect AS C WHERE A.Surname='Сорокин'AND A.Lecturer_id=C.Lecturer_id ) )
11. Напишите запрос, который выводит суммы баллов всех студентов для каждой даты сдачи экзаменов и представляет результаты в порядке убывания этих сумм.
SELECT B.Exam_date,SUM(B.Mark)AS Sum_ FROM Student AS A,Exam_marks AS B WHERE A.Student_id=B.Student_id GROUP BY B.Exam_date ORDER BY SUM(B.Mark) DESC
12. Напишите запрос, который позволяет вывести имена и идентификаторы всех студентов, для которых точно известно, что они проживают в городе, где нет ни одного университета.
SELECT A.Name_,A.Student_id FROM Student AS A WHERE A.City <> ALL (SELECT B.City FROM University AS B )
13. Напишите запрос, который выбирает данные о названиях университетов, рейтинг которых равен либо превосходит рейтинг такого-то университета.
SELECT A.Univ_name FROM University AS A WHERE A.Rating>=(SELECT B.Rating FROM University AS B WHERE B.Univ_name='BSTU' )
14. Выведите список студентов и преподавателей, живущих в Москве, с соответствующими комментариями ‘Студент’, ’Преподаватель’
SELECT A.Surname, CASE WHEN 1=1 THEN 'Lecturer' END AS Koment FROM Lecturer AS A WHERE A.City='Brest' UNION SELECT B.Surname, CASE WHEN 1=1 THEN 'Student' END AS Koment FROM Student AS B WHERE B.City='Brest'
15. Написать запрос, выполняющий вывод данных об именах и фамилиях студентов, не получивших ни одной отличной оценки
SELECT A.Name_,A.Surname FROM Student AS A WHERE 4 <=(SELECT MAX(mark) FROM Exam_marks AS B WHERE A.Student_id=B.Student_id )
16. Написать запрос, выполняющий вывод имен и фамилий преподавателей, читающих два и более различных предмета обучения.
SELECT DISTINCT A.Surname FROM Lecturer AS A,Subj_lect AS C WHERE A.Lecturer_id=C.lecturer_id AND C.Subj_id <> ANY (SELECT B.Subj_id FROM Subj_lect AS B WHERE B.Lecturer_id=C.lecturer_id )
17. Написать запрос, выполняющих вывод данных о фамилиях преподавателей, проводящих занятия у студентов, обучающихся в университетах с рейтингом , превышающим 200.
SELECT R.Surname FROM Lecturer AS R WHERE R.Lecturer_id in (SELECT F.Lecturer_id FROM Subj_lect AS F WHERE F.Subj_id in (SELECT A.Subj_id FROM Exam_marks AS A WHERE A.Student_id in ( SELECT c.Student_id FROM Student As C WHERE C.Univ_id in (SELECT B.Univ_id FROM University AS B WHERE B.Rating >3 ) ) ) )
18. Написать запрос, выполняющий выборку значений идентификаторов студентов, имеющих такие же оценки, что и студент с идентификатором 12.
SELECT A.Student_id FROM Student AS A,Exam_marks AS C WHERE A.Student_id=C.Student_id AND C.mark in ( SELECT B.mark FROM Exam_marks AS B WHERE B.Student_id=1 )
19. Написать запрос, выполняющий вывод данных об именах и фамилиях студентов, получивших хотя бы одну отличную оценку.
SELECT A.Name_,A.Surname FROM Student AS A WHERE 5 = ( SELECT MAX(mark) FROM Exam_marks AS B WHERE A.Student_id=B.Student_id )
20. Написать запрос, выполняющий вывод количества часов занятий, проводимых преподавателем Ивановым.
SELECT SUM(B.Hour_) FROM Lecturer AS A,Subject AS B,Subj_lect AS C WHERE A.Lecturer_id=C.Lecturer_id AND B.Subj_id=C.Subj_id AND A.Surname='Иванов'
21. Написать команду, которая вводит в таблицу SUBJECT строку для нового предмета обучения под названием “Алгебра”. Читается этот предмет в четвертом семестре, отводится на него 72 часа, ID этого предмета 201.
INSERT INTO Subject VALUES (201,'Algebra', 72, 4)
22. Напишите команду, которая увеличивает на 5% значения всех рейтингов университетов, в которых учатся более 1000 студентов.
UPDATE University SET Rating = Rating + Rating*0.05 WHERE Univ_id IN ( SELECT A.Univ_id FROM University AS A, Student AS B WHERE A.Univ_id=B.Univ_id GROUP BY A.Univ_id HAVING COUNT(B.Student_id)>1000)
23. Напишите команду, удаляющую записи обо всех оценках студентов, среднее значение оценок которых ниже тройки.
DELETE FROM Exam_marks WHERE Student_id in (SELECT Student_id FROM Exam_marks GROUP BY Student_id HAVING 3 > AVG(Mark))
24. Создать представление, позволяющее следить на каждый день сдачи экзаменов за количеством сданных экзаменов, количеством студентов, сдавших эти экзамены и средним баллом.
CREATE VIEW Q4 AS SELECT Exam_date, COUNT(Exam_id) AS Exam_count, COUNT(Student_id) AS Student_count, AVG (Mark) AS Avg_mark FROM Exam_marks GROUP BY Exam_date
25. Создать представление, которое показывает имена и названия сданных предметов для каждого студента CREATE VIEW Q5 AS SELECT A.Surname, C.Subj_name FROM Exam_marks AS B, Student AS A, Subject AS C WHERE B.student_id = A.Student_id AND B.Subj_id = C.Subj_id GROUP BY A.Surname, C.Subj_name
26. Создать представление, отображающее фамилию, имя, балл и дату получения оценки студентов, имеющих самый высокий балл на каждую дату сдачи экзаменов.
CREATE VIEW Q6 AS SELECT B.Surname, B.Name_, A.Mark, A.Exam_date FROM Exam_marks AS A, Student AS B WHERE A.Student_id=B.Student_id AND (Mark in (SELECT MAX(mark) FROM Exam_marks WHERE A.Exam_date = Exam_marks.Exam_date )
27. На основе предыдущего представления, создать новое представление, выводящее фамилии студентов, имеющих самый высокий балл как минимум 3 раза.
CREATE VIEW Q7 AS SELECT Surname FROM Student AS C ,Exam_marks AS D WHERE C.Student_id =D.Student_id AND 3>= (SELECT COUNT(A.Student_id) FROM Exam_marks AS A, Student as B WHERE A.Student_id=B.Student_id AND (Mark in (SELECT Max(Mark) FROM Exam_marks WHERE A.Exam_date = Exam_marks.Exam_date )
28. Создать представление, выводящее фамилии, имена и стипендии студентов, имеющих величину стипендии в пределах от 100 до 600, и позволяющее изменять и вводить значения стипендии только в этом интервале.
CREATE VIEW Q8 AS SELECT Surname, Name_, Stipend FROM Student WHERE Stipend BETWEEN 100 AND 600 WITH CHECK OPTION
29. Средствами команд T-SQL введите запись для нового студента, которого зовут Орлов Николай, обучающегося на первом курсе БГТУ, живущего в Бресте, сведенья о дате рождения и текущей стипендии неизвестны.
INSERT INTO student (surname, name, grants, course, city, birthday, univ_id) VALUES ('Orlov', 'Nicolaj', null, 1, 'Brest', null, 1)
30. Напишите команду, удаляющую из таблицы Subject сведенья о предметах обучения, по которым студентами не получено ни одной оценки.
DELETE FROM subject WHERE subj_id not in (SELECT A.Subj_id FROM Exam_marks AS A, Subject AS B WHERE A.Subj_id=B.Subj_id )
31. Напишите запрос, увеличивающий данные о величине стипендии на 20% всем студентам, у которых сумма баллов превышает значение 50.
UPDATE Student SET Stipend=Stipend+Stipend*0.2 WHERE Student_id in(SELECT A.Student_id FROM Student AS A, Exam_marks AS B WHERE A.Student_id = B.Student_id GROUP BY A.Student_id HAVING SUM(B.Mark)>50)
32. Создать представление, отображающее на каждый день сдачи экзаменов список предметов, по которым проходили экзамены, количеством студентов, сдавших экзамены, средним баллом по каждому экзамену.
CREATE VIEW Q4 AS SELECT Exam_date, Subj_name ,COUNT(Student_id) AS student_count, AVG(Mark)AS avg_mark FROM Exam_marks AS A, Subject AS B WHERE A.Subj_id = B.Subj_id GROUP BY Exam_date, B.Subj_name
33. Создать представление, отображающее для каждого предмета список студентов, сдававших этот предмет и оценку, которую тe получили.
CREATE VIEW Q5 as SELECT B.Subj_name, C.Name_, A.Mark FROM Exam_marks AS A, Subject AS B, Student AS C WHERE A.Subj_id=B.Subj_id AND A.Student_id=C.Student_id
34. Создать представление, выводящее фамилии, имена и стипендии студентов, имеющих величину стипендии более 700, и позволяющее изменять и вводить значения стипендии только с учетом этого ограничения.
CREATE VIEW Q6 AS SELECT Surname, Name_, Stipend FROM Student WHERE Stipend>700 WITH CHECK OPTION
35. Создать представление, отображающее фамилию, имя, балл и дату получения оценки студентов, имеющих самый низкий балл на каждую дату сдачи экзаменов
CREATE VIEW Q7 AS SELECT A.Exam_date, B.Surname,B.Name_ ,A.Mark FROM Exam_marks AS A, Student AS B WHERE A.Student_id = B.Student_id AND A.Mark =(SELECT MIN(C.Mark) FROM Exam_marks AS C WHERE A.Exam_date=C.Exam_date)
36. На основе предыдущего представления, создать новое представление, выводящее фамилии студентов, имевших самый низкий балл как минимум 2 раза.
CREATE VIEW Q8 AS SELECT A.Exam_date, B.Surname,B.Name_ ,A.Mark FROM Exam_marks AS A, Student AS B WHERE (A.Student_id = B.Student_id) AND (2>= (SELECT COUNT(D.Mark) FROM Exam_marks AS D WHERE A.Exam_date=D.Exam_date AND B.Student_id=D.Student_id)) AND (A.Mark =(SELECT MIN(C.Mark) FROM Exam_marks AS C WHERE A.Exam_date=C.Exam_date AND B.Student_id=C.Student_id))
37. Написать хранимую процедуру, которая осуществляет по окончании летней сессии перевод всех студентов таблицы STUDENT, на следующий курс, а данные студентов, окончивших пятый курс удаляет из базы данных.
CREATE PROCEDURE proced1 AS Begin Begin Update Student Set kurs=(kurs+1) End Begin Delete Student Where (kurs>5) End End GO exec proced1
38. Написать хранимую процедуру, осуществляющую увеличение стипендии всем студентам на заданный процент.
CREATE PROCEDURE proced2 @perc int AS Begin Update Student Set Stipend=(Stipend+Stipend*@perc/100) End GO exec proced2 10
39. Написать хранимую процедуру, возвращающую фамилию студента по заданному ID.
CREATE PROCEDURE proced3 @id_st int, @sur_st varchar(20) output AS Begin Select @sur_st=surname from Student where student_id=@id_st End GO declare @sur varchar(20) exec proced3 4, @sur_st=@sur output print @sur
40. Написать хранимую процедуру, осуществляющую вставку в таблицу Subject новую запись.
CREATE PROCEDURE proced4 @Subj_id int, @subj_name varchar(30), @hour_ int, @semestr int AS Begin Insert into Subject Values (@Subj_id, @subj_name, @hour_, @semestr) End GO exec proced4 5,"ПОАРМ",2,4
41. Написать хранимую процедуру, осуществляющую вывод количества студентов, учащихся в заданном университете.
CREATE PROCEDURE proced5 @Univ int, @Kol int output AS Begin Select @Kol=count(Univ_id) from Student where Univ_id=@Univ End GO declare @Col varchar(20) exec proced5 1, @Kol=@Col output print @Col
42. Написать триггеры осуществляющие каскадные изменения в таблицах Studnet, Exam_marks. (Т.е. Student – главная таблица, Exam_marks –зависимая таблица. Если из главной таблицы будет удалена запись, то и ссылающиеся записи из зависимой таблицы тоже должны быть удалены. Аналогично, при вставке в главную таблицу новых записей, в зависимую таблицу должны вставляться соответствующие значения. Попытка вставки в зависимую таблицу значений, которые не присутствуют в главной должна отвергаться )
Create trigger insert_trigger On Student FOR Insert AS Begin If exists (Select student_id from inserted) begin Insert into Exam_marks (Student_id) Select Student_id From inserted end end Create trigger delete_trigger On Student FOR Delete AS If exists (Select Student_id from deleted) begin Delete From Exam_marks Where Student_id in (Select Student_id From deleted) end Create trigger insert2_trigger ON Exam_mark For Insert AS If exists (Select Student_id from inserted) begin If NOT exists(Select a.Student_id from Student a , inserted b Where a.student_id=b.student_id) begin ROLLBACK TRANSACTION end end
43. Написать хранимую процедуру, которая осуществляет заменить старый заданный Univ_id в таблице Student на новое значение. Если такого значения в таблице University не существует, то новое значение должно равняться NULL.
CREATE PROCEDURE proced1 @un1 smallint, @un2 smallint AS If exists (Select Univ_id From University Where Univ_id=@un2) begin Update Student Set univ_id=@un2 Where univ_id=@un1 End else begin Update Student Set univ_id=NULL Where univ_id=@un1 End exec proced1 2,3
44. Написать хранимую процедуру, удаляющую сведения о студентах, не сдавших сессию.
CREATE PROCEDURE proced2 AS Delete Student Where Student_id in (Select Student_id from Exam_marks Where Mark=2) exec proced2
45. Написать хранимую процедуру, возвращающую фамилию преподавателя по заданному ID.
CREATE PROCEDURE proced3 @id smallint, @surname varchar(20) output as Select @surname=surname From Lecturer Where Lecturer_id=@id declare @a varchar(40) exec proced3 1,@surname=@a output print @a
46. Написать хранимую процедуру, осуществляющую вставку в таблицу University новую запись.
CREATE PROCEDURE proced4 @u smallint, @un varchar(40), @r smallint, @c varchar(20) AS Insert into University Values (@u,@un,@r,@c) exec proced4 4,"xxx",300,"Brest"
47. Написать хранимую процедуру, осуществляющую вывод список и количество ,преподавателей работающих в заданном университете.
CREATE PROCEDURE proced2_5 @univ int, AS begin begin Select Surname,name_ from Lecturer Where Univ_id=@univ end begin Select Count(univ_id) from Lecturer Where univ_id=@univ end end exec proced2_5 1
48. Написать триггеры осуществляющие каскадные изменения в таблицах Lecturer, Subj_lect. (Т.е. Lecturer – главная таблица, Subj_lect –зависимая таблица. Если из главной таблицы будет удалена запись, то и ссылающиеся записи из зависимой таблицы тоже должны быть удалены. Аналогично, при вставке в главную таблицу новых записей, в зависимую таблицу должны вставляться соответствующие значения. Попытка вставки в зависимую таблицу значений, которые не присутствуют в главной должна отвергаться )
Create trigger insert_trigger On Lecturer FOR Insert AS If exists (Select lecturer_id from inserted) begin Insert into Subj_Lect (lecturer_id) Select lecturer_id From inserted end Create trigger delete_trigger On Lecturer FOR Delete AS If exists (Select lecturer_id from deleted) begin Delete From Subj_Lect Where lecturer_id in (Select lecturer_id From deleted) end
Create trigger ins_tr2 ON Subj_Lect For Insert AS If exists (Select lecture_id from inserted) begin If NOT exists(Select a.lecturer_id from Lecturer a , inserted b Where a.lecturer_id=b.lecture_id) begin ROLLBACK TRANSACTION end end
| bbb |
| bb1 |
| bb2 |
| bb3 |