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

Основным видом запроса является запрос на выборку. Результатом выполнения этого запроса является новая таблица, которая существует до закрытия запроса. Записи формируются путем объединения записей таблиц, на которых построен запрос. Способ объединения записей таблиц указывается при определении их связи в схеме данных или при создании запроса. Условия отбора, сформулированные в запросе, позволяют фильтровать записи, составляющие результат объединения таблиц.

В Access может быть создано несколько видов запроса:

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

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

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

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

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

В СУБД Access применяются два типа запросов: QBE – запрос по образцу и SQL (Structured Query Language)- язык структурированных запросов. Запрос по образцу формируется путем заполнения специального бланка запроса в окне "Конструктора запросов". SQL – запросы создаются программистами из последовательности SQL – инструкций . SQL формируется, как правило, программистами на бланке запроса, который открывается командой "Конструктор запросов" на вкладке "Создание" и выбирается "Режим SQL" из меню Вид. Язык SQL предназначен для работы с данными, т.е. для создания, модификации и управления данными в реляционных БД.

Следует отметить, что существует несколько режимов запросов SQL (запросов в режиме ANSI-89 SQL и ANSI-92 SQL), которые соответствуют стандартам ANSI-89 SQL и ANSI-92 SQL.

Инструкции содержат описание набора данных на языке SQL. Инструкции SQL состоят из предложений (SELECT, FROM, WHERE и т.д.). Предложения на языке SQL состоят из терминов (операторов или команд, идентификаторов, констант и т.д.). Инструкция начинается оператором (одной из команд SELECT, CREATE, INSERT, UPDATE, DELETE и т.д.) и заканчивается точкой с запятой. Основные операторы SQL: SELECT, FROM и WHERE.

Например, инструкция SQL:
SELECT Студенты.КодСтудента
FROM Студенты;
состоит из предложения "SELECT Студенты.КодСтудента" и предложения "FROM Студенты".

Предложение SELECT содержит оператор SELECT и идентификатор "Студенты.КодСтудента". Здесь полное имя поля "КодСтудента" предваряется именем таблицы "Студенты" базы данных. SELECT - определяет поле, которое содержит требуемые данные. Предложение FROM состоит из оператора FROM и идентификатора "Студенты". FROM - определяет таблицу, которая содержат поля, указанные в предложении SELECT.

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

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

Чтобы посмотреть SQL – запросы на выборку в СУБД Access 2003 или 2007 необходимо в активном окне проектирования запроса по образцу (рис. 1) выполнить команду Вид/режим SQL.

Рис. 1.

Получим инструкцию SQL (SELECT) на выборку данных из БД Access 2003 по критерию успеваемости студентов "Оценка=5" (рис. 2).

Рис. 2.

Как следует из инструкции SELECT (рис. 1), она описывает набор данных на языке SQL: SELECT - определяет имена полей, предваряемые именами таблиц, в которых содержатся данные; FROM - определяет таблицы и их взаимосвязи через ключевые поля таблиц (для этого используется конструкция INNER JOIN ... ON), на основе которых отбираются данные; WHREME - определяет условия отбора полей; ORDER BY - определяет способ сортировки по возрастанию (по умолчанию выполняется сортировка по возрастанию) значений поля "Фамилия" таблицы "Студенты".

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

Лабораторная работа №1

SQL : ИЗВЛЕЧЕНИЕ ДАННЫХ - команда SELECT

Цель работы:

• познакомиться с SQL-инструкциями;
• научиться в Access создавать простейшие SQL-запросы с помощью команды SELECT;

· использование операторов IN, BETWEEN, LIKE, IS NULL.

Задание №1. Создать запрос на выборку в режиме SQL всех значений полей ИМЯ и ФАМИЛИЯ из таблицы СТУДЕНТЫ.

SELECT ИМЯ, ФАМИЛИЯ

FROM СТУДЕНТЫ;

Задание №2 . Создать запрос на выборку в режиме SQL всех столбцов таблицы СТУДЕНТЫ.

SELECT *

FROM СТУДЕНТЫ;

Задание №3. Создать запрос на выборку в режиме SQL названий городов, где проживают студенты, сведения о которых находятся в таблице ЛИЧНЫЕ ДАННЫЕ.

SELECT DISTINCT ГОРОД

