SQL 2

today's SQL
--this is an inner join
--list all the columns regardless of table
--list one of tables in the From clause
--use the inner join keyword to add another
--table
--use the on keyword to show how the tables join

Select LastName, Firstname,
DonationDate, DonationAmount
From Person p
Inner Join Donation d
ON p.PersonKey=d.PersonKey
Order by Lastname

--matches every record in one table
--with every record in the second table
Select LastName, Firstname,
DonationDate, DonationAmount
From Person p
Cross Join Donation d

--equi join
Select LastName, Firstname,
DonationDate, DonationAmount
From Person p, donation d
Where p.PersonKey=d.PersonKey

Select Firstname, LastName, ContactInfo
From Person p
Inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
Where ContactTypeKey=6

Insert into Person (LastName, Firstname)
Values ('Depp', 'Johnny')

Insert into Donation (DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values('2/25/2010',5,52,1)

Select lastName, firstname, DonationDate, DonationAmount
From Person p
inner join Donation d
on p.Personkey=d.personkey
Where lastname='Depp'

Update Person
Set LastName='Jackson',
Firstname='Janet'
Where PersonKey=1

Select * from Person

Begin tran

Update Person
Set LastName='Jackson'

Commit Tran

Delete from Person
Where PersonKey=52

Rollback tran

Begin tran

Delete from Donation

Select * from Donation

Rollback tran

Magazine Subscription Stored Proc

Here is the stored procedure we did in class

Alter Procedure usp_NewSubscription
@CustLastName varchar(30),
@CustFirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState char(2),
@CustZipcode char(11),
@CustPhone char(10),
@Magazine varchar(100),
@SubscriptTypeID int,
@StartDate DateTime
As
Begin tran
--the as keyword starts the body of the procedure
--The first thing we will do is insert the customer
Begin try
--declare the customerid as a variable
Declare @CustID int
--test to see if customer exists by matching all the fields
if exists
(Select CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone
From Customer
Where CustLastName=@CustLastName
And CustFirstName=@CustFirstName
And CustAddress=@CustAddress
And CustCity= @CustCity
And CustState= @CustState
And CustZipcode= @CustZipcode
And CustPhone=@CustPhone)
Begin --if customer does exist
--get the customer id of the existing customer
Select @CustID=Custid
From Customer
Where CustLastName=@CustLastName
And CustFirstName=@CustFirstName
And CustAddress=@CustAddress
And CustCity= @CustCity
And CustState= @CustState
And CustZipcode= @CustZipcode
And CustPhone=@CustPhone
End
Else --if it doesn't exist
Begin
--insert the customer
Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values(
@CustLastName,
@CustFirstName,
@CustAddress,
@CustCity,
@CustState,
@CustZipcode,
@CustPhone)
--the user has provided all these values as parameters
--now we get the new CustID created by the insert

Set @CustID=@@Identity
End --end of else
--Next we look for the MagID using the magazine Name

Declare @MagID int
Select @MagID=Magid from Magazine where MagName=@Magazine

--with the magid and the subscription type (which was provided as a parameter
--we can look up the magdetid which is what we need for
--the insert into subscription

Declare @magDetID int
Select @magDetID=MagDetId
From MagazineDetail
where MagID=@MagID
And SubscriptTypeID=@SubscriptTypeID
--test to see if subscription exists or not
If not exists
(Select SubscriptionID from Subscription
Where MagDetID=@MagDetID
And CustID=@CustID
And SubscriptionEnd < GetDate())
Begin
--if it doesn't already exit insert the new subscription
Insert into Subscription
(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values
(@CustID, @MagDetID, @StartDate,
dbo.func_SubscriptionEndDate(@MagDetID, @StartDate))
end
--this will happen if there are no errors above
Commit tran

End try
Begin Catch
--if there are errors do this
Rollback tran
print error_message()
End Catch

SQL

Here is the SQL we did in class with CommunityAssist::

Use CommunityAssist
/*******************
script from 2/23/2010
********************/
Select Lastname, firstname
From Person

Select * From Person

--this only returns unique values
Select Distinct PersonKey from Donation
Order by PersonKey Desc

Select DonationAmount, DonationAmount * .9 As [to Charity]
From Donation

Select * From PersonAddress
Where Not City='Seattle'

Select *
from PersonAddress
Where Apartment
is not Null

Select * from Employee
Where Hiredate > '1/1/2003'

Select * from Employee
Where Dependents >2

Select top 3 Lastname from Person
Order by LastName

Select LastName from Person
Where Lastname LIKE '_a%'

--aggregate functions
Select Count(*) from Donation

Select sum(DonationAmount) From Donation

Select Max(DonationAmount) from Donation

Select MIN(DonationAmount) from Donation

Select Round(Avg(DonationAmount),2)
from Donation

Select Month(DonationDate) as [Month],
Sum(DonationAmount) as Total
From Donation
Group by Month(DonationDate)

Select EmployeeKey,
Sum(DonationAmount) as Total
From Donation
Group by EmployeeKey

State (Cookies, sessions etc.)

Here is the code on the form side (Where you enter the data)with all three different methods


Partial Class Default2
Inherits System.Web.UI.Page

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
' QueryString()
'SessionMethod()
CookieMethod()
End Sub

Protected Sub QueryString()
Response.Redirect("Default3.aspx?Firstname=" & txtFirstName.Text & "&Lastname=" & txtLastName.Text)
End Sub

Protected Sub SessionMethod()
Session("First") = txtFirstName.Text
Session("Last") = txtLastName.Text
Response.Redirect("Default3.aspx")
End Sub

Protected Sub CookieMethod()
Dim chocolateChip As New HttpCookie("sugar")
chocolateChip("first") = txtFirstName.Text
chocolateChip("last") = txtLastName.Text
chocolateChip.Path = "C:\Sugar.txt"
Response.Cookies.Add(chocolateChip)

Response.Redirect("Default3.aspx")
End Sub
End Class

Here is the code from the receiving side:


Partial Class Default3
Inherits System.Web.UI.Page


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'GetQueryString()
'GetSession()
ReadCookie()
End Sub

Protected Sub GetQueryString()
lblFirstName.Text = Request.QueryString("FirstName")
lblLastName.Text = Request.QueryString("LastName")
End Sub

Protected Sub GetSession()
If Session("First") <> Nothing Then
lblFirstName.Text = Session("First")
lblLastName.Text = Session("Last")
End If
End Sub

Protected Sub ReadCookie()
Dim cookie As HttpCookie = Request.Cookies("C:\sugar.txt")
lblFirstName.Text = cookie("first")
lblLastName.Text = cookie("last")
End Sub
End Class

Master pages Example

I attached the Master Pages example as a Zip file to the ITC 172 Syllabus. You can download it extract it and then open it to view the files and code

Stored Procedure

First of all DON"T PANIC! stored procedures do have a bit of a learning curve. They are more like traditional programming. The first thing to do (always) is to be clear about what it is you are trying to do with the procedure. In our procedure we want to Add a new subscription to do this we must

• Get all the parameters (values) we need to insert into customer


• Get the Magazine name and the subscription type (is it for one year or 5 months etc.


• Insert the new customer


• Get the new customerId. It is an identity and we can use the built in variable
  @@Identity


• Use the magazine name to get the magID for the magazine


• Use the magid and the subscription type to get the MagDetID from the magazineDetail table


• use the Information we got to insert the Subscription. (I also used the func_EndDate to get the end date for the subscription

So here is the procedure: First Name the procedure and provide all the parameters you need. You can get them by looking at the columns each table needs for the inserts

Create Procedure usp_NewSubscription
@CustLastName varchar(30),
@CustFirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState char(2),
@CustZipcode char(11),
@CustPhone char(10),
@Magazine varchar(100),
@SubscriptTypeID int,
@StartDate DateTime
As
--the as keyword starts the body of the procedure
--The first thing we will do is insert the customer
Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values(
@CustLastName,
@CustFirstName,
@CustAddress,
@CustCity,
@CustState,
@CustZipcode,
@CustPhone)
--the user has provided all these values as parameters
--now we get the new CustID created by the insert
Declare @CustID int
Set @CustID=@@Identity

--Next we look for the MagID using the magazine Name

Declare @MagID int
Select @MagID=Magid from Magazine where MagName=@Magazine

--with the magid and the subscription type (which was provided as a parameter
--we can look up the magdetid which is what we need for
--the insert into subscription

Declare @magDetID int
Select @magDetID=MagDetId
From MagazineDetail
where MagID=@MagID
And SubscriptTypeID=@SubscriptTypeID

--now we can do the acutal insert into subscription
--I use the function we created in class earlier
--for the end date

Insert into Subscription
(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values
(@CustID, @MagDetID, @StartDate,
dbo.func_SubscriptionEndDate(@MagDetID, @StartDate))

--this is the end of the procedure so far

So, as is, this isn't much of a procedure. We still need to couch it in a transaction
so that it either all happens or non of it happens. To do that we will need to add a try catch structure. We will also modify it to check if the customer already exists. If they do we will only write the subscription, if not we will write both the customer and the subscription, But as I said at the top of the post. DONT PANIC. We will work through it slowly, step by step.

DVD ERD

Here is the ERD for the DVD database we did in class