Інформаційні системи та їх роль в управлінні економікою

SELECT  name FROM grp WHERE  id <6088 OR id >9000  або  SELECT  name FROM grp WHERE  id NOT  BETWEEN 6088 AND  9000

 

 

5) щоб вибрати  назви факультетів  для яких значення поля f_name є непорожнім  слід використати наступну інструкцію 

SELECT  name FROM inst  WHERE  f_name IS NOT NULL

 

 

6) щоб вибрати  всю інформацію  про групи 3 і 4  курсів без тих, що мають назви ФО-31,ФО-32 чи ФО-33 слід використати наступну інструкцію 

SELECT  *  FROM grp WHERE  curs IN (4,5) AND  name NOT  IN ("ФО-31","ФО-32" , "ФО-33")

 

 

7) щоб вибрати  ПІБ та табельний номер всіх студентів 1-го курсу слід використати наступну інструкцію 

SELECT  name, id   FROM stud WHERE grp IN (SELECT id FROM grpWHERE curs =1)

 

 

8) щоб вибрати поля id та name для студентів, що мають прізвище "Ковальчук"слід використати наступну SQL-інструкцію 

SELECT id, name  FROM stud  WHERE name LIKE "Ковальчук *"

 

 

9) щоб вибрати  поле name для студентів, що мають  ім"я "Луїза",   слід виконати наступний запит

SELECT  name  FROM stud  WHERE name LIKE "* Луїза *"  ORDER BY 1

 

 

10) щоб вибрати  всю інформацію про студентів, 3-я літера в прізвищі яких  є - Ж , а остання -К , слід виконати наступнмй запит 

SELECT  * FROM stud  WHERE name LIKE  "??Ж*К *"

 

 

6.6  Приклади використання  SQL для вибірки даних з декількох таблиць

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

1)Наприклад, щоб вибрати назву групи, її ідентифікатор, а також форму навчання і факультет в одну таблицю слід виконати наступний запит

Для виконання операції такого роду в операторі SELECT після ключового слова FROM вказується список таблиць, в яких  буде здійснюватися пошук відповідних даних. Після ключового слова WHERE вказується умова, по якій буде проводиться злиття. Для того, щоб виконати цей запит, потрібно дати команду:

SELECT grp.name, grp.id,  formed.name, inst.name FROM grp  , formed  , inst WHERE  grp.fed = formed.id AND grp.inst = inst.id

2) а тепер вибираємо таку  ж інформацію лише для груп 3-го курсу

SELECT grp.name, grp.id,  formed.name, inst.name FROM grp  , formed  , inst  WHERE  grp.fed = formed.id AND grp. inst = inst.id  AND grp.curs =3

3) іноді не дуже зручно писати довгі імена таблиць, тоді можна використати їх аліаси(клички, скорочення), наприклад

SELECT a.name, a.id,  b.name, c.name FROM grp a  , formed b , inst i c   WHERE  a.fed = b.id AND a.inst = c.id  AND a.curs =3

4) а ще краще так.Якщо назва поля є унікальною серед таблиць, які об"єднуються у запиті, то перед ним назви таблиці можна не вказувати. Але початківцям цього робити не рекомендується.  

SELECT g.name, g.id,  f.name, i.name FROM grp g , formed f , inst i i   WHERE  fed = f.id AND inst = i.id  AND curs =3

5) щоб вибрати унікальні назви форм навчання та факультетів на яких навчаються студенти з прізвищем РАТУШНЯК слід виконати наступну SQL-інструкцію 

SELECT  DISTINCT  f.name, i.name FROM grp g , formed f , inst i i, stud s   WHERE  fed = f.id AND inst = i.id  AND grp =g.id AND s.name LIKE "Ратушняк *"

 

 

6.7.  Обчислення всередині SELECT

SQL дозволяє виконувати різні арифметичні операції над стовпцями результуючої таблиці. У конструкції <спісок_полів> можна використовувати константи, функції та їх комбінації з арифметичними операціями і дужками. 

1)Наприклад, щоб дізнатися рік початку навчання для кожної з груп слід виконати наступний запит, якщо на дворі весна :

SELECT  DISTINCT  .name, YEAR(DATE())-curs FROM grp

 

 

Як правило  подібних SQL-функцій у  більшості СКБД доволі небагато - в межах 20-ти.Виключення складає хіба що Access, де їх не менше 50. Серед найбільш вживаних

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

YEAR(<дата>) -рік

MONTH (<дата>) -місяць

DAY(<дата>) -день у місяці

TRIM(<назва текстового поля чи рядкова константа>) -видяляє початкові та кінцеві пробіли з рядка 

