Упражнение БД. Учебная база данных
 Скачать 190.5 Kb.
|
Учебная база данныхВ приводимых в пособии примерах построения SQL-запросов и контрольных упражнениях используется база данных, состоящая из следующих таблиц. student (Студент)
student_id — числовой код, идентифицирующий студента, surname — фамилия студента, name — имя студента, , STIPEND — стипендия, которую получает студент, kurs — курс, на котором учится студент, city —город, в котором живет студент, birthday — дата рождения студента, univ_id — числовой код, идентифицирующий университет, в котором учится студент. LECTURER (Преподаватель)
lecturer__id — числовой код, идентифицирующий преподавателя, SURNAME — фамилия преподавателя, NAME — имя преподавателя, CITY — город, в котором живет преподаватель, UNIV_ID — идентификатор университета, в котором работает преподаватель. university (Университеты)
univ_id — идентификатор университета, univ_name — название университета, rating — рейтинг университета, city — город, в котором расположен университет. exam MARKS (Экзаменационные оценки) SUBJECT (Предмет обучения)
subj_id — идентификатор предмета обучения, subj_name — наименование предмета обучения, HOUR — количество часов, отводимых на изучение предмета, semester — семестр, в котором изучается данный предмет. exam_MARKS (Экзаменационные оценки)
exam_id — идентификатор экзамена, student_id — идентификатор студента, subj_id — идентификатор предмета обучения, mark — экзаменационная оценка, exam date — дата экзамена. subj_lect (Учебные дисциплины преподавателей)
lecturer_id — идентификатор преподавателя, SUBJ_ID — идентификатор предмета обучения. Упражнения Напишите запрос для вывода идентификатора (номера) предмета обучения, его наименования, семестра, в котором он читается, и количества отводимых на этот предмет часов для всех строк таблицы SUBJECT. Напишите запрос, позволяющий вывести все строки таблицы exam_marks, в которых предмет обучения имеет номер (subj_id), равный 12. Напишите запрос, выбирающий все данные из таблицы student, расположив столбцы таблицы в следующем порядке: KURS, surname, NAME, STIPEND. Напишите запрос SELECT, который выводит наименование предмета обучения (subj_name) и количество часов (hour) для каждого предмета (subject) в 4-м семестре (semester). Напишите запрос, позволяющий получить из таблицы exam_marks значения столбца mark (экзаменационная оценка) для всех студентов, исключив из списка повторение одинаковых строк. Напишите запрос, который выводит список фамилий студентов, обучающихся на третьем и последующих курсах. Напишите запрос, выбирающий данные о фамилии, имени и номере курса для студентов, получающих стипендию больше 140. Напишите запрос, выполняющий выборку из таблицы subject названий всех предметов обучения, на которые отводится более 30 часов. Напишите запрос, который выполняет вывод списка университетов, рейтинг которых превышает 300 баллов. Напишите запрос к таблице student для вывода списка фамилий (surname), имен (name) и номера курса (kurs) всех студентов со стипендией, большей или равной 100, и живущих в Воронеже. Какие данные будут получены в результате выполнения запроса? SELECT * FROM STUDENT WHERE (STIPEND < 100 OR NOT (BIRTHDAY >= '10/03/1980' AND STUDENT_ID > 1003)); Какие данные будут получены в результате выполнения запроса? SELECT * FROM STUDENT WHERE SOT ((BIRTHDAY = 10/03/1980' OR STIPEND > 100) AND STUDENT_ID >= 1003); Напишите запрос на вывод находящихся в таблице EXAM_MARKS номеров предметов обучения, экзамены по которым сдавались между 10 и 20 января 1999 года. Напишите запрос, выбирающий данные обо всех предметах обучения, экзамены по которым сданы студентами, имеющими идентификаторы 12 и 32. Напишите запрос на вывод названий предметов обучения, начинающихся на букву «И». Напишите запрос, выбирающий сведения о студентах, у которых имена начинаются на буквы «И» или «С». Напишите запрос для выбора из таблицы exam_marks записей, в которых отсутствуют значения оценок (поле mark). Напишите запрос на вывод из таблицы EXAM__MARKS записей, имеющих в поле mark значения оценок. Составьте запрос для таблицы student таким образом, чтобы выходная таблица содержала один столбец, содержащий последовательность разделенных символом «;» (точка с запятой) значений всех столбцов этой таблицы, и при этом текстовые значения должны отображаться прописными символами (верхний регистр), то есть быть представленными в следующем виде: 10;КУЗНЕЦОВ;БОРИС;0;БРЯНСК;8/12/1981;10. Составьте запрос для таблицы student таким образом, чтобы выходная таблица содержала всего один столбец в следующем виде: Б. КУЗНЕЦОВ; место жительство-Брянск; родился-8.12.81. Составьте запрос для таблицы STUDENT таким образом, чтобы выходная таблица содержала всего один столбец в следующем виде: б. Кузнецов; место жительства - Брянск; родился:8-дек-1981. Составьте запрос для таблицы STUDENT таким образом, чтобы выходная таблица содержала всего один столбец в следующем виде: Борис Кузнецов родился в 1981 году. Вывести фамилии, имена студентов и величину получаемых ими стипендий, при этом значения стипендий должны быть увеличены в 100 раз. То же, что и в задаче 4, но только для студентов 1, 2 и 4-го курсов и таким образом, чтобы фамилии и имена были выведены прописными буквами. Составьте запрос для таблицы university таким образом, чтобы выходная таблица содержала всего один столбец в следующем виде: Код-10; ВГУ-г. ВОРОНЕЖ; Рейтинг=296. То же, что и в задаче 25, но значения рейтинга требуется округлить до первого знака (например, значение 382 округляется до 400). Напишите запрос для подсчета количества студентов, сдававших экзамен по предмету обучения с идентификатором, равным 20. Напишите запрос, который позволяет подсчитать в таблице exam_marks количество различных предметов обучения. Напишите запрос, который выполняет выборку для каждого студента значения его идентификатора и минимальной из полученных им оценок. Напишите запрос, осуществляющий выборку для каждого студента значения его идентификатора и максимальной из полученных им оценок. Напишите запрос, выполняющий вывод фамилии первого в алфавитном порядке (по фамилии) студента, фамилия которого начинается на букву «И». Напишите запрос, который выполняет вывод (для каждого предмета обучения) наименования предмета и максимального значения номера семестра, в котором этот предмет преподается. Напишите запрос, который выполняет вывод данных для каждого конкретного дня сдачи экзамена о количестве студентов, сдававших экзамен в этот день. Напишите запрос для получения среднего балла для каждого курса по каждому предмету. Напишите запрос для получения среднего балла для каждого студента. Напишите запрос для получения среднего балла для каждого экзамена. Напишите запрос для определения количества студентов, сдававших каждый экзамен. Напишите запрос для определения количества изучаемых предметов на каждом курсе. Предположим, что стипендия всем студентам увеличена на 20%. Напишите запрос к таблице student, выполняющий вывод номера студента, фамилию студента и величину увеличенной стипендии. Выходные данные упорядочить: а) по значению последнего столбца (величине стипендии); б) в алфавитном порядке фамилий студентов. Напишите запрос, который по таблице exam_marks позволяет найти а) максимальные и б) минимальные оценки каждого студента и который выводит их вместе с идентификатором студента. Напишите запрос, выполняющий вывод списка предметов обучения в порядке а) убывания семестров и б) возрастания отводимых на предмет часов. Поле семестра в выходных данных должно быть первым, за ним должны следовать имя предмета обучения и идентификатор предмета. Напишите запрос, который выполняет вывод суммы баллов всех студентов для каждой даты сдачи экзаменов и представляет результаты в порядке убывания этих сумм. Напишите запрос, который выполняет вывод а) среднего, б) минимального, в) максимального баллов всех студентов для каждой даты сдачи экзаменов и который представляет результаты в порядке убывания этих значений. Напишите запрос с подзапросом для получения данных обо всех оценках студента с фамилией «Иванов». Предположим, что его персональный номер неизвестен. Всегда ли такой запрос будет корректным? Напишите запрос, выбирающий данные об именах всех студентов, имеющих по предмету с идентификатором 101 балл выше общего среднего балла. Напишите запрос, который выполняет выборку имен всех студентов, имеющих по предмету с идентификатором 102 балл ниже общего среднего балла. Напишите запрос, выполняющий вывод количества предметов, по которым экзаменовался каждый студент, сдававший более 20 предметов. Напишите команду select, использующую связанные подзапросы и выполняющую вывод имен и идентификаторов студентов, у которых стипендия совпадает с максимальным значением стипендии для города, в котором живет студент. Напишите запрос, который позволяет вывести имена и идентификаторы всех студентов, для которых точно известно, что они проживают в городе, где нет ни одного университета. Напишите два запроса, которые позволяют вывести имена и идентификаторы всех студентов, для которых точно известно, что они проживают не в том городе, где расположен их университет. Один запрос с использованием соединения, а другой — с использованием связанного подзапроса. Создайте объединение двух запросов, которые выдают значения полей univ_name, city, rating для всех университетов. Те из них, у которых рейтинг равен или выше 300, должны иметь комментарий 'Высокий', все остальные — 'Низкий'. Напишите команду, которая выдает список фамилий студентов, с комментарием 'успевает' у студентов, имеющих все положительные оценки, комментарием 'не успевает' для сдававших экзамены, но имеющих хотя бы одну неудовлетворительную оценку и комментарием 'не сдавал' — для всех остальных. В выводимом результате фамилии студентов упорядочить по алфавиту. Выведите объединенный список студентов и преподавателей, живущих в Москве, с соответствующими комментариями: 'студент' или 'преподаватель'. Выведите объединенный список студентов и преподавателей Воронежского государственного университета с соответствующими комментариями: 'студент' или 'преподаватель'. Напишите запрос, который выполняет вывод данных о фамилиях сдававших экзамены студентов (вместе с идентификаторами каждого сданного ими предмета обучения). Напишите запрос, который выполняет выборку значений фамилии всех студентов с указанием для студентов, сдававших экзамены, идентификаторов сданных ими предметов обучения. Напишите запрос, который выполняет вывод данных о фамилиях студентов, сдававших экзамены, вместе с наименованиями каждого сданного ими предмета обучения. Напишите запрос на выдачу для каждого студента названий всех предметов обучения, по которым этот студент получил оценку 4 или 5. Напишите запрос на выдачу данных о названиях всех предметов, по которым студенты получили только хорошие (4 и 5) оценки. В выходных данных должны быть приведены фамилии студентов, названия предметов и оценка. Напишите запрос, который выполняет вывод списка университетов с рейтингом, превышающим 300, вместе со значением максимального размера стипендии, получаемой студентами в этих университетах. Напишите запрос на выдачу списка фамилий студентов (в алфавитном порядке) вместе со значением рейтинга университета, где каждый из них учится, включив в список и тех студентов, для которых б базе данных не указано место их учебы. Написать запрос, выполняющий вывод списка всех пар фамилий студентов, проживающих в одном городе. При этом не включать в список комбинации фамилий студентов самих с собой (то есть комбинацию типа «Иванов-Иванов») и комбинации фамилий студентов, отличающиеся порядком следования (то есть включать одну из двух комбинаций типа «Иванов-Петров» и «Петров-Иванов»). Написать запрос, выполняющий вывод списка всех пар названий университетов, расположенных в одном городе, не включая в список комбинации названий университетов самих с собой и пары названий университетов, отличающиеся порядком следования. Написать запрос, который позволяет получить данные о названиях университетов и городов, в которых они расположены, с рейтингом, равным или превышающим рейтинг ВГУ.- Написать запрос, выполняющий вывод данных об именах и фамилиях студентов, получивших хотя бы одну отличную оценку. Написать запрос, выполняющий вывод данных об именах и фамилиях студентов, имеющих весь набор оценок (тройки, четверки и пятерки). Написать запрос, выполняющий выборку значений идентификаторов студентов, имеющих такие же оценки, что и студент с идентификатором 12. Написать запрос, выполняющий выборку всех пар идентификаторов преподавателей, ведущих одинаковые предметы обучения. Написать запрос, выполняющий вывод данных об именах и фамилиях студентов, не получивших ни одной отличной оценки. Написать запрос, выполняющий выборку значений наименований предметов обучения, на преподавание которых отводится более 50 часов. Написать запрос, выполняющий вывод количества студентов, не имеющих ни одной оценки. Написать запрос, выполняющий вывод количества студентов, имеющих только отличные оценки. Написать запрос, выполняющий вывод данных о предметах обучения, которые ведет преподаватель по фамилии Колесников. Написать запрос, выполняющий вывод имен и фамилий преподавателей, проводящих занятия на первом курсе. Написать запрос, выполняющий вывод имен и фамилий студентов, место проживания которых не совпадает с городом, в котором находится их университет. Написать запрос, выполняющий вывод количества экзаменов, сданных (с положительной оценкой) студентом с идентификатором 32. Написать запрос, выполняющий вывод имен и фамилий преподавателей, читающих два и более различных предмета обучения. Написать запрос, выполняющий вывод имен и фамилий преподавателей, проводящих занятия в двух и более семестрах. Написать запрос, выполняющий вывод данных о наименованиях предметов обучения, читаемых двумя и более преподавателями. Написать запрос, выполняющий вывод для каждого предмета обучения, преподаваемого для студентов ВГУ, его наименования, фамилии и имени преподавателя, а также города, в котором живет студент. Написать запрос, выполняющий вывод количества часов занятий, проводимых преподавателем Лагутиным. Написать запрос, выполняющий вывод фамилий преподавателей, читающих такие же предметы обучения, что и преподаватель Сорокин. Написать запрос, выполняющий вывод фамилий преподавателей, учебная нагрузка которых (количество учебных часов) превышает нагрузку преподавателя Николаева. Написать запрос, выполняющий вывод данных о преподавателях, ведущих обучение хотя бы по одному из тех предметов, которым обучает преподаватель по фамилии Сорокин. Написать запрос, выполняющий вывод данных о фамилиях преподавателей, проводящих занятия у студентов, обучающихся в университетах с рейтингом, меньшим 200. Написать запрос, выполняющий вывод данных о наименованиях университетов, расположенных в Москве и имеющих рейтинг меньше, чем у ВГУ. Написать запрос, выполняющий вывод списка фамилий студентов, обучаемых в университете, расположенном в городе, название которого стоит первым в алфавитном списке городов. Написать запрос, выполняющий вывод списка студентов, средняя оценка у которых превышает 4 балла. Написать запрос, выполняющий вывод общего количества учебных часов занятий, проводимых для студентов первого курса ВГУ. Написать запрос, выполняющий вывод среднего количества учебных часов предметов обучения, преподаваемых студентам второго курса ВГУ. Написать запрос, выполняющий вывод количества студентов, имеющих хотя бы одну неудовлетворительную оценку и проживающих в городе, не совпадающем с городом их университета. Написать запрос, выполняющий вывод списка фамилий студентов, имеющих только отличные оценки и проживающих в городе, не совпадающем с городом их университета. Написать запрос, выполняющий вывод списка фамилий студентов, имеющих две и более отличные оценки в каждом семестре и проживающих в городе, не совпадающем с городом их университета. Приведите как можно больше формулировок запроса «Получить фамилии студентов, сдававших экзамен по информатике». Приведите как можно больше формулировок запроса «Получить фамилии преподавателей, обучающих информатике». 1. Напишите команду, которая вводит в таблицу subject строку для нового предмета обучения со следующими значениями полей: SEMESTER = 4, SUBJ_NAME = 'Алгебра'; HOUR = 72; SUBJ_ID =201. Введите запись для нового студента, которого зовут Орлов Николай, обучающегося на первом курсе ВТУ, живущего в Воронеже, сведения о дате рождения и размере стипендии неизвестны. Напишите команду, удаляющую из таблицы exam_marks записи обо всех оценках студента, идентификатор которого равен 100. Напишите команду, которая увеличивает на 5 значение рейтинга всех имеющихся в базе данных университетов, расположенных в Санкт-Петербурге. Измените в таблице значение города, в котором проживает студент Иванов, на «Воронеж". Пусть существует таблица с именем student1, определения столбцов которой полностью совпадают с определениями столбцов таблицы student. Вставить в эту таблицу сведения о студентах, успешно сдавших экзамены более чем по пяти предметам обучения. Напишите команду, удаляющую из таблицы 3UBJECT1 сведения о предметах обучения, по которым студентами не получено ни одной оценки. Напишите запрос, увеличивающий данные о величине стипендии на 20% всем студентам, у которых общая сумма баллов превышает значение 50. Создайте таблицу exam_marks так, чтобы не допускался ввод в таблицу двух записей об оценках одного студента по конкретным экзамену и предмету обучения и чтобы не допускалось проведение двух экзаменов по любым предметам в один день. Создайте таблицу предметов обучения subject так, чтобы количество отводимых на предмет часов по умолчанию было равно 36. не допускались записи с отсутствующим количеством часов, поле subj_id являлось первичным ключом таблицы и значения семестров (поле semester) лежали в диапазоне от 1 до 12. Создайте таблицу exam_marks таким образом, чтобы значения поля exam_id были больше значений поля subj_id, а значения поля SUBJ_ID были больше значений поля student_id; пусть также будут запрещены значения NOLL в любом из этих трех полей. Создайте таблицу с именем subject_1 с теми же полями, что в таблице subject (предмет обучения). Поле SUBJ_ID является первичным ключом. Создайте таблицу с именем subj_lect_1 (учебные дисциплины преподавателей) с полями lecturer_id (идентификатор преподавателя) и subj_id (идентификатор преподаваемой дисциплины). Первичным ключом (составным) таблицы является пара атрибутов lecturer_id и SUBJ_ID, кроме того, поле lecturer_ID является внешним ключом, ссылающимся на таблицу lecturer_1, аналогичную таблице lecturer (преподаватель), а поле subj_id является внешним ключом, ссылающимся на таблицу subject_1, аналогичную таблице SUBJECT. Создайте таблицу с именем subj_lect_1 как в предыдущем задании, но добавьте для всех ее внешних ключей режим обеспечения ссылочной целостности, запрещающий обновление и удаление соответствующих родительских ключей. Создайте таблицу с именем lecturer_1 с теми же полями, что в таблице lecturer. Первичным ключом таблицы является атрибут lecturer_id, кроме того, поле univ_id является внешним ключом, ссылающимся на таблицу university_1 (аналог university). Для этого поля установите каскадные режимы обеспечения целостности ДЛЯ команд UPDATE И DELETE, Создайте таблицу с именем UNIVERSITY_1 с теми же полями, что в таблице university (университеты). Поле univ_id является первичным ключом. Создайте таблицу с именем EXAM_MARKS_1. Она должна содержать такие же поля, что и таблица exam_marks (экзаменационные оценки). Комбинация полей exam_id, student_id и subj_id является первичным ключом. Кроме того, поля student_id и subj_id являются внешним ключами, ссылающимися соответственно на таблицы student_1 и SUBJECT_1. Для этих полей установите режим каскадного обеспечения ссылочной целостности при операции обновления соответствующих первичных ключей и режим блокировки при попытке удаления родительского ключа при наличии ссылки на него. Создайте таблицу с именем student_1. Она должна содержать такие же поля, что и таблица student, и новое поле senior_student (староста), значением которого должен быть идентификатор студента, являющегося старостой группы, в которой учится данный студент. Укажите необходимые для этого ограничения ссылочной целостности, Создайте таблицу STUDENT_2, аналогичную таблице student, в которой поле univ_id (идентификатор университета) является внешним ключом, ссылающимся на таблицу university_1, и таким образом, чтобы при удалении из таблицы uhiversity_строки с информацией о каком-либо университете в соответствующих записях таблицы STUDENT_2 поле univ_id очищалось (замещалось на null). С помощью команды CREATE table создайте запросы для формирования таблиц учебной базы данных, с указанием первичных ключей, но без указания ограничений внешних ключей. Затем с помощью команды alter table укажите для сформированных таблиц все ограничения, в том числе и ограничения ссылочной целостности. Передайте пользователю petrov право на изменение в базе данных значений оценок для записей о студентах. Передайте пользователю SIDOROV право передавать другим пользователям права на осуществление запросов к таблице exam_marks. Отмените привилегию INSERT no отношению к таблице student у пользователя ivanov и у всех других пользователей, которым привилегия, в свою очередь, была предоставлена этим пользователем IVANOV. Передайте пользователю SIDOROV право выполнять операции вставки или обновления для таблицы university, но только для записей об университетах, значения рейтингов которых лежат в диапазоне от 300 до 400. Разрешите пользователю petrov делать запросы к таблице exam marks, но запретите ему изменять в этой таблице значения оценок студентам, имеющим неудовлетворительные (=2) оценки.
Примечание LOWER — перевод в строчные символы (нижний регистр) lower (<строка>) UPPER — перевод в прописные символы (верхний регистр) upper (<строка>) • INITCAP — перевод первой буквы каждого слова строки в прописную (заглавную) initcap (<строка>) Например: SELECT LOWER (SURNAME), UPPER (NAME) FROM STUDENT WHERE KURS = 4 AND STIPEND > 0; SELECT SURNAME, NAME, BIRTHDAY, TO_CHAR (BIRTHDAY, 'DD-MON-YYYV), TO_CHAR (BIRTHDAY, 'DD.MM.YY') FROM STUDENT; вернет результат:
|