PHP

Advanced Relational Database */ If & Case Statements Assignment */

use AdventureWorks

--1.The boss needs a quick query.  
--All he needs is a query with Sales Order Id and whether the order was online or offline.  
--Select those two columns and display either Offline or Online in the second column

select soh.SalesOrderID, soh.OnlineOrderFlag,
(case soh.OnlineOrderFlag
 when 0 then 'Offline'
 when 1 then 'Online'
End) as Status
from Sales.SalesOrderHeader soh

--2.It's time for bonuses again.  Go through all sales, listing the  firstname and lastname 
--for the salesperson, as well as the sales level.  
--less than a $50,000 sale is the Tin level
--less than a $100,000 sale is the Silver level
--Anything over $100,000 sale is the gold level

select pc.FirstName, pc.LastName, soh.TotalDue,
'Level'= case
 when soh.TotalDue < 50000 then 'Tin Level'
 when soh.TotalDue< 100000 then 'Silver Level'
 when soh.TotalDue > 100000 then 'Gold Level'
End
from Person.Contact pc
inner join Sales.SalesOrderHeader soh on pc.ContactID = soh.ContactID

--3. Using an if statement, you are going to write a query that has two modes.  
--If a variable is set to Sales mode,  it will give you a unique list of all the names of sales people in 2004.  
--If the variable is set to Customer mode, 
--it will give you a unique list of all the names of the customers in 2004.

Declare @SalesMode varchar(50)
set @SalesMode = 'c'

if @SalesMode = 's'
begin
 select distinct pc.FirstName, pc.LastName
 from Sales.SalesOrderHeader soh
 inner join Sales.SalesPerson sp on soh.SalesPersonID = sp.SalesPersonID
 inner join HumanResources.Employee hre on sp.SalesPersonID = hre.EmployeeID
 inner join Person.contact pc on hre.ContactID = pc.ContactID
 where soh.orderdate > '20040101'
end
else if @SalesMode = 'c'
begin 
 select distinct pc.FirstName, pc.LastName
 from Sales.SalesOrderHeader soh
 inner join Person.Contact pc on pc.ContactID = soh.ContactID
 where soh.orderdate between '20040101' and '20050101'
end

select * from Sales.SalesPerson
select * from Sales.SalesOrderHeader
select * from Sales.Customer
select * from Sales.CustomerAddress

Programming 2 */ Methods Assignment */

Public Class Form1

    '1. Create a single method that will be responsible for creating the place holder for each of the dice numbers.
    'a. You will likely need to pass the label in as an attribute in the method call
    '2. Create a single method that will be responsible for adding the button and 3 stats text boxes
    '3. Add a method who's job is just to roll the dice.
    '4. Add a method who's job is to collect the stats of how many there are of each number
    '5. Add a method who's job is to update the stats textboxes
    '6. Add comments to the code blocks to explain what is going on.
    'CODE:
    'Declaring variables
    Dim lblDice1, lblDice2, lblDice3, lblDice4, lblDice5 As New Label
    'To add events to the button
    Dim WithEvents butRoll As New Button
    'Declaring variables
    Dim nYatzee, nFourOfAKind, nThreeOfAKind As New Integer
    Dim lblYatzee, lblFourOfAKind, lblThreeOfAKind As New TextBox
    'Declaring random points
    Dim rnd As New Random

    'This sub is to add the dice numbering and randomization. Also used for the font and size of the label. And to add the controls.
    Private Sub addDice(ByRef lbl As Label, ByVal x As Integer, ByVal y As Integer)
        lbl.Text = 0
        lbl.Location = New Point(x, y)
        lbl.Font = New Drawing.Font("Microsoft Sans Serif", 28.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point)
        lbl.Height = 40
        lbl.Width = 40
        Me.Controls.Add(lbl)
    End Sub

    'This sub is to add the hand to the textboxes. Also for the size and to add the controls.
    Private Sub addHand(ByRef txt1 As TextBox, ByRef txt As String, ByVal x As Integer, ByVal y As Integer)
        txt1.Text = txt
        txt1.Location = New Point(x, y)
        Me.Controls.Add(txt1)
        txt1.Width = 150
    End Sub

    'This sub adds the roll button onto the form.
    Private Sub addRoll(ByRef btn As Button, ByVal txt As String, ByVal x As Integer, ByVal y As Integer)
        btn.Text = txt
        btn.Location = New Point(x, y)
        Me.Controls.Add(btn)
    End Sub

    'When the button is clicked it randomizes the numbers
    Private Sub addDi(ByVal lblDice As Label)
        lblDice.Text = rnd.Next(1, 7)
    End Sub

    Private Sub addText(ByRef txt As TextBox, ByVal txt1 As String)
        'This sub is used to add the calculation to the textboxes in the form
        txt.Text = txt1
    End Sub
    Private Sub addCalculation()
        'Declaring the variable
        Dim arrNumbers() As Integer = {0, 0, 0, 0, 0, 0}
        'For loop to randomize the numbers and determine what is a yahtzee, four of a kind, or three of a kind.
        For Each lbl As Label In Me.Controls.OfType(Of Label)()
            arrNumbers(lbl.Text - 1) += 1
        Next

        For Each i As Integer In arrNumbers
            If i = 5 Then
                nYatzee += 1
            ElseIf i = 4 Then
                nFourOfAKind += 1
            ElseIf i = 3 Then
                nThreeOfAKind += 1
            End If
        Next
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'These are linked to the subs and then added to the form1load.
        addDice(lblDice1, 10, 20)
        addDice(lblDice2, 70, 20)
        addDice(lblDice3, 130, 20)
        addDice(lblDice4, 190, 20)
        addDice(lblDice5, 250, 20)

        addHand(lblYatzee, "Yahtzees: 0", 20, 140)
        addHand(lblFourOfAKind, "Four Of A Kind: 0", 20, 180)
        addHand(lblThreeOfAKind, "Three Of A Kind: 0", 20, 220)

        addRoll(butRoll, "Roll", 100, 90)

    End Sub

    Private Sub RollDice() Handles butRoll.Click
        'all of these calculate when the button is clicked.
        addDi(lblDice1)
        addDi(lblDice2)
        addDi(lblDice3)
        addDi(lblDice4)
        addDi(lblDice5)

        addCalculation()

        addText(lblYatzee, "Yahtzee: " & nYatzee)
        addText(lblFourOfAKind, "Four Of A Kind: " & nFourOfAKind)
        addText(lblThreeOfAKind, "Three Of A Kind: " & nThreeOfAKind)

    End Sub
