Подякувати Студентському архіву довільною сумою
Admin
26.02.2023 12:38
Дякуємо, що користуєтесь нашим архівом!
Інформація про навчальний заклад
ВУЗ:
Національний університет Львівська політехніка
Інститут:
Не вказано
Факультет:
ЗІ
Кафедра:
Не вказано
Інформація про роботу
Рік:
2024
Тип роботи:
Звіт до лабораторної роботи
Предмет:
Бази даних та знань
Частина тексту файла (без зображень, графіків і формул):
МІНІСТЕРСТВО ОСВІТИ УКРАЇНИ
НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ "ЛЬВІВСЬКА ПОЛІТЕХНІКА"
Звіт
до лабораторної роботи №2
Отримання даних та статистичної інформації з бази даних за допомогою запитів мовою T-SQL
з курсу :
“БАЗИ ДАНИХ ТА ЗНАНЬ” Варіант №9
ЛЬВІВ - 2017
Мета роботи :
Ознайомитися зі структурованою мовою запитів SQL на основі її діалекту Transact-SQL для СКБД Microsoft SQL Server 2005 та отримати навики написання SQL-запитів для здійснення вибірки даних та отримання статистичної інформації за допомогою інструмен тарію Management Studio.
Завдання до лабораторної роботи :
Варіант №9
1. БД «Комп. фірма». Знайти виробників принтерів. Вивести: maker, type. Вихідні дані впорядкувати за спаданням за стовпцем maker.
2. БД «Аеропорт». З таблиці Trip вивести інформацію про рейси, що вилітають в інтервалі часу між 12 та 17 годинами включно.
3. БД «Комп. фірма». Виведіть виробника, тип, модель та частоту про цесора для ноутбуків, частота процесорів яких перевищує 600 МГц. Вивести: maker, type, model, speed.
4. БД «Комп. фірма». Знайдіть виробників, що випускають одночасно ПК та ноутбуки (використати ключове слово ANY). Вивести maker.
5. БД «Кораблі». За Вашингтонським міжнародним договором від початку 1922 р. заборонялося будувати лінійні кораблі водотоннажніс тю понад 35 тис. тонн. Вкажіть кораблі, що порушили цей договір (враховувати лише кораблі з відомим роком спущення на воду, тобто з таблиці Ships). Виведіть: name, launched, displacement.
6. БД «Комп. фірма». Для таблиці PC вивести всю інформацію з ко ментарями в кожній комірці, наприклад, 'модель: 1121', 'ціна: 600,00'.
7. БД «Аеропорт». Визначіть кількість рейсів до міста 'Moscow' для кожної дати таблиці Pass_in_trip. Вивести: date, число рейсів.
8. БД «Комп. фірма». Для кожного значення швидкості ПК, що пере вищує 600 МГц, визначіть середню ціну ПК із такою ж швидкістю. Вивести: speed, середня ціна. (Підказка: використовувати підзапити в якості обчислювальних стовпців)
9. БД «Комп. фірма». Для таблиці Product отримати підсумковий набір у вигляді таблиці зі стовпцями maker, printer, у якій для кожного ви робника необхідно вказати, чи виробляє він ('yes'), чи ні ('no') відповід ний тип продукції. У першому випадку ('yes') додатково вказати по руч у круглих дужках загальну кількість наявної (тобто, що знаходить ся в таблиці Printer) продукції, наприклад, 'yes(2)'. (Підказка: вико ристовувати підзапити в якості обчислювальних стовпців та оператор CASE)
10. БД «Кораблі». Для кожного класу порахувати кількість кораблів, що входить до нього (врахувати також кораблі в таблиці Outcomes, яких немає в таблиці Ships). Вивести: class, кількість кораблів у класі. (Підказка: використовувати оператор UNION та операцію EXISTS)
БД «Комп’ютерна фірма»
Таблиця 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.
БД «Кораблі»
Розглядається БД кораблів, що брали участь у другій світовій війні. Кораблі в «класах» побудовані за одним і тим ж проектом, а класу присвоюється, або ім’я першого корабля, побудованого за даним проектом, або назві класу дається ім’я проекту, що не співпадає з жодною назвою корабля. Корабель, що дав назву класу, називається головним.
Таблиця Classes містить ім’я класу – class, тип – type ('bb' для бо йового (лінійного) корабля або 'bc' для бойового крейсеру), країну, у якій побудовано корабель – country, кількість головних гармат – numGuns, калібр гармат – bore (діаметр жерла гармати в дюймах) та водотоннажність – displacement (вага в тоннах).
У таблиці Ships внесені назви кораблів – name, імена їхніх класів – class та роки спущення на воду – launched.
У таблицю Battles внесені назви (name) та дати (date) битв, у яких брали участь кораблі.
У таблицю Outcomes внесені результати участі кожного кораб ля в битві ('sunk' – потоплений, 'damaged' – пошкоджений, 'OK' – ці лий). Зауваження: у таблицю Outcomes можуть входити кораблі, що є відсутні в таблиці Ships.
БД «Аеропорт»
Таблиця 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'.
Короткі теоретичні відомості :
Відомості про 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 )
Проста вибірка
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 забезпечує фільтрацію дублікатів стрі чок, що мають бути виведеними.
Створені SQL-запити до БД :
/*1. БД «Комп. фірма». Знайти виробників принтерів.
Вивести: maker, type.
Вихідні дані впорядкувати за спаданням за стовпцем maker.*/
SELECT DISTINCT maker, type
FROM Product
WHERE type = 'Printer'
ORDER BY maker DESC;
/*2. БД «Аеропорт». З таблиці Trip вивести інформацію про рейси,
що вилітають в інтервалі часу між 12 та 17 годинами включно.*/
SELECT time_out
FROM Trip
WHERE (DATENAME(hour, Trip.time_out) BETWEEN 12 AND 17)
AND NOT
((DATENAME(minute, Trip.time_out) > 0)
AND
(DATENAME(hour, Trip.time_out) IN('12','17')));
/*3. БД «Комп. фірма». Виведіть виробника, тип, модель
та частоту процесора для ноутбуків, частота процесорів яких перевищує 600 МГц.
Вивести: maker, type, model, speed.*/
SELECT maker, type,
(SELECT model
FROM Product
WHERE (Product.model=Laptop.model)) AS model,speed
FROM Product, Laptop
WHERE (Product.model = Laptop.model)AND(Laptop.speed >= 600);
/*4. БД «Комп. фірма». Знайдіть виробників,
що випускають одночасно ПК та ноутбуки (використати ключове слово ANY).
Вивести maker.*/
SELECT DISTINCT maker
FROM Product
WHERE (maker=ANY(SELECT maker FROM Product WHERE type='PC'))
AND type='Laptop';
/*5. БД «Кораблі». За Вашингтонським міжнародним договором від початку 1922 р.
заборонялося будувати лінійні кораблі водотоннажністю понад 35 тис. тонн.
Вкажіть кораблі, що порушили цей договір (враховувати лише кораблі
з відомим роком спущення на воду, тобто з таблиці Ships).
Виведіть: name, launched, displacement.*/
SELECT Name, Launched, Displacement
FROM Classes C, Ships S
WHERE C.class=S.class AND S.launched >= 1922 AND Displacement>35000
/*6. БД «Комп. фірма». Для таблиці PC вивести всю інформацію
з коментарями в кожній комірці, наприклад, 'модель: 1121', 'ціна: 600,00'.*/
SELECT ('Код : '+str(code,2)) AS code,
('Модель : '+str(model,4)) AS model,
('Швидкість : '+str(speed,3)) AS speed,
('ОЗП : '+str(ram,3)) AS ram,
('Жорсткий диск : '+str(hd,2)) AS hd,
('Оптичний привід : '+cd) AS cd,
('Ціна : '+str(price,3)) AS price
FROM PC;
/*7. БД «Аеропорт». Визначіть кількість рейсів до міста 'Moscow'
для кожної дати таблиці Pass_in_trip. Вивести: date, число рейсів.*/
SELECT date, COUNT(*) AS Число_рейсів
FROM Pass_in_trip
WHERE trip_no=ANY(SELECT trip_no FROM Trip WHERE town_to='moscow')
GROUP BY date;
/*8. БД «Комп. фірма». Для кожного значення швидкості ПК, що перевищує 600 МГц,
визначіть середню ціну ПК із такою ж швидкістю. Вивести: speed, середня ціна.
(Підказка: використовувати підзапити в якості обчислювальних стовпців)*/
SELECT speed,Avg(price) AS Середня_ціна
FROM PC
WHERE speed>'600'
GROUP BY speed;
/*9. БД «Комп. фірма». Для таблиці Product отримати підсумковий набір у вигляді таблиці зі стовпцями maker,
printer, у якій для кожного виробника необхідно вказати,
чи виробляє він ('yes'), чи ні ('no') відповідний тип продукції.
У першому випадку ('yes') додатково вказати поруч
у круглих дужках загальну кількість наявної
(тобто, що знаходиться в таблиці Printer) продукції, наприклад, 'yes(2)'.
(Підказка: використовувати підзапити в якості обчислювальних стовпців та оператор CASE)*/
SELECT maker,
CASE WHEN
(
SELECT COUNT(*)
FROM Product
WHERE type='printer'
GROUP BY maker
HAVING maker=P.maker
)IS NOT NULL THEN 'yes('+str((
SELECT COUNT(*)
FROM Product
WHERE type='printer'
GROUP BY maker
HAVING maker=P.maker
),1)+')'
ELSE 'no'
END AS Printer
FROM Product P
GROUP BY maker;
/*10. БД «Кораблі». Для кожного класу порахувати кількість кораблів,
що входить до нього (врахувати також кораблі в таблиці Outcomes,
яких немає в таблиці Ships).
Вивести: class, кількість кораблів у класі.
(Підказка: використовувати оператор UNION та операцію EXISTS)*/
SELECT class,
(SELECT COUNT(*)
FROM Ships
WHERE Ships.class=Classes.class
)AS Кількість_кораблів_у_класі
FROM Classes
WHERE EXISTS(SELECT * FROM Ships WHERE Ships.class=Classes.class)
UNION
SELECT class,
(SELECT COUNT(*)
FROM Outcomes
WHERE Classes.class = Outcomes.ship
) AS Кількість_кораблів_у_класі
FROM Classes
WHERE EXISTS(SELECT * FROM Outcomes WHERE Classes.class = Outcomes.ship)
AND NOT EXISTS(SELECT * FROM Ships WHERE Ships.class=Classes.class)
Результати виконання запитів до БД :
maker type
---------- ----------
E Printer
D Printer
A Printer
(строк обработано: 3)
time_out
-----------------------
1900-01-01 14:30:00.000
1900-01-01 16:20:00.000
1900-01-01 15:42:00.000
1900-01-01 12:00:00.000
1900-01-01 14:00:00.000
(строк обработано: 5)
maker type model speed
---------- ---------- ---------- ------
B Laptop 1750 750
A Laptop 1298 600
A Laptop 1752 750
(строк обработано: 3)
maker
----------
A
B
(строк обработано: 2)
Name Launched Displacement
-------------------- -------- ------------
Iowa 1943 46000
Missouri 1944 46000
Musashi 1942 65000
New Jersey 1943 46000
North Carolina 1941 37000
South Dakota 1941 37000
Washington 1941 37000
Wisconsin 1944 46000
Yamato 1941 65000
(строк обработано: 9)
code model speed ram hd cd price
-------- ------------- --------------- --------- ------------------ --------------------- ----------
Код : 1 Модель : 1232 Швидкість : 500 ОЗП : 64 Жорсткий диск : 5 Оптичний привід : 12x Ціна : 600
Код : 2 Модель : 1121 Швидкість : 750 ОЗП : 128 Жорсткий диск : 14 Оптичний привід : 40x Ціна : 850
Код : 3 Модель : 1233 Швидкість : 500 ОЗП : 64 Жорсткий диск : 5 Оптичний привід : 12x Ціна : 600
Код : 4 Модель : 1121 Швидкість : 600 ОЗП : 128 Жорсткий диск : 14 Оптичний привід : 40x Ціна : 850
Код : 5 Модель : 1121 Швидкість : 600 ОЗП : 128 Жорсткий диск : 8 Оптичний привід : 40x Ціна : 850
Код : 6 Модель : 1233 Швидкість : 750 ОЗП : 128 Жорсткий диск : 20 Оптичний привід : 50x Ціна : 950
Код : 7 Модель : 1232 Швидкість : 500 ОЗП : 32 Жорсткий диск : 10 Оптичний привід : 12x Ціна : 400
Код : 8 Модель : 1232 Швидкість : 450 ОЗП : 64 Жорсткий диск : 8 Оптичний привід : 24x Ціна : 350
Код : 9 Модель : 1232 Швидкість : 450 ОЗП : 32 Жорсткий диск : 10 Оптичний привід : 24x Ціна : 350
Код : 10 Модель : 1260 Швидкість : 500 ОЗП : 32 Жорсткий диск : 10 Оптичний привід : 12x Ціна : 350
Код : 11 Модель : 1233 Швидкість : 900 ОЗП : 128 Жорсткий диск : 40 Оптичний привід : 40x Ціна : 980
(строк обработано: 11)
date Число_рейсів
----------------------- ------------
2003-04-01 00:00:00.000 3
2003-04-13 00:00:00.000 1
2003-04-14 00:00:00.000 2
(строк обработано: 3)
speed Середня_ціна
------ ---------------------
750 900,00
900 980,00
(строк обработано: 2)
maker Printer
---------- -------
A yes(3)
B no
C no
D yes(2)
E yes(1)
(строк обработано: 5)
class Кількість_кораблів_у_класі
-------------------- --------------------------
Bismarck 1
Iowa 4
Kongo 4
North Carolina 3
Renown 3
Revenge 4
Tennessee 2
Yamato 2
(строк обработано: 8)
Висновки :
Ознайомився з основами мови запитів до БД Transcat-SQL. Для заданих баз даних написав SQL-запити для одержання необхідної інформації. Роботу коду перевірив у середовищі Microsoft SQL Server Management Studio Express.
21.05.2017 16:05-
Ділись своїми роботами та отримуй миттєві бонуси!
0%
Оголошення від адміністратора