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

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И  НАУКИ РФ

ФГБОУ ВПО «ИРКУТСКИЙ ГОСУДАРСТВЕННЫЙ  УНИВЕРСИТЕТ»

ФИЛИАЛ В Г. БРАТСКЕ

 

 

 

 

 

 

 

 

 

 

 

 

 

КОНТРОЛЬНАЯ РАБОТА

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

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

 

 

Вариант № 17

 

 

 

 

 

 

 

 

 

Выполнил:

студент гр.ПИзcc-11                                               Фомичева А.И.

                                        (Ф.И.О.)

 

Проверил:

ст.пр.                                                                                     Мельникова В.А.

                                                               (Ф.И.О.)

 

 

 

 

 

 

 

Братск 2013

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

Порядок выполнения работы

1. Изучить структуру и элементы SQL-запроса выборки, в том числе разделы FROM, WHERE, GROUP BY, HAVING, ORDER BY, а также предикаты условия поиска и агрегатные функции.
2. Изучить операции реляционной алгебры (эквисоединение).
3. Изучить состав базы данных книготорговой компании (база данных pubs), структуру и семантику ее таблиц.

1. Создать базу данных книготорговой компании указанной ниже структуры путем импорта данных или вручную.
2. Создать индексы и ключевые поля.
3. Создать схему данных.

4. Получить у преподавателя номер варианта задания.

5. В соответствии с вариантом задания типа А произвести содержательную интерпретацию заданных SQL-запросов, выполнить их  в СУБД MS Access, проинтерпретировать результаты выполнения запросов.
6. В соответствии с вариантом задания В составить SQL-запросы по их заданному содержательному описанию, выполнить SQL-запросы в СУБД MS Access, проинтерпретировать результаты выполнения запросов.
7. Оформить отчет.

 

 

Задание:

Номер варианта	Задание типа A	Задание типа B
17	8,19,31,52, 45,55,76,84	3,11,33,78, 74,82,90,95

 

Задание типа А:

8) SELECT DISTINCT type

FROM titles

ORDER BY type ASC

Запрос служит для вывода типа в  папке titles.

19) SELECT au_lname, au_fname, city, state

FROM authors

WHERE city like "San*";

Запрос служит для вывода: фамилия  автора,имя автора, город проживания, штат проживания, у которых город проживания начинаетсяна «San».

31) SELECT type, pub_id, AVG(price) as "avg", SUM(price) as "sum"

FROM titles  

WHERE type IN ("business", "mod_cook")

GROUP BY type, pub_id

 

Запрос служит для вывода типа «business» и «mod_cook» выявляет среднее значение и сумму в папке titles.

52) SELECT pub_name

FROM publishers p INNER JOIN titles t ON p.pub_id=t.pub_id WHERE $15>price AND type= "psychology"

ORDER BY pub_name

 Данный запрос выводит книги  по психологии.

45) SELECT t.*, pub_name

FROM titles t, publishers p

WHERE t.pub_id=p.pub_id

Данный запрос служит для вывода таблиц с одинаковым идентификатором  titles и publishers.

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"

 

Данный запрос служит для вывода таблицы с именем, фамилией и названием  книги.

76) SELECT pub_name, state

FROM publishers

WHERE pub_id NOT IN

(SELECT pub_id

FROM titles)

 

Данный запрос выводит название и штат.

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)

Выводит название книги Бостона.

Задание типа В:

3) Выбрать информацию о книгах, объем (стоимость) продаж которых в текущем году меньше стоимости предварительной продажи. Информация о книгах должна включать тип книги, идентификатор и цену книги.

SELECT title_id, type, price

FROM titles

WHERE ytd_sales<advance;

11) Выбрать имена и фамилии всех авторов, упорядоченные в первую очередь по возрастанию фамилий и, во вторую очередь, по возрастанию имен.

SELECT au_lname, au_fname

FROM authors

ORDER BY au_fname, au_lname;

33) Выбрать всех издателей литературы  по бизнесу. В запросе использовать подзапрос для выборки нужных идентификаторов издателей. В условии поиска использовать предикат IN. В выбираемые данные включить имя издателя.

SELECT pub_name

FROM publishers

WHERE pub_id IN

(SELECT pub_id

FROM titles

WHERE type="business");

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

SELECT *

FROM (SELECT publishers.*, titles.* FROM publishers INNER JOIN titles ON publishers.pub_id=titles.pub_id WHERE publishers.country='usa')  AS [%$##@_Alias]

WHERE titles.price>=10;

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

SELECT publishers.state, Count(publishers.pub_id) AS [Count-pub_id]

FROM publishers

GROUP BY publishers.state;

82) Определить книги,  число продаж для которых не указано.

SELECT title

FROM titles

WHERE ytd_sales IS Null;

90) Определить издательства, не выпустившие книг.

SELECT publishers.pub_id, publishers.pub_name, publishers.country

FROM publishers LEFT JOIN titles ON publishers.pub_id=titles.pub_id

WHERE titles.pub_id IS NULL;

95) Вычислить для каждого  типа книг среднее арифметическое  минимальной и максимальной цены. Результат упорядочить по убыванию значений.

SELECT type, MAX(price) AS ["макс цена"], MIN(price) AS ["мин цена"], AVG(price) AS ["сред цена"]

FROM titles

GROUP BY type

ORDER BY type DESC;

 

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