Security

Here are the somewhat fragmentary notes from our discussion on Security

Prevent Damage to Database
malicious attacks--
Accidents -- update table
Authentication--password username, windows

There are two basic kinds of Authentication

Windows, mapping windows user accounts to SQl Server
Sql Server Authentication-- a password and user name

2 step authentication, biometrics, voice, facial
Certificates Encryption

Authorization--What permissions do you have

Select read
Insert--write
update--write change
Delete remove data
execute run procedures
create--create database objects
Alter --modify database objects
Drop--remove database object

Actors--who is going use the database

---

Actor  Fan
Tables Select Insert Update Delete Notes
Venue   X   
Artist  X
Fan     x        x     x           Update own records
                                    only

Here is the code for creating a login, a user, a role, granting permissions to the role, and adding the user to the role

Grant create Table  to DonorRole 

Create Login George with password='Pass'
Use CommunityAssist
Create User George for login George

Create Role ClientRole

Grant select, insert on ServiceGrant to ClientRole
Grant Select on CommunityService to ClientRole

exec sp_addrolemember Clientrole, George

triggers

--triggers
--stored procedure triggered by an event
--insert, update, delete
--for, after, instead of
-- for and after let the event happen and then
--execute the trigger code
--instead of intercepts the event and does
--the trigger code instead of the event
Create trigger tr_BigDonation on Donation
for insert 
As
if not exists
  (Select name from Sys.Tables
  Where name='BigDonations')
Begin
Create table BigDonations
(
 DonationKey int, 
 DonationDate DateTime, 
 DonationAmount money, 
 PersonKey int
)
End
Declare @DonationAmount money
Declare @Threshold money =500
Select @DonationAmount = DonationAmount
From Inserted
if @DonationAmount >= @Threshold
Begin
Insert into BigDonations(DonationKey,DonationDate,DonationAmount,
PersonKey)
Select DonationKey, DonationDate, DonationAmount,
PersonKey from Inserted
End

Select * from Person
Insert into Donation(DonationDate, DonationAmount, PersonKey)
Values(GetDate(), 550.75, 136)

Select * from Donation
Select * from BigDonations
Go
--Here is an alternate, more efficent version of that trigger
Create trigger tr_BigDonation on Donation
for insert 
As
--declare variables
Declare @DonationAmount money
Declare @Threshold money =500
--get value from temporary Inserted table
Select @DonationAmount = DonationAmount
From Inserted
--if the donation amount is 500 or above
if @DonationAmount >= @Threshold
Begin --outer if
if not exists
  (Select name from Sys.Tables
  Where name='BigDonations')
Begin--inner if
Create table BigDonations
(
 DonationKey int, 
 DonationDate DateTime, 
 DonationAmount money, 
 PersonKey int
)
End --end inner if
Insert into BigDonations(DonationKey,DonationDate,DonationAmount,
PersonKey)
Select DonationKey, DonationDate, DonationAmount,
PersonKey from Inserted
End --end outer if

go

Alter trigger tr_NoDelete on Donation
instead of Delete, update
As
  if not exists
     (Select name from Sys.Tables
      Where name='DeletedDonations')
   --if not create it
       Begin 
           Create table DeletedDonations
            (
           DonationKey int, 
           DonationDate DateTime, 
           DonationAmount money, 
           PersonKey int
             )    
       End
   Insert into DeletedDonations         (DonationKey,DonationDate, 
   DonationAmount, PersonKey)
 Select DonationKey, DonationDate, DonationAmount,
 PersonKey from Deleted



--
Delete from Donation where donationKey=47

Select * From Donation

Select * From DeletedDonations
go
--disable trigger to allow deletions
Disable trigger tr_NoDelete on Donation
go
--re enable trigger
enable Trigger tr_NoDelete on Donation

Stored Procedures

