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

SQL Union operator

data types need to be the same but columns don't have to have the same name
The union operator brings back all rows that are the same in the tables
The union all operator brings back only the rows that aren't the same.

select bid, name, tid
from boys
where name like 'B%'
union
select bid, name, tid
from boys
where name like 'J%'


select bid, name, tid
from boys
where name like 'B%'
union all
select bid, name, tid
from boys
where name like 'J%'

self join

select name, el.name
from employees e
    inner join employees e1
    on e.mid = e1.empid

Visual Basic Methods

The following is an example of how to use a sub and a function in order to reuse code.

   Public Class Form1
   
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
   Dim str1 As String = TextBox1.Text
   Dim strFormatted As String = ""
    
   strFormatted = digitsOnly(str1)
   checkNumberLength(strFormatted)
   TextBox1.Text = strFormatted
    
  End Sub
    
  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  Dim strFormatted As String = "("
  TextBox1.Text = digitsOnly(TextBox1.Text)
  checkNumberLength(TextBox1.Text)
    
  Dim x As Integer = 1
  For Each c As String In TextBox1.Text
  If x = 4 Then
  strFormatted += ") "
  End If
  If x = 7 Then
  strFormatted += "-"
  End If
    
  strFormatted += c
  x += 1
  Next
  TextBox1.Text = strFormatted
  End Sub
    
  Private Function digitsOnly(ByVal str As String) As String
  Dim strFormatted As String = ""
    
  For Each c As String In str
  If Char.IsDigit(c) Then
  strFormatted += c
  End If
  Next
  Return strFormatted
  End Function
    
  Private Sub checkNumberLength(ByVal num As String)
  If num.Length <> 10 Then
  MsgBox("Sorry, you need 10 numbers")
  End If
  End Sub
  End Class

SQL Subqueries Practice 1

1. Select firstname, lastname, and email of employees who are sales people.

use adventureworks
go

select FirstName, LastName, EmailAddress
from person.Contact p
 inner join HumanResources.Employee HRE
 on p.ContactID = HRE.ContactID
where hre.EmployeeID in
 (select distinct SalesPersonID from Sales.SalesPerson)

2. Get emails of all sales people who have made a sale greater than $150K.

select emailaddress
from person.Contact p
 inner join HumanResources.Employee HRE
  on p.ContactID = HRE.ContactID
 inner join Sales.SalesPerson SP
  on HRE.EmployeeID = SP.SalesPersonID
where SP.SalesPersonID in
 (select SalesPersonID from Sales.SalesOrderHeader sh
where sh.TotalDue > 150000)

Visual Basic Arrays Part 3 Queue and Hashtables

3. Create a form that will work as a printer queue. This form will have the following inputs:
a.Add the title and pages to a hashtable
b.Add the hashtable to a queue which is a class variable
c.Clear the title and set the pages back to one
Refresh the queue list.
d.When submitted the click event will do the following:
a.remove the next item in the queue
b.Refresh the queue list box

Public Class Form1
    Dim qJobs As New Queue

    Private Sub btnSend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSend.Click
        Dim list As New Hashtable
        list.Add("Title", txtTitle.Text)
        list.Add("Copies", NumericUpDown1.Value)
        qJobs.Enqueue(list)
        refreshjobs()
        NumericUpDown1.Value = 1
        txtTitle.Clear()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If qJobs.Count > 0 Then
            qJobs.Dequeue()
        End If
        refreshjobs()
    End Sub
    Private Sub refreshjobs()
        ListBox1.Items.Clear()
        For Each h As Hashtable In qJobs
            ListBox1.Items.Add(h.Item("Title") & h.Item("Copies"))

        Next
    End Sub

End Class

SQL Subqueries on the from clause

Subqueries on the From clause

use AdventureWorks
go


select avg(t.total)
 from (
  select sum(sh.TotalDue) total,sh.CustomerID
  from Sales.SalesOrderHeader sh
  where OrderDate between '20040101' and '20041231'
  group by CustomerID
  ) as t  

declare @range datetime
set @range = '20030101'
select AVG(a.total) averageCustSpend,
 (select COUNT(*)
  from Sales.SalesOrderHeader
  where OrderDate between @range and DATEADD(year,1,@range)
 )as count
