Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
Скачать 0.8 Mb.
|
Описание задания База данных книготорговой компании Рассмотрим простую предметную область жизнедеятельности, связанную с книгоизданием и маркетингом. В рамках данной предметной области существуют издатели, которые публикуют книги, авторы, которые книги пишут, и издания (сами книги). Разработана база данных pubs, определяющая описанную выше предметную область. Инфологическая модель предметной области с использованием диаграмм “сущность-связь” (ER-диаграмм) [1]), разработанных Ченом, представлена на рис. 1. На данном рисунке прямоугольниками обозначены типы сущностей (объектов), а ромбами - типы связей между сущностями. Атрибуты сущностей указаны мелким шрифтом в том же прямоугольнике, который отображает типы сущностей. Имя типа сущности отмечено в верхней части прямоугольника жирным шрифтом. Атрибуты связей в данном случае обозначены овалами. Как видно из рис. 1 у связи “Написана” имеется два атрибута: первый атрибут определяет порядок автора в названии книги, второй атрибут - гонорар автора книги. Б аза данных книготорговой компании (база данных pubs) включает три таблицы, определяющие сущности: таблица authors определяет авторов, таблица publishers - издателей, а таблица titles - сами книги. Четвертая таблица titleauthor задает отношение между таблицами titles и authors. Она показывает, какие авторы написали какие книги. Связь между таблицами titiles и publishers определяется столбцом pub_id в данных таблицах. Ниже представлены структуры используемых таблиц. Структура таблицы authors
Структура таблицы publishers
Структура таблицы titles
Структура таблицы titleauthor
В столбце 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” |