Tuesday, January 31, 2012

Dentist Appointment ERD

Here is the ERD (Entity Relation Diagrom) for the Dentist office we did in class:

Monday, January 30, 2012

SubQueries


Use MagazineSubscription

Select MagID From MagazineDetail
Where SubscriptionPrice = 
(Select MAX(SubscriptionPrice) From MagazineDetail)

Select MagID, SubscriptionPrice, 
(Select AVG(SubscriptionPrice) From MagazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice >
(Select AVG(SubscriptionPrice) From MagazineDetail)

Insert into Customer(CustLastName, CustFirstName, 
CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values('John', 'Larry', '100 somewhere st','Seattle','Wa','98000',null)

Select CustID, CustLastName, CustFirstName
From Customer
Where CustID Not in (Select CustID from Subscription)

Select MagName 
 From Magazine
 Where MagID not in 
 (Select MagID 
  from MagazineDetail
  Where MagDetID in
  (Select MagDetID 
   from Subscription))
     
                  Select MagID 
      from MagazineDetail
   Where exists
   (Select MagDetID 
    from Subscription
    where SubscriptionPrice > 100)
     
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)

Use CommunityAssist
Select Distinct (Select MAX(hireDate) from Employee) as "Newest", 
(Select MIN(HireDate) From Employee) as "Oldest",
DateDiff(yy,  (Select MIN(HireDate) From Employee),
(Select MAX(hireDate) from Employee) ) as "Range"
From Employee

Friday, January 27, 2012

ITC 222 Assignment 7

Creating and altering tables

Automart has decided to implement a new inspection process where they go to each location at random times. They want to add the results of these inspections to the database. This involves adding three tables. One look up table that contains the various types of infractions or problems. One for the inspection date and location and another for the details of each inspection


The Problems

Create the following tables. Each table should be in the Schema "Employee".

InspectionCategory

It will contain the column CategoryID which will be an identity starting at 1 and incrementing by ones. This column will also be the primary key. The next column will be CategoryName which will provide the descriptive name of the category such as "cleanliness" or " waiting area" etc.

Inspection

This table will contain an InspectionID which is the primary key and and identity starting with 1 and incrementing by 1s. It will contain an InspectionDate and a LocationID which is a foreign key relating to the table location.

Inspection Detail

This table will contain the columns InspectionDetailID, an Identity and primary key, InspectionID, a foreign key, CategoryID a foreign key related to InspectionCategory and a Description column which will contain xml.


We also are going to make some changes to existing tables

Alter the Customer.RegisteredCustomer table to make emails unique. Do the same for passwords.

Also the CustomerSurvey table has check constraint on the column "rating" that limits it to a number between 1 and 10. The constraint name is CK__CustomerS__Ratin__25869641. Drop it and add a new constraint that limits it to numbers between 1 and 5.

We are also going to correct a mistake in the current database. The vehicleID in Employee.VehicleService should be a foreign key related to the VehicleID in Customer.Vehicle. Add the Foreign Key constraint to correct this oversite.


What I am looking for

I want to see the code for the three tables and the alter tables. I am especially looking at the primary key and foreign key constraints for those tables

Calculator Examples

I am posting two examples of Calculator code that work. They both have different but effective approaches. The first is From Victoria Bailey. I have only included the code from the form.

Victoria's only works with binary operations, that is [number] [operator] [number], for example 4 + 2, 3 * 5, etc. It doesn't work with examples like 4 + 3 * 2 / 2. Christian's does

Default.aspx
   <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="lblScreen" runat="server" Text=""></asp:Label><br />
        <asp:Button ID="Button1" runat="server" Text="1"  
onclick="btnNumber_Click" /><asp:Button ID="Button2"
runat="server" Text="2" onclick="btnNumber_Click" /> 
<asp:Button ID="Button3" runat="server" Text="3" onclick="btnNumber_Click" /><br />
        <asp:Button ID="Button4" runat="server" 
Text="4" onclick="btnNumber_Click" /><asp:Button ID="Button5"
            runat="server" Text="5" onclick="btnNumber_Click" />
<asp:Button ID="Button6" runat="server" Text="6" onclick="btnNumber_Click" /><br />
        <asp:Button ID="Button7" runat="server" 
Text="7" onclick="btnNumber_Click" /><asp:Button ID="Button8"
            runat="server" Text="8" 
onclick="btnNumber_Click" />
<asp:Button ID="Button9" runat="server" Text="9" 
onclick="btnNumber_Click" /><br />
        <asp:Button ID="Button10" runat="server" Text="0" 
onclick="btnNumber_Click" /><br />
        <asp:Button ID="Button11" runat="server" Text="+" 
onclick="btnOperation_Click" />
        <asp:Button ID="Button12" runat="server" Text="-" 
onclick="btnOperation_Click" />
        <asp:Button ID="Button13" runat="server" Text="*" 
onclick="btnOperation_Click" />
        <asp:Button ID="Button14" runat="server" Text="/" 
onclick="btnOperation_Click" />
        <br /><br />

        <asp:Button ID="btnCalculate"
            runat="server" Text="Calculate" onclick="btnCalculate_Click" /><br /><br />
        <asp:Label ID="lblAnswer" runat="server" Text="Answer: "></asp:Label>
        <asp:Label ID="lblCalculation" runat="server" Text=""></asp:Label><br /><br />

    </div>
    </form>



Code Behind:
    protected void Page_Load(object sender, EventArgs e)
    {
    }


    protected void btnCalculate_Click(object sender, EventArgs e)
    {
        string op = Session["operation"].ToString();
        double number = 0;
        double secondNumber = 0;
        double total = 0;
        if (lblScreen.Text != String.Empty)
        {
            secondNumber = double.Parse(lblScreen.Text);
        }
        if ((Session["firstNumber"] != null) && (Session["firstNumber"].ToString() != ""))
        {
            number = double.Parse(Session["firstNumber"].ToString());
        }
        switch (op)
        {
            case "+":
                total = number + secondNumber;
                break;
            case "-":
                total = number - secondNumber;
                break;
            case "*":
                total = number * secondNumber;
                break;
            case "/":
                total = number / secondNumber;
                break;
            default:
                break;
        }
        lblCalculation.Text = total.ToString();
    }

    protected void btnNumber_Click(object sender, EventArgs e)
    {
        Button button = (Button)sender;
        lblScreen.Text += button.Text;
    }

    protected void btnOperation_Click(object sender, EventArgs e)
    {
        Session["firstNumber"] = lblScreen.Text;
        Button button = (Button)sender;
        Session["operation"] = button.Text;
        lblScreen.Text = String.Empty;
    }


The second example is from Christian Roehr. It uses a very different approach. It is also well commented. Again I have only included the source from the form

>
 <form id="form1" runat="server">
 <div>
 <table>
 <tbody>

 <tr>
 <td colspan="4"></td>
     <asp:TextBox ID="txtDisplay" runat="server"></asp:TextBox>
 </tr>

 <tr>
 <td>
     <asp:Button ID="Button1" runat="server" Text="7" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="Button2" runat="server" Text="8" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="Button3" runat="server" Text="9" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="btnAdd" runat="server" Text="+" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>   
 </tr>

 <tr>
 <td>
     <asp:Button ID="Button5" runat="server" Text="4" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="Button6" runat="server" Text="5" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="Button7" runat="server" Text="6"
 CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="btnSubtract" runat="server" Text="-" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>   
 </tr>

 <tr>
 <td>
     <asp:Button ID="Button9" runat="server" Text="1" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="Button10" runat="server" Text="2" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="Button11" runat="server" Text="3" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="btnMult" runat="server" Text="*" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>  
 </tr>

 <tr>
 <td>
     <asp:Button ID="btnDel" runat="server" Text="Del" ToolTip="Use the <Del> button to clear the textbox in order to correct a value before clicking one of the operator buttons. The intermediate result and last operator are still kept in memory." CssClass="buttonstyle"
         onclick="btnDel_Click"/></td>
 <td>
     <asp:Button ID="Button14" runat="server" Text="0" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="Button15" runat="server" Text="." 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
 <td>
     <asp:Button ID="btnDiv" runat="server" Text="/" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>    
 </tr>

 <tr>
 <td>
     <asp:Button ID="btnClear" runat="server" Text="CL" ToolTip="Use the <CL> button to reset the calculator to its original state. Any prior values or operators are lost." CssClass="buttonstyle" OnClick="btnCl_Click"/></td>  
 <td colspan="3">
     <asp:Button ID="btnEqual" runat="server" Text="="  CssClass="buttonstyle" OnClick="Operator_Click"/></td>    
 </tr>  


 </tbody>
    
 </table>
 </div>
 </form>
</body>
</html>

======================== default.aspx.cs
using System;
using System.Diagnostics; // needed for "Debug" class
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
 // define unique integers to identify one of
 // {+,-,*,/,=}
 protected enum OperatorTypes
 {
     UNKNOWN=0,
     ADD = 1,
     SUBTRACT = 2,
     MULT = 3,
     DIVIDE = 4,
     EQUALS = 5
 };
 protected const double m_initialValue = 0.0;
 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack) // wonna do it when page loads but not every time when we push a button
     {
         InitializeCalculator();
     }
 }
 protected void InitializeCalculator()
 {
     // START: define the session variables
     //
     // Here is why the operator is initially "+".
     // When the user clicks any operator
     // op_new = one of {+,-,*,/,=}
     // then the evaluation of the
     // _previous_ operator (op_prev ) happens and _not_ the one that was just clicked (op_new).
     // Thus, if we want everything to work the same and if we want to save ourselves
     // too much extra specialized initialization-specific code, we can just assume that
     // the very first op_prev was "+" and that the previous result (result_prev) was 0.0.
     // Then , when op_new is clicked, we evaluate
     // result_new = execute(result_prev, op_prev, number);
     // Where "number" is the number the user typed in prior to pressing op_new. "number"
     // got parsed right when we clicked op_new.
     // We could just as well have selected Session["result"] = 1.0 and Session["operator"] = "*"
     // but "+" is simpler and somehow cleaner, avoiding possible rounding errors.
     //
     // In any future discussion, i will call the session variables
     // the "stack", since that is what we kind of do when we
     // store the value on the server side and update it or retrieve it.
     Session["result"] = m_initialValue; // this writes it to the server and gives it a value of 0.0
     Session["operator"] = OperatorTypes.ADD;

     // END: define the session variables

     // in order to facilitate the very first parsing of a number
     // down the line, let's put a "0" into the text field.
     // That is how most calculators work and it also
     // puts it in sync with what is in our initial session "result" value.
     InitializeTextDisplay();
 }
 protected void InitializeTextDisplay()
 {
     txtDisplay.Text = m_initialValue.ToString();
 }
 protected void EmptyTextDisplayIfContainsInitialValue()
 {
     if (txtDisplay.Text == m_initialValue.ToString())
     {
         txtDisplay.Text = "";
     }
 }
 protected void InputNumber_Click(object sender, EventArgs e)
 {
     // avoid this scenario from happening:
     // after entering a number, get something like 06, 0555, 000, 0024
     // We dont want such numbers since they may not be parsable, or even if
     // they are, they dont look pretty.
     EmptyTextDisplayIfContainsInitialValue();

     // the button that is sending the message
     Button b = (Button)sender;
     txtDisplay.Text += b.Text;

 }
 protected void Operator_Click(object sender, EventArgs e)
 {
     // the button that is sending the message
     Button b = (Button)sender;
     // we could use the ID or we could use the text

     // START: determine which operator was clicked
     //
     // FYI: Equals() operator more optimized to work with strings, but could have done '==' too
     //
     OperatorTypes op_new = OperatorTypes.UNKNOWN;
     if (b.ID.Equals("btnAdd"))
     {
         op_new = OperatorTypes.ADD;
     }
     else if (b.ID.Equals("btnSubtract"))
     {
         op_new = OperatorTypes.SUBTRACT;
     }
     else if (b.ID.Equals("btnMult"))
     {
         op_new = OperatorTypes.MULT;
     }
     else if (b.ID.Equals("btnDiv"))
     {
         op_new = OperatorTypes.DIVIDE;
     }
     else if (b.ID.Equals("btnEqual"))
     {
         op_new = OperatorTypes.EQUALS;
     }
     else
     {
         // this should not happen anyways if we wrote
         // the code correctly.
         op_new = OperatorTypes.UNKNOWN;
     }

     // for debugging only
     Debug.Write(String.Format("op_new={0}\n", (int)op_new));

     if (op_new == OperatorTypes.UNKNOWN)
     {
         Exception ex = new Exception("Invalid operator");
         throw ex;
     }

     // END: determine which operator was clicked

     // 1. Parse the number which user had entered up to
     // the point of clicking one of the operator buttons.
     double operandNumber = GetNumber();

     // 2. Get the last result from the stack.
     double result_prev = (double)Session["result"];

     // 3. Get the operator from the stack.
     // This operator is _not_ the one we just got, but the one
     // entered before user entered <operandNumber>
     OperatorTypes op_prev = (OperatorTypes)Session["operator"];
     Debug.WriteLine("op_prev={0}\n", (int)op_prev);

     // 4. evaluate the new result based
     // on <result_prev>, <op_prev>, <operandNumber>
     // For instance, if op_prev is "*" (multiplication), then
     //
     // result_new = result_prev * operandNumber
     double result_new = ExecuteOperator(result_prev, op_prev, operandNumber);
     Debug.WriteLine("result_new={0}\n", result_new);

     // test if the number is wellformed. That can be for one of the following reasons
     // a) divide-by-zero
     // b) overflow (two very large numbers multiplied or a very small one in the denominator)
     // c) perhaps some other reason
     if (double.IsInfinity(result_new) || double.IsNaN(result_new))
     {
         // first reset the calculator since you cannot utilize the result for any future operation
         InitializeCalculator();

         // throw an exception
         string numberString = string.Format("resulting number = {0} .", result_new);
         Exception ex = new Exception(numberString+ "You either divided by zero or the operands resulted in an out-of-range number");
         throw ex;
     }




     // 5. put new result on the stack
     Session["result"] = result_new;

     // 6. put new operator on the stack
     Session["operator"] = op_new;

     // 7. display the new result
     if (op_new == OperatorTypes.EQUALS)
     {
         // return result to text box
         txtDisplay.Text = result_new.ToString();
     }
     else
     {
         // as shown in the programming assignment specification,
         // ("and clear it to make it ready for the next entry")
         // clear the text (even though that is not the typical
         // calculator behavior i think. the typical calculator
         // shows the intermittent result and only clears after
         // you press the first digit of
         // the new number you are about to enter)
         InitializeTextDisplay();
     }
 }
 protected double GetNumber()
 {
     // double number = double.Parse(txtDisplay.Text);
     double number = 0;
     bool isDouble = double.TryParse(txtDisplay.Text, out number);
     if (isDouble == false)
     {
         txtDisplay.Text = "";
         Exception ex = new Exception("Only enter valid numbers");
         throw ex;
     }
     return number;
 }
 // Perform the operation
 // returnvalue = result_old op num
 // e.g. if op=OperatorTypes.ADD
 // you will get
 // returnvalue = result_old + num
 protected double ExecuteOperator(
     double result_old,
     OperatorTypes op,
     double num
     )
 {
     double result = result_old; // default in case of an invalid operator
     switch (op)
     {
         case OperatorTypes.ADD:
             result = result_old + num;
             break;
         case OperatorTypes.SUBTRACT:
             result = result_old - num;
             break;
         case OperatorTypes.MULT:
             result = result_old * num;
             break;
         case OperatorTypes.DIVIDE:
             result = result_old / num;
             break;
         case OperatorTypes.EQUALS:
             result = result_old;
             break;
         default:
             result = result_old;
             break;
     }
     return result;
 }
 protected void btnDel_Click(object sender, EventArgs e)
 {
     // as specified in the assignment
     // have a "clear" button to clear the textbox.
     InitializeTextDisplay();
 }
 protected void btnCl_Click(object sender, EventArgs e)
 {
     // if we dont have a CLEAR button to reset everything,
     // I found that funky things happen where the operators
     // stop doing the right things after clicking a certain
     // combination of equal sign and operators etc.
     // Thus we must have a way to clear all memory and reset
     // the stack to the beginning of what it was when the calculator
     // first started.
     InitializeCalculator();
 }
}

