Тема 14. Построение запросов к базе данных. Построение запросов sql

Создание запросов SQL

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

Тема занятия: Создание запросов SQL.

Цель занятия: Научиться использовать язык SQL для создания запросов.

План занятия:

1. Создание SQL запросов на выборку.

2. Создание SQL запросов на выборку с условием отбора.

3. Создание SQL запросов с вычислением.

4. Создание SQL запросов на группировку.

SQL (Structured Query Language) – структурированный язык запросов, предоставляющий средства создания и обработки данных в БД.

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

Создать запрос с помощью конструктора.

· В конструкторе запросов щелкнете правой кнопкой мыши в области отображения таблиц и в контекстном меню выберите «Режим SQL».

1. Создание SQL запросов на выборку.

Создадим простейший запрос на выборку товаров и заказов на эти товары с помощью SQL.

· Откройте редактор SQL запросов.

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

SELECT Заказы.[Код заказа], Товары.[Наименование товара], Заказы. Количество, Товары. Цена, Товары.[Еденица измерения], Заказы.[Дата заказа]

FROM Товары INNER JOIN Заказы ON Товары.[Код товара] = Заказы. Товар;

· Сохраните запрос под именем выборка SQL.

· Проверьте правильность работы запроса.

Разберем синтаксис данной операции:

SELECT – оператор который сообщает базе данных, что данная операция является запросам (практически все запросы начинаются с этого слова). После оператора SELECT обычно начинается перечисление тех полей, которые будут включены в запрос. Например запись SELECT Заказы.[Код заказа] будет означать что в запрос будет включено поле Код заказа из таблицы «Заказы». В случае если имя поля содержит пробелы его необходимо указывать в квадратных скобках [].

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

INNER JOIN - Объединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения. Например запись «FROM Товары INNER JOIN Заказы ON Товары.[Код товара] = Заказы. Товар» означает, что в данной процедуре запроса поле Код товара Из таблицы «Товары» является источником записи для поля Товар таблицы «Заказы»

Задание: Создайте SQL запрос позволяющий вывести сведения о клиентах купивших товар.

2. Создание SQL запросов на выборку с условием отбора.

С помощью языка SQL создадим запрос позволяющий вывести сведения о сотрудниках продавших товар количеством больше 3.

Для создания запросов с условием отбора используется оператор WHERE.

· Создайте SQL запрос на выборку содержащий такие поля: Фамилия, Имя, Отчество Сотрудника, Код заказа Который тот обслужить, Количество. Проверьте правильностьего работы.

· Теперь необходимо добавить условие отбора. Откройте редактор SQL и в конец продцедуры запроса добавьте «WHERE (((Заказы. Количество)>3))».

· Сохраните запрос под именем «Выборка с условием отбора SQL». Проверьте работу запроса.

Задание: Создайте SQL запрос отбирающий все товары цена на которые не прекращена и цена не превышает 20 денежных едениц.

3. Создание SQL запросов с вычислением.

Создадим SQL запрос позволяющий вывести Фамилию клиента, Наименование товара, сумму заказа (цена*количество).

Продцедура запроса выглядит так:

SELECT Клиенты. Фамилия, Товары.[Наименование товара], Заказы! Количество*Товары! Цена AS [Сумма заказа]

FROM Товары INNER JOIN (Клиенты INNER JOIN Заказы ON Клиенты.[Код клиента]=Заказы. Клиент) ON Товары.[Код товара]=Заказы. Товар;

4. Создание SQL запросов на группировку.

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

Для группировки записей используется оператор GROUP BY.

Продцедура запроса выглядит следующим образом:

SELECT Клиенты. Фамилия, Sum(Заказы! Количество*Товары! Цена) AS [Сумма заказов]

FROM Товары INNER JOIN (Клиенты INNER JOIN Заказы ON Клиенты.[Код клиента] = Заказы. Клиент) ON Товары.[Код товара] = Заказы. Товар

GROUP BY Клиенты. Фамилия;

Контрольные вопросы:

1. Что такое SQL?

2. Как создать SQL запрос?

3. Структурные составляющие языка SQL.

4. Перечислите основные операторы языка SQL.

5. Общие сведения об операторах SELECT, WHERE, FROM, INNER JOIN, GROUP BY.

Задание для успевающих: Попытайтесь самостоятельно создать SQL запрос позволяющий удалять все записи из таблицы «Товары» цена на которые меньше 3 денежных едениц

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

po-teme.com.ua

Формирование запросов средствами языка SQL

Оператор SQL состоит из зарезервированных слов и из слов, определяемых пользователем. Зарезервированные слова являются постоянной частью языка SQL, имеют фиксированное значение, их нельзя разбивать на части. Слова, определяемые пользователем, представляют собой имена различных объектов базы данных и записываются в соответствии с синтаксическими правилами.

Слова в операторе располагаются строго в определённой последовательности. Имена формируются из символов алфавита, заданного стандартом языка. Разрешено использовать строчные и прописные буквы латинского алфавита (AZ, a-z), цифры (0-9) и символ подчёркивания (_). Имя может иметь длину до 128 символов, должно начинаться с буквы и не может содержать пробелы.

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