from(
 select SUM(sh.TotalDue) total,sh.CustomerID
 from Sales.SalesOrderHeader sh
 where OrderDate between @range and DATEADD(year,1,@range)
 group by CustomerID
 )a

Hashtables in Visual Basic - Arrays Assignment Number 2

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.

 Public Class Form1  

Public Class Form1
    Dim User As New Hashtable

    Private Sub btnUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUser.Click
        User.Clear()
        User.Add("FirstName", txtFirst.Text)
        User.Add("LastName", txtLast.Text)
        User.Add("Email", txtEmail.Text)
            txtFirst.Clear()
            txtLast.Clear()
            txtEmail.Clear()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        MsgBox(User.Item("FirstName"))
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        MsgBox(User.Item("LastName"))
    End Sub

    Private Sub btnEmail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEmail.Click
        MsgBox(User.Item("Email"))
    End Sub
End Class

Inner Join Practice 2 Ice Cream table SQL

Ice cream table
Group members: Ben, Melissa, Jo

--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'


/*
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'
*/

SQL Subqueries

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.

* SELECT
* INSERT
* UPDATE
* DELETE


For Example:

1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,

SELECT first_name, last_name, subject
FROM student_details
WHERE games NOT IN ('Cricket', 'Football');

The output would be similar to:
first_name last_name subject
------------- ------------- ----------
Shekar Gowda Badminton
Priya Chandra Chess

2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below,

SELECT id, first_name
FROM student_details
WHERE first_name IN ('Rahul', 'Stephen');

but, if you do not know their names, then to get their id's you need to write the query in this manner,

SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');

Output:
id first_name
-------- -------------
100 Rahul
102 Stephen

In the above sql statement, first the inner query is processed first and then the outer query is processed.

3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table 'maths_group'.

INSERT INTO maths_group(id, name)
SELECT id, first_name || ' ' || last_name
FROM student_details WHERE subject= 'Maths'

4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101
product_name supplier_name order_id
------------------ ------------------ ----------
Television Onida 5103
Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

NOTE:
1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle.
2) If a subquery is not dependent on the outer query it is called a non-correlated subquery.

Make a playlist of artists in Visual Basic using an ArrayList

Use a textbox to add Artists to a combobox then clear it out when you get all the Artists added.

 Public Class Form1  
   Dim ArtistList As New ArrayList  
   Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click  
     'take stuff out of text box, put stuff into the combobox, clear out textbox  
     ArtistList.Add(txtArtistTitle.Text)  
     ComboBox1.DataSource = Nothing  
     ComboBox1.DataSource = ArtistList  
     txtArtistTitle.Clear()  
   End Sub  
   Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click  
     ComboBox1.Text = Nothing  
     ArtistList.Clear()  
     ComboBox1.DataSource = Nothing  
   End Sub  
 End Class  

* Add a textbox, combobox, and a button to a form.
* When you type something in the textbox, it will add the text to an arraylist. The arraylist will be defined at the class level, not in the click event.
* When the button is clicked, you also want to set your arraylist to the datasource property of the combobox.
* In order to refresh the combobox each time something is added, you need to set the datasource literally to the word Nothing and then set your datasource again to your arraylist on the next line. This will refresh the combobox contents
* Also add a button that when clicked, it will remove all the contents from the arraylist and refresh the combobox to be empty.

Visual basic arrays, arraylists, hashtables and queues.

