Wednesday, January 29, 2014

Insert Update Delete 

Use Automart
--insert update delete

Insert into Person(LastName, FirstName)
Values('Watson', 'John')

Insert into Person(LastName, FirstName)
Values('Marley', 'Bob'),
('Carrol', 'Lewis'),
('Baggins', 'Bilbo')

Create table PersonTemp
(
   PersonKey int,
   Lastname nvarchar(255),
   FirstName nvarchar(255)
)

Insert into PersonTemp(PersonKey, lastName, Firstname)
Select PersonKey, Lastname, FirstName from Person

Insert into Person(LastName, FirstName)
Values(N'στεφενοσ', N'κονγεροσ')

Insert into Person(Lastname)
values('Dedealos')

Insert into Person(firstname)
values('Dedealos')

Insert into Person(LastName,firstname)
values('Madonna', null)

Select * from Person


Select * from PersonTemp
Order by Lastname

begin tran
Update Person
Set LastName='Anders',
firstname='Jason'
Where Personkey=1



rollback tran

Commit tran

Begin tran
Delete from Employee.VehicleServiceDetail
Select * from Employee.VehicleServiceDetail
Rollback tran
Commit tran

Drop Table PersonTemp
--Drop Database Automart
Posted by at No comments:

Monday, January 27, 2014

Subqueries 

use Automart

--basic aggregate
Select  Max(ServicePrice) from Customer.AutoService


--If you want to see which service has the max price you
--need to use a subquery in the where clause
Select ServiceName, ServicePrice From Customer.AutoService
Where ServicePrice = (Select max(ServicePrice) from Customer.AutoService)

--you can also use subqueries in the select clause
Select ServiceName, ServicePrice, 
(Select Max(ServicePrice) From Customer.Autoservice) as Maximum,
(Select Max(ServicePrice) From Customer.Autoservice)-ServicePrice as [Difference]
From Customer.Autoservice

--this one goes a little crazy, the idea is that
--we will show the total count of auto's served
--the we will show the counts for each individual
--location and then what percent each represents
--of the total.
--there are three casts. The innermost cast converts
--the division to decimal to preserve the decimal part
--(count returns an integer)
--the next cast (second one in) converts the whole
--result to decimal to limit the number of decimal places
--showing. The outermost cast converst the whole expression
--to nvarchar in order to concatinate the % sign in
--
Select (Select count(*) From Employee.VehicleService) Total,
LocationName, count(*) [Number Per Location], 
cast(cast(cast(count(*) as decimal(4,2)) / 
(Select count(*) From Employee.VehicleService) 
* 100 as decimal(4,2))as Nvarchar) + '%' [Percent]
From Employee.VehicleService vs
Inner join Customer.Location a
on a.LocationID=vs.LocationID
Group by LocationName

--the in keyword returns any value that matches
--one of the values in the result set
--here the second query
Select Distinct ServiceName from Customer.AutoService
Where autoserviceId in (Select autoServiceId from Employee.VehicleServiceDetail)

--using not with in has the same result as an outer join
Select Distinct ServiceName from Customer.AutoService
Where autoserviceId not in (Select autoServiceId from Employee.VehicleServiceDetail)

--You can link several tables with "in"
--the logic is the same as for joins
--primary key to foreign key
--follow the relationship path to get the data
--you want
Select LicenseNumber, VehicleMake, VehicleYear
From Customer.Vehicle
where VehicleId in 
(Select VehicleID from Employee.VehicleService 
where VehicleServiceID in
(Select VehicleServiceID from Employee.VehicleServiceDetail 
 Where AutoserviceID=15))

--another example, also including a query
--in the select clause to return the name of the
--autoservice
Select (Select ServiceName from Customer.AutoService where AutoserviceID=12) ServiceName,
LicenseNumber, VehicleMake, VehicleYear
From Customer.Vehicle
where VehicleId in 
(Select VehicleID from Employee.VehicleService 
where VehicleServiceID in
(Select VehicleServiceID from Employee.VehicleServiceDetail 
 Where AutoserviceID=12))

--in can be used with litteral sets as well
Select * from Employee.VehicleServiceDetail where AutoserviceID in (9, 11, 12)

--exits returns a boolean yes/no
Select * from Employee.VehicleServiceDetail where not exists 
(Select AutoserviceID from Employee.VehicleServiceDetail where autoserviceID=12)

--I often use exists to test for the existence of an object 
if exists
(Select name from sys.Databases where name = 'communityAssist')
Begin
Print 'Yep it''s there'
End

