MtSQL SQL 1:00 Class

Use PythonClubb;

Select * from Location;

Select RoomName, StreetAddress, City, State,PresentationDate, PresentationTime, Topic
From Location
join Presentation
on Location.idLocation = Presentation.Location_idLocation;

Select FirstName,Lastname, MeetingDate,MeetingTime, Topic
From Person
join member
on person.idPerson=member.Person_idPerson
join meetingattendance
on member.idMember=meetingattendance.Member_idMember
join meeting
on meeting.idMeeting=meetingattendance.Meeting_idMeeting
Where idmeeting=2;

Insert into Person(FirstName, Lastname, Email)
Values('Steve', 'Conger', 'Steve.conger@gmail.com');

Select * From Person;

Insert into Member(Person_idPerson, JoinDate)
Values(11,current_date());


Select * from Member;

/*Updates change existing records
where inserts new records
You should always use a where clause
with the key*/

Update person
set Lastname='Wrong',
Email ='John.Wrong@gmail.com'
where idPerson=1;

start transaction;
Update person
set LastName='Smith'
Where idPerson > 0;


Rollback;
Commit;

Delete from member where Person_idperson=11;
Delete from Person where idPerson=11;

Drop Table Presentation

MySQL sSQL

use Sakila;
Select * from Actor;
/*this a comment*/
Select First_name, Last_name, title, release_year, rating
from Actor
Inner join film_Actor
on Actor.actor_id = Film_Actor.actor_id
inner join Film
on film.film_id=Film_Actor.film_id
Where first_name='Bette' and Last_name='Nicholson';

Insert into Actor(first_name, last_name, last_update)
Values('Chuck','Mannley', current_date());

Insert into Film_Actor(actor_id,Film_id,Last_update)
Values(201,1,current_timestamp());


Select First_name, Last_name, title, release_year, rating
from Actor
Inner join film_Actor
on Actor.actor_id = Film_Actor.actor_id
inner join Film
on film.film_id=Film_Actor.film_id
Where actor.actor_id=201;


Create table actorb
(
 actor_id int, 
    first_name varchar(45), 
    last_name varchar(45), 
    last_update timestamp

);
Insert into actorb(actor_id, first_name, last_name, last_update)
Select actor_id, first_name, last_name, last_update
From Actor;

Select * from Actorb;

Alter table Actorb
Add Constraint PK_Actorb_Id Primary Key (actor_id);

Update actorb
set last_name = 'Guinness',
first_name='Penny'
where actor_id=1;

update actorb
Set Last_name='Smith'
Where actor_id > 0;

Delete from actor
Where actor_id=201;

Delete from film_actor
where actor_id=201;

Start transaction ;

Update actorb
set First_name='john'
where actor_id > 0;

Select * from actorb;


rollback ;
commit;

Drop table actorb;

Stored Procedures

/*********************
procedure for adding a new person
-get all the fields for new person and address
-Check to make sure the person doesn't exist
-insert into person
     -- get their plain password
  --concatenate with a seed
  --hash it (use HashPassword Function)
--insert into PersonAddress
--Insert into Contact
--make sure all inserts happen
--if that fails roll it back
--if succeeds commit it
***********************************/
Create procedure usp_NewPerson
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nvarchar(2)='Wa', 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Declare @hashed varbinary(500)
Declare @Seed int
Declare @PersonKey int
set @Seed = dbo.fx_GetSeed()
Set @hashed=dbo.fx_HashPassword(@seed, @PlainPassword)
--insert into Person
Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName, @PersonEmail, @hashed,
GetDate(), @seed)
--get the new PersonKey
set @personKey = IDENT_CURRENT('Person')
-- insert into personAddress
Insert into PersonAddress(PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, @PersonAddressStreet, @PersonAddressCity,
@PersonAddressState,@PersonAddressZip, @personKey)

Declare @home int =1
Declare @work int =2

If @HomePhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@HomePhone,@Home,@PersonKey)
end

If @WorkPhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone,@work,@PersonKey)
end
go