Here are some examples of arrays, arraylists, hashtables, and queues. (From Teeps)

  Public Class frmJuke  
  Dim strDahArray(0 To 2) As String  
  Dim arrTestMyList As New ArrayList  
  Dim hashTable As New Hashtable  
  Dim qQueue As New Queue  
  Private Sub btnAddCD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnArray.Click  
   strDahArray(0) = "This is cool"  
   strDahArray(1) = "This is cooler"  
   strDahArray(2) = "This is the coolest"  
   For x As Integer = 0 To 2  
    MsgBox(strDahArray(x))  
   Next  
   lstArray.DataSource = strDahArray  
   cboArray.DataSource = strDahArray  
  End Sub  
  Private Sub btnArrayList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnArrayList.Click  
   arrTestMyList.Add("this is not cool")  
   arrTestMyList.Add("not even this is cool")  
   arrTestMyList.Add("this sux")  
   For Each Str As String In arrTestMyList  
    MsgBox(Str)  
   Next  
   lstArray.DataSource = arrTestMyList  
   cboArray.DataSource = arrTestMyList  
  End Sub  
  Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click  
   arrTestMyList.Clear()  
   For y As Integer = 0 To (strDahArray.Length - 1)  
    strDahArray(y) = ""  
   Next  
   lstArray.DataSource = Nothing  
   cboArray.DataSource = Nothing  
  End Sub  
  Private Sub btnAddToArrayList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddToArrayList.Click  
   arrTestMyList.Add(txtArrayList.Text)  
   txtArrayList.Clear()  
  End Sub  
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
   hashTable.Add("box1", "asdf")  
   hashTable.Add("box2", "jld;fkajdf;lasdf")  
   For Each obj As DictionaryEntry In hashTable  
    MsgBox(obj.Key & "=" & obj.Value)  
   Next  
  End Sub  
  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click  
   Dim str As String  
   qQueue.Enqueue("third")  
   qQueue.Enqueue("second")  
   qQueue.Enqueue("first")  
   Do While qQueue.Count > 0  
    str = qQueue.Dequeue()  
    MsgBox(str)  
   Loop  
  End Sub  
 End Class  

Arrays in VB.net

Dim arrX (0 to 2) as String 
   arrX is almost a box with 0-2 as designators (placeholders) for the numbers 0-2.
   arrX(0)="Y"
   arrX(2)="Z"

Dim arrL as New ArrayList
   arrL.add("Y")
   arrL.add("Z")

You cannot do a .add with arrX, you are stuck with the original number of placeholders that you designate.

SQL Outer Joins

You'll generally use the OUTER JOIN form that asks for all the rows from one table or result set and any matching rows from a second table or result set. To do this, you specify either a LEFT OUTER JOIN or a RIGHT OUTER JOIN. What's the difference between LEFT and RIGHT? In INNER JOIN on two tables, you name the first table, include the JOIN keyword, and then name the second table. When you begin building queries using OUTER JOIN, the SQL Standard considers the first table you name as the one on the "left," and the second table as the one on the "right." So, if you want all the rows from the first table and any matching rows from the second table, you'll use a LEFT OUTER JOIN. Conversely, if you want all the rows from the second table and any matching rows from the first table, you'll specify a RIGHT OUTER JOIN.

 SELECT Recipe_Classes.RecipeClassDescription,  
 Recipes.RecipeTitle  
 FROM Recipe_Classes  
 LEFT OUTER JOIN Recipes  
 ON Recipe_Classes.RecipeClassID =  
 Recipes.RecipeClassID  

If you do outer join and then inner join, it ignores the nulls. If you want all the information use all outer joins.

Practice 4 Visual Basic Loops

1. Put a textbox, a listbox, 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 items to the listbox on the form.

   Private Sub theButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles theButton.Click  
     Dim times As Double  
     times = CDbl(TextBox1.Text)  
     Dim i, sum As Integer  
     sum = 0  
     For i = 1 To times  
       sum = sum + 1  
       'show items in listbox  
       ListBox1.Items.Add(sum & " items")  
     Next  
   End Sub  
 End Class  

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.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     Dim pay, months, total, goal As Integer  
     Dim years As Decimal  
     pay = CInt(txtPay.Text)  
     goal = 10000  
     For total = 1 To goal  
       total += pay  
       months = months + 1  
       years = months / 12  
       Label3.Text = ("It will take " & years.ToString("f1") & " years to make the $10,000 goal.")  
     Next  
   End Sub  
 End Class  

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.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     Dim x, y As Integer  
     'Button = 20 pixels  
     x = 1  
     Button1.Visible = False  
     For x = 1 To 10  
       For y = 1 To 10  
         Dim newbutton As New Button  
         newbutton.Location = New Point(50 * x, 50 * y)  
         newbutton.Width = 40  
         newbutton.Text = x * y  
         Me.Controls.Add(newbutton)  
       Next  
     Next  
   End Sub  
 End Class  

A loop within a loop operates in the inside loop until that loop is finished, then goes into the outer loop to get to the next level to run the inner loop again till it is finished.

SQL Inner Join Movie Databases

Inner Join Practice 3

