Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007

• Название: Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
• Дата: 15.03.2022
• Размер: 0.8 Mb.
• Имя файла: sql (1).doc
• Тип: Методические указания; #397929
• страница: 2 из 11

Описание задания
База данных книготорговой компании
Рассмотрим простую предметную область жизнедеятельности,

связанную с книгоизданием и маркетингом. В рамках данной предметной области существуют издатели, которые публикуют книги, авторы, которые книги пишут, и издания (сами книги). Разработана база данных pubs, определяющая описанную выше предметную область. Инфологическая модель предметной области с использованием диаграмм “сущность-связь” (ER-диаграмм) [1]), разработанных Ченом, представлена на рис. 1.

На данном рисунке прямоугольниками обозначены типы сущностей (объектов), а ромбами - типы связей между сущностями. Атрибуты сущностей указаны мелким шрифтом в том же прямоугольнике, который отображает типы сущностей. Имя типа сущности отмечено в верхней части прямоугольника жирным шрифтом. Атрибуты связей в данном случае обозначены овалами. Как видно из рис. 1 у связи “Написана” имеется два атрибута: первый атрибут определяет порядок автора в названии книги, второй атрибут - гонорар автора книги.

Б аза данных книготорговой компании (база данных pubs) включает три таблицы, определяющие сущности: таблица authors определяет авторов, таблица publishers - издателей, а таблица titles - сами книги. Четвертая таблица titleauthor задает отношение между таблицами titles и authors. Она показывает, какие авторы написали какие книги. Связь между таблицами titiles и publishers определяется столбцом pub_id в данных таблицах.
Ниже представлены структуры используемых таблиц.
Структура таблицы authors

Имя столбца	Тип данных	Размерность	Возможность значений null	Содержательное описание
au_id	varchar	11	Нет	Идентификатор автора
au_lname	varchar	40	Нет	Фамилия автора
au_fname	varchar	20	Нет	Имя автора
phone	char	12	Нет	Номер телефона
address	varchar	40	Да	Адрес (улица, дом, квартира)
city	varchar	20	Да	Город проживания
state	char	2	Да	Штат проживания
zip	char	5	Да	Энергичность
contract	bit	1	Нет	Наличие контракта

Структура таблицы publishers

Имя столбца	Тип данных	Размерность	Возможность значений null	Содержательное описание
pub_id	char	4	Нет	Идентификатор издательства (издате-
pub_name	varchar	40	Да	ля)
city	varchar	20	Да	Название издательства (имя издателя)
state	char	2	Да	Город
country	varchar	30	Да	Штат

Структура таблицы titles

Имя столбца	Тип данных	Размерность	Возможность значений null	Содержательное описание
title id	varchar	6	Нет	Идентификатор книги
title	varchar	80	Нет	Название книги
type	char	12	Нет	Тип книги
pub_id	char	4	Да	Идентификатор издательства
price	money	8	Да	Цена
advance	money	8	Да	Аванс (стоимость предварительной продажи)
royalty	int	4	Да	Гонорар
ytd_sales	int	4	Да	Число книг, проданных в текущем году
notes	varchar	200	Да	Замечания
pubdate	datetime	8	Нет	Дата опубликования

Структура таблицы titleauthor

Имя столбца	Тип данных	Размерность	Возможность значений null	Содержательное описание
au_id	varchar	11	Нет	Идентификатор автора книги
title id	varchar	6	Нет	Идентификатор книги
au_ord	tinyint	1	Да	Порядок автора в названии книги
royaltyper	int	4	Да	Авторский гонорар

В столбце type таблицы titles используются следующие типы книг:

business - книги по бизнесу, mod_cook - книги по современной кулинарии, popular_comp - книги по компьютерной тематике, psychology – книги по психологии, trad_cook - книги по традиционной кулинарии, UNDECIDED - неопределенный тип книги.

В столбцах state таблиц authors и publishers используются следующие обозначения административных единиц США: CA - штат Калифорния, DC - округ Колумбия, IL - штат Иллинойс, IN - штат Индиана, KS -штат Канзас, MD - штат Мэриленд, MA - штат Массачусетс, MI – штат Мичиган, NY - штат Нью-Йорк, OR - штат Орегон, TN - штат Теннесси, TX - штатТехас, UT - штат Юта.

