Методическое пособие

Организация SQLзапросов

по дисциплине «Базы данных»

для студентов специальности 230105 «Программное обеспечение вычислительной техники и автоматизированных систем»

2010

Оглавление

Введение 4

I.Основные понятия реляционных баз данных. 4

II.Типы данных SQL. 9

III.Выборка данных SELECT 11

IV.Операторы IN, BETWEEN, LIKE, IS NULL. 12

Практическая работа «Организация простейших SQL-запросов». 15

V.Числовые, символьные и строковые константы 17

Практическая работа «Выполнение запросов с параметрами». 20

VI.Итоговые (агрегирующие) функции. 21

Практическая работа «Использование функций». 24

VII.Вложенные подзапросы 25

Практическая работа «Использование вложенных подзапросов». 29

VIII.Оператор объединения UNION 30

Практическая работа «Использование объединения таблиц». 31

IX.Соединения таблиц с использованием оператора JOIN 31

Практическая работа «Внешнее соединение таблиц». 33

Приложение1 «Учебная база данных» 34

Приложение «Перечень операторов» 38

Введение

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

Неотъемлемая и важнейшая часть любой системы, применяющей базы данных,- языковые средства, обеспечивающие возможность доступа и действий над данными, определения их структур. Способов использования и интерпретации. Язык SQL появился в 1970-е годы как одно из таких средств. Его прототип был разработан фирмой IBM и известен под названием SEQUEL(Structured English Query Language). SQL вобрал в себя достоинства реляционной модели, в частности достоинства лежащего в ее основе математического аппарата реляционной алгебры и реляционного исчисления, используя при этом сравнительно не большое число операторов и относительно простой синтаксис.

Язык SQL стал официально утвержденным в качестве стандарта- языком работы с реляционными базами данных. Этот стандарт поддерживается всеми ведущими мировыми фирмами, действующими в сфере технологий база данных. Использование выразительного и эффективного стандартного языка позволило обеспечить высокую степень независимости разрабатываемых прикладных программ от конкретного типа используемой СУБД, существенно поднять уровень и унификацию инструментальных средств разработки приложений, работающих с реляционными базами данных.

Данное учебное пособие предназначено студентам специальности 230105 «Программное обеспечение вычислительной техники и автоматизированных систем» и ориентировано на обучение основам применения языка SQL по учебным курсам, связанным с изучением информационных систем, базирующихся на базах данных. С этой целью в данном пособии большое внимание уделялось подбору материала для примеров, а также задач и упражнений, необходимых для получения практических навыков составления SQL- запросов к учебной базе данных (Приложение1).

1. Основные понятия реляционных баз данных.

Реляционная модель данных была предложена Е. Коддом, известным американским специалистом в области баз данных. Реляционная модель позволила решить одну из важнейших задач в управлении базами данных - обеспечить независи­мость представления и описания данных от прикладных программ, следствием чего было бы существенное упрощение проектирования и программирования баз дан­ных.

Реляционная модель данных представляет информацию в виде совокупности взаимосвязанных таблиц, которые принято называть отношениями или реляциями.

Базовые понятия.

№_студенческого_билета

Имя

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

Курс

Специальность

23980282

Алексеев

12.03.1982

2

Биология

22991380

Яковлев

25.12.1979

4

Физика

22657879

Михайлов

29.02.1979

5

Математика

24356783

Афанасьев

19.08.1983

1

Иностранный язык

24350283

Кузнецов

03.10.1982

1

Физика

23125681

Смирнов

26.03.1981

3

История

Имя

23980282 22991380 22657879 24356783 24350283 23125681Понятие тип данных в реляционной модели данных полностью эквивалентно соответствующему понятию в алгоритмических языках. Набор поддерживаемых типов данных определяется СУБД и может сильно различаться в разных системах.

Однако практически все СУБД поддерживают следующие типы данных:

• целочисленные;

• вещественные;

• строковые;

• специализированные типы данных для денежных величин;

• специальные типы данных для временных величин (дата и/или время).

Наименьшая единица данных реляционной модели - это отдельное атомарное (неразложимое) для данной модели значение данных.

 Доменом называется мно­жество атомарных значений одного и того же типа. Иными словами, домен пред­ставляет собой допустимое потенциальное множество значений данного типа. В нашем примере можно для каждого столбца таблицы определить домен:

• домены «Имена» и «Специальности» для столбцов «Имя» и «Специальность» со­ответственно будут базироваться на строковом типе данных (в частности, такие строки не должны начинаться с мягкого знака);

• домен «Даты_рождения» для столбца «Дата_рождения» определяется на базо­вом временном типе данных - данный домен содержит только допустимый диапазон дат рождения студентов;

• домены «Номера_курсов» и «Номера_студенческих_билетов» базируются на целочисленном типе - в число его значений могут входить только те целые числа, которые могут обозначать номер курса университета (обычно от 1 до 6) и номер студенческого билета (обязательно положительное число).

 Столбцы отношения называют атрибутами, им присваиваются имена, по кото­рым к ним затем производится обращение.

 Список имен атрибутов отношения с указанием имен доменов (или типов, если домены не поддерживаются) называется схемой отношения.

Схема нашего отношения СТУДЕНТ запишется так:

________________________________________________________________________________________

СТУДЕНТ {№_студенческого_билета Номера_студенческих_билетов

Имя Имена.

Дата_рождения Даты_рождения.

Курс Номера_курсов.

Специальность Специальности}

_______________________________________________________________________________

 Степень отношения - это число его атрибутов (количество полей таблицы).

 Кортеж, соответствующий данной схеме отношения, представляет собой множе­ство пар {имя атрибута. значение}, которое содержит одно вхождение каждого име­ни атрибута, принадлежащего схеме отношения. Кор­теж - это набор именованных значений заданного типа.

Пустые значения

В некоторых случаях какой-либо атрибут отношения может быть неприменим. На­пример, в рассматриваемом в качестве примера отношении СТУДЕНТЫ может также храниться информация о потенциальных абитуриентах, посещающих под­готовительные курсы вуза. В этом случае неприменимыми оказываются атрибуты «№_студенческого_билета» и «Курс» (так как абитуриенты еще не поступили в вуз и, следовательно, не имеют студенческого билета и не могут быть отнесены к како­му-либо курсу). Кроме того, иногда при вводе информации в строку реляционной таблицы некоторые данные могут быть неизвестны и выясняться позже. (Для наше­го примера - при поступлении на подготовительные курсы абитуриент еще не оп­ределился окончательно, на какую специальность он будет поступать.) В обоих указанных случаях в поля, соответствующие неприменимым или неизве­стным атрибутам, ничего не заносится, и строка записывается в базу данных с пу­стыми значениями этих атрибутов.

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

Для обозначения пустых значений полей используется слово NULL.

 Первичный ключ это некоторый атрибут или набор атрибутов, однозначно определяющий каждый кортеж отношения и обеспечивающий уникальность строк таблицы.

 Правило целостности объектов утверждает, что первичный ключ не может быть полностью или частично пустым, т.е. иметь значение NULL.

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

 Простой ключ - ключ, содержащий только один атрибут. В общем случае опера­ции объединения выполняются быстрее в том случае, когда в качестве ключа ис­пользуется самый короткий и самый простой из возможных типов данных. С этой точки зрения наилучшим образом подходит целочисленный тип, который имеет аппаратную поддержку для выполнения над ним логических операций. Сложный или составной ключ - ключ, состоящий из нескольких атрибутов.

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

 Искусственный - ключ, созданный самой СУБД или поль­зователем с помощью некоторой процедуры, который сам по себе не содержит ин­формации. Искусственный ключ используется для создания уникальных иден­тификаторов строк, когда сущность должна быть описана полностью, чтобы од­нозначно идентифицировать конкретный элемент. Искусственный ключ часто используют вместо значимого сложного ключа, который является слишком гро­моздким, чтобы использоваться в реальной базе данных. Система поддерживает искусственный ключ, но он никогда не показывается пользователю.

 Естественный ключ - ключ, в который включены значимые атрибуты и который, таким образом, содержит информацию.

 В любой из таблиц может оказаться несколько наборов атрибутов, которые можно выбрать в качестве ключа. Такие наборы называются потенциальными или аль­тернативными ключами.

 Нередко в отношениях определяются так называемые вторичные ключи. Вторич­ный ключ представляет собой комбинацию атрибутов, отличную от комбинации, составляющей первичный ключ. Причем вторичные ключи не обязательно обла­дают свойством уникальности.