Correlated subquery

Here is a database called Magazine that the subquery is based on

--Drop Database MagazineSubscription
--create the database no options used
Create Database MagazineSubscription
Go
--use the database
Use MagazineSubscription

/*Create the tables with constraints
This needs to be done in a certain order
The primary key tables, the one side of
a relation must be done before the many
side of a relation */

Create table Magazine
(
  MagID int identity(1,1),
  MagName varchar(100) not null,
  MagType char(10) not null default 'Monthly',
  Constraint PK_Magazine Primary Key(MagID),
  
)

Create table SubscriptionType
(
 SubscriptTypeID int identity(1,1),
    SubscriptTypeName char(15),
    Constraint PK_SubscriptionType Primary Key (SubscriptTypeID)
)

Create table MagazineDetail
(
 MagDetID int Identity(1,1),
 MagID int not null,
    SubscriptTypeID int not null,
 SubscriptionPrice money,
    Constraint PK_MagazineDetails Primary Key(MagDetID),
    Constraint FK1_MagazineDetails Foreign key(MagID)
  References Magazine(magID),
    Constraint FK2_MagazineDetails Foreign Key(SubscriptTypeID)
  References SubscriptionType(subscriptTypeID)
)

Create Table Customer
(
 CustID int identity(1,1),
 CustLastName varchar(30) not null,
 CustFirstName varchar(25)null,
 CustAddress varchar(100) not null,
 CustCity varchar(50) not null,
 CustState char(2) not null,
 CustZipcode char(11) not null,
 CustPhone char(10),
 Constraint PK_Customer Primary Key (custID)
)

Create Table Subscription
(
 SubscriptionID int identity(1,1),
 CustID int not null,
 MagDetID int not null,
 SubscriptionStart DateTime not null,
 SubscriptionEnd Datetime,
    Constraint PK_Subscription Primary Key (subscriptionID),
 Constraint FK1_Subscription Foreign Key (CustID)
  References Customer(custID),
 Constraint FK2_Subscription Foreign Key(MagDetID)
  References MagazineDetail(MagDetID)
)

--insert into magazine

Insert into Magazine(MagName, MagType)
Values('Procastinators Anonymous','Monthly')

Insert into Magazine(MagName, MagType)
Values('IT Toys','Monthly')

Insert into Magazine(MagName, MagType)
Values('FireEaters Quarterly','Quarterly')

Insert into Magazine(MagName, MagType)
Values('Waste Not Want Not, A hoarders guide','Monthly')

Insert into Magazine(MagName, MagType)
Values('SQL Server','Monthly')

Insert into Magazine(MagName, MagType)
Values('Extreme Programming','Monthly')

Insert into Magazine(MagName, MagType)
Values('Insurance Actualization algorythms','Quarterly')

Insert into Magazine(MagName, MagType)
Values('Doctor Who Magazine','Weekly')

Insert into Magazine(MagName, MagType)
Values('XBox Anonymous','Monthly')

Insert into Magazine(MagName, MagType)
Values('Beer bottle Target Practice','Quarterly')


--Inserts for subscription types

Insert into SubscriptionType (subscriptTypeName)
Values('Six Month')

Insert into SubscriptionType (subscriptTypeName)
Values('Yearly')

Insert into SubscriptionType (subscriptTypeName)
Values('Three Month')

Insert into SubscriptionType (subscriptTypeName)
Values('Two Year')

Insert into SubscriptionType (subscriptTypeName)
Values('five Year')

Insert into SubscriptionType (subscriptTypeName)
Values('Three Year')

--Inserts into MagazineDetail

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 1, 23.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 2, 38.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 5, 74.29)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 1, 35.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 2, 52.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 4, 77.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 5, 99.29)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(3, 2, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(3, 5, 200.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 1, 25.00)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 3, 15.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 4, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 5, 75.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 6, 55.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(5, 2, 38.75)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(5, 6, 78.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(6, 2, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(6, 6, 99.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(7, 5, 134.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 1, 15.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 2, 21.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 3, 8.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 4, 35.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(9, 2, 38.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(10, 5, 78.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(10, 6, 58.50)

--Inserts into Customer

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Jordan','Mary','2002 South Mercer Street','Seattle','WA',
'98190','2065558828')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Danner','Thomas','100 Boardwalk South','Seattle','WA',
'98190','2065551001')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Terrance','Sarah','202 Rt 3','Bellevue','WA',
'98120','3605550128')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Camlin','Lenny','Somewhere Ave','Olympia','WA',
'98199','2535551010')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Sanderson','Lewis','101 Elsewhere Avenue','Seattle','WA',
'98190','2065550987')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Able','Tina','1000 West Blv','Bellingham','WA',
'98180','3605552020')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Evans','Karl','11 North Hill street','Tacoma','WA',
'98100','2535558998')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Zukof','Bob','WaterFront Blvd.','Bellevue','WA',
'98120','3605552435')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Martinez','Patrick','EastLake Blvd.','Seattle','WA',
'98220','2065553679')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Patterson','Lisa','1010 Binary Drive','Redmond','WA',
'98130','3605551100')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Franklin','Bernice','222 ITC Road','Olympia','WA',
'98199','3605552221')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Kim','Susan','111 Martin Luther King Way','Seattle','WA',
'98122','2065550742')