exec usp_NewPerson
@PersonLastName='Doe', 
@PersonFirstName='Jane', 
@PersonEmail='jane.doe@gmail.com', 
@PlainPassWord='DoePass', 
@PersonAddressStreet='1111 Martin Way', 
@PersonAddressZip='98000', 
@WorkPhone='2065551043'

Select * from PersonAddress
Select * from Contact

--Second version adding tran and try catch
go
Alter procedure usp_NewPerson
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nvarchar(2)='Wa', 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Declare @hashed varbinary(500)
Declare @Seed int
Declare @PersonKey int
--begin the transaction
Begin Tran
--begin the try
Begin Try
set @Seed = dbo.fx_GetSeed()
Set @hashed=dbo.fx_HashPassword(@seed, @PlainPassword)
--insert into Person
Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName, @PersonEmail, @hashed,
GetDate(), @seed)
--get the new PersonKey
set @personKey = IDENT_CURRENT('Person')
-- insert into personAddress
Insert into PersonAddress(PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, @PersonAddressStreet, @PersonAddressCity,
@PersonAddressState,@PersonAddressZip, @personKey)

Declare @home int =1
Declare @work int =2

If @HomePhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@HomePhone,@Home,@PersonKey)
end

If @WorkPhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone,@work,@PersonKey)
end
Commit Tran--if all is good
End Try 
Begin Catch--if there is an error
rollback tran --rollback
print error_message()
end catch
go

exec usp_NewPerson
@PersonLastName='Doh', 
@PersonFirstName='Jennifer', 
@PersonEmail='j.doh@gmail.com', 
@PlainPassWord='DohPass', 
@PersonAddressStreet='1111 Martin Way', 
@PersonAddressZip='98000', 
@WorkPhone='2065551043'

Go
--check to see if person exists
Alter procedure usp_NewPerson
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nvarchar(2)='Wa', 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
--check to see if person exists
--emails are unique so should be sufficient
--otherwise check several of the fields
If not exists
   (Select personKey from Person 
     where PersonEmail = @PersonEmail)
Begin --write the new person
Declare @hashed varbinary(500)
Declare @Seed int
Declare @PersonKey int
--begin the transaction
Begin Tran
--begin the try
Begin Try
set @Seed = dbo.fx_GetSeed()
Set @hashed=dbo.fx_HashPassword(@seed, @PlainPassword)
--insert into Person
Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName, @PersonEmail, @hashed,
GetDate(), @seed)
--get the new PersonKey
set @personKey = IDENT_CURRENT('Person')
-- insert into personAddress
Insert into PersonAddress(PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, @PersonAddressStreet, @PersonAddressCity,
@PersonAddressState,@PersonAddressZip, @personKey)

Declare @home int =1
Declare @work int =2

If @HomePhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@HomePhone,@Home,@PersonKey)
end

If @WorkPhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone,@work,@PersonKey)
end
Commit Tran--if all is good
End Try 
Begin Catch--if there is an error
rollback tran --rollback
print error_message()
end catch
end--end of if
Else
Begin
   print 'Person already exists'
End
go

exec usp_NewPerson
@PersonLastName='Burton', 
@PersonFirstName='Richard', 
@PersonEmail='Richard.Burton@gmail.com', 
@PlainPassWord='BurtonPass', 
@PersonAddressStreet='1111 Mecca Way', 
@PersonAddressZip='98000', 
@WorkPhone='2065551333'
go
---Stored proc to update own info
Create proc usp_UpdatePersonInfo
@PersonKey int,
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255), 
@PersonAddressState nvarchar(2), 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Begin tran
Begin try
Update Person
Set PersonLastName =@PersonLastName,
PersonFirstName=@PersonFirstName,
PersonEmail=@PersonEmail
where PersonKey = @personKey

Update PersonAddress
Set PersonAddressApt=@PersonAddressApt,
PersonAddressStreet=@PersonAddressStreet,
PersonAddressCity=@PersonAddressCity,
PersonAddressState=@personAddressState,
PersonAddressZip = @PersonAddressZip
Where PersonKey =@PersonKey

