Bazy Danych Laboratorium

5.11.2012/12.11.2012 (mam nadzieję nie 19.11.2012)

Tzw. eksploracja danych i wyznaczanie statystyk wielowymiarowych za pomocą zagnieżdżonych

zapytań:

1) Uruchomić przykład statystyk w układzie (kategorie -lata).

select CategoryName,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and year(o.OrderDate)=1996 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "1996",

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and year(o.OrderDate)=1997 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "1997",

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and year(o.OrderDate)=1998 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "1998 "

from Categories c

Wystarczy usunąć przecinek z przykładu by kod był poprawny i wyświetlił:

2) Zmodyfikować przykład tak by wyświetlał dane dla miesięcy.

select CategoryName,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=1 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Styczen",

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=2 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Luty",

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=3 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Marzec " ,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=4 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Kwiecieñ " ,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=5 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Maj " ,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=6 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Czerwiec " ,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=7 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Lipiec " ,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=8 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Sierpieñ " ,

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=9 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Wrzesieñ ",

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=10 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Pazdziernik ",

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=11 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Listopad ",

( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where

o.OrderID=od.OrderId and month(o.OrderDate)=12 and p.ProductID=od.ProductID and

p.CategoryID=c.categoryID ) as "Grudzieñ "

from Categories c

3) Karkołomne zadanie domowe : (2012 zmiana). Zamienić wiersze i kolumny! Teraz w wierszach mają być lata a w kolumnach kategorie(dotyczy kategorie lata nie miesiące). Wskazówka: umiejętnie posłużyć się schowkiem. Na zajęciach spróbować uzyskać wynik dla dwóch kolumn. Potem już z górki.

select year(o.OrderDate)as "Rok",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='1') as "Beverages",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='2') as "Condiments",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='3') as "Confections",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='4') as "Dairy Products",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='5') as "Grains/Cereals",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='6') as "Meat/Poultry",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='7') as "Produce",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where

o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and

p.CategoryID='8') as "Seafood"

from

Orders o

group by year(o.OrderDate)

order by 1

4) Rozważyć ogólny przypadek statystyki dwuwymiarowej.

Dane można posortować względem dwóch różnych parametrów. Czyli jak najprościej mówiąc w tabeli z SQL możemy mieć ukazane dane z perspektywy dwóch innych kategorii. Gdzie te dwie dane kategorie są łączone przez te dane.

5) Statystyka kraj (country) z tabeli Customers wiersze i regiony dla klientów.

6) Zapoznać się z funkcją raport tabeli przestawnej w arkuszu kalkulacyjnym.

(select Distinct S.CITY,J.CITY from S, J)

select *

from S

7) Powtórka z zapytań SQL (złączenie i grupowanie) – baza CJDate

a. Sumaryczne wartości (QTY) dla dostawców z Londynu

b. Sumaryczne wartości dla unikalnych par miasto dostawcy miasto odbiorcy

c. (premiowane) Wypisać unikalne pary miast dostawca (S) odbiorca (J). Jest ich 15. Wierszy uzyskanych w punkcje c jest 11 – wiadomo dlaczego. Zmodyfikować zapytanie w taki sposób żeby brakujące 4 pary też się pojawiły z wartością 0. (super premiowane) Uzyskać ten sam efekt posługując się tylko złączeniami (wskazówka – użyć outer join) – było na wykładzie o anti-semijoin.

d. Sumaryczny ciężar (QTY*WEIGTH) dla miast odbiorców

e. Liczba części dostarczanych przez poszczególnych dostawców (S)

f. Dostawcy dostarczający większą od przeciętnej liczbę części

g. Liczba dostawców dostarczających poszczególne części

7 – Już było !!!

8) Zapoznać się z prostymi przykładami programów w języku c#, ze szczególnym

uwzględnieniem programowania aplikacji baz danych.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.OleDb;

namespace ConsoleApplication3

{

class Program

{

static void Main(string[] args)

{

OleDbConnection conn = new

OleDbConnection(@"Provider=SQLNCLI.1;Data Source=MIROSŁAWPC\

SQLEXPRESS;Integrated Security=SSPI");

conn.Open();

conn.Close();

}

}

}

Uwaga: Postać parametru konstruktora OleDbConnection tzw. ConnectionString zależy od typu sterownika

oraz od konkretnego serwera SQL. Znaleźć w Google różne postaci tego parametru. W lab.111 nie ma

sterownika dla OleDB (stan na 17.10.2011) dlatego należy posłużyć się klasą SQLConnection.

Przykład:

SqlConnection conn = new SqlConnection(@"Data Source=p10;Integrated Security=SSPI;

Initial Catalog=Northwind");

Nieco bardziej złożony przykład.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.OleDb;

namespace ConsoleApplication3

{

class Program

{

static void Main(string[] args)

{

OleDbConnection conn = new

OleDbConnection(@"Provider=SQLNCLI.1;Data Source=MIROSŁAWPC\

SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind");

conn.Open();

OleDbCommand cmd = new OleDbCommand("select * from

Employees",conn);

Console.WriteLine("Employees");

OleDbDataReader rdr=

cmd.ExecuteReader();

while (rdr.Read())

{

for (int i = 0; i < rdr.FieldCount; i++)

{

Console.Write(rdr.GetValue(i).ToString());

Console.Write("\t");

}

}

conn.Close();

}

}

}