Advanced Relational Database */ If & Case Statements Assignment */

use AdventureWorks

--1.The boss needs a quick query.  
--All he needs is a query with Sales Order Id and whether the order was online or offline.  
--Select those two columns and display either Offline or Online in the second column

select soh.SalesOrderID, soh.OnlineOrderFlag,
(case soh.OnlineOrderFlag
 when 0 then 'Offline'
 when 1 then 'Online'
End) as Status
from Sales.SalesOrderHeader soh

--2.It's time for bonuses again.  Go through all sales, listing the  firstname and lastname 
--for the salesperson, as well as the sales level.  
--less than a $50,000 sale is the Tin level
--less than a $100,000 sale is the Silver level
--Anything over $100,000 sale is the gold level

select pc.FirstName, pc.LastName, soh.TotalDue,
'Level'= case
 when soh.TotalDue < 50000 then 'Tin Level'
 when soh.TotalDue< 100000 then 'Silver Level'
 when soh.TotalDue > 100000 then 'Gold Level'
End
from Person.Contact pc
inner join Sales.SalesOrderHeader soh on pc.ContactID = soh.ContactID

--3. Using an if statement, you are going to write a query that has two modes.  
--If a variable is set to Sales mode,  it will give you a unique list of all the names of sales people in 2004.  
--If the variable is set to Customer mode, 
--it will give you a unique list of all the names of the customers in 2004.

Declare @SalesMode varchar(50)
set @SalesMode = 'c'

if @SalesMode = 's'
begin
 select distinct pc.FirstName, pc.LastName
 from Sales.SalesOrderHeader soh
 inner join Sales.SalesPerson sp on soh.SalesPersonID = sp.SalesPersonID
 inner join HumanResources.Employee hre on sp.SalesPersonID = hre.EmployeeID
 inner join Person.contact pc on hre.ContactID = pc.ContactID
 where soh.orderdate > '20040101'
end
else if @SalesMode = 'c'
begin 
 select distinct pc.FirstName, pc.LastName
 from Sales.SalesOrderHeader soh
 inner join Person.Contact pc on pc.ContactID = soh.ContactID
 where soh.orderdate between '20040101' and '20050101'
end

select * from Sales.SalesPerson
select * from Sales.SalesOrderHeader
select * from Sales.Customer
select * from Sales.CustomerAddress

Programming 2 */ Methods Assignment */

Public Class Form1

    '1. Create a single method that will be responsible for creating the place holder for each of the dice numbers.
    'a. You will likely need to pass the label in as an attribute in the method call
    '2. Create a single method that will be responsible for adding the button and 3 stats text boxes
    '3. Add a method who's job is just to roll the dice.
    '4. Add a method who's job is to collect the stats of how many there are of each number
    '5. Add a method who's job is to update the stats textboxes
    '6. Add comments to the code blocks to explain what is going on.
    'CODE:
    'Declaring variables
    Dim lblDice1, lblDice2, lblDice3, lblDice4, lblDice5 As New Label
    'To add events to the button
    Dim WithEvents butRoll As New Button
    'Declaring variables
    Dim nYatzee, nFourOfAKind, nThreeOfAKind As New Integer
    Dim lblYatzee, lblFourOfAKind, lblThreeOfAKind As New TextBox
    'Declaring random points
    Dim rnd As New Random

    'This sub is to add the dice numbering and randomization. Also used for the font and size of the label. And to add the controls.
    Private Sub addDice(ByRef lbl As Label, ByVal x As Integer, ByVal y As Integer)
        lbl.Text = 0
        lbl.Location = New Point(x, y)
        lbl.Font = New Drawing.Font("Microsoft Sans Serif", 28.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point)
        lbl.Height = 40
        lbl.Width = 40
        Me.Controls.Add(lbl)
    End Sub

    'This sub is to add the hand to the textboxes. Also for the size and to add the controls.
    Private Sub addHand(ByRef txt1 As TextBox, ByRef txt As String, ByVal x As Integer, ByVal y As Integer)
        txt1.Text = txt
        txt1.Location = New Point(x, y)
        Me.Controls.Add(txt1)
        txt1.Width = 150
    End Sub

    'This sub adds the roll button onto the form.
    Private Sub addRoll(ByRef btn As Button, ByVal txt As String, ByVal x As Integer, ByVal y As Integer)
        btn.Text = txt
        btn.Location = New Point(x, y)
        Me.Controls.Add(btn)
    End Sub

    'When the button is clicked it randomizes the numbers
    Private Sub addDi(ByVal lblDice As Label)
        lblDice.Text = rnd.Next(1, 7)
    End Sub

    Private Sub addText(ByRef txt As TextBox, ByVal txt1 As String)
        'This sub is used to add the calculation to the textboxes in the form
        txt.Text = txt1
    End Sub
    Private Sub addCalculation()
        'Declaring the variable
        Dim arrNumbers() As Integer = {0, 0, 0, 0, 0, 0}
        'For loop to randomize the numbers and determine what is a yahtzee, four of a kind, or three of a kind.
        For Each lbl As Label In Me.Controls.OfType(Of Label)()
            arrNumbers(lbl.Text - 1) += 1
        Next

        For Each i As Integer In arrNumbers
            If i = 5 Then
                nYatzee += 1
            ElseIf i = 4 Then
                nFourOfAKind += 1
            ElseIf i = 3 Then
                nThreeOfAKind += 1
            End If
        Next
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'These are linked to the subs and then added to the form1load.
        addDice(lblDice1, 10, 20)
        addDice(lblDice2, 70, 20)
        addDice(lblDice3, 130, 20)
        addDice(lblDice4, 190, 20)
        addDice(lblDice5, 250, 20)

        addHand(lblYatzee, "Yahtzees: 0", 20, 140)
        addHand(lblFourOfAKind, "Four Of A Kind: 0", 20, 180)
        addHand(lblThreeOfAKind, "Three Of A Kind: 0", 20, 220)

        addRoll(butRoll, "Roll", 100, 90)

    End Sub

    Private Sub RollDice() Handles butRoll.Click
        'all of these calculate when the button is clicked.
        addDi(lblDice1)
        addDi(lblDice2)
        addDi(lblDice3)
        addDi(lblDice4)
        addDi(lblDice5)

        addCalculation()

        addText(lblYatzee, "Yahtzee: " & nYatzee)
        addText(lblFourOfAKind, "Four Of A Kind: " & nFourOfAKind)
        addText(lblThreeOfAKind, "Three Of A Kind: " & nThreeOfAKind)

    End Sub
End Class

Advanced Relational Database */ If & Case Statements */

If and Else If Statement

use AdventureWorks

declare @num int
set @num = 3
declare @salespersonid int

select @num

if @num = 2
begin
 set @salespersonid = (select top 1 Sales.SalesPerson.SalesPersonID from Sales.SalesPerson)
end
else if @num = 4
begin
 select 'you suck'
end
else
begin
 select 'nun of the above'
end

Case Statement

