Inserts Updates and Deletes

Use MagazineSubscription

--the basic insert statement
--You must insert into every required
--column, columns and values are
--matched by sequence, first to first,
--second to second etc.
Insert into Customer(
CustLastName
, CustFirstName
, CustAddress
, CustCity
, CustState
, CustZipcode
, CustPhone)
Values(
'Custard'
, 'Colonel'
,'Library Study'
,'Seattle'
,'Wa'
,'98000'
,'2065550987')

--these are just for checking on things
Select * from customer
Select * From Magazine
Select * From MagazineDetail
Select * from SubscriptionType
Select * from Subscription

--an insert that uses a subquery and a function
Insert into Subscription (CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values((Select MAX(custID) from Customer),2,'2/7/2011', DATEADD(YY,1,'2/7/2011'))

--inserting multiple rows. This syntax only
--became available in SQL Server 2008
--otherwise had to write the complete Insert
--statement for each row
Insert Into Magazine(MagName, MagType)
Values('Trout fishers Anonymous', 'Quarterly'),
('Think Geek', 'Weekly'),
('Amiga User group', 'Annual')

--create a simple table
Create Table CallList
(
LastName nvarchar(255),
FirstName nvarchar(255),
Phone nvarchar(20)
)

--An insert that uses a select for the values
--the columns in the subquery need
--to be compatible in datatype.
--again they are matched by sequence
Insert Into CallList(LastName, FirstName, Phone)
(Select CustLastName, CustFirstName, custPhone From Customer)

Select * from CallList

--manually beginning a transaction creates
--the possibility of an undo
Begin Transaction

--update two columns in customer
--without the where clause every record
--woulc be updated
Update Customer
Set CustFirstName='Colonel',
CustAddress='Kitchen'
Where CustID=16

Select * from Customer

--if there is a mistake you can rollback
--all sql statements since the begin tran
--will be undone
Rollback Transaction

--if there is no error you can
--commit in order to write the changes
--to the database
Commit tran

--a delete statement
Delete from CallList
Where LastName='custard'
And FirstName='Colonel'

--stored procedure to get meta data
--about a table
exec sp_help Customer

--other system views
Select * from sys.Tables
Select * from sys.procedures
Select * from sys.Databases

--fully qualified columns
--running a query with values from a different
--database context
Select CommunityAssist.dbo.Person.LastName, CommunityAssist.dbo.Person.FirstName
From CommunityAssist.dbo.Person

java classes and Inheritance

Here is a simple case of making a class and extending it (inheriting from it.) First We create a simple boat class.

Boat.java

public class Boat {

//private class fields
private int boatSize;
private double cost;

//public gets and sets (accessors and mutators)
//for the fields
public int getBoatSize()
{
return boatSize;
}
public void setBoatSize(int size)
{
boatSize=size;
}

public double getCost()
{
return cost;

}
public void setCost(double price)
{
cost=price;
}

//a simple public method
public double CostPerFoot()
{
return cost/boatSize;
}
}

Here is the class the extends, inherits from Boat. It gets
all the public methods of the parent

MotorBoat.java

public class MotorBoat extends Boat
{
double horsepower;

public double GetHorsePower()
{
return horsepower;
}
public void SetHorsePower(double hp)
{
horsepower=hp;
}
}

Here is the program class that has the main method
and which uses the motorboat class

Program.java

public class Program {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

MotorBoat myBoat=new MotorBoat();
myBoat.setBoatSize(30);
myBoat.setCost(25000);
myBoat.SetHorsePower(20);
double cpf=myBoat.CostPerFoot();

System.out.println("the boat is " + myBoat.getBoatSize()+ " feet long");
System.out.println("the boat costs " + myBoat.getCost() + " dollars");
System.out.println("the horse power is " + myBoat.horsepower );
System.out.println("the cost per foot is" + cpf);

}

}

Here is the output from the program:

the boat is 30 feet long
the boat costs 25000.0 dollars
the horse power is 20.0
the cost per foot is 833.3333333333334

These classes are still very simple. They don't have constructors
or overridable methods. I will do that next.

Animal Shelter Diagram

Database Wizards

How to view the database in Visual Studio

From the view menu select Server Explorer.
At the top of Server Explorer right click on Database Connections
Choose new Database Connection
In the dialog box choose SQL Server Connection
In the next dialog box type localhost for server
in the drop down list for databases select the desired database
The database should show up in the server explorer
Click the little triangle beside it to expand it and veiw the tables and other database objects.

Wizards with databound controls

