Hurtownie danych – Spr. 1.

PWr. WIZ, Data: 4.03.2015

Student	--------------------------------------------------------------------	Ocena
Indeks
Imię
Nazwisko

Zestaw składa się z 9 zadań. Jeżeli nie potrafisz rozwiązać zadania, to próbuj podać chociaż częściowe rozwiązanie lub uzasadnienie przyczyny braku rozwiązania. Pamiętaj o podaniu nr. indeksu oraz imienia i nazwiska.

Baza danych: AdventureWorks2014

Zad. 0.

Należy dokonać implementacji konceptualnego modelu danych (rys. 1.) w systemie MS SQL 2014

Rysunek 1. Konceptualny model danych „Sklep”

Rozwiązanie:

create table Klient

(

id_klienta Integer PRIMARY KEY

);

create table Sklep

(

id_sklepu Integer PRIMARY KEY

);

create table Produkt

(

id_produktu Integer PRIMARY KEY

);

create table robi_zakupy

(

id_robi_zakupy Integer PRIMARY KEY,

id_klienta Integer References Klient(id_klienta) NOT NULL,

id_sklepu Integer References Sklep(id_sklepu) NOT NULL,

data Date,

czas Time

);

create table nabywa

(

id_nabywa Integer PRIMARY KEY,

id_robi_zakupy Integer References robi_zakupy(id_robi_zakupy) NOT NULL,

id_produktu Integer References Produkt(id_produktu) NOT NULL,

ilosc Integer,

cena Dec(8,2)

);

Create table oferuje

(

id_sklepu Integer References sklep(id_sklepu) NOT NULL,

id_produktu Integer References produkt(id_produktu) NOT NULL,

Constraint pk_oferuje Primary Key(id_sklepu, id_produktu)

);

Zad. 1.

Odtworzyć model konceptualny fragmentu bazy danych AdventureWorks2014 uwzględniając: Osoby, Pracowników, Klientów, Zamówienia, Produkt

Rozwiązanie:

Zad 2.

Utworzyć wykaz produktów (nazw oraz ich cen), dla których nie ma zdefiniowanego koloru.

Rozwiązanie:

SELECT Name, ListPrice

FROM AdventureWorks2014.Production.Product

WHERE Color IS NULL

Zad 3.

Wyznaczyć nazwę najdroższego i najtańszego produktu

Rozwiązanie:

SELECT Name

FROM AdventureWorks2014.Production.Product

WHERE ListPrice = (SELECT MAX(ListPrice)

FROM AdventureWorks2014.Production.Product)

OR ListPrice = (SELECT MIN(ListPrice)

FROM AdventureWorks2014.Production.Product);

Zad 4.

Zdefiniować zapytanie w celu utworzenia wykazu produktów, których cena jest niższa od średniej ceny wszystkich produktów

Rozwiązanie:

SELECT Name, ListPrice

FROM AdventureWorks2014.Production.Product

WHERE ListPrice< (SELECT AVG(ListPrice)

FROM AdventureWorks2014.Production.Product)

Zad 5.

Wyznaczyć nazwę i cenę najdroższego oraz najtańszego produktu za pomocą pojedynczego zapytania

Rozwiązanie:

SELECT Name, ListPrice

FROM AdventureWorks2014.Production.Product

WHERE ListPrice = (SELECT MAX(ListPrice)

FROM AdventureWorks.Production.Product) OR

ListPrice = (SELECT MIN(ListPrice)

FROM AdventureWorks.Production.Product);

Zad. 6.

Utworzyć wykaz wszystkich klientów prezentując imiona i nazwiska klientów wraz z łączną sumą dokonanych przez nich zakupów (do określenia sumy użyć kolumny SalesOrderHeader.SubTotal). W zapytaniu należy użyć tabeli SalesOrderHeader oraz powiązanych tabel, które zawierają dane niezbędne do utworzenia wykazu.

Rozwiązanie:

SELECT FirstName, MiddleName, LastName, SUM(SOH.SubTotal) SubTotal

FROM AdventureWorks2014.Person.Person P JOIN AdventureWorks2014.Sales.Customer C ON P.BusinessEntityID = C.PersonID

JOIN AdventureWorks2014.Sales.SalesOrderHeader SOH ON C.CustomerID=SOH.CustomerID

Group by LastName, FirstName, MiddleName

Zad. 7.

Proszę wyznaczyć

1. Sumaryczną kwotę zakupów dokonanych przez wszystkich klientów

2. Sumaryczną kwotę zakupów dokonanych przez klientów poszczególnych latach

Rozwiązanie:

1. SELECT sum(subtotal)

From AdventureWorks2014.Sales.SalesOrderHeader

2. SELECT Convert(Varchar(4),OrderDate,20) Data, SUM(SubTotal) SubTotal

From AdventureWorks2014.Sales.SalesOrderHeader

Group By Convert(Varchar(4),OrderDate,20)

Zad. 8.

Proszę zdefiniować zapytanie w celu utworzenia jednego raportu zawierającego wszystkie poniższe pozycje:

• Sumaryczną sprzedaż dokonaną przez poszczególnych pracowników

• Sumaryczne kwotę zakupów dokonanych przez poszczególnych pracowników

• Sprzedaż dokonaną przez pracowników poszczególnym klientom

• Sumaryczną kwotę sprzedaży

Rozwiązanie:

Wnioski:

Uwaga:

• Sprawozdanie bez wniosków końcowych nie będzie sprawdzane i tym samym ocena jest negatywna!

• Oprogramowanie MS SQL 2014 - http://e5.onthehub.com/d.ashx?s=u5gyb53f3h