Главная страница
Навигация по странице:

  • Покупки (название таблицы Purchases )

  • Справочник книг (название таблицы Books )

  • Справочник авторов (название таблицы Authors )

  • Справочник поставщиков (название таблицы Deliveries )

  • Справочник издательств (название таблицы Publishinghouse )

  • 2.2. Упражнения с использованием операторов обработки данных для БД «Книжное дело»

  • Список групп (название таблицы Groups )

  • Справочник студентов (название таблицы Students )

  • Справочник изучаемых дисциплин (название таблицы Subjects )

  • Таблица успеваемости (название

  • Справочник преподавателей (название таблицы Lectors )

  • 2.4. Упражнения с использованием операторов обработки данных для БД «Успеваемость студентов»

    		•

    Лаб SQL 2.1. Вариант 1 База данных Книжное дело Рис. 21. Фрагмент базы данных Книжное дело


    Скачать 268.69 Kb.
    НазваниеВариант 1 База данных Книжное дело Рис. 21. Фрагмент базы данных Книжное дело
    Дата06.01.2022
    Размер268.69 Kb.
    Формат файлаdocx
    Имя файлаЛаб SQL 2.1.docx
    ТипДокументы
    #324762

    Вариант 1

    База данных «Книжное дело»



    Рис. 21. Фрагмент базы данных «Книжное дело»

    Создать таблицы с помощью SQL запросов. Заполнить можно обычным способом.

    Связь между таблицами осуществляется с помощью следующих пар полей с типом связи «один-ко-многим» соответственно:

    1. Books. Codebook - Purchases. Codebook;

    2. Deliveries. Codedelivery - Purchases. Codedelivery;

    3. Authors. Codeauthor - Books. Codeauthor;

    4. Publising_house.Code_publish - Books.Code_publish.

    Таблица 7

    Покупки (название таблицы Purchases)

    Название поля
    Тип поля
    Описание поля

    Code book
    Integer
    Код закупаемой книги

    Date order
    Date
    Дата заказа книги

    Code delivery
    Integer
    Код поставщика

    Type_purchase
    Logical
    Тип закупки (опт/ розница)

    Cost
    Currency
    Стоимость единицы товара

    Amount
    Integer
    Количество экземпляров

    Code_purchase
    Integer
    Код покупки

    Таблица 8

    Справочник книг (название таблицы Books)

    Название поля
    Тип поля
    Описание поля

    Code book
    Integer
    Код книги

    Title book
    Character
    Название книги

    Code author
    Integer
    Код автора

    Pages
    Integer
    Количество страниц

    Code_publish
    Integer
    Код издательства

    Таблица 9 Справочник авторов (название таблицы Authors)

    Название поля
    Тип поля
    Описание поля

    Code author
    Integer
    Код автора

    Name author
    Character
    Фамилия, имя, отчество автора

    Birthday
    Date
    Дата рождения

    Таблица 10 Справочник поставщиков (название таблицы Deliveries)

    Название поля
    Тип поля
    Описание поля

    Code delivery
    Integer
    Код поставщика

    Name delivery
    Character
    Фамилия, и., о. ответственного лица

    Name company
    Character
    Название компании-поставщика

    Address
    Character
    Юридический адрес

    Phone
    Numeric
    Телефон контактный

    INN
    Character
    ИНН

    Таблица 11 Справочник издательств (название таблицы Publishinghouse)

    Название поля
    Тип поля
    Описание поля

    Code_publish
    Integer
    Код издательства

    Publish
    Character
    Издательство

    City
    Character
    Город

    2.2. Упражнения с использованием операторов обработки данных для БД «Книжное дело»

    1. Выбрать из таблицы Books коды книг, названия и количество страниц, отсортировать результат по названиям книг (по возрастанию) и по полю Pages (по убыванию).

    2. Выбрать из таблицы Books названия книг и количество страниц, а из таблицы Deliveries выбрать имя соответствующего поставщика книги.

    3. Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН, у которых название компании начинается с 'ОАО'.

    4. Вывести список названий издательств (поле Publish) из таблицы Publishinghouse, которые не находятся в городе 'Москва'.

    5. Вывести список названий книг и количество страниц из таблицы Books, у которых объем в страницах укладывается в диапазон 200 - 300.

    6. Вывести список фамилий, имен, отчеств авторов из таблицы Authors, у которых фамилия начинается на одну из букв диапазона 'В' - Т'.

    Выбор записей по диапазону значений (In)

    1. Вывести список названий книг из таблицы Books, которые написаны следующими авторами: 'Толстой Л.Н.', 'Достоевский Ф.М.', 'Пушкин А.С (или другие авторы).

    Выбор записей с использованием Like

    1. Вывести список авторов из таблицы Authors, которые начинаются на букву 'К'.

    Выбор записей по нескольким условиям

    1. Выбрать коды авторов, имена авторов, названия соответствующих книг, если код издательства находится в диапазоне от 10 до 25 и количество страниц в книге больше 120.

    Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN)

    1. Вывести список названий компаний-поставщиков и названия книг, которые они поставили в период с 01.01.2012 по 31.12.2017 (условие по полю Dateorder).

    Вычисления

    1. Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages) и названия соответствующих книг (поле Titlebook).

    2. Вывести количество лет с момента рождения авторов (использовать поле Birthday) и имена соответствующих авторов (поле Nameauthor).

    Вычисление итоговых значений с использованием агрегатных функций

    1. Вывести общую сумму поставок книг (использовать поле Cost), выполненных 'ЗАО Оптторг' (условие по полю Namecompany).

    2. Вывести среднюю стоимость (использовать поле Cost) и среднее количество экземпляров книг (использовать поле Amount) в одной поставке, где автором книги является 'Акунин' (условие по полю Nameauthor).

    3. Вывести все сведения о поставке (все поля таблицы Purchases), a также название книги (поле Titlebook) с минимальной общей стоимостью (использовать поля Cost и Amount).

    4. Вывести все сведения о поставке (все поля таблицы Purchases), a также название книги (поле Titlebook) с максимальной общей стоимостью (использовать поля Cost и Amount).

    Изменение наименований полей

    1. Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages), поместив результат в поле с названием One_page, и названия соответствующих книг (поле Titlebook).

    Использование переменных в условии

    1. Вывести список авторов (поле Nameauthor), возраст которых меньше заданного пользователем (условие с использованием поля Birthday).

    Использование переменных вместо названий таблиц

    1. Вывести список издательств (поле Namecompany), книги которых были поставлены по цене 150 руб. (поле Cost).

    Выбор результата в таблицы

    1. Вывести список названий книг (поле Titlebook) и количества
      страниц (поле Pages) в каждой книге и поместить результат в таблицу с
      названием Temp1.

    Использование функций совместно с подзапросом

    1. Вывести список книг (поле Titlebook), у которых количество страниц (поле Pages) больше среднего количества страниц всех книг в таблице.

    2. Вывести список авторов (поле Nameauthor), возраст которых меньше среднего возраста всех авторов в таблице (условие по полю Birthday).

    Использование квантора существования в запросах

    1. Вывести список издательств (поле Publish), книги которых были приобретены оптом ('опт' из поля TypePurchase).

    2. Вывести список авторов (поле Nameauthor), книг которых нет в таблице Books.

    Оператор обработки данных Delete

    1. Удалить из таблицы Purchases все записи, у которых количество книг в заказе (поле Amount) = 0.


    Вариант 2

    База данных «Успеваемость студентов»



    Рис. 22. Фрагмент базы данных «Успеваемость студентов»

    Связь между таблицами осуществляется с помощью следующих пар полей с типом связи «один-ко-многим» соответственно:

    1. Groups. Codegroup - Students. Codegroup;

    2. Students. Codestud - Progress.Codestud;

    3. Subjects. Codesubject - Progress. Codesubject;

    4. Lectors. Codelector - Progress.Codelector.

    Таблица 12 Список групп (название таблицы Groups)

    Название поля
    Тип поля
    Описание поля

    Code group
    Integer
    Код группы

    Name group
    Character
    Название группы

    Num course
    Integer
    Номер курса

    Name_speciality
    Character
    Название специальности

    Таблица 13 Справочник студентов (название таблицы Students)

    Название поля
    Тип поля
    Описание поля

    Code stud
    Character
    Номер зачетной книжки

    Surname
    Character
    Фамилия студента

    Name
    Character
    Имя студента

    Lastname
    Character
    Отчество студента

    Code group
    Integer
    Код группы

    Birthday
    Date
    Дата рождения студента

    Phone
    Numeric
    Контактный телефон студента

    Таблица 14 Справочник изучаемых дисциплин (название таблицы Subjects)

    Название поля
    Тип поля
    Описание поля

    Code subject
    Integer
    Код дисциплины

    Name subject
    Character
    Название дисциплины

    Count hours
    Integer
    Количество часов в курсе

    Таблица 15 Таблица успеваемости (название таблицы Progress)

    Название поля
    Тип поля
    Описание поля

    Code stud
    Character
    Номер зачетной книжки

    Code subject
    Integer
    Код дисциплины

    Code lector
    Integer
    Код преподавателя

    Date exam
    Date
    Дата сдачи экзамена

    Estimate
    Integer
    Оценка

    Code_progress
    Integer
    Ключевое поле

    Таблица 16 Справочник преподавателей (название таблицы Lectors)

    Название поля
    Тип поля
    Описание поля

    Code lector
    Integer
    Код преподавателя

    Name lector
    Character
    Фамилия, имя, отчество преподавателя

    Science
    Character
    Ученая степень

    Post
    Character
    Должность

    Date
    Date
    Дата приема на работу

    2.4. Упражнения с использованием операторов обработки данных для БД «Успеваемость студентов»

    1. Вывести список фамилий, имен, отчеств преподавателей, их должности и ученые степени из таблицы Lectors, отсортировать результат по названиям должностей по возрастанию и по ученым степеням по убыванию.

    2. Вывести список фамилий, имен, отчеств студентов из таблицы Students и названий групп из таблицы Groups, в которых они обучаются.

    3. Вывести список дисциплин из таблицы Subjects, значение которых начинается с 'математ'.

    4. Вывести список преподавателей из таблицы Lectors и их должности, которые не являются докторами технических наук, т.е. значение поля Science не равно 'д.т.н.'.

    5. Вывести даты экзаменов из таблицы Progress и список дисциплин из таблицы Subjects, по которым сдавали экзамены в период с 01.01.2017 по 01.02.2017 (или другие даты).

    6. Вывести список дисциплин из таблицы Subjects, у которых название начинается на одну из букв диапазона 'В'-Т'.

    Выбор записей по диапазону значений (In)

    1. Вывести список преподавателей из таблицы Lectors и их должности, у которых есть одна из следующих ученых степеней: 'к.т.н.', 'к.э.н.', 'д.т.н.'

    Выбор записей с использованием Like

    1. Вывести список студентов и даты рождения из таблицы Students, которые содержат в фамилии сочетание букв 'нова'.

    Выбор записей по нескольким условиям

    1. Вывести список преподавателей из таблицы Lectors, которые принимали экзамены по дисциплинам с кода 5 - 12 и в период с 01.01.2017 по 01.02.2017.

    Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN)

    1. Вывести список названий дисциплин и имен преподавателей, которые принимали по этим дисциплинам экзамены в период с 15.01.2017 по 16.02.2017 (условие по полю Dateexam из таблицы Progress).

    Вычисления

    1. Вывести список всех преподавателей (Namelector), их ученые степени (поле Science) и срок их работы в годах (использовать поле Date_ из таблицы Lectors).

    2. Вывести список всех студентов (поля Surname, Name, Lastname из таблицы Students) и номер курса, на котором они занимаются, а также количество лет оставшейся учебы (использовать поле Numcourse из таблицы Groups).

    Вычисление итоговых значений с использованием агрегатных функций

    1. Вывести список всех групп (поле Namegroup из таблицы Groups) и количество студентов в каждой группе (по любому полю из таблицы Students).

    2. Вывести средний балл (использовать поле Estimate из таблицы Progress) по результатам экзаменов каждого студента и имена студентов (поля Surname, Name из таблицы Students) за период сдачи экзаменов с 05.01.2003 по 25.01.2003 (условие по полю Dateexam из таблицы Progress).

    3. Вывести фамилии и имена студентов (поля Surname, Name из таблицы Students) с максимальным средним баллом за весь период обучения (условие по полю Estimate из таблицы Progress).

    4. Вывести все сведения о преподавателях (все поля таблицы Lectors) с максимальным общим стажем работы (использовать поле Date_).

    Изменение наименований полей

    1. Вывести список преподавателей (Namelector), их ученые степени и срок их работы в годах, поместив результат в поле Oldyears.

    Использование переменных в условии

    1. Вывести все сведения о студентах и их даты рождения из таблицы Students, значения которых находятся в диапазоне, хранящемся в переменных Birthdaybegin и Birthdayend.

    Использование переменных вместо названий таблиц

    1. Вывести коды студентов и имен, названия и коды групп, причем таблица Students будет использоваться с именем 'А', а таблица Groups будет использоваться с именем 'В'.

    Выбор результата в таблицу

    1. Вывести все сведения о сданных экзаменах (все поля из таблицы Progress) для студента с фамилией 'Васьков' и именем 'Павел' (условия по полям Surname, Name из таблицы Students) и поместить результат в таблицу с названием Temp1.

    Использование функций совместно с подзапросом

    1. Вывести список преподавателей (все поля из таблицы Lectors), дата устройства которых меньше средней даты устройства всех преподавателей в таблице (условие по полю Date_).

    2. Вывести все сведения о сдачах экзаменов (все поля из таблицы Progress) и список студентов (поля Surname, Name из таблицы Students), которые по таблице Progress сдавали экзамены самыми последними (дата сдачи экзаменов Dateexam максимальна).

    Использование квантора существования в запросах

    1. Вывести список студентов (поля Surname, Name, Lastname из таблицы Students), которые сдали все экзамены без двоек (подзапрос по таблице Progress).

    2. Вывести список студентов (поля Surname, Name, Lastname из таблицы Students), которые не сдавали ни одного экзамена (подзапрос по таблице Progress).

    Оператор обработки данных Delete

    1. Удалить из таблицы Subjects все записи, в которых в поле Namesubject нет данных или в поле содержится пустое значение.

    написать администратору сайта