Advanced Relational Database */ Outer Join Assignment w/ Variables */

1. Show all rooms for a given hotel. Show the customer name that is in the room if booked.

use YWGLH
Declare @HolidayInn int
set @HolidayInn = 1
select h.Name, r.Number, c.FirstName, c.LastName from
Rooms r
 left outer join Hotels h on h.HotelID = r.HotelID 
 left outer join Reservations re on r.RoomID = re.RoomID
 left outer join Customers c on re.CustomerID = c.CustomerID
 where h.HotelID = @HolidayInn

2. Show all rooms for a given hotel. Show the customer name that is in the room if booked.

Declare @DoubleBed varchar(50)
 Set @DoubleBed = 'DoubleBed'
Declare @Smoking varchar(50)
 Set @Smoking = 'Smoking'
select h.Name, h.Description, r.Number, r.RoomProperties, rs.StyleName from
Rooms r
 left outer join Hotels h on h.HotelID = r.HotelID
 left outer join RoomStyles rs on r.StyleID = rs.StylesID
 where rs.StyleName = @DoubleBed and r.RoomProperties = @Smoking

3. Which rooms are open for a given hotel on a given day. Order by price from cheapest to most expensive.

Declare @CheckIn datetime
 Set @CheckIn = '20110304 00:00:00'
Declare @CheckOut datetime
Set @CheckOut = '20110305 23:59:59'
select  h.Name, r.Number, re.CheckIn, re.CheckOut, ra.Cost from
Reservations re
 left outer join Rooms r on re.RoomID = r.RoomID
 left outer join Hotels h on r.HotelID = h.HotelID
 left outer join Rates ra on r.StyleID = ra.StylesID
 where re.CheckIn between @CheckIn and @CheckOut
 order by ra.Cost desc 

Programming 2 */ Arrays Assignment */

1. Create a string array that will store the days of the week. The array should fill when the form loads. Add a button (you can drag it on the form). When the button is clicked, loop through the array and pop up a message box for each day of the week.

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Num As Integer
        Dim DaysOfWeek(6) As String
        DaysOfWeek(0) = "Sunday"
        DaysOfWeek(1) = "Monday"
        DaysOfWeek(2) = "Tuesday"
        DaysOfWeek(3) = "Wednesday"
        DaysOfWeek(4) = "Thursday"
        DaysOfWeek(5) = "Friday"
        DaysOfWeek(6) = "Saturday"
        For Num = 0 To DaysOfWeek.Length - 1
            MessageBox.Show(DaysOfWeek(Num), "Days")
        Next
    End Sub

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.

Dim h As New Hashtable
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        h.Add("FirstName", TextBox1.Text)
        h.Add("LastName", TextBox2.Text)
        h.Add("Email", TextBox3.Text)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        MsgBox(h.Item("FirstName"))
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        MsgBox(h.Item("LastName"))
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        MsgBox(h.Item("Email"))
    End Sub

3. Create a form that will work as a printer queue. This form will have the following inputs:
Title of the document.
Pages to be printed.

When submitted the click event will do the following:
Add the title and pages to a hashtable.
Add the hashtable to a queue which is a class variable.
Clear the title and set the pages back to one.
Refresh the queue list.

The Clear Current button will: remove the next item in the queue and refresh the queue list box.

Dim queue As New Queue
    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim h2 As New Hashtable
        h2.Add("no", NumericUpDown1.Text)
        h2.Add("printing", TextBox4.Text)
        queue.Enqueue(h2)
        refreshbox()
    End Sub
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If queue.Count >= 1 Then
            queue.Dequeue()
            refreshbox()
        Else
            MessageBox.Show("There is nothing left to clear.", "No! YOU SUCK!!!")
        End If
    End Sub
    Private Sub refreshbox()
        ListBox1.Items.Clear()
        For Each ht As Hashtable In queue
            ListBox1.Items.Add(ht.Item("printing") & "(" & ht.Item("no") & ")")
        Next
    End Sub
End Class

Advanced Relational Database */ Subqueries */

What is a Subquery?

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.
Use of a Subquery in the Column List of a SELECT Statement
Suppose you would like to see the last OrderID and the OrderDate for the last order that was shipped to Paris. Along with that information, say you would also like to see the OrderDate for the last order shipped regardless of the ShipCity. In addition to this, you would also like to calculate the difference in days between the two different OrderDates. Here is my T-SQL SELECT statement to accomplish this:

select top 1 OrderId,convert(char(10),
       OrderDate,121) Last_Paris_Order,
      (select convert(char(10),max(OrderDate),121) from   
             Northwind.dbo.Orders) Last_OrderDate,
       datediff(dd,OrderDate,
      (select Max(OrderDate)from Northwind.dbo.Orders)) Day_Diff
from Northwind.dbo.Orders
where ShipCity = 'Paris'
order by OrderDate desc  

The above code contains two subqueries. The first subquery gets the OrderDate for the last order shipped regardless of ShipCity, and the second subquery calculates the number of days between the two different OrderDates. Here I used the first subquery to return a column value in the final result set. The second subquery was used as a parameter in a function call. This subquery passed the "max(OrderDate)" date to the DATEDIFF function.

Use of a Subquery in the WHERE clause

A subquery can be used to control the records returned from a SELECT by controlling which records pass the conditions of a WHERE clause. In this case the results of the subquery would be used on one side of a WHERE clause condition. Here is an example:
select distinct country from Northwind.dbo.Customers
where country not in (select distinct country from Northwind.dbo.Suppliers)
Here I have returned a list of countries where customers live, but there is no supplier located in that country. I suppose if you where trying to provide better delivery time to customers, then you might target these countries to look for additional suppliers.
Suppose a company would like to do some targeted marketing. This targeted marketing would contact customers in the country with the fewest number of orders. It is hoped that this targeted marketing will increase the overall sales in the targeted country. Here is an example that uses a subquery to return the customer contact information for the country with the fewest number of orders:

select Country,CompanyName, ContactName, ContactTitle, Phone
from Northwind.dbo.Customers 
where country =
   (select top 1 country 
         from Northwind.dbo.Customers C 
                      join
              Northwind.dbo.Orders O
              on C.CustomerId = O.CustomerID
    group by country
    order by count(*)) 

Here I have written a subquery that joins the Customer and Orders Tables to determine the total number of orders for each country. The subquery uses the "TOP 1" clause to return the country with the fewest number of orders. The country with the fewest number of orders is then used in the WHERE clause to determine which Customer Information will be displayed.

Use of a Subquery in the FROM clause

The FROM clause normally identifies the tables used in the T-SQL statement. You can think of each of the tables identified in the FROM clause as a set of records. Well, a subquery is just a set of records, and therefore can be used in the FROM clause just like a table. Here is an example where a subquery is used in the FROM clause of a SELECT statement:

select au_lname, au_fname, title from 
     (select au_lname, au_fname, au_id from pubs.dbo.authors
          where state = 'CA')  as a 
                     join
     pubs.dbo.titleauthor ta on a.au_id=ta.au_id
                     join
     pubs.dbo.titles t on ta.title_id = t.title_id

Here I have used a subquery to select only the author record information, if the author's record has a state column equal to "CA." I have named the set returned from this subquery with a table alias of "a." I can then use this alias elsewhere in the T-SQL statement to refer to the columns from the subquery by prefixing them with an "a", as I did in the "ON" clause of the "JOIN" criteria. Sometimes using a subquery in the FROM clause reduces the size of the set that needs to be joined. Reducing the number of records that have to be joined enhances the performance of joining rows, and therefore speeds up the overall execution of a query.

Here is an example where I used a subquery in the FROM clause of an UPDATE statement:

set nocount on 
create table x(i int identity, 
               a char(1))
insert into x values ('a')
insert into x values ('b')
insert into x values ('c')
insert into x values ('d')
select * from x

update x
set a = b.a
from (select max(a) as a from x) b 
where i > 2

select * from x
drop table x

Here I created a table named "x," that has four rows. Then I proceeded to update the rows where "i" was greater than 2 with the max value in column "a". I used a subquery in the FROM clause of the UPDATE statement to identity the max value of column "a."
Use of a Subquery in the HAVING clause
In the following example, I used a subquery to find the number of books a publisher has published where the publisher is not located in the state of California. To accomplish this I used a subquery in a HAVING clause. Here is my code:

select pub_name, count(*) bookcnt 
from pubs.dbo.titles t
         join
     pubs.dbo.publishers p
     on t.pub_id = p.pub_id
group by pub_name
having p.pub_name in     
  (select pub_name from pubs.dbo.publishers where state <> 'CA')

