Laboratorium Baz Danych
Wykonał: Wojciech Ćwikliński
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

)