2. Правила выполнения лабораторных работ

Студент должен:

• строго выполнять весь объём домашней подготовки, указанный в описаниях соответствующих лабораторных работ;

• знать, что перед выполнением лабораторной работы необходимо получить допуск либо в виде ответов на вопросы преподавателя, либо через тест по теме работы (N:\ задание \ фамилия преподавателя \название теста);

• при выполнении задания выбирать наиболее оптимальный метод решения поставленной задачи и объяснить преподавателю по каким критериям выбран тот или иной метод;

• результаты работы сохранять в отдельной папке по дисциплине на диске, отведённом для выполнения лабораторных работ;

• в конце каждого занятия преподавателю предъявить результаты т ответить на вопросы;

• отчёт может формироваться по каждой лабораторной работе или по комплексу лабораторных работ;

• защитить лабораторные работы. Защита включает оценку выполненного отчёта и оценку ответов на вопросы.

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

Отработка пропущенных работ выполняется по согласованию с преподавателем и только в его присутствии.

3.Описание рабочего места студента для выполнения лабораторных работ

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

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

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

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

После окончания лабораторной работы студент должен:

• выключить компьютер;

• проверить порядок на рабочем месте;

• сдать рабочее место преподавателю.

Лабораторные работы №1,2

Изучение операторов языка SQL

Цель работы: получить начальные навыки работы с оператором SELECT в среде СУБД Visual FoxPro.

Пояснения к работе

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

SELECT *

FROM Salespeople;

Результат выполнения этой команды тот же, что и для рассмотренной ранее. Мощность команды SELECT заключается в ее свойстве извлекать из таблицы лишь определенную информацию. Надо отметить возможность просмотра толь­ко указанных столбцов таблицы. Для этого достаточно пропустить столбцы, которые нет необходимости просматривать, в части команды SELECT. Напри­мер,

SELECT sname, comm FROM Salespeople;

Устранение избыточных данных

DISTINCT – аргумент дающий возможность исключить дублирующиеся значения из результата выполнения предложения SELECT. Предположим, необходимо узнать, какие продавцы имеют в настоящее время заказы в таблице Orders. Для того чтобы получить список без повторений, который легче прочесть, нужно ввести следующую команду:

SELECT DISTINCT snum

FROM Orders;

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

DISTINCT можно задать только один раз для данного предложения SELECT. Если SELECT извлекает множество полей, то он исключает строки, в которых все выбранные поля идентичны. Строки, в которых некоторые значения одинаковы, а другие — различны, включаются в результат. DISTINCT, фактически, действует на всю выходную строку, а не на отдельное поле (исключе­ние составляет его применение внутри агрегатных функций), исклю­чая возможность их повторения. Альтернативой DISTINCT является ALL. Это ключевое слово имеет противоположное действие: повторяющиеся строки включаются в состав выходных данных. Поскольку часто бывает так, что не заданы ни DISTINCT, ни ALL, предполагается ALL; это ключевое слово имеет преимущество перед функциональным аргументом.

Определение выборки — предложение WHERE

Таблицы бывают достаточно большими с тенденцией к увеличению по мере добавления строк. В данный момент времени интересны только некоторые стро­ки таблицы. SQL дает возможность задать критерий определения строк, которые следует включить в состав выходных данных. Предложение WHERE команды SELECT позволяет определить предикат, условие, которое может быть либо истинным, либо ложным для каждой строки таблицы. Команда извлекает только те строки из таблицы, для которых предикат имеет значение "истина". Предпо­ложим, необходимо узнать имена всех продавцов в Лондоне (London). В этом случае можно ввести следующую команду:

SELECT sname, city

FROM Salespeople WHERE city = 'London';