Here my subquery returns the pub_name values for all publishers that have a state value not equal to "CA." The HAVING condition then checks to see if the pub_name is in the set returned by my subquery.

Setting a subquery result to a variable

use AdventureWorks
delcare @ status int
set @status = (select top 1 status from Sales.SalesOrderHeader)

select @status

select * from Sales.SalesOrderHeader

This selects the top status from the table Sales.SalesOrderHeader.

Programming 2 */ Arrays */

Arrays are programming constructs that store data and allow us to access them by numeric index or subscript. Arrays helps us create shorter and simpler code in many situations. Arraysin Visual Basic .NET inherit from the Array class in the System namespace. All arrays in VB are zero based, meaning, the index of the first element is zero and they are numbered sequentially. You must specify the number of array elements by indicating the upper bound of the array. The upper bound is the numder that specifies the index of the last element of the array. Arrays are declared using Dim, ReDim, Static, Private, Public and Protected keywords. An array can have one dimension (liinear arrays) or more than one (multidimensionalarrays). The dimensionality of an array refers to the number of subscripts used to identify an individual element. In Visual Basic we can specify up to 32 dimensions. Arrays do not have fixed size in Visual Basic.

The following code demonstrates arrays.

Imports System.Console
Module Module1
Sub Main()
Dim sport(5) As String
'declaring an array
sport(0) = "Soccer"
sport(1) = "Cricket"
sport(2) = "Rugby"
sport(3) = "Aussie Rules"
sport(4) = "BasketBall"
sport(5) = "Hockey"
'storing values in the array
WriteLine("Name of the Sport in the third location" & " " & sport(2))
'displaying value from array
End Sub
End Module

You can also declare an array without specifying the number of elements on one line, you must provide values for each element when initializing the array. The following lines demonstrate that:

Dim Test() as Integer
'declaring a Test array
Test=New Integer(){1,3,5,7,9,}

Reinitializing Arrays

We can change the size of an array after creating them. The statement assigns a completely new array object to the specified array variable. You use ReDim statement to change the number of elements in an array. The following lines of code demonstrate that. This code reinitializes the Test array declared above.

Dim Test(10) as Integer
ReDim Test(25) as Integer
'Reinitializing the array

When using the Redim statement all the data contained in the array is lost. If you want to preserve existing data when reinitializing an array then you should use the Preserve keyword which looks like this:

Dim Test() as Integer={1,3,5}
'declares an array an initializes it with three members
ReDim Preserve Test(25)
'resizes the array and retains the the data in elements 0 to 2

HashTable stores a Key Value pair type collection of data . We can retrive items from hashTable to provide the key . Both key and value are Objects.

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, 
 ByVal e As System.EventArgs) Handles Button1.Click
        Dim weeks As New Hashtable
        Dim day As DictionaryEntry
        weeks.Add("1", "Sun")
        weeks.Add("2", "Mon")
        weeks.Add("3", "Tue")
        weeks.Add("4", "Wed")
        weeks.Add("5", "Thu")
        weeks.Add("6", "Fri")
        weeks.Add("7", "Sat")
        'Display a single Item
        MsgBox(weeks.Item("5"))
        'Search an Item
        If weeks.ContainsValue("Tue") Then
            MsgBox("Find")
        Else
            MsgBox("Not find")
        End If
        'remove an Item
        weeks.Remove("3")
        'Display all key value pairs
        For Each day In weeks
            MsgBox(day.Key  "  --  "  day.Value)
        Next
    End Sub
End Class

The Queue is another adtastructure from VB.NET Collections. Queue works like First In First Out method and the item added first in the Queue is first get out from Queue. We can Enqueue (add) items in Queue and we can Dequeue (remove from Queue ) or we can Peek (that is get the reference of first item added in Queue ) the item from Queue.

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object,_
 ByVal e As System.EventArgs) Handles Button1.Click
        Dim queueList As New Queue
        queueList.Enqueue("Sun")
        queueList.Enqueue("Mon")
        queueList.Enqueue("Tue")
        queueList.Enqueue("Wed")
        queueList.Enqueue("Thu")
        queueList.Enqueue("fri")
        queueList.Enqueue("Sat")
        MsgBox(queueList.Dequeue())
        MsgBox(queueList.Peek())
        If queueList.Contains("Sun") Then
            MsgBox("Contains Sun ")
        Else
            MsgBox("Not Contains Sun ")
        End If
    End Sub
End Class