Methods 1 - Text Analyzer

 Public Class Form1
  
   Dim nwordcount As Integer
  
   Dim ncharcount As Integer
  
   Dim nspacecount As Integer
  
   Dim nsentencecount As Integer
  
   Public Sub btnAnalyze_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnalyze.Click
  
     Dim strMaterial As String = txtMaterial.Text
  
     ncharcount = GetCharacterCount(strMaterial)
  
     nsentencecount = GetSentenceCount(strMaterial)
  
     nspacecount = GetSpaceCount(strMaterial)
  
     nwordcount = GetWordCount(strMaterial)
  
     RefreshTextboxes()
  
   End Sub
  
   Private Function GetCharacterCount(ByVal str As String) As Integer
  
     Return str.Length
  
   End Function
  
   Private Function GetSentenceCount(ByVal str As String) As Integer
  
     Dim counter As Integer = 0
  
     For Each ending As String In str
  
       If ending.Contains(".") Or ending.Contains("?") Or ending.Contains("!") Then
  
         counter += 1
  
       End If
  
     Next
  
     Return counter
  
   End Function
  
   Private Function GetSpaceCount(ByRef str As String) As Integer
  
     Dim counter As Integer = 0
  
     For Each Space As String In str
  
       If Space.Contains(" ") Then
  
         counter += 1
  
       End If
  
     Next
  
     Return counter
  
   End Function
  
   Private Function GetWordCount(ByRef str As String) As Integer
  
     Dim counter As Integer = System.Text.RegularExpressions.Regex.Matches(str, "\S+").Count
  
     Return counter
  
   End Function
  
   Public Sub RefreshTextboxes()
  
     txtCharacters.Text = ncharcount
  
     txtSentences.Text = nsentencecount
  
     txtSpaces.Text = nspacecount
  
     txtWords.Text = nwordcount
  
   End Sub
  
 End Class
  

Methods 2 - Yahtzee

 Public Class Form1
  
   'Dim variables used in different Methods
  
   Dim lblDice1, lblDice2, lblDice3, lblDice4, lblDice5 As New Label
  
   Dim WithEvents butRoll As New Button
  
   Dim nYatzee, nFourOfAKind, nThreeOfAKind As New Integer
  
   Dim lblYatzee, lblFourOfAKind, lblThreeOfAKind As New TextBox
  
   Dim rnd As New Random
  
   Private Sub AddDice(ByRef lbl As Label, ByVal x As Integer, ByVal y As Integer)
  
     'Method for adding the differe labels to display the numbers rolled
  
     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
  
   Private Sub AddOutput(ByRef lbl As TextBox, ByVal outcome As String, ByVal x As Integer, ByVal y As Integer)
  
     'Method of adding the output from the roll into the label
  
     lbl.Text = outcome
  
     lbl.Location = New Point(x, y)
  
     lbl.Width = 150
  
     Me.Controls.Add(lbl)
  
   End Sub
  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  
     'Adding the labels to display the dice
  
     AddDice(lblDice1, 10, 20)
  
     AddDice(lblDice2, 70, 20)
  
     AddDice(lblDice3, 130, 20)
  
     AddDice(lblDice4, 190, 20)
  
     AddDice(lblDice5, 250, 20)
  
     'Adding the actual numbers that have been rolled into the dice labels
  
     AddOutput(lblYatzee, "Yahtzee: 0", 20, 140)
  
     AddOutput(lblFourOfAKind, "Four Of A Kind: 0", 20, 180)
  
     AddOutput(lblThreeOfAKind, "Three Of A Kind: 0", 20, 220)
  
     'Creating the Roll button
  
     butRoll.Text = "Roll"
  
     butRoll.Location = New Point(100, 90)
  
     'Adding the roll button and output lables
  
     Me.Controls.Add(butRoll)
  
     Me.Controls.Add(lblFourOfAKind)
  
     Me.Controls.Add(lblThreeOfAKind)
  
   End Sub
  
   Private Sub roll(ByRef lbl As Label)
  
     'Method telling how to roll the dice
  
     lbl.Text = rnd.Next(1, 7)
  
   End Sub
  
   Private Sub RollDice() Handles butRoll.Click
  
     'Rolling the Dice
  
     roll(lblDice1)
  
     roll(lblDice2)
  
     roll(lblDice3)
  
     roll(lblDice3)
  
     roll(lblDice4)
  
     roll(lblDice5)
  
     'Getting the stats from rolling the dice
  
     stats()
  
     'Updating the textboxes as to what was rolled
  
     UpdateTextboxes()
  
   End Sub
  
   Private Sub stats()
  
     'Creating an array to keep track of what was rolled and deciding if a three of a kind, four of a kind, or yahtzee
  
     Dim arrNumbers() As Integer = {0, 0, 0, 0, 0, 0}
  
     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 UpdateTextboxes()
  
     'Method stating to update the text boxes after each roll
  
     lblYatzee.Text = "Yatzees: " & nYatzee
  
     lblFourOfAKind.Text = "Four Of A Kind: " & nFourOfAKind
  
     lblThreeOfAKind.Text = "Three Of A Kind: " & nThreeOfAKind
  
   End Sub
  
 End Class
  

