Для решения следующих задач (по вариантам) записать операторы SQL и проверить их работоспособность в среде СУБД Access. Список студентов с указанием номера варианта см. в конце задания.
Условие для вариантов 1-3
Даны четыре таблицы. В них хранится информация о поставщиках (Поставщик), деталях (Деталь), проектах (Проект), а также о количестве деталей, поставляемых конкретным поставщиком для некоторого проекта (Заказ).
С помощью соответствующих операторов SQL создайте таблицы, проиндексируйте их, заполните данными.
Содержимое таблицы Поставщик
Np
Name_Post
Status
City
S1
Smith
London
S2
Jones
Paris
S3
Black
Paris
S4
Clark
London
S5
Adams
Athens
Содержимое таблицы Деталь
Nd
Name_Detail
Color
Width
P1
Nut
Red
P2
Bolt
Green
P3
Screw
Blue
P4
Screw
Red
P5
Cam
Blue
P6
Cog
Red
Содержимое таблицы Проект
Npr
Name_Project
City
J1
Sorter
Paris
J2
Penuche
Rome
J3
Reader
Athens
J4
Console
Athens
J5
Collator
London
J6
Terminal
Oslo
J7
Tape
London
Содержимое таблицы Заказ
Np
Nd
Npr
Number
S1
P1
J1
S2
P3
J1
S3
P3
J1
S2
P3
J2
S2
P5
J2
S3
P4
J2
S5
P2
J2
S5
P6
J2
S2
P3
J3
S4
P6
J3
S1
P1
J4
S2
P3
J4
S5
P2
J4
S5
P1
J4
S5
P3
J4
S5
P4
J4
S5
P5
J4
S5
P6
J4
S2
P3
J5
S5
P5
J5
S2
P3
J6
S2
P3
J7
S4
P6
J7
S5
P5
J7
Выполните выборку данных из созданных таблиц в соответствии с вариантом.
Вариант 1.
1) получить полные сведения о проектах с 3-его по 5-й
2) найти номер детали, имеющей наименьшее значение веса
3) найти поставщиков (наименования), выполняющих поставки для проекта J1
4) найти поставщиков (номера), выполняющих поставки детали P1 для проекта J1
5) найти детали (номера), поставляемые для Лондонских проектов
6) найти проекты (названия), для которых поставки выполняет поставщик S1
7) вычислить значение общего числа проектов, снабжаемых поставщиком S5
8) сделать все необходимые изменения для случая, когда определенное количество деталей P1, поставляемых для проекта J1 поставщиком S1, должно теперь поставляться поставщиком S2
9) добавить запись (“P7”, “Washer”,”Grey”, 1) и (“P8”, “Screw”, “Yellow”, 2) в таблицу Деталь
10) удалить данные обо всех синих деталях в таблице Деталь и соответствующие записи в таблице Заказ
11) удалить все таблицы и их индексы
Вариант 2.
1) получить полные сведения обо всех проектах в Лондоне
2) определить цвет деталей, поставляемых поставщиком S1
3) найти поставщиков (номера), выполняющих поставки как для проекта J1, так и для проекта J2
4) найти поставщиков (номера), выполняющих поставки красных деталей для Парижских и Лондонских проектов
5) найти детали (наименования), поставляемые для любого проекта поставщиком, находящемся в том же городе
6) найти проекты (названия), получающие более одной детали от поставщика S5
7) вычислить значение общего количества деталей P2, поставляемых поставщиком S5
8) изменить цвет всех красных деталей на оранжевый
9) добавить запись (“J8”, “Writer”, ”Moscow”) и (“J9”, “Screen”, “Minsk”) в таблицу Проект
10) удалить данные обо всех оранжевых деталях в таблице Деталь и соответствующие записи в таблице Заказ
11) удалить все таблицы и их индексы
Вариант 3.
1) получить полные сведения о проекте “Reader”
2) найти проекты (названия), для которых не поставляется ни одной красной детали поставщиками из Лондона
3) найти поставщиков (номера), поставляющих одну и ту же деталь для всех проектов
4) найти проекты (номера), полностью снабжаемые поставщиком S2
5) найти те детали (номера), которые поставляются для всех проектов в Лондоне
6) найти поставщиков (наименования), поставляющих синие детали для проектов в Рим
7) для каждой детали, поставляемой для некоторого проекта, получить ее номер, номер проекта и значение общего количества данной детали в проекте
8) изменить название проекта J6 на «VIDEO»
9) добавить запись (“S6”, “Anna”, 10, “Moscow”) и (“S7”, “Alex”, 20, “Minsk”) в таблицу Поставщик
10) удалить данные обо всех красных деталях в таблице Деталь и соответствующие записи в таблице Заказ
11) удалить все таблицы и их индексы
Условие для вариантов 4-6
Даны три таблицы, составляющие базу данных строительной компании «Премьер». В них хранится информация о рабочих компании (Рабочий), зданиях, в которых данная компания производит различные виды работ (Здание) и графиках выполнения этих работ (График).
С помощью соответствующих операторов SQL создайте таблицы, проиндексируйте их, заполните данными.
Содержимое таблицы Рабочий
Id рабочего
ФИО
Почасовая ставка
Специальность
Id начальника
Филимонов А.А.
130р.
Электрик
Кузнецов Б.Б.
160р.
Электрик
Никонов П.П.
140р.
Штукатур
Романов В.В.
120р.
Штукатур
Горшков Г.Г.
100р.
Кровельщик
Белов С.С.
80р.
Плотник
Морозов К.К.
170р.
Плотник
Содержимое таблицы Здание
Id здания
Адрес
Тип
Уровень сложности
Ул. Осиновая, 1213
Офис
Ул. Березовая, 1011
Офис
Ул. Вязов, 123
Жилой дом
Ул. Кленовая, 456
Магазин
Ул. Буковая, 1415
Склад
Ул. Дубовая, 789
Жилой дом
Содержимое таблицы График
Id рабочего
Id здания
Дата начала работы
Количество дней
10 октября 2014 г.
17 октября 2014 г.
8 октября 2014 г.
23 октября 2014 г.
1 декабря 2000 г
15 ноября 2014 г.
1 октября 2014 г.
15 октября 2014 г.
8 декабря 2014 г.
5 ноября 2014 г.
30 октября 2014 г.
9 октября 2014 г.
10 ноября 2014 г.
28 октября 2014 г.
5 октября 2014 г.
8 октября 2014 г.
27 октября 2014 г.
10 октября 2014 г.
24 октября 2014 г.
Выполните выборку данных из созданных таблиц в соответствии с вариантом.
Вариант 4.
1) получить полные сведения о графиках работ
2) найти все работы, которые начинаются в течение ближайших трех недель. Подсказка: Current_Date – встроенная функция, возвращающая текущую дату
3) перечислить рабочих, которые назначены на все здания офисов
4) найти все работы, выполненные в октябре
5) определить на каких работах в ноябре был занят плотник Белов С.С.?
6) каково среднее число дней работы плотников на здании офисов?
7) определить количество заработанных денег кровельщиком Горшковым Г.Г. за октябрь-декабрь 2014 г.
8) повысить на 10% зарплату рабочим, работавшим в октябре на зданиях офисов
9) добавить сведения о ещё двух работниках со специальностью плиточник и почасовой ставкой 150р.
10) уволить всех плотников
11) удалить все таблицы и их индексы
Вариант 5.
1) получить полные сведения о графиках работ продолжительностью более двух недель
2) перечислить рабочих, назначенных в октябре 2014 года на здания офисов с указанием фамилий их начальников
3) для каждого начальника выяснить среднюю почасовую ставку среди его подчиненных
4) определить тип зданий, по которым работы начали проводиться в декабре
5) у каких рабочих почасовая ставка выше среднего?
6) перечислить здания, на которые в октябре-ноябре 2014 г. назначены представители всех специальностей
7) определить максимальное количество дней по графику плотника Морозова К.К.
8) увеличить продолжительность работ в жилых домах на один день
9) добавить сведения об ещё одном магазине по ул. Майской, 4534 со 2-м уровнем сложности
10) в таблице Здание удалить данные обо всех магазинах, а в таблице График – все работы, связанные с этими объектами
11) удалить все таблицы и их индексы
Вариант 6.
1) получить полные сведения о графиках работ продолжительностью менее недели
2) перечислить фамилии рабочих, чьи почасовые ставки больше, чем ставки их начальников
3) перечислить здания уровня сложности 1, на которые еще не назначено ни одного электрика
4) выяснить ФИО начальников, руководивших работами в магазине
5) определить, кто из рабочих не был занят в декабре?
6) указать среднее количество дней, затрачиваемые на выполнение работ 3-й степени сложности
7) для начальников, у которых более двух подчиненных, выяснить максимальную почасовую оплату среди его подчиненных
8) у всех штукатуров изменить специальность на штукатур-маляр
9) назначить бригаду электриков в составе А.А.Филимонова и Б.Б.Кузнецова на работы в новом здании магазина по улице Вязов, 13 с 1.04.14 на две недели
10) уволить электрика А.А.Филимонова
11) удалить все таблицы и их индексы
Результат
В контрольной работе привести структуру (макеты) и содержимое всех таблиц, схему данных, скрипты всех операторов SQL и результаты их работы (для SELECT). Контрольная работа сдаётся на проверку в распечатанном и электронном видах (на e-mail: tberezenceva@mail.ru).