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

  • Практическая работа №4 «Управляющие конструкции языка запросов SQL »

  • BEGIN TRY ----- ЗАПРОС, ВЫЗЫВАЮЩИЙ ОПАСЕНИЯEND TRY BEGIN CATCH

  • Практическая работа №5 «Групповые операции. Агрегатные функции »

  • Практическая работа №6 «Создание многотабличного запроса»

  • Практическая работа №7 «Использование подзапросов»

  • Практическая работа №8 «Реализация запросов для нахождения минимального и максимального значений»

  • {[NOT] { ALL | SOME | ANY } ( )

  • кос пм02 09.02. кос_ПМ02. Комплект контрольнооценочных средств по профессиональному модулю пм. 02 Разработка и администрирование баз данных для специальности 09. 02. 03 Программирование в компьютерных системах


    Скачать 0.57 Mb.
    НазваниеКомплект контрольнооценочных средств по профессиональному модулю пм. 02 Разработка и администрирование баз данных для специальности 09. 02. 03 Программирование в компьютерных системах
    Анкоркос пм02 09.02.03
    Дата26.06.2022
    Размер0.57 Mb.
    Формат файлаdocx
    Имя файлакос_ПМ02.docx
    ТипДокументы
    #616175
    страница7 из 9
    1   2   3   4   5   6   7   8   9


    AND, OR, и NOT
    Задание 1.

    Вывести наименования предметов, с количеством часов равным 40 или 50.
    SELECT np

    FROM predmet

    WHERE chas = 40 or chas = 50
    Задание 2.

    Вывести наименования предметов, у которых количество часов не равно сумме лекционных и практических.
    SELECT np

    FROM predmet

    WHERE chas<> lek + pr
    3 Использование специальных операторов в условиях.
    SQL использует специальные операторы IN, BETWEEN, LIKE, и IS NULL. Мы рассмотрим как их использовать и как реляционные операторы позволяют создавать более сложные и мощные предикаты. Обсуждение оператора IS NULL будет включать отсутствие данных и значение NULL, которое указывает на то: что данные отсутствуют. Вы также узнаете о разновидностях использования оператора NOT применяющегося с этими операторами.

    Оператор IN
    Оператор IN определяет набор значений в которое данное значение может или не может быть включено.
    Задание 3.Вывести номера студентов и номера предметов, по которым получали оценки 3 или 4
    SELECT ns, np

    FROM uspevaemost

    WHERE ozenka in ( 3, 4)

    Оператор BETWEEN
    Оператор BETWEEN похож на оператор IN. В отличии от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к по-

    рядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку.
    Задание 4. Выведите из таблицы predmet все наименования предметов с количеством часов, принадлежащих интервалу 100 - 140 :
    SELECT *

    FROM predmet

    WHERE chas BETWEEN 100 AND 140;
    Контрольные задания.


      1. Из таблицы Успеваемость выведите код предмета, табельный номер преподавателя, номер студента, который получал двойку.

      2. Выведите на экран количество студентов в ‘101’ группе

      3. Создайте запрос, который выводит на экран код кафедры ‘математика’

      4. Из таблицы Преподаватель выведите на экран фамилии и табельные номера преподавателей с кафедры ‘02’

      5. Из таблицы Изучение выведите на экран номер группы и табельный номер преподавателя, который ведёт предмет с количеством часов ( chas) более 50 .

      6. По разработанной базе данных сформулируйте и выполните запросы с использованием операторов: AND, OR, IN, BETWEEN


    Практическая работа №4
    «Управляющие конструкции языка запросов SQL »

    Цель: «Сформировать навыки и умения реализации запроса с использованием управляющих конструкций»
    Ход Работы
    1.Операторные скобки

    Как и все алгоритмические языки Transact-SQL содержит в своём составе операторные скобки. Синтаксис конструкции имеет следующий вид

    BEGIN

    <команда 1>

    <команда 2>

    END

    Задание 1. Вывести фамилии преподавателей с кафедры ‘02’ и список студентов из группы ‘101’

    begin

    use uch_proc

    select fio as фио_преподавателей from prepodavatel where kkaf = '02'
    select fio as фио _студентов from student where ng = '101'

    end

    2 Операторы ветвления

    Для организации ветвления используется конструкция IF…ELSE. Синтаксис конструкции:

    IF <условие>

    <команда 1>

    ELSE

    <команда 2>

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

    Задание 2. Определить содержится ли фамилия ‘Иванов’ в таблице Преподаватель.

    Код запроса имеет вид:

    use uch_proc

    IF 'Глухов' in (select fio from prepodavatel )

    print 'фамилия есть в списке преподавателей'

    else

    print 'фамилия нет в списке преподавателей'

    Если разветвлений в алгоритме много и они одиночные, то для их замены можно воспользоваться конструкцией Case …..End

    CASE <входное выражение>

    WHEN < выражение 1> THEN < выражение результат>

    WHEN < выражение 2> THEN < выражение результат>

    ELSE < выражение результат>

    End

    Конструкция фактически представляет собой функцию. У функции имеется один параметр (входное выражение). Он указывается после Case, но не в скобках. Функция возвращает результат, поэтому эта конструкция должна входить в состав какого либо выражения. Если в примере, рассмотренном далее убрать PRINT, то система выдаст сообщение об ошибке, т.к. это будет неверный вызов функции.

    Работает конструкция следующим образом. Если значение входного выражения и одного из перечисленных, стоящих после WHEN, совпадают, то возвращается выражение – результат, указанное после соответствующего THEN. Если не одного совпадения не отмечено, то возвращается результат, стоящий после ELSE. Если значение входного выражения совпадает более чем с одним значением выражения, стоящего после WHEN, то выполняется результат, соответствующий первому совпадению.

    Задание 3 Вывести на экран значение суммы прописью, использую конструкцию Case …..End

    declare @rub char(25)

    set @rub = ‘6 руб’

    PRINT

    CASE @rub

    WHEN ‘ 1 руб’ THEN ‘ один рубль’

    WHEN ‘ 2 руб’ THEN ‘ два рубля’

    WHEN ‘ 3 руб’ THEN ‘ три рубля’

    ELSE ‘ таких денег в кассе нет’

    End

    Результат выполнения команды : таких денег в кассе нет

    В следующем примере рассмотрим применение конструкции в запросе.

    Задание 4 В запросе формируется список номеров студентов, оценок и добавлен столбец , в котором указано одно из значений ( двоечник, троечник, хорошист, отличник )

    declare @o int

    use uch_proc

    select ozenka, ns, kp ,tit=

    case ozenka

    when 2 then 'двоечник'

    when 3 then 'троечник'

    when 4 then 'хорошист'

    when 5 then 'отличник'

    end

    from uspevaemost

    3 Операторы цикла

    В Transact-SQL имеется конструкция для организации многократных повторений команд в программе : WHELE………CONTINUE. Одна обеспечивает выполнение цикла одного типа. Это цикл с предусловием. Синтаксис конструкции:

    WHELE < условие>

    { команда | блок }

    [BREAK]

    { команда | блок }

    [ CONTINUE ]

    Цикл можно завершить принудительно, для этого в нужном месте цикла нужно поместить служебное слово BREAK .
    адание 5 Вычислите и выведите на экран квадраты и кубы чисел от 1 до 5.

    declare @r int

    PRINT ' Число квадрат куб'
    set @r = 1

    WHILE @r <= 5

    BEGIN

    PRINT STR(@r )+ STR(@r*@r )+ STR(@r*@r*@r )

    SET @r = @r+1

    END

    4 Оператор LIKE
    LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы видеть, совпадает ли с условием часть его строки.
    Задание 5 Вывести фамилии преподавателей, начинающихся с буквы ‘А’

    USE uch_proc

    SELECT * FROM prepodavatel

    where fio like 'А%'

    5 Операторы для обработки исключений
    В Transact-SQL существует возможность обработки исключений:

    BEGIN TRY

    ----- ЗАПРОС, ВЫЗЫВАЮЩИЙ ОПАСЕНИЯ

    END TRY
    BEGIN CATCH

    ----- ОБРАБОТКА ОШИБКИ, КОТОРАЯ МОЖЕТ ВОЗНИКНУТЬ

    END CATCH

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

    1. По таблице ‘успеваемость’ определить получал студент с номером ‘01’ оценки. Если получал, то вывести на печать сообщение ‘Студент опрашивался’ в противном случае напечатать ‘Студент не получал оценок’ .

    2. Если вид занятия ‘лек’, то в сформированном столбце записать «лекционное занятие», если вид занятия ‘пр’, то в сформированном столбце записать «практическое занятие»

    3. В индивидуальной базе данных сформулировать и привести пример с использованием оператора LIKE

    4. В индивидуальной базе данных сформулировать и привести пример с использованием оператора Case …..End

    Практическая работа №5
    «Групповые операции. Агрегатные функции »

    Цель: «Сформировать навыки и умения реализации запроса с использованием групповых операций и агрегатных функций»
    Ход Работы
    1. Агрегатные функции

    Запросы могут производить обобщенное групповое значение полей точно также как и значение одного поля. Это делает с помощью агрегатых функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций:


    • COUNT производит номера строк или не-NULL значения полей которые выбрал запрос.

    • SUM производит арифметическую сумму всех выбранных значений данного поля.

    • AVG производит усреднение всех выбранных значений данного поля.

    • MAX производит наибольшее из всех выбранных значений данного поля.

    • MIN производит наименьшее из всех выбранных значений данного поля.

    Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля.
    Задание 1.Вывести среднюю оценку по таблице Успеваемость для студента с номером ‘01’

    USE uch_proc

    SELECT AVG(ozenka) FROM uspevaemost

    where ns='01'

    Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT чтобы производить счет чисел различных значений в данном поле.
    Задание 2.Определить в таблице Студент количество студентов в таблице
    USE uch_proc

    SELECT count(ns) FROM student

    Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени поля, как в следующем примере:

    USE uch_proc

    SELECT count(*) FROM student

    2. Групповые операции
    Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT.

    Задание 3.Определить для каждого студента по таблице Успеваемость минимальную оценку.
    USE uch_proc

    SELECT ns, min(ozenka) as 'наихудшая'

    FROM uspevaemost

    group by ns

    GROUP BY применяет агрегатные функции независимо от серий групп которые определяются с помощью значения поля в целом. В этом случае, каждая группа состоит из всех строк с тем же самым значением поля ns, и MIN функция применяется отдельно для каждой такой группы. Это значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, также как это делает агрегатная функция. Результатом является совместимость которая позволяет агрегатам и полям объединяться таким образом.

    Вы можете также использовать GROUP BY с многочисленными полями.
    Задание 4. Совершенствуя вышеупомянутый пример далее, предположим что вы хотите увидеть наименьшую оценку полученную каждым студентом по каждому предмету. Чтобы сделать это, вы должны сгруппировать таблицу uspevaemost по студентам и кодам предметов, и применить функцию MIN к каждой такой группе, подобно этому:

    USE uch_proc

    SELECT ns, min(ozenka) as 'наихудшая' , kp

    FROM uspevaemost

    group by ns, kp

    3.Предложение HAVING

    Предположим, что в предыдущем примере, вы хотели бы увидеть MIN оценку только студента ‘01’ Вы не сможете использовать агрегатную функцию в предложении WHERE

    Вы не сможете сделать что-нибудь подобно следующему:

    USE uch_proc

    SELECT ns, min(ozenka) as 'наихудшая' , kp

    FROM uspevaemost

    group by ns, kp

    WHERE ns = ‘01’

    Предложение HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк.

    Задание 5. Вывести наименьшую оценку полученную студентом ‘01’ по каждому предмету.

    USE uch_proc

    SELECT ns, min(ozenka) as 'наихудшая' , kp

    FROM uspevaemost

    group by ns, kp

    HAVINGns = ‘01’

    4. Упорядочение вывода полей.Команда ORDER BY

    Таблицы - это неупорядоченные наборы данных, и данные которые выходят из их, не обязательно появляются в какой-то определенной последовательности. SQL использует команду ORDER BY чтобы позволять вам упорядочивать ваш вывод. Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого, также как с GROUP BY, и вы можете определять возрастание ( ASC ) или убывание ( DESC ) для каждого столбца. По умолчанию установлено - возрастание.

    Обратите внимание что, во всех случаях, столбцы которые упорядочиваются должны быть указаны в выборе SELECT.

    Задание 6. Вывести по алфавиту список фамилий преподавателей

    USE uch_proc

    SELECT fio

    FROM prepodavatel

    orderby fio

    ORDER BY может кроме того, использоваться с GROUP BY для упорядочения групп. Если это так, то ORDER BY всегда приходит последним. Вот пример из Задания 4 с добавлением предложения ORDER BY. Перед группированием вывода, порядок студентов был произвольным.
    Задание 7. Вывести по порядку номера студентов в задании 4

    USE uch_proc

    SELECT ns, min(ozenka) as 'наихудшая' , kp

    FROM uspevaemost

    group by ns, kp

    order by ns
    Контрольные задания.
    1. По таблице Успеваемость определить среднюю оценку в каждой группе.

    2. По таблице Успеваемость определить количество оценок в каждой группе.

    3. Из таблицы Студент вывести список студентов по алфавиту.

    4. По таблице Изучение определить суммарное количество часов по предметам в каждой группе.

    5. По таблице Изучение определить суммарное количество часов по предметам с vidz =’пр’ в каждой группе

    6. Определить количество групп, изучающих предмет с кодом ‘01’

    7. Определите количество преподавателей на каждой кафедре.

    8. Сформулируйте и выполните запросы с использованием агрегатных функций в индивидуальной базе данных.

    Практическая работа №6
    «Создание многотабличного запроса»

    Цель: «Сформировать навыки и умения реализации запроса, построенного на основе данных из нескольких таблиц»
    Ход Работы

    Одна из наиболее важных особенностей запросов SQL - это их способность определять связи между многочисленными таблицами и выводить информацию из них в терминах этих связей.

    Эта особенность часто используется просто для эксплуатации связей встроенных в базу данных.

    Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого точкой и затем именем столбца. Имеются несколько примеров имен :

    Student. Ns

    Student.fio

    Predmet.np

    До этого, вы могли опускать имена таблиц, потому что вы запрашивали только одну таблицу одновременно.
    Когда данные выбираются из таблиц, связанных отношением один ко многим, то кроме условия запроса после ключевого слова WHERE записываются связи по ключевым полям. Например, если данные выбираются из таблиц student, gruppa , то необходимо указать равенство по связующим полям:

    student.ng= gruppa.ng

    Задание 1. Вывести на экран фамилии студентов, номер группы, в которой учится более 30 человек.

    USE uch_proc

    select student.fio, gruppa.ng, gruppa.kol

    from student, gruppa

    where student.ng= gruppa.ng and gruppa.kol>30

    Если данные выбираются из таблиц, напрямую не связанных между собой, то указываются все таблицы, находящиеся между ними.

    Задание 2. Вывести на экран фамилии преподавателей математики.

    Таблицы ‘преподаватель’ и ‘предмет’ не связаны между собой. Но они имеют общие связующие поля с таблицей ‘изучение’



    Рис 5 схема данных запроса

    USE uch_proc

    select prepodavatel.fio, predmet.np

    from prepodavatel, predmet, isuchenie

    where prepodavatel.tabn = isuchenie.tabn and

    isuchenie.kp= predmet.kp and predmet.np='математика'
    Задание 3 Вывести на экран фамилии преподавателей , количество часов, код предмета, номера групп, в которых ведут преподаватели с кафедры ‘информатика’



    Рис 6 схема данных запроса
    USE uch_proc

    select prepodavatel.fio, isuchenie.ng, isuchenie.chas, isuchenie.kp

    from prepodavatel, isuchenie, kafedra

    where prepodavatel.tabn = isuchenie.tabn and

    prepodavatel.kkaf =kafedra.kkaf and kafedra.namekaf='информатика'
    Задание 4 Определите номер и фамилию студентов, которые получали оценку 4



    Рис 7 схема данных запроса
    USE uch_proc

    select student.fio, student.ns , uspevaemost.ozenka

    from student, uspevaemost

    where student.ng= uspevaemost.ng and student.ns= uspevaemost.ns and uspevaemost.ozenka=4

    Задание 5 Определите наименования предметов, по которым студенты получали 2


    Рис 8 схема данных запроса
    USE uch_proc

    select uspevaemost.ozenka , predmet.np

    from uspevaemost, isuchenie, predmet

    where uspevaemost.ng = isuchenie.ng and uspevaemost.kp = isuchenie.kp and

    uspevaemost.tabn = isuchenie.tabn and uspevaemost.vidz = isuchenie.vidz and

    isuchenie.kp= predmet. kp and uspevaemost.ozenka=2

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

    1. Выведите на экран фамилии студентов, которые получали оценки 3 или 4.

    2. Определите количество студентов, получивших каждую оценку. ( 2, 3, 4, 5)

    3. Выведите на экран оценки, вид занятия, наименование предмета, по которому студент ‘Аристов’ получал оценки

    4. Выведите номер группы, количество человек в группе, в которой обучается студентка ‘Борисова’

    5. Укажите фамилии преподавателей и номера групп, в которых изучаются предметы с количеством часов больше 100

    6. Определите наименования предметов, которые читаются преподавателями с кафедры ‘информатика’

    7. Выведите на экран фамилии преподавателей, которые ведут предметы в группе ‘102’

    8. Сформулируйте и создайте несколько многотабличных запросов в индивидуальной базе данных

    Практическая работа №7
    «Использование подзапросов»

    Цель: «Сформировать навыки и умения реализации вложенных подзапросов, построенных на основе данных из нескольких таблиц»

    Ход Работы
    1. Построение подзапросов.

    Часто невозможно решить поставленную задачу путем использования одного запроса. Это особенно актуально в тех случаях, когда при использовании условия поиска в предложении WHERE
    <сравниваемое значение> <оператор> <значение, с которым сравнивать>

    значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT.

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

    Внутренний подзапрос представляет собой также оператор SELECT и кодирование его предложений подчиняется тем же правилам, что и для основного оператора SELECT.

    В общем случае оператор SELECT с подзапросом имеет вид

    SELECT ...

    FROM ...

    WHERE <сравниваемое значение> <оператор> SELECT ...

    FROM ... WHERE ...

    Задание 1. С помощью подзапроса вывести номера студентов, обучающихся в группе с количеством человек >32



    Рис 9 схема данных запроса


    Внешний запрос

    use uch_proc

    select fio

    from student

    Внутренний запрос

    where ng in ( select ng

    from gruppa

    where kol > 32 )

    В начале выполняется подзапрос, в котором выбираются все группы с количеством человек более 32. Внешний запрос выбирает запись из таблицы ‘студент’ и определяет входит ли номер группы в список номеров групп, выбранных подзапросом.

    Задание 2. С помощью подзапроса вывести ФИО преподавателей, которые ведут предметы, с количеством часов chas > 150



    Рис 10 схема данных запроса
    use uch_proc

    select fio

    from prepodavatel

    where tabn in ( select tabn

    from isuchenie

    where kp in ( select kp

    from predmet

    where chas > 150 and ng ='102'

    ) )

    В начале выполняется подзапрос, в котором выбираются коды предметов с количеством часов более 150 и для группы ‘101’ . Второй подзапрос выбирает табельные номера преподавателей, которые ведут предметы, указанные в 1-м подзапросе. Внешний запрос выбирает запись из таблицы ‘преподаватель’ и определяет входит ли его табельный номер в список значений второго подзапроса.

    Задание 3.

    С помощью подзапроса вывести табельный номер преподавателей, которые ведут предмет ‘история’ в группе ‘101’.



    Рис 11 схема данных запроса

    use uch_proc

    select tabn

    from isuchenie

    where ng = '101' and kp in ( select kp

    from predmet

    where np ='история'

    )

    Подзапрос выбирает код предмета с названием 'история'. Внешний запрос выводит табельные номера преподавателей, которые ведут предмет с таким номером в группе ‘101’

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

    EXISTS (<подзапрос> )

    Это предложение содержит истину, если запрос возвращает хотя бы одну запись.

    Задание 4.

    Вывести ФИО студентов, которые хотя бы раз получали ‘4’.



    Рис 12 схема данных запроса

    use uch_proc

    select fio

    from student

    where ns in ( select ns

    from uspevaemost

    where ozenka = 4 and uspevaemost.ns= student.ns and uspevaemost.ng= student.ng

    )

    Если студент хотя бы раз получал 4, то его номер будет выбран в подзапросе.

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


    1. С помощью подзапроса вывести фамилии преподавателей с кафедры ‘иностранный язык’.

    2. С помощью подзапроса вывести фамилии преподавателей, которые ведут предметы с vidz=’пр’.

    3. С помощью подзапроса вывести наименование предметов, у которых в таблице ‘изучение’ количество часов более 70.

    4. С помощью подзапроса вывести номер группы и количество человек в ней, если в этой группе ведет преподаватель с табельным номером ‘103’

    5. Вывести ФИО преподавателей, которые ведут хотя бы один предмет.

    6. Вывести ФИО студентов, которые хотя бы раз получали ‘2’. или ‘3’.

    7. Определите с помощью подзапроса номера студентов, группу и их оценки, кто изучает предмет с кодом ‘02’ и видом занятия ‘пр’.

    8. Сформулируйте и создайте несколько подзапросов в индивидуальной базе данных

    Практическая работа №8
    «Реализация запросов для нахождения минимального и максимального значений»

    Цель: «Сформировать навыки и умения реализации вложенных подзапросов, построенных для нахождения минимального и максимального значений»
    Ход Работы
    Для того, чтобы рассмотреть запросы на нахождение минимального и максимального значений, создадим базу данных ‘склад’. Она состоит из таблиц:
    Tovar ( kod_tov, zena), post ( kod_post, name), postavka (n_post, data, kol, kod_tov, n_sklad)

    Rashod_tov (n_rash, data, kol, kod_tov, n_sklad)
    Задание1. Создайте структуру приведённых таблиц. Заполните таблицы данными.


    Рис 13 схема данных запроса



    Tovar




    post

    kod_tov


    kod_post

    zena




    kod_post

    name

    001

    12

    120




    11

    «Весёлый молочник»

    002

    11

    300




    12

    «Сластёна»

    003

    14

    340




    13

    «Колокольчик»

    004

    11

    100




    14

    «Южная ночь»


    Postavka

    n_post

    data

    kol

    kod_tov

    n_sklad

    01

    11 сентября

    100

    003

    3

    02

    11 сентября

    200

    001

    2

    03

    13 сентября

    50

    003

    1

    04

    16 сентября

    130

    002

    1

    05

    16 сентября

    230

    004

    2

    06

    18 сентября

    70

    003

    2

    07

    20 сентября

    200

    001

    2


    Rashod

    n_rash

    data

    kol

    kod_tov

    n_sklad

    111

    1 октября

    30

    003

    1

    112

    1 октября

    10

    004

    2

    113

    2 октября

    50

    001

    2

    114

    3 октября

    20

    004

    2

    115

    4 октября

    15

    002

    1


    Задание2. Выведите код товара с минимальной ценой
    use sklad

    Select zena , kod_tov

    From tovar

    where zena in (

    Select min ( zena)

    From tovar

    )

    При выполнении подзапроса определяется величина минимальной цены ( в примере это 100). Во внешнем запросе цена каждого товара сравнивается с найденной минимальной ценой, если совпадает, то запись выводится на экран. Вместо равенства используется оператор in .

    Задание3. По таблице ‘Postavka’ определите дату и склад с максимальной величиной поставки.

    use sklad

    Select data , n_sklad,kol

    From Postavka

    where kol in (

    Select max ( kol)

    From Postavka

    )

    При выполнении подзапроса определяется величина максимального количества ( в примере это 230). Во внешнем запросе количество каждой поставки сравнивается с найденным максимальным количеством, если совпадает, то запись выводится на экран.
    В приведённых примерах значение максимума и минимума определяется по числовым полям таблицы. Но иногда возникает необходимость вычислить значения по вычисляемым полям.
    Использование ALL, SOME

    Если в условиях поиска необходимо указать, что сравниваемое значение (значение столбца, результат вычисления выражения) должно находиться в определенных отношениях со всеми значениями из множества значений, возвращаемых подзапросом, применяют предложение типа
    <сравниваемое значение> {[NOT] <оператор>

    {ALL | SOME | ANY} (<подзапрос>)

    где подзапрос может возвращать более одного значения. Операторопределяет операцию сравнения (>, >=, < и т.д.). Отношение сравниваемого значения и значений, возвращаемых подзапросом, устанавливается словами ALL и SOME (ANY).


    • ALL определяет, что условие поиска истинно, когда сравниваемое значение находится в отношении, определяемом оператором, со всеми значениями, возвращаемыми подзапросом.


    Например:

    .

    WHERE STOLBEZ ALL ( SELECT POLE FROM ТABLIZA)

    определяет, что текущее значение столбца STOLBEZ должно быть больше всех значений в столбце POLE из таблицы ТABLIZA


    • SOME (вместо него можно указать ANY) что условие поиска истинно, когда сравниваемое значение находится в отношении, определяемом оператором, хотя бы с одним значением, возвращаемым подзапросом.

    Например:

    WHERE STOLBEZ > SOME (SELECT POLE FROM Т ABLIZA)

    определяет, что текущее значение столбца STOLBEZ должно быть больше хотя бы одного значения в столбце POLE из таблицы ТABLIZA

    Задание4. По таблице ‘Postavka’ определите склад, на который поставлено максимальное количество товара.

    use sklad

    select n_sklad, sum( kol) as sum_kol

    from Postavka

    groupby n_sklad

    having sum( kol) >= All (

    select sum( kol)

    from Postavka

    groupby n_sklad)

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

    В этом запросе не было готового поля, по которому определяется наибольшее количество. Для реализации запроса использовался оператор ALL.
    Задание5. Определить имя поставщика, который поставляет минимальный перечень товара. При построении этого запроса используется две таблицы : Tovar, post. Для того, чтобы узнать сколько наименований товара поставляет каждый поставщик используется конструкция

    1)select count(kod_tov)

    from tovar

    groupby kod_post

    Это самый вложенный подзапрос. Чтобы узнать код поставщика, который поставляет минимальный перечень товара, нужно записать код:

    2)select kod_post

    fromtovar

    groupby kod_post

    havingcount(kod_tov) >= All(

    select count(kod_tov)

    from tovar

    groupby kod_post )

    Имя поставщика находится в таблице post. Нам необходимо выбрать тех поставщиков, у которых, код совпадает со значениями, полученными в списке второго подзапроса. Итоговый вид запроса:

    3)use sklad

    select name

    from post

    where kod_post in (

    select kod_post

    from tovar

    groupby kod_post

    having count(kod_tov) >= All(

    select count(kod_tov)

    from tovar

    groupby kod_post )

    )

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


    1. Определить количество и дату, когда было отпущено минимальное количество товара.

    2. Определить имя поставщика, который поставляет товар, с максимальной ценой.

    3. Для каждой даты определите сумму отпущенного товара. На основе этого запроса определите дату с максимальной суммой расхода.

    4. Определите номер склада, с которого отпускалось минимальное количество товара.

    5. Определите код товара, который поставлялся на максимальное количество складов.

    6. Сформулируйте и выполните запросы на нахождение минимального и максимального значений в своей базе данных.

    Практическая работа №9

    1   2   3   4   5   6   7   8   9


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