select FirstName, LastName,
 (case EmailPromotion
 when 1 then 'EmailPromo'
 when 2 then 'TextPromo'
 else 'Leave me alone'
 End)
 as promo
from Person.Contact

Advanced Relational Database */ Subqueries */

use Adventureworks

--Select first name, last name, and email of employees who are sales people
select pc.FirstName, pc.LastName, pc.EmailAddress
from Person.Contact pc
 inner join HumanResources.Employee hre on pc.ContactID = hre.ContactID
 where hre.EmployeeID 
 in 
  (
  select ssp.SalesPersonID
  from Sales.SalesPerson ssp
  )

--Get emails of all sales people who have made a sale greater than $150k 
select pc.EmailAddress
from Person.Contact pc
 inner join HumanResources.Employee hre 
 on pc.ContactID = hre.ContactID
 inner join Sales.SalesPerson ssp
 on hre.EmployeeID = ssp.SalesPersonID
 where ssp.SalesPersonID 
 in 
  (
   select soh.SalesPersonID
   from Sales.SalesOrderHeader soh
   where soh.TotalDue > 150000
  )

select pp.ProductID, pp.Name, (pp.ListPrice - pp.StandardCost) Markup
from Production.Product pp
where pp.ProductID
not in
 (
  select sod.ProductID
  from Sales.SalesOrderDetail sod
 )
order by Markup desc

--The CEO wants to reward the top sales person for the previous year.  
--Write a query that will find the top sales person based on sales last year (saleslastyear).  
--Given that sales person, get their first and last name, how many years they have been around, 
--how many customers they have, and how many sales they have.


Declare @Top int
set @Top = 
(
select TOP 1 ssp.SalesPersonID
from Sales.SalesPerson ssp
order by ssp.SalesLastYear desc
)
Declare @Customer int
set @Customer =
(
select COUNT(distinct soh.CustomerID)
from Sales.SalesOrderHeader soh
where soh.SalesPersonID = @Top
)
Declare @Sales int
set @Sales =
(
select COUNT(distinct soh.SalesOrderID)
from Sales.SalesOrderHeader soh
where soh.SalesPersonID = @Top
)
select pc.FirstName, pc.LastName, ssp.SalesLastYear, DATEDIFF(YEAR, hre.HireDate, GETDATE()) YearsWorked, @Customer Customers, @Sales TotalSales
from Person.Contact pc
 inner join HumanResources.Employee hre 
 on pc.ContactID = hre.ContactID
 inner join Sales.SalesPerson ssp
 on hre.EmployeeID = ssp.SalesPersonID
 where ssp.SalesPersonID 
 in 
  (
   select @Top
   from Sales.SalesOrderHeader soh
  )
order by ssp.SalesLastYear desc

Programming 2 */ Methods */

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strCharacters, strWords, strSentences, strSpaces As String

        strCharacters = TextBox1.Text
        Dim strCharactersCounted = getCharacters(strCharacters)
        TextBox2.Text = strCharactersCounted

        strWords = TextBox1.Text
        Dim strWordsCounted = getWordCount(strWords)
        TextBox3.Text = strWordsCounted

        strSentences = TextBox1.Text
        Dim strSentencesCounted = getSentencesCount(strSentences)
        TextBox4.Text = getSentencesCount(strSentences)

        strSpaces = TextBox1.Text
        Dim strSpacesCounted = getSpacesCount(strSpaces)
        TextBox5.Text = getSpacesCount(strSpaces)

    End Sub

    Private Function getCharacters(ByVal strCharacters As String)
        Return strCharacters.Length
    End Function

    Private Function getWordCount(ByVal InputString As String) As Integer
        Return Split(System.Text.RegularExpressions.Regex.Replace(InputString, "\s+", Space(1))).Length
    End Function

    Private Function getSentencesCount(ByVal strSentences As String)
        Dim maxCount As Integer = strSentences.Length
        Dim intSent As Integer = 0
        For Each singleChar As String In strSentences
            If singleChar = "." Or singleChar = "?" Or singleChar = "!" Then
                intSent += 1
            End If
        Next

        Return intSent
    End Function

    Private Function getSpacesCount(ByVal strSpaces As String)
        Dim maxCount As Integer = strSpaces.Length
        Dim intSent As Integer = 0
        For Each singleChar As String In strSpaces
            If singleChar = " " Then
                intSent += 1
            End If
        Next

        Return intSent
    End Function
End Class

Advanced Relational Database */ Outer Join Assignment w/ Variables */

1. Show all rooms for a given hotel. Show the customer name that is in the room if booked.

use YWGLH
Declare @HolidayInn int
set @HolidayInn = 1
select h.Name, r.Number, c.FirstName, c.LastName from
Rooms r
 left outer join Hotels h on h.HotelID = r.HotelID 
 left outer join Reservations re on r.RoomID = re.RoomID
 left outer join Customers c on re.CustomerID = c.CustomerID
 where h.HotelID = @HolidayInn

2. Show all rooms for a given hotel. Show the customer name that is in the room if booked.

Declare @DoubleBed varchar(50)
 Set @DoubleBed = 'DoubleBed'
Declare @Smoking varchar(50)
 Set @Smoking = 'Smoking'
select h.Name, h.Description, r.Number, r.RoomProperties, rs.StyleName from
Rooms r
 left outer join Hotels h on h.HotelID = r.HotelID
 left outer join RoomStyles rs on r.StyleID = rs.StylesID
 where rs.StyleName = @DoubleBed and r.RoomProperties = @Smoking

3. Which rooms are open for a given hotel on a given day. Order by price from cheapest to most expensive.

Declare @CheckIn datetime
 Set @CheckIn = '20110304 00:00:00'
Declare @CheckOut datetime
Set @CheckOut = '20110305 23:59:59'
select  h.Name, r.Number, re.CheckIn, re.CheckOut, ra.Cost from
Reservations re
 left outer join Rooms r on re.RoomID = r.RoomID
 left outer join Hotels h on r.HotelID = h.HotelID
 left outer join Rates ra on r.StyleID = ra.StylesID
 where re.CheckIn between @CheckIn and @CheckOut
 order by ra.Cost desc 

Programming 2 */ Arrays Assignment */

1. Create a string array that will store the days of the week. The array should fill when the form loads. Add a button (you can drag it on the form). When the button is clicked, loop through the array and pop up a message box for each day of the week.

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Num As Integer
        Dim DaysOfWeek(6) As String
        DaysOfWeek(0) = "Sunday"
        DaysOfWeek(1) = "Monday"
        DaysOfWeek(2) = "Tuesday"
        DaysOfWeek(3) = "Wednesday"
        DaysOfWeek(4) = "Thursday"
        DaysOfWeek(5) = "Friday"
        DaysOfWeek(6) = "Saturday"
        For Num = 0 To DaysOfWeek.Length - 1
            MessageBox.Show(DaysOfWeek(Num), "Days")
        Next
    End Sub

