use AdventureWorks2012 ;

---------------------------------------------------------------------------------
-- Abschnitt 7.1.4
-- In AdventureWorks2008R2 ausführbar.

select * from HumanResources.Shift ;

select ShiftID as id, StartTime as start, EndTime as end, Name
	from HumanResources.Shift ;

select * 
	from HumanResources.Department 
	where DepartmentID between 1 and 6 ; 

select * 
	from HumanResources.Department 
	where DepartmentID < 7 ;

select * 
	from HumanResources.Department 
	where DepartmentID <= 6 ;

select DepartmentID as ID, Name, ModifiedDate
	from HumanResources.Department 
	where GroupName = 'Manufacturing' ;

select DepartmentID as ID, Name, ModifiedDate            
	from HumanResources.Department 
	where ModifiedDate = '20020601' and 
		  ( DepartmentID = 4 or 
            DepartmentID = 5 ); 

select v.AccountNumber as Nr, v.Name, v.CreditRating as Cred,
		v.PreferredVendorStatus as VendStat, 
		v.ActiveFlag as Active, v.PurchasingWebServiceURL as URL
	from Purchasing.Vendor as v
	where v.PurchasingWebServiceURL is not null ;

select distinct p.FirstName + ' ' + p.LastName as Name
	from Person.Person as p 
	where p.FirstName not like '%a%' 
	order by Name ;

select distinct p.FirstName + ' ' + p.LastName as Name
	from Person.Person as p 
	where p.LastName like 'Anders_n' ;

---------------------------------------------------------------------------------
-- Abschnitt 7.1.5.7
select distinct d.Name + '/' + d.GroupName as "Group", 
				s.Name as Shift
	from HumanResources.Department as d inner join
		HumanResources.EmployeeDepartmentHistory as h on
			h.DepartmentID = d.DepartmentID inner join
		HumanResources.Shift as s on
			h.ShiftID = s.ShiftID
	order by "Group" ;

select distinct p.Name
	from Sales.SalesOrderDetail as o inner join
		Production.Product as p on
			o.ProductID = p.ProductID
	where DaysToManufacture >= 4 and
		ListPrice > 3500 and
		CarrierTrackingNumber is not null and 
		OrderQty > 4 ;

select c.Name, fromCurrencyCode, toCurrencyCode
	from Sales.Currency as c left join
		Sales.CurrencyRate as r on
			c.CurrencyCode = r.fromCurrencyCode ;


---------------------------------------------------------------------------------
-- Abschnitt 7.1.11
select SalesOrderID OrderDate, DueDate, ShipDate, CustomerID 
	from Sales.SalesOrderHeader
	order by OrderDate desc, DueDate ;

select distinct City
	from Sales.Store as s inner join
		Person.BusinessEntityAddress as ea on
			s.BusinessEntityID = ea.BusinessEntityID inner join
		Person.Address as a on
			ea.AddressID = a.AddressID ;

select distinct City
	from Sales.Store as s inner join
		Person.BusinessEntityAddress as ea on
			s.BusinessEntityID = ea.BusinessEntityID inner join
		Person.Address as a on
			ea.AddressID = a.AddressID ;

select distinct top 5 percent City
	from Sales.Store as s inner join
		Person.BusinessEntityAddress as ea on
			s.BusinessEntityID = ea.BusinessEntityID inner join
		Person.Address as a on
			ea.AddressID = a.AddressID 
	order by City;

select distinct top 5 percent City
	from Sales.Store as s inner join
		Person.BusinessEntityAddress as ea on
			s.BusinessEntityID = ea.BusinessEntityID inner join
		Person.Address as a on
			ea.AddressID = a.AddressID 
	order by City;

---------------------------------------------------------------------------------
-- Abschnitt 7.1.15
select count(*) as "Count", min(SubTotal) as MinSubTot,
		max(TaxAmt) as MaxTax, avg(Freight) as AvgFreight,
		sum(TotalDue) as GrandTotal
	from Sales.SalesOrderHeader as h 
	where year ( orderdate ) = 2008 ; 

select count(*) as Products, 
		count (SizeUnitMeasureCode) as WithUnit, 
		count (distinct SizeUnitMeasureCode) as Units
	from Production.Product as p ;

select ProductID, count(*) as "Count", 
		min (UnitPrice) as "Min", max (UnitPrice) as "Max", 
		avg ( UnitPrice ) as "Avg", stdev ( UnitPrice ) as "StDev"
	from Purchasing.PurchaseOrderDetail
	group by ProductID ;