При их определении могут задаваться следующие ограничения:

• UNIQUE - ограничение уникальности, значения вторичных ключей при дан­ном ограничении не могут дублироваться;

• NOT NULL - при данном ограничении ни один из атрибутов, входящих в со­став вторичного ключа, не может принимать значение NULL.

 Перекрывающиеся ключи - сложные ключи, которые имеют один или несколько общих столбцов.

 Внешний ключ - это атрибут (или множество атрибутов) одного отношения, явля­ющийся ключом другого отношения.

Внешний ключ отношения является ссылкой на первичный ключ другого отношения.

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

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

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

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

Условия целостности данных.

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

Категорийная целостность.

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

Ссылочная целостность.

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

Корпоративные ограничения целостности.

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

Типы связей между таблицами.

 При установлении связи между двумя таблицами одна из них будет являться глав­ной (master), а вторая - подчиненной (detail).

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

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

 У главной таблицы может быть несколько подчиненных, но у подчиненной табли­цы может быть только одна главная.

Различают четыре типа связей между таблицами реляционной базы данных:

• один к одному - каждой записи одной таблицы соответствует только одна за­пись другой таблицы;

• один ко многим - одной записи главной таблицы могут соответствовать несколь­ко записей подчиненной таблицы;

• многие к одному - нескольким записям главной таблицы может соответство­вать одна и та же запись подчиненной таблицы;

• многие ко многим - одна запись главной таблицы связана с несколькими запи­сями подчиненной таблицы, а одна запись подчиненной таблицы связана с не­сколькими записями главной таблицы.

 Тип связи многие ко многим в реляционной базе данных не реализуется.

Примеры:

СТУДЕНТЫ

! ID студ. билета

ФИО

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

Курс

Специальность

УСПЕВАЕМОСТЬ

1

1

! ID успеваемости

ID студ. билета

Предмет

Оценка

ЛИЧНЫЕ ДАННЫЕ

! ID студ. билета

Адрес проживания

Телефон

1

Как реализовать тип связи многие ко многим.

ФИЛЬМЫ

НОСИТЕЛИ

! ID фильма

Название фильма

Жанр

Режиссер

! ID носителя

Тип носителя

Стоимость

1   1

Х

ФИЛЬМЫ НА НОСИТЕЛЯХ

! ID по порядку

! ID фильма

! ID носителя



   

2. Типы данных SQL.

Язык определения данных-DDL(Data Definition Language)-дает возможность создания, изменения и удаление различных объектов базы данных(таблиц, индексов, пользователей, привилегий т.д.).

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

Язык обработки данных-DML(Data Manipulation Language)- предоставляет возможность выборки информации из базы данных и её преобразования - это компоненты единого SQL.

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

Тип данных «строка символов»

Представляет собой символьные строки фиксированной длины

CHARACTER [(длина)] или

CHAR [(длина)]

Текстовые значения поля таблицы, определенного как тип CHAR, имеют фиксированную длину, которая определяется параметром длина. Строка может содержать до 255 символов. Если во вводимой в поле текстовой константе фактическое число символов меньше числа, определенного параметром числа, определенного параметром длина. То эта константа автоматически дополняется справа пробелами до заданного числа символов.

Некоторые реализации языка SQL поддерживают в качестве типа данных строки переменной длины. Он описывает текстовую строку, которая может иметь произвольную длину до определенного конкретной реализации SQL максимума (в Oracle -до 2000 символов). В отличии от типа CHAR в этом случае при вводе текста константы, фактическая длина которой меньше заданной, не производиться её дополнение пробелами до заданного максимального значения. Константы типа CHARACTER и VARCHAR, в выражениях SQL заключается в одиночных кавычках, например, 'текст'.

Числовые типы данных

• INTEGER-используется для предоставления целых чисел в диапазоне от 2 - до .

• SMOLLINT-используется для предоставления целых чисел в меньшем, чем для INTEGER,в диапазоне, а именно - от

• DECIMAL(точность [,масштаб])- десятичное число с фиксированной точкой, точность определяет количество значащих цифр в числе. Масштаб указывает максимальное число цифр в числе. Масштаб указывает максимальное число цифр справа от точки.

• MERIC(точность[,масштаб])- десятичное число с фиксированной точкой, такое же, как и DECIMAL

• FLOAT[(точность)]- число с плавающей точкой указанной минимальной точностью.

• REAL- число такое же, как при типе FLOAT, за исключением определения точности по умолчания( в зависимости от конкретной реализации SQL).

• .DOUBLE PRECISION- число аналогичное REAL, но точность в два раза выше точности REAL.

СУБД Oracle использует дополнительно тип данных NUMBER для предоставления всех числовых данных, с фиксированной или плавающей точкой.

Его синтаксис:

NUMBER[(точность[,масштаб])]

Если значение параметра точность не указано явно, оно полагается равным 38. Значение параметра масштаб по умолчанию предполагает равным 0. Значения параметра точность может изменяться от 1 до 38; значения параметра масштаб может изменяться от -84 до 128. Использование отрицательных значений масштаба означает сдвиг десятичной точки в сторону старших разрядов. Например, определения NUMBER (7,-3) означает округление до тысячи

Дата и время

СУБД Oracle имеется тип DATE,используемый для хранения даты и времени. Поддерживаются даты, начиная от 1 января 4712 года до н.э до 31 декабря 4712 года. По умолчанию при наличии типа данных для хранения даты и времени позволяет поддерживать специальную арифметику дат и времен. Добавление к переменной DATE целого числа означает увеличение даты на соответствующее число дней, а вычитание соответствует определению более ранней даты. Константы типа DATE записываются в зависимости от формата, принятого в операционной системе.

Неопределенные или пропущенные данные(NULL)

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

• В агрегирующих функциях, позволяющих получить сводную информацию по множеству значений атрибута, например суммарное или среднее значение, для обеспечения точности и однозначности толкования результатов отсутствующие или NULL- значения атрибутов игнорируются.

• Условные операторы от булевой двузначной логике TRUE/FALSE расширяются до трехзначной логики TRUE/FALSE/UNKNOWN.

• Все операторы, за исключением оператора конкатенации строк «║», возвращают пустое значение (NULL), если значение любого из операндов отсутствует (имеет «значение NULL»).

• Для проверки на пустое значение следует использовать операторы IS NULL и IS NOT NULL (использование с этой целью оператора сравнения «=» является ошибкой).

• Функция преобразования типов, имеющие NULL в качестве аргумента, возвращают пустое значение (NULL).

Используемые термины и обозначения

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

Синтаксис команд SQL:

• Оператор определения «::=» разделяет определяемый элемент (слева от оператора) и собственно его определение (справа от оператора);

• Квадратные скобки «[]» указывают необязательный элемент синтаксической конструкции;

• Многоточие «…» определяет, что выражение, предшествующее ему, может повторяться любое число раз;

