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

 

 

ФЕДЕРАЛЬНОЕ АГЕНТСТВО  ПО ОБРАЗОВАНИЮ

ГОУ ВПО «Иркутский государственный  университет»

Филиал в г. Братске

 

 

 

 

 

 

 

 

 

 

 

 

Контрольная работа

По дисциплине «База  данных»

 «Выборка данных из базы

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

 

Вариант 14.

 

 

 

 

 

 

 

 

Выполнил

Студент группы ПИз-10:                                  

 

Проверил:                                                                                        

                                                                              Мельникова В.А.

 

 

 

 

 

 

 

 

Братск – 2012

Цель работы:

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

Задание 1: Создать базу данных книготорговой компании.

Создать схему данных (рис.1)

 

Рис.1 Схема данных

2. Дать содержательную интерпретацию SQL-запросам, выполнить их с помощью СУБД MS Access, дать содержательную интерпретацию результатам выполнения SQL-запросов.

1. Выбрать все данные из таблицы authors с упорядочением фамилий по алфавиту:

SELECT au_lname, au_fname

FROM authors

ORDER BY au_lname, au_fname;

 

 

2. Выбрать все данные из таблицы title, число книг, проданных в текущем году, цена которых < аванса:

SELECT type, title_id, price

FROM title

WHERE price*ytd_sales < advance

3. Выбрать все данные из таблицы authors, фамилии и имена авторов имеющих один контракт и номер телефона которых начинается на 408:

SELECT au_lname, au_fname 

FROM authors

WHERE contract=1 AND phone LIKE ‘408*’

4. Выбрать из таблицы titles типы книг с датой опубликавания MIN(pubdate), MAX(pubdate):

SELECT type, MIN(pubdate), MAX(pubdate)

FROM titles

GROUP BY type

5. Посчитать количество авторов (по возврастанию) и определить число издательств которые публиковали данного автора.

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

6. Выбрать издательства которые не публиковали книги по современной кулинарии:

SELECT pub_name

FROM publishers p

WHERE NOT EXISTS

(SELECT *

FROM titles t

WHERE p.pub_id=t.pub_id

AND type="mod_cook")

7. Выбрать издательства, город штат и страну из таблици publishers которые публиковало 20 и более книг:

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)

8 Получить все города и страны из таблиц authors и publishers.

SELECT city, state

FROM authors

UNION SELECT city, state

FROM publishers

ORDER BY state, city

 

2. Составить SQL-запросы по их заданному содержательному описанию, выполнить SQL-запросы на SQL-сервере с использованием клиентских утилит ISQL/w или SQL-EM, проинтерпретировать результаты выполнения запросов.

1. Определить среднюю цену книг  по бизнесу.

SELECT AVG(price)

FROM titles

WHERE type= 'business';

2. Подсчитать число строк в таблице authors, включающих одинаковые идентификаторы авторов. В выбираемые данные включить идентификатор автора и соответствующее ему число повторяющихся строк.

SELECT au_id, COUNT(*)

FROM authors

GROUP BY au_id

HAVING COUNT(*)>=1;

3.  Выбрать тип и цену для  всех книг, цена которых не  превышает средней. В запросе  использовать подзапрос, определяющий среднюю цену книг.

SELECT type, AVG(price)

FROM titles

GROUP BY type

HAVING AVG(price) < (SELECT AVG(price) FROM titles);

 

4.  Определить страны, в которых  расположены издательства книг. Названия стран в результирующей таблице не должны повторяться. Вывести названия стран в порядке убывания.

SELECT DISTINCT country

FROM publishers

ORDER BY country;

5.  Определить для каждого  штата число находящихся в  нем издательств.

SELECT state, COUNT(pub_id)

FROM publishers

GROUP BY state;

6.  Определить число городов,  в которых выпускается литература  по компьютерам. В запросе использовать подзапрос.

SELECT COUNT(city)

FROM publishers

WHERE pub_id IN

(SELECT pub_id

FROM titles

WHERE type= 'popular_comp');

7.  Определить штаты, в издательства  которых издали книги ценой  более 10 долларов. В запросе использовать  подзапрос.

 

SELECT state

FROM publishers

WHERE  pub_id in

(SELECT pub_id

FROM titles

WHERE price < 10);