10. Телефонная станция

Минимальный список характеристик:

• Номер абонента, фамилия абонента, адрес, наличие блокиратора, примечание;

• Код АТС, код района, количество номеров;

• Номер спаренного телефона абонента, задолженность, дата установки.

Один спаренный номер одной АТС может использоваться несколькими абонентами и один и тот же абонент может использовать телефоны разных АТС.

Выборки:

• Выбрать пары сблокированных телефонов.

SELECT Номера.Номер_телефона, Count(Номера.Абонент) AS [Count-Абонент] FROM Абоненты INNER JOIN Номера ON Абоненты.Номер_абонента = Номера.Абонент WHERE (((Абоненты.Блокиратор)=Yes)) GROUP BY Номера.Номер_телефона HAVING (((Count(Номера.Абонент))>1));

• Определить АТС, районы действия которых перекрываются.

Делаем запрос с группировкой по районам. Считаем, сколько АТС приходится на каждый район, если больше одной, то выбираем.

SELECT АТС.[Код района], Count(АТС.Код) AS [Count-Код] FROM АТС GROUP BY АТС.[Код района] HAVING (((Count(АТС.Код))>1));

В следующем запросе выбираем сами АТС. Создаем запрос из таблицы «АТС» и предыдущего запроса. Будем выбирать те записи, код района в которых равен коду района из запроса. И у нас получается список АТС с кодами районов.

SELECT АТС.Код, АТС.[Код района] FROM АТС RIGHT JOIN АТС1 ON АТС.[Код района] = АТС1.[Код района] WHERE (((АТС.[Код района])=[АТС1]![Код района]));

• Выбрать телефоны группового пользования, Вывести их номера и фамилии абонентов.

Это задание похоже на первое, но осложняется выбором ФИО. Поэтому сделаем его из двух запросов. В первом отберем записи с номерами групповых телефонов (число абонентов на один номер больше единицы).

SELECT Номера.Номер_телефона, Count(Абоненты.Номер_абонента) AS [Count-Номер_абонента] FROM Абоненты INNER JOIN Номера ON Абоненты.Номер_абонента = Номера.Абонент GROUP BY Номера.Номер_телефона HAVING (((Count(Абоненты.Номер_абонента))>1));

Во втором запросе покажем абонентов, чей номер равен номеру телефона из предыдущего запроса:

SELECT Номера.Номер_телефона, Абоненты.ФИО FROM Группа1, Абоненты INNER JOIN Номера ON Абоненты.Номер_абонента = Номера.Абонент WHERE (((Номера.Номер_телефона)=[Группа1]![Номер_телефона]));

• Выбрать список абонентов АТС 47, имеющих задолженность больше 100 руб.

SELECT Абоненты.ФИО, Номера.Задолженность FROM АТС INNER JOIN (Абоненты INNER JOIN Номера ON Абоненты.Номер_абонента = Номера.Абонент) ON АТС.Код = Номера.АТС WHERE (((Номера.Задолженность)>100) AND ((АТС.Код)=47));

11. Спорт

Минимальный список характеристик:

• Название вида спорта, единица измерения, мировой рекорд и его дата;

• ФИО спортсмена, год рождения, команд, спортивный разряд;

• Наименование соревнования, показанный результат, дата проведения, место проведения.

Один спортсмен может заниматься разными видами спорта.

Выборки:

• Вывести таблицу распределения мест в соревновании 'открытый чемпионат' в городе 'Киев' по 'шахматам' в 2000 г. SELECT спортсмен.фио, результаты.позиция FROM результаты INNER JOIN спортсмен ON результат.спортсмен_id = спортсмен.id INNER JOIN соревнование ON результаты.соревнование_id = соревнование.id INNER JOIN вид_спорта ON результаты.вид_спорта_id = вид_спорта.id WHERE соревнование.название = 'открытый чемпионат' and соревнование.место_проведения = 'Киев' and вид_спорта.название = 'шахматы' and YEAR(соревнование.дата) = 2000 ORDER BY результаты.позиция

• Определить спортсменов, которые выступают более чем в 3 видах спорта.

Считаем число умерших от кариеса и с помощью первого запроса высчитываем процент.

• Пациентов, которые болеют (болели) всеми болезнями.