Subquery Practice

 use AdventureWorks
  
 select PC.FirstName, PC.LastName, PC.EmailAddress, HE.Title
  
 from Person.Contact PC
  
 inner join HumanResources.Employee HE
  
 on PC.ContactID = HE.ContactID
  
 where HE.EmployeeID in
  
      (
  
      select SS.SalesPersonID
  
      from Sales.SalesPerson SS
  
      )
  
 select SP.salespersonID, PC.EmailAddress
  
 from Sales.SalesPerson SP
  
 inner join HumanResources.Employee HRE
  
 on SP.SalesPersonID = HRE.EmployeeID
  
 inner join Person.Contact PC
  
 on HRE.ContactID = PC.ContactID
  
 where SP.SalesPersonID in 
  
      (
  
      select SOH.SalesPersonID
  
      from Sales.SalesOrderHeader SOH
  
      where SOH.TotalDue > '150000'
  
      )
  
 select PP.productID, (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
  
 use AdventureWorks
  
 declare @top_sales_person int
  
 set @top_sales_person = 
  
      (
  
      select top 1 SP.SalesPersonID
  
      from Sales.SalesPerson SP
  
      order by SP.SalesLastYear desc
  
      )
  
 declare @customers int
  
 set @customers =
  
      (
  
      select COUNT (distinct customerid)
  
      from Sales.SalesOrderHeader SOH
  
      where SOH.SalesPersonID = @top_sales_person
  
      )
  
 declare @total_sales int
  
 set @total_sales =
  
      (select COUNT (distinct SOH.SalesOrderID)
  
      from Sales.SalesOrderHeader SOH
  
      where SOH.SalesPersonID = @top_sales_person
  
      )
  
 select PC.FirstName, PC.LastName, DATEDIFF ( year, HRE.HireDate, GETDATE() ) Tenure, @customers Customers 
  
 , @total_sales Total_Sales
  
 from Person.Contact PC
  
 inner join HumanResources.Employee HRE
  
 on PC.ContactID = HRE.ContactID
  
 inner join Sales.SalesPerson SP
  
 on HRE.EmployeeID = SP.SalesPersonID
  
 where SP.SalesPersonID = @top_sales_person  

Programming Final

Calculator

 Public Class Form1
  
   Dim number1 As Decimal
  
   Dim number2 As Decimal
  
   Dim use As Integer
  
   Private Sub btn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1.Click
  
     txtDisplay.Text = txtDisplay.Text & btn1.Text
  
   End Sub
  
   Private Sub btn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click
  
     txtDisplay.Text = txtDisplay.Text & btn2.Text
  
   End Sub
  
   Private Sub btn3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn3.Click
  
     txtDisplay.Text = txtDisplay.Text & btn3.Text
  
   End Sub
  
   Private Sub btn4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn4.Click
  
     txtDisplay.Text = txtDisplay.Text & btn4.Text
  
   End Sub
  
   Private Sub btn5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn5.Click
  
     txtDisplay.Text = txtDisplay.Text & btn5.Text
  
   End Sub
  
   Private Sub btn6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn6.Click
  
     txtDisplay.Text = txtDisplay.Text & btn6.Text
  
   End Sub
  
   Private Sub btn7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn7.Click
  
     txtDisplay.Text = txtDisplay.Text & btn7.Text
  
   End Sub
  
   Private Sub btn8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn8.Click
  
     txtDisplay.Text = txtDisplay.Text & btn8.Text
  
   End Sub
  
   Private Sub btn9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn9.Click
  
     txtDisplay.Text = txtDisplay.Text & btn9.Text
  
   End Sub
  
   Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
  
     txtDisplay.Text = " "
  
   End Sub
  
   Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
  
     number1 = number1 + Val(txtDisplay.Text)
  
     use = 1
  
     txtDisplay.Clear()
  
   End Sub
  
   Private Sub btnSubtract_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubtract.Click
  
     number1 = number1 + Val(txtDisplay.Text)
  
     use = 2
  
     txtDisplay.Clear()
  
   End Sub
  
   Private Sub btnMultiply_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMultiply.Click
  
     number1 = number1 + Val(txtDisplay.Text)
  
     use = 3
  
     txtDisplay.Clear()
  
   End Sub
  
   Private Sub btnDivide_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDivide.Click
  
     number1 = number1 + Val(txtDisplay.Text)
  
     use = 4
  
     txtDisplay.Clear()
  
   End Sub
  
   Private Sub btnEquals_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEquals.Click
  
     number2 = number2 + Val(txtDisplay.Text)
  
     If use = 1 Then
  
       txtDisplay.Text = number1 + number2
  
     ElseIf use = 2 Then
  
       txtDisplay.Text = number1 - number2
  
     ElseIf use = 3 Then
  
       txtDisplay.Text = number1 * number2
  
     ElseIf use = 4 Then
  
       txtDisplay.Text = number1 / number2
  
     End If
  
     number1 = 0
  
     number2 = 0
  
     use = 0
  
   End Sub
  
   Private Sub btnDeciamal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeciamal.Click
  
     txtDisplay.Text = txtDisplay.Text & btnDeciamal.Text
  
   End Sub
  
 End Class
  

If and Case Statements

declare @num int
set @num =4
declare @salespersonid int

select @num

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

use AdventureWorks
select FirstName, LastName,
case emailpromotion
 when 1 then 'Email Promo'
 when 2 then 'Text Promo'
 else 'Leave Me Alone'
end promo
from person.contact

Practice #5

1. Adding Days of The Week

 Public Class Form1
  
   Dim week(0 To 6) As String
  
   Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
  
     week(0) = "Monday"
  
     week(1) = "Tuesday"
  
     week(2) = "Wednesday"
  
     week(3) = "Thursday"
  
     week(4) = "Friday"
  
     week(5) = "Saturday"
  
     week(6) = "Sunday"
  
     For Each day As String In week
  
       MsgBox(day)
  
     Next
  
   End Sub
  
 End Class  

2. Adding Users

 Public Class Form1
  
   Dim Users As New Hashtable
  
   Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
  
     Users.Add("FirstName", txtFirstName.Text)
  
     Users.Add("LastName", txtLastName.Text)
  
     Users.Add("EMail", txtEMail.Text)
  
     txtEMail.Clear()
  
     txtFirstName.Clear()
  
     txtLastName.Clear()
  
   End Sub
  
   Private Sub btnFirstName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirstName.Click
  
     MsgBox(Users.Item("FirstName"))
  
   End Sub
  
   Private Sub bntLastName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntLastName.Click
  
     MsgBox(Users.Item("LastName"))
  
   End Sub
  
   Private Sub btnEmail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEmail.Click
  
     MsgBox(Users.Item("EMail"))
  
   End Sub
  
 End Class  

3. Printer Queue

 Public Class Form
  
   Dim joblist As New Queue
  
   Private Sub btnSend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSend.Click
  
     Dim jobs As New Hashtable
  
     jobs.Add("Job Title", txtTitle.Text)
  
     jobs.Add("Page Quantity", NumericUpDown1.Value)
  
     joblist.Enqueue(jobs)
  
     For Each h As Hashtable In joblist
  
       lstJobs.Items.Add("(" & h.Item("Page Quantity") & ")" & h.Item("Job Title"))
  
     Next
  
     jobs.Clear()
  
     joblist.Clear()
  
     txtTitle.Clear()
  
     NumericUpDown1.Value = 1
  
   End Sub
  
   Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
  
     lstJobs.Items.Clear()
  
   End Sub
  
 End Class
  

Practice Numer 4

#1 - Adding items to a list box with a loop

 Public Class Form1
  
   Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
  
     Dim amount As Integer = txtValue.Text
  
     For n As Integer = 1 To amount
  
       ListBox1.Items.Add(n & "items")
  
     Next
  
   End Sub
  
 End Class  

#2 - How many years will it take to save up to a certain amount of money

 Public Class Form1
  
   Private Sub btnYears_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnYears.Click
  
     Dim payment As Integer = txtPayment.Text
  
     Dim counter As Integer = 1
  
     Dim amount As Integer
  
     Dim goal As Integer = 10000
  
     Dim years As Integer
  
     Do While amount < goal
  
       counter += 1
  
       amount = payment * counter
  
     Loop
  
     years = counter / 12
  
     MessageBox.Show(years & " Years")
  
   End Sub
  
 End Class  

#3 - Create a Times table using a loop within a loop

 Public Class Form1
  
   Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
  
     Dim y As Integer
  
     Dim x As Integer
  
     For x = 1 To 10
  
       For y = 1 To 10
  
         Dim btn As New Button
  
         btn.Location = New Point(35 * x, 35 * y)
  
         btn.Height = 35
  
         btn.Width = 35
  
         Me.Controls.Add(btn)
  
         btn.Text = (x * y)
  
       Next
  
     Next
  
   End Sub
  
 End Class  

Left/Right Outer Joins

Below are examples of left and right outer joins.

 use Yummys
  
 select *
  
 from Brand B
  
 left outer join xrefFlavorBrand xrefFB
  
 on B.BrandID = xrefFB.BrandID
  
 --Right Outer Join
  
 select *
  
 from sales S
  
 right outer join xrefDistSales xrefDS
  
 on S.SalesID = xrefDS.SalesID  

Loops

Below are some examples of code for different loops used in VB.Net

Code for a do(while first) loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim x As Integer = 5
  
     Do While x < 10
  
       x += 1
  
     Loop
  
   End Sub
  
 End Class  

Code for a do(while last) loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim x As Integer = 5
  
     Do
  
       x += 1
  
       MsgBox(x)
  
     Loop While (x < 10)
  
   End Sub
  
 End Class  

Code for a for loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim x As Integer = 1
  
     Dim y As Integer = 5
  
     For n As Integer = x To y
  
       MsgBox(n)
  
     Next
  
   End Sub
  
 End Class  

Code For a for each loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim name As String = "Ben"
  
     Dim character As Char
  
     For Each character In name
  
       MsgBox(character)
  
     Next
  
   End Sub
  
 End Class  

Inner Join Practice 2

 --drop table Brand
  
 --drop table Flavor
  
 --drop table xrefFlavorBrand
  
 --drop table sales
  
 --drop table distributors
  
 --drop table xrefDistFlavorBrand
  
 --drop table xrefDistSales
  
 use Yummys
  
 go
  
 create table Brand
  
 (
  
    BrandID int identity(1,1) primary key
  
    ,BrandName varchar(50)
  
    ,BrandLocation varchar(50)
  
 )
  
 insert into Brand (BrandName, BrandLocation) values ('BlueBunny', 'San Francisco')
  
 insert into Brand (BrandName, BrandLocation) values ('PurpleMonkey', 'Detroit')
  
 insert into Brand (BrandName, BrandLocation) values ('PinkHippo', 'Malibu')
  
 insert into Brand (BrandName, BrandLocation) values ('GreenRabbit', 'Tucson')
  
 insert into Brand (BrandName, BrandLocation) values ('RainbowLamb', 'Minneapolis')
  
 select * 
  
 from Brand
  
 create table flavor
  
    (
  
    Flavor_ID int identity (1,1) PRIMARY KEY,
  
    flavor_name varchar (40),
  
    descraption varchar (100),
  
    quantity int
  
    )
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    values
  
    ('choclate', 'classic choclate', 2)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    values
  
    ('Vanilla', 'Plain Vanilla', 4)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Neopolatin', '1/3 Choclate, 1/3 Vanilla. 1/3 Strawberry', 1)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Coffee', 'Fresh Cround Coffee Flavor', 0)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Sardine', 'Self-Explanatory', 7)
  
 select *
  
 from flavor
  
 create table xrefFlavorBrand
  
 (
  
    xrefID int identity (1,1) primary key
  
    , BrandID int
  
    , FlavorID int
  
 )
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (1,1)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (2,2)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (3,3)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (4,4)
  
 insert into xrefFlavorBrand (BrandID, FlavorID) values (5,5)
  
 create table sales
  
 (
  
    SalesID int identity (1,1) primary key
  
    , xrefID int
  
    , ScoopNo int
  
    , Price decimal(4,2)
  
    , SalesDate int
  
    )
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (1, 2, 2.00, 1/2/10)
  
 insert into sales 
  
    (xrefID, ScoopNo, Price, SalesDate) values (2, 3, 3.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (3, 2, 2.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (2,3,3.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (4,2,2.00, 1/4/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (5, 4, 4.00, 1/4/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (1, 5, 5.00, 1/5/10)  
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (4, 3, 3.00, 1/5/10)  
  
 select * from sales
  
 create table xrefDistFlavorBrand
  
 (
  
       xrefDistSalesID int identity (1,1) primary key
  
       ,BrandID int
  
       ,FlavorID int
  
       ,distributor_ID int
  
 )
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(1,1,1)
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(2,2,2)
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(3,3,3)
  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(4,4,4)
  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values (5,5,5)
  
 select * 
  
 from xrefDistFlavorBrand
  
 --here is the second table
  
 create table xrefDistSales
  
 (
  
       xrefDistSalesID int identity (1,1) primary key
  
       ,BrandID int
  
       ,SalesID int      
  
 )
  
 insert into xrefDistSales(BrandID,SalesID) values (1,1)
  
 insert into xrefDistSales(BrandID,SalesID) values (2,2)
  
 insert into xrefDistSales(BrandID,SalesID) values (3,3)
  
 insert into xrefDistSales(BrandID,SalesID) values (4,4)
  
 insert into xrefDistSales(BrandID,SalesID) values (5,5)
  
 select *
  
 from xrefDistSales
  
 create table distributors
  
    (
  
       distributor_ID int identity (1,1) PRIMARY KEY,
  
       DistributorName varchar (30)
  
    );
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('ND Icecream')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('GF Grocery')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Henreys Foods')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Mayville Grocery')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Red River Valley Icecream')
  
 select *
  
 from distributors
  
 --keep track of the number of tubs of ice cream on hand
  
 Select * 
  
 from flavor FL
  
 inner join xrefDistFlavorBrand xDFB
  
 on FL.Flavor_ID = xDFB.FlavorID
  
 where quantity <=1
  
 --search distributors by flavor and brand, get a list of distributors
  
 select * 
  
 from distributors D
  
 inner join xrefDistFlavorBrand xDFB
  
 on d.Distributor_ID = xDFB.Distributor_ID
  
 inner join flavor F
  
 on f.Flavor_ID = xDFB.FlavorID
  
 inner join Brand B
  
 on B.BrandID = xDFB.BrandID
  
 where flavor_name = 'choclate'
  
 and
  
 BrandName = 'BlueBunny'
  
 --Keep track of which flavors and brands were sold
  
 select * 
  
 from sales S
  
 inner join xrefDistSales xDS
  
 on s.SalesID = xDS.SalesID
  
 inner join xrefDistFlavorBrand xDFB
  
 on xDS.xrefDistSalesID = xDFB.xrefDistSalesID
  
 inner join flavor F
  
 on f.Flavor_ID = xDFB.FlavorID
  
 where f.flavor_name = 'vanilla'
  

Assignment Three - Question Four

Create a form with three buttons and a listbox. Upon loading put three items in the listbox so it is not empty. Make button one selct item one, button two select item two, and button three select item three.

 Public Class Form1
  
   Dim WithEvents lstItems As New ListBox
  
   Dim strone As String = "Item One"
  
   Dim strtwo As String = "Item Two"
  
   Dim strthree As String = "Item Three"
  
   Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  
     lstItems.Location = New Point(82, 5)
  
     lstItems.Items.Add(strone)
  
     lstItems.Items.Add(strtwo)
  
     lstItems.Items.Add(strthree)
  
     Me.Controls.Add(lstItems)
  
   End Sub
  
   Private Sub btnOne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOne.Click
  
     lstItems.SelectedIndex = 0
  
   End Sub
  
   Private Sub btnTwo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTwo.Click
  
     lstItems.SelectedIndex = 1
  
   End Sub
  
   Private Sub btnThree_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThree.Click
  
     lstItems.SelectedIndex = 2
  
   End Sub
  
 End Class  

Assignment Three - Question Three

When button is clicked, it will create 3 labels and 3 textboxes associated with those labels. When textboxes are hovered over, change their background color. When it is not being hovered over, change the bacground color back to white.

 Public Class Form1
  
   Dim WithEvents txtone, txttwo, txtthree As New TextBox
  
   Dim WithEvents lblone, lbltwo, lblthree As New Label
  
   Private Sub btnMultiply_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMultiply.Click
  
     txtone.Height = 75
  
     txtone.Width = 100
  
     txtone.Text = "I Am Textbox One"
  
     txtone.Location = New Point(4, 1)
  
     txtone.BackColor = Color.White
  
     txttwo.Height = 75
  
     txttwo.Width = 100
  
     txttwo.Text = "I Am Textbox Two"
  
     txttwo.Location = New Point(225, 1)
  
     txttwo.BackColor = Color.White
  
     txtthree.Height = 75
  
     txtthree.Width = 100
  
     txtthree.Text = "I Am Textbox Three"
  
     txtthree.Location = New Point(450, 1)
  
     txtthree.BackColor = Color.White
  
     lblone.Height = 100
  
     lblone.Width = 100
  
     lblone.Text = "I Am Label One"
  
     lblone.Location = New Point(4, 85)
  
     lbltwo.Height = 100
  
     lbltwo.Width = 100
  
     lbltwo.Text = "I Am Label Two"
  
     lbltwo.Location = New Point(225, 85)
  
     lblthree.Height = 100
  
     lblthree.Width = 100
  
     lblthree.Text = "I Am Label Three"
  
     lblthree.Location = New Point(450, 85)
  
     Me.Controls.Add(txtone)
  
     Me.Controls.Add(txttwo)
  
     Me.Controls.Add(txtthree)
  
     Me.Controls.Add(lblone)
  
     Me.Controls.Add(lbltwo)
  
     Me.Controls.Add(lblthree)
  
   End Sub
  
   Private Sub txtone_mouseenter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtone.MouseEnter
  
     txtone.BackColor = Color.Red
  
   End Sub
  
   Private Sub txtone_mouseleave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtone.MouseLeave
  
     txtone.BackColor = Color.White
  
   End Sub
  
   Private Sub txttwo_mouseenter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txttwo.MouseEnter
  
     txttwo.BackColor = Color.Red
  
   End Sub
  
   Private Sub txttwo_mouseleave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txttwo.MouseLeave
  
     txttwo.BackColor = Color.White
  
   End Sub
  
   Private Sub txtthree_mouseenter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtthree.MouseEnter
  
     txtthree.BackColor = Color.Red
  
   End Sub
  
   Private Sub txtthree_mouseleave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtthree.MouseLeave
  
     txtthree.BackColor = Color.White
  
   End Sub
  
 End Class  

Assignment Three - Question Two

Form with 4 buttons
1 - enable/disable textbox
2 - change background color in textbox between two colors
3 - Put in and remove text in textbox
4 - Change border style between fixed 3d and none

 Public Class Form1
  
   Private Sub btnEnable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnable.Click
  
     If txtBox.Enabled = True Then
  
       txtBox.Enabled = False
  
     ElseIf txtBox.Enabled = False Then
  
       txtBox.Enabled = True
  
     End If
  
   End Sub
  
   Private Sub btnBackground_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackground.Click
  
     If txtBox.BackColor = Color.White Then
  
       txtBox.BackColor = Color.Red
  
     ElseIf txtBox.BackColor = Color.Red Then
  
       txtBox.BackColor = Color.White
  
     End If
  
   End Sub
  
   Private Sub btnHide_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHide.Click
  
     If txtBox.Text = "Now You See Me" Then
  
       txtBox.Text = ""
  
     ElseIf txtBox.Text = "" Then
  
       txtBox.Text = "Now You See Me"
  
     End If
  
   End Sub
  
   Private Sub btnBoarder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBoarder.Click
  
     If txtBox.BorderStyle = BorderStyle.Fixed3D Then
  
       txtBox.BorderStyle = BorderStyle.None
  
     ElseIf txtBox.BorderStyle = BorderStyle.None Then
  
       txtBox.BorderStyle = BorderStyle.Fixed3D
  
     End If
  
   End Sub
  
 End Class  

Assignment 3 - Question One

When button is hovered over move button to a random location

 Public Class Form1
  
   Private Sub btn1_MouseHover(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1.MouseHover
  
     Dim rnd1 As New Random
  
     Dim x As Integer = rnd1.Next(100)
  
     Dim y As Integer = rnd1.Next(100)
  
     btn1.Location = New Point(x, y)
  
   End Sub
  
 End Class  

Assignment

 use Yummys
  
 go
  
 --drop table Brand
  
 --drop table Flavor
  
 --drop table xrefFlavorBrand
  
 create table Brand
  
 (
  
    BrandID int identity(1,1) primary key
  
    ,BrandName varchar(50)
  
    ,BrandLocation varchar(50)
  
 )
  
 insert into Brand (BrandName, BrandLocation) values ('BlueBunny', 'San Francisco')
  
 insert into Brand (BrandName, BrandLocation) values ('PurpleMonkey', 'Detroit')
  
 insert into Brand (BrandName, BrandLocation) values ('PinkHippo', 'Malibu')
  
 insert into Brand (BrandName, BrandLocation) values ('GreenRabbit', 'Tucson')
  
 insert into Brand (BrandName, BrandLocation) values ('RainbowLamb', 'Minneapolis')
  
 select * 
  
 from Brand
  
 create table flavor
  
    (
  
    Flavor_ID int identity (1,1) PRIMARY KEY,
  
    flavor_name varchar (40),
  
    descraption varchar (100)
  
    )
  
    insert into flavor
  
    (flavor_name, descraption)
  
    values
  
    ('choclate', 'classic choclate')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    values
  
    ('Vanilla', 'Plain Vanilla')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    Values
  
    ('Neopolatin', '1/3 Choclate, 1/3 Vanilla. 1/3 Strawberry')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    Values
  
    ('Coffee', 'Fresh Cround Coffee Flavor')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    Values
  
    ('Sardine', 'Self-Explanatory')
  
 select *
  
 from flavor
  
 create table xrefFlavorBrand
  
 (
  
    xrefID int identity (1,1) primary key
  
    , BrandID int
  
    , FlavorID int
  
 )
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (1,1)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (2,2)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (3,3)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (4,4)
  
 insert into xrefFlavorBrand (BrandID, FlavorID) values (5,5)
  
 select *
  
 from xrefFlavorBrand
  
 select FL.*, xFB.BrandID, B.BrandName
  
 from flavor FL 
  
 inner join xrefFlavorBrand xFB
  
 on FL.Flavor_ID = xFB.xrefID
  
 inner join Brand B
  
 on xFB.BrandID = B.BrandID
  
 where FL.flavor_name = 'Coffee'
  

Adding a button, label, listbox, and text box programmatically

Here I added a button, label, listbox, and text box programmatically. I also changed some properties along the way.

 Public Class Form1
  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  
     Dim txtBox As New TextBox
  
     Dim lblLabel As New Label
  
     Dim btnButton As New Button
  
     Dim lstList As New ListBox
  
     txtBox.Height = 7
  
     txtBox.Width = 150
  
     txtBox.Text = "Hello"
  
     txtBox.Location = New Point(50, 1)
  
     lblLabel.Text = "Hola"
  
     lblLabel.Location = New Point(50, 150)
  
     btnButton.Height = 75
  
     btnButton.Width = 75
  
     btnButton.Text = "Bonjour"
  
     btnButton.Location = New Point(50, 50)
  
     lstList.Height = 70
  
     lstList.Width = 50
  
     lstList.Text = "Hi"
  
     lstList.Location = New Point(50, 175)
  
     Me.Controls.Add(txtBox)
  
     Me.Controls.Add(lblLabel)
  
     Me.Controls.Add(btnButton)
  
     Me.Controls.Add(lstList)
  
   End Sub
  
 End Class  

Using If and Case statements

1. Shipping Costs

 Public Class Form1
  
   Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotal.Click
  
     Dim itemscost As String
  
     Dim totalcost As Integer
  
     itemscost = txtItemsCost.Text
  
     If itemscost >= 50 Then
  
       totalcost = itemscost
  
     ElseIf itemscost < 50 Then
  
       totalcost = itemscost + 5
  
     End If
  
     MessageBox.Show(totalcost)
  
   End Sub
  
 End Class  

2. Temperature Control App

 Public Class Form1
  
   Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotal.Click
  
     Dim temp As Integer = txtTemp.Text
  
     Dim AC As Integer = 76
  
     Dim heat As Integer = 72
  
     If temp < heat Then
  
       MessageBox.Show("Heat Turned On")
  
     ElseIf temp > AC Then
  
       MessageBox.Show("AC Turned on")
  
     ElseIf temp <> heat And AC Then
  
       MessageBox.Show("Temperature Perfect")
  
     End If
  
   End Sub
  
 End Class  

3. Sock Size by Age

 Public Class Form1
  
   Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSize.Click
  
     Dim size As String
  
     Dim age As String = txtAge.Text
  
     Select Case age
  
       Case 0 To 2
  
         size = "XS"
  
       Case 3 To 4
  
         size = "S"
  
       Case 5 To 8
  
         size = "M"
  
       Case 9 To 12
  
         size = "L"
  
       Case 13 To 20
  
         size = "XL"
  
     End Select
  
     MsgBox(size)
  
   End Sub
  
 End Class  

If Statements

A few different applications of If statements and Case satements

An if statement that makes one decision

 Public Class Form1
  
   Private Sub btnOne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOne.Click
  
     If chkCheck.Checked Then
  
       MessageBox.Show("Congrats, You Are Alive")
  
     End If
  
   End Sub
  
 End Class  

An if statement where it's either or

 Public Class Form1
  
   Private Sub btnOne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOne.Click
  
     If chkCheck.Checked Then
  
       MessageBox.Show("You Win")
  
     Else
  
       MessageBox.Show("You Lose")
  
     End If
  
   End Sub
  
 End Class  

An if statement where many decisions are made

 Public Class Form1
  
   Private Sub btnOne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOne.Click
  
     If chkCheck.Checked Then
  
       MessageBox.Show("You win 1 dollar")
  
     ElseIf chkCheck2.Checked Then
  
       MessageBox.Show("You lose 1 dollar")
  
     ElseIf chkCheck3.Checked Then
  
       MessageBox.Show("You win 2 dollars")
  
     ElseIf chkCheck4.Checked Then
  
       MessageBox.Show("You lose 2 dollars")
  
     End If
  
   End Sub
  
 End Class  

A case statement

 Public Class Form1
  
   Private Sub btnFeedback_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFeedback.Click
  
     Dim team As String
  
     Dim anwser As String
  
     team = txtYesOrNo.Text
  
     Select Case team
  
       Case "yes"
  
         anwser = "Too Bad"
  
       Case "no"
  
         anwser = "Good Choice"
  
       Case Else
  
         anwser = "Check Your Spelling"
  
     End Select
  
     MsgBox(anwser)
  
   End Sub
  
 End Class  

VB Word Problems

1. Morgan has 6 cows and 7 sheep. How many total animals does she have?
Public Class frmcows

   Private Sub btnAnwser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnwser.Click
  
     Dim sheep As Integer = 7
  
     Dim cows As Integer = 6
  
     Dim total As Integer
  
     total = sheep * cows
  
     MessageBox.Show(total)
  
   End Sub
  
 End Class
  

2. Diane bought 7 burgers, each of which cost $4. How much did she spend on burgers?

   Private Sub btnCost_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCost.Click
  
     Dim cost As Integer = 4
  
     Dim amount As Integer = 7
  
     Dim totalcost As Integer
  
     totalcost = cost * amount
  
     MessageBox.Show(totalcost)
  
   End Sub
  
 End Class
  

3. Ole has 15 apples and 12 oranges. Whats his total fruit count?
Public Class frmFruit

 Private Sub btnFruit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFruit.Click
  
     Dim apples As Integer = 15
  
     Dim oranges As Integer = 12
  
     Dim totalfruit As Integer
  
     totalfruit = apples + oranges
  
     MessageBox.Show(totalfruit)
  
   End Sub
  
 End Class
  

4. There are 33 horses to begin, 15 go into the barn, 7 come back out. What is the new population?

 Public Class frmHorses
  
   Private Sub btnLeftover_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLeftover.Click
  
     Dim beginningpopulation As Integer = 33
  
     Dim barn As Integer = 15
  
     Dim returners As Integer = 7
  
     Dim newpopulation As Integer
  
     newpopulation = beginningpopulation - barn + returners
  
     MessageBox.Show(newpopulation)
  
   End Sub
  
 End Class  

5. Ingelbert has 15 apples and 3 times as many oranges. How many pieces of fruit do they have?

 Public Class frmFruit2
  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  
     Dim apples As Integer = 15
  
     Dim oranges As Integer
  
     Dim total As Integer
  
     oranges = apples * 3
  
     total = apples + oranges
  
     MessageBox.Show(total)
  
   End Sub
  
 End Class  

6. Using the grade table decide how many marks did Brian score in math and science combined, how many more does Andrew need for a perfect math score, and what is Andrew's percentages for all o the quizzes put together.

 Public Class frmGradeTABLE
  
   Private Sub btnGradeInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGradeInfo.Click
  
     Dim AEnglish As Integer = 12
  
     Dim AGeography As Integer = 19
  
     Dim AMath As Integer = 18
  
     Dim AScience As Integer = 7
  
     Dim BEnglish As Integer = 22
  
     Dim BGeography As Integer = 15
  
     Dim BMath As Integer = 7
  
     Dim BScience As Integer = 22
  
     Dim combinedmath As Integer
  
     Dim mathperfectscore As Integer
  
     Dim APercentage As Integer
  
     Dim totalanwser As String
  
     combinedmath = BMath + AMath
  
     mathperfectscore = 25 - AMath
  
     APercentage = (AEnglish + AGeography + AMath + AScience) / 100
  
     totalanwser = String.Concat(combinedmath.ToString & Environment.NewLine, mathperfectscore.ToString & Environment.NewLine, APercentage.ToString & Environment.NewLine)
  
     Console.WriteLine(totalanwser)
  
     MessageBox.Show(totalanwser)
  
   End Sub
  
 End Class  

Table Variable Create and Inner Join

Below is code on how I created 2 Table Variables and then inner joined the two using a primary key.

 use blog
  
 go
  
 declare @Potatoe_Varieties table
  
 (
  
      Potatoe_ID int identity (1,1) PRIMARY KEY,
  
      Name varchar (40),
  
      Color varchar (10),
  
      Uses varchar (60)
  
 );
  
 Insert into @Potatoe_Varieties
  
 (Name, Color, Uses)
  
 Values
  
 ('Norlands', 'Red', 'Fresh Market Sales')
  
 Insert into @Potatoe_Varieties
  
 (Name, Color, Uses)
  
 Values
  
 ('Russet Burbank', 'Yellow', 'Chips, fries, dehydrated for later use')
  
 Insert into @Potatoe_Varieties
  
 (Name, Color, Uses)
  
 Values
  
 ('Yukon Gold', 'Golden', 'Fresh Market Sales and dehydrated for later use')
  
 declare @Potatoe_size table
  
 (
  
      Potatoe_ID int identity (1,1) PRIMARY KEY,
  
      [weight] varchar (10),
  
      diameter varchar (10),
  
      yield varchar (30)
  
 );
  
 Insert into @Potatoe_size
  
 ([weight], diameter, yield)
  
 Values
  
 ('8oz', '6 inches', '150-300 100lb bags per acre')
  
 Insert into @Potatoe_size
  
 ([weight], diameter, yield)
  
 Values
  
 ('12oz', '7 inches', '250-375 100lb bags per acre')
  
 Insert into @Potatoe_size
  
 ([weight], diameter, yield)
  
 Values
  
 ('7oz', '6 inches', '150-300 100lb bags per acre')
  
 select PV.*, [weight], diameter, yield
  
 from @Potatoe_Varieties PV
  
 inner join @Potatoe_size PS
  
      on PV.Potatoe_ID = PS.Potatoe_ID
  

Joining Two Strings

 Public Class Form1
  
 
  
   Private Sub btnJoin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnJoin.Click
  
     Dim start As String = 9
  
     Dim finish As String = 7
  
     Dim together As String = String.Concat(start, finish)
  
     Console.WriteLine(together)
  
 
  
 
  
     MessageBox.Show(together)
  
 
  
   End Sub
  
 End Class  

Converting Into to Dec and Dec to Int

   Private Sub btnDecToInt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDecToInt.Click
  
     Dim value As Integer
  
     Dim quantity As Decimal
  
 
  
     value = 5
  
     quantity = 0.3
  
 
  
     quantity = Convert.ToDecimal(value)
  
     MessageBox.Show(quantity.GetType.ToString)
  
   End Sub
  
 
  
   Private Sub btnIntToDec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIntToDec.Click
  
     Dim X As Integer
  
     Dim Y As Decimal
  
 
  
     X = 1
  
     Y = 0.1
  
 
  
     X = Convert.ToInt16(Y)
  
     MessageBox.Show(X.GetType.ToString)
  
   End Sub
  
 End Class  

Assignment1 - Creating Database and table, inserting records, and selecting all records

 create database Frogs
  
 create table Wyoming_Species
  
      (
  
      Frog_ID int identity (1,1) PRIMARY KEY,
  
      Scientific_Name varchar (100),
  
      IUCN_Red_List_Status varchar (50),
  
      Vernacular_Name varchar (50),
  
      Family varchar(30)
  
      );
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Bufo baxteri', 'Extinct in the Wild(EW)', 'Wyoming Toad', 'Bufonidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Bufo boreas', 'Near Threatened(NT)', 'Western Toad', 'Bufonidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Bufo cognatus', 'Least Concern(LC)', 'Great Plains Toad', 'Bufonidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Bufo hemiophrys', 'Lese Concern(LC)', 'Canadian Toad', 'Bufonidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Bufo woodhousii', 'Least Concern(LC)', 'Woodhouse''s Toad', 'Bufonidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Psedacris maculata', 'Least Concern(LC)', 'Boreal Chorus Frog', 'Hylidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Rana catesbeiana', 'Least Concern(LC)', 'Bullfrog', 'Ranidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Ranna luteiventris', 'Least Concern(LC)', 'Columbia Spotted Frog', 'Ranidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Rana pipiens', 'Least Concern(LC)', 'Northern Leopard Frog', 'Ranidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Rana sylvatica', 'Least Concern(LC)', 'Wood Frog', 'Ranidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Spea bombifrons', 'Least Concern(LC)', 'Plains Spadefoot', 'Scaphiopodidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Spea intermontana', 'Least Concern(LC)', 'Great Basin Spreadfoot', 'Scaphiopodidae');
  
 Insert Into Wyoming_Species
  
 (Scientific_Name, IUCN_Red_List_Status, Vernacular_Name, Family)
  
 Values
  
 ('Ambystoma mavortium', ' ', 'Barred Tiger Salamander', 'Ambystomatidae');
  
 select *
  
 from Wyoming_Species  

Create and Use Database

 create database Fields
  
 Use Fields  

Create Table Variable, Insert Records, and Change one Row

 Use BLOG
  
 GO
  
  declare @Fields table
  
      (
  
      Name varchar (25),
  
      County varchar (25),
  
      Crop varchar (25)
  
      );
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Droske Quarter', 'Pembina', 'Potatoes');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Nash Quarter', 'Walsh', 'Wheat');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Cambell Half Section', 'Pembina', 'Navy Beans');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Home 90', 'Pembina', 'Sugar Beets')
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Lysengen Quarter', 'Pembina', 'Potatoes')
  
 select *
  
 from @Fields
  
 delete from @Fields
  
 where Name='Lysengen Quarter'
  
 select *
  
 from @Fields
  
 update @Fields
  
 set Crop = 'Wheat'
  
 where Name = 'Cambell Half Section'
  
 select *
  
 from @Fields  

Converting A Number Variable to a String

 Public Class Form1
  
 
  
   Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConvert.Click
  
     Dim number As Integer
  
     number = 97
  
 
  
     number.ToString()
  
 
  
     MessageBox.Show(number)
  
 
  
   End Sub
  
 End Class  

Setting sting, boolean, integer, and decimal Variables

 Public Class Form1
  
 
  
   Private Sub btnInterger_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInterger.Click
  
     Dim interger As Integer
  
     interger = 5
  
     MessageBox.Show(interger)
  
 
  
   End Sub
  
 
  
   Private Sub btnDecimal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDecimal.Click
  
     Dim decimalvalue As Decimal
  
     decimalvalue = 10
  
     MessageBox.Show(decimalvalue)
  
   End Sub
  
 
  
   Private Sub btnBoolean_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBoolean.Click
  
     Dim booleanvalue As Boolean
  
     booleanvalue = 15
  
     MessageBox.Show(booleanvalue)
  
   End Sub
  
 
  
   Private Sub btnString_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnString.Click
  
     Dim stringvalue As String
  
     stringvalue = 20
  
     MessageBox.Show(stringvalue)
  
 
  
   End Sub
  
 End Class
  
   

Selecting and Deleting with a Table Variable

 Use BLOG
  
 GO
  
  declare @Fields table
  
      (
  
      Name varchar (25),
  
      County varchar (25),
  
      Crop varchar (25)
  
      );
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Droske Quarter', 'Pembina', 'Potatoes');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Nash Quarter', 'Walsh', 'Wheat');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Cambell Half Section', 'Pembina', 'Navy Beans');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Home 90', 'Pembina', 'Sugar Beets')
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Lysengen Quarter', 'Pembina', 'Potatoes')
  
 select *
  
 from @Fields
  
 delete from @Fields
  
 where Name='Lysengen Quarter'
  
 select *
  
 from @Fields  

Selecting with a table variable

 Use BLOG
  
 GO
  
  declare @Fields table
  
      (
  
      Name varchar (25),
  
      County varchar (25),
  
      Crop varchar (25)
  
      );
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Droske Quarter', 'Pembina', 'Potatoes');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Nash Quarter', 'Walsh', 'Wheat');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Cambell Half Section', 'Pembina', 'Navy Beans');
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Home 90', 'Pembina', 'Sugar Beets')
  
 Insert into @Fields
  
 (Name, County, Crop)
  
 values
  
 ('Lysengen Quarter', 'Pembina', 'Potatoes')
  
 select *
  
 from @Fields  

Table Variable

Table Variable tool used in SQL when you need a table that will be removed after you are out of the scope.  This way you only use temporary memory.

Syntax for Table Variable:
Use BLOG
declare @TibetanYaks Table(
        YakID int
       ,YakName char (30))


//Special Note of the @ before the table name

Tempory Tables in SQL

Temporary Table is a very useful tool when using SQL Server, and you are only needing a table for this one session of SQL.  After creating the table and filling with the with temporary data you only need for one session, you can use it like any other normal table, but it will be automatically dropped after closing that session of SQL.  The only restraint on this is that you cannot use foreign keys with Temp Tables.

Syntax to Create Temp Table:
create database BLOG
create table #Yaks (
       YakID int
       ,YakName char (30) )

//Special Note to the pound sign(#) infront of table name Yaks

Class

This is my Blog