Full Text Catalog

use Master
go
Create database FullTextExample
go
Alter Database FullTextExample
Add Filegroup FullTextCatalog
Go
Use FullTextExample
Go
Create Table Test
(
   testID int identity(1,1) primary Key,
   TestText Nvarchar(255)
)
Go
Insert into Test(TestText)
Values('For test to be successful we must have a lot of text'),
('The test was not successful. sad face'),
('there is more than one test that can try a man'),
('Success is a relative term'),
('It is a rare man that is always successful'),
('The root of satisfaction is sad'),
('men want success')

Insert into Test(TestText)
Values('I go to work sadly'),
('I went to work yesterday'),
('I have gone to work every day'),
('going to work')

Select * From test

Create FullText Catalog TestDescription
on Filegroup FullTextCatalog
Go
Create FullText index on Test(TestText)
Key Index [PK__Test__A29BFBA819F32655]
on TestDescription
With Change_tracking auto
go
--find all instances that have the word "sad"
Select TestID, TestText 
From Test
Where FreeText(TestText, 'sad')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'success')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'men')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'relative')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'Success')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'is')

Select TestID, TestText 
From Test
Where Contains(TestText, '"success*"')


Select TestID, TestText 
From Test
Where Contains(TestText, ' Formsof (Inflectional, see)')

Select TestID, TestText 
From Test
Where Contains(TestText, ' Formsof (Inflectional, go)')

Select TestID, TestText 
From Test
Where Contains(TestText, 'Near ((work, day), max)')

Select * From Test

Data Warehouse SQL Script

Use master
go
if exists (Select name from sys.Databases 
where name='MetroAltDw')
Begin
Drop Database MetroAltDW
End
Go
Create Database MetroAltDW
Go
Use MetroAltDW
Go
Create table DimEmployee
(
   DimEmployeeKey int identity(1,1),
   EmployeeKey int unique,
   EmployeelastName nvarchar(255),
   EmployeeFirstName nvarchar(255),
   EmployeeEmail nvarchar(255),
   EmployeeCity nvarchar(255),
   EmployeeZipCode nchar(5),
   EmployeeHireDate date,
   PositionName nvarchar(255),
   EmployeeHourlyPayRate decimal(5,2)

)
go
Create table DimBus
(
    DimBusKey int identity(1,1),
 BusKey int unique,
 BusPurchaseDate Date,
 BusTypeDescription nvarchar(255),
 BusTypeCapacity int,
 BusTypePurchaseprice decimal(12,2)
)
Go
Create table DimRoute
(
 DimRouteKey int identity(1,1),
 BusrouteKey int unique,
 BusRouteZone nvarchar(255)
)
go
Create table DimBusScheduleAssignment
(
   DimBusScheduleAssignmentKey int identity(1,1),
   BusScheduleAssignmentKey int unique,
   ShiftName nvarchar(255)
   
)
go
Create table DimDate
(
 DimDateKey int not null,
 BusScheduleAssignmentDate Date unique,
 BusScheduleAssignmentYear int,
 BusScheduleAssignmentMonth int,
 BusScheduleAssignmentDay int,
 BusScheduleAssignmentDayOfWeek nvarchar(30)

)

go

Create table FactBusSchedule
(
 DimEmployeeKey int not null,
 DimBusKey int not null,
 DimRouteKey int not null,
 DimBusScheduleAssignmentKey int not null,
 DimDateKey int not null,
 Riders int,
 FareAmount money,
 FareImplementationDateYear int
)
Go
alter table DimEmployee
Add Constraint PK_DimEmployee 
primary key(DimEmployeeKey)

alter table DimBus
Add Constraint PK_DimBus 
primary key(DimBusKey)

alter table DimRoute
Add Constraint PK_DimRoute 
primary key(DimRouteKey)

alter table DimBusScheduleAssignment
Add Constraint PK_DimBusScheduleAssignment 
primary key(DimBusScheduleAssignmentKey)

alter table DimDate
Add Constraint PK_DimDate 
primary key(DimDateKey)

alter table FactBusSchedule
Add Constraint PK_FactBusSchedule 
primary key(DimEmployeeKey, DimRouteKey,
DimBusKey, DimBusScheduleAssignmentKey,
DimDateKey)