Drag a data bindable control such as a gridview onto the designer.
Click the little smart tag in the upper right corner
from the resulting menu choose data source/new data source
Choose SQL Database
If you have an existing connection string to the database you want to use, use it,
other wise choose new data string. It opens the dialog box where you enter
localhost and select the database
Once the connection string is established the dialog box opens where you
Select what table you want the data to come from.
You can select all or some of the fields
With the Where button you can set criteria for which rows to show
with the Sort button you can set the sort
With the advance you can set up Insert Update and Delete statements
Click next
Test you query
Finish

Subqueries

Use CommunityAssist

Select lastName, FirstName, City
From Person p, PersonAddress pa
Where p.PersonKey=pa.PersonKey
And not City = 'Seattle'

11. Select c.ContactTypeName
From ContactType c
Left Outer Join PersonContact pc
On c.ContactTypeKey=pc.ContactTypeKey
where pc.ContactTypeKey is null

Use MagazineSubscription

--an inner join and subquery criteria
Select Magname, SubscriptionPrice
From MagazineDetail md
Inner Join Magazine m
On m.MagID=md.MagID
Where SubscriptionPrice =
(Select MAX(SubscriptionPrice)
From MagazineDetail)

-- subquery for column definition
Select Magid, SubscriptionPrice,
(Select avg(SubscriptionPrice) from MagazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice >
(Select AVG(SubscriptionPrice)
From MagazineDetail)

--subqueries using "in" for subsets
-- the logic of this is
--Return the set of customers
--whose customer ids are in the set
--of subscriptions where the subscription's
--magazine detail id is in the set of Magazine
--detail ids which have a subscription price
--equal to the smallest subscription price
Select CustfirstName, CustLastname, (Select MIn(SubscriptionPrice) from MagazineDetail) as smallest
From Customer

Where CustID in --if custID here
(Select CustID --must be custid here
from Subscription

Where magDetID in --if magdetID here
(Select MagDetID --must be magdetid here
From Magazinedetail

Where SubscriptionPrice=
(Select MIn(SubscriptionPrice)
from magazineDetail)))

--using all
--****************************************
--when you use an comparitive with a subquery
--you must use all or any
--******************************************
--all matches each value in the subquery against all
--other values. In this case the subscription price
--must be greater than or equal to all other subscription
--prices, which returns only the maximum subscription
--price
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >= all
(Select SubscriptionPrice
From MagazineDetail)

--any says that the subcription price must be
--greater than any one of the other prices
--the effect is to return all but the smallest price
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice > any
(Select SubscriptionPrice
From MagazineDetail)

Select SubscriptTypeID, AVG(SubscriptionPrice) as Average
From MagazineDetail
Group by SubscriptTypeID

--this is a correlated subquery
--that means the subquery is dependent
--on a value from the main query
--for its completion
--it is equivalent to a recursive function
--in programming
--Note that like a self join the same table
--is aliased with two different aliases
--treating it like two tables
Select md.SubscriptTypeID, MagDetID, SubscriptionPrice
From magazineDetail md
Where SubscriptionPrice >=
(Select AVG(SubscriptionPrice)
From magazinedetail amd
where md.SubscripttypeID = amd.SubscripttypeID)

Select * from SubscriptionType

--exists returns a boolean does it exist
--in the subset or not
--a bit more efficient than in
Select Magname
From Magazine
Where Exists
(Select MagID
from MagazineDetail
Where SubscriptTypeID=5)

--another example of using exists
--to test whether a database exists
If exists
(Select name From sys.databases
where name = 'communityAssist')
Begin
print 'Yep its there'
End

Ajax Example

First we added the script manager to the page. this is necessary to use any of the Ajax controls. Then we added an Udate panel. This is the section of the page updatable by Ajax. The Ajax panel requires a content template. Inside the content template we added a radio button list with two buttons. One for yes and one for no. Under the list we added a simple panel from the main tool box and copied the content of the order form into the panel.

Here is a crude map of the layout:



Here is the changed source for Default2.aspx.

<%@ Page Title="Order Form" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<!--script manager must be on page. It handles the Javascript and xml for ajax-->
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

<h2>Order Your Computer</h2>
<p>Are you ready to order</p>


<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<!--Start content for ajax update panel-->
<!--radio button list in Ajax panel but not in the plain panel-->
<asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True"
onselectedindexchanged="RadioButtonList1_SelectedIndexChanged">
<asp:ListItem>Yes</asp:ListItem>
<asp:ListItem>No</asp:ListItem>
</asp:RadioButtonList>
<!-- start plain panel to box in form-->
<asp:Panel runat="server" ID="Panel1">
<!-- start order form-->
<p>Choose your size</p>
<asp:RadioButtonList ID="rdoSize" runat="server" cssclass="myList">
<asp:ListItem Value="400">15 Inch</asp:ListItem>
<asp:ListItem Value="500">17 Inch</asp:ListItem>
</asp:RadioButtonList>
<p>Choose Ram</p>
<asp:RadioButtonList ID="rdoRam" runat="server" CssClass="myList">
<asp:ListItem Value="100">3 gigabytes</asp:ListItem>
<asp:ListItem Value="150">4 gigabytes</asp:ListItem>
<asp:ListItem Value="250">8 gigabytes</asp:ListItem>
</asp:RadioButtonList>
<p>Choose Processor</p>
<asp:RadioButtonList ID="rdoProcessor" runat="server">
<asp:ListItem Value="100">I3</asp:ListItem>
<asp:ListItem Value="150">I5</asp:ListItem>
<asp:ListItem Value="200">I7</asp:ListItem>
</asp:RadioButtonList>

<br /><asp:Button ID="Button1" runat="server" Text="Submit"
onclick="Button1_Click" />
<!--end order form-->
</asp:Panel><!--end plain panel-->
<!--end ajax panel content-->
</ContentTemplate>
</asp:UpdatePanel>
</asp:Content>

Here is the code for the Default2.aspx.cs. the only real changes on this are the radiobutton_selectedIndexchanged event and the redirect with the url query string

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Panel1.Visible = false;
}