Wednesday, January 25, 2012

Joins


use CommunityAssist

Select LastName, Firstname, Street, [State], City, Zip
From Person
Inner Join PersonAddress
ON Person.PersonKey=PersonAddress.PersonKey

--
Select LastName, Firstname, Street, [State], City, Zip, ContactInfo
From Person, PersonAddress, personContact
WHERE Person.PersonKey=PersonAddress.PersonKey
AND Person.PersonKey=PersonContact.PersonKey

Select p.PersonKey, LastName, Firstname, Street, [State], City, Zip
From Person p
Inner Join PersonAddress pa
ON p.PersonKey=pa.PersonKey


Select LastName, Firstname, Hiredate, Dependents, Street, City,
[State], Zip
From Person p
inner join Employee e
on p.PersonKey=e.PersonKey
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey

select LastName, City
From Person
Cross join PersonAddress

Select ServiceName, GrantKey
From [Service] s
left outer join ServiceGrant sg
On s.ServiceKey=sg.ServiceKey
Where GrantKey is Null

Select ServiceName, GrantKey
From [Service] s
inner join ServiceGrant sg
on sg.GrantKey > s.ServiceKey

--a self join joins a table with itself
--here is a little script to show 
--an example

Create Database sample
GO
Use sample
Go
Create table Employee
(
EmployeeID int Primary key,
lastName varchar(30) not null,
Supervisior int
)
GO
Insert into Employee
Values(1,'Smith', 3)
Insert into Employee
Values(2,'Rogers', 3)
Insert into Employee
Values(3,'Johnson', null)
Insert into Employee
Values(4,'Larson', 2)
Insert into Employee
Values(5,'Standish', 3)