SELECT DISTINCT Пациенты.ФИО FROM Пациенты INNER JOIN (Диагнозы INNER JOIN Лечение ON Диагнозы.Номер = Лечение.Диагноз1) ON Пациенты.Номер = Лечение.Пациент WHERE ((((SELECT COUNT ( Лечение.Диагноз1 ) FROM Лечение WHERE Лечение.Пациент = Пациенты.Номер ))=(SELECT COUNT ( Диагнозы.[Номер]) FROM Диагнозы )));

SELECT спортсмен.фио, COUNT(DISTINCT результаты.вид_спорта_id) as count FROM результаты INNER JOIN спортсмен ON результат.спортсмен_id = спортсмен.id INNER JOIN вид_спорта ON результаты.вид_спорта_id = вид_спорта.id GROUP BY результат.вид_спорта_id HAVING count > 3

• Вывести список спортсменов, превысивших мировые рекорды.

SELECT DISTINCT спортсмен.фио FROM результаты INNER JOIN спортсмен ON результат.спортсмен_id = спортсмен.id INNER JOIN вид_спорта ON результаты.вид_спорта_id = вид_спорта.id WHERE результаты.результат > вид_спорта.рекорд

• Определить наилучший показатель спортсмена 'Караваев' в виде спорта 'бег'.

SELECT спортсмен.фио, MAX(результаты.результат) AS лучший_результат FROM результаты INNER JOIN спортсмен ON результат.спортсмен_id = спортсмен.id INNER JOIN соревнование ON результаты.соревнование_id = соревнование.id INNER JOIN вид_спорта ON результаты.вид_спорта_id = вид_спорта.id WHERE вид.спорта = 'бег' and спортсмен.фио = 'Караваев' and GROUP BY спортсмен.id

12. Сельскохозяйственные работы

Минимальный список характеристик:

• Наименование сельскохозяйственного предприятия, дата регистрации, вид собственности, число работников, основной вид продукции, является ли передовым в освоении новой технологии, прибыль, примечание;

• Дата поставки, объем, себестоимость поставщика;

• Название продукции, единица измерения, закупочная цена.

Одно и то же предприятие может выпускать разные виды продукции и одна и та же продукция может выпускаться разными предприятиями.

Выборки:

• Вывести предприятия, являющиеся нерентабельными в текущем году.

Добавляю в 1 таблицу (Предприятие) атрибут ЗАТРАТЫ для запроса: 1

SELECT Название FROM Предприятие WHERE Прибыль < Затраты

• Определить, какого вида собственности предприятие является ведущим в поставке продукта 'банан'.

• select видсобственности from Предприятие where IDпредприятия IN (select ID_предприятия from Продукты WHERE Название = БАНАН)(изменено) and Введушийвтехнологиях = TRUE

• Определить объем дохода на одного работника в предприятии 'КАМАЗ'.

SELECT наименование, (прибыль/число_работников_работников ) AS “доход на работника” FROM предприятие

WHERE предприятие.наименование = “ Камаз”

• Вывести список продуктов, для которых закупочная цена, как правило, ниже себестоимости производителя.

SELECT продукт FROM продукция

INNER JOIN поставка ON продукция.поставкаID = поставка.поставкаID

WHERE продукция.закупочная_цена < поставка.себестоимость_производителя

13. Городской транспорт

Минимальный список характеристик:

• Вид транспорта, средняя скорость движения, количество машин в парке, стоимость проезда;

• номер маршрута, количество остановок в пути, количество машин на маршруте, количество пассажиров в день;

• начальный пункт пути, конечный пункт, расстояние.

Один и тот же вид транспорта может на разных маршрутах использовать разные пути следования.

Выборки:

• Определить оптимальный по времени маршрут между пунктами 'Холодная Гора' и 'Парк '.

Select Маршрут.Номер Маршрута, min(Путь.Расстояние) From Маршрут INNER JOIN Путь On Путь.МаршрутID = Маршрут. Маршрут ID

WHERE (НачальныйПунктПути = "Холодная Гора") and (Конечный пункт = "Парк")

Group By min(Путь.Расстояние)

• Определить среднее время ожидания на остановке троллейбуса №39.

Определить среднее время ождания (в таблице маршрут айди других сущностей должно быть) select count(*) AS СреднееВремяжидания from Машрут INNER JOIN Путь ON Путь.ID =Машрут.ПутьID INNER JOIN Транспорт ON Транспорт.ID =Машрут.ТранспортID WHERE (СреднееВремяОжидания = Путь.Расстояние / (Путь.Расстояние * Машрут.КоличествоМашинВМаршруте)) AND (Маршрут.НомерМаршрута = 39)

