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