2) щоб  вибрати ПІБ студентів та довжину цього поля, для яких ця довжина більша 35 слід використати наступну інструкцію:

SELECT  .name, LENGTH(name) FROM stud WHERE    LENGTH(name) > 35

У SQL також визначені і так звані агрегуючі  функції, які здійснюють дії над сукупністю однакових полів у групі записів. Серед них: 
AVG (<ім'я поля>) - середня по всім значенням даного поля 
COUNT (<ім'я поля>) або COUNT (*) - кількість записів 
MAX (<ім'я поля>) - максимальне з усіх значень даного поля 
MIN (<ім'я поля>) - мінімальне з усіх значень даного поля 
SUM (<ім'я поля>) - сума всіх значень даного поля 
Слід враховувати, що кожна агрегуюча функція повертає єдине значення.  

 

 

 3) щоб підрахувати середнє значення  табельного номера усіх студентів слід використати наступну інструкцію:

SELECT  AVG(id) FROM stud

 

  

4) щоб підрахувати кількість студентів, що навчаються на заочній формі навчання слід використати наступну інструкцію:

SELECT  .COUNT(*) FROM stud s, grp g , formed f WHERE s.grp =grp.id AND g.fed =f.id AND f.name="заочна"

 

  

 

6.8.  Групування даних  

 

Групування даних  в операторі SELECT здійснюється за допомогою  ключового слова GROUP BY та ключового  слова HAVING, за допомогою якого задаються  умови розбиття записів на групи.

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

1) Визначимо для прикладу кількість студенських груп  у кожному з факультетів: 

SELECT  COUNT(*) , i.name FROM  grp g , inst i WHERE g.inst = i.id  GROUP BY i.name

 

 

Kлючевое слово  HAVING працює наступним чином: спочатку GROUP BY розбиває рядка на групи,  потім на отримані набори накладаються  умови .

2) Наприклад, усунемо з попереднтого прикладу ті факультети/філії, що мають менше 15 груп 

 

SELECT  COUNT(*) , i.name FROM  grp g , inst i WHERE g.inst = i.id  GROUP BY i.name HAVING COUNT(*) >=15

 

 

3) Вибрати назви  факультетів та груп, у яких  навчаються понад 40 студентів

SELECT  i.name , g.name , COUNT(*)  FROM  grp g , inst i, stud sWHERE g.inst = i.id  AND s.grp =g.id GROUP BY i.name, g.name HAVING COUNT(*) >40

 

  

 

6.9.  Сортування даних  

 

Для сортування даних, що отримуються за допомогою оператора SELECT служить ключове слово ORDER BY. З його допомогою можна сортувати результати по будь-якому стовпцю або виразу, зазначеному в <списку_вибору>. Дані можуть бути впорядковані як за зростанням так і за спаданням. 

1) Відсортувати назви факультетів у порядку зростання

SELECT  name FROM inst ORDER BY name або  SELECT  name FROM inst ORDER BY 1

 

 

2) Вибрати відсортований  список груп у порядку спадання  та ПІБ студентів у них в  порядку зростання

SELECT g.name  , s name  FROM grp g , stud s  WHERE s.grp = g.id ORDER BY 1 DESC , s.name

 

  

 

6.10.  Операція об"єднання

У SQL передбачена можливість здійснення вибірки інформації з різних відношень(таблиць), які є результатами оператора SELECT, у один список  .Природно, ці відносини повинні бути визначені по одній схемі.

1) Вивести у один список поля id та name  з таблиць inst та formed 

SELECT id, name FROM inst   UNION  SELECT id, name FROM formed

 

 

2) Вибрати з  таблиці grp ідентифікатори груп  б їх назви та рік початку  навчання 

 

SELECT id , name , YEAR(DATE()) -curs  AS yr  WHERE MONTH(DATE() ) < 9 UNION SELECT id , name , YEAR(DATE()) -curs+1 AS yr  WHERE MONTH(DATE() )  >8

 

  

 

6.11.  Виставкові таблиці  

 

До цих пір ми говорили про таблиці, які реально зберігаються в базі даних. Це, так звані, базові таблиці (base tables). Існує інший вид таблиць, що отримав назву "виставкових" (іноді їх називають "віртуальними таблицями" чи представленнями).

Представлення (view) - це таблиця, вміст якої береться з інших таблиць за допомогою запиту. При цьому нові копії даних не створюються

Коли вміст базових таблиць змінюється, СУБД автоматично перевиконує запити, що створюють view, що призводить до відповідними до змін у представленнях. 
Представлення визначається за допомогою команди

CREATE VIEW <назва_представлення> [<назва_столбця>,...] AS <запит> 