• Фигурные скобки «{}» объединяют последовательность элементов в логическую группу, один из элементов которой должен быть обязательно использован;

• Вертикальная черта «/» указывает, что часть определения, следующая за этим символом, является одним из возможных вариантов;

• В угловые скобки «<>» заключаются элементы, объясняемые по мере того, как они вводятся.

3. Выборка данных SELECT

Оператор SELECT предназначен для выборки информации из таблиц базы данных.

SELECT [DISTINCT] <список атрибутов> - запрос на извлечение информации

FROM <список таблиц>- список имен таблиц, из которых извлекается информация.

[WHERE <условие выборки>]- определяет, какие записи указанных таблиц должны быть выбраны

[ORDER BY< список атрибутов >]- определяет порядок записей в выходной таблице

[GROUP BY< список атрибутов >] - определяет атрибуты, по которым производится группировка информации

[HAVING <условие>]- определяет группы, которые войдут в выходную таблицу

[UNION <выражение с оператором SELECT >]; - используется для объединения выходных данных двух или более SQL- запросов в единое множество строк и столбцов.

Ключевое слово SELECT сообщает базе данных, что данное предложение является запросом на извлечение информации. После слова SELECT через запятую перечисляются наименования полей (список абитуриентов), содержимое которых запрашивается. Обязательным ключевым словом а предложении- запросе SELECT является слово FROM(из). За ключевым словом FROM указывается список разделенных запятыми имен таблиц, из которых извлекается информация.

Пример.

SELECT NAME, SURNAME

FROM STUDENT;

Любой SQL-запрос должен заканчиваться символом «;» ( точка с запятой). Приведенный запрос осуществляет выборку всех значений полей NAME и SURNAME из таблицы STUDENT.

Порядок следования столбцов в этой таблице соответствует порядку полей NAME и SURNAME, указанному в запросе, а не их порядку во входной таблице STUDENT.

Пример.

Если необходимо вывести значения всех столбцов таблицы, то можно вместо перечисления их имен использовать символ «*» (звездочка).

SELECT *

FROM STUDENT;

Пример.

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

SELECT CITY

FROM STUDENT;

Пример.

Для исключения из SELECT- запроса повторяющихся записей используются ключевые слова DISTINCT(отличный).

SELECT DISTINCT CITY

FROM STUDENT;

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

Пример:

Написать запрос, выполняющий выборку имен всех студентов с фамилией Петров, сведения о которых находятся в таблице STUDENT

SELECT NAME, SURNAME

FROM STUDENT

WHERE SURNAME ='Петров';

Пример:

В задаваемых предложениях WHERE условиях могут использоваться операции сравнения, определяемые операторами =(равно), >(больше), <(меньше), >=(больше или равно), <=(меньше или равно), <>(не равно), а также логические операторы AND, OR и NOT. Запрос для получения имен и фамилий студентов, обучающихся на третьем курсе и получающих стипендию(размер стипендии больше нуля), будет выглядеть таким образом:

SELECT NAME, SURNAME

FROM STUDENT

WHERE KURS = 3 AND STIPEND> 0;

3. Операторы IN, BETWEEN, LIKE, IS NULL.

 Операторы IN(равен любому из списка) NOT IN(не равен ни одному из списка) используется для сравнения проверяемого значения поля с заданным списком. Этот список значений указывается в скобках справа от оператора IN. Построенный с использованием IN предикат(условие) считается истинным, если значения поля, имя которого указано слева от IN, совпадает(подразумевается точное совпадение) с одним из значений, перечисленных в списке, указанном в скобках справа от IN.

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

Пример:

Получить из таблицы EXAM_MARKS сведения о студентах, имеющих экзаменационные оценки только 4 и 5.

SELECT *

FROM EXAM_MARKS

WHERE MARK IN (4,5);

Пример:

Получить из таблицы EXAM_MARKS сведения о студентах, не имеющих ни одной экзаменационные оценки только 4 и 5.

SELECT *

FROM EXAM_MARKS

WHERE MARK NOT IN (4,5);

 Оператор BETWEEN используется для проверки условия вхождения значения поля в заданный интервал, то есть вместо списка значений атрибута этот оператор задает границы его изменения.

Пример:

Запрос на вывод записей о предметах, на изучение которых отводиться количество часов, находящееся в пределах между 30 и 40, имеет вид:

SELECT *

FROM SUBJECT

WHERE HOUR BETWEEN 30 AND 40;

30 и 40, входят во множество значений, с которыми производится сравнение. Оператор BETWEEN может использоваться как для числовых, так и для символьных типов полей.

 Оператор LIKE применим только к символьным полям типа CHAR или VARCHAR. Этот оператор просматривает строковые значения полей с целью определения, входит ли заданная в операцию LIKE подстрока(образец поиска) в символьную строку-значение проверяемого поля. Можно применять шаблон:

• Символ подчеркивания «?» указанный в шаблоне, определяет возможность наличия в указанном месте одного любого символа;

• Символ «*» допускает присутствие в указанном месте проверяемой строки последовательности любых символов произвольной длины.

Like '[A-K]*' - значения атрибутов символьного типа, начинающиеся с А по К

Like '[!A-K]*' - значения атрибутов символьного типа, начинающиеся на любые символы кроме символов с А по К ( с Л по Я)

Like '[A,K]*' - значения атрибутов символьного типа, начинающиеся с А или К

Пример:

Написать запрос, выбирающий из таблицы STUDENT сведения о студентах, фамилии которых начинаются на букву «Р».

SELECT *

FROM STUDENT

WHERE SURNAME LIKE "Р*";

 Оператор NULL используется для проверки содержимого поля на наличие в нем пустого значения. IS NULL( является пустым), IS NOT NULL(является не пустым).

Упорядочение выходных полей (ORDER BY)

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

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

Примеры:

1. Выбрать все данные из таблицы предметов обучения SUBJECT с упорядочением по наименованиям предметов.

SELECT *

FROM subject

ORDER BY subj_name;

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

SELECT *

FROM subject

ORDER BY subj_name DESC;

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

SELECT *

FROM subject

ORDER BY semester,subj_name;

 При упорядочении вместо наименований столбцов можно указывать их номера, имея, однако, в виду, что данном случае это номера столбцов указанные при определении выходных данных в запросе, а не номера столбцов в таблице. Полем с номером 1 является первое поле, указанное в предложении ORDER BY- независимо от его расположения в таблице.

Пример:

SELECT SUBJ_ID, SEMESTER

FROM SUBJECT

ORDER BY 2 DESC;

В этом запросе выводимые записи будут упорядочены по полю SEMESTER.

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

Практическая работа «Организация простейших SQL-запросов».

Из таблицы STUDENT «Учебной базы данных»:

1. Вывести все данные студента с номером 265;

2. Вывести информацию о студентах с именем Вадим;

3. Выбрать фамилии студентов со 2-го и 3-го курсов получающих стипендию;

4. Вывести имена, фамилии и даты рождения студентов 5 курса;

5. Вывести информацию о студентах из Воронежа;

6. Выбрать фамилию, имя, курс, город студентов получающих стипендию от 100 до 200 рублей;

7. Вывести список идентификаторов университетов, исключая повторения.

Из таблицы EMP:

EMPNO

ENAME

JOB

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

17-DEC-80

800

20

7499

ALLEN

SALESMAN

20-FEB-81

1600

300

30

7521

WARD

SALESMAN

22- FEB-81

1250

500

30

7566

JONES

MANAGER

02-APR-81

2975

20

7654

MARTIN

SALESMAN

28-SEP-81

1250

1400

30

7698

BLAKE

MANAGER

01-MAY-81

2850

30

7782

CLARK