select ProductID, count(*) as "Count", 
		min (UnitPrice) as "Min", max (UnitPrice) as "Max", 
		avg ( UnitPrice ) as "Avg", stdev ( UnitPrice ) as "StDev"
	from Purchasing.PurchaseOrderDetail
	group by ProductID 
	having stdev ( UnitPrice ) is not null ;

---------------------------------------------------------------------------------
-- Abschnitt 7.1.19
select p.ProductID, p.Name, p.ProductNumber
	from Production.Product as p
	where p.ProductID not in (
		select distinct ProductID
			from Sales.SalesOrderDetail as d inner join
				Sales.SalesOrderHeader as h on
					d.SalesOrderID = h.SalesOrderID
			where year ( h.OrderDate ) = 2007 and
				month ( h.OrderDate ) < 10
		) ;

select AccountNumber, Name, 
		( select count(*) from Purchasing.PurchaseOrderHeader
			 where VendorID = v.BusinessEntityID ) as Purchases
	from Purchasing.Vendor as v ;

select p.FirstName + ' ' + p.LastName as Name, AvgDue
	from Sales.Customer as c inner join
		Person.Person as p on
			c.CustomerID = p.BusinessENtityID inner join
		( select CustomerID, count(*) as Sales, 
					avg ( TotalDue ) as AvgDue
				from Sales.SalesOrderHeader as h
				group by CustomerID ) as stat on
			c.CustomerID = stat.CustomerID
	where Sales > 1 ;

-- funktioniert NICHT:
select distinct CustomerID
	from Sales.SalesOrderHeader
	where TotalDue > avg ( TotalDue ) ;

select distinct CustomerID
	from Sales.SalesOrderHeader
	where TotalDue > ( 
		select avg ( TotalDue ) 
			from Sales.SalesOrderHeader 
		) ;

select CustomerID, PersonID, StoreID, TerritoryID
	from Sales.Customer
	where CustomerID in (
		select distinct CustomerID
			from Sales.SalesOrderHeader
			where TotalDue > ( 
				select avg ( TotalDue ) 
					from Sales.SalesOrderHeader 
				)
		) ;

select distinct EmailAddress
	from Production.ProductReview
union
select distinct EmailAddress
	from Person.EmailAddress as m inner join
		Production.Document as d on
			m.BusinessEntityID = d.Owner ;

select p.FirstName, p.LastName, m.EmailAddress
	from Person.Person as p inner join
		Person.EmailAddress as m on
			p.BusinessEntityID = m.BusinessEntityID
	where p.BusinessEntityID in (
		select BusinessEntityID
			from HumanResources.Employee
		except
		select distinct Owner
			from Production.Document ) ;

select distinct BillToAddressID
	from Sales.SalesOrderHeader
except
select distinct ShipToAddressID
	from Sales.SalesOrderHeader ;

select distinct BillToAddressID
	from Sales.SalesOrderHeader
union
select distinct ShipToAddressID
	from Sales.SalesOrderHeader ;

select distinct BillToAddressID
	from Sales.SalesOrderHeader
intersect
select distinct ShipToAddressID
	from Sales.SalesOrderHeader ;

select Name
	from Production.Product as p
	where not exists (
			select *
				from Production.ProductPhoto
				where ProductID = p.ProductID 
			);

select ProductID, Name, ProductNumber             
	from Production.Product as p
	where ReorderPoint < any ( 
			select distinct OrderQty 
				from Sales.SalesOrderDetail
				where ProductID = p.ProductID
			);

select ProductID, Name, ProductNumber             
	from Production.Product as p
	where ReorderPoint < some ( 
			select distinct OrderQty 
				from Sales.SalesOrderDetail
				where ProductID = p.ProductID
			);

select ProductID, Name, ProductNumber             
	from Production.Product as p
	where ReorderPoint < all ( 
			select distinct OrderQty 
				from Sales.SalesOrderDetail
				where ProductID = p.ProductID
			);

select ProductID, Name, ProductNumber             
	from Production.Product as p
	where ReorderPoint > all ( 
			select distinct OrderQty 
				from Sales.SalesOrderDetail
				where ProductID = p.ProductID
			)
intersect
select ProductID, Name, ProductNumber             
	from Production.Product as p
	where ReorderPoint < all ( 
			select distinct OrderQty 
				from Sales.SalesOrderDetail
				where ProductID = p.ProductID
			);

select ProductID, Name, ProductNumber             
	from Production.Product as p
	where ReorderPoint < all ( 
			select distinct OrderQty 
				from Sales.SalesOrderDetail
				where ProductID = p.ProductID
			) and
		exists ( select *
					from Sales.SalesOrderDetail
					where ProductID = p.ProductID
			  ) ;

---------------------------------------------------------------------------------
-- Abschnitt 7.1.22