FROM [ЛИЧНЫЕ ДАННЫЕ];

Задание №4. Создать запрос на выборку в режиме SQL, выполняющий выборку имен всех студентов с фамилией Иванов, сведения о которых находятся в таблице СТУДЕНТЫ.

SELECT ФАМИЛИЯ, ИМЯ

FROM СТУДЕНТЫ

WHERE ФАМИЛИЯ="Иванов";

Задание №5 . Создать запрос на выборку в режиме SQL для получения имен и фамилий студентов, обучающихся в группе УИТ-22 на бюджетной форме обучения.

SELECT ФАМИЛИЯ, ИМЯ

FROM СТУДЕНТЫ

WHERE ГРУППА="УИТ-22" AND БЮДЖЕТ=true;

Задание №6 . Создать запрос в режиме SQL. на выборку из таблицы СДАЧА ЭКЗАМЕНОВ сведении о студентах, имеющих оценки только 4 и 5.

SELECT *

FROM [ СДАЧА ЭКЗАМЕНОВ ]

WHERE ОЦЕНКА IN (4,5);

Задание №7. Coздать зanpoc и режиме SQL на выборку сведений о студентах, имеющих экзаменационную оценку 3 по предмету ИОСУ.

SELECT *

FROM [ СДАЧА ЭКЗАМЕНОВ ]

WHERE ПРЕДМЕТ =" ИОСУ " And ОЦЕНКА Not In (4,5);

Задание №8. Создать запрос в режиме SQL на выборку записей о предметах, часы которых находятся в пределах между 100 и 130.

SELECT *

FROM ПРЕДМЕТЫ

WHERE ЧАСЫ BETWEEN 100 AND 130;

Задание №9. Создать запрос в режиме SQL на выборку из таблицы СТУДЕНТЫ сведений о студентах, фамилии которых начинаются, например, на букву «С».

SELECT *

FROM СТУДЕНТЫ

WHERE ФАМИЛИЯ LIKE " С *";

Вывод: В ходе лабораторной работы познакомились с SQL-инструкциями, научились в Access создавать простейшие SQL-запросы с помощью команды SELECT используя операторы IN, BETWEEN, LIKE.

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

Запрос Q011. Выводится информация о товарах из таблицы m_product, коды которых есть и в таблице m_income:

SELECT *
FROM m_product
WHERE id IN (SELECT product_id FROM m_income);

Запрос Q012. Выводится список товаров из таблицы m_product, кодов которых нет в таблице m_outcome:

SELECT *
FROM m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);

Запрос Q013. В этом запросе SQL выводится уникальный список кодов и названий товаров, коды которых есть в таблице m_income, но которых нет в таблице m_outcome:

SELECT DISTINCT product_id, title
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Запрос Q014. Выводится из таблицы m_category уникальный список категорий, названия которых начинаются на букву М:

SELECT DISTINCT title
FROM m_product
WHERE title LIKE "М*";

Запрос Q015. Пример выполнения арифметических операций над полями в запросе и переименования полей в запросе (alias). В этом примере для каждой записи о расходе товара подсчитываются сумма расхода = количество*цена и размер прибыли, при предположении, что прибыль составляет 7 процентов от суммы продаж:

Price, amount*price AS outcome_sum,
amount*price/100*7 AS profit
FROM m_outcome;

Запрос Q016. Проанализировав и упростив арифметические операции, можно увеличить скорость выполнения запроса:

outcome_sum*0.07 AS profit
FROM m_outcome;

Запрос Q017. При помощи инструкции INNER JOIN можно объединить данные нескольких таблиц. В следующем примере, в зависимости от значения ctgry_id, каждой записи таблицы m_income, сопоставляется название категории из таблицы m_category, к которой принадлежит товар:

SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;

Запрос Q018. Такие функции как SUM - сумма, COUNT - количество, AVG – среднее арифметическое значение, MAX – максимальное значение, MIN – минимальное значение называются агрегатными функциями. Они принимают множество значений, и после их обработки возвращают единственное значение. Пример подсчета суммы произведения полей amount и price при помощи агрегатной функции SUM:

SELECT SUM(amount*price) AS Total_Sum
FROM m_income;

Запрос Q019. Пример использования нескольких агрегатных функций:

SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,
MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min,
Count(*) AS Total_Number
FROM m_income;