Use CommunityAssist
go
-- stored procedures
create proc usp_Donations
@PersonKey int
As
Select DonationKey, 
DonationDate, 
DonationAmount
From Donation
Where PersonKey = @PersonKey 

exec usp_Donations @PersonKey=3

Go
--add new person
--Get all the parameters for Person,
--personAddress and PersonContact
--Make sure the person is not in the database
--already
--begin a transaction
--Begin a try--
--hash their password
--insert into person
--insert into personAddress --getting the person key
--insert into personContact with key
--commit tran
--or catch the errors and rollaback

Version 1

Create proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
Declare @PersonKey int = Ident_Current('Person')
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)

exec Usp_AddPerson
@PersonLastName='Anderson', 
@PersonFirstName='Marty', 
@PersonUsername='MAnderson@gmail.com', 
@PersonPlainPassword='martyPass', 
@Street='1002 South Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559873', 
 @WorkPhone='2065558769'

Version 2

 Go
 Alter proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
--Declare variables
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =6
Declare @WorkPhoneType int =2
Begin Tran --start transaction
Begin try--start Try
--insert into Person
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
--get person key
Declare @PersonKey int = Ident_Current('Person')
--insert int personAddress
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
--insert home phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
--insert work phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)
commit tran --all is good write it
End try
Begin catch --if an error will fall here
Rollback Tran--undo the transaction
print 'Transaction rolled back'
print Error_Message();
End Catch

exec Usp_AddPerson
@PersonLastName='Munse', 
@PersonFirstName='Nelson', 
@PersonUsername='NMunse@gmail.com', 
@PersonPlainPassword='MunsePass', 
@Street='1004 North Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559773', 
 @WorkPhone='2065558729'

Third Version

  Alter proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
--Declare variables
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2
if exists
 (Select PersonKey from Person
 Where PersonLastName =@PersonLastName
 And PersonFirstname=@PersonFirstName
 And PersonUserName=@PersonUsername)
Begin --begin if
Print 'Person already exists'
end --end if
Else
Begin--begin else
Begin Tran --start transaction
Begin try--start Try
--insert into Person
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, 
PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
--get person key
Declare @PersonKey int = Ident_Current('Person')
--insert int personAddress
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
--insert home phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
--insert work phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)
commit tran --all is good write it
End try
Begin catch --if an error will fall here
Rollback Tran--undo the transaction
print 'Transaction rolled back'
print Error_Message();
End Catch
end --end else

exec Usp_AddPerson
@PersonLastName='Munse', 
@PersonFirstName='Nelson', 
@PersonUsername='NMunse@gmail.com', 
@PersonPlainPassword='MunsePass', 
@Street='1004 North Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559773', 
 @WorkPhone='2065558729'

ERD Individual Assignment 2 Diagrams

Recipe Ingredient Entities

---

Student Course Entities

---

Dentist office Entities

functions

--temp tables
use CommunityAssist

--this creates a temp table that is isolated
--to a single session 
Create table #ZPeople
(
    FirstName nvarchar(255),
 Lastname nvarchar(255)

)

Insert into #ZPeople(Firstname, Lastname)
Select PersonFirstName, PersonLastName
From Person
Where PersonLastName like 'Z%'

Select * From #ZPeople

--this creates a global temporary table
--that crosses sessions
Create table ##ZPeople
(
    FirstName nvarchar(255),
 Lastname nvarchar(255)

)

Insert into ##ZPeople(Firstname, Lastname)
Select PersonFirstName, PersonLastName
From Person
Where PersonLastName like 'Z%'

--functions

Go
--a simple function
create function fx_cube
(@number int) --parameters to be passed in
returns int --return type
As
Begin --start blocl
Declare @cube int --declare a varible
Set @Cube = @number * @number * @number
--return the value
return @Cube
End; --end function block
go
--use function
Select dbo.fx_Cube(2)
go

