Подякувати Студентському архіву довільною сумою
Admin
26.02.2023 12:38
Дякуємо, що користуєтесь нашим архівом!
Отримання даних та статистичної інформації
Інформація про навчальний заклад
ВУЗ:
Національний університет Львівська політехніка
Інститут:
Не вказано
Факультет:
Не вказано
Кафедра:
Не вказано
Інформація про роботу
Рік:
2009
Тип роботи:
Лабораторна робота
Предмет:
Бази даних та знань
Група:
КС-42
Частина тексту файла (без зображень, графіків і формул):
Міністерство освіти та науки України
Національний університет “Львівська політехніка”
Лабораторна робота № 2
з курсу “Бази даних та знань”
Отримання даних та статистичної інформації
з бази даних за допомогою запитів мовою T-SQL
Львів 2009
Мета роботи: ознайомитися зі структурованою мовою запитів SQL на основі її діалекту Transact-SQL для СКБД Microsoft SQL Server 2005 та отримати навики написання SQL-запитів для здійснення вибірки даних та отримання статистичної інформації за допомогою інструмен тарію Management Studio.
1. Відомості про Transact-SQL
Мова Transact-SQL (чи скорочено T-SQL) – це власний діалект мови структурованих запитів (Structured Query Language – SQL), що використовується в системі керування базами даних (СКБД) Microsoft SQL Server. Мова T-SQL призначена виключно для роботи з СКБД Microsoft SQL Server, хоча і є сумісною на початковому рівні зі стан дартом ANSI SQL-92. Це означає, що основні базові оператори є такими ж, як і для інших відомих СКБД: Oracle, Sybase, DB2, MySQL, PostgreSQL. Тому отримані навики написання SQL-запитів за допомо гою T-SQL можуть бути використані й при роботі із цими перелічени ми серверами баз даних. Але при цьому слід враховувати, що в кожній реляційній СКБД використовуються різні розширення та способи підвищення продуктивності, що доповнюють вказаний стандарт та навіть виходять далеко за його рамки. Це такі засоби, як умовна обробка (IF … THEN), керуючі оператори (наприклад, цикли WHILE), змінні, засоби оброблення помилок тощо.
У загальному мова SQL поділяється на три частини:
Оператори визначення даних – DDL (Data Definition Lan guage). Призначені для опису структури баз даних. Це такі як: CREATE (створити), ALTER (змінити), DROP (видалити).
Оператори маніпулювання даними – DML (Data Manipulation Lan guage). Призначені для отримання, вставки, видалення чи зміни даних у базі даних. Це такі як: SELECT (вибрати), INSERT (вставити), UPDATE (обновити), DELETE (видалити).
Оператори керування доступом до даних – DCL (Data Control Language). Це такі як: GRANT (надати доступ), REVOKE (анулювати попередні права).
Цей «кістяк» є загальним для всіх існуючих діалектів мови SQL. Найбільшим по своїй структурі та можливостям серед них є оператор вибірки SELECT. Саме тому ця лабораторна робота і є зорієнтованою на написання SQL-запитів на основі оператора SELECT.
Оператор вибірки SELECT
Загальний формат.
SELECT [ DISTINCT ]
< * | перелік стовпців | формули | статистичні функції >
FROM < перелік таблиць [ псевдоніми ] >
[ WHERE < умова відбору даних > ]
[ GROUP BY < ім’я стовпця(ів) > [ HAVING < умова для групи > ] ]
[ ORDER BY < перелік стовпців | порядкові № стовпців > ] [ ; ]
SELECT – основний оператор. Після нього перераховуються стовпці, які необхідно отримати від сервера.
FROM – у цій секції вказуються таблиці, з яких необхідно отримати певні дані.
WHERE – у цій секції можна задати параметри пошуку та обме ження для вихідних даних.
GROUP BY – у цій секції можна вказати перелік стовпців, за якими необхідно згрупувати дані.
HAVING – у цій секції можна задати додаткові обмеження пошуку для створених груп.
ORDER BY – у цій секції можна вказати параметри сортування, а точніше, стовпці, за якими буде відсортований результат.
Зауваження: згідно негласного правила, прийнято писати команди та ключові слова мови SQL великими літерами, а назви імен таблиць, стовпців та відмінних від констант змінних – змішаним регістром літер, хоча така вимога для СКБД не є обов’язковою.
Повний шлях.
Повний формат звертання до таблиці виглядає так:
БазаДаних.Схема.Таблиця (Labor_SQL.Pavelchak.Classes )
Якщо вже існує підключення до потрібної бази даних, то її можна не вказувати, а вказати лише схему та таблицю
Схема.Таблиця ( Pavelchak.Classes )
Якщо таблиця створена для схеми dbo, тоді можна пропустити й цей параметр.
Таблиця ( Ships )
Тому рекомендується створювати таблиці саме із цими правами. Якщо необхідно звернутися до іншої БД, але відомо, що таблиця належить dbo, тоді до таблиці можна звернутися так
БазаДаних. . Таблиця ( Labor_SQL. .Ships )
3.3. Проста вибірка.
Вибір усіх стовпців із даними:
SELECT * FROM Students;
Буде виведено таблицю з усіма даними. Груповий символ * замінює перелік з усіма стовпцями таблиці.
Вибір декількох стовпців:
SELECT Surname, Name FROM Students;
Стовпці будуть виведені в тому порядку, у якому вони вказані в переліку після ключового слова SELECT.
Псевдоніми для стовпців:
SELECT Surname AS Sn, Name AS N FROM Students;
або
SELECT Surname Sn, Name N FROM Students;
Ключове слово AS є необов’язковим. Псевдоніми (у прикладі Sn та N) вико ристо вуються лише для іменування стовпців при виведенні та представленні їх за межами цього запиту.
Ключове слово DISTINCT:
SELECT DISTINCT Surname, Name FROM Students;
Ключове слово DISTINCT забезпечує фільтрацію дублікатів стрі чок, що мають бути виведеними.
Сортування даних.
Сортування за вказаним напрямом:
SELECT Surname, Name FROM Students
ORDER BY Name, Surname DESC;
Сортування здійснюється за стовпцями, які вказані після ключо вого слова ORDER BY. Порядок сортування визначається таким чи ном:
спочатку здійснюється сортування за першим вказаним у пере ліку ORDER BY стовпцем;
далі, якщо у першому стовпцеві є однакові поля, тоді сортування виконується за наступним стовпцем і т.д.;
для кожного стовпця можна задати напрямок сортування у виг ляді ключових слів ASC (за зростанням) та DESC (за спадан ням), при цьому напрямок ASC (за зростанням) можна не вказувати, оскільки він приймається за замовчуванням.
Сортування за номером стовпця:
SELECT Surname, Name FROM Students
ORDER BY 2, 1 DESC;
Цифри вказують на відносне положення в переліку зі стовпцями після ключового слова SELECT.
Фільтрація даних.
SELECT Surname, Name FROM Students;
WHERE Id >= 2 ;
Фільтрація даних виконується за допомогою логічної умови, що вказується після ключового слова WHERE. Якщо стрічка задовольняє вка заній умові, тоді вона буде вибрана, якщо ні – пропускається. Пере лік допустимих операцій конструкції WHERE наводиться в табл. 3.1.
Таблиця 3.1. Операції, що використовуються в конструкції WHERE
Операція
Приклад
Призначення
=, >, <, >=, <=, <>, != , !> , !<
Price <= 120
Table1.Id = Table2.Id
Стандартні операції порівняння.
1. Результати виконання операцій «більше ніж» (>) та «менше ніж» (<) можуть змінюватися в залежності від вибраної схеми впорядкування.
Наприклад, якщо в БД вибрана схема впорядкування, яка не чутлива до ре гістру літер, тоді 'Pavel' = 'pavel', а як що враховується, тоді 'Pavel' <> 'pavel'.
2. Ідентичні операцій: <> та !=;
!> та <=; !< та >= .
AND, OR, NOT
Price <= 120 AND
Table1.Id = Table2.Id
OR NOT Old > 20
Стандартні булеві логічні операції. Вони використовуються для об’єднан ня декількох умов.
IS [NOT] NULL
Price IS NULL
Пошук даних за пустими (NULL) значеннями у стовпцях.
[NOT] BETWEEN
Price BETWEEN
25 AND 50
Ця операція порівняння повертає значення TRUE, якщо перше значення (Price) знаходиться між другим (25) та третім (50) значеннями включно, та є еквівалентна виразу Price >= 25 AND Price <= 50.
[NOT] LIKE
Name LIKE 'Pav%'
Email LIKE '%@.polynet.lviv.ua'
Операція порівняння за вказаним шаб лоном. Основні символи-шаблони:
% замінює довільну послідовність з нуль або більше символів;
_ (підкреслення) замінює довільний одиничний сим вол;
[] замінює довільний одиничний символ, що вказаний у наборі, наприк лад, [bcz] чи [0-5].
[^] замінює довільний одинич ний символ, що не входить у вказаний набір, наприклад, [^bk-z].
Детальніше у лабораторній роботі №1.
[NOT] IN
Surname NOT IN
('Петров', 'Іванов', 'Скворцов')
Операція порівняння для визначення відповідності заданого значення одно му зі значень, що перераховані в спис ку. Ця операція часто використовуєть ся в підзапитах (див. п. 3.12).
[NOT] EXISTS
EXISTS (SELECT * FROM Laptop WHERE L.model = P.model)
Операція порівняння, що повертає значення TRUE, якщо підзапит повер тає хоча б одну стрічку (див. п. 3.12).
ALL, ANY, SOME
Model = ANY (SELECT Model FROM PC)
Ці операції порівняння повертають значення TRUE, якщо всі (ALL) або будь-які (ANY чи SOME) значення в підзапиті відповідають умові операції
Обчислювальні стовпці.
Конкатенація даних:
SELECT Surname + ' ' + Name + CAST(Rating AS NCHAR) AS FullName
FROM Students;
Оператор + виконує як математичне сумування, так і конкатена цію (сполучення) стрічок.
Функція CAST() здійснює явне приведення типів:
CAST(<вираз> AS <тип [(довжина)]>)
Математичні обчислення:
SELECT Surname, SQRT(Koef * Rating) / 100 AS Formula
FROM Students;
У формулах використовуються стандартні операції +, -, *, /, дужки ( ) для вказання порядку обчислення та математичні функції.
Додаткові функції для роботи зі стрічковими даними, матема тичні функції та функції для роботи з дани ми типу «дата та час» наведені в додатку до лабораторної роботи № 1.
Статистичні дані.
Основні статистичні функції:
COUNT (*)
повертає кількість наявних стрічок для вибра них даних;
COUNT (ім’я стовпця)
повертає кількість стрічок, що мають значен ня у вказаному стовпці, при цьому NULL-значення не враховуються;
SUM (ім’я стовпця)
повертає суму значень у вказаному стовпці;
AVG (ім’я стовпця)
повертає середнє значення для вказаного стовпця;
MIN (ім’я стовпця)
повертає мінім. значення у вказаному стовпці;
MAX (ім’я стовпця)
повертає макс. значення у вказаному стовпці.
Функції COUNT(), MIN(), MAX() працюють із довільними типами даних, а SUM() та AVG () лише із числовими стовпцями.
SELECT COUNT(*) num, COUNT(ID) numID, SUM(Rating) sumR,
AVG(Rating) avgR, MIN(Rating) minR, MAX(Rating) maxR
FROM Students WHERE Year >= 2007;
Виводити одночасно стовпці з даними та статистичну інформа цію не можна, тому що статистика виводиться лише одною стрічкою.
SELECT COUNT(DISTINCT Rating) numR, AVG(DISTINCT Rating) avgR
FROM Students ;
Вказання параметра DISTINCT у статистичних функціях дає можливість обробляти лише унікальні значення для стовпця.
Групова статистика.
Створення груп:
SELECT Groups, COUNT(*) num, MIN(Rating) minR, MAX(Rating) maxR
FROM Students
GROUP BY Groups;
Вираз GROUP BY вказує системі керування БД відсортувати та впорядку вати дані за вказаним стовпцем. Статистичні функції вико нують тут підрахунок для кожної групи окремо. У переліку стовпців оператора SELECT можуть бути присутніми лише стовпці, які наявні в переліку конструкції GROUP BY, а також статистичні функції.
Фільтрування груп:
SELECT Groups, COUNT(*) num
FROM Students
WHERE Rating >= 80
GROUP BY Groups
HAVING COUNT(*) >= 5
ORDER BY num DESC;
Вираз HAVING задає умову відбору для груп. Ця умова будується за такими ж правилами (табл. 3.1), що й для виразу WHERE, однак порівнюватися можуть лише значення для стовпців, що вказані у переліку GROUP BY, або для статистичних функцій.
Порядок дій сервера при відборі даних для SQL-запи ту такий:
здійснюється вибірка даних із таблиці Students для всіх стрічок, для яких задовольняється умова Rating >= 80;
далі, профільтровані в п.1 стрічки з даними сортуються та групу ються за стовпцем Groups;
для згрупованих даних виконується перевірка COUNT(*) >= 5; групи, що не задовольняють даній умові, відкидаються.
виводяться назви груп Groups та підраховане число стрічок num, що залишилися після виконання п.1, у порядку, вказаному в конструкції ORDER BY, тобто за спаданням кількості стрічок num для кожної групи.
Об’єднання даних із різних таблиць.
Декартовий добуток двох таблиць:
SELECT * FROM Roman, Zenoviy;
Просте перечислення таблиць у конструкції FROM відповідає реляцій ній операції декартового добутку, при якій кількість вибра них стрі чок є рівною добутку числа стрічок першої таблиці на число стрічок другої, тобто кожна стрічка з однієї таблиці поєднується з кожною стрічкою другої таблиці.
Декартовий добуток трьох таблиць:
SELECT * FROM Roman, Zenoviy, Mykolay;
Внутрішній добуток:
SELECT * FROM Roman AS R, Zenoviy AS Z
WHERE R.B = Z.B ;
Ця конструкція, як правило, відповідає реляційному об’єднання двох таблиць між якими встановлений зв’язок, що є реалізований за допомогою зовнішнього та потенційного ключів.
Технічно, результат запиту отримується таким чином:
виконується операція декартового добутку для таблиць Roman і Zenoviy, у результаті чого, отримуємо результуючу таблицю Roman х Zenoviy;
далі, для цієї таблиці здійснюється відбір лише тих стрічок, що задо вольняють вказаній умові в конструкції WHERE.
При написанні складних SQL-запитів для назв таблиць викорис товують короткі псевдоніми (у прикладі для таблиці Roman визначено псевдонім R, а для Zenoviy – Z). Коли псевдонім визначено, тоді слід використовувати лише його. Ключове слово AS можна опускати.
При використанні 2-х та більше таблиць у запиті для однакових назв стовпців, що зустрічаються в декількох таблицях, необхідно використовувати для їх позначення крапкову нотацію
<ім’я таблиці>.<ім’я стовпця>
При внутрішньому добутку 3-х таблиць, результат вибірки буде виконуватися аналогічно попередньому:
SELECT * FROM Roman R, Zenoviy Z, Mykolay M
WHERE R.B = Z.B AND Z.B = M.B ;
Об’єднання таблиці самої із собою:
Наприклад, необхідно вивести пари моделей, що мають одна кову ціну:
SELECT C1.Model Model1, C2.Model Model2, C1.Price
FROM Car C1, Car C2
WHERE C1.Price = C2.Price AND C1.Model > C2.Model ;
Тут взаємодіють між собою оригінальна таблиця та її дублікат, і тому їхнє перейменування є обов’язковим. Умова C1.Model > C2.Model використовується для того, щоб одинакові пари не вибиралися двічі.
Конструкція JOIN.
У виразі FROM може бути вказана явна операція з’єднання для двох та більше таблиць. Синтаксис конструкції JOIN має такий вигляд:
FROM <таблиця 1> { [INNER] | {LEFT | RIGHT | FULL} [OUTER] |
CROSS } JOIN <таблиця 2> [ON <умова з’єднання>]
[INNER] JOIN – внутрішнє з’єднання;
LEFT [OUTER] JOIN – ліве зовнішнє з’єднання;
RIGHT [OUTER] JOIN – праве зовнішнє з’єднання;
FULL [OUTER] JOIN – повне зовнішнє з’єднання;
CROSS JOIN – перехресне з’єднання;
Службові слова INNER та OUTER можуть бути опущені, оскіль ки зовнішнє з’єднання однозначно визначається його типом LEFT (ліве), RIGHT (праве) чи FULL (повне), а просто JOIN означає внутріш нє з’єднання.
Внутрішнє з’єднання [INNER] JOIN:
SELECT *
FROM Roman R JOIN Zenoviy Z ON R.B = Z.B ;
Отриманий результат є повністю ідентичним результату внут ріш нього добутку 2-х таблиць (див. п. 3.9). Основна відмінність полягає в тому, що відношення між двома таблицями є частиною конструкції FROM та позначене, як [INNER] JOIN, а умова для об’єд нання таблиць вказана за допомогою спеціального оператора ON, на противагу умові в конструкції WHERE.
Альтернативним по відношенню до синтаксису оператора INNER JOIN є синтаксис внутрішнього добутку 2-х таблиць:
SELECT * FROM Roman R, Zenoviy Z
WHERE R.B = Z.B ;
Ліве зовнішнє з’єднання LEFT [OUTER] JOIN:
SELECT *
FROM Roman R LEFT JOIN Zenoviy Z ON R.B = Z.B ;
При лівому зовнішньому з’єднанню в підсумкову таблицю ма ють бути включеними всі стрічки з «лівої» таблиці (розташованої перед оператором JOIN), незалежно від того, чи є для них відповідні стрічки в «правій» таблиці. Якщо для стрічок лівої таблиці є у відповідності стрічки з «правої» таблиці, тоді вони виводяться разом із ними, якщо відповідних стрічок у «правій» таблиці немає, тоді на їх місцях виводяться NULL-значення.
Альтернативним по відношенню до синтаксису оператора LEFT JOIN є синтаксис добутку таблиць з особливим знаком операції *=. За замов чуванням ця операція не підтримується (необхідно встановити рівень сумісності менший чи рівний 80).
SELECT * FROM Roman R, Zenoviy Z
WHERE R.B *= Z.B ;
Праве зовнішнє з’єднання RIGHT [OUTER] JOIN:
SELECT *
FROM Roman R RIGHT JOIN Zenoviy Z ON R.B = Z.B ;
Подібно, як і при лівому з’єднанню, при правому зовнішньому з’єднанню в підсумкову таблицю ма ють бути включеними всі стрічки з «правої» таблиці (розташованої після оператора JOIN), незалежно від того, чи є для них відповідні стрічки в «лівій» таблиці. Якщо для стрічок правої таблиці є у відповідності стрічки з «лівої» таблиці, тоді вони виводяться разом із ними, якщо відповідних стрічок у «лівій» таблиці немає, тоді на їх місцях виводяться NULL-значення.
Альтернативним по відношенню до синтаксису оператора RIGHT JOIN є синтаксис добутку таблиць з особливим знаком операції =*. За замов чуванням ця операція не підтримується (необхідно встановити рівень сумісності менший чи рівний 80).
SELECT * FROM Roman R, Zenoviy Z
WHERE R.B =* Z.B ;
Повне зовнішнє з’єднання FULL [OUTER] JOIN:
SELECT *
FROM Roman R FULL JOIN Zenoviy Z ON R.B = Z.B ;
Повні з’єднання можна вважати, у своєму роді, результатом су місного застосування лівого та правого з’єднань, тобто мають бути включені в результат усі стрічки з обох таблиць. Якщо стрічки з обох таблиць мають між собою певну відповідність, тоді вони виводяться разом, якщо ні – то із протилежного боку для них виводяться NULL-значення.
Перехресне з’єднання CROSS JOIN:
SELECT *
FROM Roman R CROSS JOIN Zenoviy Z ;
При перехресному з’єднанню 2-х таблиць для кожної стрічки з лівої таблиці ставиться у відповідність кожна стрічка із правої таблиці, тобто буде виведено стрічок таке число, яке рівне добутку кількості стрічок лівої таблиці на число стрічок правої таблиці.
Альтернативним по відношенню до синтаксису оператора CROSS JOIN є синтаксис декартового добутку 2-х таблиць (див. п.3.9):
SELECT * FROM Roman, Zenoviy;
Операція UNION.
Ця операція використовується для об’єднання 2-х запитів в один набір даних:
<запит 1>
UNION [ALL]
<запит 2>
GROUP BY <перелік стовпців>
Фактично, операція UNION не представляє собою з’єднання, оскільки вона більше нагадує спосіб додавання даних з одного запиту безпо середньо до кінця даних, отриманих за допомогою іншого запиту. Якщо є вказаним параметр ALL, тоді виводяться всі дублікати вихід них стрічок, у іншому випадку, у вихідному наборі залишаться лише унікальні стрічки. Таким чином, можна зв’язувати разом довільну кількість запитів, а за допомогою круглих дужок задавати порядок їхнього об’єднання.
На об’єднання накладаються ряд умов:
кількість вихідних стовпців та послідовність їхнього виведення за типами для всіх запитів мають бути ідентичними;
у кінцевому наборі з даними використовуються імена стовпців, що вказані в першому запиті;
конструкція GROUP BY може бути лише одна, та стосуватися всього об’єднання, і тому вона наводиться лише в кінці композитного запиту. Імена стовпців, за якими має виконувати ся сортування, мають співпадати з іменами відповідних стовпців першого запиту, у протилежному випадку, доцільно використо вувати порядкову нумерацію стовпців;
SELECT *
FROM Roman R JOIN Zenoviy Z ON R.B = Z.B
UNION
SELECT *
FROM Roman R JOIN Mykolay M ON R.B = M.B
ORDER BY 3 DESC;
Використання підзапитів.
У запитах підзапити відіграють роль джерел даних, які можуть бути розміщені в різних секціях оператора вибірки. Підзапити органі зовуються за допомогою звичайного оператора SELECT.
Підзапити в якості таблиць конструкції FROM:
SELECT A, C, Zen.B
FROM Roman R, (SELECT C, Z.B FROM Zenoviy Z ) AS Zen
WHERE R.B = Zen.B;
У результаті виконання підзапиту отримується нова віртуальна таблиця, для якої обов’язково має бути визначений псевдонім.
Підзапити в якості обчислювальних стовпців:
Нехай необхідно вивести для стрічок таблиці Roman кількість відповідних їм стрічок у таблиці Zenoviy
SELECT A, B,
(SELECT COUNT(*) FROM Mykolay M
WHERE M.B = R.B) AS num
FROM Roman R;
У цьому прикладі для кожної стрічки таблиці Roman викону ється підрахунок відповідних їм стрічок у таблиці Mykolay.
Підзапити в якості обчислювальних стовпців використовуються для отримання статистичних даних (кількість, мінімум, максимум, сума, середнє значення) з підлеглої таблиці.
Підзапити в якості списку даних для операції IN:
SELECT * FROM Mykolay
WHERE B IN (SELECT B FROM Roman);
У цьому випадку підзапит повинен повернути для операції IN лише один стовпець із даними. При вказанні в перелі ку стовпців підза питу SELECT більше одного стовпця – сервер видасть помилку.
SELECT * FROM Mykolay
WHERE B IN
(SELECT B FROM Roman WHERE Roman.A < Mykolay.D);
При опрацюванні кожної стрічки підсумкової таблиці, сформо ваної в конструкції FROM, на відповідність умові конструкції WHERE, дані цієї стрічки можуть бути використані в підзапиті. Іншими словами, підзапит може звертатися лише до даних поточної стрічки основного запиту.
Логічна операція EXISTS:
SELECT * FROM Roman
WHERE EXISTS
(SELECT * FROM Mykolay WHERE Roman.B = Mykolay.B);
Операція EXISTS приймає значення TRUE, якщо підзапит повер тає довільну кількість стрічок, якщо ж підзапит не повертає жодної стрічки – значення FALSE. Для NOT EXISTS – усе навпаки.
Як правило, операція EXISTS використовується в залежних під запитах. Цей вид запиту має посилання на зовнішні значення в головному запиті. Результат підзапиту може залежати від цих значень, і повинен оцінюватися окремо для кожної стрічки запиту, у якому міститься цей підзапит. Тому операція EXISTS може мати різні значення для кожної стрічки головного запиту.
Ключові слова SOME, ANY, ALL:
<вираз> {= | > | < | >= | <= | <> | != | !> | !<} SOME | ANY | ALL (<підзапит>)
Результатом підзапиту має бути один стовпець величин, який умовно позначимо вектором . Представимо попередній синтаксис для SOME | ANY та ALL у таких альтернативних видах:
cond = FALSE
для
cond = cond OR (<значення> < > )
cond = TRUE
для
cond = cond AND (<значення> < > )
Ключові слова SOME та ANY є синонімами, і тому несуттєво, який із них використовувати.
Як видно з альтернативного трактування роботи цих логічних конструк цій, то ANY поводить себе як при обчисленні логічної суми OR, а ALL – як при обчисленні логічної суми AND для всіх значень вектора . Якщо підзапит не поверне жодної стрічки, тобто , то результат буде початковим, тобто для ANY буде FALSE, а для ALL – TRUE.
SELECT * FROM Mykolay
WHERE B = ANY (SELECT B FROM Roman);
SELECT * FROM Mykolay
WHERE B <>ALL (SELECT B FROM Roman WHERE B IS NOT NULL);
У другому підзапиті ми відсікаємо всі пусті (NULL) значення, оскільки при порівнянні значень з NULL-значенням результатом буде UNKNOWN.
Зауваження: оператор =ANY є еквівалентним оператору IN, а оператор <>ALL є еквівалентним оператору NOT IN.
Оператор CASE.
Перевіряє перелік умов та повертає одне з можливих значень.
Оператор CASE має два формати:
А) Простий формат CASE для визначення результату порівнян ня певного виразу з набором простих значень.
CASE <вхідне значення >
WHEN <порівнюване значення 1> THEN <вихідне значення 1>
…
WHEN <порівнюване значення N> THEN <вихідне значення N>
[ELSE <вихідне значення>]
END
У заданому порядку порівнюється вхідне значення з порівнюва ними значеннями для кожного виразу WHEN.
Повертається вихідне значення найпершого виразу WHEN, для якого виконується умова: вхідне значення = порівнюване зна чення.
Якщо жодна умова не справджується, тоді повертається вихідне значення для ELSE або NULL, якщо таке значення для ELSE не вказано.
SELECT Model, Price,
CASE Model
WHEN 'M1' THEN 'Alfa Romeo Spider'
WHEN 'M3' THEN 'Audi TT Coupe'
WHEN 'M4' THEN 'Cadillac XLR'
ELSE 'модель не відома'
END AS ModelNote
FROM Car;
Б) Пошуковий формат CASE для визначення результату на основі набору логічних виразів.
CASE
WHEN <логічний вираз 1> THEN <вихідне значення 1>
…
WHEN <логічний вираз N> THEN <вихідне значення N>
[ELSE <вихідне значення>]
END
У заданому порядку обчислює логічні вирази для кожної конструкції WHEN.
Повертається вихідне значення найпершого виразу WHEN, для якого його логічний вираз повертає TRUE.
Якщо жоден логічний вираз не повертає TRUE, тоді повер тається вихідне значення для ELSE або NULL, якщо таке значення для ELSE не вказано.
SELECT Model, Price,
CASE
WHEN Price IS NULL THEN 'Нема в наявності'
WHEN Price <=60 THEN 'Дешево'
WHEN Price <=100 THEN 'Нормальна ціна'
ELSE 'Дорого'
END AS PriceNote
FROM Car;
Коментарі.
Одиничний коментар:
-- Вивід даних із таблиці Mykolay
SELECT * FROM Mykolay;
Даний вид коментарю починається двома символами дефісу –, а його дія продовжується тільки до кінця стрічки.
Блоковий коментар:
/*
SELECT * FROM Mykolay
WHERE B = ANY (SELECT B FROM Roman);
*/
SELECT * FROM Mykolay;
Сервер не бере до уваги значення тексту розміщеного між /*…*/ . Довжина коментарів при цьому не обмежується.
Таблиця 1. Завдання до лабораторної роботи
Варіант 12.
1. БД «Комп. фірма». Знайти модель, частоту процесора та об’єм жорсткого диску для тих комп’ютерів, що комплектуються накопичувачами 10 або 20 Мб та випускаються виробником 'A'. Вивести: model, speed, hd. Вихідні дані впорядкувати за зростанням за стовпцем speed.
2. БД «Фірма прий. вторсировини». З таблиці Outcome_o вивести всю інформацію за 14 число будь-якого місяця.
3. БД «Комп. фірма». Знайдіть виробників ПК із процесором не менше 600 МГц. Вивести: maker.
4. БД «Комп. фірма». Знайти виробників ПК, моделей яких немає в продажу (тобто відсутні в таблиці PC).
5. БД «Комп. фірма». Знайти тих виробників ПК, усі моделі ПК яких є в наявності в таблиці PC (використовуючи операцію EXISTS). Вивести maker.
6. БД «Кораблі». Вивести значення таблиці Ships із коментарями, наприклад, 'name: California', 'class: Tennessee', 'launched: 1921'.
7. БД «Комп. фірма». Знайти тих виробників ПК, для яких не всі моде лі ПК є в наявності в таблиці PC (використовувати засоби групової статистики). Вивести maker.
8. БД «Кораблі». Вкажіть назву та країну кораблів, що були потоплені в битвах, але лише для тих кораблів, для яких ця інформація є відо мою. Вивести: ship, country. (Підказка: використовувати підзапити в якості обчислювальних стовпців та перевірку на NULL)
9. БД «Комп. фірма». Для таблиці Product отримати підсумковий набір у вигляді таблиці зі стовпцями maker, pc, у якій для кожного вироб ника необхідно вказати, чи виробляє він ('yes'), чи ні ('no') відповідний тип продукції. У першому випадку ('yes') додатково вказати поруч у круглих дужках загальну кількість наявної (тобто, що знаходиться в таблиці PC) продукції, наприклад, 'yes(2)'. (Підказка: використовувати підзапити в якості обчислювальних стовпців та оператор CASE)
10. БД «Комп. фірма». Для кожної моделі продукції з усієї БД виведіть її найвищу ціну. Вивести: type, model, максимальна ціна. (Підказка: використовувати оператор UNION)
ДОДАТОК
1. БД «Комп’ютерна фірма»
Таблиця Product містить дані про виробника – maker, номер моделі – model та тип – type ('PC' – ПК, 'Laptop' – ноутбук, 'Printer' – принтер). Вважається, що номера моделей у таблиці Product є унікальними для всіх виробників та типів продуктів.
У таблиці PC для кожного ПК, що однозначно визначається унікальним кодом – code, є вказані: модель – model, швидкість – speed (процесор у мегагерцах), об’єм пам’яті – ram (у мегабайтах), розмір диску – hd (у гігабайтах), швидкість CD-приводу – cd (наприк лад, '4x') та ціна – price.
Таблиця Laptop є аналогічною таблиці PC за виключенням того, що замість швидкості CD-приводу містить розмір екрану – screen (у дюймах).
У таблиці Printer для кожної моделі принтера – model вказують ся мож ли вості кольору друку – color ('y' – для кольорових), тип принтера – type ('Laser'– лазерний, 'Jet' – струменевий, 'Matrix' – матричний) та ціна – price.
2. БД «Фірма прийому вторинної сировини»
Фірма має декілька пунктів для приймання вторинної сировини. Кожний пункт отримує гроші для їх видачі здавачам цієї сировини.
Відомості про отримання грошей на пунктах прийому запи суються в таблицю Income_o, первинний ключ якої є визначений за двома стовпцями: point та date. При цьому в стовпець date записується лише дата (без часу), тобто приймання грошей (inc) на кожному пункті відбувається не частіше одного разу на день.
Відомості про видачу грошей здавачам сировини записуються в таблицю Outcome_o, для якої також визначений первинний ключ за стовпцями point та date, що, у свою чергу, гарантує звітність кожного пункту про видані гроші (out) не частіше одного разу на день.
У випадку, коли прихід та розхід грошей може фіксуватися декілька разів на день, тоді використовується інша схема з таблицями, що мають первинний ключ code: Income та Outcome. Тут також значення стовпця date не містять часу.
3. БД «Кораблі»
Розглядається БД кораблів, що брали участь у другій світовій війні. Кораблі в «класах» побудовані за одним і тим ж проектом, а класу присвоюється, або ім’я першого корабля, побудованого за даним проектом, або назві класу дається ім’я проекту, що не співпадає з жодною назвою корабля. Корабель, що дав назву класу, називається головним.
Таблиця Classes містить ім’я класу – class, тип – type ('bb' для бо йового (лінійного) корабля або 'bc' для бойового крейсеру), країну, у якій побудовано корабель – country, кількість головних гармат – numGuns, калібр гармат – bore (діаметр жерла гармати в дюймах) та водотоннажність – displacement (вага в тоннах).
У таблиці Ships внесені назви кораблів – name, імена їхніх класів – class та роки спущення на воду – launched.
У таблицю Battles внесені назви (name) та дати (date) битв, у яких брали участь кораблі.
У таблицю Outcomes внесені результати участі кожного кораб ля в битві ('sunk' – потоплений, 'damaged' – пошкоджений, 'OK' – ці лий). Зауваження: у таблицю Outcomes можуть входити кораблі, що є відсутні в таблиці Ships.
4. БД «Аеропорт»
Таблиця Company містить ідентифікатор – ID _comp та назву компанії – name, що здійснює перевезення пасажирів.
Таблиця Trip містить інформацію про виконувані рейси: номер рейсу – trip_no, ідентифікатор компанії – ID_comp, тип літака – plane, місто відправлення – town_from, місто прибуття – town_to, час відправ лення – time_out та час прибуття – time_in.
Таблиця Passenger містить ідентифікатор – ID_psg та ім’я паса жира – name.
Таблиця Pass_in_trip містить інформацію про польоти: номер рейсу – trip_no, дата вильоту – date (день), ідентифікатор пасажира – ID_psg та місце – place, на якому він сидів під час польоту.
Зауваження: слід мати на увазі, що:
рейси виконуються щоденно, а тривалість польоту будь-якого рей су менша доби;
час та дати враховуються відносно одного часового поясу;
час відправлення та прибуття вказується з точністю до хвилини;
серед пасажирів можуть бути такі, що мають однакові прізвища (однакові значення поля name, наприклад, 'Bruse Willis');
номер місця в салоні – це число з літерою; число визначає номер ряду, а літера ('a' - 'd') місце в ряду зліва направо в алфавітному порядку, наприклад, '6d'.
Результат виконання роботи
/*1*/
select product.model,speed, hd from product,pc
where maker= 'A' and hd=10 or hd=20
order by speed asc
model speed hd
-------------------------------------------------- ------ -------------
1232 450 10
1233 450 10
1276 450 10
1298 450 10
1401 450 10
1408 450 10
1752 450 10
1232 500 10
1233 500 10
1276 500 10
1298 500 10
1401 500 10
1408 500 10
1752 500 10
1232 500 10
1233 500 10
1276 500 10
1298 500 10
1401 500 10
1408 500 10
1752 500 10
1121 750 20
1232 750 20
1233 750 20
1260 750 20
1276 750 20
1288 750 20
1298 750 20
1321 750 20
1401 750 20
1408 750 20
1433 750 20
1434 750 20
1750 750 20
1752 750 20
2111 750 20
2112 750 20
/*2*/
select * from Outcome_o
where datepart(day,date)=14
point date out
----- ----------------------- ---------------------
1 2001-03-14 00:00:00.000 15348,00
3 2001-09-14 00:00:00.000 2300,00
/*3*/
select maker,speed from product,pc
where speed >= 600 and product.model=pc.model
maker speed
---------- ------
B 750
B 600
B 600
A 750
A 900
/*4*/
Select maker from product,pc where product.model!=pc.model
Group by maker
maker
----------
A
B
C
D
E
/*5*/
select distinct maker from product where exists (select* from pc
where pc.model=product.model)
maker
----------
A
B
E
/*6*/
Select'name: '+cast(name as nchar) as 'name',
'class: '+cast(class as nchar) as 'class',
'launched: '+cast(launched as nchar) as 'launched'
from ships
name class launched
------------------------------------ ------------------------------------- ----------------------------------------
name: California class: Tennessee launched: 1921
name: Haruna class: Kongo launched: 1916
name: Hiei class: Kongo launched: 1914
name: Iowa class: Iowa launched: 1943
name: Kirishima class: Kongo launched: 1915
name: Kongo class: Kongo launched: 1913
name: Missouri class: Iowa launched: 1944
name: Musashi class: Yamato launched: 1942
name: New Jersey class: Iowa launched: 1943
name: North Carolina class: North Carolina launched: 1941
name: Ramillies class: Revenge launched: 1917
name: Renown class: Renown launched: 1916
name: Repulse class: Renown launched: 1916
name: Resolution class: Renown launched: 1916
name: Revenge class: Revenge launched: 1916
name: Royal Oak class: Revenge launched: 1916
name: Royal Sovereign class: Revenge launched: 1916
name: South Dakota class: North Carolina launched: 1941
name: Tennessee class: Tennessee launched: 1920
name: Washington class: North Carolina launched: 1941
name: Wisconsin class: Iowa launched: 1944
name: Yamato class: Yamato launched: 1941
/*7*/
select maker
from product
where type='PC'
and exists (select* from pc where product.model!=pc.model)
Group by maker
maker
----------
A
B
E
/*9*/
select distinct maker,'pc'pc,case when exists(select*from product where type='pc'
and P.maker=maker)
Then 'yes('+CAST((select count(*)from product where P.maker=maker and type='pc')
as nvarchar)+')' else 'no' end 'так чи ні'
from product P
maker pc так чи ні
---------- ---- -----------------------------------
A pc yes(2)
B pc yes(1)
C pc no
D pc no
E pc yes(3)
/*10*/
select 'laptop' as type,model,max(price)as 'максимальна ціна' from laptop
group by model
union
select 'pc' as type,model,max(price)as 'максимальна ціна' from pc
group by model
union
select 'printer' as type,model,max(price)as 'максимальна ціна' from printer
group by model
type model максимальна ціна
------- -------------------------------------------------- ---------------------
laptop 1298 1050,00
laptop 1321 970,00
laptop 1750 1200,00
laptop 1752 1150,00
pc 1121 850,00
pc 1232 600,00
pc 1233 980,00
pc 1260 350,00
printer 1276 400,00
printer 1288 400,00
printer 1401 150,00
printer 1408 270,00
printer 1433 270,00
printer 1434 290,00
Ділись своїми роботами та отримуй миттєві бонуси!
0%
Оголошення від адміністратора