Sites of interest

http://www.templatemonster.com/website-templates.php - for templates

VB Practice 7 - Methods II - Yahtzee

Public Class Form1

    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)
        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 Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        addDice(lblDice1, 10, 20)
        addDice(lblDice2, 70, 20)
        addDice(lblDice3, 130, 20)
        addDice(lblDice4, 190, 20)
        addDice(lblDice5, 250, 20)
        resultscount(lblYatzee, "Yahtzees: 0", 20, 140)
        resultscount(lblFourOfAKind, "Four of a Kinds: 0", 20, 180)
        resultscount(lblThreeOfAKind, "Three of a Kinds: 0", 20, 220)
        AddButton(butRoll, "Roll", 100, 90)

    End Sub

    Private Sub resultscount(ByRef results As TextBox, ByRef Name As String, ByVal x As Integer, ByVal y As Integer)
        results.Text = Name
        results.Location = New Point(x, y)
        results.Width = 150

        Me.Controls.Add(results)

    End Sub

    Private Sub RollDice() Handles butRoll.Click
        Dim arrNumbers() As Integer = {0, 0, 0, 0, 0, 0}

        RollSingleDice(lblDice1)
        RollSingleDice(lblDice2)
        RollSingleDice(lblDice3)
        RollSingleDice(lblDice4)
        RollSingleDice(lblDice5)

        GetStats()

        lblYatzee.Text = "Yatzees: " & nYatzee
        lblFourOfAKind.Text = "Four Of A Kind: " & nFourOfAKind
        lblThreeOfAKind.Text = "Three Of A Kind: " & nThreeOfAKind

    End Sub
    Private Sub AddButton(ByRef results As Button, ByRef Name As String, ByVal x As Integer, ByVal y As Integer)
        results.Text = Name
        results.Location = New Point(x, y)
        results.Width = 150

        Me.Controls.Add(results)

    End Sub
    Private Sub RollSingleDice(ByRef lblDice As Label)
        lblDice.Text = rnd.Next(1, 7)
    End Sub
    Private Sub GetStats()
        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
End Class

VB Practice 6 - Methods

Public Class Form1
    Dim chara, word, sentVal, space As Integer
    Private Sub btnAnalyze_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnalyze.Click
        'nchar = GetCharCount(TextBox1.Text)
        chara = TextBox1.Text.Length.ToString
        word = getWordCount(TextBox1.Text)
        space = getSpacesCount(TextBox1.Text)
        sentVal = getSentenceCount(TextBox1.Text)
        updatestats()
    End Sub
    Private Function getWordCount(ByVal value As String) As Integer
        Dim word As String
        word = System.Text.RegularExpressions.Regex.Matches(value, "\S+").Count
        Return word
    End Function
    Private Function getSentenceCount(ByVal value As String) As Integer
        Dim sentVal As String = 1
        sentVal = System.Text.RegularExpressions.Regex.Matches(value, "\.").Count
        sentVal += System.Text.RegularExpressions.Regex.Matches(value, "\!").Count
        sentVal += System.Text.RegularExpressions.Regex.Matches(value, "\?").Count
        Return sentVal
    End Function
    Private Function getSpacesCount(ByVal value As String) As Integer
        For Each s As String In value
            If s = " " Then
                space += 1
            End If
        Next
        Return space
    End Function
    Private Sub updatestats()
        txtChara.Text = chara
        txtword.Text = word
        txtspaces.Text = space
        txtSent.Text = sentVal
    End Sub

End Class

SQL Reservations Database queries

use FunHotelz
go
--Show all rooms for a given hotel. Show the customer name that is in the room if booked.

select r.RoomNum, h.HotelName, c.CustFirstName, c.CustLastName
from Room r
left outer join xrefRoomCust xRC
on r.RoomID = xRC.RoomID
left outer join Hotel h
on r.HotelID = h.HotelID
left outer join Customer c
on xRC.CustID = c.CustID
where CustLastName = 'Hummer'