--get a random seed
Create Function fx_Seed
()
returns int
As
Begin
Declare @Time DateTime = GetDate();
Declare @Mille int = Datepart(MILLISECOND, @Time)
Return @Mille

End
go
Select dbo.fx_Seed()
go
--this function hashes a password
Create function fx_hashPass
(@Seed int, @Password nvarchar(50))
Returns Varbinary(500)
As
Begin
Declare @SeedChar nvarchar(4) = Cast(@seed as nvarchar(4))
Declare @CharToHash nvarchar(54) = @SeedChar+@Password
Declare @Hash varbinary(500)
set @Hash=hashbytes('sha2_512', @CharToHash)
return @Hash
End
go
Select dbo.fx_HashPass(dbo.fx_Seed(), 'P@ssw0rd1')
Select dbo.fx_HashPass(dbo.fx_Seed(), 'P@ssw0rd1')

go
--this sees if the passwords match 
Create function fx_Login
(@userName nvarchar(255) ,@password nvarchar(50))
returns int
As
Begin
Declare @seed int
Declare @DBHash varbinary(500)
Declare @Key int
--select values from the database
--and assign them to the variables
Select @key=PersonKey,@seed = PersonPassKey, @DBHash=PersonUserPassword       from Person
 Where PersonUsername=@UserName
 --concatinate the seed and password
Declare @Concat nvarchar(54)=Cast(@seed as nvarchar(4)) + @Password
Declare @NewHash varbinary(500)= hashbytes('Sha2_512',@Concat)
--see if they match
if Not @DBHash=@NewHash
Begin
--if not then just return 0
Set @key = 0
End
Return @Key
end

--test functions and login
Insert into person(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, Personpasskey, PersonEntryDate, PersonUserPassword)
Values ('Smith', 'Bob', 'bobSmith@gmail.com', 'BobPass',
dbo.fx_Seed(), GetDate(), 
dbo.fx_hashPass(dbo.fx_Seed(), 'BobPass'))

Select * From Person

Select dbo.fx_Login('bobSmith@gmail.com','BobPass')

USE [CommunityAssist]
GO

/****** Object:  UserDefinedFunction [dbo].[fx_MedianAvg]    Script Date: 2/18/2016 11:32:43 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--this function returns a median average
Create Function [dbo].[fx_MedianAvg]
(@Max decimal(16,4), @Min Decimal(16,4))
returns decimal(16,4)
As
Begin
Declare @Range decimal(16,4)
Declare @Median Decimal(16,4)
Select @Range= @Max-@min
Set @Median = @Range /2
Return @Median
End
GO


Select Avg(GrantAllocation) from Servicegrant
Select dbo.fx_MedianAvg(max(GrantAllocation),min(GrantAllocation))
From ServiceGrant

SQL 2

This includes SQL 1

--set the database context
Use CommunityAssist

/*Over view or SQL commands
Part 1 */

--basic statement [Select columns from Table name]
--the * is a wild card for all
Select * from Person;


Select PersonLastName, PersonFirstName,
PersonUserName
From Person;

--alias column names, the 'as'  keyword is optional
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person;

--Order by sorts a result set
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by PersonLastName

--Order by Desc

Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by [Last Name] Desc, PersonFirstName Desc

--where clause limits the rows to those that match the criteria
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName ='Tanner'
Order by [Last Name] Desc

Select * From ServiceGrant
Where GrantAmount > 500

Select * from ServiceGrant where GrantDate>'8/9/2013'

Select * From ServiceGrant where GrantDate 
Between '9/1/2013' and '10/1/2013'

--you can calculate
Select 50 * ((23/2.0 )+ 27) - 3

--The LIKE keyword lets you search for patterns 
--in this case everybody whose name begins with 'AND.'
--the % is a wildcard for any number of characters
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName Like 'A%'

--nulls cannot be = to anything you search for nulls
--with the IS keyword
Select * From PersonAddress
Where Apartment is null

--is not null
Select * From PersonAddress
Where Apartment is not null