End Class

Advanced Relational Database */ If & Case Statements */

If and Else If Statement

use AdventureWorks

declare @num int
set @num = 3
declare @salespersonid int

select @num

if @num = 2
begin
 set @salespersonid = (select top 1 Sales.SalesPerson.SalesPersonID from Sales.SalesPerson)
end
else if @num = 4
begin
 select 'you suck'
end
else
begin
 select 'nun of the above'
end

Case Statement

select FirstName, LastName,
 (case EmailPromotion
 when 1 then 'EmailPromo'
 when 2 then 'TextPromo'
 else 'Leave me alone'
 End)
 as promo
from Person.Contact

Advanced Relational Database */ Subqueries */

use Adventureworks

--Select first name, last name, and email of employees who are sales people
select pc.FirstName, pc.LastName, pc.EmailAddress
from Person.Contact pc
 inner join HumanResources.Employee hre on pc.ContactID = hre.ContactID
 where hre.EmployeeID 
 in 
  (
  select ssp.SalesPersonID
  from Sales.SalesPerson ssp
  )

--Get emails of all sales people who have made a sale greater than $150k 
select pc.EmailAddress
from Person.Contact pc
 inner join HumanResources.Employee hre 
 on pc.ContactID = hre.ContactID
 inner join Sales.SalesPerson ssp
 on hre.EmployeeID = ssp.SalesPersonID
 where ssp.SalesPersonID 
 in 
  (
   select soh.SalesPersonID
   from Sales.SalesOrderHeader soh
   where soh.TotalDue > 150000
  )

select pp.ProductID, pp.Name, (pp.ListPrice - pp.StandardCost) Markup
from Production.Product pp
where pp.ProductID
not in
 (
  select sod.ProductID
  from Sales.SalesOrderDetail sod
 )
order by Markup desc

--The CEO wants to reward the top sales person for the previous year.  
--Write a query that will find the top sales person based on sales last year (saleslastyear).  
--Given that sales person, get their first and last name, how many years they have been around, 
--how many customers they have, and how many sales they have.


Declare @Top int
set @Top = 
(
select TOP 1 ssp.SalesPersonID
from Sales.SalesPerson ssp
order by ssp.SalesLastYear desc
)
Declare @Customer int
set @Customer =
(
select COUNT(distinct soh.CustomerID)
from Sales.SalesOrderHeader soh
where soh.SalesPersonID = @Top
)
Declare @Sales int
set @Sales =
(
select COUNT(distinct soh.SalesOrderID)
from Sales.SalesOrderHeader soh
where soh.SalesPersonID = @Top
)
select pc.FirstName, pc.LastName, ssp.SalesLastYear, DATEDIFF(YEAR, hre.HireDate, GETDATE()) YearsWorked, @Customer Customers, @Sales TotalSales
from Person.Contact pc
 inner join HumanResources.Employee hre 
 on pc.ContactID = hre.ContactID
 inner join Sales.SalesPerson ssp
 on hre.EmployeeID = ssp.SalesPersonID
 where ssp.SalesPersonID 
 in 
  (
   select @Top
   from Sales.SalesOrderHeader soh
  )
order by ssp.SalesLastYear desc

Programming 2 */ Methods */

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strCharacters, strWords, strSentences, strSpaces As String

        strCharacters = TextBox1.Text
        Dim strCharactersCounted = getCharacters(strCharacters)
        TextBox2.Text = strCharactersCounted

        strWords = TextBox1.Text
        Dim strWordsCounted = getWordCount(strWords)
        TextBox3.Text = strWordsCounted

        strSentences = TextBox1.Text
        Dim strSentencesCounted = getSentencesCount(strSentences)
        TextBox4.Text = getSentencesCount(strSentences)

        strSpaces = TextBox1.Text
        Dim strSpacesCounted = getSpacesCount(strSpaces)
        TextBox5.Text = getSpacesCount(strSpaces)

    End Sub

    Private Function getCharacters(ByVal strCharacters As String)
        Return strCharacters.Length
    End Function

    Private Function getWordCount(ByVal InputString As String) As Integer
        Return Split(System.Text.RegularExpressions.Regex.Replace(InputString, "\s+", Space(1))).Length
    End Function

    Private Function getSentencesCount(ByVal strSentences As String)
        Dim maxCount As Integer = strSentences.Length
        Dim intSent As Integer = 0
        For Each singleChar As String In strSentences
            If singleChar = "." Or singleChar = "?" Or singleChar = "!" Then
                intSent += 1
            End If
        Next

        Return intSent
    End Function

    Private Function getSpacesCount(ByVal strSpaces As String)
        Dim maxCount As Integer = strSpaces.Length
        Dim intSent As Integer = 0
        For Each singleChar As String In strSpaces
            If singleChar = " " Then
                intSent += 1
            End If
        Next

        Return intSent
    End Function
End Class

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