Материал: tbd2

ФЕДЕРАЛЬНОЕ АГЕНТСТВО СВЯЗИ

Ордена Трудового Красного Знамени

Федеральное государственное бюджетное образовательное учреждение высшего образования

МОСКОВСКИЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ СВЯЗИ И ИНФОРМАТИКИ

ФАКУЛЬТЕТ ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ

ЛАБОРАТОРНАЯ РАБОТА №2

по дисциплине «ТЕХНОЛОГИИ БАЗ ДАНЫХ»

Кафедра ИСУиА

Выполнил:

МОСКВА, 2018 г.

Цель работы

Построить таблицы, заполнить их информацией и сформировать по 2 запроса следующих типов:

• выборка всех данных;

• запрос с условием;

• запрос с использованием синонимов;

• с использованием подзапроса;

• с использованием сортировки;

• с использованием группировки;

• с использованием конкатенации;

• выборка неповторяющихся записей;

• с использованием агрегатных функций;

• с использованием NOT;

• с использованием WHERE.

Запросы сохранить в виде представлений

Выполнение.

Создадим таблицы, к которым будут написаны запросы, и заполним их записями. Для этого создадим файл Tables.sql со следующим содержанием:

CREATE TABLE FRESHMEN_STD(

id INT PRIMARY KEY,

name VARCHAR(20) NOT NULL,

surname VARCHAR(20) NOT NULL,

birthdate DATE NOT NULL,

pointsumm INT NOT NULL);

CREATE TABLE FAC_PTS(

id INT PRIMARY KEY,

name VARCHAR(10) NOT NULL,

min_pts INT NOT NULL);

INSERT INTO FRESHMEN_STD VALUES (1,'KIRILL','SIMONOV','04-08-1997',231);

INSERT INTO FRESHMEN_STD VALUES (2,'JOHN', 'SMITH','12-01-1997',220);

INSERT INTO FRESHMEN_STD VALUES (3,'OLOF', 'MEISTER','29-10-1997',181);

INSERT INTO FRESHMEN_STD VALUES (4,'MARIA', 'DISH','05-06-1998',238);

INSERT INTO FRESHMEN_STD VALUES (5,'FILTHY', 'ROBOT','05-11-1996',225);

INSERT INTO FRESHMEN_STD VALUES (6,'GOOD', 'GUY','30-12-1997',190);

INSERT INTO FRESHMEN_STD VALUES (7,'FELIX','KJELBERG','29-10-1997',399);

INSERT INTO FAC_PTS VALUES (1,'BST', 210);

INSERT INTO FAC_PTS VALUES (2,'BVT', 215);

INSERT INTO FAC_PTS VALUES (3,'IB', 190);

INSERT INTO FAC_PTS VALUES (4,'BAP', 200);

INSERT INTO FAC_PTS VALUES (5,'BRT', 215);

В ыведем результаты выполнения данных команд (рисунок 1):

Рисунок 1 – Таблицы FRESHMEN_STD и FAC_PTS

Напишем запросы, которые требуются по заданию. Для этого создадим файл Queries.sql и заполним его:

CREATE VIEW all_students AS

SELECT * FROM FRESHMEN_STD;

CREATE VIEW all_faculties AS

SELECT * FROM FAC_PTS;

CREATE VIEW john AS

SELECT * FROM FRESHMEN_STD WHERE NAME='JOHN';

CREATE VIEW good_pts AS

SELECT * FROM FRESHMEN_STD WHERE POINTSUMM > 230;

CREATE VIEW full_names AS

SELECT std_p.NAME, std_p.SURNAME FROM FRESHMEN_STD std_p;

CREATE VIEW bst_accepted AS

SELECT ID FROM FRESHMEN_STD WHERE POINTSUMM >= (SELECT MIN_PTS FROM FAC_PTS WHERE NAME='BST');

CREATE VIEW maria_accepted AS

SELECT NAME FROM FAC_PTS WHERE MIN_PTS<= (SELECT POINTSUMM FROM FRESHMEN_STD WHERE NAME='MARIA');

CREATE VIEW min_to_max AS

SELECT NAME FROM FAC_PTS ORDER BY MIN_PTS ASC;

CREATE VIEW std_age AS

SELECT NAME, SURNAME FROM FRESHMEN_STD ORDER BY BIRTHDATE;

CREATE VIEW birthdate_count AS

SELECT BIRTHDATE, COUNT(pointsumm) "POINTSUMM" FROM FRESHMEN_STD GROUP BY BIRTHDATE;

CREATE VIEW min_pts_count AS

SELECT MIN_PTS, COUNT(name) "NAME" FROM FAC_PTS GROUP BY MIN_PTS;

CREATE VIEW alot_of_points AS

SELECT 'Student HAS MORE THEN 220 POINTS:' || SURNAME "SURNAME" FROM FRESHMEN_STD WHERE POINTSUMM > 200;

CREATE VIEW pass_points AS

SELECT MIN_PTS || ' points is needed for faculties: ' || COUNT(NAME) "NAME" FROM FAC_PTS GROUP BY MIN_PTS;

CREATE VIEW distinct_birthdate AS

SELECT DISTINCT BIRTHDATE FROM FRESHMEN_STD;

CREATE VIEW deistinct_min_pts AS

SELECT DISTINCT MIN_PTS FROM FAC_PTS;

CREATE VIEW avg_pts AS

SELECT AVG(POINTSUMM) "POINTSUMM" FROM FRESHMEN_STD;

CREATE VIEW max_pts AS

SELECT MAX(min_pts) "MIN_PTS" FROM FAC_PTS;

CREATE VIEW not_leo AS

SELECT * FROM FRESHMEN_STD WHERE BIRTHDATE NOT BETWEEN '23-07-1997' AND '22-08-1997';

CREATE VIEW not_accepted AS

SELECT * FROM FRESHMEN_STD WHERE POINTSUMM IS NOT BETWEEN 190 AND 250;

Выполним данный файл и посмотрим результаты представлений (Рисунки 2-5):

Рисунок 2 – Результаты запросов

Рисунок 3 – Результаты запросов

Рисунок 4 – Результаты запросов

Р исунок 5 – Результаты запросов