• Вывести маршруты трамваев в порядке убывания их протяженности.

select Маршрут.НомерМаршрута, Путь.Расстояние from Машрут INNER JOIN Путь ON Путь.ID =Машрут.ПутьID INNER JOIN Транспорт ON Транспорт.ID =Машрут.ТранспортID Where (Транспорт.ВидТранспорта = "Трамвай") GROUP BY Путь.Расстояние DESC;

• Вывести список ежедневных денежных поступлений для всех видов транспорта.

14. География

Минимальный список характеристик:

• Название страны, регион, столица, площадь территории, является ли страна развитой в экономическом отношении;

• количество населения,

• название национальности, язык, общая численность.

В одной стране могут присутствовать люди разной национальности.

Выборки:

• Считая, что государственным является язык, на котором разговаривает не менее 20% населения страны, выбрать список государственных языков страны 'Китай'.

SELECT SUM(Население.Язык)/SUM(Население.Численность)

FROM Страна

INNER JOIN Население ON Страна.ID = Население.Страна

Group By Население.Язык HAVING SUM(Население.Язык)/SUM(Население.Численность) > 0.2

• Выбрать численность населения по всем странам.

SELECT Население.Количество населения FROM Население

INNER JOIN Страна ON Население.Страна_ID = Страна.Страна_ID

• Определить столицу той страны, где проживает более всего представителей национальности 'светлый эльф'.

SELECT Страна.Столица FROM Страна

INNER JOIN Национальность ON Страна.Национальность_ID = Национальность.Национальность_ID

WHERE (Национальность.Название = "Светлый эльф"

• Выбрать список национальностей, проживающих в регионе 'Драконовы Горы'

SELECT Национальность.Название FROM Национальность

INNER JOIN Страна ON Национальность.Страна_ID = Страна.Страна_ID

WHERE (Страна.Регион = "Драконьи горы")

16. Аэропорт

Минимальный список характеристик:

• Номер самолета, тип, число мест, скорость полета;

• Номер маршрута, расстояние, пункт вылета, пункт назначения;

• Дата и время вылета, дата и время прилета, количество проданных билетов.

Один тип самолета может летать на разных маршрутах и по одному маршруту могут летать разные типы самолетов.

Выборки:

• Определить среднее расчетное время полета для самолета 'ТУ-154' по маршруту 'Чугуев' - 'Мерефа'.

• Выбрать марку самолета, которая чаще всего летает по тому же маршруту.

SELECT марка FROM самолет

• Выбрать маршрут/маршруты, по которым чаще всего летают рейсы, заполненные менее чем на 70%.

• Определить наличие свободных мест на рейс №870 31 декабря 2000 г.

select Самолёт.Число_Мест - Полёт.Количество_проданных_билетов AS Число_Свободных_Мест

from Полёт

inner join Самолёт on Самолёт.Номер_Самолёта = Самолёт.Номер_самолёта

inner join Маршрут on Полёт.Номер_Маршрута = Маршрут.Номер_маршрута

where Самолёт.Номер_Самолёта = 870 and Полёт.Дата = "31.12.2000"

17. Персональные эвм

Минимальный список характеристик:

• Фирма-изготовитель, название и место размещения фирмы;

• Тип процессора, тактовая частота, объем ОЗУ, объем жесткого диска, дата выпуска ПЭВМ;

• Фирма-реализатор: наименование, адрес, телефон;

• Объем партии рыночного предложения, цена партии.

Один тип персональной ЭВМ (ПЭВМ) может изготавливаться разными фирмами и одна и та же фирма может собирать разные типы ПЭВМ.

Выборки:

• Определить фирму, которая представляет самую новую модель на базе процессора 'Pentium-IV'.

SELECT изготовитель.название FROM изготовитель INNER JOIN изготовитель_эвм ON изготовитель_эвм.изготовитель_id = изготовитель.id INNER JOIN типы_эвм ON изготовитель_эвм.эвм_id = типы_эвм.id WHERE типы_эвм.тип_процессора = 'Pentium' and типы_эвм.дата_выпуска = (select MAX(дата_выпуска) FROM типы_эвм)