USE [master]
GO
CREATE LOGIN [ZAL] WITH PASSWORD=N'12345', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Northwind]
GO
CREATE USER [ZAL] FOR LOGIN [ZAL]
GO
GRANT INSERT, SELECT ON [dbo].[CATEGORIES] to [ZAL]
go
GRANT INSERT, SELECT ON [dbo].[Products] to [ZAL]
go
GRANT INSERT, SELECT ON [dbo].[ORDER DETAILS] to [ZAL]

--1
sp_addlinkedserver @server='OrclDB'
					,@srvproduct='Oracle'
					,@provider='OraOLEDB.Oracle'
					,@datasrc='hw12'

sp_addlinkedsrvlogin @rmtsrvname='OrclDB'
					 ,@useself='FALSE'
					 ,@locallogin='ZAL'
					 ,@rmtuser='pd202115'
					 ,@rmtpassword='12345'

--2

--z oracle
SELECT * FROM OPENQUERY (OrclDB, 'select OrderID, max(UnitPrice*Quantity) as najwyzsza from NORTHWIND.[ORDER DETAILS] where rownum = 1
group by OrderID
order by najwyzsza desc');

--lokalnie
select top 1 OrderID, max(UnitPrice*Quantity) as najwyzsza from [Order Details]
group by OrderID
order by najwyzsza desc


--3
select * from products
select * from Categories

--testowa procedura (lokalnie)
create procedure PROC2
(
@Kategoria nvarchar(50)
)as
(select c.CategoryName, count(p.CategoryID) as [liczba produktow] from Products p
join Categories c on c.CategoryID=p.CategoryID
where c.CategoryName like @Kategoria
group by c.CategoryName
)

exec PROC2 'Beverages'

--procedura z openrowset
create procedure PROC2
(
@Kategoria nvarchar(50)
)as
(select c.CategoryName, count(p.CategoryID) as [liczba produktow] from Products p
inner join OPENROWSET('OraOLEDB.Oracle', 'hw12';'HR';'hr',
     'SELECT xxx.CategoryName, xxx.CategoryID
      FROM Northwind.dbo.Categories xxx') as c on c.CategoryID=p.CategoryID
where c.CategoryName like @Kategoria
group by c.CategoryName
)

exec PROC2 'Beverages'


--5

--lokalnie
select ProductName, UnitPrice from Products
group by ProductName, UnitPrice
having UnitPrice>(select avg(UnitPrice) from Products)




--Z
Select *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\Folder\Northwind.mdb';'admin';'',
     'SELECT xx.NazwaProduktu as ProductName xx.CenaJednostkowa as UnitPrice
      FROM Northwind.dbo.Produkty xx')  p
group by p.ProductName, p.UnitPrice
having p.UnitPrice>(select avg(UnitPrice) from Products)