Movie Quote Database.
You are one of those weird people who thinks in movie quotes. If it wasn't for movies, you would probably never speak at all. It's time to capture that master mind of yours and put it into electronic form. Create a database which will store movie quotes, the movie it is heard in, and the actor who said it.
•Find all the quotes for a particular movie and list the actor/actress - Jo

•Find a specific movie and actor/actress by entering in a phrase from a quote - Ben

•Find all the movies and quotes that were uttered by a particular actor/actress - Melissa

You should be able to find the following:
This database should be in 3rd normal form. Always be thinking: "If this information were to change, how many places would I have to make that change?" This doesn't apply for primary keys since they never change.

 --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'  

VB notes 2-18-11 Loops and Do Whiles

To count the number of check boxes checked on the form. 3 Check boxes on the form.

Dim nCount as integer = 0
for each element as object in me.controls
     If TypeOf element Is CheckBox Then
          If CType(element, CheckBox).Checked = True Then
               nCount +=1
          End If
     End If   
Next
txtCount.Text = nCount

Dim nControls As Integer = me.Controls.Count
Dim nCurrent As Integer = 0
Dim obj As Object
Do While nCurrent < nControls

     obj = Me.Controls.Item(nCurrent)
     If TypeOf ojb Is CheckBox Then
          If CType(obj, CheckBox).Checked Then
               nCount +=1
          End If
     End If
     nCurrent +=1
     txtCount.Text = nCount
     If nCount > 1 Then 
          Exit Do
Loop

Web Design Notes 2-16-11

Classes are reusable, id's are not.

You must use a
for a different paragraph or different line.

Dreamweaver double spaces paragraphs automatically.

Become

Span tag on it's own does nothing.

External, internal, imbedded - three ways to do styles in a webpage.

Screen readers use the strong tag for bold text.

Inner Join 6 tables in SQL - YummyTables

 --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'  

Random comments

Each object has methods, properties and events.

Ctrl KC = comment out lines
Ctrl KU = un-comment lines

Do (while first)-
x = 1
Do while x < 10
x += 1
Loop

Do (while last) -
x = 1
Do while x < 10
x += 1
Loop while x < 10

For -
Dim n as Integer
for n = 2 to 10 step 2 - counts by 2, you can also do -2 to count backwards

Next

For each

While x < 10

End while

Inner Join 3 tables in SQL

 --drop table Brand  
 --drop table Flavor  
 --drop table xrefFlavorBrand  
 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)  
    )  
    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 code for controls in Visual Basic

# Research and blog the following:

* add a button to a page programmatically
* add a textbox to a page programmatically
* add a label to a page programmatically
* add an item list to a page programmatically
* understand how to place these in a certain location on the form
* understand how to change the width and height
* understand how to set readonly and disabled
* understand how to set the text value

 Public Class Form1  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
     ' create button  
     Dim btn As New Button  
     ' set some button properties  
     btn.Text = "go"  
     btn.Top = 45  
     btn.Left = 190  
     'add button to form  
     Me.Controls.Add(btn)  
     'create textbox  
     Dim dynamicText As TextBox = Nothing  
     dynamicText = New Windows.Forms.TextBox  
     dynamicText.Name = "TimeTextBox"  
     dynamicText.Location = New System.Drawing.Point(8, 8)  
     dynamicText.Size = New System.Drawing.Size(232, 20)  
     dynamicText.TabIndex = 0  
     Me.Controls.Add(dynamicText)  
     'create label  
     Dim lbl As New Label  
     lbl.SetBounds(10, 50, 100, 25)  
     lbl.Text = "Hello World!"  
     Me.Controls.Add(lbl)  
     'create listbox  
     Dim lstEmails As ListBox  
     lstEmails = New System.Windows.Forms.ListBox()  
     '  
     'lstEmails  
     '  
     lstEmails.IntegralHeight = False  
     lstEmails.Location = New Point(16, 75)  
     lstEmails.Name = "lstEmails"  
     lstEmails.Size = New System.Drawing.Size(264, 224)  
     lstEmails.TabIndex = 0  
     Me.Controls.Add(lstEmails)  
     '  
   End Sub  
 End Class  

