--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'
Monday, February 14, 2011
Inner Join 6 tables in SQL - YummyTables
Wednesday, February 9, 2011
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
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
Friday, February 4, 2011
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
* 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
Labels:
coding button,
coding labels,
coding textbox,
visual basic
Wednesday, February 2, 2011
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.
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.
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
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
Labels:
heat problem,
socks,
Spacely's Sprockets,
visual basic,
word problem
Tuesday, February 1, 2011
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:
An if statement where it's either or:
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 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
Subscribe to:
Posts (Atom)