Cody's Awesome Assortment of Information...: March 2011

Wednesday, March 30, 2011

Example of Sub-Queries

Here is a sub query that has a sub-query in the "SELECT" clause of the main query, and another in the "FROM" Clause

use AdventureWorks

DECLARE @range datetime
set @range = '20030101'

SELECT AVG(T.Total) as Average, 
 (
  SELECT COUNT(*)
  FROM Sales.SalesOrderHeader
  WHERE OrderDate between @range and DATEADD(year,1,@range)
 ) as ORDERCOUNT
FROM ( 
  SELECT SUM(sh.TotalDue) Total, sh.CustomerID
  FROM Sales.SalesOrderHeader sh
  WHERE OrderDate between @range and DATEADD(year,1,@range)
  GROUP BY CustomerID
 ) AS t

Lets take a look at what this query actually does. Starting, I "DECLARE" a "datetime" variable, this is to make it more speedy to set the date I would like to check the year for. The main goal of this query is to show the average total for range of one year for all sales, and the "COUNT" for the year of sales done. What the first sub-query, that is located in the select clause, takes all the records from the "SalesOrderHeader" which is actually all the records from when the company started, then drops all the records that don't fall into the range, that was specified in the "SET" at the top.
The next sub-query is actally in the "FROM" clause, its actually used to make another table that takes all the records from the "SalesOrderHeader" and gets only the records in the range, same as the sub-query above. Then groups the records by there "CustomerID" this makes is so they are unique and then takes those grouped records and finds the "SUM" of the "TotalDue" column of these records. This sub-query used in the "FROM" clause makes it appear as a unique table, listing the "Total" sales of each "CustomerID" by taking this they can find out the average a person spends during the given year.

More To Come, CA.

Monday, March 28, 2011

What is a Subquery?

By the definition "Subquery or Inner query or Nested query is a query in a query."

SELECT studentName, major, studentId
FROM StudentTable
WHERE major IN 
 (SELECT major FROM MajorTable WHERE major = 'Awesome')

SELECT *
FROM (SELECT studentId FROM StudetTable WHERE studentId < 3)

declare @studentName varChar(50)
set @studentName = (SELECT top 1 studentName FROM StudentTable)

Wednesday, March 9, 2011

SQL-Hotel Reservations Assignment

Well here you go...

use Reservations  
   
 select C.name  
 from dbo.Hotel H  
 left outer join dbo.xrefHotexRoom xHR  
 on H.hotelId = xHR.hotelId  
 LEFT OUTER JOIN dbo.Room R  
 on xHR.roomId = R.roomId  
 left outer join dbo.xrefRoomxCustomer xRC  
 on R.roomId = xRC.roomId  
 left outer join dbo.Customer C  
 on xRC.customerId = C.customerId  
 where R.booked = 1

select R.roomNumber , H.hotelname  
 from Room R  
 left outer join xrefHotexRoom xHR  
 on R.roomId = xHR.roomId  
 left outer join Hotel H  
 on xHR.hotelId = H.hotelId  
 where R.bed = 1 and R.smoking = 1 and R.fridge = 0 and R.hottub = 0  
 and H.bar = 0 and H.exercise = 0 and H.pets = 0 and H.[pool] = 1 and H.restaurant = 1 and H.wireless = 1

select H.hotelname, r.roomNumber, rt.name, xHRT.rate  
 from Hotel H  
 left outer join xrefHotexRoom xHR  
 on H.hotelId = xHR.hotelId  
 left outer join Room R  
 on xHR.roomId = R.roomId  
 left outer join dbo.xrefRoomxType xRT  
 on R.roomId = xRT.roomId  
 left outer join dbo.RoomType RT  
 on xRT.typeId = RT.typeId  
 left outer join xrefHotelxRoomType xHRT  
 on xRT.typeId = xHRT.typeId AND H.hotelId = xHRT.hotelId  
 where R.booked = 0  
 order by xHRT.rate

More to Come, CA.

Friday, March 4, 2011

ASP.Net Some Example Master File Layouts

Here are three variations of example Master page layouts that I have setup in asp.net, the pages are using divs and formating to create a table like layout of the information the page. Using <div> tags and CSS formatting I think you are able to more control over your codes visual representation.