if @HomePhone is not null
Begin
  Update Contact
  Set ContactNumber=@HomePhone,
  ContactTypeKey=1
  where personKey =@PersonKey
end

if @WorkPhone is not null
Begin
  Update Contact
  Set ContactNumber=@WorkPhone,
  ContactTypeKey=2
  where personKey =@PersonKey
end
Commit Tran
End Try
Begin Catch
Rollback tran
Print Error_Message()
end catch
go
exec usp_UpdatePersonInfo
@PersonKey=134,
@PersonLastName='Burton', 
@PersonFirstName='Richard', 
@PersonEmail='JAnderson@gmail.com', 
@PersonAddressStreet='14994 Broadway', 
@PersonAddressCity='New York',
@PersonAddressState='NY',
@PersonAddressZip='13444', 
@WorkPhone='2065551666'
Go
select * from Person Where PersonEmail='Richard.Burton@gmail.com'
Select * from PersonAddress where personkey=134
Select * from Contact where personkey=134

Temp Tables and functions

--temp tables and functions
Use Community_Assist

Create table #tempPerson
(
   PersonLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)

insert into #TempPerson(PersonlastName, PersonFIrstName, PersonEmail)
Select PersonLastName, PersonFirstname, PersonEmail from Person

Select * from #tempPerson

Create table ##TempEmployee
(
  EmployeeKey int,  
  PersonKey int, 
  EmployeeHireDate date, 
  EmployeeAnnualSalary money
)

Insert into ##TempEmployee(EmployeeKey, PersonKey, 
EmployeeHireDate, EmployeeAnnualSalary)
Select * from Employee

Select * from ##TempEmployee
go
--functions
Create function fx_Cube
(@number int)
returns int
As
Begin
   Declare @cube int
   Set @cube = @number * @number * @number
   return @cube
End
Go
Select dbo.fx_Cube(5) as Cubed
Go
create function fx_DonationPercent
(@amount money, @percentage decimal(5,2))
returns money --set return type
As
Begin --begin function
 if @percentage > 1
  Begin --begin if
  set @percentage = @percentage /100
 end --end if
 Declare @percent money
 set @Percent=@amount * @percentage
 return @percent
End --end function

go
Select donationAmount, 
dbo.fx_DonationPercent(donationAmount, 85) charity,
dbo.fx_DonationPercent(donationAmount, .15) Overhead
From Donation

Select sum(donationAmount) Total, 
sum(dbo.fx_DonationPercent(donationAmount, 85)) charity,
sum(dbo.fx_DonationPercent(donationAmount, .15)) Overhead
From Donation

go

Alter function fx_Address
(@Apt nvarchar(255),
@Street nvarchar(255),
@City nvarchar(255),
@State nchar(255),
@Zip nchar(255))
Returns nvarchar(255)
As
Begin
 Declare @address nvarchar(255)
 if @Apt is null 
  Begin
    set @Address=@street + ', ' +@City + ', ' + @State + ' ' + @Zip
  end
 else
  Begin
    set @Address=@Apt + ', '+ @street + ', ' 
 +@City + ', ' + @State + ' ' + @Zip
  end
  return @address
End
Go
Select PersonFirstName, PersonLastName,
dbo.fx_Address( PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip) [Address]
From Person p
Inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
go
Create Function fx_Zip
(@Zipper nvarchar(255))
returns nvarchar(255)
As
Begin
Declare @Zip nvarchar(255)
set @Zip = @zipper
Return @Zip
End
go
Select dbo.fx_Zip(PersonAddressZip)
From PersonAddress

Better Index method for add books

I will update the github to reflect this

public ActionResult Index([Bind(Include = "Title, ISBN, AuthorName")]NewBook nb)
        {
            Author a = new Author();
            a.AuthorName = nb.AuthorName;
            db.Authors.Add(a);
            db.SaveChanges();
            // for donation get userkey from the session
            Book b = new Book();
            b.BookTitle = nb.Title;
            b.BookISBN = nb.ISBN;
            b.BookEntryDate = DateTime.Now;
            Author author = db.Authors.FirstOrDefault
                (x => x.AuthorName == nb.AuthorName);
            b.Authors.Add(author);

            db.Books.Add(b);
            db.SaveChanges();

            Message m = new Message();
            m.MessageText="Thank you, the book has been added";

            return View("Result", m);
        }

