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.