2. Create a form with textboxes for the following:
- first name
- last name
- email.
Add labels to describe each box.
Add a button for adding the user. The add user button click event will do the following:
- add the user information into a hashtable
- clear the text from the text boxes

We want to add the information into the hashtable using three keys:
FirstName, LastName, Email.

Add First Name, Last Name, and Email buttons to the bottom of the form. Each of these buttons is
going to fire off a message box that will show the information that was just added in.

Dim h As New Hashtable
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        h.Add("FirstName", TextBox1.Text)
        h.Add("LastName", TextBox2.Text)
        h.Add("Email", TextBox3.Text)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        MsgBox(h.Item("FirstName"))
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        MsgBox(h.Item("LastName"))
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        MsgBox(h.Item("Email"))
    End Sub

3. Create a form that will work as a printer queue. This form will have the following inputs:
Title of the document.
Pages to be printed.

When submitted the click event will do the following:
Add the title and pages to a hashtable.
Add the hashtable to a queue which is a class variable.
Clear the title and set the pages back to one.
Refresh the queue list.

The Clear Current button will: remove the next item in the queue and refresh the queue list box.

Dim queue As New Queue
    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim h2 As New Hashtable
        h2.Add("no", NumericUpDown1.Text)
        h2.Add("printing", TextBox4.Text)
        queue.Enqueue(h2)
        refreshbox()
    End Sub
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If queue.Count >= 1 Then
            queue.Dequeue()
            refreshbox()
        Else
            MessageBox.Show("There is nothing left to clear.", "No! YOU SUCK!!!")
        End If
    End Sub
    Private Sub refreshbox()
        ListBox1.Items.Clear()
        For Each ht As Hashtable In queue
            ListBox1.Items.Add(ht.Item("printing") & "(" & ht.Item("no") & ")")
        Next
    End Sub
End Class

Advanced Relational Database */ Subqueries */

What is a Subquery?

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.
Use of a Subquery in the Column List of a SELECT Statement
Suppose you would like to see the last OrderID and the OrderDate for the last order that was shipped to Paris. Along with that information, say you would also like to see the OrderDate for the last order shipped regardless of the ShipCity. In addition to this, you would also like to calculate the difference in days between the two different OrderDates. Here is my T-SQL SELECT statement to accomplish this:

select top 1 OrderId,convert(char(10),
       OrderDate,121) Last_Paris_Order,
      (select convert(char(10),max(OrderDate),121) from   
             Northwind.dbo.Orders) Last_OrderDate,
       datediff(dd,OrderDate,
      (select Max(OrderDate)from Northwind.dbo.Orders)) Day_Diff
from Northwind.dbo.Orders
where ShipCity = 'Paris'
order by OrderDate desc  

The above code contains two subqueries. The first subquery gets the OrderDate for the last order shipped regardless of ShipCity, and the second subquery calculates the number of days between the two different OrderDates. Here I used the first subquery to return a column value in the final result set. The second subquery was used as a parameter in a function call. This subquery passed the "max(OrderDate)" date to the DATEDIFF function.

Use of a Subquery in the WHERE clause

A subquery can be used to control the records returned from a SELECT by controlling which records pass the conditions of a WHERE clause. In this case the results of the subquery would be used on one side of a WHERE clause condition. Here is an example:
select distinct country from Northwind.dbo.Customers
where country not in (select distinct country from Northwind.dbo.Suppliers)
Here I have returned a list of countries where customers live, but there is no supplier located in that country. I suppose if you where trying to provide better delivery time to customers, then you might target these countries to look for additional suppliers.
Suppose a company would like to do some targeted marketing. This targeted marketing would contact customers in the country with the fewest number of orders. It is hoped that this targeted marketing will increase the overall sales in the targeted country. Here is an example that uses a subquery to return the customer contact information for the country with the fewest number of orders:

select Country,CompanyName, ContactName, ContactTitle, Phone
from Northwind.dbo.Customers 
where country =
   (select top 1 country 
         from Northwind.dbo.Customers C 
                      join
              Northwind.dbo.Orders O
              on C.CustomerId = O.CustomerID
    group by country
    order by count(*)) 

Here I have written a subquery that joins the Customer and Orders Tables to determine the total number of orders for each country. The subquery uses the "TOP 1" clause to return the country with the fewest number of orders. The country with the fewest number of orders is then used in the WHERE clause to determine which Customer Information will be displayed.

Use of a Subquery in the FROM clause

The FROM clause normally identifies the tables used in the T-SQL statement. You can think of each of the tables identified in the FROM clause as a set of records. Well, a subquery is just a set of records, and therefore can be used in the FROM clause just like a table. Here is an example where a subquery is used in the FROM clause of a SELECT statement:

select au_lname, au_fname, title from 
     (select au_lname, au_fname, au_id from pubs.dbo.authors
          where state = 'CA')  as a 
                     join
     pubs.dbo.titleauthor ta on a.au_id=ta.au_id
                     join
     pubs.dbo.titles t on ta.title_id = t.title_id

Here I have used a subquery to select only the author record information, if the author's record has a state column equal to "CA." I have named the set returned from this subquery with a table alias of "a." I can then use this alias elsewhere in the T-SQL statement to refer to the columns from the subquery by prefixing them with an "a", as I did in the "ON" clause of the "JOIN" criteria. Sometimes using a subquery in the FROM clause reduces the size of the set that needs to be joined. Reducing the number of records that have to be joined enhances the performance of joining rows, and therefore speeds up the overall execution of a query.

Here is an example where I used a subquery in the FROM clause of an UPDATE statement:

set nocount on 
create table x(i int identity, 
               a char(1))
insert into x values ('a')
insert into x values ('b')
insert into x values ('c')
insert into x values ('d')
select * from x

update x
set a = b.a
from (select max(a) as a from x) b 
where i > 2

select * from x
drop table x

Here I created a table named "x," that has four rows. Then I proceeded to update the rows where "i" was greater than 2 with the max value in column "a". I used a subquery in the FROM clause of the UPDATE statement to identity the max value of column "a."
Use of a Subquery in the HAVING clause
In the following example, I used a subquery to find the number of books a publisher has published where the publisher is not located in the state of California. To accomplish this I used a subquery in a HAVING clause. Here is my code:

select pub_name, count(*) bookcnt 
from pubs.dbo.titles t
         join
     pubs.dbo.publishers p
     on t.pub_id = p.pub_id
group by pub_name
having p.pub_name in     
  (select pub_name from pubs.dbo.publishers where state <> 'CA')

Here my subquery returns the pub_name values for all publishers that have a state value not equal to "CA." The HAVING condition then checks to see if the pub_name is in the set returned by my subquery.

Setting a subquery result to a variable

use AdventureWorks
delcare @ status int
set @status = (select top 1 status from Sales.SalesOrderHeader)

select @status

select * from Sales.SalesOrderHeader

This selects the top status from the table Sales.SalesOrderHeader.

Programming 2 */ Arrays */