Запрос Q020. В этом примере подсчитана сумма всех товаров с кодом 1, оприходованных в июне 2011 года:

SELECT
FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.

Запрос Q021. Следующий запрос SQL вычисляет на какую сумму было продано товаров, имеющих код 4 или 6:

SELECT
FROM m_outcome
WHERE product_id=4 OR product_id=6;

Запрос Q022. Вычисляется на какую сумму было продано 12 июня 2011 года товаров, имеющих код 4 или 6:

SELECT Sum(amount*price) AS outcome_sum
FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;

Запрос Q023. Задача такова. Вычислить на какую общую сумму было оприходовано товаров категории "Хлебобулочные изделия".

Для решения этой задачи нужно оперировать тремя таблицами: m_income, m_product и m_category, потому что:
- количество и цена оприходованных товаров хранятся в таблице m_income;
- код категории каждого товара хранится в таблице m_product;
- название категории title хранится в таблице m_category.

Для решения данной задачи воспользуемся следующим алгоритмом:
- определение кода категории "Хлебобулочные изделия" из таблицы m_category посредством подзапроса;
- соединение таблиц m_income и m_product для определения категории каждого оприходованного товара;
- вычисление суммы прихода(= количество*цена) для товаров, код категории которых равен коду, определенному вышеуказанным подзапросом.

FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Хлебобулочные изделия");

Запрос Q024. Задачу вычисления общей суммы оприходованных товаров категории "Хлебобулочные изделия" решим следующим алгоритмом:
- каждой записи таблицы m_income, в зависимости от значения его product_id, из таблицы m_category, сопоставить название категории;
- выделить записи, для которых категория равна "Хлебобулочные изделия";
- вычислить сумму прихода = количество*цена.

SELECT Sum(amount*price) AS income_sum
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Хлебобулочные изделия";

Запрос Q025. В этом примере вычисляется сколько наименований товаров было израсходовано:

SELECT COUNT(product_id) AS product_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Запрос Q026. Инструкция GROUP BY используется для группировки записей. Обычно записи группируются по значению одного или нескольких полей, и относительно каждой группы применяется какая-либо агрегатная операция. Например, следующий запрос состявляет отчет о продаже товаров. То есть генерируется таблица, в которой будут названия товаров и сумма, на которую они проданы:

SELECT title, SUM(amount*price) AS outcome_sum
FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY title;

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

SELECT c.title, SUM(amount*price) AS outcome_sum,
ROUND(AVG(amount*price),2) AS outcome_sum_avg
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;

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

SELECT product_id, SUM(amount) AS amount_sum,
Round(Avg(amount),2) AS amount_avg
FROM m_income
GROUP BY product_id
HAVING Sum(amount)>=500;

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

SELECT title, SUM(amount*price) AS income_sum
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
WHERE dt BETWEEN #4/1/2011# AND #6/30/2011#
GROUP BY title
HAVING SUM(amount*price)>=1000;

Запрос Q030. В некоторых случаях нужно сопоставлять каждой записи некоторой таблицы каждую запись другой таблицы; что называется декартовым произведением. Таблица, образующаяся в результате такого соединения, называется таблицей Декарта. Например, если некоторая таблица А имеет 100 записей и таблица В имеет 15 записей, то их таблица Декарта будет состоять из 100*15=150 записей. Следующий запрос соединяет каждую запись таблицы m_income с каждой записью таблицы m_outcome:

SELECT *FROM m_income, m_outcome;

Запрос Q031. Пример группирования записей по двум полям. Следующий запрос SQL вычисляет по каждому поставщику сумму и количество поступивщих от него товаров:

SUM(amount*price) AS income_sum

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

SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
GROUP BY supplier_id, product_id;

Запрос Q033. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом LEFT JOIN объединены в один отчет. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже поступил, но еще не реализован, то клетка outcome_sum для этой записи будет пустой. Также необходимо отметить, что данный запрос служит только примером использования относительно сложных запросов в качестве подзапроса. Производительность данного запроса SQL при большом объеме данных сомнительна:

SELECT *
FROM
SUM(amount*price) AS income_sum
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
LEFT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);