protected void Button1_Click(object sender, EventArgs e)
{
//this code executes when the button is clicked
//declare and instantiate the Computer class
Computer comp = new Computer();

//assign the selected values to class properties
comp.Size = rdoSize.SelectedItem.ToString();
comp.SizePrice = double.Parse(rdoSize.SelectedValue.ToString());
comp.Ram = rdoRam.SelectedItem.ToString();
comp.RamPrice = double.Parse(rdoRam.SelectedValue.ToString());
comp.Processor = rdoProcessor.SelectedItem.ToString();
comp.ProcessorPrice = double.Parse(rdoProcessor.SelectedValue.ToString());

//Save the object to a session variable
Session["myOrder"] = comp;

//redirect to the second page
Response.Redirect("Default3.aspx");

}

protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
Panel1.Visible = true;
}
else
{
//Panel1.Visible = false;
//redirect with a url passing a name value pair
Response.Redirect("Default4.aspx?msg=Please Come Again");
}
}
}

Here is the change on Default3.aspx.cs for the URL querystring

protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("Default4.aspx?msg=Thank You for your Order");
}

Here is the code on the confirmation page to read the query string and choose the label.

protected void Page_Load(object sender, EventArgs e)
{
msgLabel.Text = Request.QueryString["msg"];
}

Joins

Use MagazineSubscription

--inner join

Select CustLastName, CustFirstName,SubscriptionID,SubscriptionStart,SubscriptionEnd
From Customer c
Inner Join Subscription s
ON c.CustID=s.CustID

Select CustLastName, CustFirstName,SubscriptionID,SubscriptionStart,SubscriptionEnd
From Customer c, Subscription s
Where c.CustID=s.custID

--cross join
Select CustLastName, CustFirstName,SubscriptionID,SubscriptionStart,SubscriptionEnd
From Customer
Cross Join Subscription

Select CustFirstName,CustLastName,
SubscriptionID,MagName,SubscriptionPrice,
SubscriptionStart,SubscriptionEnd
From Customer c
Inner Join Subscription s
On c.CustID=s.CustID
Inner Join MagazineDetail md
on s.MagDetID=md.MagDetID
Inner Join Magazine m
on m.MagID=md.MagID
Where CustLastName='Jordan'


Select CustFirstName,CustLastName,
SubscriptionID,MagName,SubscriptionPrice,
SubscriptionStart,SubscriptionEnd
From Customer c, Subscription s,
MagazineDetail md, Magazine m
Where c.CustID=s.CustID
AND s.MagDetID=md.MagDetID
And m.MagID=md.MagID
And CustLastName='Jordan'

Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values('smith', 'joe','1000 elsewhere', 'Seattle', 'wa','98000','2065553456')

--outer joins
Select CustLastName, SubscriptionID
From Customer c
Left Outer Join Subscription s
On c.CustID=s.CustID
Where SubscriptionID is null

Select CustLastName, SubscriptionID
From Subscription s
Right Outer Join Customer c
On c.CustID=s.CustID
Where SubscriptionID is null


Use master

Create Database SelfJoinTest

Use SelfJoinTest

Create Table Employee
(
EmployeeID int primary key,
EmployeeLastName Nvarchar(255),
SupervisorID int
)

Insert Into Employee
Values(1,'Smith',2),
(2,'Jones',3),
(3, 'Brown',null),
(4, 'Able',2)

Select * From Employee

Select e.EmployeeLastName as "slave",
Boss.EmployeelastName as master
From Employee Boss
Inner Join Employee e
On e.SupervisorID=Boss.EmployeeID