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