|
|
Практическая работа. Взаимодействие MySQL и PHP.. Методические указания для выполнения практических работ
Ход Работы
SQL символизирует собой Структурированный Язык Запросов. Запросы - вероятно наиболее часто используемый аспект SQL. Запрос - команда которую вы даете вашей программе базы данных, и которая сообщает ей чтобы она вывела определенную информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера.
1. Формат команды SELECT
Общий вид команды:
SELECT * | { [ DISTINCT | ALL] < value expression >.,..}
FROM { < table name > [ < alias > ] }.,..
[ WHERE ]
[ GROUP BY { | }.,..]
[ HAVING ]
[ ORDERBY { | }.,..]
В самой простой форме, команда SELECT просто инструктирует базу данных чтобы извлечь информацию из таблицы. Например, вы могли бы вывести таблицу ‘Студент’ напечатав следующее:
SELECT ns,fio FROM student
Эта команда просто выводит все данные из таблицы.
SELECT Ключевое слово которое сообщает базе данных что эта
команда - запрос. Все запросы начинаются этим словом
ns, fio Это - список столбцов из таблицы которые выбираются
запросом. Любые столбцы не перечисленные здесь не
будут включены в вывод команды. Это, конечно, не значит
что они будут удалены или их информация будет стерта
из таблиц, потому что запрос не воздействует на
информацию в таблицах; он только показывает данные/
FROM - ключевое слово, подобно SELECT, которое должно
быть представлено в каждом запросе. Оно сопровожда-
ется пробелом и затем именем таблицы используемой
в качестве источника информации.
В данном случае - это таблица student
Если вы хотите видеть каждый столбец таблицы, имеется необязательное сокращение которое вы можете использовать. Звездочка (*) может применяться для вывода полного списка столбцов следующим образом:
SELECT * FROM student;
Это приведет к тому же результату что и наша предыдущая команда.
DISTINCT (ОТЛИЧИЕ) - аргумент который обеспечивает Вас способом устранять двойные значения из вашего предложения SELECT. Предположим что вы хотите знать какие студенты получали оценки.
SELECT DISTINCT ns
FROM uspevaemost
Другими словами, DISTINCT следит за тем, какие значения были ранее, так что бы они не были продублированы в списке. Это - полезный способ избежать избыточности данных. DISTINCT опускает строки где все выбранные поля идентичны. Строки в которых некоторые значения одинаковы а некоторые различны - будут сохранены.
WHERE - предложение команды SELECT, которое позволяет вам устанавливать предикаты, условие которых может быть или верным или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы для которой такое утверждение верно. Например, предположим вы хотите видеть номера групп с количеством студентов более 20 человек.
SELECT ng
FROM gruppa
WHERE kol > 20
Когда предложение WHERE представлено, программа базы данных просматривает всю таблицу по одной строке и исследует каждую строку чтобы определить верно ли утверждение.
2. Реляционные операторы
Реляционный оператор - математический символ который указывает на определенный тип сравнения между двумя значениями. Реляционные операторы которыми располагает SQL :
= Равный к
> Больше чем
< Меньше чем
>= Больше чем или равно
<= Меньше чем или равно
<> Не равно
Основные Булевы операторы также распознаются в SQL. . Стандартными операторами Буля распознаваемыми в SQL являются:
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;
Контрольные задания.
Из таблицы Успеваемость выведите код предмета, табельный номер преподавателя, номер студента, который получал двойку.
Выведите на экран количество студентов в ‘101’ группе
Создайте запрос, который выводит на экран код кафедры ‘математика’
Из таблицы Преподаватель выведите на экран фамилии и табельные номера преподавателей с кафедры ‘02’
Из таблицы Изучение выведите на экран номер группы и табельный номер преподавателя, который ведёт предмет с количеством часов ( chas) более 50 .
По разработанной базе данных сформулируйте и выполните запросы с использованием операторов: 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 имеется конструкция для организации многократных повторений команд в программе : WHILE………CONTINUE. Одна обеспечивает выполнение цикла одного типа. Это цикл с предусловием. Синтаксис конструкции:
WHILE < условие>
{ команда | блок }
[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
Контрольные задания.
По таблице ‘успеваемость’ определить получал студент с номером ‘01’ оценки. Если получал, то вывести на печать сообщение ‘Студент опрашивался’ в противном случае напечатать ‘Студент не получал оценок’ .
Если вид занятия ‘лек’, то в сформированном столбце записать «лекционное занятие», если вид занятия ‘пр’, то в сформированном столбце записать «практическое занятие»
В индивидуальной базе данных сформулировать и привести пример с использованием оператора LIKE
В индивидуальной базе данных сформулировать и привести пример с использованием оператора 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
Контрольные задания.
Выведите на экран фамилии студентов, которые получали оценки 3 или 4.
Определите количество студентов, получивших каждую оценку. ( 2, 3, 4, 5)
Выведите на экран оценки, вид занятия, наименование предмета, по которому студент ‘Аристов’ получал оценки
Выведите номер группы, количество человек в группе, в которой обучается студентка ‘Борисова’
Укажите фамилии преподавателей и номера групп, в которых изучаются предметы с количеством часов больше 100
Определите наименования предметов, которые читаются преподавателями с кафедры ‘информатика’
Выведите на экран фамилии преподавателей, которые ведут предметы в группе ‘102’
Сформулируйте и создайте несколько многотабличных запросов в индивидуальной базе данных
Практическая работа №7
«Использование подзапросов»
Цель: «Сформировать навыки и умения реализации вложенных подзапросов, построенных на основе данных из нескольких таблиц»
Ход Работы
1. Построение подзапросов.
Часто невозможно решить поставленную задачу путем использования одного запроса. Это особенно актуально в тех случаях, когда при использовании условия поиска в предложении WHERE
<сравниваемое значение> <оператор> <значение, с которым сравнивать>
значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT.
Другой причиной, которая должна побудить к использованию вложенных подзапросов, является то, что во многих случаях значение, с которым надо сравнивать, должно представлять собой не одно, а несколько значений.
Внутренний подзапрос представляет собой также оператор SELECT и кодирование его предложений подчиняется тем же правилам, что и для основного оператора SELECT.
В общем случае оператор SELECT с подзапросом имеет вид
SELECT ...
FROM ...
WHERE <сравниваемое значение> <оператор> SELECT ...
FROM ... WHERE ...
Задание 1. С помощью подзапроса вывести номера студентов, обучающихся в группе с количеством человек >32
Рис 9 схема данных запроса
u se uch_proc
select fio
from student
w here 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, то его номер будет выбран в подзапросе.
Контрольные задания.
С помощью подзапроса вывести фамилии преподавателей с кафедры ‘иностранный язык’.
С помощью подзапроса вывести фамилии преподавателей, которые ведут предметы с vidz=’пр’.
С помощью подзапроса вывести наименование предметов, у которых в таблице ‘изучение’ количество часов более 70.
С помощью подзапроса вывести номер группы и количество человек в ней, если в этой группе ведет преподаватель с табельным номером ‘103’
Вывести ФИО преподавателей, которые ведут хотя бы один предмет.
Вывести ФИО студентов, которые хотя бы раз получали ‘2’. или ‘3’.
Определите с помощью подзапроса номера студентов, группу и их оценки, кто изучает предмет с кодом ‘02’ и видом занятия ‘пр’.
Сформулируйте и создайте несколько подзапросов в индивидуальной базе данных
Практическая работа №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 )
)
Контрольные задания.
Определить количество и дату, когда было отпущено минимальное количество товара.
Определить имя поставщика, который поставляет товар, с максимальной ценой.
Для каждой даты определите сумму отпущенного товара. На основе этого запроса определите дату с максимальной суммой расхода.
Определите номер склада, с которого отпускалось минимальное количество товара.
Определите код товара, который поставлялся на максимальное количество складов.
Сформулируйте и выполните запросы на нахождение минимального и максимального значений в своей базе данных.
Практическая работа №9
|
|
|
Скачать 1.47 Mb.