Select * from Employee
--the self join
Select e.LastName as employee, s.Lastname as supervisor
From employee e
Inner join employee s
on s.employeeid=e.supervisior 

Tuesday, January 24, 2012

ADO

The database scripts can be found on the syllabus. Here is a description of how to run the scripts with SQL Server Express through visual Studio

Running SQL scripts through Visual Studio

Default.Aspx
<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 
Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server">
        </asp:DropDownList>

        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" 
        Text="Get Donations" />
    </form>
</body>
</html>
Default.aspx.cs

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

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection connect = new SqlConnection 
("Data Source=localhost;initial catalog=communityassist;integrated security=true");

            //SqlConnection connect = new SqlConnection 
("Data Source=localhost;initial catalog=communityassist;user=manager;password=p@ssw0rd1");

            //SqlConnection connect2 = new SqlConnection();
            //SqlConnectionStringBuilder builder=new SqlConnectionStringBuilder();
            //builder.DataSource = "localhost";
            //builder.InitialCatalog = "community assist";

            string sql = "Select Distinct DonationDate from donation";

            SqlCommand cmd = new SqlCommand(sql, connect);

            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(ds, "Donations");

            DropDownList1.DataSource = ds.Tables["Donations"];
            DropDownList1.DataTextField= "DonationDate";
            DropDownList1.DataBind();
        }

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
         //ado objects

        SqlConnection connect = new SqlConnection