Точка с запятой является стандартным разделителем, но в некоторых реализациях (в частности, в компоненте Query) разделитель в конце команды необязателен.

Набор объектов, используемых в базе данных, зависит от СУБД. К основным объектам относятся таблицы, представления, хранимые процедуры, триггеры, индексы, ключи, создаваемые пользователем функции, ограничения целостности и др.

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

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

Хранимые процедуры представляют собой группу команд SQL, объединённых в один модуль. Такая группа команд компилируется и выполняется как единое целое.

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

Оператор SELECT

Общая форма команды SELECT

Общая форма оператора SELECT приводится в стандартах. В более простых случаях достаточно воспользоваться только некоторыми возможностями оператора SELECT:

SELECT [DISTINCT] список_выбираемых_полей

FROM список_таблиц или представлений

[WHERE условие_отбора_строк]

[GROUP BY спецификация_группировки

[HAVING условие_отбора_групп]]

[UNION другое_выражение_Select]

[ORDER BY спецификация_сортировки];

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

Простейшие конструкции языка SQL позволяют:

· назначать поля, которые должны быть выбраны;

· назначать к выборке все поля;

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

· данных;

· подставлять собственные заголовки полей в результирующей таблице;

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

· использовать литералы в списке выбираемых элементов;

· ограничивать число возвращаемых строк;

· формировать сложные условия поиска;

· устранять одинаковые строки из результата.

Поля и предложение FROM

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

В предложении FROM перечисляются все объекты (один или несколько), из которых производится выборка данных. Каждая таблица или представление, которые упоминаются в запросе, должны быть перечислены в предложении FROM. В простейшем случае после слова FROM записывается имя таблицы, из которой извлекаются данные. Если требуется извлечение значений всех полей, то вместо списка полей можно указать символ *. Например, чтобы получить сведения из всех полей таблицы country, надо записать:

SELECT * FROM country

Для получения данных из определённых полей используется команда, в которой после слова SELECT перечислены только нужные поля:

SELECT Last_Name,First_Name,City,Country,Phone FROM custoly

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

SELECT First_Name,Last_Name,Phone,City,Country FROM custoly

Для уточнения объекта, которому принадлежит поле, перед именем поля указывается имя объекта. Задание составного имени имя_таблицы.имя_поля является обязательным при использовании нескольких таблиц или представлений, а также при использовании имён полей с пробелами. При задании полей, имена которых содержат пробел, надо использовать кавычки или апострофы.

Например, при выводе данных из таблицы biolife для полей Species Name и Length (cm) используются составные имена:

SELECT Category,Common_Name,biolife."Species Name", biolife."Length (cm)"

FROM biolife

SELECT Category,Common_Name,biolife.'Species Name', biolife.'Length (cm)'

FROM biolife

SELECT biolife.Category,biolife.Common_Name,

biolife.'Species Name',biolife.'Length (cm)'

FROM biolife

Литералы

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

апострофах.

SELECT LastName,"получает",Salary," в год" FROM employee

или

SELECT LastName,‘получает’,Salary,‘ в год’ FROM employee

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

Конкатенация

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

Для этого используется операция конкатенации, которая задаётся двумя вертикальными чёрточками (||).

SELECT FirstName||' '||LastName,HireDate FROM employee

Этот запрос выводит список сотрудников с указанием даты поступления на работу.

Использование квалификатора AS

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

SELECT VenueNo,Event_Name AS Name, Event_Description AS Description

FROM events

SELECT VenueNo,Event_Name AS Name,

Event_Date AS events.'Date',Event_Time AS events.'Time'

FROM events

В последнем запросе вместо имён полей Event_Name, Event_Date, Event_Time для столбцов используются названия Name, Date, Time. Поскольку идентификаторы Date и Time в таблицах формата Paradox используются для задания типов данных, то при формировании названий столбцов пришлось использовать составные имена.

Предложение WHERE

Ограничения на число выводимых строк

Число возвращаемых в результате запроса строк может быть ограничено путем использования предложения WHERE, содержащего условия отбора. Так как в языке SQL применяется трёхзначная логика, то условие отбора для отдельных строк может принимать значения true, false или unknown.

Значение unknown получается при сравнении значения null c любым другим значением, включая null. Запрос возвращает в качестве результата только те строки, для которых предикат имеет значение true. При формировании условия используются следующие операции: сравнения (=, <>, >, <, >=,<=), BETWEEN, IN, LIKE, IS NULL, EXIST, ANY, ALL, SOME.

Операции сравнения

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

Пример 2. Получить список сотрудников с именем Brown (Lee):

SELECT LastName, FirstName, Salary FROM employee

WHERE LastName='Brown'

SELECT LastName, FirstName, Salary FROM employee

WHERE LastName='Lee '

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

Пример 3. Получить список сотрудников с зарплатой меньше 27 000:

SELECT LastName, FirstName, Salary FROM employee

WHERE Salary<27000

Пример 4. Получить список фирм-заказчиков с указанием города и страны за исключением заказчиков из Канады:

SELECT Company,City,Country FROM customer

WHERE Country <>'Canada'