При наличии предложения WHERE программа обработки базы данных про­сматривает таблицу строка за строкой и для каждой строки проверяет, истинен ли на ней предикат. Столбец city включен в результат не потому, что он указан в предложении WHERE, а потому, что имя этого столбца указано в предложении SELECT. Совершенно необязательно, чтобы столбец, используемый в предложении WHERE, был представлен в числе тех столбцов, которые необходимо видеть среди выходных данных. Можно выбрать всех покупателей (Customers) с рейтингом (rating) 100 следующим образом:

SELECT *

FROM Customers

WHERE rating = 100;

Здесь не используются одиночные кавычки, поскольку поле rating является числовым.

Использование реляционных и булевых операторов.

Реляционный оператор — это математический символ, который задает оп­ределенный тип сравнения между двумя значениями. Уже известно как приме­няются равенства, такие как 2+3=5 или city = 'London'. Однако существуют и другие операторы сравнения. Предположим, необходимо вычислить продавцов (Salespeople), комиссионные (commissions) которых превышают заданное значе­ние. В этом случае следует воспользоваться сравнением типа "больше или равно". SQL распознает следующие операторы сравнения:

= Равно

> Больше, чем

< Меньше, чем

>= Больше или равно

<= Меньше или равно

<> Неравно

Эти операторы имеют стандартное значение для числовых величин. Их оп­ределение для символьных значений зависит от используемого формата пред­ставления (ASCII или EBCDIC). SQL сравнивает символьные значения в терминах соответствующих чисел, определенных в формате преобразования. Символьные значения, представляющие числа, например, необязательно равны тому числу, которое они представляют.

Предположим, необходимо увидеть всех покупателей (Customers) с рейтин­гом (rating) более 200. Поскольку 200 — это скалярное значение, как и все значения столбца rating, для их сравнения можно использовать оператор отно­шения:

SELECT *

FROM Customers

WHERE rating > 200;

При необходимости увидеть всех покупателей, рейтинг (rating) которых боль­ше или равен 200, следовало бы использовать предикат:

rating >= 200.

SQL распознает основные булевы операторы. Далее перечислены булевы операторы и основные принципы их действия:

• AND берет два булевых выражения (в виде A AND В) в качестве аргументов и дает в результате истину, если они оба истинны.

• OR два булевых выражения (в виде A OR В) в качестве аргументов и оценивает результат как истину, если хотя бы один из них истинен.

• NOT берет единственное булево выражение (в виде NOT А) в качестве аргу­мента и изменяет его значение с истинного на ложное или с ложного на истинное.

Используя предикаты с булевыми операторами, можно значительно увели­чить их избирательную мощность. Предположим, необходимо увидеть всех по­купателей (customers) из San Jose, чей рейтинг (rating) превышает 200:

SELECT *

FROM Customers

WHERE city = 'San Jose' AND rating > 200;

При использовании OR , будут получены сведения обо всех тех покупателях (customers), которые либо проживают в San Jose, либо имеют рейтинг (rating), превышающий 200.

SELECT *

FROM Customers

WHERE city = 'San Jose' OR rating > 200;

NОТ дает возможность получить отрицание (противоположное значение) булева выражения. Вот пример запроса с использованием NOT:

SELECT*

FROM Customers

WHERE city ='San Jose'

OR NOT rating > 200;

Заметим, что оператор NOT должен предшест­вовать булеву выражению, значение которого он должен изменить, но не может располагаться непосредственно перед оператором сравнения.

Можно по­лучить другой результат по следующей команде:

SELECT *

FROM Customers

WHERE NOT (city = 'San Jose'

OR rating > 200);

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

Использование специальных операторов в «условиях»

Оператор IN

IN полностью определяет множество, которому данное значение может при­надлежать или не принадлежать. Если нужно найти всех продавцов, располо­женных либо в 'Barcelona', либо в 'London', основываясь только на том, что известно к настоящему моменту, необходимо написать следующий запрос:

SELECT *

FROM Salespeople

WHERE city IN ('Barcelona', 'London');