("Data Source=localhost;initial catalog=communityassist;integrated security=true");

        //SqlConnection connect2 = new SqlConnection();
        //SqlConnectionStringBuilder builder=new SqlConnectionStringBuilder();
        //builder.DataSource = "localhost";
        //builder.InitialCatalog = "community assist";

        string sql = "Select DonationDate, DonationAmount from donation Where DonationDate=@date";

        SqlCommand cmd = new SqlCommand(sql, connect);
        cmd.Parameters.AddWithValue("@date", DateTime.Parse(DropDownList1.SelectedItem.ToString()));

        DataSet ds = new DataSet();

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        da.Fill(ds,"Donations");

        GridView1.DataSource = ds.Tables["Donations"];
        GridView1.DataBind();

         
       
    }
}

Monday, January 23, 2012

Aggregate Functions

Here is the in-class code for Aggregate Functions
Use CommunityAssist 

--aggregate function

Select COUNT(PersonKey) "Number of Donations"
From Donation

--DISTINCT will return only unique values -- in the following
--it returns only unique persons for donors
Select COUNT(Distinct PersonKey) [Number of Donors]
From Donation

Select SUM(donationAmount) [total donations]
From Donation

Select Avg(donationAmount) "Average"
From Donation

Select MAX(donationAmount) "Highest"
From Donation