--Insert into subscription

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 4, '1/15/2006', '7/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 14, '1/15/2006', '1/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (2, 7, '1/15/2006', '1/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (3, 8, '1/2/2006', '1/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 11, '2/01/2006', '2/01/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 4, '2/01/2006', '9/01/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (5, 16, '1/05/2006', '1/05/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 5, '2/15/2006', '2/15/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 12, '2/15/2006', '2/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 18, '2/15/2006', '2/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (7, 18, '3/01/2006', '3/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (8, 20, '3/01/2006', '3/01/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (9, 22, '3/10/2006', '3/10/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 4, '3/15/2006', '10/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 23, '3/15/2006', '3/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (11, 11, '3/20/2006', '3/20/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (12, 17, '4/01/2006', '4/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 18, '4/01/2006', '4/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 4, '4/15/2006', '10/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 24, '4/15/2006', '4/15/2009')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 18, '4/15/2006', '4/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (3, 14, '4/15/2006', '4/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (12, 22, '4/15/2006', '4/15/2007')

Here is the correlated subquery

use MagazineSubscription

 --a correlated subquery is when the subquery uses a value in the 
 --outer query as part of its criteria
 --it results in something resembling a recursive function
 --in this case what it does is makes sure that
 --like is compared to like
 --subscription type 1 (one year) is compared only to other
 --subscription type 1's and subscription type 5 (five year) 
 --is compared only to other subscription type 5's etc.

Select subscriptTypeID, MagDetID, SubscriptionPrice
From MagazineDetail md
Where subscriptionPrice >=
(Select Avg(SubscriptionPrice) from magazineDetail md2
 where md.SubscriptTypeID=md2.SubscriptTypeID)
Posted by at No comments:
Labels:

Wednesday, January 22, 2014

Joins 

--Joins

--inner join, cross, outer joins 

Use Automart

--basic inner join with join syntax
--in the on clause you specify how the two tables
--relate
Select[FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person 
Inner Join Customer.Vehicle
On Person.Personkey=Customer.Vehicle.Personkey 
Order by LastName

--the "inner" key word is the default and not required
--though I think it is a good idea for clarity
Select[FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person 
Join Customer.Vehicle
On Person.Personkey=Customer.Vehicle.Personkey
Order by LastName

--same thing but with the tables aliased
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person p
Join Customer.Vehicle v 
On p.Personkey=v.Personkey
Order by LastName

Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person p, Customer.Vehicle v
Where p.Personkey=v.PersonKey

--this results in a cross join
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person p, Customer.Vehicle v

-- two tables joined. You can join as many tables as you need
-- by repeating inner join and on
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear], email
From Person as p
Inner Join Customer.Vehicle as v 
On p.Personkey=v.Personkey
Inner Join customer.RegisteredCustomer as  rc
on p.Personkey=rc.PersonKey
Order by LastName

--same thing in an older syntax
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear], email
From Person p, Customer.Vehicle v, Customer.RegisteredCustomer rc
Where p.Personkey=v.PersonKey
And p.FirstName=rc.PersonKey

--explicit cross join
Select person.Personkey, LastName, FirstName, LicenseNumber, VehicleMake, VehicleYear
From Person 
Cross join Customer.Vehicle

--in an left outer join all the records in the first table
--are returned, only the matching records in the second
--table are returned. Where there is no matching record
--the result set displays a null
--outer joins are good for finding mis-matched data
--customers who never purchased anything, for instance

Select ServiceName, Employee.VehicleServiceDetail.AutoServiceID
from Customer.Autoservice
left outer join Employee.VehicleServiceDetail
on Customer.Autoservice.AutoServiceID=Employee.VehicleServiceDetail.AutoServiceID
Where Employee.VehicleServiceDetail.AutoServiceID is null


Select Lastname, rc.Personkey
From Person p
Left outer join Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
Where rc.PersonKey is null

--in a right join its the second table that
--returns all its records
Select LastName, rc.Personkey
From Customer.RegisteredCustomer rc
right outer join Person p
on p.Personkey=rc.PersonKey
Where rc.PersonKey is null

--full join returns all the records from both tables
--same results really as outer join
Select LastName, rc.Personkey
From Customer.RegisteredCustomer rc
full join Person p
on p.Personkey=rc.PersonKey

--just need a record with no matches
Insert into Customer.AutoService(ServiceName, ServicePrice)
Values ('Alternator Replacement', 550.00)
Posted by at No comments:
Labels:

Wednesday, January 15, 2014

Notes on Assignment2

using HTTP Get in Asp.net

Here is the code for transferring more than one value via the HTTP query string. The first code is on default2 in the Confirm button.

protected void btnConfirm_Click(object sender, EventArgs e)
    {
        Response.Redirect("Default3.aspx?lastname="+ txtLastName.Text + "&firstname=" + txtFirstName.Text);
    }

The second part, reading the values from the string is in the page load event of Default3

string lastName = Request.QueryString["lastname"];
        string firstName = Request.QueryString["firstname"];
        Label1.Text = "Thank you for submission, " + lastName + ", " + firstName;

Things to think about

The pattern is more important than the detail. You can always look up the details. So what is the patter?

The Master page is a template that lets you create a consistent look and feel for a web site.

When you use a master page, you want to add content pages not web forms. Content pages will use the master pages, web forms are independent.

The assignment is basically moving the content of a form from one page to another. The thing to take away from it, is that it is better to create an object to store all the information (our customer class) and move one thing, rather than move each value separately.

Web pages are stateless, meaning they don't retain values. Each page is also independent of the every other page. Generally, one web page cannot see what's on another. So to move things you have to use a Session variable or a cookie to store the value and then call that session or cookie on the next page. (the same holds true to save a value between page refreshes which happen with each page button click)

Posted by at No comments:
Labels:

Aggregate Functions 

--Aggregate functions: count, sum, avg, min, max
use Automart;
--counts all rows
Select count(*) as [number] from Person

Select Count(*) [Number of Employees]From Employee
Where LocationID=1

--aggregate functions ignore nulls
Select count(SupervisorID) [Supervisors] from Employee
Where LocationID=1

Select * from Employee

--
Select Sum(ServicePRice) from Customer.Autoservice
Select avg(ServicePrice) from customer.Autoservice
Select min(ServicePrice) from Customer.Autoservice
Select ServiceName, Max(ServicePrice) from Customer.AutoService
Group by ServiceName

--for future reference, how to get which row matches
--a min or max value. Need to use a subquery
Select ServiceName, ServicePrice
from customer.autoservice
Where ServicePrice = (Select max(servicePrice) From customer.AutoService)

--you must group by any column that is not
--a part of the aggregate function
--in this case, locationID
Select LocationID, count(VehicleServiceID) [total services]
From Employee.VehicleService
Group by LocationID

--the where clause can still be used if the criteria is a 
--simple 'scalar', row by row value. Having is used 
--when the criteria is an aggregate function 
--the where must always come before the group by
--the having always comes after
Select LocationID, month(ServiceDate) [Month], count(VehicleServiceID)[total Services]
From Employee.VehicleService
where LocationID=2
Group by LocationID, month(ServiceDate)
having count(vehicleServiceID) > 5
order by locationID
Posted by at No comments:
Labels:

Thursday, January 9, 2014

Assignment1

Default.aspx

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="AssignmentOneStyle.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Tip Calculator</h1>
        <p>
            <asp:Label ID="Label1" runat="server" Text="Enter the amount">
            </asp:Label> 
            <asp:TextBox ID="txtAmount" runat="server"></asp:TextBox>

        </p>
        <asp:RadioButtonList ID="rdbPercentage" runat="server">
            <asp:ListItem Text="10%" Value=".1" />
            <asp:ListItem Text="15%" Value=".15" Selected="true"></asp:ListItem>
            <asp:ListItem Text="20%" Value=".2"></asp:ListItem>
            <asp:ListItem Text="other" Value="0"></asp:ListItem>
        </asp:RadioButtonList> 
        <p><asp:TextBox ID="txtOther" runat="server" placeholder="other"></asp:TextBox></p>
        <asp:Button ID="btnCalculate" runat="server" Text="Submit" OnClick="btnCalculate_Click" />
        <p>
            <asp:Label ID="lblTip" runat="server" Text="" CssClass="result"></asp:Label><br />
            <asp:Label ID="lblTotal" runat="server" Text="" CssClass="result"></asp:Label>
        </p>
    </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)
    {

    }
    protected void btnCalculate_Click(object sender, EventArgs e)
    {
        double amount = 0;
        bool goodAmount = double.TryParse(txtAmount.Text, out amount);
        if (!goodAmount)
        {
            Response.Write("<script>alert('Please enter a valid amount');</script>");
            txtAmount.Text = "";
            txtAmount.Focus();
            return;
        }
        double percent=0;
        if(rdbPercentage.SelectedItem.Text != "other")
        { 
            percent = double.Parse(rdbPercentage.SelectedValue.ToString());
        }
        else
        {
            bool goodPercent = double.TryParse(txtOther.Text, out percent);
            if (!goodPercent)
            {
                Response.Write("<script>alert('Please enter a valid Percentage');</script>");
                txtOther.Text = "";
                txtOther.Focus();
                return;
            }
        }
        TipCalculator Tip = new TipCalculator(amount, percent);
        lblTip.Text = Tip.CalculateTip().ToString("$##0.##");
        lblTotal.Text = Tip.CalculateTotal().ToString("$##0.##");
    }
}

CalculateTip.cs

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

/// 
/// Summary description for TipCalculator
/// 
public class TipCalculator
{

    private double amount;
    private double percent;
 public TipCalculator()
 {
        Amount = 0;
        Percent = 0;
 }
    public TipCalculator(double amt, double perc)
    {
        Amount = amt;
        Percent = perc;
    }


    public double Amount
    {
        set { amount = value; }
        get { return amount; }
    }

    public double Percent
    {
        set
        {
            if (value > 1)
            {
                value /= 100;
            }
            percent = value;
        }
        get
        {
            return percent;
        }
    }

    public double CalculateTip()
    {
        return Amount * Percent;
    }

    public double CalculateTotal()
    {
        return Amount + CalculateTip();
    }
}

AssignmentOne.css

body {
}
h1
{
    background-color:navy;
    color:white;
    border-bottom:5px solid black;
}

.result{
    color:green;
}
Posted by at No comments:
Labels:

Wednesday, January 8, 2014

History scope

Bakery

History

Jenny has been running a bakery for 3 years. She has tried to keep to track of her sales and inventory on a spreadsheet, but has not managed to get clear idea of what her costs and profits if any are. She would like a database to track her real inventory costs as well as point of sale receipts.

Scope

The bakery database will track inventory purchases and usage. It will track waste. It will track sales and labor costs. The database will also track overhead costs. It will allow the owner to compare different types of sales and costs. It will also allow the tracking of customers and customer rewards.

Objective

The database will make the profit/ loss analysis of the bakery more transparent

TimeLine

  • Basic scope and history
  • Gather information—2 weeks
  • Requirements /Business Rules--week
  • Design –week
  • Normalize—testing
  • Build database –add sample data
  • Test it with sql
  • Security and disaster recovery analysis
Posted by at No comments:
Labels:

Simple Selects 

Use Automart
/*this is a multiline
comment. this example is 
for assignment 1*/

Select * From Person;

--Simple select listing columns
Select FirstName, LastName 
From Person

Select Lastname, firstName
from Person
order by Lastname 

Select Lastname, Firstname
From person
order by lastname desc, firstname

Select lastname as [Last Name], firstName as [first Name]
From Person

--preview
Select ServiceName, servicePrice, 
cast(Round(ServicePrice * .09, 2)as decimal(5,2)) Tax
From Customer.Autoservice

Select Servicename, servicePrice
From Customer.Autoservice
Where serviceName='tune up'

Select * From Employee.VehicleService
Where ServiceDate='2/25/2010'

Select ServiceName, ServicePrice
From customer.AutoService
Where  ServicePRice   > 100

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

Select Distinct VehicleID from Employee.VehicleService
order by VehicleID

Select * from  Employee
Where supervisorID is not null

Select * from  Employee
Where supervisorID is not null
And SupervisorID=6


Select * from  Employee
Where supervisorID is  null
or SupervisorID=6

Select * from Employee.VehicleService
Where ServiceDate between '2/1/2010' and '2/28/2010'

Select GetDate()

Select * from Person
order by lastname

Select * from Person
where lastname like 'C%l'

--% any number of characters
-- _ for one character
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)