Автор работы: Пользователь скрыл имя, 19 Декабря 2012 в 11:37, лабораторная работа
Цель работы: изучить принципы работы с базой данных, изучить спецификации запроса языка баз данных SQL, получить практические навыки составления и содержательной интерпретации запросов выборки данных (операторов SELECT), а также их выполнения с использованием СУБД MS Access.
Цель работы: изучить принципы работы с базой данных, изучить спецификации запроса языка баз данных SQL, получить практические навыки составления и содержательной интерпретации запросов выборки данных (операторов SELECT), а также их выполнения с использованием СУБД MS Access.
Задание.
Тип А: Дать содержательную интерпретацию SQL-запросам, выполнить их с помощью СУБД MS Access, дать содержательную интерпретацию результатам выполнения SQL-запросов.
Тип Б: Составить SQL-запросы по их заданному содержательному описанию, выполнить SQL-запросы на SQL-сервере с использованием клиентских утилит ISQL/w или SQL-EM, проинтерпретировать результаты выполнения запросов.
Вариант:
11 |
7,24,36,43,69,76,79,88 |
5,13,35,39,54,69,82,94 |
Исходные таблицы:
Таблица authors
au_id |
au_lname |
au_fname |
phone |
address |
city |
state |
contract |
172-32-1176 |
White |
Johnson |
408 496-7223 |
10932 Bigge Rd. |
Menlo Park |
CA |
1 |
213-46-8915 |
Green |
Marjorie |
415 986-7020 |
309 63rd St. #411 |
Oakland |
CA |
1 |
238-95-7766 |
Carson |
Cheryl |
415 548-7723 |
589 Darwin Ln. |
Berkeley |
CA |
1 |
267-41-2394 |
O'Leary |
Michael |
408 286-2428 |
22 Cleveland Av. #14 |
San Jose |
CA |
1 |
274-80-9391 |
Straight |
Dean |
415 834-2919 |
5420 College Av. |
Oakland |
CA |
1 |
341-22-1782 |
Smith |
Meander |
913 843-0462 |
10 Mississippi Dr. |
Lawrence |
KS |
0 |
409-56-7008 |
Bennet |
Abraham |
415 658-9932 |
6223 Bateman St. |
Berkeley |
CA |
1 |
427-17-2319 |
Dull |
Ann |
415 836-7128 |
3410 Blonde St. |
Palo Alto |
CA |
1 |
472-27-2349 |
Gringlesby |
Burt |
707 938-6445 |
PO Box 792 |
Covelo |
CA |
1 |
486-29-1786 |
Locksley |
Charlene |
415 585-4620 |
18 Broadway Av. |
San Francisco |
CA |
1 |
527-72-3246 |
Greene |
Morningstar |
615 297-2723 |
22 Graybar House Rd. |
Nashville |
TN |
0 |
648-92-1872 |
Blotchet-Halls |
Reginald |
503 745-6402 |
55 Hillsdale Bl. |
Corvallis |
OR |
1 |
672-71-3249 |
Yokomoto |
Akiko |
415 935-4228 |
3 Silver Ct. |
Walnut Creek |
CA |
1 |
712-45-1867 |
del Castillo |
Innes |
615 996-8275 |
2286 Cram Pl. #86 |
Ann Arbor |
MI |
1 |
722-51-5454 |
DeFrance |
Michel |
219 547-9982 |
3 Balding Pl. |
Gary |
IN |
1 |
724-08-9931 |
Stringer |
Dirk |
415 843-2991 |
5420 Telegraph Av. |
Oakland |
CA |
0 |
724-80-9391 |
MacFeather |
Stearns |
415 354-7128 |
44 Upland Hts. |
Oakland |
CA |
1 |
756-30-7391 |
Karsen |
Livia |
415 534-9219 |
5720 McAuley St. |
Oakland |
CA |
1 |
807-91-6654 |
Panteley |
Sylvia |
301 946-8853 |
1956 Arlington Pl. |
Rockville |
MD |
1 |
846-92-7186 |
Hunter |
Sheryl |
415 836-7128 |
3410 Blonde St. |
Palo Alto |
CA |
1 |
893-72-1158 |
McBadden |
Heather |
707 448-4982 |
301 Putnam |
Vacaville |
CA |
0 |
899-46-2035 |
Ringer |
Anne |
801 826-0752 |
67 Seventh Av. |
Salt Lake City |
UT |
1 |
998-72-3567 |
Ringer |
Albert |
801 826-0752 |
67 Seventh Av. |
Salt Lake City |
UT |
1 |
Таблица publishers
pub_id |
pub_name |
city |
state |
country |
0736 |
New Moon Books |
Boston |
MA |
USA |
0877 |
Binnet & Hardley |
Washington |
DC |
USA |
1389 |
Algodata Infosystems |
Berkeley |
CA |
USA |
1622 |
Five Lakes Publishing |
Chicago |
IL |
USA |
1756 |
Ramona Publishers |
Dallas |
TX |
USA |
9901 |
GGG&G |
Mмnchen |
(null) |
Germany |
9952 |
Scootney Books |
New York |
NY |
USA |
9999 |
Lucerne Publishing |
Paris |
(null) |
France |
Таблица titles
title_id |
title |
type |
pub_id |
price |
advance |
ytd_sales |
pubdate |
BU1032 |
The Busy Executive's Database Guide |
business |
1389 |
19.99 |
5,000.00 |
4095 |
Jun 12 1991 12:00AM |
BU1111 |
Cooking with Computers: Surreptitious Balance Sheets |
business |
1389 |
11.95 |
5,000.00 |
3876 |
Jun 9 1991 12:00AM |
BU2075 |
You Can Combat Computer Stress! |
business |
0736 |
2.99 |
10,125.00 |
18722 |
Jun 30 1991 12:00AM |
BU7832 |
Straight Talk About Computers |
business |
1389 |
19.99 |
5,000.00 |
4095 |
Jun 22 1991 12:00AM |
MC2222 |
Silicon Valley Gastronomic Treats |
mod_cook |
0877 |
19.99 |
0.00 |
2032 |
Jun 9 1991 12:00AM |
MC3021 |
The Gourmet Microwave |
mod_cook |
0877 |
2.99 |
15,000.00 |
22246 |
Jun 18 1991 12:00AM |
MC3026 |
The Psychology of Computer Cooking |
UNDECIDED |
0877 |
(null) |
(null) |
(null) |
Apr 3 1996 3:45AM |
PC1035 |
But Is It User Friendly? |
popular_comp |
1389 |
22.95 |
7,000.00 |
8780 |
Jun 30 1991 12:00AM |
PC8888 |
Secrets of Silicon Valley |
popular_comp |
1389 |
20.00 |
8,000.00 |
4095 |
Jun 12 1994 12:00AM |
PC9999 |
Net Etiquette |
popular_comp |
1389 |
(null) |
(null) |
(null) |
Apr 3 1996 3:45AM |
PS1372 |
Computer Phobic AND Non-Phobic Individuals: Behavior Variations psychology |
popular_comp |
0877 |
21.59 |
7,000.00 |
375 |
Oct 21 1991 12:00AM |
PS2091 |
Is Anger the Enemy? |
psychology |
0736 |
10.95 |
2,275.00 |
2045 |
Jun 15 1991 12:00AM |
PS2106 |
Life Without Fear |
psychology |
0736 |
7.00 |
6,000.00 |
111 |
Oct 5 1991 12:00AM |
PS3333 |
Prolonged Data Deprivation: Four Case Studies |
psychology |
0736 |
19.99 |
2,000.00 |
4072 |
Jun 12 1991 12:00AM |
PS7777 |
Emotional Security: A New Algorithm |
psychology |
0736 |
7.99 |
4,000.00 |
3336 |
Jun 12 1991 12:00AM |
TC3218 |
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean |
trad_cook |
0877 |
20.95 |
7,000.00 |
375 |
Oct 21 1991 12:00AM |
TC4203 |
Fifty Years in Buckingham Palace Kitchens |
trad_cook |
0877 |
11.95 |
4,000.00 |
15096 |
Jun 12 1991 12:00AM |
TC7777 |
Sushi, Anyone? |
trad_cook |
0877 |
14.99 |
8,000.00 |
4095 |
Jun 12 1991 12:00AM |
Таблица titleauthor
au_id |
title_id |
172-32-1176 |
PS3333 |
213-46-8915 |
BU1032 |
213-46-8915 |
BU2075 |
238-95-7766 |
PC1035 |
267-41-2394 |
BU1111 |
267-41-2394 |
TC7777 |
274-80-9391 |
BU7832 |
409-56-7008 |
BU1032 |
427-17-2319 |
PC8888 |
472-27-2349 |
TC7777 |
486-29-1786 |
PC9999 |
486-29-1786 |
PS7777 |
648-92-1872 |
TC4203 |
672-71-3249 |
TC7777 |
712-45-1867 |
MC2222 |
722-51-5454 |
MC3021 |
724-80-9391 |
BU1111 |
724-80-9391 |
PS1372 |
756-30-7391 |
PS1372 |
807-91-6654 |
TC3218 |
846-92-7186 |
PC8888 |
899-46-2035 |
MC3021 |
899-46-2035 |
PS2091 |
998-72-3567 |
PS2091 |
998-72-3567 |
PS2106 |
Задания типа А:
Формулировка запроса на языке SQL |
Словесная интерпретация запроса |
Результаты выполнения запроса |
SELECT AVG(price) FROM titles; |
Определить среднюю цену на книги из таблицы «titles» |
|
SELECT COUNT(*) FROM authors WHERE state= "CA"; |
Подсчитать общее количество строк в таблице «authors» со значением «CA» в поле «state» |
|
SELECT state, COUNT(pub_id) FROM publishers GROUP BY state; |
Определить для каждого штата
число находящихся в нем |
|
SELECT title, pub_name FROM titles INNER JOIN publishers ON titles.pub_id=publishers.pub_ |
Выбрать названия книг и имена выпустивших их издателей с использование операции соединения INNER JOIN. |
|
SELECT type, AVG(price) FROM titles GROUP BY type HAVING AVG(price) < (SELECT AVG(price) FROM titles); |
Выбрать тип и цену для всех книг, цена которых не превышает средней |
|
SELECT pub_name, state FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles); |
Выбрать издательства и штат , идентификатор которых не входит в список идентификаторов из таблицы «titles» |
|
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"); |
Найти авторов самых дешёвых книг, вышедших в США. |
|
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; |
Вывести Фамилию и Имя автора (из таблицы Авторы), у которого IDномер совпадает с IDномером автора ( из таблицы АВТОР-КНИГА), где IDзаголовка книги совпадает с ID Заголовком книги( из таблицы КНИГИ), где что-то Калифорния и упорядочив штаты( из таблицы Издатели) с совпадающими IDИздателя (из таблицы Издатели) и IDИздателя (из таблицы Заголовки) по фамилии и имени. |
|
Задания типа Б:
Словесная интерпретация запроса |
Формулировка запроса на языке SQL |
Результаты выполнения запроса |
Выбрать все идентификаторы и цены книг, причём цена книги должна лежать в диапазоне от 10000 до 40000. |
SELECT title_id, price FROM titles WHERE price between 10000 and 40000; |
|
Выбрать информацию о книгах, упорядоченную по убыванию объёма продаж (по стоимости). Информация о книгах должна включать идентификатор, цену, объем продаж (по количеству) и объем продаж (по стоимости). |
SELECT title_id, price, ytd_sales, price*ytd_sales as "Доход от продажи" FROM titles ORDER BY price*ytd_sales DESC; |
|
Выбрать издателей, не выпускающих книг по бизнесу. |
SELECT publishers.pub_name FROM publishers WHERE (((publishers.[pub_id]) Not In (SELECT pub_id FROM titles WHERE type= "business"))); |
|
Определить города и штаты проживания каждого из авторов и издателей в виде одной результирующей таблицы. |
SELECT city, state FROM authors UNION SELECT city, state FROM publishers ORDER BY state, city; |
|
Вычислить среднюю цену всех книг и медиану цены. |
SELECT AVG(price) as "средняя цена" ,SUM(price) as "медиана цены" FROM titles ; |
|
Определить местонахождение |
SELECT city FROM publishers WHERE price IN (SELECT price FROM titles WHERE price<20); |
|
Определить книги, число продаж для которых не указано. |
SELECT title FROM titles WHERE ytd_sales IS NULL; |
|
Определить книги, выпущенные в период с 1 июля 1991 г. по 30 октября 1991 г. |
SELECT title, pubdate FROM titles WHERE (((pubdate)>#01/07/1991#) and ((pubdate)<#30/10/1991#)); |
|
Информация о работе Выборка данных из базы данных с использованием языка SQL