Запрос Q034. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом RIGTH JOIN объединены в один отчет. Следующий запрос выводит отчет о сумме платежей каждого клиента по использованным им платежным системам и сумме сделанных им инвестиций. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже реализован, но еще не поступил, то клетка income_sum для этой записи будет пустой. Наличие таких пустых клеток является показателем ошибки в учете продаж, так как до продажы сначала необходимо, чтобы соответствующий товар поступил:

SELECT *
FROM
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
RIGHT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);

Запрос Q035. Выводится отчет о сумме доходов и расходов по продуктам. Для этого создается список продуктов по таблицам m_income и m_outcome, затем для каждого продукта из этого списка вычисляется сумма его приходов по таблице m_income и сумма его расходов по таблице m_outcome:

SELECT product_id, SUM(in_amount) AS income_amount,
SUM(out_amount) AS outcome_amount
FROM
(SELECT product_id, amount AS in_amount, 0 AS out_amount
FROM m_income
UNION ALL
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;

Запрос Q036. Функция EXISTS возвращает значение TRUE, если переданное ей множество содержит элементы. Функция EXISTS возвращает значение FALSE, если переданное ей множество пустое, то есть не содержит элементов. Следующий запрос выводит коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b

Запрос Q037. Выводятся коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Запрос Q038. Выводятся коды товаров, которые содержатся как в таблице m_income, но не содержатся в таблице m_outcome:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Запрос Q039. Выводится список товаров, сумма продаж которых максимальная. Алгоритм таков. Для каждого товара вычисляется сумма его продаж. Затем, определяется максимум этих сумм. Затем, для каждого товара снова вычисляется сумма его продаж, и выводятся код и сумма продаж товаров, сумма продаж которых равна максимальной:

SELECT product_id, SUM(amount*price) AS amount_sum
FROM m_outcome
GROUP BY product_id
HAVING SUM(amount*price) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id));

Запрос Q040. Зарезервированное слово IIF (условный оператор) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата (TRUE или FALSE). В следующем примере поставка товара считается «малой», если количество меньше 500. В противном случае, то есть количество поступления больше или равно 500, поставка считается «большой»:

SELECT dt, product_id, amount,
IIF(amount<500,"малая","большая") AS mark
FROM m_income;

Запрос SQL Q041. В случае, когда оператор IIF используется несколько раз, удобнее заменить его оператором SWITCH. Оператор SWITCH (оператор множественного выбора) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата. В следующем примере поставленная партия считается «малой», если количество товара в партии меньше 500. В противном случае, то есть если количество товара больше или равно 500, партия считается «большой»:

SELECT dt, product_id, amount,
SWITCH(amount<500,"малая",amount>=500,"большая") AS mark
FROM m_income;

Запрос Q042. <300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

SELECT dt, product_id, amount,
IIF(amount<300,"малая",
IIF(amount<1000,"средняя","большая")) AS mark
FROM m_income;

Запрос SQL Q043. В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount<300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

SELECT dt, product_id, amount,
SWITCH(amount<300,"малая",
amount<1000,"средняя",
amount>=1000,"большая") AS mark
FROM m_income;

Запрос SQL Q044. В следующем запросе продажи разделяются на три группы: малые (до 150), средние (от150 до 300), большие (300 и более). Далее, для каждой группы вычисляется итоговая сумма:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price<150,"малая",
IIf(amount*price<300,"средняя","большая")) AS Category
FROM m_outcome) AS t
GROUP BY Category;

Запрос SQL Q045. Функция DateAdd используется для прибавления дней, месяцев или лет к данной дате и получения новой даты. Следующий запрос:
1) к дате из поля dt прибавляет 30 дней и отображает новую дату в поле dt_plus_30d;
2) к дате из поля dt прибавляет 1 месяц и отображает новую дату в поле dt_plus_1m:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
FROM m_income;

Запрос SQL Q046. Функция DateDiff предназначена для вычисления разницы между двумя датами в различных единицах (днях, месяцах или годах). Следующий запрос вычисляет разницу между датой в поле dt и текущей датой в днях, месяцах и годах:

SELECT dt, DateDiff("d",dt,Date()) AS last_day,
DateDiff("m",dt,Date()) AS last_months,
DateDiff("yyyy",dt,Date()) AS last_years
FROM m_income;

Запрос SQL Q047. Вычисляются количество дней со дня поступления товара (таблица m_income) до текущей даты с помощью функции DateDiff и сопоставляется срок годности (таблица m_product):

DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id;

Запрос SQL Q048. Вычисляются количество дней со дня поступления товара до текущей даты, затем проверяется превышает ли это количество срок годности:

SELECT a.id, product_id, dt, lifedays,
DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Да","Нет") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;

Запрос SQL Q049. Вычисляются количество месяцев со дня поступления товара до текущей даты. В столбце month_last1 вычисляется абсолютное количество месяцев, в столбце month_last2 вычисляется количество полных месяцев:

SELECT dt, DateDiff("m",dt,Date()) AS month_last1,
DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;

Запрос SQL Q050. Выводится поквартальный отчет о количестве и сумме оприходованных товаров за 2011 год:

SELECT kvartal, SUM(outcome_sum) AS Total
FROM (SELECT amount*price AS outcome_sum, month(dt) AS m,
SWITCH(m<4,1,m<7,2,m<10,3,m>=10,4) AS kvartal
FROM m_income WHERE year(dt)=2011) AS t
GROUP BY kvartal;

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

SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum
FROM (SELECT product_id, amount*price as in_sum, 0 as out_sum
from m_income
UNION ALL
SELECT product_id, 0 as in_sum, amount*price as out_sum
from m_outcome) AS t
GROUP BY product_id
HAVING SUM(in_sum)

Запрос Q052. Нумерацию строк, возвращаемых запросом, реализуют по-разному. Например, можно перенумеровать строки отчета, подготовленного в MS Access, средствами самого MS Access. Перенумеровать можно и с использованием языков программирования, например, VBA или PHP. Однако иногда это необходимо сделать в самом запросе SQL. Итак, следующий запрос пронумерует строки таблицы m_income в соответствии с порядком возрастания значений поля ID:

SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price
FROM m_income a INNER JOIN m_income b ON a.id <= b.id
GROUP BY b.id, b.product_id, b.amount, b.price;

Запрос Q053. Выводится пятерка лидеров среди продуктов по сумме продаж. Вывод первых пяти записей осуществляется с помощью инструкции TOP:

SELECT TOP 5, product_id, sum(amount*price) AS summa
FROM m_outcome
GROUP BY product_id
ORDER BY sum(amount*price) DESC;

Запрос Q054. Выводится пятерка лидеров среди продуктов по сумме продаж, и нумерует строки в результате:

SELECT COUNT(*) AS N, b.product_id, b.summa
FROM

FROM m_outcome GROUP BY product_id) AS a
INNER JOIN
(SELECT product_id, sum(amount*price) AS summa,
summa*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)<=5
ORDER BY COUNT(*);

Запрос Q055. Следующий SQL-запрос показывает использование математических функций COS, SIN, TAN, SQRT, ^ и ABS в MS Access SQL:

SELECT (select count(*) from m_income) as N, 3.1415926 as pi, k,
2*pi*(k-1)/N as x, COS(x) as COS_, SIN(x) as SIN_, TAN(x) as TAN_,
SQR(x) as SQRT_, x^3 as "x^3", ABS(x) as ABS_
FROM (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.id<=b.id
GROUP BY b.id) t;

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

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

Классификация команд языка SQL

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

Команды для построения запросов.

Команды встроенных процедур и функций.

Команды триггеров и системных таблиц.

Наборы комбинаций для работы с датой и строковыми переменными.

Команды для работы с данными и таблицами.

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

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

Сферу использования SQL можно рассматривать с точки зрения офисного программного обеспечения, а именно MicrosoftAccess. Этот язык, а точнее, его разновидность — MySQL, позволяет администрировать базы данных в сети Internet. Даже среда разработки Oracle использует в основе своих запросов команды SQL.

Использование SQL в MicrosoftAccess

Одним из самых простых примеров использования языка для программирования баз данных считается пакет программного обеспечения MicrosoftOffice. Изучение этого программного продукта предусмотрено школьным курсом информатики, а в одиннадцатом классе рассматривается система управления базой данных MicrosoftAccess.

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

Рассмотрим конкретный пример:

SELECT Pe_SurName

WHERE Pe_Name = "Мэри";

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

Хоть и использование SQL в Access ограничено, иногда такие простые запросы очень сильно могут упростить выполнение поставленного задания.

Использование команд SQL в Oracle

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

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

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