Select * from ServiceGrant

--date Functions
Select Distinct Year(GrantDate) From ServiceGrant
Select Distinct Month(GrantDate) From ServiceGrant
Select Distinct Day(GrantDate) From ServiceGrant

Select Cast(Day(GrantDate)as varchar(2))+ '/' 
+ cast(Month(GrantDate) as varchar(2)) + '/' 
+ cast(Year(GrantDate)as varchar(4)) as Year 
From ServiceGrant

Select GrantDate From ServiceGrant


--the datediff function subtracts on date from another in the specified units 
--dd is days, yy years, mm months
Select GrantReviewDate, GrantDate, DateDiff(dd,GrantDate,GrantReviewDate) as [Days before Review]
From ServiceGrant
Where DateDiff(dd,GrantDate,GrantReviewDate) > 2

--aggregate functions are functions that work
--on sets of rows at a time
Select format(Sum(GrantAllocation),'$#,###.00') as total from ServiceGrant 
Select Avg(GrantAllocation)as total from ServiceGrant 

Select Count(GrantAllocation)as total from ServiceGrant 
Where GrantAllocation > 500

Select Max(GrantAllocation)as total from ServiceGrant 
Select Min(GrantAllocation)as total from ServiceGrant 

--when mixing aggregate functions such as sum
--with non-aggregate functions or fields like Month(GrantDate)
--you have to GROUP BY all the non-aggregate values
Select Month(GrantDate) as [Month], Sum(GrantAllocation) as total
From ServiceGrant
Group by Month(GrantDate)

--having works like a where by you use it when you have
--an aggregate function in the criteria
Select ServiceKey, Sum(GrantAllocation)
From ServiceGrant
Group by ServiceKey
 Having sum(GrantAllocation) > 2000

--inner join joins two tables. the keyword INNER is optional
Select ServiceName, Sum(GrantAllocation) as Total
From ServiceGrant
inner join CommunityService
on ServiceGrant.ServiceKey=CommunityService.ServiceKey
Group by ServiceName

--another inner join
Select p.PersonKey,PersonLastName, PersonFirstName, Street, City,[State],zip
From Person as p
join PersonAddress as pa
on p.PersonKey=pa.PersonKey

Select * From ServiceGrant

--joining multiple tables
--the person table is joined twice
--once for the client and once for the employee
Select GrantAmount, GrantDate, p.PersonLastName as Client, ServiceName, p2.PersonLastName as EmployeeName, GrantAmount
From Person as p
join ServiceGrant as sg
on p.PersonKey=sg.PersonKey
join CommunityService as cs
on cs.ServiceKey=sg.ServiceKey
join Employee as e
on e.EmployeeKey=sg.EmployeeKey
inner join person p2
on p2.PersonKey = e.PersonKey

--Data Manipulation queries
Insert into Person ( PersonLastName, PersonFirstName, PersonUsername)
Values('Kim','Jim','jimkim'),
('Morgan','Randy','rmorgan')

Select * from Person

Create Table PeopleTemp
(
 PersonKey int,
 PersonLastName nvarchar(255),
 PersonFirstName nvarchar(255)
)

Insert into PeopleTemp(personKey, PersonLastName, PersonFirstName)
Select PersonKey PersonLastName, PersonFIrstName
From Person

Select * from PeopleTemp

Insert into Person ( PersonLastName, PersonFirstName, PersonUsername)
Values('Lawrence', 'Ahab', 'Alaw')
Insert into PersonAddress([Street],[City],[Zip], PersonKey)
Values('1001 Westlake','Seattle','98001', ident_current('Person'))

Select * from Person
Select * From PersonAddress

update peopleTemp
Set PersonLastName= 'Smith'

Update Person
Set PersonLastName='Alberts',
PersonFirstName='Jay'
where PersonKey=1

Begin Tran

Rollback Tran
commit tran