--Search for an available room with different options. (Room: # beds, smoking, fridge, hot tub.)

select r.roomNum, h.HotelName, r.NumBeds, r.Smoking, r.HotTub, xRC.DOA
from Room r
left outer join Hotel h
on r.HotelID = h.HotelID
left outer join xrefRoomCust xRC
on r.RoomID = xRC.RoomID
where r.NumBeds = 1
and xRC.DOA is null

SQL Inner join Movie Databases

--drop table Movies
--drop table Actors
--drop table Quotes
--drop table xrefMovieActor
--drop table xrefActorQuote
--drop table xrefMovieQuotes

use DragonsQuotes
go

create table Quotes
(
      QuoteID int identity (1,1) primary key
      , Quote varchar(100)
)

Insert into Quotes (Quote) values ('Hakuna Matata')
Insert into Quotes (Quote) values ('Dat-da-dah!  We''re dead.')
Insert into Quotes (Quote) values ('Well you shouldn''t have been trying to steal our Ozone.')
Insert into Quotes (Quote) values ('I am Iron Man.  The suit and I are one.')
Insert into Quotes (Quote) values ('This is some rescue. When you came in here, didn''t you have a plan for getting out?')

select *
from Quotes

create table xrefActorQuote
(
      xrefAQID int identity (1,1) primary key
      , ActorID int
      , QuoteID int
);

insert into xrefActorQuote (ActorID, QuoteID) values (1,1)
insert into xrefActorQuote (ActorID, QuoteID) values (2,2)
insert into xrefActorQuote (ActorID, QuoteID) values (3,3)
insert into xrefActorQuote (ActorID, QuoteID) values (4,4)
insert into xrefActorQuote (ActorID, QuoteID) values (5,5)

select *
from xrefActorQuote

create table Movies
(
      MovieID int identity(1,1) not null primary Key
      ,MovieName varchar(100)
)

Insert into Movies
(
      MovieName
)

values
(
      'The Lion King'
)

Insert into Movies
(
      MovieName
)

values
(
      'How to Train Your Dragon'
)

Insert into Movies
(
      MovieName
)

values
(
      'Men in Black 2'
)

Insert into Movies
(
      MovieName
)

values
(
      'Iron Man 2'
)

Insert into Movies
(
      MovieName
)

values
(
      'Star Wars'
)

Select * 

from Movies

create table xrefMovieQuotes
(
xrefMQID int identity(1,1) not null primary Key
,MovieID int
,QuotesID int
)

insert into xrefMovieQuotes(MovieID,QuotesID) values (1,1)
insert into xrefMovieQuotes(MovieID,QuotesID) values (2,2)
insert into xrefMovieQuotes(MovieID,QuotesID) values (3,3)
insert into xrefMovieQuotes(MovieID,QuotesID) values (4,4)
insert into xrefMovieQuotes(MovieID,QuotesID) values (5,5) 

select * from xrefMovieQuotes

create table Actor
(
      ActorID int identity (1,1) PRIMARY KEY,
      ActorName varchar (40)
);

insert into Actor
(ActorName)
Values
('Rafiki')

insert into Actor
(ActorName)
Values
('Hiccup')

insert into Actor
(ActorName)
Values
('Will Smith')

insert into Actor
(ActorName)
Values
('Robert Downey Jr.')

insert into Actor
(ActorName)
Values
('Carrie Fisher')

select *
from Actor

create table xrefMovieActor
(
      xrefMAID int identity (1,1) Primary Key,
      MovieID int,
      ActorID int
);

insert into xrefMovieActor
(MovieID, ActorID)
Values
('1', '1')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('2', '2')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('3', '3')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('4', '4')
insert into xrefMovieActor
(MovieID, ActorID)
Values
('5', '5')

select *
from xrefMovieActor


select *
from xrefMovieActor


Select A.ActorName, M.MovieName
from xrefMovieActor xMA
inner join Actor A
      on A.ActorID = xMA.ActorID
inner join xrefActorQuote xAQ
      on A.ActorID = xAQ.ActorID
inner join Movies M
      on M.MovieID = xMA.MovieID
where MovieName = 'Men in Black 2'

select *
from Movies M
inner join xrefMovieActor xrefMA
      on M.MovieID = xrefMA.MovieID
inner join xrefMovieQuotes xrefMQ
      on xrefMA.MovieID = xrefMQ.MovieID
inner join Quotes Q
      on Q.QuoteID = xrefMQ.QuotesID
inner join Actor A
      on A.ActorID = xrefMA.ActorID
where QUOTE like '%suit%'

select a.ActorName,m.MovieName
from Actor a
inner join xrefMovieActor x on x.ActorID = a.ActorID
inner join Movies m on m.MovieID = x.MovieID
where a.ActorName = 'Will Smith'

SQL If/Case statement practice

use AdventureWorks
go

--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 SalesOrderID,
case OnlineOrderFlag
 when 0 then 'offline'
 when 1 then 'online'
 end online
from Sales.SalesOrderHeader SOD


--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 LastName, FirstName,
 case
  when TotalDue < 50000 then 'Tin Level'
  when TotalDue < 100000 then 'Silver Level'
  when TotalDue > 100000 then 'Gold Level'
 end
from Sales.SalesOrderHeader soh
 inner join Person.Contact p
  on soh.ContactID = p.ContactID
  


--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 @mode varchar (150)
set @mode = 'c' 
If @mode = 's'
 begin
 select distinct firstname, lastname
 from person.Contact pc
 inner join HumanResources.Employee hr
 on pc.ContactID = hr.ContactID
 inner join Sales.SalesOrderHeader so
 on hr.EmployeeID = so.contactID
 where so.OrderDate between '20040101' and '20041231'
 end

else if @mode = 'c'
 begin
 select distinct firstname, lastname 
 from Person.Contact pc
 inner join Sales.SalesOrderHeader so
 on pc.ContactID = so.ContactID
 where so.OrderDate between '20040101' and '20041231'
 end 

SQL If and Case statements

If Statements example:

declare @num int
set @num = 4

select @num

if @num = 3 
begin
 select 'yay...its 3'
end
else
begin
 select 'boo...no 3'
end

Case Statements example

use AdventureWorks
go

select FirstName, LastName,
 case EmailPromotion
  when 1 then 'EmailPromo'
  when 2 then 'TextPromo'
  else 'Leave Me Alone'
 end as promo
from Person.Contact