Word Problems 2 Spacely's Sprockets, Heat problem, Socks

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.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim purchase, shipping, total As Double  
     purchase = CDbl(txtpurchase.text)  
     shipping = 5  
     'Calculate total  
     If purchase >= 50 Then  
       total = FormatCurrency(purchase)  
     ElseIf purchase < 50 Then  
       total = purchase + shipping  
     End If  
     'Display total  
     MessageBox.Show("Your total is: " & FormatCurrency(total), "Please Pay")  
   End Sub  
 End Class  

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.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim temp As Double  
     temp = CDbl(txtpurchase.Text)  
     'Calculate total  
     If temp <= 72 Then  
       MessageBox.Show("The heat is on.", "Heat")  
     ElseIf temp > 72 And temp < 76 Then  
       MessageBox.Show("The system is idle", "Idle System")  
     ElseIf temp >= 76 Then  
       MessageBox.Show("The A/C is on.", "A/C")  
     End If  
   End Sub  
 End Class  

3.) 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:
a.0-2 yrs -XS
b.3-4 yrs -S
c.5-8 yrs -M
d.9-12 yrs -L
e.13+ yrs -XL

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim age As Double  
     age = CDbl(txtpurchase.Text)  
     'Calculate total  
     If age <= 2 Then  
       MessageBox.Show("Your child's size is XS.", "XS")  
     ElseIf age >= 3 And age <= 4 Then  
       MessageBox.Show("Your child's size is Small", "Small")  
     ElseIf age >= 5 And age <= 8 Then  
       MessageBox.Show("Your child's size is Medium", "Medium")  
     ElseIf age >= 9 And age <= 12 Then  
       MessageBox.Show("Your child's size is Large", "Large")  
     ElseIf age >= 13 Then  
       MessageBox.Show("Your child's size is X-Large.", "X-Large")  
     End If  
   End Sub  
 End Class  

Case Statement in Visual Basic

A case statement in Visual Basic:

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim num As Double  
     Select Case num  
       Case 5  
         txtResults.Text = "case 1"  
       Case 5 To 7  
         txtResults.Text = "case 2"  
       Case 7 To 12  
         txtResults.Text = "case 3"  
     End Select  
   End Sub  
 End Class  

If Statements in Visual Basic

An if statement that makes one decision:

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim num1, num2, largerNum As Double  
     'Convert text box to double  
     num1 = CDbl(txtFirstNum.Text)  
     num2 = CDbl(txtSecondNum.Text)  
     'Find results  
     If num1 > num2 Then  
       largerNum = num1  
     Else  
       largerNum = num2  
     End If  
     'Display results  
     txtResults.Text = "The larger number is " & largerNum & "."  
   End Sub  
 End Class  

An if statement where it's either or:

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim message As String  
     message = "On a bad day, I have mood swings - but on a good day, I have the whole mood playground – Charles Rosenblum"  
     If txtmessage.Text.ToUpper = "Y" Then  
       MessageBox.Show(message, "Quote")  
     End If  
     MessageBox.Show("At first, I only laughed at myself. Then I noticed that life itself is amusing. I've been in a generally good mood ever since. – Marilyn vos Savant", "Bad Mood Quote")  
   End Sub  
 End Class  

An if statement where many decisions are made:

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim gpa As Double = CDbl(txtGpa.Text)  
     Dim honors As String  
     If gpa >= 3.9 Then  
       honors = " summa cum laude."  
     ElseIf gpa >= 3.6 Then  
       honors = " magna cum laude."  
     ElseIf gpa >= 3.3 Then  
       honors = " cum laude."  
     ElseIf gpa >= 2 Then  
       honors = "."  
     End If  
     txtResults.Text = "You graduated" & honors  
   End Sub  
 End Class  

Create inner join on 2 tables in SQL

Blog post: Research and test the creation of 2 table variables, insert 3 records into each, both having a column in common, and select all records from both tables by joining them together. You can use a join or an inner join for this.

 Declare @Frogs table  
 (  
 id int primary key identity(1,1) not null  
 , legs int  
 , color varchar(36)  
 , spots int  
 )  
 Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)  
 Insert into @Frogs (legs, color, spots) values (4, 'red', 7)  
 Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)  
 declare @Ponds table  
 (  
 pid int primary key identity (1,1) not null  
 , id int  
 , location varchar (30)  
 , color varchar (20)  
 , weeds varchar (30)  
 )  
 insert into @Ponds (id, location, color, weeds) values (1, 'Fargo', 'blue', 'Full')  
 insert into @Ponds (id, location, color, weeds) values (2, 'Grand Forks', 'green', 'Half full')  
 insert into @Ponds (id, location, color, weeds) values (3, 'Hillsboro', 'blue-green', 'Quarter full')  
 select f.legs  
      ,f.color  
      ,p.location  
 from @Ponds p  
    inner join @Frogs f  
      on f.id=p.id  
    order by f.id  