Пример 5. Получить список заказчиков из US с указанием названия фирмы, города и штата:

SELECT Company,City,State FROM customer

WHERE Country ='US'

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

Операция BETWEEN

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

Пример 6. Получить список сотрудников, у которых зарплата лежит в диапазоне от 25 000

до 30 000:

SELECT LastName,FirstName,Salary FROM employee

WHERE Salary BETWEEN 25000 and 30000

Тот же запрос с использованием операторов сравнения будет выглядеть следующим образом:

SELECT LastName,FirstName,Salary FROM employee

WHERE Salary>=25000 and Salary<=30000

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

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

SELECT LastName,FirstName,Salary FROM employee

WHERE LastName BETWEEN "Nelson" AND "Osborne"

Рисунок 3 - Результат реализации операции BETWEEN в примере 7

Пример 8. Вывести список сотрудников, фамилии которых находятся между Nel и Osb:

SELECT LastName,FirstName,Salary FROM employee

WHERE LastName BETWEEN "Nel" AND "Osb"

В таблице базы данных employee значений Nel и Osb нет.

Рисунок 4 - Результат реализации операции BETWEEN в примере 8

Однако сотрудники с фамилиями, начинающимися с символов, для которых выполняется условие «больше или равно Nel» и «не более Osb», попадут в выборку. Фамилии, начинающиеся с символов O, Os, Osb, попадают в заданный диапазон, а Osborne – нет.

Предикат BETWEEN с отрицанием NOT (NOT BETWEEN) позволяет получить записи, у которых значение заданного поля лежит вне введённого диапазона (меньше нижней границы и больше верхней границы).

Пример 9. Найти рыб с длиной меньше 10 и больше 80 дюймов (маленьких и очень больших):

SELECT Category,Common_Name,Length_In FROM biolife

WHERE Length_In NOT BETWEEN 10 AND 80

Операция IN

Предикат IN проверяет, совпадает ли заданное значение (например, значение столбца или функция от него) с одним из перечисленных в списке. Элементы списка записываются через запятую в круглых скобках. Если проверяемое значение равно какому-либо элементу в списке, то предикат принимает значение true. Разрешено использовать конструкцию NOT IN.

Пример 10. Вывести список компаний из городов Santa Maria, San Jose, Downey:

SELECT Company,City FROM customer

WHERE City in ('Santa Maria','San Jose','Downey')

Пример 11. Вывести заказы и даты их оплаты, у которых средством оплаты была не Visa и не AmEx:

SELECT OrderNo,SaleDate,PaymentMethod FROM orders

WHERE PaymentMethod not in ('Visa','AmEx')

Предикат LIKE

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

В подстроке можно применять любые разрешённые символы (с учетом верхнего и нижнего регистров), а также специальные символы:

% – замещает любое количество символов (в том числе и 0),

_ – замещает только один символ.

Пример 12. Получить список сотрудников, фамилии которых начинаются с буквы F:

SELECT FirstName,LastName FROM employee

WHERE LastName LIKE "F%"

Пример 13. Получить список сотрудников, у которых имя заканчивается на «er»:

SELECT FirstName,LastName FROM employee

WHERE FirstName LIKE "%er"

Предикат IS NULL

В SQL-запросах NULL означает, что значение столбца неизвестно. Условия поиска, в которых значение столбца сравнивается с NULL, всегда принимают значение unknown и, соответственно, приводят к ошибке. Таким образом, в запросах нет смысла применять выражения вида

WHERE имя_поля=NULL

Если требуется определить, имеет ли поле значение, используется предикат IS NULL. Условие, содержащее IS NULL, принимает значение true только тогда, когда значение поля или выражения имеет значение NULL (пусто, не определено). Разрешено также использовать конструкцию IS NOT NULL, которая означает не пусто, имеет какое-либо значение. Предикат IS NULL возвращает только значения true или false.

Логические операции

Логические операции AND, OR, NOT позволяют сформировать сложные условия отбора записей. Если в одном выражении используется несколько логических операций, то они выполняются с учётом приоритета: сначала выполняется отрицание NOT, затем AND (логическое И), только после этого OR (логическое ИЛИ). Для изменения порядка выполнения операций разрешается использовать скобки.

Пример 14. По таблице country (рисунок 5) было выполнено два запроса. Проанализировать результаты.

Рисунок 5 - Исходная таблица

Запрос 1.

SELECT * FROM country

WHERE Area<100000 or Population<3000000 and Continent='South America'

Рисунок 6 - Результат запроса 1

Запрос 2.

SELECT * FROM country

WHERE (Area<100000 or Population<3000000) and Continent='South America'

Рисунок 7 - Результат запроса 2

Запросы отличаются порядком действий при вычислении значения условия. Результирующие наборы разные.

Работа с датами

В разных СУБД отличаются встроенные функции для работы с датами и используются разные форматы для представления даты, даты и времени. Причём отличаются как внутреннее, так и внешнее представления. Внешне дата может быть представлена строками различных форматов, например:

”October 27,2008”

”27-OCT-2008”

”10-27-08”

”10/27/08”

”27.10.08”