Delete From Person Where Personkey =1

Drop table PeopleTemp

Select * from Person

Set operators, Windows Functions, Data Modification

--Set operators
--Data manipulation
-- Windows Functions
-- union Intersect Except

use CommunityAssist
--union
Select PersonLastName, PersonFirstName,
EmployeeHireDate
From Person p
join Employee  e on p.PersonKey=e. PersonKey
Union
Select EmployeeLastName, EmployeeFirstName, 
EmployeeHireDate
From MetroAlt.dbo.Employee
use MetroAlt
Select PersonLastName, PersonFirstName,
EmployeeHireDate
From CommunityAssist.dbo.Person p
join CommunityAssist.dbo.Employee  e on p.PersonKey=e. PersonKey
Union All
Select EmployeeLastName, EmployeeFirstName, 
EmployeeHireDate
From MetroAlt.dbo.Employee

--intersect
Select City from PersonAddress
Intersect
Select EmployeeCity from MetroAlt.dbo.Employee

--except
Select City from PersonAddress
Except 
Select EmployeeCity from MetroAlt.dbo.Employee

Select EmployeeCity from MetroAlt.dbo.Employee
Except
Select City from PersonAddress

--ranking functions
Select GrantKey, ServiceKey, GrantAllocation,
Row_Number() over (Order by GrantAllocation desc) as RowNumber,
Rank() over (Order by GrantAllocation desc) as [Rank],
Dense_Rank() over (Order by GrantAllocation desc) as [DenseRank],
NTILE(10) over (Order by GrantAllocation desc) as [NTILE]
From ServiceGrant
Order by GrantAllocation desc

--windows partition functions
Select Grantkey, ServiceKey, GrantAllocation,
Sum(GrantAllocation) over () as TotalAllocations,
Sum(GrantAllocation) over(Partition by ServiceKey) as PerService
From ServiceGrant
order by GrantAllocation Desc

--pivot
Select EmployeeKey, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select EmployeeKey,  ServiceKey, GrantKey From
dbo.ServiceGrant) as a
pivot (Count(GrantKey) for ServiceKey in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) as b

-- modifying data

Insert into Person  (PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, Personpasskey, PersonEntryDate, PersonUserPassword)
Values('Alberts','Larry','larryA@gmail.com',null, 12345678,
GetDate(), dbo.fx_HashPassword('AlbertPass')),
('Able','Luke','luke.able@gmail.com',null, 12345678,
GetDate(), dbo.fx_HashPassword('AblePass'))

Select * from Person order by PersonLastName

Begin tran
Update Person 
Set PersonFirstName = 'Jason',
PersonPlainpassword='JasonPass'
Where Personkey=1
Commit Tran
Rollback tran

Delete from Person where Personkey =1
 Begin Tran
 Truncate Table GrantReview

 Select * From GrantReview

 Rollback Tran

 Insert into person(personLastname, PersonFirstName)
 Values('Lucas', 'George')
 Insert into Donation(DonationDate, DonationAmount, PersonKey)
 Values(GetDate(), 1500,Ident_Current('Person'));
 @@Identity

 Select * from Person
 Select * From Donation

SQL Part One

--set the database context
Use CommunityAssist

/*Over view or SQL commands
Part 1 */

--basic statement [Select columns from Table name]
--the * is a wild card for all
Select * from Person


Select PersonLastName, PersonFirstName,
PersonUserName
From Person;

--alias column names, the 'as'  keyword is optional
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person;

--Order by sorts a result set
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by PersonLastName

--Order by Desc

Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by [Last Name] Desc

--where clause limits the rows to those that match the criteria
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName ='Anderson'
Order by [Last Name] Desc

--you can calculate
Select 50 * ((23/2.0 )+ 27) - 3

--The LIKE keyword lets you search for patterns 
--in this case everybody whose name begins with 'AND.'
--the % is a wildcard for any number of characters
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName Like 'And%'