Arrays are programming constructs that store data and allow us to access them by numeric index or subscript. Arrays helps us create shorter and simpler code in many situations. Arraysin Visual Basic .NET inherit from the Array class in the System namespace. All arrays in VB are zero based, meaning, the index of the first element is zero and they are numbered sequentially. You must specify the number of array elements by indicating the upper bound of the array. The upper bound is the numder that specifies the index of the last element of the array. Arrays are declared using Dim, ReDim, Static, Private, Public and Protected keywords. An array can have one dimension (liinear arrays) or more than one (multidimensionalarrays). The dimensionality of an array refers to the number of subscripts used to identify an individual element. In Visual Basic we can specify up to 32 dimensions. Arrays do not have fixed size in Visual Basic.

The following code demonstrates arrays.

Imports System.Console
Module Module1
Sub Main()
Dim sport(5) As String
'declaring an array
sport(0) = "Soccer"
sport(1) = "Cricket"
sport(2) = "Rugby"
sport(3) = "Aussie Rules"
sport(4) = "BasketBall"
sport(5) = "Hockey"
'storing values in the array
WriteLine("Name of the Sport in the third location" & " " & sport(2))
'displaying value from array
End Sub
End Module

You can also declare an array without specifying the number of elements on one line, you must provide values for each element when initializing the array. The following lines demonstrate that:

Dim Test() as Integer
'declaring a Test array
Test=New Integer(){1,3,5,7,9,}

Reinitializing Arrays

We can change the size of an array after creating them. The statement assigns a completely new array object to the specified array variable. You use ReDim statement to change the number of elements in an array. The following lines of code demonstrate that. This code reinitializes the Test array declared above.

Dim Test(10) as Integer
ReDim Test(25) as Integer
'Reinitializing the array

When using the Redim statement all the data contained in the array is lost. If you want to preserve existing data when reinitializing an array then you should use the Preserve keyword which looks like this:

Dim Test() as Integer={1,3,5}
'declares an array an initializes it with three members
ReDim Preserve Test(25)
'resizes the array and retains the the data in elements 0 to 2

HashTable stores a Key Value pair type collection of data . We can retrive items from hashTable to provide the key . Both key and value are Objects.

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, 
 ByVal e As System.EventArgs) Handles Button1.Click
        Dim weeks As New Hashtable
        Dim day As DictionaryEntry
        weeks.Add("1", "Sun")
        weeks.Add("2", "Mon")
        weeks.Add("3", "Tue")
        weeks.Add("4", "Wed")
        weeks.Add("5", "Thu")
        weeks.Add("6", "Fri")
        weeks.Add("7", "Sat")
        'Display a single Item
        MsgBox(weeks.Item("5"))
        'Search an Item
        If weeks.ContainsValue("Tue") Then
            MsgBox("Find")
        Else
            MsgBox("Not find")
        End If
        'remove an Item
        weeks.Remove("3")
        'Display all key value pairs
        For Each day In weeks
            MsgBox(day.Key  "  --  "  day.Value)
        Next
    End Sub
End Class

The Queue is another adtastructure from VB.NET Collections. Queue works like First In First Out method and the item added first in the Queue is first get out from Queue. We can Enqueue (add) items in Queue and we can Dequeue (remove from Queue ) or we can Peek (that is get the reference of first item added in Queue ) the item from Queue.

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object,_
 ByVal e As System.EventArgs) Handles Button1.Click
        Dim queueList As New Queue
        queueList.Enqueue("Sun")
        queueList.Enqueue("Mon")
        queueList.Enqueue("Tue")
        queueList.Enqueue("Wed")
        queueList.Enqueue("Thu")
        queueList.Enqueue("fri")
        queueList.Enqueue("Sat")
        MsgBox(queueList.Dequeue())
        MsgBox(queueList.Peek())
        If queueList.Contains("Sun") Then
            MsgBox("Contains Sun ")
        Else
            MsgBox("Not Contains Sun ")
        End If
    End Sub
End Class

Programming 2 */ A few programs with Loops */

'Loops

 Public Class Form1  
   '1. Put a textbox and a button on the form. The user must enter a number and press the button.   
   'When the button is pressed, it will add that many buttons to the form.  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     Dim num As Integer  
     num = CInt(TextBox1.Text)  
     For num = 1 To num  
       ListBox1.Items.Add(num)  
     Next  
   End Sub  
   Private Sub TextBox1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TextBox1.MouseClick  
     ListBox1.Items.Clear()  
   End Sub  
   '2. If you were to put away a certain amount of money every month, how many years would it take   
   'you to save up $10,000. Use a textbox to gather the amount that is to be put away every month.   
   'Use a label to display the number of years it would take.   
   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click  
     Dim pay, count, total, goal As Integer  
     goal = 10000  
     count = 1  
     pay = CInt(TextBox2.Text)  
     Do While total < goal  
       total += pay  
       count += 1  
     Loop  
     Label1.Text = FormatNumber((count / 12), 1) & " years left until you get to $10,000.00"  
   End Sub  
   '3. Write a program that will create a times table.  
   '1 2 3 4 5  6  7  8  9 10  
   '2 4 6 8 10 12  14 16 18 20  
   '..…  
   'You could use labels or buttons to hold the numbers. Generate these dynamically and add them   
   'to the Me.Controls collection.  
   'You will have to use two loops, one inside of the other to get this to work.  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
     For x = 1 To 10  
       For y = 1 To 10  
         Dim button As New Button  
         button.Location = New Point(35 * x, 25 * y)  
         button.Width = 35  
         Me.Controls.Add(button)  
         button.Text = (x * y)  
       Next  
     Next  
   End Sub  
 End Class  

Advanced Relation Database */ Outer Joins */

SQL OUTER JOIN has 2 sub-types called: LEFT OUTER JOIN and RIGHT OUTER JOIN.
The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.If we slightly modify our last SQL statement to:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers LEFT JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

and the Sales table still has the following rows:

CustomerID	Date	SaleAmount
2	5/6/2004	$100.22
1	5/6/2004	$99.95

The result will be the following:

FirstName	LastName	SalesPerCustomers
John	Smith	$99.95
Steven	Goldfish	$100.22
Paula	Brown	NULL
James	Smith	NULL

As you can see we have selected everything from the Customers (first table). For all rows from Customers, which don’t have a match in the Sales (second table), the SalesPerCustomer column has amount NULL (NULL means a column contains nothing).
The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).

Advanced Relation Database */ Movie Quotes */

--The script for the Movie quotes

