Cody's Awesome Assortment of Information...

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.

Friday, February 25, 2011

Left and Right Outer Joins...Awesome

Outer joins return all rows from one of the tables in the FROM clause, this is as long as those rows meet any WHERE search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.

Below is a "Left Outer Join" example that will be explained in extraneousness detail after the code.\/\/\/

 SELECT *  
 FROM Employee   
   LEFT OUTER JOIN Jobs  
   ON Employee.PayGrade = Jobs.PayGrade

Since this is a LEFT OUTER JOIN so it will show all the "Employee" records, and all the "Jobs" records that have a "PayGrade" that is also in the same as the "Employee" "PayGrade".

Below is a "Right Outer Join" example that will be explained below the code.\/\/\/

 SELECT *  
 FROM Employee   
   RIGHT OUTER JOIN Jobs  
   ON Employee.PayGrade = Jobs.PayGrade

A RIGHT OUTER JOIN is almost exactly the same as a LEFT OUTER JOIN, but instead of having all the records from the first table stated, "Employee", it will have all the records in "Jobs" instead, showing the "Employee" records with the same "PayGrade" of "Jobs".

More To Come, CA.

Wednesday, February 23, 2011

An Awesome SQL Assignment-Movie Quotes

Some Awesome assignment, database creation, table creations, and table filling.

 create database MovieQuote  
 GO  
   
 CREATE table actor(  
      actorId int Primary Key Identity(1,1),  
      actorName varchar(50)  
 )  
   
 CREATE table quote(  
      quoteId int Primary Key Identity(1,1),  
      quoteName varchar(50)  
 )  
   
 CREATE table movie(  
      movieId int Primary Key Identity(1,1),  
      movieName varchar(50),  
      xrefActorxQuote int  
 )  
   
 CREATE table xrefActorxQuote(  
      xrefActorxQuoteId int Primary Key Identity(1,1),  
      actorId int,  
      quoteId int  
 )  
 Go  
   
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (1, 1, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (2, 7, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (3, 8, 8)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (4, 9, 9)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (5, 10, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (6, 41, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (7, 42, 10)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (8, 43, 11)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (9, 44, 12)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (10, 45, 12)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (11, 46, 17)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (12, 47, 13)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (13, 48, 14)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (14, 49, 14)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (15, 50, 15)  
   
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (1, 14, 2)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (2, 15, 2)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (3, 12, 4)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (4, 16, 4)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (5, 17, 5)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (6, 10, 1)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (7, 1, 3)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (8, 8, 3)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (9, 9, 3)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (10, 11, 4)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (11, 13, 5)  
   
 INSERT quote (quoteId, quote) VALUES (1, 'Here''s looking at you, kid.')  
 INSERT quote (quoteId, quote) VALUES (7, 'endship.')  
 INSERT quote (quoteId, quote) VALUES (8, 'Play it, Sam. Play ''As Time Goes By.')  
 INSERT quote (quoteId, quote) VALUES (9, 'Round up the usual suspects.')  
 INSERT quote (quoteId, quote) VALUES (10, 'We''ll always have Paris.')  
 INSERT quote (quoteId, quote) VALUES (41, 'Of all the gin joints in all the towns in all the world, she walks into mine.')  
 INSERT quote (quoteId, quote) VALUES (42, 'I''m going to make him an offer he can''t refuse.')  
 INSERT quote (quoteId, quote) VALUES (43, 'Frankly, my dear, I don''t give a damn.')  
 INSERT quote (quoteId, quote) VALUES (44, 'After all, tomorrow is another day!')  
 INSERT quote (quoteId, quote) VALUES (45, 'As God is my witness, I''ll never be hungry again.')  
 INSERT quote (quoteId, quote) VALUES (46, 'Plastics.')  
 INSERT quote (quoteId, quote) VALUES (47, 'Mrs. Robinson, you''re trying to seduce me... Aren''t you?')  
 INSERT quote (quoteId, quote) VALUES (48, 'Toto, I''ve got a feeling we''re not in Kansas anymore')  
 INSERT quote (quoteId, quote) VALUES (49, 'There''s no place like home.')  
 INSERT quote (quoteId, quote) VALUES (50, 'I''ll get you, my pretty, and your little dog too!')  
   
 INSERT movie (movieId, movieName) VALUES (1, 'The Godfather')  
 INSERT movie (movieId, movieName) VALUES (2, 'The Wizard of Oz')  
 INSERT movie (movieId, movieName) VALUES (3, 'Casablanca')  
 INSERT movie (movieId, movieName) VALUES (4, 'Gone with the Wind')  
 INSERT movie (movieId, movieName) VALUES (5, 'The Graduate')  
   
 INSERT actor (actorId, actorName) VALUES (1, 'Humphrey Bogart')  
 INSERT actor (actorId, actorName) VALUES (8, 'Ingrid Bergman')  
 INSERT actor (actorId, actorName) VALUES (9, 'Claude Rains')  
 INSERT actor (actorId, actorName) VALUES (10, 'Marlon Brando')  
 INSERT actor (actorId, actorName) VALUES (11, 'Clark Gable')  
 INSERT actor (actorId, actorName) VALUES (12, 'Vivien Leigh')  
 INSERT actor (actorId, actorName) VALUES (13, 'Dustin Hoffman')  
 INSERT actor (actorId, actorName) VALUES (14, 'Judy Garland')  
 INSERT actor (actorId, actorName) VALUES (15, 'Margaret Hamilton')  
 INSERT actor (actorId, actorName) VALUES (16, 'Rhett Butler')  
 INSERT actor (actorId, actorName) VALUES (17, 'Mr. Maguire')

That thing people do...

 select movieName, actorName, quote  
 From movie m  
      inner join xrefActorxMovie xam  
      on m.movieId = xam.movieId  
        
      inner join actor a  
      on xam.actorId = a.actorId  
        
      inner join xrefQuotexActor xqa  
      on a.actorId = xqa.actorId  
        
      inner join quote q  
      on xqa.quoteId = q.quoteId  
        
 Where actorName = 'Humphrey Bogart'

Another thing people do...

 Select movieName, actorName, quote  
 From dbo.movie m  
      inner join dbo.xrefActorxMovie xAM  
      on m.movieId = xAM.movieId  
   
      inner join dbo.actor a  
      on xAM.actorId = a.actorId  
   
      inner join dbo.xrefQuotexActor xQA  
      on a.actorId = xQA.actorId  
   
      inner join dbo.quote q  
      on xQA.quoteId = q.quoteId  
        
 where movieName = 'Casablanca'

The Last thing people do...

 Select actorName, quote, movieName  
 From actor a  
      inner join xrefQuotexActor xqa  
      on a.actorId = xqa.actorId  
        
      inner join quote q  
      on xqa.quoteId = q.quoteId  
        
      inner join xrefActorxMovie xam  
      on a.actorId = xam.actorId  
        
      inner join movie m  
      on xam.movieId = m.movieId  
 Where quote = 'Plastics.'

More to come, CA. Sometime.

Monday, February 14, 2011

Asp.Net Form Validation...YAY!!!

Form validation in ASP.Net is so much easier than you would think, using just two tags you can check if a text box is null...\/\/\/

 <table>  
    <tr align="right">  
       <td>Description of File: </td>  
       <td>  
         <asp:TextBox id="txtName" runat="server" />  
       </td>  
       <td align="left">  
         <asp:RequiredFieldValidator id="valTxtName" ControlToValidate="txtName" ErrorMessage="Please enter your Name!" runat="server" />  
       </td>  
     </tr>  
     <tr>  
       <td colspan="2" align="center""><asp:Button ID="add" runat="server"   
           Text="Add Old Pic" onclick="add_Click" /></td>  
     </tr>  
 </table>

As long you use the the "asp" tags, using "asp:TextBox" and set the id property of the text box, this will come into play in the "asp:RequiredFieldValidator". The asp validator can be any place on the form, and can be used to send a message if the validation fails, by still having a null value.