SQL - Урок 4. Вибір даних - оператор SELECT

Отже, в нашій базі даних є три таблиці: users (користувачі), topics (теми) і posts (повідомлення). І ми хочемо подивитися, які дані містяться. Для цього в SQL існує оператор SELECT. Синтаксис його використання наступний:

SELECT що_вибрати FROM звідки_вибрати;

Замість "що_вибрати" ми повинні вказати або ім'я стовпця, значення якого хочемо побачити, або імена кількох стовпців через кому, або символ зірочки (*), що означає вибір усіх стовпців таблиці. Замість "звідки вибрати" слід вказати ім'я таблиці.

Давайте спочатку подивимося всі стовпці з таблиці users:

SELECT * FROM users;

Ось і всі наші дані, які ми вносили до цієї таблиці. Але припустимо, що ми хочемо подивитися тільки стовпець id_user (наприклад, у минулому уроці, нам треба було заповнити таблиці topics (теми) знати, які id_user є у таблиці users). Для цього у запиті ми вкажемо ім'я цього стовпця:

SELECT id_user FROM users;

Ну, а якщо ми захочемо подивитися, наприклад, імена та e-mail наших користувачів, то ми перерахуємо стовпці, що цікавлять, через кому:

SELECT name, email FROM users;

Аналогічно, ви можете подивитися, які дані містять інші наші таблиці. Давайте подивимося, які у нас є теми:

SELECT * FROM topics;

Зараз у нас лише 4 теми, а якщо їх буде 100? Хотілося б, щоб вони виводилися, наприклад, за абеткою. Для цього в SQL існує ключове слово ORDER BYпісля якого вказується ім'я стовпця за яким відбуватиметься сортування. Синтаксис наступний:

SELECT ім'я_стовпця FROM ім'я_таблиці ORDER BY ім'я_стовпця_сортування;

За умовчанням сортування йде за зростанням, але це можна змінити, додавши ключове слово DESC

Тепер наші дані відсортовані в порядку спадання.

Сортування можна проводити відразу за кількома стовпцями. Наприклад, наступний запит відсортує дані по стовпцю topic_name, і якщо в цьому стовпці буде кілька однакових рядків, то в стовпці id_author буде здійснено сортування за спаданням:

Порівняйте результат із результатом попереднього запиту.

Дуже часто нам не потрібна вся інформація з таблиці. Наприклад, ми хочемо дізнатися, які теми було створено користувачем sveta (id=4). Для цього у SQL є ключове слово WHERE, синтаксис такий запит:

Для прикладу умовою є ідентифікатор користувача, тобто. нам потрібні лише ті рядки, у стовпці id_author яких стоїть 4 (ідентифікатор користувача sveta):

Або ми хочемо дізнатися, хто створив тему "велосипеди":

Звичайно, було б зручніше, щоб замість id автора виводилося його ім'я, але імена зберігаються в іншій таблиці. У наступних уроках ми дізнаємося, як вибирати дані з кількох таблиць. А поки що дізнаємося, які умови можна задавати, використовуючи ключове слово WHERE.

Оператор	Опис
= (Рівно)	Відбираються значення, що рівні зазначеному Приклад: SELECT * FROM topics WHERE id_author=4; Результат:
> (більше)	Відбираються значення більше від зазначеного Приклад: SELECT * FROM topics WHERE id_author>2; Результат:
< (меньше)	Відбираються значення менше від зазначеного Приклад: SELECT * FROM topics WHERE id_author Результат:
>= (більше чи одно)	Відбираються значення великі та рівні вказаному Приклад: SELECT * FROM topics WHERE id_author>=2; Результат:
<= (меньше или равно)	Відбираються значення менші та рівні вказаному Приклад: SELECT * FROM topics WHERE id_author Результат:
!= (Не рівно)	Відбираються значення не рівні вказаному Приклад: SELECT * FROM topics WHERE id_author!=1; Результат:
IS NOT NULL	Відбираються рядки, що мають значення у вказаному полі Приклад: SELECT * FROM topics WHERE id_author IS NOT NULL; Результат:
IS NULL	Відбираються рядки, які не мають значення у вказаному полі Приклад: SELECT * FROM topics WHERE id_author IS NULL; Результат: Empty set – немає таких рядків.
BETWEEN (між)	Відбираються значення між зазначеними Приклад: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Результат:
IN (значення міститься)	Відбираються значення, які відповідають зазначеним Приклад: SELECT * FROM topics WHERE id_author IN (1, 4); Результат:
NOT IN (значення не міститься)	Відбираються значення, крім зазначених Приклад: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Результат:
LIKE (відповідність)	Відбираються значення, що відповідають зразку Приклад: SELECT * FROM topics WHERE topic_name LIKE "вел%"; Результат: Можливі метасимволи оператора LIKE будуть розглянуті нижче.
NOT LIKE (не відповідність)	Відбираються значення, що не відповідають зразку Приклад: SELECT * FROM topics WHERE topic_name NOT LIKE "вел%"; Результат:

Метасимволи оператора LIKE

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

Найпоширеніший метасимвол - % . Він означає будь-які символи. Наприклад, якщо нам треба знайти слова, що починаються з букв "вел", ми напишемо LIKE "вел%", а якщо ми хочемо знайти слова, які містять символи "клуб", то ми напишемо LIKE "%клуб%". Наприклад:

Ще один часто використовуваний метасимвол - _ . На відміну від %, який позначає кілька чи жодного символу, нижнє підкреслення означає рівно один символ. Наприклад:

Зверніть увагу на пробіл між метасимволом і "риб", якщо його пропустити, запит не спрацює, т.к. метасимвол _ позначає рівно один символ, а пропуск - це теж символ.

На сьогодні досить. У наступному уроці ми навчимося складати запити до двох та більше таблиць. А поки що спробуйте самостійно скласти запити до таблиці posts (повідомлення).

Цей урок присвячений SQL запитамдо бази даних на VBA Access. Ми розглянемо, як на VBA здійснюється запити INSERT, UPDATE, DELETE до бази даних, а також навчимося одержувати конкретне значення із запиту SELECT.

Ті, хто програмують на VBA Accessі працюючи при цьому з базою даних SQL сервера, дуже часто стикаються з таким простим і потрібним завданням як посил SQL запиту до бази даних, INSERT, UPDATE або простий SQL запит SELECT . А так як ми програмісти-початківці ми теж повинні вміти це робити, тому сьогодні займемося саме цим.

Ми вже торкалися теми отримання даних із SQL сервера, де якраз на VBA писали код для отримання цих даних, наприклад у статті про Вивантаження даних у текстовий файл з MSSql 2008 або також трохи торкалися у матеріалі Вивантаження даних із Access у шаблон Word та Excel. але так чи інакше там ми розглядали це поверхово, а сьогодні пропоную поговорити про це трохи докладніше.

Примітка! Всі приклади нижче розглянуті з використанням ADP проекту Access 2003 та бази даних MSSql 2008. Якщо Ви не знаєте що взагалі таке ADP проект, то це ми розглядали в матеріалі Як створити та налаштувати ADP проект Access

Вихідні дані для прикладів

Припустимо, у нас є таблиця test_table, яка міститиме номери та назви місяців на рік (запити виконані з використанням Management Studio)

CREATE TABLE .( NOT NULL, (50) NULL) ON GO

Як я вже сказав, ми будемо використовувати ADP проект, налаштований на роботу з MS SQL 2008, в якому я створив тестову форму та додав кнопку start із підписом «Виконати», яка знадобиться для тестування нашого коду, тобто. весь код ми писатимемо в обробнику події « Натискання кнопки».

Запити до бази INSERT, UPDATE, DELETE на VBA

Щоб довго не тягнути відразу приступимо, припустимо, нам потрібно додати рядок до нашої тестової таблиці ( код прокоментовано)/

Private Sub start_Click() "Оголошуємо змінну для зберігання рядка запиту Dim sql_query As String "Записуємо в неї потрібний нам запит sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "Червень")" "Виконуємо його DoCmd. RunSQL sql_query End Sub

У цьому випадку запит виконується за допомогою поточних параметрів підключення до бази даних. Можемо перевірити, чи додалися дані чи ні.

Як бачимо, дані вставили.

Для того, щоб видалити один рядок, пишемо ось такий код.

Private Sub start_Click() "Оголошуємо змінну для зберігання рядка запиту Dim sql_query As String "Записуємо в неї запит на видалення sql_query = "DELETE test_table WHERE id = 6" "Виконуємо його DoCmd.RunSQL sql_query End Sub

Якщо ми перевіримо, то побачимо, що потрібний рядок вийшов.

Для оновлення даних записуємо в змінну sql_query запит update, сподіваюся, сенс зрозумілий.

Запит SELECT до бази на VBA

Тут справи трохи цікавіше, ніж з іншими конструкціями SQL.

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

Private Sub start_Click() "Оголошуємо змінні "Для набору записів з бази Dim RS As ADODB.Recordset "Рядок запиту Dim sql_query As String "Рядок для виведення підсумкових даних у повідомленні Dim str As String "Створюємо новий об'єкт для записів set RS = New ADODB .Recordset "Рядок запиту sql_query = "SELECT id, name_mon FROM test_table" "Виконуємо запит з використанням поточних налаштувань підключення проекту RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Циклом перебираємо запису Wh. для виведення повідомлення str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "перехід до наступного запису RS.MoveNext Wend "Виведення повідомлення msgbox str End Sub