USE [DoubleTechnicalQuotes]  
 GO  
 /****** Object: Table [dbo].[xQuoteRef] Script Date: 02/23/2011 14:14:00 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 CREATE TABLE [dbo].[xQuoteRef](  
 [xID] [int] IDENTITY(1,1) NOT NULL,  
 [mID] [int] NULL,  
 [aID] [int] NULL,  
 [qID] [int] NULL,  
 PRIMARY KEY CLUSTERED   
 (  
 [xID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  
 GO  
 /****** Object: Table [dbo].[randomizer] Script Date: 02/23/2011 14:14:00 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 CREATE TABLE [dbo].[randomizer](  
 [quoteid] [int] NULL,  
 [randomid] [int] NULL  
 ) ON [PRIMARY]  
 GO  
 /****** Object: Table [dbo].[MovieQuote] Script Date: 02/23/2011 14:14:00 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 SET ANSI_PADDING ON  
 GO  
 CREATE TABLE [dbo].[MovieQuote](  
 [qID] [int] IDENTITY(1,1) NOT NULL,  
 [aID] [int] NULL,  
 [MovieQuote] [varchar](200) NULL,  
 CONSTRAINT [PK__MovieQuo__C276CFE907020F21] PRIMARY KEY CLUSTERED   
 (  
 [qID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  
 GO  
 SET ANSI_PADDING OFF  
 GO  
 /****** Object: Table [dbo].[Movie] Script Date: 02/23/2011 14:14:00 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 SET ANSI_PADDING ON  
 GO  
 CREATE TABLE [dbo].[Movie](  
 [mID] [int] IDENTITY(1,1) NOT NULL,  
 [MovieName] [varchar](50) NULL,  
 PRIMARY KEY CLUSTERED   
 (  
 [mID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  
 GO  
 SET ANSI_PADDING OFF  
 GO  
 /****** Object: Table [dbo].[ActorActress] Script Date: 02/23/2011 14:14:00 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 SET ANSI_PADDING ON  
 GO  
 CREATE TABLE [dbo].[ActorActress](  
 [aID] [int] IDENTITY(1,1) NOT NULL,  
 [ActorActressName] [varchar](50) NULL,  
 PRIMARY KEY CLUSTERED   
 (  
 [aID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  
 GO  
 SET ANSI_PADDING OFF  
 GO  

--Select statements for the Movie quotes.

select m.MovieName Movie, a.ActorActressName Actor_or_Actress, q.MovieQuote Quote  
 from Movie m  
 inner join xQuoteRef x  
      on m.mID = x.mID  
 inner join ActorActress a  
      on a.aID = x.aID  
 inner join MovieQuote q  
      on q.aID = x.qID  
 where q.MovieQuote like '%Hey%'  
 select m.MovieName Movie, a.ActorActressName Actor_or_Actress, q.MovieQuote Quote  
 from Movie m  
 inner join xQuoteRef x  
      on m.mID = x.mID  
 inner join ActorActress a  
      on a.aID = x.aID  
 inner join MovieQuote q  
      on q.aID = x.qID  
 where a.ActorActressName = 'Dr. Rumack'  

Programming 2 */ Kinds of Loops */

A Do loop repeatedly executes a block of statements either as long as or until a certain condition is true. The condition can be checked either at the top of the loop or at the bottom. There are two different ways that you can do the Do loop. It first can be done by putting While after the Do.

Dim num As Integer = 1  
     Do While num <= 7  
       MessageBox.Show(num)  
       num += 1  
     Loop  

The second Do can be done by putting the While after the Loop.

Dim num As Integer = 1  
     Do  
       MessageBox.Show(num)  
       num += 1  
     Loop While num <= 7  

A For...Next loop repeats a block of statements a fixed number of times. The counter variable assumes an initial value and increases it by one after each pass through the loop until it reaches the terminating value. Alternative increment values can be specified with the Step keyword.

'Displays in a message box the first five numbers and their square roots.  
 'I also used the step keyword to skip step 2. So it will skip 2 4. 
     For i As Integer = 1 To 5 Step 2  
       MessageBox.Show(i & " " & i ^ 2)  
     Next  

A For Each loop repeats a group of statements for each element in an array.

Dim i As Integer = 500  
     For Each number As Integer In New Long() {50, 15, 32}  
       MessageBox.Show(number & " ")  
     Next  

Programming 2 */ Form Object Manipulation */

1. Create a form with a single button (use the gui for this one). When the button is hovered over, move the button to another part of the form. The button should move back and forth between 2 points. If you want to be special, move the button to a random part of the form. You will have to use a random number generator for the x and y coordinates making sure the random x and y are not larger than the form height and width. If you don't feel like being special, just...