Пример 15. Вывести список сотрудников, принятых на работу после заданной даты. Использовать разные варианты форматов даты:

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'1.01.94'

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'1/01/90'

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'27-Oct-1992'

Таблицы формата Paradox поддерживают только часть из перечисленных выше форматов. В запросах приведены варианты разрешённых форматов даты.

Пример 16. Выполнить сравнение результатов двух вариантов запроса, отличающихся только форматом записи даты.

Рисунок 8 - Дата задана строкой '12/30/93'

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

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'12/30/93'

Дата 12/30/93 означает: 12 – месяц, 30 –день, 93 – год.

Рисунок 9 - Дата задана строкой '31.12.1992'

Привычная форма даты 31.12.1992 означает: 31–день, 12–месяц, 1992–год. Значения дат можно сравнивать друг с другом, вычитать одну из другой.

Пример 17. Получить список служащих, проработавших на предприятии к 1/01/2000 более 8 лет:

SELECT FirstName,LastName,HireDate FROM employee

WHERE '1/01/2000'-HireDate > 8*365+2

Пример 18. Получить список сотрудников, поступивших на работу до 1.01.89 и после 31.12.93, то есть раньше 1989 г. и позже 1993 г.:

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate NOT BETWEEN "1-JAN-1989" AND "31-DEC-1993"

Кроме абсолютных дат некоторые реализации языка SQL (например, InterBase) позволяют оперировать относительными значениями: yesterday (вчера), today (сегодня), now (сейчас, включая время), tomorrow (завтра).

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

infopedia.su

Формирование запроса в SQL

SQL символизирует структурированный язык запросов (Structured Query Language). Запросы являются наиболее часто используемым аспектом SQL. Есть категория пользователей SQL, которые используют язык только для формулировки запросов. Поэтому изучение SQL начинается с обсуждения запроса и того, как он выполняется в этом языке. Что такое запрос? Это команда, которая формулируется для СУБД и требует предоставить определенную указанную информацию. Эта информация обычно выводится непосредственно на экран дисплея компьютера или используемый терминал, хотя в ряде случаев ее можно направить на принтер, сохранить в файле или использовать в качестве исходных данных для другой команды или процесса.

Как осуществляется связь запросов?

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

Команда SELECT

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

SELECT snum, sname, city, comm FROM Salespeople;

Выходные данные для этого запроса представлены на рисунке ниже.

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

SELECT	Ключевое слово, которое сообщает базе данных, что команда является запросом. Все запросы начинаются с этого ключевогослова, за которым следует пробел.
snum, sname …	Список столбцов таблицы, которые должны быть представлены в результате выполнения запроса. Столбцы, имена которых не представлены в списке, не включаются в состав выходных данных команды. Это, однако, не приводит к удалению из таблиц таких столбцов или содержащейся в них информации, потому что запрос не воздействует на информацию, представленную в таблицах: он только извлекает данные.
FROM Salespeople	FROM, так же как и SELECT, является ключевым словом, которое должно быть представлено в каждом запросе. Заним следует пробел, а затем — имя таблицы, которая используется как источник информации для запроса. В приведенном примере это таблица Salespeople. Символ «точка с запятой»(;) используется во всех интерактивных командах SQL для сообщения базе данных, что команда сформулирована и готова к выполнению. В некоторых системах этот символ заменен на символ «слэш обратный» («\») в строке, которая непосредственно следует за концом команды.

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

Использование клавиши возврата каретки (клавиши Eпter) является произвольным. Можно ввести запрос в одной строке следующим образом:

SELECT snum, sname, city, comm FROM Salespeople;

Поскольку в SQL точка с запятой применяется для того, чтобы пометить конец команды, большинство SQL-пporpaмм использует клавишу «Возврат каретки» (выполняется нажатием клавиши Return или Enter) как пробел.

Выбор чего-либо простейшим способом

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

SELECT * FROM Salespeople;

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

SELECT в общем виде

Обобщая предыдущие рассуждения, следует отметить, что команда SELECT начинается с ключевого слова SELECT, за которым следует пробел. После него следует список разделенных запятыми имен столбцов, которые необходимо увидеть. Если нужно увидеть все столбцы таблицы, то можно заменить список имен столбцов символом (*) (звездочка). За звездочкой следует ключевое слово FROM, за ним — пробел и имя таблицы, к которой направляется запрос. Символ точка с запятой(;) нужно использовать для того, чтобы закончить запрос и показать, что команда готова для выполнения.

Просмотр только определенных столбцов таблицы

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

SELECT sname, comm FROM Salespeople;

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

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

Перестановка столбцов

Колонки таблицы упорядочены по определению, но это не значит, что их нужно извлекать в том же порядке. Звездочка (*) извлечет столбцы в соответствии с их порядком, но если указать столбцы раздельно, они выстраиваются их в любом желаемом порядке. В таблице Orders зададим такой порядок столбцов: сначала разместим столбец «дата заказа (odate), за ним — столбец «номер продавца» (snum), затем — «номер заказа» (onum) и «количество» (amt):

SELECT odate, snum, onum, amt FROM Orders;

Выходные данные, полученные по этому запросу, представлены на рисунке ниже.

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

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