MANAGER

09-JUN-81

2450

10

7788

SCOTT

ANALYST

19-APR-87

3000

20

7839

KING

PRESIDENT

17-NOV-81

5000

10

7844

TURNER

SALESMAN

08-SEP-81

1500

0

30

7876

ADAMS

CLERK

23-MAY-87

1100

20

7900

JAMES

CLERK

03-DEC-81

950

30

7902

FORD

ANALYST

03-DEC-81

3000

20

7934

MILLER

CLERK

23-JAN-82

1300

10

EMPNO - номер руководителя, ENAME- имена служащих,

JOB- должность, HIREDATE- дата зачисления на работу,

SAL- месячная зарплата, COMM- премия,

DEPTNO- номер отдела.

1. Найти всех служащих с зарплатой в диапазоне от $1000 до $2000;

2. Выбрать все категории должностей

JOB

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

3. Вывести всех служащих, зачисленных на работу в 1981 году;

4. Вывести данные о сотрудниках 10 и 20 отделов в алфавитном порядке по их именам;

5. Вывести значения полей ENAME и JOB для всех клерков в 20 отделе;

6. Найти всех служащих, имена которых содержат комбинации символов TH или LL;

7. Вывести информацию о служащих имеющих премию;

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

5. Числовые, символьные и строковые константы

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

Если в запросе вместо спецификации столбца SQL обнаруживает число, то оно интерпретируется как числовая константа.

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

Пример:

SELECT 'Фамилия', SURNAME, 'Имя', NAME, 100

FROM STUDENT;

SURNAME

NAME

Фамилия

Иванов

Имя

Иван

100

Фамилия

Петров

Имя

Перт

100

Фамилия

Сидоров

Имя

Вадим

100

Арифметические операции для преобразования числовых данных

1. Унарный(одиночный) оператор «-»(знак минус) изменяет знак числового значения, перед которым он указан, на противоположный.

2. Бинарные операторы «+», «-», «*», и «/» предоставляют возможность выполнения арифметических операций сложения, вычитания, умножения и деления.

Пример:

SELECT NAME, SURNAME, STIPEND, =( STIPEND* KURS)/2

FROM STUDENT

WHERE KURS = 4 AND STIPEND> 0;

SURNAME

NAME

STIPEND

KURS

Сидоров

Вадим

150

4

300

Петров

Антон

200

4

400

…

…

…

…

…

Операторы конкатенации строк

Операция конкатенации «&» позволяет соединять(«склеивать») значения двух или более столбцов символьного типа или символьных констант в одну строку.

Пример:

SELECT SURNAME &'_'& NAME, STIPEND

FROM STUDENT

WHERE KURS = 4 AND STIPEND> 0;

STIPEND

Сидоров_Вадим

150

Петров_Антон

200

….

….

Функции преобразования символов в строке

LCASE(строка)-перевод в строчные символы

UCASE(строка)-перевод в прописные символы

Пример:

SELECT LCASE (SURNAME), UCASE (NAME)

FROM STUDENT

WHERE KURS = 4 AND STIPEND> 0;

SURNAME

NAME

сидоров

ВАДИМ

петров

АНТОН

….

….

Строковые функции

LEN(строка) -определение длины строки

• тип возвращаемого значения INT

• функция возвращает NULL, если строка имеет NULL значение

LEFT(строка, длина) - выделение левой подстроки

RIGHT(строка, длина)-выделение правой подстроки

MID(строка, начало , количество)- выделение подстроки

LTRIM (строка)- удаляет пробелы в начале строки

RTRIM (строка)- удаляет пробелы в конце строки

STR(число) -выполняет конвертирование числового значения в символьный формат

INSTR (строка, подстрока [, <начало поиска> [, <номер вхождения>]])-

возвращает позицию найденной подстроки (тип возвращаемого значения- INT), где

<начало поиска> задает начальную позицию в строке для поиска<подстроки>. Если не задано, то по умолчанию применяется значение 1;

<номер вхождения> задает порядковый номер искомой подстроки. Если не задан, то по умолчанию применяется значение 1.

Значение выражений в <начале поиска> или <номера вхождения> должны иметь беззнаковый целый тип или приводиться к этому типу

Примеры:

SELECT LEN (SURNAME)

Запрос1

SURNAME

Выражение1

Петров

ов

Сидоров

ов

Кузнецов

ов

Зайцева

ва

Павлов

ов

Лукин

ин

Питров

овFROM STUDENT

WHERE KURS = 3 ;

Запрос1

SURNAME

Выражение1

KURS

Петров

6

3

Павлов

6

3

Лукин

5

3

SELECT SURNAME, Right(SURNAME,2)

FROM STUDENT

WHERE KURS IN (2, 3, 4);

Функции работы с числами

ABS(значимое числовое выражение)- абсолютное значение

ROUND(<значимое число выражения>, <точность>)- выполняет округление числа с указанной точностью

SIGN(<значимое числовое выражение>)- определяет знак числа

ISNUMERIC(выражение)- определяет имеет ли выражение числовой тип данных

RAND(целое число)-вычисляет случайное число с плавающей запятой в интервале от 0 до 1

POWER(число, степень)- возводит число в степень

SQRT (число)-извлекает квадратный корень из числа

Тригонометрические функции:

COS(<значимое числовое выражение>)

SIN(<значимое числовое выражение>)

TAN(<значимое числовое выражение>)

EXP(<значимое числовое выражение>)- экспоненциальная функция

LOG(<значимое числовое выражение>)

FIX(<значимое числовое выражение>)

INT(<значимое числовое выражение>)

Пример:

SELECT UNIV_NAME, RATING, ROUND(RATING, -1)

FROM UNIVERSITY;

UNIV_NAME

RATING

МГУ

606

610

ВГУ

296

300

НГУ

345

350

РГУ

416

420

БГУ

326

330

ТГУ

368

370

ВГМА

327

330

…

…

…

Функции работы с датами.

GETDATE()- возвращает текущую системную дату

ISDATE(строка)-проверяет строку на соответствие одному из форматов даты и времени

DAY(дата)-возвращает число указанной даты

MONTH(дата)-возвращает месяц указанной даты

YEAR(дата)-возвращает год указанной даты

DATEADD(тип,число,дата)-прибавляет к дате указанное число единиц заданного типа(год, месяц, день,час и т.д.)

Практическая работа «Выполнение запросов с параметрами».

1. Составить запрос для таблицы STUDENT «Учебной базы данных» таким образом, чтобы выходная таблица содержала один столбец, содержащий последовательность разделённых символом «;» значений всех столбцов этой таблицы, и при этом текстовые значения должны отображаться прописными символами (например,10;КУЗНЕЦОВ;БОРИС;0;БРЯНСК;8/12/1981;10).

2. Составить запрос для таблицы STUDENT «Учебной базы данных» таким образом, чтобы выходная таблица содержала один столбец в следующем виде: Б.КУЗНЕЦОВ; место жительства - БРЯНСК; родился - 8.12.81.

3. Составить запрос для таблицы STUDENT «Учебной базы данных» таким образом, чтобы выходная таблица содержала один столбец в следующем виде: б.кузнецов; место жительства - брянск; родился 8-ДЕК-1981.

4. Составить запрос для таблицы STUDENT «Учебной базы данных» таким образом, чтобы выходная таблица содержала один столбец в следующем виде: Борис Кузнецов родился в 1981 году.

5. Вывести фамилии, имена студентов и величину получаемой ими стипендии, при этом значения стипендий увеличить в 3,35 раз и округлить до целых..

6. Составить запрос для таблицы STUDENT «Учебной базы данных» таким образом, чтобы выходная таблица содержала один столбец в следующем виде: БОРИС КУЗНЕЦОВ родился в 1981 году, только для студентов 1,2 и 4-го курсов.