SELECTCONCAT(CONCAT(CONCAT(‘Сотрудник ‘, sname), CONCAT(SUBSTR(fname, 0, 1), SUBSTR(otch, 0, 1))), CONCAT(‘принятнаработу ‘, acceptdate)) FROM employees WHERE acceptdate > to_date(‘01.01.80′,’dd.mm.yyyy’);

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

Использование SQL в сети Internet

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

За хранение информации в Интернете, как и в других местах, отвечают непосредственно базы данных, а сайты являются системами управления. Как правило, сайты и их программный код организованы на разных языках программирования, но в основе баз данных лежит одна из разновидностей SQL, а именно язык создания баз данных, ориентированный под веб-интерфейсы MySQL.

Синтаксис и основной набор команд этого языка полностью копируют привычный всем SQL, но с некоторыми своими дополнениями, которые и дают ему отличие от Microsoft tSQL Server.

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

$link = mysqli_connect("localhost", "root", "", "tester");

if (!$link) die("Error");

$query = "create table users(

login VARCHAR(20),

password VARCHAR(20)

if (mysqli_query($link, $query)) echo "Таблица создана.";

elseecho "Таблица не создана: ".mysqli_error();

mysqli_close($link);

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

Синтаксис изменен под Вэб, но в основу положены команды MicrosoftSQLServer.

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

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

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

Select * from Person

Where P_BerthDay >= ‘01/01/2016’ and P_BerthDay<= ‘03/01/2016’

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

SelectP_Name - имя

P_SurName - фамилия

P_Patronimic - отчество

Where P_BerthDay >= ‘01/01/2016’ and P_BerthDay<= ‘03/01/2016’

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

Основные команды SQL для изменения данных

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

Insert (пер. Вставить).

Update (пер. Обновление).

Delete (пер. Удалить).

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

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

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

Команда Insert

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

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

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

Insert into person

Select ‘Григорьев’,’Виталий’,’Петрович’,’01/01/1988’

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

Insertintoperson(P_Name, P_SurName ,P_BerthDay)

Values (‘Дэвид’, ‘Гук’,’02/11/1986’)

Такая команда заполнит только указанные ячейки, а все остальные будут иметь значение null.

Команда для изменения данных

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

Команда Update SQL имеет несложный синтаксис. Для правильного использования необходимо указать, какие данные, в какой колонке и в какой записи стоит изменить. Далее составить скрипт и выполнить его. Рассмотрим пример. Нужно изменить дату рождения Дэвида Гука, который внесен в таблицу сотрудников под номером 5.

Set P_BerthDay = ’02/10/1986’ where P_ID = 5

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

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

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

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

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

Предположим, что была создана процедура для вывода даты рождения сотрудников с ранее описанной таблицы Person. В таком случае нет необходимости писать весь запрос. Для получения необходимой информации достаточно выполнить команду Exec [имя процедуры] и передать необходимые для выборки параметры. Как пример можно рассмотреть механизм создания процедуры такого характера:

CREATEPROCEDUREPrintPerson

@DB smalldatetime

@DE smalldatetime

SELECT * from Person

FROM HumanResources.vEmployeeDepartmentHistory

WHERE P_BerthDay >= @DB and P_BerthDay <= @DE

ANDEndDateISNULL;

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

Организация целостности данных. Триггеры

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

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

Типы команд SQL, которые можно использовать в триггере, не ограничены. Рассмотрим на примере.

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

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

Указываем, для какой операции с данными (в нашем случае это операция изменения данных).

Следующим шагом будет указание таблиц и переменных:

declare @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime

DEclare cursor C1 for select P_ID, P_BerthDay from Inserted

DEclare cursor C2 for select P_ID, P_BerthDay from deleted

Задаем шаги выбора данных. После, в теле курсоров прописываем условие и реакцию на него:

if @ID = @nID and @nDate = "01/01/2016"

sMasseges "Выполнить операцию невозможно. Дата не подходит"

Стоит упомянуть о том, что триггер можно не только создать, но и отключить на время. Такую манипуляцию может провести только программист, выполнив команды SQL SERVER:

altertablePERSONdisabletriggerall - для отключения всех триггеров, созданных для данной таблицы, и, соответственно, altertablePERSONenabletriggerall - для их включения.

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

Вывод

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

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

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