Лабораторная работа 8.1 Работа с представлениями на SQL Server и с перекрестными запросами в Access

Представления SQL Server и перекрестные запросы Access, обращение из Microsoft Access к представлениям SQL Server

Задание:

1) Создайте в базе данных Northwind представление SalesProductCountry, который должен возвращать информацию о сумме продаж каждого продукта в каждой стране. В возвращаемом наборе результатов должно быть три столбца:

·        CategoryName (из таблицы Categories);

·        Country (из таблицы Customers);

·        Sales (произведение значений из столбца Quantity в таблице Order Details и столбца UnitPrice в той же таблице);

Возвращаемые значения должны выглядеть так, как представлено на рис:

2) Создайте новую базу данных Microsoft Access C:\PivotQuery.mdb, в которой необходимо создать перекрестный запрос PivotSales. Этот запрос должен:

·        обращаться к созданному вами запросу SalesProductCountry в базе данных Northwind на вашем локальном SQL Server;

·        по строкам должен выводить названия продуктов;

·        по столбцам должен выводить названия стран;

·        в ячейках на пересечении строк и столбцов - сумму продаж каждого продукта по каждой стране.

Созданный запрос должен выглядеть так, как представлено на рис:

Решение:

К пункту 1 - создание представления в SQL Server:

1) Откройте Enterprise Manager и подключитесь к своему локальному серверу SQL Server.

2) Раскройте в нем контейнер Databases -> Northwind -> Views, щелкните правой кнопкой мыши по контейнеру Views и в контекстном меню выберите New View. Откроется окна конструктора представлений.

3) Щелкните правой кнопкой мыши по пустому серому полю в верхней части экрана и в контекстном меню выберите Add Table. Добавьте таблицы Customers, Order Details, Orders, Products, Categories. Нажмите на кнопку Close, чтобы закрыть окно добавления таблиц.

4) Установите флажки напротив следующих полей:

·        в таблице Categories - CategoryName;

·        в таблице Customers - Country;

·        в таблице Order Details - Quantity и Unitprice.

5) В окне с кодом TSQL отредактируйте запрос, чтобы его текст выглядел так:

SELECT     dbo.Categories.CategoryName, dbo.Customers.Country, SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS Sales

FROM         dbo.Customers

INNER JOIN dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID

GROUP BY dbo.Categories.CategoryName, dbo.Customers.Country

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

Примечание. В принципе, группировку здесь можно было бы и не проводить - на работоспособность нашей задачи это бы не повлияло. Однако в этом случае нам бы пришлось выполнять вычисления на клиенте - в MS Access, что делать не рекомендуется.

6) Нажмите на кнопку с изображением дискеты, чтобы сохранить представление, и в ответ на приглашение введите для него имя SalesProductCountry. Закройте окно Enterprise Manager.

К пункту 2 - создание перекрестного запроса в Access:

1) Создайте подключение ODBC типа System DSN с именем NorthwindDSN, которое обращалось бы к базе данных Northwind на вашем локальном сервере SQL Server (см. ответ к лаб. 3.3).

2) Запустите Microsoft Access и создайте новую базу данных C:\PivotQuery.mdb.

3) В окне базы данных в меню File выберите Внешние данные -> Связь с таблицами. В окне Связь в списке Тип файлов выберите Базы данных ODBC (DSN). В окне Select Data Source перейдите на вкладку Machine Data Source, выберите созданный вами источник данных NorthwindDSN и нажмите на кнопку OK.

4) В списке таблиц выберите созданное вами представление dbo.SalesProductCountry и нажмите OK.

5) В окне "Выбор однозначного индекса" выделите все три строки и нажмите OK. В списке таблиц базы данных PivotQuery появится новая внешняя таблица (представление) dbo_SalesProductCountry.

6) В окне базы данных перейдите в контейнер Запросы и нажмите на кнопку Создать на панели управления.

7) В окне Новый запрос в списке шаблонов выберите Перекрестный запрос.

8) На экране Создание Перекрестных таблиц выделите dbo_SalesProductCountry и нажмите Далее.

9) На следующем экране вам потребуется выбрать поле, которое будет использоваться в качестве строк. Выберите Country.

10) Далее вам потребуется выбрать поле для столбцов. Выберите CategoryName.

11) Далее вам нужно будет выбрать агрегатную функцию для оставшегося значения Sales. Выберите Сумма и нажмите Далее.

12) На последнем экране определите имя запроса (например, Продажи категорий товаров по странам) и нажмите Готово. Просмотрите результат выполнения перекрестного запроса и закройте окно Access с сохранением внесенных изменений.

Получить учебные материалы по этому курсу