7. Составить запрос для таблицы UNIVERSITY «Учебной базы данных» таким образом, чтобы выходная таблица содержала один столбец в следующем виде: Код - 10;ВГУ - г.ВОРОНЕЖ;Рейтинг=296.

8. Из таблицы EMP выбрать имена всех служащих в алфавитном порядке и их зарплату, увеличенную на 15% и округлённую до целых.

ИМЯ

PRT_SAL

SMITH

920

…

…

9. Сформируйте следующую таблицу результатов для таблицы EMP:

Имя_Должность

SMITH……. CLERK

ALLEN……. SALESMAN

…

10. Сформируйте следующую таблицу результатов для таблицы EMP:

EMOLOYEE

SMITH (CLERK)

ALLEN (SALESMAN)

…

11. Из таблицы EMP вывести по каждому служащему его имя, дату зачисления на работу и дату годовой аттестации (Review Date). Дата аттестации наступает ровно через год после зачисления служащего на работу.

6. Итоговые (агрегирующие) функции.

Агрегация или агрегирование (лат. aggregatio - присоединение) - процесс объединения элементов в одну систему.

Агрегирующие функции позволяют получить из таблицы сводную (агрегированную) информацию, выполняя операции над группой строк таблицы. В качестве аргументов этих функций используются имена полей. К агрегирующим относятся следующие функции: COUNT, SUM, AVG, MAX, MIN.

Функция COUNT.

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

Синтаксис:

COUNT (имя поля)

Пример.

Необходимо подсчитать общее количество преподавателей из Москвы.

SELECT COUNT(surname)

FROM lecturer

WHERE city='Москва';

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

SELECT COUNT(*)

FROM lecturer

WHERE city='Москва';

Если аргументом функции является столбец, содержащий пустое значение NULL, то COUNT вернет число строк, которые не содержат пустые значения и к которым применимо определенное в COUNT условие или группирование.

Поведение функции COUNT(*) не зависит от пустых значений. Она возвращает общее количество строк в таблице.

Пример.

1. Подсчитать общее количество студентов в таблице «STUDENT».

SELECT COUNT(*) Результат 10

FROM student;

2. Подсчитать количество студентов, для которых указаны даты рождения.

SELECT COUNT(birthday) Результат 9

FROM student;

Функция AVG.

Предназначена для подсчета среднего значения поля на множестве записей таблицы.

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

Синтаксис:

AVG(имя поля)

Пример.

Для определения среднего значения поля MARK(оценки) по всем записям таблицы EXAM_MARKS можно использовать запрос с функцией AVG следующего вида:

SELECT AVG (mark)

FROM exam_marks;

Функция AVG подсчитывает сумму известных значений и делит ее на количество этих значений, а не на общее количество значений, среди которых могут быть NULL- значения. Если столбец состоит только из пустых значений, то функция AVG также возвратит NULL.

Функция SUM.

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

Синтаксис:

SUM (имя поля)

Пример.

Вычислить общую сумму стипендии для студентов всех курсов из таблицы «STUDENT».

SELECT SUM(stipend)

FROM student;

Функция MAX.

Вычисляет наибольшее из всех выбранных значений данного поля. При использовании функции MAX нулевые значения игнорируются.

Синтаксис:

MAX (имя поля)

Пример.

Вывести максимальное значение стипендии студентов из таблицы «STUDENT».

SELECT MAX (stipend)

FROM student;

Функция MIN.

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

Синтаксис:

MIN (имя поля)

Пример.

Вывести университет с минимальным рейтингом.

SELECT MIN (rating)

FROM university;

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

Например, найти min количество часов, отведенное для каждой дисциплины или найти мах значение стипендии, получаемое студентами на 3 курсе или количество преподавателей, работающих в ТГУ и т.д.

Для формирования групп в запросе SELECT используется предложение GROUP BY( группировать по)

В предложении GROUP BY должны быть указаны все выбираемые столбцы, приведенные после ключевого слова SELECT, кроме столбцов, указанных в качестве аргумента в агрегирующей ФУНКЦИИ.

При необходимости часть, сформированных с помощью GROUP BY групп может быть исключена с помощью предложения HAVING

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

Итак, если в операторе SELECT задается одновременно предложение WHERE и HAVING,то порядок выполнения следующий:

1. Отбираются все записи, удовлетворяющие условию WHERE

2. Из отобранных записей формируются группы

3. Для каждой группы вычисляются значения групповых функций

4. Отбираются группы, удовлетворяющие условию HAVING

Примеры.

1. Вывести предметы, на которые отводится более 72 часов.

SELECT subj_name, MAX(hour)

FROM subject

GROUP BY subj_name

HAVING MAX (hour) >=72;

2. Найти максимальное значение оценки, полученной каждым студентом.

SELECT student_id, MAX(mark)

FROM exam_marks

GROUP BY student_id;

В предложении GROUP BY для группирования может быть использовано более одного столбца.

3. Найти максимальное значение оценки, полученной каждым студентом по каждому предмету.

SELECT STUDENT_ID ,SUBJ_ID, MAX(MARK)

FROM EXAM_MARKS

GROUP BY STUDENT_ID ,SUBJ_ID;

В данном случае строки вначале группируются по значениям первого поля, указанного в SELECT , а внутри этих групп- в подгруппы по значениям второго поля.

Предложение ORDER BY может использоваться с GROUP BY для упорядочения групп записей. При этом оператор ORDER BY в запросе всегда должен стоять последним.

SELECT SUBJ_NAME, SEMESTER, MAX(HOUR)

FROM SUBJECT

GROUP BY SEMESTER, SUBJ_NAME

ORDER BY SEMESTER;

Практическая работа «Использование функций».

По таблице EMP.

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

2. Составить запрос для получения максимальной зарплаты по каждой должности.

3. Составить запрос для подсчёта количества менеджеров, работающих в компании.

4. Составить запрос, вычисляющий разницу между наибольшим и наименьшим окладами в компании.

5. Составить запрос, позволяющий найти отделы, в которых работает более трёх служащих.

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

По «Учебной базе данных».

7. Составить запрос для подсчёта количества студентов, сдававших экзамен по предмету обучения с идентификатором, равным 22 по таблице EXAM MARKS.

8. Составить запрос, который выполняет выборку для каждого студента значения его идентификатора и минимальной из полученных им оценок по таблице EXAM MARKS, используя предложение GROUP BY.

9. Составить запрос, выполняющий вывод фамилии первого в алфавитном порядке (по фамилии) студента, фамилия которого начинается на букву «П» по таблице STUDENT.

10. Составить запрос, который выполняет вывод (для каждого предмета обучения) наименования предмета и максимального значения номера семестра, в котором этот предмет преподаётся по таблице SUBJECT.

11. Составить запрос для определения количества студентов, сдававших каждый экзамен.

12. Составить запрос для определения количества студентов, проживающих в Воронеже.

13. Составить запрос, выполняющий вывод номера студента, фамилию студента и стипендию, увеличенную на 20%. Выходные данные упорядочить по значению последнего столбца(величине стипендии).

14. Составить запрос, выполняющий вывод списка предметов обучения в порядке убывания семестров. Поле семестра в выходных данных должно быть первым, за ним должны следовать имя предмета обучения и идентификатор предмета.

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

    7. Вложенные подзапросы

SQL позволяет использовать одни запросы внутри других запросов, то есть вкладывать запросы друг в друга. Предположим, известна фамилия студента («Петров»), но неизвестно значение поля STUDENT_ID для него. Чтобы извлечь данные обо всех оценках этого студента можно записать следующий запрос:

SELECT *

FROM EXAM_MARKS

WHERE STUDENT_ID =

(SELECT STUDENT_ID

FROM STUDENT WHERE SURNAME= "Петров");

Как работает запрос SQL со связанным подзапросом?

• Выбирается строка из таблицы, имя которой указано во внешнем запросе.

• Выполняется подзапрос и полученное значение применяется для анализа этой строки в условии предложения WHERE внешнего запроса.

• По результату оценки этого условия принимается решение о включении или не включении строки в состав выходных данных.

• Процедура повторяется для следующей строки таблицы внешнего запроса.

Следует обратить внимание, что приведенный выше запрос корректен только в том случае, если в результате выполнения указанного в скобках подзапроса возвращается единственное значение. Если в результате выполнения подзапроса будет возвращено несколько значений, то этот подзапрос будет ошибочным. Если в таблице STUDENT будет несколько записей со значениями поля SURNAME= "Петров".

В некоторых случаях для гарантии получения единственного значения в результате выполнения подзапроса используется DISTINCT. Оператор IN так же широко применяется в подзапросах.

Пример:

Данные обо всех оценках студентов из Воронежа можно выбрать с помощью следующего запроса:

SELECT *

FROM EXAM_MARKS

WHERE STUDENT_ID IN

(SELECT STUDENT_ID

FROM STUDENT

WHERE CITY = "Воронеж");

Формирование связанных подзапросов

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

Пример:

Выбрать сведения обо всех предметах обучения, по которым проводился экзамен 12 января 2008 г.

SELECT *

FROM SUBJECT AS SU

WHERE 12/01/2008

( SELECT EXAM_DATE

FROM EXAM_MARKS AS EX

WHERE SU.SUBJ_ID = EX.SUBJ_ID );

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

Эту же задачу можно решить с помощью операции соединения таблиц.

SELECT DISTINCT SU. SUBJ_ID, SUBJ_ NAME, HOUR, SEMESTER

FROM SUBJECT FIRST, EXAM_MARKS SECOND

WHERE FIRST. SUBJ_ID = SECOND. SUBJ_ID

AND SECOND. EXAM_DATE = 12/01/2008;

В этом выражении алиасами таблиц являются имена FIRST и SECOND.

Связанные подзапросы в HAVING

Предикат предложения HAVING оценивается не для каждой строки результата, а для группы выходных записей, сформированной предложением GROUP BY внешнего запроса.

Пример:

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

SELECT EXAM_DATE, SUM(MARK)

FROM EXAM_MARKS AS A

GROUP BY EXAM_DATE

HAVING 10<

(SELECT COUNT (MARK)

FROM EXAM_MARKS AS B

WHERE A. EXAM_DATE = B. EXAM_DATE);

Подзапрос вычисляет количество строк с одной и той же датой, совпадающей с датой, для которой сформирована очередная группа основного запроса.

Использование оператора EXISTS

Оператор EXISTS(существует) генерирует значение истина или ложь.

Оператор EXISTS не может принимать значение UNION(неизвестно).

Пример:

Извлечь из таблицы EXAM_MARKS данные о студентах, получивших хотя бы одну неуд. оценку.

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS AS A

WHERE EXISTS

(SELECT *

FROM EXAM_MARKS AS B

WHERE MARK < 3

AND B. STUDENT_ID =А. STUDENT_ID);

При использовании связанных подзапросов предложение EXISTS анализирует каждую строку таблицы, на которой имеется ссылка во внешнем запросе. Главный запрос получает строки- кандидаты на проверку условия. Для каждой строки- кандидаты выполняется подзапрос. Как только подзапрос находит строку, где в столбце MARK значение удовлетворяет условию, он прекращает выполнение и возвращает значение истина внешнему запросу, который затем анализирует свою строку- кандидата.

В подзапросе, указываемом в операторе EXISTS, нельзя использовать агрегирующие функции.

Пример:

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

SELECT *

FROM STUDENT AS FIRST

WHERE EXISTS

(SELECT SUBJ_ID

FROM EXAM_MARKS AS SECOND

GROUP BY SUBJ_ID

HAVING COUNT(SUBJ_ID) > 1

WHERE FIRST. STUDENT_ID = SECOND. STUDENT_ID);

Оператор ALL, как правило, эффективно используется с неравенствами. В SQL выражение <> ALL реально означает не равно ни одному из результатов подзапроса.

Пример:

Подзапрос, выбирающий данные о названиях всех университетов с рейтингом более высоким, чем рейтинг любого университета Воронежа:

SELECT *

FROM UNIVERSITY

WHERE RETING > ALL

(SELECT RETING

FROM UNIVERSITY

WHERE CITY "Воронеж" );

В этом запросе вместо ALL можно использовать ANY(проанализировать, как в этом случае измениться смысл приведенного запроса):

SELECT *

FROM UNIVERSITY

WHERE RETING > ANY

(SELECT RETING

FROM UNIVERSITY

WHERE CITY "Воронеж" );

Особенности применения операторов ANY,ALL,EXISTS при обработке пустых значений (NULL)

Когда правильный подзапрос не генерирует никаких выходных данных, оператор ALL автоматически принимает значение истина, а оператору ANY -значение ложь.

Пример:

SELECT *

FROM UNIVERSITY

WHERE RETING > ANY

(SELECT RETING

FROM UNIVERSITY

WHERE CITY "New York" );

Не генерирует выходные данные(подразумевается, что в базе нет данных об университетах города New York), в то время как запрос

SELECT *

FROM UNIVERSITY

WHERE RETING > ALL

(SELECT RETING

FROM UNIVERSITY

WHERE CITY "New York" );

Полностью воспроизводит таблицу UNIVERSITY.

Когда в SQL сравниваются два значения, одно из которых NULL- значение, результат принимает значение UNION(неизвестно). Предикат UNION, так же как и FALSE- предикат, создает ситуацию, когда строка не включается в состав выходных данных, но результат при этом будет различен для разных типов запроса, в зависимости от использования в них ALL или ANY вместо EXISTS.

Пример:

Найти все данные об университетах, рейтинг которых меньше рейтинга любого университета в Москве.

SELECT *

FROM UNIVERSITY

WHERE RETING < ANY

(SELECT RETING

FROM UNIVERSITY

WHERE CITY "Москва" );

SELECT *

FROM UNIVERSITY AS А

WHERE NOT EXISTS.

(SELECT *

FROM UNIVERSITY AS В

WHERE А. RETING >= В. RETING

AND CITY "Москва");

Практическая работа «Использование вложенных подзапросов».

1. Написать запрос с подзапросом для получения данных обо всех оценках студента с фамилией «Зайцева». Предположим, что его персональный номер неизвестен.

2. Написать запрос, выбирающий данные об именах всех студентов, имеющих по предмету с идентификатором 10 балл выше общего среднего балла.

3. Написать запрос, который выполняет выборку имён всех студентов, имеющих по предмету с идентификатором 10 балл ниже общего среднего балла.

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

5. Написать команду SELECT, использующую связанные подзапросы и выполняющую вывод имён и идентификаторов студентов, у которых стипендия совпадает с максимальным значением стипендии для города, в котором живёт студент.

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

7. Написать два запроса, которые позволяют вывести имена и идентификаторы всех студентов, для которых точно известно, что они проживают не в том городе, где расположен их университет:

• с использованием соединения;

• с использованием связанного подзапроса.

  8. Написать запрос EXISTS, позволяющий вывести данные обо всех студентах, обучающихся в вузах, которые имеют рейтинг выше 300.

  9. Написать предыдущий запрос, используя соединения.

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

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

8. Оператор объединения UNION