DISТINCT — аргумент, дающий возможность исключить дублирующиеся значения из результата выполнения предложения SELECT. Предположим, необходимо узнать, какие продавцы имеют в настоящее время заказы в таблице Orders. Не имеет значения количество заказов каждого из продавцов, нужен лишь список номеров продавцов (snum). Необходимо ввести:

SELECT snum FROM Orders;

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

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

SELECT DISTINCT snum FROM Orders;

Выходные данные для этого запроса представлены на рисунке ниже.

DISTINCT отслеживает, какие значения появились в списке выходных данных, и исключает из него дублирующиеся значения. Это полезный способ исключить избыточные данные. Если таковых нет, не следует использовать DISТINCT, поскольку он может скрыть проблемы. Предположим, все имена покупателей различны. Если кто-то введет второго покупателя с фамилией Clemens в таблицу Customers при использовании SELECT DISТINCT cname, можно не заметить, что имеются дублирующиеся данные. Будут получены ошибочные сведения о Clemens, поскольку в этом случае нет информации об избыточности данных.

Параметры DISТINCT. DISТINCT можно задать только один раз для данного предложения SELECT. Если SELECT извлекает множество полей, то он исключает строки, в которых все выбранные поля идентичны. Строки, в которых некоторые значения одинаковы, а другие — различны, включаются в результат. DISТINCT, фактически, действует на всю выходную строку, а не на отдельное поле (исключение составляет его применение внутри агрегатных функций, см. главу 6), исключая возможность их повторения.

DISТINCT в сравнении с ALL. Альтернативой DISTINCT является ALL. Это ключевое слово имеет противоположное действие: повторяющиеся строки включаются в состав выходных данных. Поскольку часто бывает так, что не заданы ни DISТINCT, ни ALL, предполагается ALL; это ключевое слово имеет преимущество перед функциональным аргументом.

Источник: SQL для простых смертных / Мартинн Грабер

С уважением, Артём Санников

Сайт: ArtemSannikov.ru

Tags: MySQL, База данных.

artemsannikov.ru

Тема 14. Построение запросов к базе данных

Типы запросов

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

Запросы на выборку

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

Запросы с параметрами

Запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие. Условие - ограничение, заданное для отбора записей, включаемых в результирующий набор записей запроса или значение, которое требуется вставить в поле. Можно разработать запрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами.

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

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

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

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

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

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

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

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

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

Запросы SQL

Запрос SQL — это запрос, создаваемый при помощи инструкций SQL Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access.

Когда пользователь создает запрос в режиме конструктора запроса, Microsoft Access автоматически создает эквивалентную инструкцию SQL. Фактически, для большинства свойств запроса, доступных в окне свойств в режиме конструктора, имеются эквивалентные предложения или параметры языка SQL, доступные в режиме SQL Режим SQL - окно, в котором выводится инструкция SQL текущего запроса или которое используется для создания запроса SQL. После внесения изменений в запрос в режиме SQL его вид в режиме конструктора может измениться.

Создание запроса на выборку

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

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

Как и другие объекты Ассеss, запросы можно создавать автоматически с помощью Мастера или вручную

Вычисления в запросе

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

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

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

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

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

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

Для создания запроса, производящего вычисления, служит тот же самый бланк запроса по образцу. Разница только в том, что в одном из столбцов вместо имени поля записывают формулу. В формулу входят заключенные в квадратные скобки названия полей, участвующих в расчете, а также знаки математических операций, например так: Сумма: [Кол]*[Заводская цена]

2. В узкий столбец непросто записать длинную формулу, но если нажать комбинацию клавиш Shift+F2, то открывается вспомогательное диалоговое окно, которое называется Область ввода. В нем можно ввести длинную формулу, а потом щелчком на кнопке ОК перенести ее в бланк запроса по образцу.

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

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

Итоговые запросы

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

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

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

1. Рассмотрим работу салона, занимающегося продажей подержанных автомобилей. Результаты работы салона за последнюю неделю содержатся в таблице. В ней можно выделить несколько групп по разному признаку. Записи можно сгруппировать по моделям автомобилей (ВАЗ — отдельно и БМВ — отдельно) или по году выпуска (1989, 1993 и т. д.). Для каждой из групп можно провести итоговое вычисление по полю Цена.

2. Итоговые запросы создают на основе известного нам бланка запроса по образцу, только теперь в нем появляется дополнительная строка — Группировка.

3. Для введения этой строки в бланк надо щелкнуть на кнопке Групповые операции на панели инструментов программы Ассеss.

Далее все происходит очень просто.

4. В тех полях, по которым производится группировка, надо установить (или оставить) функцию Группировка.

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

6. Щелчок на кнопке Вид запускает запрос и выдает результирующую таблицу с необходимыми итоговыми данными.

7. В строке Группировка можно указать лишь одну итоговую функцию. А как быть, если надо найти и сумму, и среднее, и максимальное значение, и еще что-то? Решение простое: одно и то же поле можно включить в бланк запроса по образцу несколько раз.

Статистические функции

Следующие параметры в строке Групповая операция бланка запроса являются статистическими функциями:

Элемент	Результат	Тип поля
Sum	Сумма значений поля.	«Числовой», «Дата/время», «Денежный» и «Счетчик»
Avg	Среднее от значений поля.	«Числовой», «Дата/время», «Денежный» и «Счетчик»
Min	Наименьшее значение поля.	«Текстовый», «Числовой», «Дата/время», «Денежный» и «Счетчик»
Max	Наибольшее значение поля.	«Текстовый», «Числовой», «Дата/время», «Денежный» и «Счетчик»
Count	Число значений поля без учета пустых значений.	«Текстовый», «Числовой», «Дата/время», «Денежный», «Счетчик», «Логический» и «Поле объекта OLE»
StDev	Среднеквадратичное отклонение от среднего значения поля.	«Числовой», «Дата/время», «Денежный» и «Счетчик»
Var	Дисперсия значений поля.	«Числовой», «Дата/время», «Денежный» и «Счетчик»

Функции «First» и «Last»

Функции First и Last используются для возвращения первой или последней записи. Записи возвращаются в том порядке, в котором они были введены. Сортировка записей не влияет на выполнение этих функций.

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

Выше мы говорили о том, что все виды запросов на выборку создают временные результирующие таблицы. Базовые таблицы при этом не изменяются. Тем не менее, специально для разработчиков баз данных существует особая группа запросов, которые называются запросами на изменение. Они позволяют автоматически создавать новые таблицы или изменять уже имеющиеся. Логика использования запросов на изменение такая:

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

• после запуска запроса образуется временная результирующая таблица;

• данные из этой временной таблицы используют для создания новых таблиц или изменения существующих.

Существует несколько видов запросов на изменение. Самый простой и понятный — это запрос на создание таблицы.

Тема 14. Построение запросов к базе данных

Типы запросов

Запросы на выборку

Запросы с параметрами

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

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

Запросы SQL

infopedia.su

Создание запросов на языке sql

SQL – язык структурированных запросов – неотъемлемая часть современных СУБД. Все запросы, которые мы создаем с помощью конструктора, автоматически переводятся на язык SQL и лишь затем идут в дальнейшую обработку. Здесь мы рассмотрим простейшие приемы создания запросов на языке SQL, что будет вполне достаточно для выполнения соответствующего задания контрольной работы.

Извлечение информации осуществляется инструкцией:

SELECT <список извлекаемых полей>

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

[WHERE <условия, налагаемые на значения полей>]

[ORDER BY <имена полей, по которым производится упорядочение>]

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

Перевод с английского некоторых ключевых слов языка SQL: SELECT – выбрать; FROM – из; WHERE – где; ORDER BY – упорядочить по…; GROUP BY – группировать по…; NOT – не; AND – и; OR – или; BETWEEN – между; AS – как.

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

Создание запроса на языке sql в субд Аccess

Для создания запроса на языке SQL необходимо:

запустить редактор запросов SQL;
ввести текст запроса на языке SQL;
запустить запрос на исполнение.

Рассмотрим методику создания запроса на следующем примере.

Задание. Вывести все сведения из таблицы ТУРЫ.

Запрос на языке SQL будет иметь вид

SELECT * FROM ТУРЫ

Для создания запроса необходимо:

В окне БазаИванов выбрать вкладку Запросы; выбрать Создание запроса в режиме конструктора; нажать Открыть.
В окне Добавление таблицы нажать Закрыть (если это окно не появилось, то и нет необходимости его закрывать).
В окне Запрос: запрос на выборку выполнить команду меню Вид/Режим SQL. Откроется окно редактора запросов Запрос: запрос на выборку, уже содержащее оператор SELECT – первое слово инструкции выбора.
Ввести текст инструкции

SELECT * FROM ТУРЫ

Внимание!

Английские слова должны вводиться при установке английского языка, русские слова – русского языка. Типичная ошибка начинающих: в нашем запросе после ввода слова FROM забывают переключить язык на русский, букву «Т» набирают как английскую, затем переключаются на русский алфавит, и хотя внешне разницы в начертании не видно, при попытке исполнить запрос получим сообщение об ошибке.

Для любознательных: попробуйте сделать эту ошибку и посмотрите, какое будет сообщение.

закрыть окно запроса; на вопрос о сохранении изменения макета ответить Да;
задать имя запроса; нажать ОК;
исполнить запрос обычным образом.

Редактирование запроса

Созданный запрос можно отредактировать:

Если запрос был создан в режиме SQL, то скорее всего откроется редактор запросов SQL. Если же запрос откроется в режиме конструктора, выполнить команду меню Вид/Режим SQL.

Теперь в текст запроса можно внести нужные изменения.

Извлечение информации из таблиц

Пример 1. Вывести все сведения из таблицы ФИРМЫ.

SELECT * FROM ФИРМЫ

Примечание. Символ «*» (звездочка) означает «все поля».

Пример 2. Из таблицы ТУРЫ вывести следующие сведения строго по порядку: количество туристов, цена тура, дата.

SELECT Колич, Цена, Дата FROM ТУРЫ

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

Пример 3. Из таблицы ТУРЫ вывести следующие сведения: количество туристов, цена тура, дата тура. Имена полям в запросе дать именно такие, как они приведены здесь.