Public Class Form1
   Private Sub Button1_MouseEnter(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.MouseEnter  
     Dim rnd1 As New Random  
     Dim x As Integer = rnd1.Next(150)  
     Dim y As Integer = rnd1.Next(300)  
     Button.Location = New Point(x, y)  
   End Sub  
   Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click  
     MessageBox.Show("You Win")  
   End Sub  

2.
a. Button 2: This button will make the textbox enabled/disabled.
b. Button 3: Turn a background color on the textbox on and off. (Toggle between white and
another color)
c. Button 4: Put text inside of the text box and take it away
d. Button 5: Change the border style of the textbox between none and fixed3d
Create a form with 4 buttons and one text box (use the gui). The button wording should
describe what it does.

Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click  
     If TextBox1.Enabled = True Then  
       TextBox1.Enabled = False  
     Else : TextBox1.Enabled = True  
     End If  
   End Sub  
   Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click  
     If TextBox1.BackColor = Color.White Then  
       TextBox1.BackColor = Color.Black  
     Else : TextBox1.BackColor = Color.White  
     End If  
   End Sub  
   Private Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click  
     If TextBox1.Text = "" Then  
       TextBox1.Text = "HI!!!"  
     Else : TextBox1.Text = ""  
     End If  
   End Sub  
   Private Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click  
     If TextBox1.BorderStyle = BorderStyle.None Then  
       TextBox1.BorderStyle = BorderStyle.Fixed3D  
     Else : TextBox1.BorderStyle = BorderStyle.None  
     End If  
   End Sub

3. Create a form with a button (using the gui). When this button is clicked, it will create 3 labels and 3 textboxes associated with those labels. When the textboxes are hovered over, change their background color. When it is not being hovered over, change the background color back to white.

Dim l1, l2, l3 As New Label  
   Dim WithEvents t1, t2, t3 As New TextBox  
   Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click  
     l1.Location = New Point(50, 50)  
     l1.Text = "Label1"  
     l2.Location = New Point(50, 100)  
     l2.Text = "Label2"  
     l3.Location = New Point(50, 150)  
     l3.Text = "Label3"  
     Me.Controls.Add(l1)  
     Me.Controls.Add(l2)  
     Me.Controls.Add(l3)  
     t1.Location = New Point(150, 50)  
     t2.Location = New Point(150, 100)  
     t3.Location = New Point(150, 150)  
     Me.Controls.Add(t1)  
     Me.Controls.Add(t2)  
     Me.Controls.Add(t3)  
   End Sub  
   Private Sub t1_MouseEnter(ByVal sender As Object, ByVal e As System.EventArgs) Handles t1.MouseEnter  
     t1.BackColor = Color.Black  
   End Sub  
   Private Sub t2_MouseEnter(ByVal sender As Object, ByVal e As System.EventArgs) Handles t2.MouseEnter  
     t2.BackColor = Color.Black  
   End Sub  
   Private Sub t3_MouseEnter(ByVal sender As Object, ByVal e As System.EventArgs) Handles t3.MouseEnter  
     t3.BackColor = Color.Black  
   End Sub  
   Private Sub t1_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles t1.MouseLeave  
     t1.BackColor = Color.White  
   End Sub  
   Private Sub t2_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles t2.MouseLeave  
     t2.BackColor = Color.White  
   End Sub  
   Private Sub t3_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles t3.MouseLeave  
     t3.BackColor = Color.White  
   End Sub

4.
Create a form with 3 buttons and a listbox.
Load 3 items into the listbox on load so that its not empty
Button 1: Select Item 1
Button 2: Select Item 2
Button 3: Select Item 3

Dim WithEvents b1, b2, b3 As New Button  
   Dim lb1 As New ListBox  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
     b1.Location = New Point(300, 140)  
     b2.Location = New Point(300, 170)  
     b3.Location = New Point(300, 200)  
     lb1.Location = New Point(300, 230)  
     b1.Text = "Button 7"  
     b2.Text = "Button 8"  
     b3.Text = "Button 9"  
     Me.Controls.Add(b1)  
     Me.Controls.Add(b2)  
     Me.Controls.Add(b3)  
     Me.Controls.Add(lb1)  
     lb1.Items.Add("You")  
     lb1.Items.Add("are")  
     lb1.Items.Add("annoying!!!")  
   End Sub  
   Private Sub b1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles b1.Click  
     lb1.SelectedIndex = 0  
   End Sub  
   Private Sub b2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles b2.Click  
     lb1.SelectedIndex = 1  
   End Sub  
   Private Sub b3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles b3.Click  
     lb1.SelectedIndex = 2  
   End Sub  
   Private Sub clear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clear.Click  
     TextBox1.Text = ""  
     TextBox1.BackColor = Color.White  
     TextBox1.BorderStyle = BorderStyle.Fixed3D  
     TextBox1.Enabled = True  
     t1.Text = ""  
     t2.Text = ""  
     t3.Text = ""  
     Button.Location = New Point(12, 11)  
   End Sub  
   Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click  
     Application.Exit()  
   End Sub  
 End Class  

Programming 2 */ Hand Coding Controls */

Public Class HowToHandCodeControls  
   Private Sub HowToHandCodeControls_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
     Dim B As New Button  
     B.Text = "Berries"  
     B.Location = New Point(1, 1)  
     B.Name = "btnBerries"  
     B.Size = New System.Drawing.Size(50, 50)  
     Me.Controls.Add(B)  
     Dim T As New TextBox  
     T.Text = "Terry"  
     T.Location = New Point(100, 1)  
     T.ReadOnly = False  
     Me.Controls.Add(T)  
     Dim L As New Label  
     L.Text = "Larry"  
     L.Location = New Point(1, 100)  
     T.Enabled = False  
     Me.Controls.Add(L)  
     Dim LB As New ListBox  
     LB.Text = "Larry's Berries"  
     LB.Location = New Point(100, 100)  
     Me.Controls.Add(LB)  
   End Sub  
 End Class  

Programming 2 /* Conditionals */

Public Class Conditionals  
   Private Sub Conditionals_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
     '1)  
     'Mr. Spacely has been rethinking the company's shipping schemes. As an employee for Spacely's   
     'Sprockets, Spacely has asked you to write a quick program that will be used to determine shipping   
     'costs.  
     'A total purchase of items totaling under $50, we charge $5 shipping.  
     'A total purchase of items totaling $50 and over, there is no shipping charge.  
     Dim TotalCost, Cost, Shipping As Decimal  
     Cost = 49  
     If Cost < 50 Then  
       Shipping = 5.0  
     ElseIf Cost >= 50 Then  
       Shipping = 0.0  
     End If  
     TotalCost = Cost + Shipping  
     MessageBox.Show(TotalCost)  
     '2)  
     'You are looking for something to control your heat in your apartment and you discover there is   
     'NOT an app for that. It's about time that someone created one. You decide that you are the one   
     'to do it. Here's what you want to do.  
     '- You want to turn the heat on when the temp has dropped below 72  
     '- You also want to turn the AC on when the temp gets above 76  
     'Your app should display in a message box if the heat is on, the AC is on, or if the system is idle.   
     'Plug in different temps for the room in a variable to see what the thermostat will do with it.  
     Dim Temp As Integer  
     Temp = 76  
     If Temp < 72 Then  
       MessageBox.Show("The Heat Is On")  
     ElseIf Temp > 76 Then  
       MessageBox.Show("The AC Is On")  
     Else  
       MessageBox.Show("The System Is Idle")  
     End If  
     '3)  
     'a. 0-2 yrs - XS  
     'b. 3-4 yrs - S  
     'c. 5-8 yrs - M  
     'd. 9-12 yrs - L  
     'e. 13+ yrs - XL  
     'You are working on a clothing website where people can buy kids socks. It's really hard for the   
     'customers to know what size they should buy for what age. It would be a good idea for the   
     'customer to have a tool to input their child's age and have the website suggest a size for them.   
     'Write a tool where you can set the age as a variable and have it suggest on of the sizes below:  
     Dim Shirts As Integer  
     Select Case Shirts  
       Case 0 - 2  
         MessageBox.Show("XS")  
       Case 3 - 4  
         MessageBox.Show("S")  
       Case 5 - 8  
         MessageBox.Show("M")  
       Case 9 - 12  
         MessageBox.Show("L")  
       Case Is >= 13  
         MessageBox.Show("XL")  
     End Select  

Challenge me in Mini Putt!

Hey guys I scored a 37 in Mini Putt. Go down on the bottom of my page to challenge me :)

Advanced Relational Database /* Inner Join between two tables */

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.Tables in a database are often related to each other with keys.A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

--McIntyre Inner Join Pop Tarts
use master;  
 create table #PopTartsStore  
      (  
      StoreId int primary key identity (1,1)  
      , Flavor varchar (20)  
      , Price dec(6,2)  
      )  
 insert into #PopTartsStore  
 values  
 ('Apple Cinnamon', 9999.99)  
 insert into #PopTartsStore  
 values  
 ('Brown Sugar', 9999.99)  
 insert into #PopTartsStore  
 values  
 ('Strawberry', 9999.99)  
 create table #PopTartsDistributor  
      (  
      DID int primary key identity (1,1)  
      , StoreId int  
      , Distributors varchar(50)  
      )  
 insert into #PopTartsDistributor  
 values  
 (1, 'Walgreens')  
 insert into #PopTartsDistributor  
 values  
 (2, 'Walmart')  
 insert into #PopTartsDistributor  
 values  
 (3, 'Wally''s')  
 --st = #PopTartsStore  
 --di = #PopTartsDistributor  
 SELECT st.StoreId, st.Flavor, st.Price, di.Distributors  
 From #PopTartsStore as st  
      Inner Join #PopTartsDistributor as di  
      on st.StoreId = di.StoreId  
select * from #PopTartsStore
select * from #PopTartsDistributor

Programming 2 /* Case Statements */