Оператор UNION используется для объединения выходных данных двух или более SQL- запросов в единое множество строк и столбцов.

Пример:

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

SELECT "Студент"SURNAME, STUDENT_ID

FROM STUDENT

WHERE CITY "Москва"

UNION

SELECT "Преподаватель"SURNAME, LECTURER _ID

FROM LECTURER

WHERE CITY "Москва";

Использование оператора INION возможно только при объединении запросов, соответствующие столбцы которых совместимы по объединению, то есть соответствующие числовые поля должны иметь полностью совпадающие тип и размер, символьные поля должны иметь точно совпадающее количество символов. Если NULL- значения запрещены для столбца хотя бы одного любого подзапроса объединения, то они должны быть запрещены и для всех соответствующих столбцов в других подзапросах объединения.

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

Пример:

Пусть в таблице STUDENT имеются записи о студентах, в которых не указан идентификатор университета. Требуется составить список студентов с указанием наименования университета для тех студентов, у которых эти данные есть, но при этом не исключая и студентов, у которых университет не указан. Можно получить желаемые сведения, сформировав объединения двух запросов, один из которых выполняет выборку студентов с названиями их университетов, а второй выбирает студентов с NULL- значениями в поле UNIV_ID. Константа «Неизвестен», чтобы отметить в списке тех студентов, у которых отсутствует информация об университете.

SELECT SURNAME, NAME ,UNIV_NAME

FROM STUDENT, UNIVERSITY

WHERE STUDENT. UNIV_ID = UNIVERSITY. UNIV_ID

UNION

SELECT SURNAME, NAME,«Неизвестен»

FROM STUDENT

WHERE UNIV_ID IS NULL

ORDER BY 1;

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

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

2. Написать запрос, использующий ANY или ALL, выполняющий выборку данных о студентах, у которых в городе их постоянного местожительства нет университета.

3. Написать запрос, выбирающий из таблицы EXAM MARKS данные о названиях предметов обучения, для которых значение полученных на экзамене оценок (поле MARK) превышает любое значение оценки для предмета, имеющего идентификатор, равный 105.

4. Написать этот же запрос с использованием MAX.

5. Создать объединение двух запросов, которые выдают значения полей UNIV_NAME, CITY, RATING для всех университетов. Те из них у которых рейтинг равен или выше 300, должны иметь комментарий «Высокий», все остальные - «Низкий».

6. Написать команду, которая выдаёт список фамилий студентов, с комментарием «успевает» у студентов, имеющих все положительные оценки, комментарием «не успевает» для сдававших экзамены, но имеющих хотя бы одну неудовлетворительную оценку и комментарием «не сдавал» - для всех остальных. В выводимом результате фамилии студентов упорядочить по алфавиту.

7. Вывести объединённый список студентов и преподавателей, живущих в Москве, с соответствующими комментариями: «студент» или «преподаватель».

8. Вывести объединённый список студентов и преподавателей Воронежского государственного университета с соответствующими комментариями: «студент» или «преподаватель».

   9. Соединения таблиц с использованием оператора JOIN

Если в операторе SELECT после ключевого слова FROM указывается не одна, а две таблицы, то в результате выполнения запроса, в котором отсутствует предложение WHERE, каждая строка одной таблицы будет соединена с каждой строкой второй таблицы. Такая операция называется декартовым произведением, или полным соединением таблиц. Соединение таблиц имеет смысл в случае, если соединяются не все строки исходных таблиц, а только интересующие пользователя. Такое ограничение может быть осуществлено путем применения в запросе соответствующего условия в предложении WHERE. Таким образом, SQL позволяет выводить информацию из нескольких таблиц, связывая их по значениям определенных полей.

Пример:

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

SELECT STUDENT.SURNAME, UNIVERSITY.UNIV_NAME, STUDENT. CITY

FROM STUDENT, UNIVERSITY

WHERE STUDENT. CITY = UNIVERSITY. CITY;

Соединение, использующие предикаты, основанные на равенствах, называется эквисоединением. Рассмотренный пример соединения таблиц относятся к виду так называемого внутреннего соединения. При этом соединяются только те строки таблиц, для которых истинным является предикат, задаваемый в предложении ON выполняемого запроса.

Операции соединения таблиц посредством ссылочной целостности

Информация в таблицах STUDENT и EXAM_MARKS уже связана посредством поля STUDENT_ID являются первичным ключом. Состояние связанных таким образом таблиц называется состоянием ссылочной целостности. В данном случае ссылочная целостность этих таблиц подразумевает, что каждому значению поля STUDENT_ID в таблице EXAM_MARKS обязательно соответствует такое же значения поля STUDENT_ID в таблице STUDENT. В таблице EXAM_MARKS не может быть записей, имеющих идентификатор студентов, которых нет в таблице STUDENT.

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

SELECT SURNAME, MARK, SUBJ_ID

FROM STUDENT, EXAM_MARKS

WHERE STUDENT. STUDENT_ID = EXAM_MARKS. STUDENT_ID;

Тот же результат может быть получен при использовании в запросе JOIN.

SELECT SURNAME, MARK

FROM STUDENT JOIN EXAM_MARKS

ON STUDENT. STUDENT_ID = EXAM_MARKS. STUDENT_ID;

Внешнее соединение таблиц

При использовании внутреннего соединения таблиц соединяются только те их строки, в которых совпадают значения полей, задаваемые в запросе предложением WHERE. Однако во многих случаях это может привести к нежелательной потере информации. Рассмотрим еще раз приведенный выше пример запроса на выборку списка фамилий студентов с полученными ими оценками и идентификаторами предметов. При использовании, внутреннего соединения в результат запроса не попадут студенты, которые еще не сдавали экзамены, и которые, следовательно, отсутствуют в таблице EXAM_MARKS. Если же необходимо иметь записи об этих студентах в выдаваемом запросом списке, то можно присоединить сведения о студентах, не сдававших экзамен, путем использования оператора UNION с соответствующим запросом.

Пример:

SELECT SURNAME, CAST MARK AS CHAR(1), CAST SUB_ID AS CHAR(10)

FROM STUDENT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID

UNION

SELECT SURNAME, CAST NULL AS CHAR(1), CAST NULL AS CHAR(10)

FROM STUDENT

WHERE NOT EXIST

(SELECT *

FROM EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID);

Функция преобразования типов CAST используется для обеспечения совместимости типов полей объединяемых запросов левого внешнего соединения.

SELECT SURNAME, MARK

FROM STUDENT LEFT OUTER JOIN EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

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

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

SELECT SURNAME, MARK

FROM STUDENT RIGHT OUTER JOIN EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

Здесь таблица STUDENT, за счет записей которой осуществляет расширение выводимой таблицы, указана справа от оператора JOIN.

Использование псевдонимов при соединении таблиц.

Пример:

Нужно найти фамилии студентов, имеющих одинаковые имена. Запрос для поиска фамилий студентов, имеющих одинаковые имена.

SELECT FIRST.SURNAME, SECOND.MARK

FROM STUDENT FIRST, STUDENT SECOND

WHERE FIRST.NAME = SECOND.NAME;

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

SELECT FIRST.SURNAME, SECOND. SURNAME

FROM STUDENT FIRST, STUDENT SECOND

WHERE FIRST. SURNAME < SECOND. SURNAME;

Практическая работа «Внешнее соединение таблиц».

1. Написать запрос, который выполняет вывод данных о фамилиях сдававших экзамены студентов ( вместе с идентификаторами каждого сданного ими предмета обучения).

2. Написать запрос, который выполняет выборку значений фамилий всех студентов с указанием для студентов, сдававших экзамены, идентификаторов сданных ими предметов обучения.

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

