SQL from manage Databases

Create database TestDB2 --gives you all the defaults

--create database with specific file info
Create database TestDB3
On primary 
(Name=N'TestDB3', 
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3.mdf')
Log on
(Name=N'TestDb3_log',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3_Log.ldf')

--Drop a database
Drop Database TestDb2

--detatch a database

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TestDB3'
GO

attach a database
USE [master]
GO
CREATE DATABASE [TestDB3] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3_Log.ldf' )
 FOR ATTACH
GO

Admin and XML

--the Amin Assignment

use metroalt

--what is a schema ownership of object
--default dbo.
go
Create schema ManagementSchema


Go
Create view ManagementSchema.vw_AnnualRidership
As
Select Year(BusScheduleAssignmentDate) as [Year],
Sum(Riders) as Total
From BusScheduleAssignment bsa
join Ridership r
on bsa.BusScheduleAssignmentKey=r.BusScheduleAssigmentKey
Group by Year(BusScheduleAssignmentDate)
go

Create role ManagementRole

Grant Select, insert, update on Employee to ManagementRole

Grant select on Schema::managementSchema to managementRole

Create login Albert with password='P@ssw0rd1'

Create user Albert for login Albert



exec sp_addrolemember 'ManagementRole','Albert'

Backup Database MetroAlt To Disk='C:\Backup\MetroAlt.bak'

--XML Assignment
Create Xml Schema Collection ManagementNotesXMLSchema
As
'

  
    
      
        
        
      
    
  
'

Create Table maintenanceNotes
(
   maintenanceNotesKey int identity(1,1),
   maintenanceNotesDate Date,
   maintenanceNote xml (managementNotesXmlSchema)

)

Insert into maintenanceNotes (maintenanceNotesDate, maintenanceNote)
Values (GetDate(), '

  
  
  
    The brakes are shot
  
  
    Needs new tires
  
')


Select * from maintenanceNotes

Use Community_Assist

Select * From GrantReviewComment

Select GrantReviewKey, EmployeeKey, 
GrantReviewCommentDate, 
GrantReviewNote.query
('declare namespace rn="http://www.community_assist.org/reviewnotes";//rn:reviewnote/rn:recommendation')
From GrantReviewComment

Fraction and unit testing

here is the test document

here is the Unit Test Code

using System;
using Fractions;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace FractionTestProject
{
    [TestClass]
    public class UnitTest1
    {

        [TestMethod]
        public void AdditionTestMethod()
        {
            Fraction f1 = new Fraction("1/2");
            Fraction f2 = new Fraction(1, 3);
            Fraction f3 = f1 + f2;

            Assert.AreEqual("5/6", f3.ToString());
        }

        [TestMethod]

        public void MultiplicationTestMethod()
        {
            Fraction f1 = new Fraction("1/2");
            Fraction f2 = new Fraction(1, 3);
            Fraction f3 = f1 * f2;

            Assert.AreEqual("1/6", f3.ToString());
        }

        [TestMethod]
        public void GreaterThanTestMethod()
        {
            Fraction f1 = new Fraction("1/2");
            Fraction f2 = new Fraction(1, 3);


            Assert.IsTrue(f1 > f2);
        }

    }
}

Triggers

--stored procedures
--parameterized view

use Community_Assist
go
Create proc usp_HRViewProc
@EmployeeKey int
As
Select PersonLastName,
PersonfirstName,
PersonEmail,
EmployeeHireDate,
EmployeeAnnualSalary
From Person p
inner Join Employee e
on p.PersonKey=e.PersonKey
Where EmployeeKey = @EmployeeKey

exec usp_HRViewProc @EmployeeKey=2
Go
--Create hash password function
Alter function fx_hashPassword2
(@seed int, @password nvarchar(50))
returns varbinary(500)
As
Begin
Declare @newPassword nvarchar(70)
set @newPassword = 
cast(@seed as nvarchar(20)) + @Password
Declare @hashed varbinary(500)
set @hashed = hashbytes('sha2_512', @newPassword)
return @hashed
End


Select * from businessrule
go
--new person stored procedure
Alter proc usp_NewPerson
@lastName nvarchar(255),
@FirstName nvarchar(255)=null,
@Email nvarchar(255),
@password nvarchar(50),
@AptNumber nvarchar(255)=null,
@Street nvarchar(255),
@City nvarchar(255)='Seattle',
@State nvarchar(255)='WA',
@Zip nvarchar(255),
@HomePhone nvarchar(255)
As
--test to see if person exists
if exists
 (Select PersonLastName, PersonEmail From Person
 Where PersonLastName=@LastName
 And PersonEmail=@Email)
 Begin--begin if
 Print 'Already registered'
 return
 End--end if

--create the password hash
Declare @seed int
Set @seed = dbo.fx_GetSeed()
declare @hashedPass varbinary(500)
set @hashedPass = dbo.fx_HashPassword(@seed,@password)

Begin tran --start a transaction
Begin try -- begin try
--insert into person
Insert into Person(  
PersonLastName, PersonFirstName,
 PersonEmail, PersonPassWord, 
 PersonEntryDate, PersonPassWordSeed)
 Values(@LastName, @FirstName,
 @Email, @hashedPass,GetDate(),@seed)

 --get the key of the person just inserted
 Declare @key int = ident_current('Person')
 --insert into PersonAddress
 Insert into PersonAddress(
 PersonAddressApt,
 PersonAddressStreet, 
 PersonAddressCity, PersonAddressState, 
 PersonAddressZip, PersonKey)
 Values( @AptNumber,@street,@city,@state,@Zip,@key)

 Insert into Contact([ContactNumber],
 [ContactTypeKey], [PersonKey])
 Values (@HomePhone, 1, @Key)

 Commit Tran --commit the transaction if no error
 End Try
 Begin Catch
 Rollback Tran
 Print Error_Message()
 End Catch


 exec usp_NewPerson
 @lastName = 'Conger', 
 @FirstName='Steve', 
 @Email='spconger@gmail.com', 
 @password='congerPass', 
 @Street='101 nowhere bld', 
 @Zip='98122', 
 @HomePhone='2065551201'

 Select * from PersonAddress
 Select * from Contact

 Select * from Person
 go
 --stored procedure update Address
 Create proc usp_UpdateAddress
 @PersonAddressApt nvarchar(255) = null, 
 @PersonAddressStreet nvarchar(255), 
 @PersonAddressCity nvarchar(255)= 'Seattle', 
 @PersonAddressState nvarchar(255)='Wa', 
 @PersonAddressZip nvarchar(255), 
 @PersonKey int
As
Begin tran
Begin Try
 Update PersonAddress 
 Set PersonAddressApt=@PersonAddressApt,
 PersonAddressStreet=@PersonAddressStreet,
 PersonAddressCity =@PersonAddressCity,
 PersonAddressState=@PersonAddressState,
 PersonAddressZip=@PersonAddressZip
 Where PersonKey = @PersonKey
Commit Tran
End try
Begin Catch
 Rollback tran
 print error_message()
End catch


Select * from PersonAddress

Exec usp_UpdateAddress
@PersonAddressApt = '304', 
@PersonAddressStreet='100 South Mann Street', 
@PersonAddressZip='98001',
@PersonKey=1;


--trigger
--like a stored proc but triggered by an event 
--insert update delete
--triggers have no parameters
-- for an event, instead of an event

Create Trigger Tr_PersonDelete on Person
instead of Delete
As
if not exists
  (Select name from sys.Tables 
    where name='PersonDelete')
Begin
CREATE TABLE [dbo].[PersonDelete](
 [PersonKey] int NOT NULL,
 [PersonLastName] nvarchar(255) NOT NULL,
 [PersonFirstName] nvarchar(255) NULL,
 [PersonEmail] nvarchar(255) NULL,
 [PersonPassWord] varbinary(500) NULL,
 [PersonEntryDate] datetime NOT NULL,
 [PersonPassWordSeed] int NULL
 )

End
Insert into PersonDelete (PersonKey,PersonLastName,
PersonFirstName,PersonEmail,PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Select PersonKey,PersonLastName,
PersonFirstName,PersonEmail,PersonPassWord,
PersonEntryDate, PersonPassWordSeed
From Deleted --temp table in temp database that stores 
--deletions and updates

Select * from Person

Insert into Person (PersonLastName, PersonFirstName, 
PersonEmail,  PersonEntryDate)
Values('NotHereLong','Deleted','Deleted@gmail.com', GetDate())

Delete from Person where Personkey=132

Select * from PersonDelete
--check to see if request is large than max request
Create trigger tr_CheckMax on GrantRequest
for Insert
as
if not exists
  (Select name from sys.Tables
    Where name ='OverMax')
Begin
Create Table OverMax
(
     RequestDate date,
  PersonKey int,
  GrantypeKey int,
  Explanation nvarchar(255) null,
  RequestAmount money

)

End

Declare @GrantMax money
Declare @GrantType int
Declare @RequestAmount money

Select @GrantType=GrantTypeKey from inserted
Select @GrantMax = GrantTypeMaximum from GrantType
   Where GranttypeKey=@GrantType
Select @RequestAmount = GrantRequestAmount from inserted

if @RequestAmount > @GrantMax
Begin
Insert into OverMax(RequestDate, PersonKey, GranTypeKey,Explanation, RequestAmount)
Select GrantRequestDate, PersonKey, GrantTypeKey, GrantRequestExplanation, GrantRequestAmount
From inserted
End

Select * from GrantType


insert into GrantRequest( 
GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount)
Values (GetDate(),1,1,'Always hungry',300.00)

Select * from GrantRequest

Select * from OverMax