В столбце country таблицы publishers используются следующие

обозначения стран: France - Франция, Germany - Германия, USA - США.

Домен городов, используемый в таблицах authors и publishers,

включает города Ann Arbor, Berkeley, Boston, Chicago, Corvallis, Colevo,

Dallas, Gary, Lawrence, Menlo Park, Munchen, Nashville, New York, Oakland, Palo Alto, Paris, Rockville, Salt Lake City, San Francisco, San Jose, Vacaville, Walnul Creek, Washington.

В приложении 1 приведен полный пример базы данных pubs.
Лабораторные задания типа А
Дать содержательную интерпретацию SQL-запросам, выполнить их на SQL-сервере с использованием клиентских утилит Query Analyzerили SQL- EM, дать содержательную интерпретацию результатам выполнения SQL-запросов.
1) SELECT au_lname, au_fname

FROM authors
2) SELECT au_lname, au_fname

FROM authors

ORDER BY au_lname
3) SELECT au_lname, au_fname

FROM authors

ORDER BY au_lname, au_fname
4) SELECT title_id, price, ytd_sales,

price*ytd_sales “ytd dollar sales”

FROM titles

ORDER BY price*ytd_sales
5) SELECT title_id, price, ytd_sales,

price*ytd_sales “ytd dollar sales”

FROM titles

ORDER BY price*ytd_sales DESC
6) SELECT title_id, type, ytd_sales

FROM titles

ORDER BY type ASC, ytd_sales DESC
7) SELECT AVG(price)

FROM titles
8) SELECT DISTINCT type

FROM titles

ORDER BY type ACS
9) SELECT DISTINCT city

FROM authors

ORDER BY city DESC
10) SELECT DISTINCT state

FROM authors

ORDER BY state
11) SELECT DISTINCT country

FROM publishers

ORDER BY country DESC
12) SELECT AVG(price), AVG(DISTINCT price)

FROM titles
13) SELECT *

FROM titles
14) SELECT au_lname, au_fname

FROM authors

WHERE state= “CA”
15) SELECT type, title_id, price

FROM titles

WHERE price*ytd_sales < advance
16) SELECT au_id, city, state

FROM authors

WHERE state= “CA” OR city= “Palo Alto”
17) SELECT title_id, price

FROM titles

WHERE price between $5 AND $15
18) SELECT title_id, price

FROM titles

WHERE type IN (“mod_cook”, “trad_cook”, “business”)
19) SELECT au_lname, au_fname, city, state

FROM authors

WHERE city like “San%”
20) SELECT type, title_id, price

FROM titles

WHERE title_id like “B_2075”
21) SELECT type, title_id, price

FROM titles

WHERE title_id like “B[AUN]7832”
22) SELECT AVG(price) “AVG”

FROM titles

WHERE type= “business”
23) SELECT AVG(price) “avg” SUM(price) “sum”

FROM titles

WHERE type IN (“business”, “mod_cook”)
24) SELECT COUNT(*)

FROM authors

WHERE state= “CA”
25) SELECT COUNT(*)

FROM titles

WHERE LIKE “Co%s”
26) SELECT title

FROM titles

WHERE ytd_sales IS NULL
27) SELECT au_lname “Фамилия”, au_fname “Имя”

FROM authors

WHERE contract=1 AND phone LIKE “408____-__2_”
28) SELECT phone

FROM authors

WHERE address LIKE “%Broadway Av.%”
29) SELECT title, pubdate

FROM titles

WHERE pubdate>= “Jun 9 1991 12:00AM”

AND pubdate< “6/16/91”
30) SELECT type, AVG(price) “avg”, SUM(price) “sum”

FROM titles

WHERE type IN (“business”, “psychology”)

GROUP BY type
31) SELECT type, pub_id, AVG(price) “avg”, SUM(price) “sum”

FROM titles

WHERE type IN (“business”, “mod_cook”)

GROUP BY type, pub_id
32) SELECT type, AVG(price)

FROM titles

WHERE price>$11

GROUP BY type

HAVING AVG(price)>$19.7
33) SELECT au_id, COUNT(*)