Word Problem 6 - Quiz scores

a.) Given the table of quiz marks out of 25 in each subject calculate How many marks did Brian totally obtain in Mathematics and Science?
Write a program in Visual Basic to calculate the marks Brian obtained in Mathematics and Science.

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim M, S, marks As Integer  
     'Set values  
     M = 7  'Marks in Mathmetics
     S = 22  'Marks in Science
     marks = M + S  
     'Display results  
     MessageBox.Show("Brian has " & marks & " marks in Mathematics and Science.")  
   End Sub  
 End Class  

b.) How many more marks does Andrew need for a perfect score in Mathematics?

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim M, S, marks As Integer  
     'Set values  
     M = 18  'Brian's actual marks
     S = 25 'total possible
     marks = S - M  
     'Display results  
     MessageBox.Show("Brian needs " & marks & " marks for a perfect score in Mathematics.")  
   End Sub  
 End Class  

c.) What is Andrew's percentage for all of the quizzes together?

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim M, S, En, G As Integer  
     Dim marks As Decimal  
     'Set values  
     M = 18 'marks in Mathematics  
     S = 7 'marks in Science  
     En = 12 'marks in English  
     G = 15 'marks in Geography  
     marks = (S + M + En + G) / 100  
     'Display results  
     MessageBox.Show("Brian has " & FormatPercent(marks) & " marks for all the quizzes together.")  
   End Sub  
 End Class  

Word Problem 5 - Inglebert's Apples and Oranges with variable

Inglebert has 15 apples and 3 times as many oranges. How may pieces of fruit does she have?
Create a program in Visual Basic to calculate the number of pieces of fruit that Inglebert has. Enter the initial value for apples in a text box.

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim A, O, pieces As Integer  
     'Set values  
     A = txtInitial.Text  
     O = A * 3  
     pieces = A + O  
     'Display results  
     MessageBox.Show("Inglebert has " & pieces & " pieces of fruit.")  
   End Sub  
 End Class  

Word Problem 5 - Inglebert's Apples and Oranges

Inglebert has 15 apples and 3 times as many oranges. How many pieces of fruit does she have?
Create a program in Visual Basic to calculate the number of pieces of fruit Inglebert has with the given numbers.

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim A, O, pieces As Integer  
     'Set values  
     A = 15  
     O = A * 3  
     pieces = A + O  
     'Display results  
     MessageBox.Show("Inglebert has " & pieces & " pieces of fruit.")  
   End Sub  
 End Class  

Word Problem 4 - Horses in the field part 2

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?
Write a program in Visual Basic to find out how many horses are standing in the field.
Gather the initial value in a text box.

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  

     'Declare variables  

     Dim I, O, B, horses As Integer  

     'Set values  

     O = 15  

     I = txtInitial.Text  

     B = 7  

     horses = I - O + B  

     'Display results  

     MessageBox.Show("There are " & horses & " horses in the field.")  

   End Sub  

 End Class  

Word Problem 4 - Horses in the field

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?
Write a program in Visual Basic to find out how many horses are standing in the field.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim I, O, B, horses As Integer  
     'Set values  
     O = 33  
     I = 15  
     B = 7  
     horses = O - I + B  
     'Display results  
     MessageBox.Show("There are " & horses & " horses in the field.")  
   End Sub  
 End Class  

Word Problem 3 - Apples and Oranges

Ole has 15 apples and 12 oranges. How many pieces of fruit does he have?

Write a program in Visual Basic to display the number of pieces of fruit Ole has.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim A, O, fruit As Integer  
     'Set values  
     A = 15  
     O = 12  
     fruit = A + O  
     'Display results  
     MessageBox.Show("Ole has " & fruit & " pieces of fruit.")  
   End Sub  
 End Class  