Тут ми використовуємо цикли VBA Access для того щоб перебрати всі значення в нашому наборі записів.

Але досить часто буває необхідно отримати не всі значення з набору записів, а лише одне, наприклад, назва місяця за його кодом. І для цього використовувати цикл якось накладно, тому ми можемо просто написати запит, який поверне всього одне значення і звертатиметься саме до нього, наприклад, отримаємо назву місяця за кодом 5

Private Sub start_Click() "Оголошуємо змінні "Для набору записів з бази Dim RS As ADODB.Recordset "Рядок запиту Dim sql_query As String "Рядок для виведення підсумкового значення Dim str As String "Створюємо новий об'єкт для записів set RS = New ADODB.Recordset "Рядок запиту sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Виконуємо запит з використанням поточних налаштувань підключення проекту RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Отримуємо наше значення str = RS. End Sub

Для універсальності ми вже звернулися за ім'ям осередку, а, по її індексу, тобто. 0, а це найперше значення в Recordset, в результаті ми отримали значення "Травень".

Як бачите, все досить просто. Якщо Вам досить часто потрібно набувати конкретного значення з бази ( як в останньому прикладі), то рекомендую вивести весь код в окрему функцію (Як написати функцію на VBA Access 2003) з одним вхідним параметром, наприклад код місяця ( якщо розглядати наш приклад) і просто де необхідно вивести це значення, викликати потрібну нам функцію з потрібним параметром і все, цим ми значно зменшимо код VBA і покращимо сприйняття нашої програми.

На сьогодні це все. Успіхів!

Опис навчального проекту "Магазин"

Схема зв'язків таблиць

Опис таблиць

m_category - категорії товарів

m_income - прихід товарів

m_outcome - витрата товарів

m_product - довідник, опис товарів

m_supplier - довідник; інформація про постачальників

m_unit – довідник; одиниці виміру

Для практичної перевірки прикладів наведених у даному навчальному матеріалі, необхідно мати наступне програмне забезпечення:

Microsoft Access 2003 або нова.

Запит SQL у MS Access. початок

Щоб побачити вміст таблиці, клацніть двічі на назві таблиці на панелі зліва:

Для переходу в режим редагування полів таблиці на верхній панелі виберіть режим Конструктора:

Щоб вивести результат запиту SQL, клацніть двічі на назві запиту на панелі зліва:

Щоб перейти в режим редагування запиту SQL, на верхній панелі виберіть режим SQL:

Запит SQL. Приклади у MS Access. SELECT: 1-10

У запиті SQL оператор SELECT використовується здійснення вибірки з таблиць бази даних.

Запит SQL Q001.Приклад запиту SQL для отримання лише потрібних полів у потрібній послідовності:

SELECT dt, product_id, amount

FROM m_income;

Запит SQL Q002.У цьому прикладі запиту SQL символ зірочки (*) використаний виведення всіх стовпців таблиці m_product, інакше кажучи, для отримання всіх полів відношення m_product:

SELECT *
FROM m_product;

ЗапитSQL Q003.Інструкція DISTINCT використовується для виключення записів, що повторюються, і отримання безлічі унікальних записів:

SELECT DISTINCT product_id

FROM m_income;

Запит SQL Q004.Інструкція ORDER BY використовується для сортування записів за значеннями певного поля. Ім'я поля вказується за інструкцією ORDER BY:

SELECT *
FROM m_income

ORDER BY price;

Запит SQL Q005.Інструкція ASC використовується як додаток до інструкції ORDER BY та служить для визначення сортування за зростанням. Інструкція DESC використовується як додаток до інструкції ORDER BY і служить для визначення сортування за спаданням. У випадку, коли ні ASC, ні DESC не вказані, мається на увазі наявність ASC (default):

SELECT *
FROM m_income

ORDER BY dt DESC, ціна;

Запит SQL Q006.Для відбору необхідних записів із таблиці користуються різними логічними виразами, які висловлюють умову відбору. Логічне вираз наводиться після інструкції WHERE. Приклад отримання з таблиці m_income всіх записів , для яких значення більше 200:

SELECT *
FROM m_income

WHERE amount>200;

Запит SQL Q007.Для вираження складних умов користуються логічними операціями AND (кон'юнкція), OR (диз'юнкція) та NOT (логічне заперечення). Приклад отримання з таблиці m_outcome всіх записів, для яких значення amount дорівнює 20 і значення price більше або 10:

Price

FROM m_outcome
WHERE amount=20 AND price>=10;

Запит SQL Q008.Для об'єднання двох або більше таблиць користуються інструкціями INNER JOIN, LEFT JOIN, RIGHT JOIN. У наступному прикладі вилучаються поля dt, product_id, amount, price з таблиці m_income та поле title з таблиці m_product. Запис таблиці m_income з'єднується із записом таблиці m_product за рівності значення m_income.product_id значенню m_product.id:

ON m_income.product_id=m_product.id;

Запит SQL Q009.У цьому запиті SQL потрібно звернути увагу на дві речі: 1) шуканий текст укладено в одинарні лапки ("); 2) дата наведена у форматі #Місяць/День/Рік#, що вірно для MS Access. бути іншим Приклад виведення інформації про надходження молока 12 червня 2011 р. Зверніть увагу на формат дати #6/12/2011#:

SELECT dt, product_id, title, amount, price

FROM m_income INNER JOIN m_product

WHERE title="Молоко" And dt=#6/12/2011#; !}

Запит SQL Q010.Інструкція BETWEEN використовується для перевірки належності певного діапазону значень. Приклад запиту SQL, що виводить інформацію про товари, що надійшли між 1-м та 30-м червнем 2011 року:

SELECT *
FROM m_income INNER JOIN m_product

ON m_income.product_id=m_product.id
WHERE dt BETWEEN #6/1/2011# And #6/30/2011#;

Запит SQL. Приклади у MS Access. SELECT: 11-20

Один запит 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 відсотків від суми продажу:

amount*price/100*7 AS profit
FROM m_outcome;

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

SELECT dt, product_id, amount, price, amount*price AS outcome_sum,

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 Sum(amount*price) AS income_sum

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

Запит Q021.Наступний запит SQL обчислює яку суму було продано товарів, мають код 4 чи 6:

SELECT Sum(amount*price) as outcome_sum

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 для визначення категорії кожного оприбуткованого товару;
- обчислення суми приходу(= кількість*ціна) для товарів, код категорії яких дорівнює коду, визначеному вищезазначеним підзапитом.
SELECT
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, зіставити назву категорії ;
- виділити записи, для яких категорія дорівнює "Хлібобулочні вироби";
- Обчислити суму приходу = кількість * ціна.

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:
FROM m_income, m_outcome;

Запит Q031.Приклад групування записів двома полями. Наступний запит SQL обчислює по кожному постачальнику суму і кількість товарів, що від нього надходять:

SUM(amount*price) AS income_sum

Запит Q032.Приклад групування записів двома полями. Наступний запит обчислює для кожного постачальника суму та кількість його продуктів, проданих нами:

SELECT supplier_id, product_id, SUM(amount) AS amount_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 FROM m_income;

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

SELECT dt, product_id, amount,

SWITCH(amount =500,"велика") AS mark
FROM m_income;

Запит Q042.У наступному запиті якщо кількість товару в партії, що надійшла менше 300, то партія вважається «малою». В іншому випадку, якщо умова amount SELECT dt, product_id, amount,
IIF(amount IIF(amount FROM m_income);

Запит SQL Q043.У наступному запиті якщо кількість товару в партії, що надійшла менше 300, то партія вважається «малою». В іншому випадку, якщо умова amount SELECT dt, product_id, amount,
SWITCH(amount amount 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 IIf(amount*price 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 =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
від 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 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(*)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) як SQRT_, x^3 як "x^3", ABS(x) as ABS_
FROM (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.idGROUP BY b.id) t;

Запит SQL. Приклади у MS Access. UPDATE: 1-10

Запит U001.Наступний SQL-запит на зміну збільшує на 10% ціни на товари з кодом 3 у таблиці m_income:

UPDATE m_income SET price = price*1.1

WHERE product_id=3;

Запит U002.Наступний SQL-запит на оновлення збільшує в таблиці m_income на 22 одиниці кількість усіх товарів, назви яких починаються зі слова "Олія":

UPDATE m_income SET amount = amount+22

WHERE product_id IN (SELECT id FROM m_product WHERE title LIKE "Олія*");

Запит U003.Наступний SQL-запит на зміну таблиці m_outcome знижує на 2 відсотки ціни на всі товари, виробником яких є ТОВ "Солодке":

UPDATE m_outcome SET price = price*0.98

WHERE product_id IN
(SELECT a.id FROM m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="ТОВ)"Сладкое"");. !}

Лабораторна робота №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.Створити заnpoc та режим SQL на вибірку відомостей про студентів, які мають екзаменаційну оцінку 3 з предмета ІОСУ.

SELECT *

FROM [РЕШТАЕКЗАМЕНІВ]

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

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

SELECT *

FROMПРЕДМЕТИ

WHEREГОДИННИКBETWEEN 100 і 130;

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

SELECT *

FROMСТУДЕНТИ

WHEREПРІЗВИЩЕLIKE "З*";

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

Приклади запитів SQL можуть бути використані вивчення і практикуму написання запитів SQL в MS Access.

Один запит 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 відсотків від суми продажу:

amount*price/100*7 AS profit
FROM m_outcome;

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

SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
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.