FROM authors

GROUP BY au_id

HAVING COUNT(*)>1
34) SELECT type, MIN(price), MAX(price)

FROM titles

GROP BY type

ORDER BY type
35) SELECT type, MIN(price), MAX(price)

FROM titles

GROUP BY type

HAVING MAX(price)-MIN(price)>=3
36) SELECT state, COUNT(DISTINCT pub_id)

FROM publishers

GROUP BY state
37) SELECT pub_name, AVG(price) “дvg”,

COUNT(DISTINCT title_id) “count”

FROM titles t JOIN publishers p ON t.pub_id=p.pub_id

GROUP BY pub_name
38) SELECT type, (MIN(price)+MIN(price))/2, AVG(price)

FROM titles

GROUP BY type

HAVING type<> “UNDECIDED”

ORDER BY 2 DESC
39) SELECT type, MIN(pubdate), MAX(pubdate)

FROM titles

GROUP BY type
40) SELECT title, pub_name

FROM titles CROSS JOIN publishers
41) SELECT *

FROM titles, publishers
42) SELECT title, pub_name

FROM titles, publishers

WHERE titles.pub_id=publishers.pub_id
43) SELECT title, pub_name

FROM titles JOIN publishers
ON titles.pub_id=publishers.pub_id
44) SELECT *

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id
45) SELECT t.*, pub_name

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id
46) SELECT a.city, a.state

FROM authors a, publishers p

WHERE a.city=p.city AND a.state=p.state
47) SELECT au_lname, au_fname

FROM authors a JOIN titleauthor ON a.au_id=ta.au_id

JOIN titles t ON ta.title_id=t.title_id

WHERE au_lname LIKE “R%”

AND state IN (“CA”, “TX”, “NY”, “OR”, “UT”)

AND (title LIKE “_h_ %” OR title LIKE “% _h_ %”

OR title LIKE “% _h_”)
48) SELECT title, type

FROM authors a, titles t, titleauthor ta, publishers p

WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

AND t.pub_id=p.pub_id AND p.city=a.city
49) SELECT au_lname, au_fname, title

FROM authors a, titles t, titleauthor ta, publishers p

WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

AND t.pub_id=p.pub_id

AND ((p.country= ‘USA’ AND t.type=’popular_comp’)

OR (p.country=’France’ AND t.type=’psychology’))
50) SELECT au_lname, au_fname, city

FROM authors a, titles t, titleauthor ta

WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

AND (city LIKE “[CPR]%” OR city LIKE “%San%”)

AND (title LIKE “% the %” OR title LIKE “The %”

OR title LIKE “% a %” OR title LIKE “A %”)
51) SELECT DISTINCT au_lname, au_fname

FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id

JOIN titles t ON ta.title_id=t.title_id

JOIN publishers p ON p.pub_id=t.pub_id

WHERE p.state= “CA”

ORDER BY au_lname, au_fname
52) SELECT pub_name

FROM publishers p JOIN titles t ON p.pub_id=t.pub_id

WHERE $15>price AND type= “psychology”

ORDER BY pub_name
53) SELECT pub_name, AVG(price)

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id

GROUP BY pub_name
54) SELECT pub_name, AVG(price)

FROM titles t JOIN publishers p ON t.pub_id=p.pub_id

GROUP BY pub_name
55) SELECT au_lname, au_fname, title

FROM authors a, titles t, titleauthor ta

WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

AND type= “popular_comp”
56) SELECT au_lname, au_fname, title

FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id

JOIN titles t ON ta.title_id=t.title_id

WHERE type= “psychology”
57) SELECT au_lname, au_fname, pub_name, COUNT(*)

FROM authors a, titles t, titleauthor ta, publishers p

WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

AND t.pub_id=p.pub_id

GROUP BY au_lname, au_fname, pub_name
58) SELECT MIN(price)

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id

GROUP BY country

HAVING country=’USA’
59) SELECT pub_name, COUNT(*)

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id

AND (type= ‘mod_cook’ OR type=’trad_cook’)

GROUP BY pub_name
60) SELECT pub_name, COUNT(*)

FROM publishers p, titles t

WHERE p.pub_id=t.pub_id AND price>$15

