|
Radosław Wolicki |
Kierunek: ETI |
Prowadzący: dr inż. A. Sikorski |
|
Bazy Danych/ LAB 9 |
Wydział: Fizyka Techniczna |
Data: 26.11.2012 |
Zaawansowane techniki wykorzystywania zapytań zagnieżdżonych
Baza Northwind
1) Wyznaczyć najlepsze miesiące po względem obrotów dla poszczególnych lat, które obejmuje baza danych Northwind
select SUM(Quantity*UnitPrice) as 'Cena',Year(o.OrderDate)as 'Rok',Month(o.OrderDate) as 'Miesi¹c' from [Order Details] od , Orders o
where
od.OrderID=o.OrderID
group by
Year(o.OrderDate),Month(o.OrderDate)
2) Dla każdego miesiąca (w poszczególnych latach) wyznaczyć pracownika, który wygenerował największe dochody
select YEAR(o1.OrderDate) as 'Rok',MONTH(o1.orderDate) as 'Miesi¹c',
(
select EmployeeID from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
having SUM(Quantity*unitPrice)=
(
select max(total) from
(
select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
) t
)
) [The Best Sales Representative of the Month]
from Orders o1 group by YEAR(o1.OrderDate),MONTH(o1.orderDate)
3) Zmodyfikować zapytanie z p.2 tak by znajdowało najlepszą: kategorię, produkt,
klienta, kraj (pole country w tabelce Customers)
select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o
where
od.OrderID=o.OrderID
group by
EmployeeID,YEAR(OrderDate),MONTH(OrderDate)
order by 3,4
select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=1996 and MONTH(OrderDate)=7
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
select max(total) from
(
select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=1996 and MONTH(OrderDate)=7
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
) t
select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=1996 and MONTH(OrderDate)=7
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
having SUM(Quantity*unitPrice)=
(
select max(total) from
(
select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=1996 and MONTH(OrderDate)=7
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
) t
)
select YEAR(o1.OrderDate),MONTH(o1.orderDate) from Orders o1 group by YEAR(o1.OrderDate),MONTH(o1.orderDate)
select YEAR(o1.OrderDate),MONTH(o1.orderDate) ,
(
select EmployeeID from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
having SUM(Quantity*unitPrice)=
(
select max(total) from
(
select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o
where
od.OrderID=o.OrderID and
YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)
group by
EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)
) t
)
) [The Best Sales Representative of the Month]
from Orders o1 group by YEAR(o1.OrderDate),MONTH(o1.orderDate)
4) Dla poszczególnych miesięcy wyznaczyć kraj o największej średniej wartości
zamówienia
?????
5) Posługując się VS i językiem c# uzyskać wyniki z pp 1-4 wykonując w pętli skalarne
zapytanie dla poszczególnych 23 miesięcy. Przykładowo dla kategorii o największych
obrotach posłużyć się następującym wzorcem zapytania:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI");
SqlConnection conn1 = new SqlConnection(conn.ConnectionString);
conn.Open(); conn1.Open();
SqlCommand cmd1 = new SqlCommand() { Connection = conn };
cmd1.CommandText = "select distinct YEAR(OrderDate),MONTH(OrderDate) from Orders";
SqlDataReader rdr = cmd1.ExecuteReader();
while (rdr.Read())
{
SqlCommand cmd2 = new SqlCommand() { Connection = conn1 };
cmd2.CommandText = String.Format(
"select CategoryID from Orders join [Order Details] on Orders.OrderID=[Order Details].OrderID " +
"join Products on Products.ProductID=[Order Details].ProductID " +
"where YEAR(OrderDate)={0} and MONTH(OrderDate)={1} group by CategoryID " +
"having Sum(Quantity*[Order Details].UnitPrice)= " +
"(select max(totals) from " +
"(select Sum(Quantity*[Order Details].UnitPrice) totals from Orders join [Order Details] on Orders.OrderID=[Order Details].OrderID " +
"join Products on Products.ProductID=[Order Details].ProductID " +
"where YEAR(OrderDate)={0} and MONTH(OrderDate)={1} group by CategoryID) t )", rdr.GetInt32(0), rdr.GetInt32(1));
Console.Write(rdr.GetInt32(0));
Console.Write("\t");
Console.Write(rdr.GetInt32(1));
Console.Write("\t");
int categoryId = (int)cmd2.ExecuteScalar();
Console.Write(categoryId);
Console.Write("\t");
cmd2.CommandText = "select CategoryName from Categories where CategoryID =" + categoryId.ToString();
cmd2.CommandText = string.Format("select CategoryName from Categories where CategoryID ={0}", categoryId);
Console.Write(cmd2.ExecuteScalar());
Console.Write("\n");
}
rdr.Close();
conn.Close();
//,rdr.GetInt32(0),rdr.GetInt32(1));
}
}
}
cmd1.CommandText = String.Format(
"select CategoryID from Orders join [Order Details] on Orders.OrderID=[Order Details].OrderID " +
"join Products on Products.ProductID=[Order Details].ProductID " +
"where YEAR(OrderDate)={0} and MONTH(OrderDate)={1} group by CategoryID " +
"having Sum(Quantity*[Order Details].UnitPrice)= " +
"(select max(totals) from " +
"(select Sum(Quantity*[Order Details].UnitPrice) totals from Orders join [Order Details] on
Orders.OrderID=[Order Details].OrderID " +
"join Products on Products.ProductID=[Order Details].ProductID " +
"where YEAR(OrderDate)={0} and MONTH(OrderDate)={1} group by CategoryID) t )"
,rdr.GetInt32(0),rdr.GetInt32(1));
6) Dla każdego miesiąca wyznaczyć kategorie, których produkty nie były zamówione
w SQL:
select YEAR(OrderDate) , MONTH(OrderDate),ProductName from Orders o,Products
where
not exists(
select * from [Order Details] join Orders on Orders.OrderID=[Order Details].OrderID
where YEAR(OrderDate)=YEAR(o.OrderDate) and MONTH(OrderDate)=MONTH(o.OrderDate)
and Products.ProductID=[Order Details].ProductID
)
group by YEAR(OrderDate) , MONTH(OrderDate),ProductName