Select MiN(donationAmount) "Highest"
From Donation

--if you have an aggregate function in the SELECT
--any other column in the SELECT not a part of an 
--aggregate function must be contained in a
--GROUP BY clause
Select EmployeeKey, COUNT(DonationKey) "Count",
sum(DonationAmount) as Total
From Donation
Group by EmployeeKey

--this does several things:
--MONTH and YEAR are scalar functions
--that means they operate on one row at a time
--SUM is an aggreagate function operating on 
--multiple rows--so the MONTH and YEARfunctions
--must be contained in a group by clause
--the aliases don't work, you must do the functions
--themselves
--if you have an aggergate function in your 
--criteria you must use HAVING instead of WHERE
--you can use both in a query as in the example below
--the WHERE comes after the FROM
--the HAVING comes after the GROUP BY

Select MONTH(DonationDate) [Month], Year(DonationDate) [Year],SUM(donationAmount) total
From Donation
Where YEAR(DonationDate)=2010
Group by Year(donationDate), Month(DonationDate)
having SUM(donationAmount) > 3000

Here is a solution to the substring problem where it wouldn't return single worded entries. The problem was it was looking for a space and none existed. The solution, if inelegant is to concatenate in a space within the charIndex function so that every column has a trailing space.

Select SUBSTRING(VehicleMake, 1, CHARINDEX(' ', VehicleMake+' ', 1))
From Customer.Vehicle