GROUP BY pub_name

ORDER BY pub_name DESC
61) SELECT title, COUNT(DISTINCT a.au_id)

FROM titles t JOIN titleauthor ta ON t.title_id=ta.title_id

JOIN authors a ON ta.au_id=a.au_id

JOIN publishers p ON p.pub_id=t.pub_id

GROUP BY title
62) SELECT state, COUNT(DISTINCT p.pub_id)

FROM publishers p JOIN titles t ON p.pub_id=t.pub_id

GROUP BY state
63) SELECT title

FROM titles

WHERE pub_id=

(SELECT pub_id

FROM publishers

WHERE pub_name= “Binnet & Hardley”)
64) SELECT pub_name

FROM publishers

WHERE pub_id IN

(SELECT pub_id

FROM titles

WHERE type= “business”)
65) SELECT pub_name

FROM publishers p

WHERE EXISTS

(SELECT *

FROM titles t

WHERE p.pub_id=t.pub_id

AND type=“popular_comp”)
66) SELECT pub_name

FROM publishers p

WHERE NOT EXISTS

(SELECT *

FROM titles t

WHERE p.pub_id=t.pub_id

AND type=“mod_cook”)
67) SELECT pub_name

FROM publishers

WHERE pub_id NOT IN

(SELECT pub_id

FROM titles

WHERE type=“psychology”)
68) SELECT type, price

FROM titles

WHERE price < (SELECT AVG(price) FROM titles)
69) SELECT type, AVG(price)

FROM titles

GROUP BY type

HAVING AVG(price) < (SELECT AVG(price) FROM titles)
70) SELECT DISTINCT a.city, a.state

FROM authors a

WHERE NOT EXISTS

(SELECT *

FROM publishers p

WHERE a.city=p.city AND a.state=p.state)
71) SELECT DISTINCT p.city, p.state

FROM publishers p

WHERE NOT EXISTS

(SELECT *

FROM authors a

WHERE p.city=a.city AND p.state=a.state)
72) SELECT MIN(price)

FROM titles t

WHERE t.pub_id IN

(SELECT pub_id

FROM publishers

WHERE country=’USA’)
73) SELECT title, type, price

FROM titles

WHERE price>ALL

(SELECT price

FROM titles

WHERE type= “psychology”)
74) SELECT COUNT(DISTINCT city)

FROM publishers

WHERE pub_id IN

(SELECT pub_id

FROM titles

WHERE type= “psychology”)
75) SELECT pub_name

FROM publishers p

WHERE 15>SOME

(SELECT price

FROM titles t

WHERE p.pub_id=t.pub_id

AND type= “trad_cook”)
76) SELECT pub_name, state

FROM publishers

WHERE pub_id NOT IN

(SELECT pub_id

FROM titles)
77) SELECT title

FROM titles

WHERE pub_id NOT IN

(SELECT pub_id

FROM publishers)
78) SELECT title

FROM titles t

WHERE price>=

(SELECT AVG(price)

FROM titles tt, publishers pp

GROUP BY pub_id

HAVING t.pub_id=pp.pub_id)
79) SELECT au_lname, au_fname, price

FROM authors a, titles t, titleauthor ta, publishers p

WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

AND t.pub_id=p.pub_id AND country=’USA’

AND price=

(SELECT MIN(price)

FROM titles tt, publishers pp

WHERE tt.pub_id=pp.pub_id

GROUP BY country

HAVING country=’USA’)
80) SELECT DISTINCT au_lname, au_fname

FROM authors a, titles t, titleauthor ta

WHERE a.au_id=ta.au_id AND ta.title_id IN

(SELECT title_id

FROM titles

WHERE ytd_sales=

(SELECT MAX(ytd_sales)

FROM titles))
81) SELECT DISTINCT a.city, a.state

FROM authors a

WHERE NOT EXISTS

(SELECT *

FROM publishers p

WHERE a.city=p.city AND a.state=p.state)

UNION SELECT DISTINCT p.city, p.state

FROM publishers p

WHERE NOT EXISTS

(SELECT *

FROM authors a

WHERE p.city=a.city AND p.state=a.state)
82) SELECT title, price

