Лабораторная работа N 5.
Язык структурированных запросов (SQL)

Простые запросы

Перейти к закладке "Запросы" и нажать кнопку "Создать".

Ввести текст запроса, для выполнения запроса закрыть окно, сохранить запрос и нажать кнопку "Открыть".

    1. Печать всей таблицы

      Список всех групп

      SELECT *
      FROM grups

    2. Печать с сортировкой по одному полю

      Печать списка предметов с сортировкой по названию

      SELECT С_lekz, C_priznak
      FROM kurs ORDER BY c_lekz

    3. Выборка из одной таблицы с условием

      Список студентов группы номер 2

      SELECT c_fio_stud
      FROM studs
      WHERE n_grupa=2

       

    4. Выборка по шаблону

      Список предметов, имеющих в названии слово "Анализ"

      SELECT *
      FROM kurs
      WHERE c_lekz LIKE "*АНАЛИЗ*"

    5. Контрольное задание
  1. Напечатать список групп, где количество студентов больше 15 со 2 курса.
  2. Найти всех студентов с фамилией Иванов и телефоном на 41.

    Запросы с соединением

    Таблицы соединяются по полю, имеющими одинаковую семантику, по одинаковому значению. Обычно соединяемые таблицы находятся в связи один-ко-многим.

    1. Простое соединение

      Напечатать список студентов платных групп

      SELECT grups.c_grupa, grups.c_budget, studs.c_fio_stud

      FROM grups, studs

      WHERE grups.n_grupa=studs.n_grupa

      AND grups.c_budget='Платная'

    2. Соединение с условием отбора

      Список преподавателей кафедры алгебры

      SELECT kafedr.c_kaf, preps.c_fio_prep, c_tel_prep

      FROM kafedr, preps

      WHERE kafedr.n_kaf=preps.n_kaft

      AND kafedr.c_kaf='Алгеба'

    3. Контрольные задания.

      1. Получить список студентов 2-го курса бюджетных групп, живущих на ЧМЗ (по телефону определить - на 21, 24, 22 начинаются ).

      2. Для каждого зав. Кафедрой определить его ученую степень.

      4. Соединение 3-х и более таблиц

      Соединение это обычно выполняется над таблицами со связью многие-ко-многим, или когда в условии отбора данных из двух связанных таблиц присутствуют данные из 3-ей таблицы.

      1. Связь многие-ко-многим

        Таблица KURS_STUD ( Один студент - много предметов, один предмет - много студентов)

        Список всех студентов с отметками за Мат. анализ

        SELECT studs.c_fio_stud, kurs.c_lekz, kurs.c_priznak, kurs.c_sachet, kurs_stud.n_otmetka

        FROM studs, kurs, kurs_stud

        WHERE studs.n_stud=kurs_stud.n_stud

        AND kurs_stud.n_lekz=kurs.n_lekz

        AND kurs.c_lekz='Мат.Анализ'

      2. Дополнительная связь

Список всех студентов группы М-101 с отметками за все предметы

SELECT grups.c_grupa, studs.c_fio_stud, kurs.c_lekz, kurs.c_priznak, kurs.c_sachet, kurs_stud.n_otmetka

FROM studs, kurs, kurs_stud, grups

WHERE studs.n_stud=kurs_stud.n_stud

AND kurs_stud.n_lekz=kurs.n_lekz

AND studs.n_grupa=grups.n_grupa

AND grups.c_grupa='M-101'

ORDER BY kurs.c_lekz

 

    1. Контрольные задания
      1. Получить список студентов 2-го курса бюджетных групп, у которых есть задолженности по предметам(список).
      2. Распечатать список предметов и групп, которые ведет преподаватель Воронин.(Или другой, какой есть в базе)
      3. Получить список задолжников каждого преподавателя.

Подзапросы.

    1. Подзапросы с подмножеством

      Получить список студентов - задолжников

      SELECT *

      FROM studs

      WHERE n_stud IN

      ( SELECT n_stud

      FROM kurs_stud

      WHERE n_otmetka=2 OR EMPTY(n_otmetka ) )

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

    2. Подзапросы с EXISTS и NOT EXISTS

EXISTS означает примерно "если подмножество не пусто", соотвественно NOT EXISTS означает "если подмножество пусто". Используются в подзапросах.

Найти всех преподавателей, которые не ведут ни одного предмета

SELECT *

FROM preps

WHERE NOT EXISTS

(SELECT *

FROM kurs_grup

WHERE preps.n_prep = kurs_grup.n_prep )

Haйти всех студентов из группы М-101, у которых есть хоть одна 3-ка.

SELECT *

FROM studs

WHERE n_grupa IN

( SELECT n_grupa FROM grups WHERE c_grupa='M-101' )

AND EXISTS

(SELECT *

FROM kurs_stud

WHERE studs.n_stud=kurs_stud.n_stud

AND n_otmetka = 3 )

3.Контрольные задания.

  1. Получить список студентов с указанием группы, у которых нет отметок. ( Нет записей в таблице KURS_STUD)
  2. Получить список предметов, которые неизвестно кто ведет.

     

    Функции.

    1. Count()

      Функция COUNT() - подсчет числа записей, удовлетворяющих условию запроса.

      Сколько всего студентов 1-го курсав базе

      SELECT "количество студентов = ", COUNT(*)

      FROM studs, grups

      WHERE studs.n_grupa=grups.n_grupa

      AND grups.n_kurs=1

    2. SUM()

      Функция SUM() - суммирование по числовому полю

      Посчитать количество часов практических занятий у преподавателя 1

      SELECT SUM(kurs.N_Time)

      FROM kurs, kurs_grup

      WHERE kurs.n_lekz = kurs_grup.n_lekz

      AND kurs.c_priznak='Практика'

      AND kurs_grup.n_prep=1

      Сначала в таблице KURS_GRUP отбираем все предметы и группы препод.1, затем из них отбираем практики и суммируем все часы этих предметов. 

    3. MAX()

      Функция MAX() - поиск максимального элемента в подмножестве.

      Поиск максимального номера студента

      SELECT MAX(n_stud)

      FROM studs

      Аналогично функции MAX работает функция MIN.

    4. Контрольные задания.