A select-case statement is a great way to perform a logical test that might have multiple directions if found true or false. You could write a bunch of if-then-else statements but that could be very cumbersome. Here is how you utilize the Select-Case statement.

'McIntyre Case Statements
Public Class Form1  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
     Dim number As Integer = 8  
     Select Case number  
       Case 1 To 5  
         Debug.WriteLine("Between 1 and 5, inclusive")  
         ' The following is the only Case clause that evaluates to True.  
       Case 6, 7, 8  
         Debug.WriteLine("Between 6 and 8, inclusive")  
       Case 9 To 10  
         Debug.WriteLine("Equal to 9 or 10")  
       Case Else  
         Debug.WriteLine("Not between 1 and 10, inclusive")  
     End Select  
   End Sub  
 End Class  

Programming 2 /* If Statements */

An "If" statement in Visual Basic is a comparison statement that evaluates to true or false. If the statement evaluates to true, then the inner statements within the "If" block are executed. If the statement evaluates to false, then the compiler skips over the comparison statement and executes the following lines of code. "If" statements are common in all programming languages, and they are an essential part of creating web and desktop applications.

'McIntyre If Statements
Public Class Form1  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
     Dim number, digits As Integer  
     Dim myString As String  
     number = 53  
     If number < 10 Then  
       digits = 1  
     End If  
     If digits = 1 Then  
       myString = "One"  
     Else : myString = "More than one"  
     End If  
     If number < 100 Then  
       digits = 2  
     ElseIf number > 100 Then  
       digits = 3  
     Else  
       MessageBox.Show("No")  
     End If  
   End Sub  
 End Class  

How to draw a Cartoon Banana.

Steps -
1. Draw a right-angled triangle, with a slightly slanted rectangle underneath, then draw another right-angled triangle but upside down.
2. Draw a border around the shapes, this will make the outline of the banana. Make sure the line does not touch the shapes. At each end, draw the edge dark for the ends of the banana.
3. Draw two large eyes, have them touching but not overlapping. Draw two pupils inside. For different looks, try making one eye a different size or shape to the other.
4. No draw the mouth as shown in the video, but be careful how far you curve the lip as this can dramatically change the characters mood.
5. Erase all underlines and make the outlines bold.

Now add whatever you like to the banana. Arms, Legs, Hands, Clothes, whatever you want.


You can find the YouTube video here.

Programming 2 /* Practice #1 - Acts and Facts */

'McIntyre Practice #1 - Acts and Facts 
Public Class ActsAndFacts   
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click   
    '1. Morgan loves collecting small stuffed animals. She has 6 cows and 7 sheep. How many   
        animals does she have in her collection?     
    'Variables declared   
    Dim Cows, Sheep, Animals As Integer   
    'Variables set   
    Cows = 6   
    Sheep = 7   
    'Calculation of total animals   
    Animals = Cows + Sheep   
    'Output in a message box   
    MessageBox.Show("There are " & Animals & " Animals.")   
   End Sub   
   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click   
    '2. Diane bought 7 mega burgers. Each mega burger cost $4. How many dollars did she spend on the Mega Burgers?   
    'Variables declared   
    Dim MegaBurgers As Integer   
    Dim MBCost, TotalCost As Decimal   
    'Variables set   
    MegaBurgers = 7   
    MBCost = 4   
    'Calculation of Total Cost of the Mega Burgers   
    TotalCost = MegaBurgers * MBCost   
    'Output in a message box   
    MessageBox.Show("The Total Cost of the Megaburgers is " & FormatCurrency(TotalCost, 2) & ".")   
   End Sub   
   Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click   
    '3. Ole has 15 apples and 12 oranges. How many pieces of fruit does he have?   
    'Variables declared   
    Dim Apples, Oranges, TotalFruit As Integer   
    'Variables set   
    Apples = 15   
    Oranges = 12   
    'Calculation of Total Fruit   
    TotalFruit = Apples + Oranges   
    'Output in a message box   
    MessageBox.Show("There are a total of " & TotalFruit & " fruit.")   
   End Sub   
   Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click   
    '4. There are 33 horses in a field. 15 horses go into the barn. Then 7 of them come back out. How many horses are standing in the field?   
    'Variables declared   
    Dim FieldHorses, BarnHorses, HorsesThatCameOut, HorsesStanding As Integer   
    'Variables set   
    FieldHorses = 33   
    BarnHorses = 15   
    HorsesThatCameOut = 7   
    'Calculation of Horses left in the field   
    HorsesStanding = (FieldHorses - BarnHorses) + HorsesThatCameOut   
    'Output in a message box   
    MessageBox.Show("There are " & HorsesStanding & " standing in the field.")   
   End Sub   
   Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click   
    '5. Inglebert has 15 apples and 3 times as many oranges. How many pieces of fruit does she have?   
    'Variables declared   
    Dim Apples, Oranges, TotalFruit As Integer   
    'Variables set   
    Apples = 15   
    Oranges = Apples * 3   
    'Calculation of Total Fruit   
    TotalFruit = Apples + Oranges   
    'Output in a message box   
    MessageBox.Show("There are a total of " & TotalFruit & " fruit.")   
   End Sub   
   Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click   
    '6.  English Geography Mathematics Science    
    'Andrew 12  19   18   7    
    'Brian  22  15   7   22    
    'The table shows quiz marks out of 25 in each subject.    
    '•How many marks did Brian totally obtain in Mathematics and Science?   
    '•How many more marks does Andrew need for a perfect score in Mathematics?   
    '•What is Andrew's percentage for all of the quizzes together?   
    'Variables declared   
    Dim AEng, BEng, AGeo, BGeo, AMath, BMath, ASci, BSci, PerfectScore, BTPoints, NeededForPerfectScore, APerc As Integer   
    'Variables set   
    AEng = 12   
    BEng = 22   
    AGeo = 19   
    BGeo = 15   
    AMath = 18   
    BMath = 7   
    ASci = 7   
    BSci = 22   
    PerfectScore = 25   
    'Calculation of Brian's total points in Math and Science   
    BTPoints = BMath + BSci   
    'Needed marks for a perfect score for Andrew in Math   
    NeededForPerfectScore = PerfectScore - AMath   
    'Andrew's percentage for all quizzes   
    APerc = (AEng + AGeo + AMath + ASci) / 4   
    'Outputting the answers in a message box   
    MessageBox.Show("Total marks for Brian in Math and Science is " & BTPoints & ".")   
    MessageBox.Show("Andrew needs " & NeededForPerfectScore & " points for a perfect score in Math.")   
    MessageBox.Show("Andrew's percentage for all of the quizzes together is " & APerc & ".")   
   End Sub   
  End Class   

Programming 2 /* how to join a string to another string */

String Concatenation
Two strings can be combined to form a new string consisting of the strings joined together. It is represented by an ampersand (&).

Dim Google, BackInTime, HotTubTimeMachine as String
Google = "Lougle"  
BackInTime = " is a better name."  
HotTubTimeMachine = Google & BackInTime  