Alter table FactBusSchedule
Add Constraint FK_DimEmployee
Foreign Key (DimEmployeeKey) 
References DimEmployee(DimEmployeeKey)

Alter table FactBusSchedule
Add Constraint FK_DimBus
Foreign Key (DimBusKey) 
References DimBus(DimBusKey)

Alter table FactBusSchedule
Add Constraint FK_DimRoute
Foreign Key (DimRouteKey) 
References DimRoute(DimRouteKey)

Alter table FactBusSchedule
Add Constraint FK_DimBusScheduleAssignment
Foreign Key (DimBusScheduleAssignmentKey) 
References DimBusScheduleAssignment(DimBusScheduleAssignmentKey)

Alter table FactBusSchedule
Add Constraint FK_DimDate
Foreign Key (DimDateKey) 
References DimDate(DimDateKey)

XML

here is the xml file we made: memo.xml

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="memo.xslt"?>
<m:memo xmlns:m="http://www.MetroAlt.com/memo">
 <m:heading>
  <m:to>Drivers</m:to>
  <m:from>Dispatchers</m:from>
  <m:about>Road Closures</m:about>
  <m:date>2015-07-28</m:date>
 </m:heading>
 <m:body>
  <m:p>There are several road closures this week.</m:p>
  <m:p>Watch for bulletons and notices.</m:p>
 </m:body>
</m:memo>

here is the schema that describes the xml document: memo.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="to" type="xs:string" />
       <xs:element name="from" type="xs:string" />
       <xs:element name="about" type="xs:string" />
       <xs:element name="date" type="xs:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Here is the xslt file: memo.xslt

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="to" type="xs:string" />
       <xs:element name="from" type="xs:string" />
       <xs:element name="about" type="xs:string" />
       <xs:element name="date" type="xs:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Here are the files we did for SQL Server

Create xml Schema collection sch_memo
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="to" type="xs:string" />
       <xs:element name="from" type="xs:string" />
       <xs:element name="about" type="xs:string" />
       <xs:element name="date" type="xs:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>'

Create table memos
(
   memoId int identity(1,1),
   memoText xml(sch_memo)
)