This is a one column master page with the navigation menu setup down the left side of the content column.

 <%@ Master Language="C#" AutoEventWireup="true" CodeFile="SiteMaster1.master.cs" Inherits="SiteMaster" %>  
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">  
 <head runat="server">  
   <title></title>  
   <link href="~/Styles/Master1SS.css" rel="stylesheet" type="text/css" />  
   <asp:ContentPlaceHolder ID="HeadContent" runat="server">  
   </asp:ContentPlaceHolder>  
 </head>  
 <body>  
   <form runat="server">  
   <div id="wrapper">  
     <div id="headerWrapper">  
       <div style="vertical-align: middle;">  
         <h1>Media Center of The Universe</h1>  
       </div>  
     </div>  
     <div id="contentWrapper">  
       <div class="menu">  
         <asp:Menu ID="NavigationMenu" runat="server" Orientation="Vertical" StaticDisplayLevels="2" MaximumDynamicDisplayLevels="2">   
          <LevelMenuItemStyles>   
            <asp:MenuItemStyle CssClass="menuLevel"/>  
            <asp:MenuItemStyle CssClass="menuLevel"/>  
            <asp:MenuItemStyle CssClass="menuLevel"/>  
          </LevelMenuItemStyles>   
          <Items>   
            <asp:MenuItem NavigateUrl="~/Default.aspx" Text="Home" />  
            <asp:MenuItem NavigateUrl="~/Pages/Media.aspx" Text="Media">   
              <asp:MenuItem NavigateUrl="~/Pages/Pictures.aspx" Text="Pictures">  
                <asp:MenuItem NavigateUrl="~/Pages/PicsNew.aspx" Text="New Pictures" />  
                <asp:MenuItem NavigateUrl="~/Pages/PicsOld.aspx" Text="Old Pictures" />  
                <asp:MenuItem NavigateUrl="~/Pages/PicCategories.aspx" Text="Categories" />  
              </asp:MenuItem>  
              <asp:MenuItem NavigateUrl="~/Pages/Videos.aspx" Text="Videos" />   
            </asp:MenuItem>   
            <asp:MenuItem NavigateUrl="~/Pages/About.aspx" Text="About" />   
          </Items>   
         </asp:Menu>  
       </div>  
       <div class="content">  
         <asp:ContentPlaceHolder ID="MainContent" runat="server" />  
       </div>  
       <div class="clear" />  
     </div>  
     <div id="footer">  
       Media Center of The Universe Footer  
     </div>  
   </div>  
   </form>  
 </body>  
 </html>

Here is what the above layout will look like with my CSS formatting.

Here is a layout for a two column master page with a variation in the menu placement, across the top of the two content column.

 <%@ Master Language="C#" AutoEventWireup="true" CodeFile="SiteMaster2.master.cs" Inherits="SiteMaster" %>  
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">  
 <head id="Head1" runat="server">  
   <title></title>  
   <link href="~/Styles/Master3SS.css" rel="stylesheet" type="text/css" />  
   <asp:ContentPlaceHolder ID="HeadContent" runat="server">  
   </asp:ContentPlaceHolder>  
 </head>  
 <body>  
   <form id="Form1" runat="server">  
   <div id="wrapper">  
     <div id="headerWrapper">  
       <h1>One Column Master</h1>  
     </div>  
     <div id="bodyWrapper">  
       <div class="menu">  
         <asp:Menu ID="NavigationMenu" runat="server" CssClass="menu" EnableViewState="false"  
           IncludeStyleBlock="true" Orientation="Vertical">  
           <Items>  
             <asp:MenuItem NavigateUrl="~/Default.aspx" Text="Home" />  
             <asp:MenuItem NavigateUrl="~/About.aspx" Text="About" />  
           </Items>  
         </asp:Menu>  
       </div>  
       <div id="contentWrapper">  
         <div class="content1">  
           <asp:ContentPlaceHolder ID="MainContent" runat="server" />     
         </div>  
         <div class="content2">  
           Hello  
         </div>  
         <div class="clear"></div>  
         <div id="footer">Footer</div>  
       </div>  
       <div class="clear"></div>  
     </div>  
   </div>  
   </form>  
 </body>  
 </html>

Here is what the above layout looks like.

Here is a layout for another two column master page with the menu placement next to the left side of the two content columns.

 <%@ Master Language="C#" AutoEventWireup="true" CodeFile="SiteMaster2.master.cs" Inherits="SiteMaster" %>  
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">  
 <head id="Head1" runat="server">  
   <title></title>  
   <link href="~/Styles/Master3SS.css" rel="stylesheet" type="text/css" />  
   <asp:ContentPlaceHolder ID="HeadContent" runat="server">  
   </asp:ContentPlaceHolder>  
 </head>  
 <body>  
   <form id="Form1" runat="server">  
   <div id="wrapper">  
     <div id="headerWrapper">  
       <h1>Master Page Layout 3</h1>  
     </div>  
     <div id="bodyWrapper">  
       <div class="menu">  
         <asp:Menu ID="NavigationMenu" runat="server" CssClass="menu" EnableViewState="false"  
           IncludeStyleBlock="true" Orientation="Vertical">  
           <Items>  
             <asp:MenuItem NavigateUrl="~/Default.aspx" Text="Home" />  
             <asp:MenuItem NavigateUrl="~/About.aspx" Text="About" />  
           </Items>  
         </asp:Menu>  
       </div>  
       <div id="contentWrapper">  
         <div class="content1">  
           <asp:ContentPlaceHolder ID="MainContent" runat="server" />     
         </div>  
         <div class="content2">  
           Hello  
         </div>  
         <div class="clear"></div>  
         <div id="footer">Footer</div>  
       </div>  
       <div class="clear"></div>  
     </div>  
   </div>  
   </form>  
 </body>  
 </html>

Here is the picture of the above layout.

More to Come, CA.