1. Посчитать количество пятерок у студентов группы М-101

2. Посчитать количество курсов кафедры алгебры.

3. Посчитать количество часов по всем курсам кафедры Мат. Анализ.

4. Посчитать, сколько зачетов должны сдать студенты группы М-101.

Группировка данных

Данные в одной таблице группируются по какому-то полю, значение которого не уникально, обычно являющегося вторичным ключем.

Найти, сколько студентов в каждой группе

SELECT n_grupa, COUNT(*)

FROM studs

GROUP BY n_grupa

Группировка идет по полю n_grupa - все данные с одинаковым значением этого поля группируются и подсчитывается число записей в каждой такой группе.

Группировать можно и данные из разных таблиц.

Количество часов по лекциям и практикам отдельно на каждой кафедре

SELECT preps.n_kaf, kurs.c_priznak, SUM(kurs.n_time)

FROM preps, kurs, kurs_grup

WHERE preps.n_prep=kurs_grup.n_prep

AND kurs.n_lekz=kurs_grup.n_lekz

GROUP BY preps.n_kaf, kurs.c_priznak

Отбор групп.

Выбирать можно не только записи, но и группы.

Напечатать список групп, в которых количество пятерок у студентов больше 10 на группу.

SELECT grups.c_grupa, 'кол-во пятерок ',COUNT(*)

FROM grups, studs, kurs_stud

WHERE grups.n_grupa=studs.n_grupa

AND studs.n_stud=kurs_stud.n_stud

AND kurs_stud.n_otmetka=5

GROUP BY grups.c_grupa

HAVING COUNT(*) > 10

 

Контрольные задания.

  1. Посчитать количество студентов каждого преподавателя.
  2. Посчитать, сколько зачетов и экзаменов должен принять каждый преподаватель.
  3. Найти студентов, которые не сдали 2 и более предметов.
  4. Найти группу с наиболее большим количеством задолжников..

 

Вставка записей в таблицу.

    1. Прямое занесение данных

      "вручную" в таблицу кафедр с вычислением уникального ключа

      INSERT (n_kaf, c_kaf, N_kol, c_sav, n_kol_kurs)

      INTO kafedr

      VALUES ( 5, "Мет.Оптимизации", 0, 'Не знаю', 30)

      Самостоятельно занесите еще несколько записей.

    2. Вставка записей из другой таблицы.

      То же с вычислением уникального ключа

      INSERT INTO kafedr(n_kaf, c_kaf, N_kol, c_sav, n_kol_kurs)

      SELECT max(n_kaf),'Следующая кафедра',0, 'Не знаю', 0

      FROM kafedr

      Мат. Анализ читается у всех групп 1 курса мат. Факультета. У всех ведет Воронин.

      Внести соответствующие записи в таблицу KURS_GRUP

      INSERT INTO kurs_grup(n_lekz, n_grupa, n_prep)

      SELECT kurs.n_lekz, grups.n_grupa, 1

      FROM kurs, grups

      WHERE kurs.n_lekz='Мат.анализ'

      AND grups.n_kurs=1

       

    3. Контрольные задания.

Вставить в таблицу KURS_STUD всех студентов 1-го курса и все предметы, которые у них ведутся из таблицы KURS_GRUP. Отметки расставить случайным образом.

Обновление таблиц.

    1. Прямое обновление.

      Студента Иванова перевести из группы М-101 в группу М-102

      UPDATE studs

      SET n_grupa = 2

      WHERE c_fio_stud = "ИВАНОВ"

      AND n_grupa=1

    2. Обновление с подзапросом.

      Программа расчета количества студентов в каждой группе

      UPDATE grups

      SET n_men =

      ( SELECT count(*)

      FROM studs

      WHERE studs.n_grupa=grups.n_grupa )

    3. Контрольные задания.
  1. Расчитать количество преподавателей и количество курсов в таблице KAFEDR согласно реальным значениям.

Удаление данных.

Идеология аналогична обновлению.

    1. Простое удаление

      Удалить кафедру "Мет.оптимизации"

      DELETE

      FROM kafedr

      WHERE c_kaf='Мет.Оптимизации'

    2. Удаление с подзапросом.

      Удалить преподавателей, которые не относятся ни к какой кафедре

      DELETE

      FROM preps

      WHERE N_kaf NOT EXISTS

      ( SELECT N_kaf

      FROM kafedr

      WHERE kafedr.n_kaf=preps.n_kaf )

    3. Контрольные задания
  1. Удалить группы и студентов 5-го курса.
  2. Удалить все другие ссылки на студентов и группы 5-го курса (которых теперь нет) из таблиц KURS_GRUP, KURS_STUD.
  3. Перевести остальных студентов на курс выше. (перименовать группу и курс) funcltions for Access:
    s = str(n) example: "12"=str(12)
    n = val(s) example 12=val("12")
    s = left(s,kol) example "MM-" = left("MM-103",3)
    s = right(s,kol) example "103" = right("MM-103",3)
  4. Добавить новые группы 1-го курса и новых студентов.