select convert ( char(10), getdate(), 104 ) as DateString, 
		convert ( char(8),  getdate(), 108 ) as TimeString ;

select sysdatetime(), sysdatetimeoffset(), sysutcdatetime(),
	current_timestamp, getdate(), getutcdate() ;

select day(getdate()), month(getdate()), year(getdate()) ;

select datepart ( yy, getdate() ),  datepart ( yyyy, getdate() ),
	 datepart ( m, getdate() ),  datepart ( mm, getdate() ), 
	 datepart ( d, getdate() ),  datepart ( dd, getdate() ), 
	 datepart ( y, getdate() ),  datepart ( dy, getdate() ), 
	 datepart ( q, getdate() ),  datepart ( qq, getdate() ), 
	 datepart ( wk, getdate() ), datepart ( ww, getdate() ), 
	 datepart ( dw, getdate() ) ;

select datediff ( mi , '00011224 00:00' , getdate() ) ;

select datediff ( dd , '20141223 23:59' , '20141224 00:00' ) ,
	datediff ( mi, '20141223 23:59' , '20141224 00:00' );

select char ( 65 ) ;		

select ascii ( 'A' );

select len ( 'Einführung in Datenbanken & SQL' ) ;

select ltrim ( '  x  ' ), rtrim ( '  x  ' ), 
		len ( ltrim ( '       ' ) ) ;

select charindex ( '&', 'Einführung in Datenbanken & SQL' ) ;

select replace ( 'Einführung in Datenbanken & SQL' , 
				 '&' , 'und' ) ;

select replace ( 
	replace ( 
		replace ( 'Einführung in Datenbanken & SQL','&','und' ),
		    'Datenbanken', 'DB' ), 
		'SQL', 'Structured Query Language' );

select stuff ( 'Einführung in Datenbanken & SQL', 15, 0, 
			   'die wunderbare Welt der ' ) ;

select left ( 'Einführung in Datenbanken & SQL', 10 ) ,  
		right ( 'Einführung in Datenbanken & SQL', 3 ) ;

select left ( 'Einführung in Datenbanken & SQL', 
			  charindex (
                  ' ', 'Einführung in Datenbanken & SQL' 
              )-1 
       ) ;

select right ( 'Einführung in Datenbanken & SQL', 
               len ( 'Einführung in Datenbanken & SQL') -
        charindex ( ' ', 'Einführung in Datenbanken & SQL' )  ) ;

select left ( right ( 'Einführung in Datenbanken & SQL', len ( 'Einführung in Datenbanken & SQL')-charindex ( ' ', 'Einführung in Datenbanken & SQL')),charindex (' ', right ( 'Einführung in Datenbanken & SQL',len ( 'Einführung in Datenbanken & SQL') -charindex ( ' ', 'Einführung in Datenbanken & SQL' ) ) )-1 ) ;

---------------------------------------------------------------------------------
-- Abschnitt 7.2.1
insert into production.unitmeasure ( unitmeasurecode, name )
	values ( 'µm', 'Mikrometer' ) ;

insert into production.unitmeasure ( unitmeasurecode, name )
	values ( 'nm', 'Nanometer' ) ,
		( 'pm', 'Picometer' ) ,
		( 'fm', 'Femtometer' ) ,
		( 'µ', 'Mikron' ) ;

insert into production.unitmeasure ( unitmeasurecode, name )
	select FirstName, LastName
		from HumanResources.Employee as e inner join
			Person.Person as p on
				e.BusinessEntityID = p.BusinessEntityID
		where e.BusinessENtityID = 1 ;

insert into production.unitmeasure ( unitmeasurecode, name )
	select left ( FirstName, 3) , LastName
		from HumanResources.Employee as e inner join
			Person.Person as p on
				e.BusinessEntityID = p.BusinessEntityID
		where e.BusinessENtityID between 4 and 10 ;

---------------------------------------------------------------------------------
-- Abschnitt 7.3.1
select p.ProductID, c.Name as Category, s.Name as Subcategory,
		p.Name as Product, p.ProductNumber, p.ListPrice
	into wrkBikesAccessories
	from Production.ProductCategory as c inner join
		Production.ProductSubcategory as s on
			s.ProductCategoryID = c.ProductCategoryID inner join
		Production.Product as p on
			p.ProductSubcategoryID = s.ProductSubcategoryID
	where c.Name in ( 'Bikes', 'Accessories' ) 
	order by c.Name, s.Name, p.Name ;

---------------------------------------------------------------------------------
-- Abschnitt 7.4.1
-- drop table MyEmployees;

create table MyEmployees (
	EmployeeID smallint primary key,
	FirstName varchar(30) not null,
	LastName varchar(40) not null,
	Title varchar(50) not null,
	ManagerID smallint null references MyEmployees ( EmployeeID )
);