Thursday, January 12, 2012

Calculator with Addition

Here is the calculator. I want to mention that when you do multiplication and division you will have to use a somewhat different pattern. There is only a zero in the session variable when you click the operator. That results in the final answer also equaling zero or infinity. Try to figure out how to do these, if you can and we will look at it again on Tuesday.

Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 
Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Calculator</title>
    <link href="calculator.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
    <tbody>
    <tr>
    <td colspan="4">
        <asp:TextBox ID="txtDisplay" runat="server"></asp:TextBox></td>
    
    </tr>
    <tr>
    <td>
        <asp:Button ID="Button1" runat="server" Text="7" 
CssClass="buttonstyle" OnClick="InputNumber_Click" /></td>
    <td>
        <asp:Button ID="Button2" runat="server" Text="8" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="Button3" runat="server" Text="9" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="btnAdd" runat="server" Text="+" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
    </tr>
    <tr>
    <td>
        <asp:Button ID="Button5" runat="server" Text="4" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="Button6" runat="server" Text="5" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="Button7" runat="server" Text="6" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="btnSubtract" runat="server" Text="-" 
CssClass="buttonstyle" OnClick="Operator_Click" /></td>
    </tr>
    <tr>
    <td>
        <asp:Button ID="Button9" runat="server" Text="1" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="Button10" runat="server" Text="2" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="Button11" runat="server" Text="3" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="btnMult" runat="server" Text="*" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
    </tr>
    <tr>
    <td>
        <asp:Button ID="btnDel" runat="server" Text="Del" 
CssClass="buttonstyle"/></td>
    <td>
        <asp:Button ID="Button14" runat="server" Text="0" 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="Button15" runat="server" Text="." 
CssClass="buttonstyle" OnClick="InputNumber_Click"/></td>
    <td>
        <asp:Button ID="btnDiv" runat="server" Text="/" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
    </tr>
    <tr>
   
    <td colspan="4">
        <asp:Button ID="btnEqual" runat="server" Text="=" 
CssClass="buttonstyle" OnClick="Operator_Click"/></td>
    </tr>
    </tbody>
    </table>
    </div>
    </form>
</body>
</html>



Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Session["result"] = 0.0;
        }

    }
    protected void InputNumber_Click(object sender, EventArgs e)
    {
        Button b = (Button)sender;
        txtDisplay.Text += b.Text;
    }
    protected void Operator_Click(object sender, EventArgs e)
    {
        Button b = (Button)sender;

        if (b.ID.Equals("btnAdd"))
        {
            Addition();
            Session["operator"] = "+";
        }
        if(b.ID.Equals("btnEqual"))
        {
            Calculate();
        }
    }

    protected void Addition()
    {
       
        try
        {
            double num = GetNumber();
            double numResult = (double)Session["result"];
            Session["result"] = num + numResult;
            txtDisplay.Text = "";
        }
        catch(Exception ex)
        {
            Response.Write(ex.Message);
        }

    }

    protected double GetNumber()
    {
       
        double number =0;
        bool isDouble = double.TryParse(txtDisplay.Text, out number);
        if (isDouble == false)
        {
            txtDisplay.Text = "";
            Exception ex = new Exception("Only enter valid numbers");
            throw ex;
        }
        return number;
    }

    protected void Calculate()
    {
        double number = (double)Session["result"];
        double number2 = GetNumber();
        if (Session["operator"].ToString().Equals("+"))
        {
            txtDisplay.Text = (number + number2).ToString();
        }
    }
}

