Язык SQL

Имя столбца	Тип данных	Размерность	Возможность значений 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. 

 

Лабораторные задания

Часть 1

Дать содержательную интерпретацию SQL-запросам, выполнить их на SQL-сервере с использованием клиентской утилиты SQL Query Analyzer. 

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) "avg",

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 t1, 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