-- Tabelle mit Daten populieren:
insert into MyEmployees values 
	(1,   'Ken',   'Sánchez', 'Chief Executive Officer', null),
	(273, 'Brian', 'Welcker', 'Vice President of Sales', 1),
	(274, 'Stephen', 'Jiang', 'North American Sales Mgr', 273 ),
	(275, 'Michael', 'Blythe','Sales Representative', 274),
	(276, 'Linda', 'Mitchell','Sales Representative', 274),
	(285, 'Syed',  'Abbas','Pacific Sales Manager', 273),
	(286, 'Lyn',  'Tsoflias','Sales Representative', 91),
	(16,  'David', 'Bradley', 'Marketing Manager', 273),
	(23,  'Mary',  'Gibson', 'Marketing Specialist', 16),
	(91,  'Clara', 'O''Connor', 'CTO', null);

-- "CTE" - alles bis zum go ist /ein/ Statement!
with /*pgSQL: RECURSIVE*/ DirectReports (
		ManagerID, EmployeeID, Title, Level )
as
( -- 1. Generation von Datensätzen: Mitarbeiterdaten
	select e.ManagerID, e.EmployeeID, e.Title,0 AS Level
		from MyEmployees as e 
		where EmployeeID = 1
	union all
	-- Weitere Generationen: CTE rekursiv mit Datensätzen bestücken
	select e.ManagerID, e.EmployeeID, e.Title, Level + 1
		from MyEmployees as e inner join 
			DirectReports as d on 
				e.ManagerID = d.EmployeeID
)
-- Ausführung der CTE
select d.level, e.employeeID, e.firstname, e.lastname,
		e.title as position,d.employeeID as reportsToID,
		d.title as reportsToPos
	from DirectReports as d inner join
		MyEmployees as e on
			e.ManagerID = d.EmployeeID ;
go

---------------------------------------------------------------------------------
-- Abschnitt 7.5.1
update wrkBikesAccessories 
	set ListPrice = 25.90,
		Product = 'Touring Tyre'
	-- select * from wrkBikesAccessories 
	where Product = 'Touring Tire' ;
	
update wrkBikesAccessories 
	set Product = replace ( Product, 'tire', 'tyre' )
	--select * from wrkBikesAccessories 
	where Product like '%tire%' ;

update wrkBikesAccessories 
	set Product = replace ( Product, 'gray', 'grey' )
	--select * from wrkBikesAccessories 
	where Product like '%gray%' ;
	
update wrkBikesAccessories 
	set Product = replace (
					replace ( Product, 'tyre', 'tire' ),
					'grey', 'gray'
				  ),
		Subcategory = replace (
						replace ( Subcategory, 'tyre', 'tire' ),
						'grey', 'gray' 
				  	 );
					 
update wrkBikesAccessories 
	set ListPrice *= 0.95
	-- select s, w.*
	from wrkBikesAccessories as w inner join
		 ( select d.ProductID, sum ( d.OrderQty ) as s
			from Sales.SalesOrderDetail as d 
			group by d.ProductID 
		 ) as stat on
			w.ProductID = stat.ProductID
	where s < 400 ;		

update wrkBikesAccessories 
	set ListPrice *= 0.95
	-- select * from wrkBikesAccessories 
	where ProductID in (
			select d.ProductID as s
				from Sales.SalesOrderDetail as d 
				group by d.ProductID
				having sum ( OrderQty ) < 400 ) ;

---------------------------------------------------------------------------------
-- Abschnitt 7.6.1

delete production.unitmeasure
	where unitmeasurecode like '%µ%' ;

delete production.unitmeasure
	-- select * from production.unitmeasure
	where unitmeasurecode in (
			select distinct left ( p.firstname, 3 ) 
				from person.person as p 
				where p.BusinessEntityID between 4 and 10 
			);

delete production.unitmeasure
	-- select * 
	from production.unitmeasure as m inner join
		person.person as p on
			m.unitmeasurecode = left ( p.firstname, 3 ) and
			p.BusinessEntityID between 4 and 10 ;

merge into Production.Product as trg
	using wrkBikesAccessories as src
	on ( trg.ProductID = src.ProductID )

	when matched then update 
		set ListPrice = src.ListPrice,
			Name = src.Product

	when not matched by target then
		insert ( Name, ListPrice, ProductNumber, 
				SafetyStockLevel, ReorderPoint, StandardCost, 
				DaysToManufacture, SellStartdate )
			values ( src.Product, src.ListPrice, src.Number, 
					 200, 250, 23.90, 
					 1, '20150101' ) ; 