Calculator.css
body {
}

.buttonstyle
{
    width:50px;
    
}

#txtDisplay
{
    text-align:right;
}

Wednesday, January 11, 2012

SQL Functions

--concatination

Select (Lastname + ', ' + firstname) as [Person Name]
From Person

-- Operators
Select DonationAmount, DonationAmount * .85 as [Charity]
From Donation

Select * From Donation
Select YEAR(DonationDate) as [Year] From Donation
Select MONTH(DonationDate) as [Month] from Donation
Select  Distinct Day(DonationDate) as [Day] from Donation


Select DATEPART(MM,DonationDate) From Donation
Select DatePart(hh,DonationDate) as [Month] from Donation

Select DATEDIFF(DD,'12/31/2011', GETDATE())
Select DateAdd(YY, 5, GETDATE())

Select * from PersonAddress

Select CHARINDEX(Street, ' ') From PersonAddress


Select SUBSTRING(Street, 1, 10) 
From PersonAddress

Select SUBSTRING(Street, 1, charindex(' ', Street)) 
From PersonAddress

Select * From Donation
Select '$' +CAST( DonationAmount as Varchar) as Amount from Donation

Select '(' + SUBSTRING(ContactInfo, 1,3) + ')' 
From PersonContact 
Where ContactTypeKey !=6

Select '$' +CAST(( DonationAmount * .85) as Varchar) as CharityAmount from Donation



Monday, January 9, 2012

SQL SELECT


Use CommunityAssist

Select Lastname, Firstname 
From Person;

Select * From Person

Select lastname as [Last Name], 
Firstname as [First Name]
From Person

--if there are no special characters or spaces
--you don't need brackets
Select lastname Surname, 
Firstname Name
From Person

/*this is a multi line
comment */

Select Distinct PersonKey from Donation
Order by PersonKey Desc

Select * from Donation
Order by donationAmount Desc

Select * from Donation
Order by 3 Desc

Select Top 10 DonationKey, DonationAmount
From Donation
Order by DonationAmount Desc

Select DonationKey, donationAmount
From Donation
Where DonationAmount > 1000

-- > < >= <= != <>

Select * From PersonAddress
Where City='Seattle'

Select * From PersonAddress
Where not City='Seattle'

Select * from Donation
Where DonationDate='2/2/2010'

Select * from Donation
Where DonationDate between '2/2/2010' And '2/6/2010'

Select * From PersonAddress
Where City='Kent' or City='Bellevue'

Select * From PersonContact
Where ContactInfo LIKE '306%'
-- _ for a single character ITC22_ 220 222 224 226

Select * From Donation
Where EmployeeKey in (3, 4, 5)

Select * from PersonAddress
Where Apartment is Not Null

Sunday, January 8, 2012

ITC 222 assignment six

Insert, Update, Delete


Problems


1-5 Insert the following new customer, his vehicle, and the services provided. He is not going to become a registered customer at this time. (This will require multiple insert statements. You will also need to look up the automatically generate keys to use as foreign keys.)

Jay Russell, license BFM349, Ford Fusion, 2011, at the new Tacoma shop. He got an oil change and new windshield wipers.

6. Registered customer 7 has a new email address. Change it to "laura26@yahoo.com" change the password to "moonshine"

7. Change the price of auto service 12 to $110.90

8. Delete AutoserviceDetailID 80 from vehicle service detail.



What I am Looking for


The insert requires some effort. Not only do you have to do multiple insert statements, you also have to look up vales, and the inserts must be done in the correct order. 1through 5 are wort 7 points. The others are worth 1 point each, though they are not without their dangers


To Turn In


Copy the SQL code to a Google doc and share it with spconger@gmail.com

Saturday, January 7, 2012

ITC 222 Assignment Five

Sub Queries




Problems