Word Problem 2 - Mega Burgers in Visual Basic

Diane bought 7 Mega Burgers. Each Mega Burger cost $4. How many dollars did she spend on the Mega Burgers?

Write a program to display how much the Mega Burgers cost in Visual Basic.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim MB, Cost, TotalPrice As Integer  
     'Set values  
     MB = 7  
     Cost = 4  
     TotalPrice = MB * Cost  
     'Display results  
     MessageBox.Show("The Mega Burgers cost " & FormatCurrency(TotalPrice))  
   End Sub  
 End Class  

Word Problem 1 - Stuffed Animals in Visual Basic

Morgan loves collecting small stuffed animals. She has 6 cows and 7 sheep. How many animals does she have in her collection?
Write a program in Visual Basic to display the number of stuffed animals in a message box.

 Public Class Form1  
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
     'Declare variables  
     Dim C, S, TA As Integer  
     'Set values  
     C = 6  
     S = 7  
     TA = C + S  
     'Display results  
     MessageBox.Show("Morgan has " & TA & " stuffed animals.")  
   End Sub  
 End Class  

Web Design Notes 1-26-11

My heading

= heading size 1-6

paragraph
or = bold, or = italic = underline







style sheets - list of all your font sizes and settings that the rest of your website refers to.
h1 {color: red;} = makes all h1 text red
h1 is the selector color is the property and red is the value

The three types of styles are Inline Styles, Embedded Styles, and External Styles
External Style is generally the best one to work with. p{font-size: 12 px;} in the styles.css style sheet. External sheet is being accessed.
Inline Styles: allows you to override an embedded or external style rule.


= commented out

This text should be in blue.

Anything you don't see on the webpage should be in the header section.



style>


; signals the end of a rule.

font-family: Arial, Helvetica, sans-serif; } = order in which fonts are displayed if not Arial then Helvetica then sans-serif...

Inline styles will override others.

Welcome to my page

= heading on the page in bold font

h2 { border: 1px solid green; width: 150px; } = heading on page will have a solid green border 1 px wide, the box will be 150 px wide on the page.

Links to css sheets should go in the header section in html sheet


= link relation - type of doc - name of file type of file

Style classes allow changes from style sheets or defaults.

p.red{ color: red; } = will change font in paragraph to red
p.blue{ color: blue; }
p.green{ color: green; }

The span tag on it's own does nothing, but you can use it to make classes.

This text should be in blue.

= makes "should" highlighted if in the style sheet.

.border { 2px dashed black; } = general class to make border in css sheet

Classes are reusable, id's are not reusable.
ID's use #

h1, h2, h3, h4, h5, h6 { color: brown; } = Multiple Selector, will make every h text brown will be the same as
h1 { color: brown; }
h2 { color: brown; }
h3 { color: brown; }

Contextual Selectors
h2 h5 { color: brown; }


b i { background-color: burlywood; } = will only give you the background color if there is italic command in bold command

Creating a database and table in SQL

 --drop database CoFrogs 
create database CoFrogs 
go 
use CoFrogs 
create table Colorado 
( 
     [id] int identity(1,1) not null primary key 
     ,[ScientificName] varchar(50) 
     ,[IUCNRedListStatus] varchar(20) 
     ,[VernacularName] varchar(50) 
     ,[Family] varchar(30) 
) 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo Boreas' 
     ,'Near Threatened' 
     ,'Western Toad' 
     ,'Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo Cognatus' 
     ,'Least Concern' 
     ,'Great Plains' 
     ,'Toad     Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo debilis' 
     ,'Least Concern' 
     ,'Green Toad' 
     ,'Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo punctatus' 
     ,'Least Concern' 
     ,'Red-Spotted Toad' 
     ,'Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo woodhousii' 
     ,'Least Concern' 
     ,'Woodhouses Toad' 
     ,'Bufonidae') 
select * from Colorado 

Joining a string to another string, also known as string concatenation, in Visual Basic

To join a string to another string, also known as string concatenation in Visual Basic you would use the & between strings in double quotes.

1:  Dim quote1 as string
2:  Dim part1 as string = "How many legs "
3:  Dim part2 as string = "does that frog have?"
4:  quote1 = part1 & part2

which produces the output
How many legs does that frog have?