Порядок выполнения работы. Теоретические сведения

Теоретические сведения

Подзапрос – это выражение, приписывающее значение отношению.

Подзапрос порождает скалярное значение. Рассмотрим случай, когда подзапрос порождает скалярное значение –единственное значение атрибута. В этом случае в пункте WHERE происходит сравнение двух скалярных значений. При этом атрибут часто является ключом отношения. Для выполнения сравнения используются операторы > , < , = , >= , <= . Например используя подзапрос, выполним поиск режиссера спектакля “Macbeth” . Для этого используем отношения:

Performances(title, year, runningTime, type, theatreName, composer, producerC#)

Producers(name, address, cert#, networth)

В первом отношении содержатся названия спектаклей, а во втором – имена режиссеров. Запрос имеет следующий вид:

SELECT name

FROM Producers

WHERE cert#=

( SELECT Producer#

FROM Performances

WHERE title=’Macbeth’).

Рассмотрим случай, когда условие, указанное в пункте WHERE, выполняется сравнение кортежа, имеющего единственный атрибут, и унарного отношения. Отношение представляется множеством значений единственного атрибута. Кортеж t можно сравнивать с отношением R, при этом условие сравнения может содержать операторы > , < , = , >= , <= и ключевые слова IN,ANY,ALL.

Условие t IN R истинно тогда и только тогда, когда t совпадает с одним из кортежей R. Условие t NOT IN R истинно, когда t не совпадает ни с одним кортежем R и R – унарное отношение.

Условие t > ALL R истинно тогда и только тогда, когда t больше любого значения унарного отношения R. Условие t <> ALL R эквивалентно t NOT IN R.

Условие t > ANY R истинно тогда и только тогда, когда t больше по крайней мере одного из значений унарного отношения R. Условие t = ANY R эквивалентно t IN R.

Например, выполним запрос к отношениям:

Performances(title, year, runningTime, type, theatreName, composer, producerC#),

Producers(name, address, cert#, networth)

и определим имя и адрес режиссеров, поставивших в 1998 году спектакли в Мариинском театре:

SELECT name,address

FROM Producers

WHERE cert# IN

(SELECT producer#

FROM Performances

WHERE year=1998 AND theatre=’Mariinsky’);

Подзапрос порождает список скалярных значений атрибутов. Рассмотрим случай, когда условие, указанное в пункте WHERE, содержит кортеж, который представляется списком скалярных значений атрибутов, например (name,address). Кортеж t можно сравнивать с отношением R, если и кортеж и отношение имеют одинаковый список атрибутов, расположенных в одинаковом порядке. При этом условие может содержать операторы > , < , = , >= , <= и ключевые слова IN,ANY,ALL:

Условие t IN R истинно тогда и только тогда, когда t совпадает с одним из кортежей R. Условие t NOT IN R истинно, когда t не совпадает ни с одним кортежем R.

Условие t <> ALL R истинно тогда и только тогда, когда кортеж t не совпадает ни с одним кортежем из R. Это условие эквивалентно t NOT IN R.

Условие t <> ANY R истинно тогда и только тогда, когда в R есть хотя бы один кортеж, отличающийся от t .

Например, требуется найти все данные о спектаклях, в которых участвует актер Farukh Ruzimatov. Воспользуемся двумя отношениями:

Performances(title,year,runningTime,type,theatreName, composer,producerC#),

ActsIn(perfTitle, perfYear, perfTheatre, actorName).

Запишем запрос в виде:

(SELECT title,year,runningTime,type,theatreName, composer

FROM Performances

WHERE (title,theatre,year) IN

(SELECT perfTitle,perfTheatre,perfYear

FROM ActsIn

WHERE actorName=’Farukh Ruzimatov’);

Основной запрос просматривает последовательно кортежи (title,theatre,year) из отношения Performances и проверяет условие, содержатся ли они в полученном в отношении. При совпадении кортежей формируется результат запроса.

В подзапросах можно использовать ключевое слово EXISTS. При этом условие EXISTS R истинно, если R не пусто, и соответственно условие NOT EXISTS R истинно если R пусто. Например, запрос о спектаклях, в которых участвует актер Farukh Ruzimatov может быть записан с ключевым словом EXISTS:

(SELECT title,year,runningTime,type,theatreName,composer

FROM Performances

WHERE EXISTS

( SELECT *

FROM ActsIn

WHERE actorName=’Farukh Ruzimatov’

AND perfTitle=title AND thetreName=tneatre

AND perfYear=year);

При выполнении этого запроса сначала на основе отношения ActsIn формируется множество кортежей, содержащих названия спектаклей, в которых участвует Farukh Ruzimatov, затем из отношения Performances извлекается информация об этих спектаклях.

Порядок выполнения работы

Выполните следующие подзапросы типа к базе данных кораблей на языке SQL. Используйте подзапросы и операторы EXIST, IN, ALL, ANY.

1. Найдите страны, корабли которых имеют наибольшее число орудий.

2. Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.

3. Найдите названия кораблей с орудиями калибра 16 дюймов.

4. Найдите сражения, в которых участвовали корабли класса Конго.

5. Найдите названия кораблей, имеющих наибольшее число орудий среди всех кораблей такого же водоизмещения.

Выполните следующие подзапросы типа к базе данных компьютеров на языке SQL. Используйте подзапросы и операторы EXIST, IN, ALL, ANY.

1. Найдите производителей ПК со скоростью не менее 1600 МГц.

2. Найдите принтеры, имеющие самую высокую скорость.

3. Найдите лаптопы, скорость которых меньше скорости любого ПК.

4. Найдите номер модели любого типа продукта (ПК, лаптопа, принтера), имеющего самую высокую скорость.

5. Найдите производителей самых дешевых цветных принтеров.

6. Найдите производителей ПК с самым быстрым процессором среди всех ПК с наименьшим объемом RAM.

Приложение 1

База данных спектаклей

Performances

title	year	runningTime	type	theatre	composer	producerC#
Aida			perform	Mariinsky	Diuseppe Verdy
Don Carlos			perform	La Scala	Diuseppe Verdy
Don Pascuale			concert	Mariinsky	Gaetano Donizetti	null
Macbeth			perform	Mariinsky	Pyotr Tchaikovsky
Swan lake			perform	Mariinsky	Pyotr Tchaikovsky
Giselle			perform	Mariinsky	Adolphe Adam
Salome			perform	Mariinsky	Richard Strauss
Carmen			perform	Sarah Bernhart	George Bizet
Apollo			perform	Mariinsky	Igor Stravinsky
Otello			perform	La Scala	Diuseppe Verdy
Romeo and Juliet			perform	Covent Garden	Sergei Prokofiev
Arabella			perform	Covent Garden	Peter Mussbach

ActsIn

perfTitle	theatreName	perfYear	actorName
Don Carlos	La Scala		Olga Borodina
Carmen	La Canerenlola		Olga Borodina
Aida	Mariinsky		Olga Borodina
Aida	Mariinsky		Irina Bogacheva
Rigoletto	Bolshoy Theatre		Irina Bogacheva
Samson and Dalila	Mariinsky		Irina Bogacheva
Carmen	Mariinsky		Yury Marusin
La Traviata	Bolshoy Theatre		Yury Marusin
Swan Lake	Mariinsky		Ulyana Lopatkina
Giselle	Mariinsky		Ulyana Lopatkina
Giselle	Mariinsky		Farukh Ruzimatov
Apollo	Mariinsky		Farukh Ruzimatov
Otello	La Scala		Plassito Domingo
Otello	La Scala		Frivotti Nuggi

Actors

name	address	ampoule	gender	bithdate
Olga Borodina	Saint Petersburg Garden st.10/10	Singer	F	1972-02-02
Irina Bogacheva	Saint Petersburg Nevsky st.15/15	Singer	F	1950-01-01
Yury Marusin	Saint Petersburg Grate Sea st.15/15	Singer	M	1945-05-05
Ulyana Lopatkina	Saint Petersburg Small Sea st.25/25	Principal dancer	F	1975-05-05
Farukh Ruzimatov	Saint Petersburg Nevsky st.150/15	Principal dancer	M	1973-03-03
Plassito Domingo	Milan	Singer	M	1966-06-06

Producers

name	address	cert#	networth
Alexei Stepaniuk	Saint Petersburg Garden st.10/10
Yury Alexandrov	Saint Petersburg Lime av.15/15
David McVicar	London Old st. 12/2
Lev Ivanov	Saint Petersburg Palace quay 21
Jules Perrot	Paris Capuchin av.112
David Freeman	New York Fifth av.133
Rollan Petit	Paris Capuchin av.150
George Balanchine	Paris
Valery Gergiev	Saint Petersburg Theatre sq.1
Karlo Fontana	Milan
Kennet Macmillan	London Olg St 33
Peter Mussbach	London Liverpool st 17

Theatre

teatreName	artDirector	city	cert#
Mariinsky	Valery Gergiev	Saint Petersburg
La Scala	Karlo Fontana	Milan
Covent Garden	Russel Roberts	London
Bolshoy Theatre	Valery Borisov	Moscow
Grand Opera	Rudolf Nuriev	Paris

База данных кораблей второй мировой войны

Classes Battles

сlass	type	country	num Guns	bore	displ­ace­ment		name	date
Bismark	Bs	Germany					North Atlantic	24.2.41
Iowa	Bs	USA					Guadalcanal	15.11.42
Kondo	Bc	Japan					North Cope	26.12.43
North California	Bs	USA					Surigao Straiy	25.10.44
Renown	Bc	G Britain
Revenge	Bs	G Britain
Tennessee	Bs	USA
Yamato	Bs	Japan

Outcomes Ships

ship	battle	result		name	class	launched
Bismarck	North Atlantic	sunk		California	Tennessee
California	Surigao Strait	ok		Haruna	Kongo
Duke of York	North Cape	ok		Hiei	Kongo
Fuso	Surigao Strait	sunk		Iowa	Iowa
Hood	North Atlantic	sunk		Kirishima	Kongo
King George	North Atlantic	ok		Kongo	Kongo
Kirishima	Guadalcanal	sunk		Missouri	Iowa
Prince of Wales	North Atlantic	damaged		Musachi	Yamato
Rodney	North Atlantic	ok		New Jersey	Iowa
Schamhost	North Cape	sunk		North Carolina	North Carolina
South Dakota	Guadalcanal	damaged		Ramillies	Ravenge
Tennessee	Surigao Strait	ok		Renown	Renown
Washington	Guadalcanal	ok		Repulse	Renown
West Virginia	Surigao Strait	ok		Resolution	Revenge
Yamashiro	Surigao Strait	sunk		Revenge	Revenge
				Royal Oak	Revenge
				Royal Sovereign	Revenge
				Tennessee	Tennessee
				Washington	North Carolina
				Wisconsin	Iowa
				Yamato	Yamato

База данных компьютеров

Product		PC
Maker	Model	Type		Model	Speed	RAM	HD	CD	Price
A		PC						6x
A		PC						6x
A		PC						6x
B		PC						8x
B		PC						8x
B		Printer						8x
B		Printer						8x
C		PC						8x
C		PC						8x
D		PC						8x
D		PC
D		PC		Laptop
D		Laptop		Model	Speed	RAM	HD	Screen	Price
D		Laptop						9.5
D		Laptop						11.3
D		Printer						10.4
D		Printer						11.2
E		Laptop						11.2
E		Laptop						12.1
F		Laptop						12.1
G		Laptop						12.1
G		Laptop
H		Printer		Printer
I		Printer		Model	Color	Type	Price
					True	Ink-jet
					True	Ink-jet
					False	Laser
					False	Laser
					False	Ink-jet
					true	Laser