Выборка данных из базы данных с использованием языка SQL

Автор работы: Пользователь скрыл имя, 19 Декабря 2012 в 11:37, лабораторная работа

Краткое описание

Цель работы: изучить принципы работы с базой данных, изучить спецификации запроса языка баз данных SQL, получить практические навыки составления и содержательной интерпретации запросов выборки данных (операторов SELECT), а также их выполнения с использованием СУБД MS Access.

Прикрепленные файлы: 1 файл

отчет.docx

— 189.07 Кб (Скачать документ)

Лабораторная работа

Выборка данных из базы данных с использованием языка SQL.

Цель работы: изучить принципы работы с базой данных, изучить спецификации запроса языка баз данных 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_id;

Выбрать названия книг и имена выпустивших  их издателей с использование операции соединения 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