SELECT Колич AS [количество туристов],

Цена AS [цена тура],

Дата AS [дата тура]

FROM ТУРЫ

Поле Колич получит псевдоним «количество туристов», поле Цена – «цена тура» и т.д.

studfiles.net

Создание запросов SQL | Интересные учебники

Создание запросов SQL

Все запросы, которые мы рассматривали до сих пор, создавались либо с помощью мастера, либо с помощью Конструктора запросов. Конструктор запросов представляет собой графический инструмент для создания запросов по образцу (QBE — Query By Example). Однако на самом деле любой запрос хранится в базе данных в формате SQL (Structured Query Language — язык структурированных запросов). Основное достоинство этого языка состоит в том, что он является стандартом для большинства реляционных СУБД. SQL имеет унифицированный набор инструкций, которые можно использовать во всех СУБД, поддерживающих этот язык. Действующим на данный момент стандартом языка SQL является принятая Американским национальным институтом стандартов (American National Standards Institute — ANSI) версия SQL-92. Фирмы — разработчики СУБД при реализации языка SQL могут вносить в него расширения, но обязаны реализовать базовый набор команд ASNSI SQL. Процессор обработки данных Jet является составной частью Access и выполняет инструкции Access SQL (Jet SQL), который отличается от ANSI SQL существенно (как правило, настольные СУБД, совместимые со стандартом SQL, реализуют не все инструкции ANSI SQL). Замечание В дальнейшем для обозначения используемого в Access диалекта языка SQL мы будем применять термин Jet SQL. Это будет правильнее, т. к. процессор обработки данных используется не только в приложениях, созданных в среде Access, но и в приложениях, разработанных с помощью Microsoft Visual Basic. В данном разделе мы собираемся дать обзор используемого в Access языка SQL и показать, как можно создать запросы, которые невозможно создать с помощью Конструктора запросов. Сравнение ANSI и Jet SQL Язык Jet SQL почти соответствует стандарту ANSI SQL-89. В реализацию языка SQL для Microsoft Jet 4.x (используемого, начиная с версии Microsoft Access 2000) внесены несколько расширений, которые приближают его к стандарту ANSI SQL-92 и Transact-SQL — диалекту языка SQL для Microsoft SQL Server. Для тою чтобы обеспечить совместимость с предыдущими версиями Microsoft Jet, эти расширения можно использовать только в специальном режиме — ANSI SQL-92. Замечание Режим ANSI SQL-92 доступен только при использования программы Microsoft OLE DB Provider для Jet. Все запросы, которые создаются в режиме ANSI SQL-92, помечаются специальным флажком, причем в одной базе данных могут храниться как обычные SQL-запросы, так и запросы, созданные в расширенном синтаксисе. Основные различия языков Jet SQL и ANSI SQL состоят в следующем:

они имеют разные наборы зарезервированных слов и типов данных;
разные правила применимы к оператору Between. . .And, используемому для определения условий выборки записей;
подстановочные знаки ANSI и Microsoft Jet, которые используются в операторе Like, взаимно исключают друг друга;
язык Jet SQL обычно предоставляет пользователю большую свободу, например разрешается группировка и сортировка по выражениям;
язык Jet SQL позволяет использовать более-сложные выражения.

Зарезервированные слова Jet SQL Приведенные здесь таблицы предназначены для сравнения зарезервированных слов ANSI SQL и Jet SQL. Ниже перечисляются зарезервированные слова Jet SQL, которые идентичны зарезервированным словам ANSI SQL (знаком звездочки помечены слова, которые доступны только в режиме ANSI SQL-92):


ADD	COMMIT*	FETCH*	MAX	ROLLBACK*
ALL	CONSTRAINT	FROM	MIN	SELECT
ALTER	COUNT	FOREIGN	NOT	SET
ANY	CREATE	GRANT*	NULL	SOME
ALIAS	CREATE VIEW*	HAVING	ON	TRANSACTION*
AS	CURRENT*	IN	OR	UNION
ASC	CURSOR*	INDEX	ORDER	UNIQUE
AUTHORAZATI ON*	DECLARE*	INNER	OUTER	UPDATE
AVG	DELETE	INSERT	PARAMETERS	VALUE


BEGIN*	DESC	INTO	PRIMARY	VALUES
BETWEEN	DISALLOW	IS	PRIVILEGES*	WHERE
BY	DISTINCT	JOIN	PROCEDURE	WORK*
CHECK*	DROP	KEY	REFERENCES
CLOSE*	DROP VIEW*	LEFT	REVOKE*
COLUMN	EXISTS	LIKE	RIGHT

Зарезервированные слова, обозначающие типы данных, не включены в этот список, т. к. соответствие типов данных ANSI SQL и Jet SQL приводится ниже, в. Большинство операторов сравнения в ANSI SQL и Jet SQL совпадают: =, <, <=, > и =>. Исключение составляет оператор неравенства. Оператору неравенства ! = в ANSI SQL соответствует оператор <> в Jet SQL. Как и в ANSI SQL, зарезервированное слово Jet SQL IN может быть использовано для задания списка значений в предложении WHERE или списка, созданного подчиненным запросом. Оператор IN также может использоваться для идентификации таблицы в другой базе данных. Хотя в последней версии Jet SQL появились новые инструкции, связанные с обработкой транзакций (раньше они реализовывались с помощью процедур VBA), тем не менее существует отличие в механизме выполнения транзакции от рекомендуемого ANSI SQL:

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

