Laboratorium Baz Danych
Wykonał: Wojciech Ćwikliński
Prowadzący: dr inż. Andrzej Sikorski
Kierunek: ETI
Laboratorium 1 - 7.10.2013
Ad. 5) Utworzenie nowego użytkownika
use CJDate
create login wojt with password='www'
create user wojt for login wojt
grant control to wojt
Ad. 7) Skonfigurowanie zapory Windows, zezwolenie dostępu dla TCP na porcie 1433.
Po wykonaniu tego punktu mogłem zalogować się do bazy danych kolegi obok. Należało wpisać
zamiast gwiazdki jego numer IP, zmienić  Windows Authentication na  Server Authentication
i podać jego nazwę oraz hasło użytkownika.
Ad. 11) Wykonanie kopii bezpieczeństwa.
backup database CJDate to disk = 'c:\bd\CJDate.bak'
restore filelistonly from disk = 'c:\bd\CJDate.bak'
restore database CJDate from disk = 'c:\bd\CJDate.bak'
backup database Northwind to disk = 'c:\bd\Northwind.bak'
backup database Northwind
to disk = 'c:\bd\CJDate.bak'
WITH INIT;
GO
backup database Northwind to disk = 'c:\bd\Northwind.bak'
backup database Northwind
to disk = 'c:\bd\CJDate.bak'
WITH DIFFERENTIAL;
GO
Laboratorium 2 - 14.10.2013
Lab2x.pdf
Ad. 1)
create login wojt with password='www'
create user wojt for login wojt
EXEC sp_addsrvrolemember 'wojt', 'sysadmin'
Ad. 2)
Zainstalowałem bazę Adventure Works
Ad.3)
use Northwind
A. select Lastname, Firstname from Employees
B. select * from Employees where not country = 'USA'
C. select * from [Order Details] where OrderID = '10625'
D. select Quantity * UnitPrice from [Order Details] where OrderID = '10625'
E. select Quantity * UnitPrice * (1-Discount) from [Order Details] where OrderID =
'10625'
select MAX(Discount) from [Order Details]
Ad.4)
Utworzyłem nową bazę danych.
create database wojtek
Ad.5)
A. select count(*) from S
B. select count(*) from S where city = 'London'
C. select count(*) from S group by CITY
D. select count(*) from S where [STATUS] > 10
Ad.12)
use CJDate
declare @max int
select @max=max(qty) from spj
select * from spj where qty= @max
Laboratorium 3 - 21.10.2013
Ad.10)
Uruchomienie prostego kodu w Javie
public class Test1 {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
System.out.println("Hej! Umiem juz programować w Javie!");
}
}
Ad.11)
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
public class connectDS {
public static void main(String[] args) {
// Declare the JDBC objects.
Connection con = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
// Establish the connection.
SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("wojt");
ds.setPassword("www");
ds.setServerName("localhost");
ds.setPortNumber(1433);
ds.setDatabaseName("CJData");
con = ds.getConnection();
// Execute a stored procedure that returns some data.
cstmt = con.prepareCall("select*from SPJ");
cstmt.setInt(1, 50);
rs = cstmt.executeQuery();
// Iterate through the data in the result set and display it.
while (rs.next()) {
System.out.println("EMPLOYEE: " + rs.getString("LastName") +
", " + rs.getString("FirstName"));
System.out.println("MANAGER: " + rs.getString("ManagerLastName") +
", " + rs.getString("ManagerFirstName"));
System.out.println();
}
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
 finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
if (cstmt != null) try { cstmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
Ad.12)
select S# from SPJ where QTY=(select max(QTY) from SPJ)
select max(QTY) from SPJ
select S# from SPJ where QTY =800
select s# from SPJ where QTY=(select MAX(QTY) from SPJ)
declare @max int
select @max=max(qty) from SPJ
select * from SPJ where qty = @max
Zadania z Lab3x.pdf
Ad. 1)
create login wojt with password='www'
create user wojt for login wojt
use Northwind
grant select to wojt
use CJDate
grant control to wojt
Ad.2)A
select ShippedDate, OrderID from Orders order by 2
Ad.2)B
Select YEAR(OrderDate),[Order Details].OrderID,ProductID, Quantity, UnitPrice,
Discount from [Order Details], Orders where Orders.OrderID=[Order Details].OrderID and
year(OrderDate)='1997'
Ad.2)C
select distinct CompanyName,LastName from Employees e ,Customers c,Orders o
where
o.EmployeeID=e.EmployeeID and
o.CustomerID=c.CustomerID
order by 1
Ad.2)D
select CompanyName, [Order Details].OrderID,ProductID,Quantity,UnitPrice,Discount from
Customers join Orders on Customers.CustomerID=Orders.CustomerID
join [Order Details] on [Order Details].OrderID =Orders.OrderID
Ad.2)E
select CompanyName, [Order Details].OrderID,ProductID,Quantity,UnitPrice,Discount from
Customers, Orders , [Order Details]
where
Customers.CustomerID=Orders.CustomerID and
[Order Details].OrderID =Orders.OrderID
select ProductName,OrderID,[Order Details].ProductID,Quantity,[Order
Details].UnitPrice,Discount
from [Order Details] ,Products
where
[Order Details].ProductID =Products.ProductID
select (select ProductName from Products where PRoducts.ProductID=[Order
Details].ProductID ) ProductName,
OrderID,ProductID,Quantity,UnitPrice,Discount
from [Order Details]
Laboratorium 4 - 28.10.2013
Kontynuacja lab3x.pdf
select[Orders].OrderID,Customers.CompanyName,Country, EmployeeID, OrderDate
from Orders join Customers on Customers.CustomerID=[Orders].CustomerID, [Order
Details]
where Orders.OrderID=[Order Details].OrderID
Ad.2)J
select distinct ProductName, Country from Products, Customers where Country='Germany'
Ad.2)F
select Products.UnitPrice,Quantity,Discount, Products.ProductID,
Categories.CategoryID,Categories.CategoryName from [Order Details] join Products on
Products.ProductID=[Products].ProductID
join Categories on Categories.CategoryID= [Categories].CategoryID
Ad.4)A
select Orders.OrderID, ProductID, UnitPrice, Discount, OrderDate
from [Order Details],Orders where Orders.OrderID=[Order Details].OrderID
and Year(OrderDate)=1997
select Orders.OrderID, ProductID, OrderDate
from [Order Details] join Orders on Orders.OrderID=[Order Details].OrderID
and Year(OrderDate)=1998
Ad.4)B
select max(CompanyName) as company,Count(*) as ilosc from Customers c join Orders o on
c.CustomerID=o.CustomerID
group by c.CustomerID
order by 2 desc
Ad.4)C
select max(CompanyName),Sum(Quantity*UnitPrice) from
Customers c join Orders o on c.CustomerID=o.CustomerID,[Order Details] od
where
od.OrderID=o.OrderID
group by c.CustomerID
order by 2 desc
Zadanie
select CompanyName from Customers
where
 not exists