For each of these problems use subqueries for all parts of the problem, even though joins may make more sense to you.

1. List the name and price of the service with the maximum price.

2. Get a list of the first and last names of every person who is an employee.

3. Get a list of all the employee supervisors.

4. List the name of every person who is not a registered customer.

5. Get the license make and year of every vehicle that has been served. Some may appear more than once. You can use DISTINCT to not show duplicates.

6. Get the same information as in number five but only for those vehicles that have had service 12 completed on them.

7. Now repeat the query above, but include the service name in the select clause. (Remember, no inner joins).

8. Get the service price, the average service price and the difference between the two for every service. Sort them in descending order by service price. (This is one that can only be done with subqueries).



What I am looking for


I want only sub queries. 1 through 6 are worth 1 point each. 7 and 8 are worth 2 points each.


To Turn in


Post the SQL in a Google doc. Share it with spconger@gmail.com

ITC 222 Assignment 4

Joins


Inner Joins

1. Return the names (first and last) and emails of every registered user.

2. Do the same query based on the old style "where" based syntax.

3. List the customer names, the license plate, the vehicle make and year of every registered customer.

4. List the customer name, the license plate, the vehicle make and year and the services provided for the Vehicle service with the ID of 12.

5. List the location names and the names of all the employees that work at those locations.

Cross Joins

6. Do a cross join between Person and registered customer. Use the cross Join syntax.

7. Do a cross join using the older syntax

Outer Joins

8. List the customers people that do not have a registered customer account.

9. List any location names that has never provided a service for any car


What I am looking for

I want to see the SQL that would provide the data to the query. Each one is worth 1 pt. with one point for free for a total of 10 points.


To Turn in

Copy the SQL into a Google doc. Share it with spconger@gmail.com

Thursday, January 5, 2012

Stuf for the first Assignment

Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" 
Inherits="_Default"  Trace="false" ViewStateMode="Enabled"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 
Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Stuff</h1>
    <asp:DropDownList ID="DropDownList1" runat="server">
        
        </asp:DropDownList>
        <p><asp:Label ID="Label1" runat="server" Text="Enter Name"></asp:Label>
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox></p>
        <asp:Calendar ID="Calendar1" runat="server"></asp:Calendar>
        
    
        <asp:Button ID="btnConfirm" runat="server" Text="Confirm" 
            onclick="btnConfirm_Click" />
        
    
    </div>
    </form>
</body>
</html>

Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string[] colors = new string[5];
        colors[0] = "Blue";
        colors[1] = "Green";
        colors[2] = "Red";
        colors[3] = "Yellow";
        colors[4] = "Teal";

        if (!IsPostBack)
        {
            DropDownList1.DataSource = colors;
            DropDownList1.DataBind();
        }

        //DropDownList1.Items.Add("Red");
        //DropDownList1.Items.Add("Green");
    }
    protected void btnConfirm_Click(object sender, EventArgs e)
    {
        string name = txtName.Text;
        string date = Calendar1.SelectedDate.ToShortDateString();
        string chosenColor = DropDownList1.SelectedItem.ToString();

        //Response.Redirect("Confirmation.aspx?name=" 
        //    + name+"&date="
        //    +date+"&color="+chosenColor);

        Session["name"] = name;
        Session["color"]=chosenColor;
        Session["date"] = date;

        Response.Redirect("Confirmation.aspx");
    }
   
}

Confirmation.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Confirmation.aspx.cs" Inherits="Confirmation" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Confirmation Page</h1>
    </div>
    <asp:Label ID="lblName" runat="server" Text="Label"></asp:Label><br />
    <asp:Label ID="lblColor" runat="server" Text="Label"></asp:Label><br />
    <asp:Label ID="lblDate" runat="server" Text="Label"></asp:Label>


    </form>
</body>
</html>


Confirmation.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Confirmation : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //lblColor.Text = Request.QueryString["color"];
        //lblName.Text = Request.QueryString["name"];
        //lblDate.Text = Request.QueryString["date"];
        if (Session["color"] != null)
        {
            lblColor.Text = Session["color"].ToString();
            lblDate.Text = Session["date"].ToString();
            lblName.Text = Session["name"].ToString();
        }
        else
        {
            Response.Redirect("Default.aspx");
        }

        
    }
}