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?

Convert an integer into a decimal and a decimal into an integer in Visual Basic

To convert an integer (NoFrogs) into a decimal you would use the CDbl command.

1:  Dim NoFrogs as integer
2:  Dim TotalFrogs as Double
3:  TotalFrogs = CDbl(NoFrogs) 

To convert a decimal (NoFrogs) into an integer you would use the CInt command.

1:  Dim NoFrogs as decimal
2:  Dim TotalFrogs as Integer
3:  TotalFrogs = CInt(NoFrogs) 

convert a number variable into a string Visual Basic

To convert a number variable into a string in Visual Basic you would use the CStr (Convert to String) command;

1:  Dim NoFrogs as integer 
2:  txtBox.text = CStr(NoFrogs) 

Declaring variables in Visual Basic

To declare a variable in Visual Basic you use Dim and then set the variable to what you want, ex;

1:  Dim Frogs as String 
2:  Dim Frogs as Boolean = True
3:  Dim Frogs as integer = 4
4:  Dim Frogs as decimal = 2.6

Alter one row of a table variable

Research and test the creation of a table variable, insert 3 records into it and change one of the values of a column in only 1 row. Then select all records.

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)

select * from @Frogs

UPDATE @Frogs
SET color='green', spots=9
WHERE color='purple' AND spots=22

select * from @Frogs

Delete one record of a table variable

Research and test the creation of a table variable, insert 3 records into it and delete only one of the records. The select all records.

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)

select * from @Frogs

DELETE FROM @Frogs
WHERE id=1

select * from @Frogs

Create table variable with 3 columns and 3 rows

Research and test the creation of a table variable, insert 3 records into it, and select all records. Use 3 columns and 3 records.

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)

select * from @Frogs

1/19/11 Notes

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


mdf - data ROM
ldf - logging

two types of tables, temp tables and table variables
syntax and when to use each type

There are two types of Temp Tables, they are local and global.
Local temp tables have a single # sign as the first character in their names, they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instances of the server.
Global temp tables have a double # sign as the first character in their names, they are visible to any user after they are created, and they are deleted when all users referencing the tables have disconnected from the server.
Local temp table:

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

Global temp table:
Use DB
Create Table ##Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)

A table variable uses the @ symbol as the first character in the name and it deletes when the script you are looking at finishes running. (As long as the script takes to run)

declare @mynumber int
set @mynumber = 3
select @mynumber

Declare @Frogs table;
id int primary key identity(1,1)
, legs int not null
)
Insert into @Frogs (legs) values (@mynumber)
Insert into @Frogs (legs) values (4)

select * from @Frogs

go

First Blog Post

messagebox.show("Yay!")