FROM titles t JOIN publishers p ON t.pub_id=p.pub_id

WHERE p.country= “USA” AND t.price=

(SELECT MAX(price)

FROM titles tt JOIN publishers pp

ON tt.pub_id=pp.pub_id

WHERE country= “USA”)
83) SELECT pub_name, COUNT(*)

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id

GROUP BY pub_name

HAVING COUNT(*)>=ALL

(SELECT COUNT(*)

FROM titles tt, publishers pp

WHERE tt.pub.id=pp.pub_id

GROUP BY pub_name)
84) SELECT pub_name, city, state, country

FROM publishers p

WHERE EXISTS

(SELECT *

FROM titles t

WHERE t.pub_id=p.pub_id)

AND 20>ALL

(SELECT price

FROM titles t

WHERE t.pub_id=p.pub_id

AND price IS NOT NULL)
85) SELECT state, SUM(price)

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id

GROUP BY state

HAVING state NOT IN (“TN”, “MA”, “TX”)

AND SUM(price)>

(SELECT SUM(price)

FROM titles tt, publishers pp

WHERE tt.pub.id=pp.pub_id

AND pp.city= “Boston”)
86) SELECT pub_name, MIN(price)

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id

GROUP BY pub_name

HAVING MIN(price)>=ALL

(SELECT MIN(price)

FROM titles tt JOIN publishers pp

ON tt.pub_id=pp.pub_id

GROUP BY pub_name)
87) SELECT *

FROM publishers

WHERE pub_id IN

(SELECT pub_id

FROM titles

WHERE type= “psychology” AND pub_id IN

(SELECT pub_id

FROM publishers

WHERE country= “USA”

AND state<> “CA”)
88) SELECT au_lname, au_fname

FROM authors a

WHERE a.au_id IN

(SELECT au_id

FROM titleauthor ta

WHERE ta.title_id IN

(SELECT title_id

FROM titles t

WHERE “CA”=SOME

(SELECT state

FROM publishers p

WHERE p.pub_id=t.pub_id)))

ORDER BY au_lname, au_fname
89) SELECT state, COUNT(*)

FROM publishers p

WHERE EXISTS

(SELECT *

FROM titles t

WHERE p.pub_id=t.pub_id)

AND $22>ALL

(SELECT price

FROM titles t

WHERE p.pub_id=t.pub_id

AND price IS NOT NULL)

GROUP BY state

ORDER BY state ASC
90) SELECT state

FROM publishers p1

GROUP BY state

HAVING COUNT(DISTINCT pub_name)=

(SELECT COUNT(*)

FROM publishers p2

WHERE EXISTS

(SELECT *

FROM titles t

WHERE p2.pub_id=t.pub_id)

AND $22.5>ALL

(SELECT price

FROM titles t

WHERE p2.pub_id=t.pub_id

AND price IS NOT NULL)

GROUP BY state

HAVING p1.state=p2.state)
91) SELECT p1.pub_id

FROM titles t 1 , publishers p1

WHERE t1.pub_id=p1.pub_id

GROUP BY p1.pub_id

HAVING COUNT(DISTINCT title)=

(SELECT COUNT(*)

FROM titles t2

WHERE t2.pub_id=p1.pub_id

AND EXISTS

(SELECT *

FROM titleauthor ta3, authors a3

WHERE ta3.au_id=a3.au_id

AND ta3.title_id=t2.title_id

AND a3.state IN

(SELECT state

FROM publishers p4

WHERE “business”=SOME

(SELECT type

FROM titles t5

WHERE p4.pub_id= t5.pub_id))))
92) SELECT city, state

FROM authors

UNION SELECT city, state

FROM publishers

ORDER BY state, sity
93) SELECT city

FROM authors

UNION SELECT city

FROM publishers
94) SELECT state

FROM authors

UNION SELECT state

FROM publishers
95) SELECT city, state

FROM authors

WHERE state IS NOT NULL

UNION SELECT city, state

FROM publishers

WHERE state IS NOT NULL

ORDER BY city DESC, state ASC
96) SELECT state, MIN(price), MAX(price), AVG(price)

FROM authors a, titles t, titleauthor ta

WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

GROUP BY state

HAVING state<> “CA”