--nulls cannot be = to anything you search for nulls
--with the IS keyword
Select * From PersonAddress
Where Apartment is null

--is not null
Select * From PersonAddress
Where Apartment is not null

Select * from ServiceGrant

--date Functions
Select Distinct Year(GrantDate) From ServiceGrant
Select Distinct Month(GrantDate) From ServiceGrant
Select Distinct Day(GrantDate) From ServiceGrant

--the datediff function subtracts on date from another in the specified units 
--dd is days, yy years, mm months
Select GrantReviewDate, GrantDate, DateDiff(dd,GrantDate,GrantReviewDate) as [Days before Review]
From ServiceGrant
Where DateDiff(dd,GrantDate,GrantReviewDate) > 2

--aggregate functions are functions that work
--on sets of rows at a time
Select format(Sum(GrantAllocation),'$#,###.00') as total from ServiceGrant 
Select Avg(GrantAllocation)as total from ServiceGrant 

Select Count(GrantAllocation)as total from ServiceGrant 
Where GrantAllocation > 500

Select Max(GrantAllocation)as total from ServiceGrant 
Select Min(GrantAllocation)as total from ServiceGrant 

--when mixing aggregate functions such as sum
--with non-aggregate functions or fields like Month(GrantDate)
--you have to GROUP BY all the non-aggregate values
Select Month(GrantDate) as [Month], Sum(GrantAllocation) as total
From ServiceGrant
Group by Month(GrantDate)

--having works like a where by you use it when you have
--an aggregate function in the criteria
Select ServiceKey, Sum(GrantAllocation)
From ServiceGrant
Group by ServiceKey
Having sum(GrantAllocation) > 2000

--inner join joins two tables. the keyword INNER is optional
Select ServiceName, Sum(GrantAllocation) as Total
From ServiceGrant
inner join CommunityService
on ServiceGrant.ServiceKey=CommunityService.ServiceKey
Group by ServiceName

--another inner join
Select p.PersonKey,PersonLastName, PersonFirstName, Street, City,[State],zip
From Person as p
join PersonAddress as pa
on p.PersonKey=pa.PersonKey

Select * From ServiceGrant

--joining multiple tables
--the person table is joined twice
--once for the client and once for the employee
Select GrantAmount, GrantDate, p.PersonLastName as Client, ServiceName, p2.PersonLastName as EmployeeName, GrantAmount
From Person p
join ServiceGrant sg
on p.PersonKey=sg.PersonKey
join CommunityService cs
on cs.ServiceKey=sg.ServiceKey
join Employee e
on e.EmployeeKey=sg.EmployeeKey
inner join person p2
on p2.PersonKey = e.PersonKey

Login Service Code

Here is the Interface

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IbookReviewLoginService" in both code and config file together.
[ServiceContract]
public interface IbookReviewLoginService
{
    [OperationContract]
    int ReviewerLogin(string password, string username);

    [OperationContract]
    int ReviewerRegistration(ReviewerLite r);
    
}

[DataContract]
public class ReviewerLite
{
    [DataMember]
    public string LastName { set; get; }

    [DataMember]
    public string FirstName { set; get; }

    [DataMember]
    public string UserName { set; get; }

    [DataMember]
    public string Password { set; get; }
    [DataMember]
    public string Email { set; get; }
}

Here is the service itself

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "bookReviewLoginService" in code, svc and config file together.
public class bookReviewLoginService : IbookReviewLoginService
{
    BookReviewDbEntities db = new BookReviewDbEntities();
    public int ReviewerLogin(string password, string username)
    {
        int result = db.usp_ReviewerLogin(username, password);
        if(result !=-1)
        {
            var key = from k in db.Reviewers
                      where k.ReviewerUserName.Equals(username)
                      select new { k.ReviewerKey };
             foreach(var k in key)
            {
                result=(int)k.ReviewerKey;
            }
        }
       
        return result;
    }