(
select * from
Products p join Categories c on c.CategoryID=p.CategoryID and
CategoryName='Beverages'
where
not exists
(select * from Orders join [Order Details] on [Order
Details].OrderID=orders.OrderID
where Orders.CustomerId=Customers.CustomerID and [Order
Details].ProductID=p.ProductID)
)
Laboratorium 5 - 4.11.2013
Zadania z Lab3x.pdf
Ad.5)A
select * from Orders where OrderDate between '1996-1-1' and '1996-12-13'
Ad.5)
select * from Employees where Lastname like '%u%'
Ad.5)D
select CompanyName from Customers where CompanyName like 'x%'
Ad.5)E
select CompanyName from Customers where CompanyName like '%x%'
Ad.5)F
select * from Customers where CompanyName like '____________________'
select CompanyName from Customers where len(CompanyName)>20
Ad.5)G
select * from Customers where CompanyName like '%[-]%'
Ad.5)H
select * from Customers where CompanyName like '%[öäüß]%';
Ad.5)I
select * from Customers where CompanyName not like '%[öäüß]%';
Ad.5)J
select * from Products where
floor(UnitPrice/power(10,floor(log10(UnitPrice))))=3
Ad.5)K
select CompanyName from Customers where
not CompanyName like '%[^ abcdefghijklmnoprstuwxyz]%'
Ad.6)A
select sum(Quantity*[Order Details].UnitPrice) [Category Total]
from
[Order Details],Products,Categories
/*select CategoryName, [Catergory Details].UnitPrice) [Category total],
Category ID
*/
where [Order Details].ProductID=Products.ProductID and
Categories.CategoryID=Products.ProductID
group by Categories.CategoryID
/////
select sum(Quantity*[Order Details].UnitPrice) [Category Total],
Categories.CategoryID, Min(CategoryName) as CategName
from [Order Details],Products,Categories
where [Order Details].ProductID=Products.ProductID and
Categories.CategoryID=Products.CategoryID
group by Categories.CategoryID
Ad.6)C
select sum(UnitPrice*Quantity), CustomerID from
[Order Details] od join Orders o on o.OrderID=od.OrderID
where Year(OrderDate)=1997
group by CustomerID
order by 1 desc
Ad.6)D
select top 1 sum(UnitPrice*Quantity), CustomerID from
[Order Details] od join Orders o on o.OrderID=od.OrderID
where Year(OrderDate)=1997
group by CustomerID
order by 1 desc
Laboratorium 6 - 18.11.2013
Lab4x.pdf
Ad.1)
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 month(OrderDate)=9 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
Ad.2)
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 month(OrderDate)=6 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "Czerwiec 1996",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=7 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "Lipiecc 1996",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=8 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "Sierpien 1996",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=9 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "Wrzesien 1996",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=10 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "Pazdziernik 1996",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=11 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "Listopad 1996",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=12 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "Grudzien 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 month(OrderDate)=1 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "styczen 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
Ad.3)
select year(o.OrderDate),
(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)
Ad.4)
select * from customers
select * from Employees
select distinct country from Customers
select distinct Region.RegionDescription from Employees
join EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeID
join Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryID
join Region on Region.RegionID=Territories.RegionID
select sum(quantity*UnitPrice), customers.country, RegionDescription
from [Order Details] join orders on Orders.OrderID=[Order Details].OrderID
join Customers on customers.CustomerID=Orders.CustomerID
join Employees on Employees.EmployeeID=Orders.EmployeeID
join EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeID
join Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryID
join Region on Region.RegionID=Territories.RegionID
group by
Customers.Country,RegionDescription
Ad.5)
select distinct Country from Customers
select distinct Region.RegionDescription from Employees
join EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeID
join Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryID
join Region on Region.RegionID=Territories.RegionID
select sum(Quantity*UnitPrice),Customers.Country,RegionDescription from [Order
Details] join Orders on Orders.OrderID =[Order Details].OrderID
join Customers on Customers.CustomerID=Orders.CustomerID
join Employees on Employees.EmployeeID=Orders.EmployeeID
 join EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeID
join Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryID
join Region on Region.RegionID=Territories.RegionID
group by Customers.Country,RegionDescription
Ad.7)B
use CJDate
select S.CITY,J.CITY,sum(QTY) from S,J,SPJ
where S.S#=SPJ.S# and J.J#=SPJ.J#
group by S.CITY,J.CITY
Laboratorium 7 - 25.11.2013
Zad.1
use CJDate
select count(P#) from SPJ
group by S#
Zad.2
select count(distinct P#) from SPJ
group by S#
Zad.3
select count(distinct P#),max(SNAME)
from SPJ join S on S.S#=SPJ.S#
group by S.S#
;
Zad.4
with nofP as
(
select count(distinct P#) t,max(SNAME) name
from SPJ join S on S.S#=SPJ.S#
group by S.S#
)
select t, name from nofP
where
t>(select avg(t) from nofP)
////////
select count(distinct P#) t,max(SNAME) name
from SPJ join S on S.S#=SPJ.S#
group by S.S#
having count(distinct P#)>
(select avg(t) from
(select count(distinct P#) t,max(SNAME) name
from SPJ join S on S.S#=SPJ.S#
group by S.S#
) nofP)
Zad.5
use CJDate
select j.city,SUM(QTY*[Weight]) from J,SPJ,P
where
 J.J#=SPJ.J# and
SPJ.P#=P.P#
group by (J.CITY)
Zad.6
select * from
(
select YEAR(OrderDate) y,Quantity*od.UnitPrice val,CategoryId
from Orders o, [Order Details]od ,Products p
where o.OrderID=od.OrderID and od.ProductID=p.ProductID
) st
pivot
(sum(val) for CategoryId in ([1],[2],[3],[4],[5],[6],[7],[8])) pt
Lab4x.pdf
Ad.7)E
select count(distinct P#),max(SNAME) from SPJ
join S on S.S#=SPJ.S#
group by S.S#
Ad.7)F
with nofP as
(select count(distinct P#) t ,max(SNAME) name from SPJ
join S on S.S#=SPJ.S#
group by S.S#)
select * from nofP
where t>(select avg(t) from nofP)
Zad.7 Produkt roku
select 'Product of the year:' [Caption],y [Year], (select ProductName from Products
where ProductId=t.p) [Product of the Year] from
(
select Sum(UnitPrice*Quantity) s ,year(OrderDate) y,ProductID p,
RANK() over (partition by year(OrderDate) order by Sum(UnitPrice*Quantity) desc) r
from [Order Details] join Orders on Orders.OrderID=[Order Details].OrderID
group by year(OrderDate),ProductID
) t where t.r=1
Laboratorium 8 - 2.12.2013
Zad.1 Suma dla poszczególnych produktów ze wszystkich lat.
select sum(UnitPrice*Quantity) as suma, ProductID, Year(OrderDate) as year from [Order
Details]
join orders on Orders.OrderID=[Order Details].OrderID
group by ProductID, Year(OrderDate)
order by 3
Zad.2 ID najlepiej sprzedajÄ…cego siÄ™ produktu ze wszystkich lat
select year(o1.OrderDate),
(
select top 1
ProductID from [Order Details]
join orders on Orders.OrderID=[Order Details].OrderID
where
Year(OrderDate) = year(o1.OrderDate)
group by ProductID, Year(OrderDate)
order by sum(Quantity*unitPrice) desc
)
from Orders o1
group by year(o1.OrderDate)
Zad.3 Jeden najlepszy miesiąc w 1996 - grudzień
select top 1
MONTH(OrderDate) from [Order Details]
join Orders on Orders.OrderID=[Order Details].OrderID
where
year (OrderDate)=1996
group by month(OrderDate), year(OrderDate)
order by sum (Quantity*UnitPrice) desc
Zad.4 Najlepsze miesiÄ…ce we wszystkich latach
select year(o1.OrderDate),
(
select top 1
month(OrderDate) from [Order Details]
join Orders on Orders.OrderID=[Order Details].OrderID
where
Year(OrderDate) = year(o1.OrderDate)
group by month(OrderDate), Year(OrderDate)
order by sum(Quantity*unitPrice) desc
)
from Orders o1
group by year(o1.OrderDate)
order by 1
Zad.5 Maksymalne wartości dla wszystkich lat
select max(total), y from
(
Select sum(quantity*unitprice) total,
MONTH(OrderDate) m, year(OrderDate) y from [Order Details]
join Orders on Orders.OrderID=[Order Details].OrderID
group by month(OrderDate), year(OrderDate)
)
tab group by y
Zad.6
select Sum(Quantity*UnitPrice),
Month(OrderDate),Year(OrderDate) from [Order Details]
join Orders on Orders.OrderID=[Order Details].OrderID
group by Month(OrderDate),year(OrderDate)
order by 3,2
Zad.7
with tab as
(
select Sum(Quantity*UnitPrice) total,
EmployeeID,Year(OrderDate) y from [Order Details]
join Orders on Orders.OrderID=[Order Details].OrderID
group by EmployeeID,year(OrderDate)
) select * from tab
Zad.8
with tab as
(
select Sum(Quantity*UnitPrice) total,
EmployeeID,Year(OrderDate) y from [Order Details]
join Orders on Orders.OrderID=[Order Details].OrderID
group by EmployeeID,year(OrderDate)
) select * from tab
where total =(select max(total) from tab tab1 where tab1.y=tab.y)
order by y
Zad.9 Najlepsi pracownicy w poszczególnych latach
with tab as
(
select Sum(Quantity*UnitPrice) total,
EmployeeID,Year(OrderDate) y from [Order Details]
join Orders on Orders.OrderID=[Order Details].OrderID
group by EmployeeID,year(OrderDate)
) select *,(select LastName from Employees where Employees.EmployeeID=tab.EmployeeID)
from tab
where total =(select max(total) from tab tab1 where tab1.y=tab.y)
order by y
Laboratorium 9 - 9.12.2013
Lab5x.pdf
Ad.1) Najlepsze miesiące po względem obrotów dla poszczególnych lat
select year(OrderDate), month(OrderDate) from Orders
group by
year(OrderDate), month(OrderDate)
Zad.1 Najlepsze produkty wg ProductID w poszczególnych miesiącach i latatach.
select year(OrderDate), month(OrderDate),
(
select top 1 ProductID from
Orders join [Order Details] on [Order Details].OrderID=Orders.OrderID
where
year(OrderDate)=year(o.OrderDate) and
month(OrderDate)=month(o.OrderDate)
group by ProductID
order by sum(Quantity*[Order Details].UnitPrice) desc
)
prod
from Orders o group by year(OrderDate), month(OrderDate)
;
Zad.2 Najlepsze kraje w poszczególnych miesiącach i latatach.
select year(OrderDate) as year, month(OrderDate) as month,
(
select top 1 Country from
Orders join [Order Details] on [Order Details].OrderID=Orders.OrderID
join Customers on Customers.CustomerID=Orders.CustomerID
where
year(OrderDate)=year(o.OrderDate) and
month(OrderDate)=month(o.OrderDate)
group by Country
order by sum(Quantity*[Order Details].UnitPrice) desc
)
country
from Orders o group by year(OrderDate), month(OrderDate)
;
Zad.3 To samo co w zad.2 ale wykonane za pomocÄ… Visual Studio
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace ConsoleApplication16
{
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated
Security=SSPI");
conn.Open();
SqlCommand cmd = new SqlCommand("select year(OrderDate),month(OrderDate) from
Orders group by year(OrderDate),month(OrderDate)", conn);
SqlDataReader rdr = cmd.ExecuteReader();
for (int i = 1; rdr.Read(); i++)
{
Console.Write(i);
Console.Write('\t');
Console.Write(rdr.GetInt32(0));
Console.Write('\t');
Console.Write(rdr.GetInt32(1));
Console.WriteLine();
}
conn.Close();
}
}
}
Zad.4
select year(OrderDate),month(OrderDate),CategoryID, sum(Quantity*od.UnitPrice) from
Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
group by year(OrderDate),month(OrderDate),CategoryID
order by 1 asc ,2 asc,4 desc
Zad.5
select year(OrderDate),month(OrderDate),CategoryID, sum(Quantity*od.UnitPrice),
rank() over (partition by year(OrderDate),month(OrderDate) order by
sum(Quantity*od.UnitPrice) desc)
from
Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
group by year(OrderDate),month(OrderDate),CategoryID
;
Zad.6
with t as
(
select year(OrderDate) y,month(OrderDate) m,CategoryID c,
rank() over (partition by year(OrderDate),month(OrderDate) order by
sum(Quantity*od.UnitPrice) desc) r
from
Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
group by year(OrderDate),month(OrderDate),CategoryID
)
select * from t where t.r=1
;
Zad.7 Kategoria miesiÄ…ca
with t as
(
select year(OrderDate) y,month(OrderDate) m,CategoryID,
rank() over (partition by year(OrderDate),month(OrderDate) order by
sum(Quantity*od.UnitPrice) desc) r
from
Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
group by year(OrderDate),month(OrderDate),CategoryID
)
select *,(select CategoryName from Categories c where c.CategoryID=t.CategoryID)
[Category of the Month]
from t where t.r=1
Laboratorium 10 - 16.12.2013
Lab6x.pdf
Ad.1)A
select * from P where city ='London'
Ad.1)B
select P# from P where city ='London'
Ad.1)C
select distinct J#,P# from SPJ
Ad.1)D
select J#, (select COUNT(P#) from P where city = 'London'),
(select count(distinct P.P#) from P join SPJ on P.P#=SPJ.P# where city = 'london'
and J.J#=SPJ.J#) from J
Ad.1)E
select * from J
where
(select COUNT(P#) from P where city = 'London')
= (select count(distinct P.P#) from P join SPJ on P.P#=SPJ.P# where city = 'london'
and j.j#=SPJ.J#)
Ad.1)F
select j# from j
except
select j# from
(select j#, p# from j, p where p.city = 'london'
except
select j#, p# from spj
)
t
Ad.2)B
select j# from j where
not exists
( -- lista czesci z londynu
select * from
P left outer join SPJ on P.P#=SPJ.P# and SPJ.J#=J.J#
where city = 'london' and spj.p# is null
)
Ad.2)C Nic nie wypisało
select * from
P left outer join SPJ on P.P#=SPJ.P# and SPJ.J#='J4'
where city = 'london' and SPJ.P# is null
Ad.3 Wypisanie par S i J
select s#, j# from s, j
where
-- s dostarcza all co j odbierze
not exists
(
select P# from SPJ where SPJ.J#=J.J#
except
select P# from SPJ where SPJ.S#=S.S#
)
Zad.1
select S#,J# from S,J
except
select S#,J# from
(
select S.S#,SPJ.J#,SPJ.P# from SPJ,S
except
select S#,J#,P# from SPJ
) t
Zad.2
select S#,J# from S,J
except
select S#,J# from
(
select S.S#,SPJ.J#,SPJ.P# from SPJ,S
except
select SPJ.S#,J.J#,SPJ.P# from SPJ,J
) t
Zad.3 Baza Northwind.
Nie wyszukało pracownika, ani klienta który by wszystko kupował, albo błędy w kodzie
select EmployeeID from Employees
where
not exists
(
select ProductID from Products
except
select ProductID from Orders join [Order Details] on Orders.OrderID=[Order
Details].OrderID
and Orders.EmployeeID=Employees.EmployeeID
)