Programming 2 /* how to convert an integer into a decimal and a decimal into an integer */

Converting an integer into a decimal.

Dim intYahoo as Integer
intYahoo = CDec(1)

Converting a decimal into an integer is done the same way.

Dim decYahoo as Decimal
decYahoo = CInt(1)

Advanced Relational Database /* Assignment 1 Frog Database */ 1/24/11

use master;  
 Go  
 Create database Frogs  
 use Frogs  
 create table NDFrogs  
      (  
      ID Int Primary Key identity (1,1)  
      , Scientific_Name varchar(50)  
      , IUCN_Red_List_Status varchar(30)  
      , Vernacular_Name varchar (30)  
      , Family varchar (30)  
      )  
 insert into NDFrogs  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)  
 values  
 ('Bufo americanus', 'Least Concern (LC)', 'American Toad', 'Bufonidae')  
 insert into NDFrogs  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)  
 values  
 ('Bufo cognatus', 'Least Concern (LC)', 'Great Plains Toad', 'Bufonidae')  
 insert into NDFrogs  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)  
 values  
 ('Bufo hemiophrys', 'Least Concern (LC)', 'Canadian Toad', 'Bufonidae')  
 insert into NDFrogs  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)  
 values  
 ('Bufo woodhousii', 'Least Concern (LC)', 'Woodhouse''s Toad', 'Bufonidae')  
 insert into NDFrogs  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)  
 values  
 ('Hyla versicolor', 'Least Concern (LC)', 'Eastern Gray Treefrom', 'Hylidae')  
 insert into NDFrogs  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)  
 values  
 ('Pseudacris maculata', 'Least Concern (LC)', 'Boreal Chorus Frog', 'Hylidae')  
 select * from NDFrogs  

Creating A Database in Microsoft SQL Server 1/24/11

This will create a blank database named Blogging.

Use Master;  
 Go
 Create Database Blogging;  

After creating the database we can use it by using the 'use' command.

use Blogging;  

Converting a number variable into a string 1/24/11

When converting a number variable such as an integer we use the 'ToString' command. You can do it this way and also a different way by using the 'CStr' command. Here I show both ways.

This is the 'ToString' command.

Dim intYahoo As Integer = 8
Dim strYahoo As String = intYahoo.ToString

This is the 'CStr' command.

Dim intYahoo As Integer = 8
Dim strYahoo As String = CStr(intYahoo)

Both of these do the same thing when converting.

Variables in Visual Basic 1/24/11

Visual Basic, just like most programming languages, uses variables for storing values. A variable has a name (the word that you use to refer to the value that the variable contains). A variable also has a data type (which determines the kind of data that the variable can store). A variable can represent an array if it has to store an indexed set of closely related data items.

'Declaring a String  
Dim strGoogle As String  
'Declaring a Boolean  
Dim blnBing As Boolean  
'Declaring an Integer  
Dim intYahoo As Integer  
'Declaring a Decimal  
Dim decAsk As Decimal  
'Assigning a value to a variable  
strGoogle = "Googling"  
blnBing = False  
intYahoo = 1  
decAsk = 0.25  
'Outputting all of the values to the variables in a message box  
MessageBox.Show(strGoogle & " " & blnBing & " " & intYahoo + decAsk)  

Advanced Relational Database /* Testing the creation of a table variable, inserting 3 records into it and changing one of the values of a column in only 1 row, and selecting all records */ 1/21/11

use AntiGoogle

declare @ProblemsWithPalmersQuestions table
(
    ID int primary key identity(1,1),
    Problems varchar(50) null,
    Googling varchar(20) null
)

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('Too Hard', 'Not Good')

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('He doesn''t have his own problems figured out', 'False Information')

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('Algebra problems are no fun', 'Too Much Information')

update @ProblemsWithPalmersQuestions
set Problems = 'Too Easy'
where Problems = 'Too Hard'

select * from @ProblemsWithPalmersQuestions

Advanced Relational Database /* Testing the creation of a table variable, inserting 3 records into it and deleting one record, and selecting all records */ 1/21/11

use AntiGoogle

declare @ProblemsWithPalmersQuestions table
(
    ID int primary key identity(1,1),
    Problems varchar(50) null,
    Googling varchar(20) null
)

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('Too Hard', 'Not Good')

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('He doesn''t have his own problems figured out', 'False Information')

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('Algebra problems are no fun', 'Too Much Information')

delete from @ProblemsWithPalmersQuestions
where Problems = 'Too Hard'

select * from @ProblemsWithPalmersQuestions

Advanced Relational Database /* Testing the creation of a table variable, inserting 3 records into it, and selecting all records */ 1/21/11

use AntiGoogle

declare @ProblemsWithPalmersQuestions table
(
    ID int primary key identity(1,1),
    Problems varchar(50) null,
    Googling varchar(20) null
)

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('Too Hard', 'Not Good')

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('He doesn''t have his own problems figured out', 'False Information')

insert into @ProblemsWithPalmersQuestions
(Problems, Googling)
values
('Algebra problems are no fun', 'Too Much Information')

select * from @ProblemsWithPalmersQuestions

Advanced Relational Database /* Temp Tables and Table Variables */ 1/19/11

use DB
Create Table Frogs (
  ID int primary key identity(1,1)
 ,Legs int not null
 ,Color varchar(8982)
  )

* look up temp tables and table variable
--syntax
--when to use

 4 ways to store data: local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), and table variables (@table_name). 

Local Temporary Tables

Create Table #pimp
(
  id int
  ,daddy varchar(50)
)

A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it.

Table Variables
Declare @people table
(
  id int
  ,name varchar(32)
)

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.


I found all of my information here.

Programming 2 /* Repeatable Processing */ 1/19/11

--Calculating a tip.
$23.12 @ 10%
T = 23.12
Tip = .1
TotalTip = T * Tip

--Calculating Final Grade.
30% Blog, 40% Assignments, 10% Peer Evals, 20% Final
86/100 blog, 345/450 assignments, 43/50 peers, 55/80 final
BlogTP = 100
BlogS = 86
BlogP = BlogS/BlogTP
BlogW = .3
AssignTP = 450
AssignS = 345
AssignP = AssignS/AssignTP
AssignW = .4
PeerTP = 50
PeerS = 43
PeerP = PeerS/PeerTP
PeerW = .1
FinalTP = 80
FinalS = 55
FinalP = FinalS/FinalTP
FinalW = .2
Grade = (BlogP * BlogW) + (AssignP * AssignW) +...

--It's time to send out all the Christmas cards again, but you don't feel like writing all the envelopes by hand.
--How could you use a formula to create the addresses for you?
FL = First & " " & Last
SL = Address
TL = City & " " & State & " " Zip

M&M

Keep bloggin while I mind boggle in my zone like i’m in the twilight, dog.
Get off my bone, this is my mic doggonit, and I like hoggin’ it...

Code

Dim strNotGooglingAnything as String
txtNo.text = strNotGooglingAnything
strNotGooglingAnything = "Book Power :)"