В обычном режиме ряд инструкций ANSI SQL не поддерживается, однако их можно реализовать другими средствами Access: меню, кнопками, диалоговыми окнами. В приведен список инструкций ANSI SQL и эквивалентные им средства Access. Таблица 8.3. Зарезервированные слова ANSI SQL, не поддерживаемые Access SQL


	Зарезервированное слово	Эквивалент в Access
	AUTHORIZATION	Диалоговое окно прав доступа
	BEGIN	Метод Access VBA BeginTrans
	CHECK	Свойство Условие на значение поля таблицы
	CLOSE	Кнопка системного меню Закрыть
	COMMIT	Метод Access VBA CommitTrans
	CREATE VIEW	Режим конструктора запросов и фильтры
	CURRENT	Запрос в режиме таблицы, области выделения записи
	CURSOR	Запрос в режиме таблицы
	DECLARE	Запрос в режиме таблицы (курсор поддерживается автоматически)
	DROP VIEW	Режим конструктора запросов
	FETCH	Поля в форме или отчете
	GRANT, PRIVILEGES, REVOKE	Диалоговое окно прав доступа
	ROLLBACK	Метод Access VBA RollbackTrans
	TRANSACTION	Методы транзакций в Access VBA
	VALUES	Значения, введенные в таблицы или формы
	WORK	Метод Access VBA BeginTrans

advicecomp.ru

Построение sql-запросов

Провести анализ структуры базы данных «STUDIUM» (рис. 1).

Рис. 1 – Реляционная модель базы данных «STUDIUM»

Описание полей таблицы STUDENT (СТУДЕНТ):

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

SURNAME — фамилия студента (varchar(20)),

NAME — имя студента (varchar(15)),

STIPEND — стипендия, которую получает студент (smallmoney),

KURS — курс, на котором учится студент (int),

CITY — город, в котором живет студент (varchar(10)),

BIRTHDAY — дата рождения студента (datetime),

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

Описание полей таблицы LECTURER (ПРЕПОДАВАТЕЛЬ):

LECTURER_ID — числовой код, идентифицирующий преподавателя (int),

SURNAME — фамилия преподавателя (varchar(20)),

NAME — имя преподавателя (varchar(15)),

CITY — город, в котором живет преподаватель (varchar(10)),

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

Описание полей таблицы SUBJECT (ПРЕДМЕТ ОБУЧЕНИЯ):

SUBJ_ID — идентификатор предмета обучения (int),

SUBJ_NAME — наименование предмета обучения (varchar(20)),

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

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

Описание полей таблицы UNIVERSITY (УНИВЕРСИТЕТЫ):

UNIV_ID — идентификатор университета (int),

UNIV_NAME — название университета (varchar(20)),

RATING — рейтинг университета (int),

CITY — город, в котором расположен университет (varchar(10)).

Описание полей таблицы EXAM_MARKS (ЭКЗАМЕНАЦИОННЫЕ ОЦЕНКИ):

EXAM_ID — идентификатор экзамена (int),

STUDENT_ID — идентификатор студента (int),

SUBJ_ID — идентификатор предмета обучения (int),

MARK — экзаменационная оценка (int),

EXAM DATE — дата экзамена (datatime).

Описание полей таблицы SUBJ_LECT (УЧЕБНЫЕ ДИСЦИПЛИНЫ ПРЕПОДАВАТЕЛЕЙ):

LECTURER_ID — идентификатор преподавателя (int),

SUBJ ID — идентификатор предмета обучения (int).

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

Таблица STUDENT

Таблица LECTURER Таблица SUBJECT

Таблица UNIVERSITY Таблица EXAM_MARKS

Таблица SUBJ_LECT

Варианты запросов к базе данных:

1. Напишите запрос для определения количества изучаемых предметов на каждом курсе.

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

3. Напишите запрос для получения среднего балла для каждого экзамена.

4. Напишите запрос для получения среднего балла для каждого студента.

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

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

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

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

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

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

11. Напишите запрос, выполняющий вывод фамилии первого в алфавитном порядке (по фамилии) студента, фамилия которого начинается на букву «И».

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

13. Напишите запрос, выбирающий из таблицы EXAM_MARKS данные

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

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

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

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

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

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

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

20. Напишите команду, которая вводит в таблицу SUBJECT строку для нового предмета обучения со следующими значениями полей:

SEMESTER = 4; SUBJ_NAME = ‘Алгебра’; HOUR = 72; SUBJ_ID =201.

21. Напишите команду, удаляющую из таблицы EXAM_MARKS записи обо всех оценках студента, идентификатор которого равен 100.

22. Измените в таблице значение города, в котором проживает студент Иванов, на «Воронеж».

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

24. Напишите запрос, увеличивающий данные о величине стипендии на 20% всем студентам, у которых общая сумма баллов превышает значение 50.

studfiles.net