4. Написать запрос на выдачу для каждого студента названий всех предметов обучения, по которым этот студент получил оценку 4 или 5.

5. Написать запрос на выдачу данных о названиях всех предметов, по которым студенты получили только хорошие (4 и 5) оценки. В выходных данных должны быть приведены фамилии студентов, названия предметов и оценка.

6. Написать запрос, который выполняет вывод списка университетов с рейтингом, превышающим 300, вместе со значением максимального значения стипендии, получаемой студентами в этих университетах.

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

8. Написать запрос, выполняющий вывод списка всех пар фамилий студентов, проживающих в одном городе. При этом не включать в список комбинации фамилии студентов самих с собой (то есть комбинации типа «Иванов - Иванов») и комбинацию фамилий студентов, отличающиеся порядком следования (то есть включать одну из двух комбинаций типа «Иванов - Петров» и «Петров - Иванов»).

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

10. Написать запрос, который позволяет получить данные о назначениях университетов и городов, в которых они расположены, с рейтингом, равным или превышающим рейтинг ВГУ.

Приложение1 «Учебная база данных»

STUDENT (Студент)

STUDENT_ID

SURNAME

NAME

STIPEND

KURS

CITY

BIRTHDAY

UNIV_ID

1

Иванов

Иван

150

1

Орёл

3/12/1982

10

3

Петров

Пётр

200

3

Курск

1/12/1980

10

6

Сидоров

Вадим

150

4

Москва

7/06/1979

22

10

Кузнецов

Борис

0

2

Брянск

8/12/1981

10

12

Зайцева

Ольга

250

2

Липецк

1/05/1981

10

265

Павлов

Андрей

0

3

Воронеж

5/11/1979

10

32

Котов

Павел

150

5

Белгород

NULL

14

654

Лукин

Артём

200

3

Воронеж

1/12/1981

10

276

Петров

Антон

200

4

NULL

5/08/1981

22

55

Белкин

Вадим

250

5

Воронеж

7/01/1980

10

…….

……..

…….

…….

……..

……..

……..

……...

STUDENT_ID-числовой код, идентифицирующий студента,

SURNAME - фамилия студента,

NAME - имя студента,

STIPEND - стипендия, которую получает студент,

KURS - курс, на котором учится студент,

CITY - город, в котором живёт студент,

BIRTHDAY - дата рождения студента,

UNIV_ID - числовой код, идентифицирующий университет, в котором учится студент.

LECTURER (Преподаватель)

LECTURER_ID

SURNAME

NAME

CITY

UNIV_ID

244

Колесников

Борис

Воронеж

10

46

Никонов

Иван

Воронеж

10

74

Лагутин

Павел

Москва

22

108

Струков

Николай

Москва

22

276

Николаев

Виктор

Воронеж

10

328

Сорокин

Андрей

Орёл

10

…….

…….

…….

…….

…….

LECTURER _ID- числовой код, идентифицирующий преподавателя,

SURNAME - фамилия преподавателя,

NAME- имя преподавателя,

CITY-город, в котором живет преподаватель,

UNIV_ID- идентификатор университета, в котором работает преподаватель.

SUBJECT (Предмет обучения)

SUBJ_ID

SUBJ_NAME

HOUR

SEMESTER

10

Информатика

56

1

22

Физика

34

1

43

Математика

56

2

56

История

34

4

94

Английский

56

3

73

Физкультура

34

5

……

……

……

……

SUBJ_ID- идентификатор предмета обучения,

SUBJ_NAME- наименование предмета обучения,

HOUR- количество часов, отводимых на изучение предмета,

SEMESTER- семестр, в котором изучается данный предмет.

UNIVERSITY (Университеты)

UNIV_ID

UNIV_ NAME

RATING

CITY

22

МГУ

606

Москва

10

ВГУ

296

Воронеж

11

НГУ

345

Новосибирск

32

РГУ

416

Ростов

14

БГУ

326

Белгород

15

ТГУ

368

Томск

18

ВГМА

327

Воронеж

……

……

……

……

UNIV_ID- идентификатор университета,

UNIV_ NAME- название университета,

RATING- рейтинг университета,

CITY- город, в котором расположен университет.

EXAM_MARKS (Экзаменационные оценки)

EXAM_ID

STUDENT_ID

SUBJ_ID

MARK

EXAM_DATE

145

12

10

5

12/01/2000

34

32

10

4

23/01/2000

75

55

10

5

05/01/2000

238

12

22

3

17/06/1999

639

55

22

NULL

22/06/1999

43

6

22

4

18/01/2000

…..

…..

…..

…..

…..

EXAM_ID- идентификатор экзамена,

STUDENT_ID- идентификатор студента,

SUBJ_ID- идентификатор предмета обучения,

MARK- экзаменационная оценка,

EXAM_DATE- дата экзамена.

SUBJ_LECT (Учебные дисциплины преподавателей)

LECTURER_ID

SUBJ_ID

24

24

46

46

74

74

108

108

276

276

328

328

……

…..

LECTURER_ID- идентификатор преподавателя,

SUBJ_ID- идентификатор предмета обучения.

Приложение «Перечень операторов»

Операторы определения данных

Оператор

Действие

CREATE TABLE

Создает новую таблицу БД

DROP TABLE

Удаляет таблицу из БД

alt="Учебно методические материалы по теме Организация SQL запросов"ER TABLE

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

CREATE VIEW

Создает виртуальную таблицу, соответствующую некоторому SQL-запросу

alt="Учебно методические материалы по теме Организация SQL запросов"ER VIEW

Изменяет, ранее созданное представление

DROP VIEW

Удаляет ранее созданное представление

CREATE INDEX

Создает индекс для некоторой таблицы для обеспечения быстрого доступа по атрибутам, входящим в индекс

DROP INDEX

Удаляет ранее созданный индекс

Операторы манипулирования данными

Оператор

Действие

DELETE

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

INSERT

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

UPDATE

Обновляет значения одного или нескольких столб­цов в одной или нескольких строках, соответству­ющих условиям фильтрации

Оператор запросов

Оператор

Действие

SELECT

Оператор, заменяющий все операторы реляцион­ной алгебры и позволяющий сформировать резуль­тирующее отношение, соответствующее запросу

Операторы управления действиями (транзакциями)

Оператор

Действие

CCOMMIT

Завершает комплексную, взаимосвязанную обра­ботку информации, объединенную в транзакцию

ROLLBACK

Отменяет изменения, проведенные в ходе выполнения транзакции

SAVEPOINT

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

Операторы администрирования данными

Оператор

Действие

alt="Учебно методические материалы по теме Организация SQL запросов"ER DATABASE

Изменяет набор основных объектов в базе данных, ограничений, касающихся всей базы данных

alt="Учебно методические материалы по теме Организация SQL запросов"ER DBAREA

Изменяет ранее созданную область хранения

alt="Учебно методические материалы по теме Организация SQL запросов"ER PASSWORD

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

CREATE DATABASE

Создает новую базу данных

CREATE DBAREA

Создает новую область хранения базы данных

DROP DATABASE

Удаляет базу данных

DROP DBAREA

Удаляет область хранения базы данных

GRANT

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

REVOKE

Лишает права доступа к базе данных или отдельным ее элементам

Операторы управления курсором

Оператор

Действие

DECLARE

Определяет курсор для запроса. Задает имя и определяет связанный с ним запрос к БД

OPEN

Открывает курсор. Открывает объект базы данных

FETH

Устанавливает курсор на определенную запись и считывает ее

CLOSE

Закрывает курсор. Закрывает объект базы данных

PREPARE

Генерирует план выполнения запроса в соответствии с инструкцией SELECT

EXECUTE

Выполняет сгенерированный ранее запрос