Как видно из примера, IN определяет множество, элементы которого точно перечисляются в круглых скобках и разделяются запятыми. Если в поле, имя которого указано слева от IN, есть одно из перечисленных в списке значений (требуется точное совпадение), то предикат считается истинным. Если элементы множества имеют числовой, а не символьный тип, то одиночные кавычки непо­средственно слева и справа от значения необходимо опустить. Можно найти всех покупателей, обслуживаемых продавцами 1001, 1007, 1004.

SELECT *

FROM Customers

WHERE snum IN (1001,1007,1004);

Оператор BETWEEN

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

Следующий запрос позволит извлечь из таблицы Salespeople всех продавцов (salespeople), комиссионные которых имеют величину в диапазоне .10 и .12:

SELECT

FROM Salespeople

WHERE comm BETWEEN .10 AND. 12;

Оператор BETWEEN является включающим, т.е. граничные значения (в дан­ном примере это .10 и .12) делают предикат истинным. SQL непосредственно не поддерживает исключающий BETWEEN. Необходимо сформулировать гра­ничные значения так, чтобы включающая интерпретация была справедлива, либо сделать примерно следующую запись:

SELECT *

FROM Salespeople

WHERE (comm BETWEEN .10, AND.12) AND NOT comm IN (.10, .12);

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

Следующий запрос выбирает всех покупателей имена которых попадают в заданный алфавитный диапазон:

SELECT *

FROM Customers

WHERE cname BETWEEN 'A' AND 'G';

Оператор LIKE

LIKE применим только к полям типа CHAR или VARCHAR, поскольку он используется для поиска подстрок. Другими словами, он осуществляет просмотр строки для выяснения: входит ли заданная подстрока в указанное поле. С это же целью используются шаблоны — специальные символы, которые могут обозначать все, что угодно. Существует два типа шаблонов, используемых с LIKЕ:

• Символ "подчеркивание" (_) заменяет один любой символ. Например, образцу 'b_t' соответствуют 'bat' или 'bit', но не соответствует 'brat'.

• Символ "процент" (%) заменяет последовательность символов произвольной длины, в том числе и нулевой. Например, образцу '%p%t' соответствуют 'put', 'posit', 'opt', но не 'spite'.

Можно найти покупателей, фамилии которых начинаются на 'G' :

SELECT *

FROM Customers

WHERE cname LIKE 'G%';

Работа с NULL-значениями

Часто в таблице встречаются записи с незаданными значениями какого-либо полей, потому что значение поля неизвестно или его просто нет. В таких случаях SQL позволяет указать в поле NULL-значение. Строго говоря, NULL-значение вовсе не представлено в поле. Когда значение поля есть NULL это значит, что программа базы данных специальным образом помечает поле, как не содержащее какого-ибо значения для данной строки (записи). Дело обстоит не так в случае простого приписывания полю значения "нуль" или "пробел", которые база данных трактует как любое другое значение. Поскольку NULL не является значением как таковым, он не имеет типа данных. NULL может раз­даться в поле любого типа. Тем не менее, NULL, как NULL-значение, часто используется в SQL.

Оператор IS NULL

Поскольку NULL фиксирует пропущенные значения, результат любого сравнения при наличии NULL-значений неизвестен. Когда NULL-значение сравнивается с любым значением, даже с NULL-значением, результат просто неизвестен. Булево значение "неизвестно" ведет себя также, как "ложь" — строка на которой предикат принимает значение "неизвестно", не включается в результат запроса — при одном важном исключении: NOT от лжи есть истина (NOT(false)=true), тогда как NOT от неизвестного значения есть также неизвестное значение. Следовательно, такое выражение как "city = NULL" или "city IN (NULL)” является неизвестным независимо от значения city.

Часто необходимо различать false и unknown — строки, содержащие значения не удовлетворяющие предикату, и строки, которые содержат NULL. Для этой цели SQL располагает специальным оператором IS, который используется с ключевым словом NULL для локализации NULL-значения.