При цьому повинні дотримуватися наступні обмеження: 
представлення повинне базуватися на єдиному запиті (UNION не припустимо) 
вихідні дані запиту, що формує уявлення, повинні бути не впорядковані (ORDER BY не припустимо) 
Створимо виставкову таблицю , що зберігає інформацію про студентів та назву їх груп, факультетів та форм навчання

:

CREATE VIEW studPlace AS  SELECT stud.id , stud.name  AS studName , grp.name AS  grpName , formed.name  AS fedName , inst.name AS instName   FROM  grp , stud ,inst , formed  WHERE  stud.grp = grp.id AND grp.inst = inst.id  AND grp.fed =formed.id

Тут використана ще одна, раніше не описана, можливість SQL - присвоєння нових імен стовпцями представлення. Зауважимо, що якщо ми хочемо присвоїти нові імена стовпців подання, потрібно вказувати імена для всіх стовпців. Тип даних стовпця виставкової таблиці та її нульовий статус завжди залежать від того, як він був визначений у базовій таблиці (таблицях).

Запит на вибірку даних до представлення виглядає абсолютно аналогічно запиту до будь-якої іншої таблиці. Однак на зміну даних у представленні  накладаються обмеження. Коротко про них можна сказати наступне:

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

Якщо подання засновано більше ніж на одній таблиці, то зміни даних в ньому не допускаються, тому що в більшості випадків СКБД не може правильно відновити схему базових таблиць зі схеми подання. 
Видалення подання проводиться за допомогою оператора: 
    DROP VIEW <імя_представлення> 

6.12. Команди  модифікації даних 

 

До цієї групи відносяться оператори  додавання, заміни та видалення записів. 
Додати новий запис у таблицю: 
INSERT INTO <назва_таблиці> [(<назва_поля>, <назва_поля >,...)] 
VALUES (<значення>, <значення >,..)

Список стовпців в цiй команді не є обов'язковим параметром, адже список полів включений у квадратні дужки. У випадку, коли список буде пропущений, повинні бути вказані значення для всіх полів таблиці в тому порядку, як ці стовпці були створені,наприклад:

INSERT INTO grp VALUES ( 11001, "МММ-11", 197 ,5, 1) 

Приклад з вказанням списку полів 

INSERT INTO stud (name, grp ) VALUES ( "Петренко Петро Петрович", 9613) 

 

Модифікація записів: 
     UPDATE <назва_таблиці> SET <назва_поля> = <значення >,... 
         [WHERE <умова>] 
Якщо задано ключове слово WHERE і умова, то команда UPDATE застосовується тільки до тих записів, для яких воно виконується. Якщо умову не задано, UPDATE застосовується до всіх записів. Приклад: 

 

UPDATE  stud  SET grp = 11001 WHERE grp=9999 

 

Як умови можуть використовуватися такі ж логічні  вирази як і при вибірці інформації за допомогою оператора SELECT.

Видалення записів 
    DELETE FROM <назва_таблиці> [WHERE <умова>]

Видаляються всі записи, що задовольняють вказаній умові. Якщо ключове слово WHERE і умова відсутня, з таблиці видаляються всі записи. Приклад:

DELETE FROM grp WHERE curs > 5 
 

 

6.13.  Інші можливості SQL(серверні процедури, тригери) 

 

Збережені процедури. Практичний досвід створення додатків обробки даних показує, що ряд операцій над даними, що реалізують спільну для всіх користувачів логіку і не пов'язаних з призначеним для користувача інтерфейсом, доцільно винести на сервер. Однак, для написання процедур, що реалізують ці операції стандартних можливостей SQL не достатньо, оскільки тут необхідні оператори обробки розгалужень, циклів і т.д. Тому багато постачальників СУБД пропонують власні процедурні розширення SQL (PL / SQL компанії Oracle та інших). Ці розширення містять логічні оператори (IF ... THEN ... ELSE), оператори переходу за умовою (SWITCH ... CASE ...), оператори циклів (FOR, WHILE, UNTIL) і оператори предачі управління у процедури (CALL, RETURN). За допомогою цих засобівстворюються функціональні модулі, які зберігаються на сервері разом з базою даних. Зазвичай такі модулі називають збереженими процедурами. Вони можуть бути викликані з передачею параметрів будь-яким користувачем, які мають на те відповідні права. У деяких системах збережені процедури можуть бути реалізовані і у вигляді зовнішніх по відношенню до СУБД модулів на мовах загального призначення, таких як C,  Pascal тощо. Ось приклад створення такої процедури для СКБД INFORMIX:

CREATE  PROCEDURE <назва_процедури> ( список назв і типів параметрів)