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

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

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

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

 

 

 

 

 

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

 

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

Тема: SQL

11 вариант

 

 

 

 

 

 

 

 

Выполнил:

студент группы:                         _____________               Сулаберидзе А.З.

ПИз-10            (подпись)                            

                                                                            

                                                                                                   

 

 

Проверил:                                   _____________      Мельникова В. А. ст. преподаватель           (подпись) 

кафедры ИТ

 

 

 

 

 

 

 

 

 

Братск-2012

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

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

Таблицы базы данных:

 

Рисунок 1 – Authors

Рисунок 2 – Publishers

Рисунок 3 – Titles

 

Рисунок 4 – Titleauthor

 

Схема данных:

 

Рисунок 2 – схема данных

 

Задание:

11

A: 7,24,36,43,69,76,79,88

B: 5,13,35,39,54,69,82,94

 

 

 

 

Часть А

7) SELECT AVG(price)

FROM titles

Делает выборку средней цены по столбцу price из таблицы titles

 

24) SELECT COUNT(*)

FROM authors

WHERE state= "CA"

Делает выборку  количества строк таблицы authors, в которых столбец state принимает значение CA

 

36) SELECT state, COUNT(pub_id)

FROM publishers

GROUP BY state

Делает выборку из таблицы publishers, показывает 2 столбца: state и количество совпадений для строк

43) SELECT title, pub_name

FROM titles  INNER JOIN publishers

ON titles.pub_id=publishers.pub_id

Делает выборку столбцов title и pub_name из таблицы, объединенной(titles и publishers) по условию, что pub_id таблицы titles совпадает с pub_id таблицы

 

 

 

 

 

 

 

 

 

 

69) SELECT type, AVG(price)

FROM titles

GROUP BY type

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

Делает выборку  из таблицы titles, сопоставляя столбец type с средней стоимостью по типу книги

 

 

 

76) SELECT pub_name, state

FROM publishers

WHERE pub_id NOT IN

(SELECT pub_id

FROM titles)

Выборка из столбцов pub_name,state таблицы publishers, по условию что pub_id не совпадает с значениями столбца pub_id из titles

 

 

 

 

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")

Запросы делает выборку по столбцам au_lname,au_fname,price из таблицы authors,titles,titleauthor, publishers по условиям:

Titleauthors.title_id=titles.title_id И authors.au_id=titleauthors.au_id И titles.pub_id=publishers.pub_id И price является минимальными значениями столбца price таблиц titles и publishers по условию равенства titles.pub_id и publishers.pub_id, группируя по стране(USA)

 

 

 

 

 

 

 

 

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

Выборка по столбцам au_lname,au_fname из таблицы authors сортированная по возрастанию по столбцам au_lname и аu_fname, где authors.au_id является результатом выборки столбца au_id из таблицы titleauthors ,где titleauthors.title_id является результатом выборки столбца title_id из таблицы title по условию, что некоторые значения таблицы, являющейся результатом выборки столбца state из таблицы publishers по условию равенства titles.pub_id и publishers.pub_id, равны CA

 

 

 

Часть Б

5)SELECT *

FROM [titles]

WHERE [price] BETWEEN 10000 AND  40000

Делает выборку по всем столбцам таблицы titles,по условию, что цена книги лежит между 10000 и 40000

 

13) SELECT [pub_id], [title], [price], [advance], [ytd_sales]

FROM [titles]

ORDER BY [ytd_sales];

Делает выборку по столбцам pub_id,title,price,advance,ytd_sales,по таблице titles, сортируя по возрастанию по столбцу ytd_sales

35) SELECT *

FROM titles

WHERE NOT [titles].[type]='business' AND EXISTS

(SELECT *

FROM [titles] INNER JOIN [publishers] ON [titles].[pub_id]=[publishers].[pub_id]);

Выбирает все книги, которые  имеют  любой тип, кроме бизнеса.

 

 

 

54) (SELECT SUM(price)/COUNT(*)

FROM (

SELECT DISTINCT [price]

FROM [titles])) UNION ALL

(SELECT AVG(price)

FROM [titles]);

Запрос выводит на экран  медиану и среднее значение цены таблицы titles

 

 

39) (SELECT [city],[state],[au_fname]

FROM [authors])

UNION ALL (SELECT [city],[state],[pub_name]

FROM [publishers]);

Запрос делает объединение  строк таблицы authors и таблицы publishers, показывая города и штаты, в которых расположены авторы и издательства

 

 

 

 

 

 

 

 

 

 

69) SELECT n.pub_name AS Publishers_Name

FROM

((SELECT pub_name,COUNT(*) AS quantity

FROM

(SELECT *

FROM publishers p INNER JOIN titles t ON p.pub_id = t.pub_id)

GROUP BY pub_name) n

INNER JOIN

(SELECT pub_name,COUNT(*) AS quantity

FROM

(SELECT *

FROM publishers p INNER JOIN titles t ON p.pub_id=t.pub_id

WHERE t.price<=100)

GROUP BY pub_name) l

ON n.pub_name=l.pub_name )

WHERE n.quantity=l.quantity

Запрос выводит на экран  имена издательств, каждая книга  которых соответствует условию, что цена этой книги меньше определенного  числа.

В подзапросе создаем временную  таблицу, в которой каждому издательству соответствует общее количество книг. Во втором подзапросе создаем  временную таблицу, в которой  каждому издательству соответствует  количество книг с ценой<=X(в данном случае X=100). Объединив временные таблицы в одну по имени издательств сравниваем исходное количество книг с количеством книг, соответствующих условию, выводим первый столбец(n.pub_name, т.к имена издательств совпадают)

 

 

 

 

 

 

82)  SELECT *

FROM titles

WHERE titles.ytd_sales Is NULL;

Запрос показывает книги, которые не содержат данные о количестве продаж за год

 

 

 

 

94)SELECT *

FROM [titles]

WHERE [pubdate] BETWEEN #07/01/1991# AND #10/30/1991#

Запрос выводит на экран  книги, опубликованные с 01.07.1991 по 30.10.1991