    public int ReviewerRegistration(ReviewerLite r)
    {
     
        int result = db.usp_NewReviewer(r.UserName, r.FirstName, r.LastName, r.Email, r.Password);

        return result;
    }
}

Table Expressions part 2

use MetroAlt
--the correlated subquery from the assignment
Select PositionKey, EmployeeKey, EmployeeHourlyPayRate
From EmployeePosition ep1
where EmployeeHourlyPayRate =
(Select Max(EmployeeHourlyPayRate) 
From EmployeePosition ep2
Where ep1.PositionKey=ep2.PositionKey)

--just a subquery that select the max
--for one position
Select employeeKey, PositionKey, EmployeeHourlypayRate from EmployeePosition
Where  Positionkey=1 
And EmployeeHourlypayRate = 
(Select Max(EmployeeHourlyPayrate) from EmployeePosition where PositionKey = 1)

--Table Expressions
--views

Use communityAssist
GO --seperates batches

--a view is a stored query that offers a
--"view" of the data
Create view vw_HREmployees
AS
Select PersonLastName [Last Name],
PersonFirstname [First Name],
PersonUserName Email,
[Street],
[Apartment],
[State],
[Zip],
[ContactInfo] Phone,
[ContactTypeName] [Contact Type],[EmployeeHireDate]  [Hire Date],[EmployeeSSNumber] SSNumber,
[EmployeeMonthlySalary] [Monthly Salary]
From person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on pc.PersonKey =p.PersonKey
inner join Employee e
on e.PersonKey=p.PersonKey
inner join ContactType ct
on ct.ContactTypeKey = pc.ContactTypeKey


Go
--using the view
--order by is not allowed in a view but
--you can use it in the select when calling
--the view
Select * from vw_HREmployees
order by [Last Name]

go
--changing the view to add schemabinding
--schema binding prevents a user from
--changing the underlying tables on which
--the view depends
Alter view vw_HREmployees with schemabinding
AS
Select PersonLastName [Last Name],
PersonFirstname [First Name],
PersonUserName Email,
[Street],
[Apartment],
[State],
[Zip],
[ContactInfo] Phone,
[ContactTypeName] [Contact Type],[EmployeeHireDate]  [Hire Date],[EmployeeSSNumber] SSNumber,
[EmployeeMonthlySalary] [Monthly Salary]
From dbo.person p
inner join dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join dbo.PersonContact pc
on pc.PersonKey =p.PersonKey
inner join dbo.Employee e
on e.PersonKey=p.PersonKey
inner join dbo.ContactType ct
on ct.ContactTypeKey = pc.ContactTypeKey

Begin tran --begin transaction
--this attempted alteration results in an error
--becase the view depends on this table and
--column
Alter Table Employee
Drop Column EmployeeSSNumber
Rollback tran --not necessary because nothing
--happened but need to end the tran

go
--create a schema (ownership)
Create Schema EmployeeSchema

--fully qualified path
--Server.Database.Schema.Table

--create a view owned by the schema
Create view EmployeeSchema.vw_HireDates
As
Select * From Employee
go

--create a table valued function
Create function fx_Employee
(@EmployeeKey int) --parameter
returns table --return type
As
Return
Select GrantKey, PersonKey, 
GrantNeedExplanation, GrantReviewDate,
GrantApprovalStatus, GrantAllocation
From ServiceGrant
Where EmployeeKey = @EmployeeKey
Go
--using the function 4 is an employeeKey
Select * from dbo.fx_Employee(4)

--cross apply
Select distinct a.ServiceKey, c.GrantAllocation
From dbo.ServiceGrant as a
Cross Apply
(Select serviceKey, grantAllocation, GrantKey
From ServiceGrant b
Where b.ServiceKey=a.ServiceKey
Order by GrantAllocation desc, serviceKey desc
Offset 0 rows Fetch First 3 rows only) as c