Для нахождения всех записей со значениями NULL в таблице Customers в столбце city следует ввести:

SELECT *

FROM Customers

WHERE city IS NULL;

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

Специальные операторы, которые были рассмотрены в этой главе, могут непосредственно предшествовать булеву оператору NOT. Этим они отличаются от операторов сравнения, которые должны содержать NOT перед всем выраже­нием. Например, если не осуществляется поиск NULL-значений, а, напротив, необходимо исключить их из выходных данных, то нужно использовать NOT для того, чтобы придать предикату противоположное значение:

SELECT *

FROM Customers

WHERE City IS NOT NULL;

Если NULL-значения отсутствуют, то в результате выполнения этого запроса будет получена вся таблица Customers.

SELECT *

FROM Salespeople

WHERE city NOT IN ('London', 'San Jose');

Другой способ выразить то же самое:

SELECT *

FROM Salespeople WHERE NOT city IN ('London', 'San Jose');

Аналогичным образом можно использовать NOT BETWEEN и NOT LIKE.

Суммирование данных с помощью функций агрегирования.

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

• COUNT определяет количество строк или значений поля, выбранных посред­ством запроса и не являющихся NULL-значениями;

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

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

• МАХ вычисляет наибольшее из всех выбранных значений данного поля;

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

Функции агрегирования используются как имена полей в предложении запроса SELECT с одним исключением: имена полей применяются как аргументы. Для SUM и AVG могут использоваться только цифровые поля. Для COUNT, MAX и MIN — цифровые и символьные поля. При употреблении с символьными полями МАХ и MIN применяются к ASCII-эквивалентам: MIN предполагает минимальное (пер­вое), а МАХ — максимальное (последнее) значения в соответствии с алфавитным порядком.

Чтобы найти сумму (SUM) всех заявок из таблицы Orders, можно ввести следующий запрос:

SELECT SUM(amt)

FROM Orders;

Специальные атрибуты в COUNT

Функция COUNT отличается от предыдущих тем, что подсчитывает

количество значений в данном столбце или количество строк в таблице. Когда подсчитываются значения по столбцу, в команде используется DISTINCT для подсчета числа различных значений данного поля. Можно использовать его, например для подсчета количества продавцов, имеющих в настоящее время заказы в лице Orders:

SELECT COUNT (DISTINCT snum)

FROM Orders;

В данном примере DISTINCT вместе со следующим за ним именем поля, к которому он применяется, заключен в круглые скобки и не следует непосредственно за SELECT, как это было ранее. Такая форма применения DISTINCT с COUNT к отдельным столбцам предписывается стандартом ANSI, но многие программы не придерживается этого требования. Указанным способом DISTINCT можно применять с любой функцией агрегирования, но чаще всего он используется с COUNT. Применение его с МAX и MIN бесполезно; а используя SUM и AVG, необходимо включение в выходные данные повторяющихся значений, так как они влияют на сумму и средне значений всех столбцов.

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

SELECT COUNT (*)

FROM Customers;

Использование дубликатов в агрегатных функциях. Агрегатные функции могут также (во многих реализациях) иметь аргумент ALL, который размещается перед именем поля, как и DISTINCT, но обозначает противоположное: включить дубликаты. Требования ANSI не допускают подобного для COUNT, но многие реализации игнорируют это ограничение. Различие между ALL и * при использовании COUNT заключается в следующем:

• ALL использует имя поля в качестве аргумента;

• ALL не подсчитывает NULL-значения.

Поскольку * является единственным аргументом, который включает NULL-значения и используется только с COUNT, функции, отличные от COUNT, игнорируют NULL-значения в любом случае. Следующая команда осуществляет подсчет количества значений поля rating, отличных от NULL-значений, в таб­лице Customers (включая повторения):

SELECT COUNT (ALL rating)

FROM Customers;