Проектирование базы данных "Цирк" (Контрольная работа № 2)

Контрольная работа № 2. ИТЭ. 2006/7 весна

Таблица АртистЦирка

КодАртиста	Фамилия	ГодРождения	Город
1	Гудини	1950	Лондон
2	Кио	1960	Воронеж
3	Попов	1957	Ленинград
4	Запашный	1970	Ленинград

Таблица Животное

КодЖивотного	Вид	Кличка	Вес
1	пантера	Багира	150
2	пантера	Кики	160
3	собака	Рекс	30
4	лев	Цезарь	180
5	лев	Брут	175,5
6	бегемот	Тото	500
7	крокодил		0

Таблица «Номер»

КодАртиста	КодЖивотного	Город	Название	Жанр
1	1	Париж	Кольцо Огня	Дрессура
1	3	Париж	побег	Дрессура
2	1	Мадрид	Индия	Дрессура
3	2	Дрезден	Сакура	пантомима
3	3	Воронеж

1.  Самый молодой артист, который работает с пантерами.

SELECT TOP 1 АртистЦирка.Фамилия, АртистЦирка.ГодРождения, Животное.Вид

FROM Животное INNER JOIN (АртистЦирка INNER JOIN Номер ON АртистЦирка.КодАртиста = Номер.КодАртиста) ON Животное.КодЖивотного = Номер.КодЖивотного

WHERE (((Животное.Вид)="пантера"))

ORDER BY АртистЦирка.Фамилия DESC;

Фамилия	ГодРождения	Вид
Попов	1957	пантера

2.  С какими артистами работает самое маленькое животное?

А)    SELECT АртистЦирка.Фамилия, Животное.Вид, Животное.Кличка, Min(Животное.Вес) AS [Min-Вес]

FROM Животное INNER JOIN (АртистЦирка INNER JOIN Номер ON АртистЦирка.КодАртиста = Номер.КодАртиста) ON Животное.КодЖивотного = Номер.КодЖивотного

GROUP BY АртистЦирка.Фамилия, Животное.Вид, Животное.Кличка;

Фамилия	Вид	Кличка	Min-Вес
Гудини	пантера	Багира	150
Гудини	собака	Рекс	30
Кио	пантера	Багира	150
Попов	пантера	Кики	160

Б)  SELECT top 1 АртистЦирка.Фамилия, Животное.Вид, Животное.Кличка, Животное.Вес

FROM Животное INNER JOIN (АртистЦирка INNER JOIN Номер ON АртистЦирка.КодАртиста = Номер.КодАртиста) ON Животное.КодЖивотного = Номер.КодЖивотного

ORDER BY Животное.Вес;

Фамилия	Вид	Кличка	Вес
Попов	собака	Рекс	30
Гудини	собака	Рекс	30

3.  Создать таблицу, которая содержит данные о животных семейства кошачьих.

SELECT Животное.Вид, Животное.Кличка, Животное.Вес

FROM Животное

WHERE (((Животное.Вид) in ("пантера","лев", "тигр", "кошка")));

Вид	Кличка	Вес
пантера	Багира	150
пантера	Кики	160
лев	Цезарь	180
лев	Брут	175,5

4.  Вывести список всех видов животных.

А)  SELECT  Животное.Вид  FROM Животное;

Вид
пантера
пантера
собака
лев
лев

Б)  SELECT distinct  Животное.Вид  FROM Животное;

Вид
лев
пантера
собака

5.  Составить список всех городов, упоминаемых в БД.

SELECT АртистЦирка.Город

FROM АртистЦирка

UNION select Номер.Город  from Номер;

Город
Воронеж
Дрезден
Ленинград
Лондон
Мадрид
Париж

6.  Составить таблицу, которая содержит следующие данные: минимальный, средний, максимальный вес животного и общее количество животных.

SELECT Min(Животное.Вес) AS [Min-Вес], Avg(Животное.Вес) AS [Avg-Вес], Max(Животное.Вес) AS [Max-Вес], Count(Животное.КодЖивотного) AS [Count-КодЖивотного]

FROM Животное;

Min-Вес	Avg-Вес	Max-Вес	Count-КодЖивотного
30	139,1	180	5

7.  Слово «гиппопотам» заменить на «бегемот».

UPDATE Животное SET Животное.Вид = "бегемот"

WHERE (((Животное.Вид)="гиппопотам"));

8.  Создать перекрестный запрос

TRANSFORM Count(Номер.Название) AS [Count-Название]

SELECT Номер.Город, Count(Номер.Название) AS [Всего номеров]

FROM АртистЦирка INNER JOIN Номер ON АртистЦирка.КодАртиста = Номер.КодАртиста

GROUP BY Номер.Город

PIVOT АртистЦирка.Фамилия;

Город	Всего номеров	Гудини	Кио	Попов
Воронеж	0			0
Дрезден	1			1
Мадрид	1		1
Париж	2	2

9.  Удалить все номера, в которых участвуют крокодилы.

DELETE Номер.*, Животное.Вид

FROM Животное INNER JOIN Номер ON Животное.КодЖивотного = Номер.КодЖивотного

WHERE (((Животное.Вид)="крокодил"));

КодАртиста	КодЖивотного	Город	Название	Жанр	Вид
1	7	Милан	Нильские воды	мистерия	крокодил