Create tables

/* ***************************
Add to Community_Assist
EventType, Location, 
Event, EventAttendence, EventComments
************************* */
Use Community_Assist

Create table EventType
(
    EventTypeKey int identity(1,1) primary key,
 EventTypeName nvarchar(255) not null,
 EventTypeDescription nvarchar(255) null
);

Create Table EventLocation
(
   EventLocationKey int identity(1,1),
   EventLocationName nvarchar(255) not null,
   EventLocationAddress nvarchar(255) not null,
   EventLocationCity nvarchar(255) default 'Seattle',
   EventLocationState nchar(2) default 'WA',
   EventLocationZip nchar(10) not null,
   constraint pk_EventLocation Primary Key(EventLocationKey)

);

Create Table CAEvent
(
   CAEventKey int identity(1,1) primary key,
   CAEventName nvarchar(255) not null,
   CAEventDate Date not null,
   CAEventStartTime time null,
   CAEventEndTime time null,
   EventLocationKey int Foreign key 
  references EventLocation(EventLocationKey),
   EventTypeKey int Foreign key 
     references EventType(EventTypeKey),
   EmployeeKey int null,
   Constraint FK_EmployeeCAEvent Foreign Key(EmployeeKey)
  references Employee(EmployeeKey)

)

Create table EventAttendence
(
    CAEventKey int not null,
 PersonKey int not null,
 Constraint PK_EventAttendence 
    primary key(CaEventKey, PersonKey),
 Constraint FK_CAEventAttend foreign Key(CAEventKey)
    references CAEvent(CAEventKey),
    Constraint FK_PersonAttend foreign Key(PersonKey)
    references Person(PersonKey)
)

Create table EventComment
(
    EventCommentKey int identity(1,1) not null,
 CAEventKey int not null,
 PersonKey int not null,
 EventCommentDate Date not null,
 EventRating int not null,
 CommentText nvarchar(max)

)

Alter Table EventComment
Add Constraint PK_EventComment Primary key (EventCommentKey)

Alter Table EventComment
Add 
Constraint FK_CAEventComment Foreign Key(CAEventKey)
   References CAEvent(CAEventKey),
Constraint FK_PersonComment Foreign Key(PersonKey)
   References Person(PersonKey)

Alter Table EventComment
Add 
Constraint df_CommentDate  default GetDate() for EventCommentDate

Alter Table EventComment
Drop Constraint ck_Rating

Alter Table EventComment
Drop column eventRating

Alter Table EventComment
Add EventRating int


Alter Table EventComment
Add constraint ck_Rating Check (EventRating between 1 and 5)

Sql 1 PM class

Use Community_Assist;

Select * from Person
Order by PersonLastName;

Select PersonFirstName, PersonLastName, PersonEmail
From Person
Order by PersonLastName;

Select PersonFirstName, PersonLastName, PersonEmail
From Person
Where PersonLastName='Brown';

Select * from Donation
Where DonationDate between'8/10/2015' and '8/11/2015';

Select * from Donation
Where DonationAmount > 1000;

Select * from Employee;

Select PersonFirstName, PersonLastName, EmployeeHireDate,
EmployeeAnnualSalary
From Person
inner join Employee
On Person.PersonKey=Employee.PersonKey;

Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonEntryDate)
Values('Foles',
'Nick',
'nick.foles@gmail.com'
,getDate())


Insert into PersonAddress(PersonAddressApt,
PersonAddressStreet, PersonAddressCity, 
PersonAddressState, PersonAddressZip, PersonKey)
Values(null,'10010 Elsewhere','Seattle','Wa', '98100', 130)

Insert into Donation(PersonKey, DonationDate, 
DonationAmount)
Values(130, GetDate(), 1000000)

Select * from Donation

Select * from PersonAddress

Select * from Person