Insert into memos (memoText)
Values('<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="memo.xslt"?>
<m:memo xmlns:m="http://www.MetroAlt.com/memo">
 <m:heading>
  <m:from>Dispatchers</m:from>
  <m:to>Drivers</m:to>
  <m:about>Road Closures</m:about>
  <m:date>2015-07-28</m:date>
 </m:heading>
 <m:body>
  <m:p>There are several road closures this week.</m:p>
  <m:p>Watch for bulletons and notices.</m:p>
 </m:body>
</m:memo>')

Select * from Employee 
Where EmployeeCity='Kent'
For xml raw('employee'), elements, root('employees')


Select PositionName, Employeelastname, EmployeeFirstName, EmployeeEmail
From Position
inner join EmployeePosition
on Position.PositionKey=EmployeePosition.PositionKey
inner Join Employee
on Employee.EmployeeKey=EmployeePosition.EmployeeKey
Where EmployeeCity='Bellevue'
order by PositionName
for xml auto, elements, root('Employees')

use CommunityAssist
Select GrantReviewdate, EmployeeKey, GrantKey, 
GrantReviewNote.query('declare namespace rn = "http://www.communityassist.org/reviewnotes";//rn:reviewnote/rn:recommendation') as recommendations
from GrantReview

Security Stored Procedures

Select * from Position
--Driver access to own information
--personal information
--shifts driven
--pay per month
--pay per year
--pay per shift
--select route schedules
--select Bus Barn
go
Create schema DriverSchema
go
Create proc DriverSchema.usp_ViewEmployeeInfo
@EmployeeID int
As
Select e.EmployeeKey [Employee Number], 
EmployeeLastName [Last Name], 
EmployeeFirstName [First Name], 
EmployeeAddress [Address], 
EmployeeCity [City], 
EmployeeZipCode [Zip Code], 
EmployeePhone [Phone], 
EmployeeEmail [Email], 
EmployeeHireDate [Hire Date],
PositionName [Position],
EmployeeHourlyPayRate [Pay Rate]
From Employee e
inner Join EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner Join Position p
on p.PositionKey=ep.PositionKey
Where e.EmployeeKey=@EmployeeID

exec DriverSchema.usp_ViewEmployeeInfo 1

Go
create proc DriverSchema.usp_shiftsDrive
@month int,
@year int,
@EmployeeID int
As
Select
[BusScheduleAssignmentDate] [Date],
[BusDriverShiftName] [Shift],
[BusRouteKey] [Route],
[BusKey] [Bus],
[BusDriverShiftStartTime] [Start],
[BusDriverShiftStopTime] [Stop],
DateDiff(hh,[BusDriverShiftStartTime],[BusDriverShiftStopTime]) [hours]
From [dbo].[BusScheduleAssignment] bsa
inner Join BusDriverShift bs
on bsa.BusDriverShiftKey=bs.BusDriverShiftKey
Where Year([BusScheduleAssignmentDate])=@Year
And Month([BusScheduleAssignmentDate])=@Month
And bsa.EmployeeKey=@EmployeeID

exec DriverSchema.usp_shiftsDrive
@Year=2014,
@Month=7,
@EmployeeID=1
go
Create Proc DriverSchema.usp_UpdatePersonal
@LastName nvarchar(255),
@FirstName nvarchar(255),
@Address nvarchar(255),
@City nvarchar(255)='Seattle',
@Zip nchar(5),
@Phone nchar(10),
@EmployeeId int
As
if exists
  (Select EmployeeKey from Employee where EmployeeKey=@EmployeeID)
Begin
Update Employee
Set [EmployeeLastName]=@Lastname,
[EmployeeFirstName]=@firstName,
[EmployeeAddress]=@Address,
[EmployeeCity]=@City,
[EmployeeZipCode]=@zip,
[EmployeePhone]=@phone
Where EmployeeKey = @EmployeeID
return 1
End
Else
Begin
Declare @msg nvarchar(30)
Set @msg='Employee Doesn''t Exist'
Print @msg
return 0
end
go
exec DriverSchema.usp_UpdatePersonal
@LastName = 'Kenner-Jones' ,
@FirstName = 'Susanne',
@Address = '234 Some Other Street',
@City ='Seattle',
@Zip= '98100',
@Phone='2065554312',
@EmployeeId =600

Select * From Employee

Create login KJSusa with password='@Passw0rd1'

Use Metroalt 
Create user KJSusa for login KJSusa with default_Schema=DriverSchema

Create role DriverRole

Grant exec, select on Schema::DriverSchema
to DriverRole

exec sp_addrolemember 'DriverRole', 'KJSusa'

SQL For Security

--see who the employees are
Select *
From Person p
inner join Employee e
on p.PersonKey = e.PersonKey

--new login
Create Login TinaMoon with password='password'

--if you had not already created it
Create schema EmployeeSchema

--user for CommunityAssist
Create user TinaMoon for Login TinaMoon
 
--new role
 Create role HumanResourcesRole

--Permission for the role
 Grant select, insert, update on Employee to HumanResourcesRole
 Grant select, insert, update on Person
 To HumanResourcesRole
 Grant select, insert, update on PersonAddress to HumanResourcesRole
 Grant Select, insert, update on
 PersonContact to HumanResourcesRole
Grant exec on usp_newDonation to HumanResourcesRole
Grant select on Schema::EmployeeSchema to HumanResourcesRole

--add use to the role
exec sp_addrolemember 'HumanResourcesRole','TinaMoon'

Queries and Procedures for Community Assist

--community Assist queries and procedures
use CommunityAssist

--Total Donations by year and month
--Total Grants requested amount allocated
--total grants by Service
--How long the employees have worked 
--Total grants per recipient--compared to lifetime maximums
go
create view vw_TotalDonations
AS
Select Year(DonationDate) as [Year],
Month(donationDate) as [Month],
Sum(donationAmount) as [Total]
From Donation
Group by Year(DonationDate), 
Month(donationDate)
go

Select * From vw_TotalDonations
go
Alter view vw_TotalGrantsByService
as
Select ServiceName as [Service],
Sum(GrantAmount) as Requested,
Sum (GrantAllocation) as Allocated,
Sum(GrantAmount)-Sum(GrantAllocation) 
as [Difference]
From CommunityService cs
inner Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
group by ServiceName
Go
Create Proc usp_TotalGrantsByService
@serviceName Nvarchar(255)
As
Select ServiceName as [Service],
Sum(GrantAmount) as Requested,
Sum (GrantAllocation) as Allocated,
Sum(GrantAmount)-Sum(GrantAllocation) 
as [Difference]
From CommunityService cs
inner Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
Where ServiceName = @ServiceName
group by ServiceName

exec usp_TotalGrantsByService 'food'

Select min(EmployeeHireDate) from Employee
go
Create view vw_EmployeeInfo
As
Select PersonLastName as [Last Name],
PersonFirstName as [First Name],
EmployeeStatus as [Status],
EmployeeMonthlySalary [Monthly Salary],
EmployeeHireDate HireDate,
DateDiff(yy,EmployeeHireDate, GetDate())
As [Years With Charity]
From Person p
Inner join Employee e
on p.PersonKey=e.PersonKey

Select * from vw_EmployeeInfo
order by [Years With Charity] desc
go
create view vw_GrantsAndServiceMaximums
As
Select PersonFirstName [First Name],
PersonLastName [Last Name],
ServiceName [Service],
ServiceLifeTimeMaximum [Maximum],
sum(GrantAllocation) Allocation
From Person p
inner join ServiceGrant sg
on p.personKey=sg.Personkey
inner join communityService cs
on cs.servicekey=sg.servicekey
Where GrantAllocation is not null
And not GrantAllocation =0
group by personFirstname,
personlastname,
ServiceName,
ServiceLifeTimeMaximum

The query for Metro alt ridership and fare totals

use MetroAlt

Select Year(BusScheduleAssignmentDate) as[Year]
,BusRoutezone,
case 
When Year(BusScheduleAssignmentDate)=2012 then 2.40
When Year(BusScheduleAssignmentDate)=2013 then 3.15
When Year(BusScheduleAssignmentDate)=2014 then 3.25
When Year(BusScheduleAssignmentDate)=2015 
then 3.50
end
 [Bus Fare],
Count(Riders) as [total Riders],
case 
When Year(BusScheduleAssignmentDate)=2012 then 2.40
When Year(BusScheduleAssignmentDate)=2013 then 3.15
When Year(BusScheduleAssignmentDate)=2014 then 3.25
When Year(BusScheduleAssignmentDate)=2015 
then 3.50
end *  count(riders) as [Total Fares]
from BusRoute br
inner join BusScheduleAssignment bsa
on br.BusRouteKey=bsa.BusRouteKey
inner Join Ridership r
on r.BusScheduleAssigmentKey=
bsa.BusScheduleAssignmentKey
Group by Year(BusScheduleAssignmentDate),
busRouteZone

The query for bus driver pay

Use MetroAlt


Select EmployeeLastName,
EmployeefirstName,
PositionName,
YEar(BusScheduleAssignmentDate) [Year],
EmployeeHourlyPayRate,
Sum(DateDiff(hh, BusDriverShiftSTarttime, BusDriverShiftStopTime)) [total Hours],
Sum(DateDiff(hh, BusDriverShiftSTarttime, BusDriverShiftStopTime)) * EmployeeHourlyPayRate [Annual Pay]
From employee e
inner Join EmployeePosition ep
on e.EmployeeKey = ep.EmployeeKey
inner join Position p
on  p.PositionKey=ep.PositionKey
inner join BusScheduleAssignment bsa
on e.EmployeeKey=bsa.EmployeeKey
inner Join BusDriverShift bs
on bs.BusDriverShiftKey =bsa.BusDriverShiftKey
Where YEar(BusScheduleAssignmentDate)=2013
And e.EmployeeKey=16
Group by  EmployeeLastName,
EmployeefirstName,
PositionName,
YEar(BusScheduleAssignmentDate),
EmployeeHourlyPayRate