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.

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.

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.

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.

SQL-Ice Cream Assignment--Part 2

The sale, distributors, and inventory queries of an ice cream shop database.\/\/\/

 Select flavName, brandName, saleScoops, salePrice, saleDate  
 From dbo.iceCreamSales S  
      inner join dbo.xrefIceCreamSalesxFlavorBrand xS  
      on S.saleId = xS.salesId  
      inner join dbo.xrefIceCreamFlavorxBrand xF  
      on xS.xrefFlavId = xF.xrefFlavId  
      inner join dbo.iceCreamFlavor F  
      on xF.flavId = F.flavId  
      inner join dbo.iceCreamBrand B  
      on xF.brandId = B.brandId  
 Go

This is the sales query, showing the sale, how many scoops, what date, and when a sale of a flavor and brand has happened.

 Select tubsOnHand, flavName, brandName  
 From dbo.iceCreamInventory I  
      Inner Join dbo.xrefIceCreamInventoryxFlavorBrand xI  
      on I.invenId = xI.invenId  
      inner join dbo.xrefIceCreamFlavorxBrand xF  
      on xI.xrefFlavId = xF.xrefFlavId  
      inner join dbo.iceCreamFlavor F  
      on xF.flavId = F.flavId  
      inner join dbo.iceCreamBrand B  
      on xF.brandId = B.brandId  
 Where tubsOnHand <= 1  
 Go

This is the Inventory query, showing where there is one or less tubs left on had for all flavor and brands.

 Select distrName, flavName, brandName  
 From iceCreamDistributor D  
      Inner Join dbo.xrefIceCreamDistributorxFlavorBrand xD  
      on D.distrID= xD.distrId  
      inner join dbo.xrefIceCreamFlavorxBrand xF  
      on xD.xrefFlavId = xF.xrefFlavId  
      inner join dbo.iceCreamFlavor F  
      on xF.flavId = F.flavId  
      inner join dbo.iceCreamBrand B  
      on xF.brandId = B.brandId  
 where B.brandId=1 and F.flavId=2  
 Go  

This is the Distriubtors query, found by brand and flavor combination.

SQL-Ice Cream Assignment

The creation of a database, the creation of three tables, inserting so some records into the tables, an inner join to get the brands of a flavor from the tables using a cross reference, and another inner join to get the flavors of a brand from the tables using a cross reference.

 CREATE DATABASE ColdFusion  
 GO  
   
 USE ColdFusion  
 CREATE table iceCreamBrand(  
      brandId int Primary Key Identity(1,1),  
      brandName varchar(50)  
 )  
 CREATE table iceCreamFlavor(  
      flavorId int Primary Key Identity(1,1),  
      flavorName varchar(50)  
 )  
 CREATE table xrefIceCream(  
      flavorId int,  
      brandId int  
 )  
 Go  
   
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (1, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (2, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (3, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (4, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (5, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (6, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (7, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (8, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (9, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (10, 4)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (11, 4)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (12, 4)  
 Go  
   
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (1, N'Marshmallow Candy')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (2, N'Jelly Bean')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (3, N'Vanilla')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (4, N'Chocolate')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (5, N'Butter Pecan')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (6, N'Strawberry')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (7, N'Cookies and Cream')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (8, N'Coffee')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (9, N'Rocky Road')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (10, N'Chocolate Marshmallow')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (11, N'Cherry')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (12, N'Chocolate Almond')  
 Go  
   
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (1, N'Baskin-Robbins')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (2, N'Ben and Jerry''s')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (3, N'Gay Lea')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (4, N'Healthy Choice')  
 Go  
   
 declare @flavorName varchar(50);  
 set @flavorName = 'Vanilla';  
   
 Select brandName, flavName  
 From iceCreamBrand B  
      Inner Join xrefIceCream X  
      on B.brandId=X.brandId  
           Inner join iceCreamFlavor F  
           on F.flavID=X.flavorID  
 Where flavName=@flavorName  
 Go  
   
 declare @brandName varchar(50);  
 set @brandName = 'Baskin-Robbins';  
   
 Select flavName, brandName  
 From iceCreamFlavor F  
      Inner Join xrefIceCream X  
      on F.flavID=x.flavorId  
           inner join iceCreamBrand B  
           on B.brandID = x.brandID   
 where brandName=@brandName  
 Go  

More to come, CA.

Table Variable-Using Inner Join Select

Lets take a look at how to make two tables, that have a "Similar Column", joined together, by using a "Select" statement with an inner join...

 Declare @FamousPainters Table(  
      FP_id int PRIMARY KEY IDENTITY(1,1),  
      painterName nvarchar(25)  
 )  
   
 Declare @Paintings Table(  
      P_id int Primary Key Identity(1,1),  
      FP_id int,  
      paintingName nvarchar(35),  
      paintingYear nvarchar(15)  
 )  
   
 INSERT into @FamousPainters(painterName)    
 Values('Lenardo da Vinci')  
 INSERT into @FamousPainters(painterName)    
 Values('Michelagelo Buonarroti')  
 INSERT into @FamousPainters(painterName)    
 Values('Vincent van Gogh')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(1,'Portrait of Ginevra deBenci','1478-1480')  
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(1,'Mona Lisa','1503-1506')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Creation of Adam','Unknown')   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Libyan Sibyl','Unknown')  
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Delphic Sibyl','Unknown')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(3,'Starry Night','1889')  
   
 SELECT painterName, paintingName, paintingYear  
 FROM @FamousPainters  
   Inner Join @Paintings  
     On [@FamousPainters].FP_id=[@Paintings].FP_id  
 Order By [@FamousPainters].painterName  
   
 GO  

First I created two "Table Variables" one a list of famous painters, with just the names of the painters and an id. The id of the famous painters will be the similar column of both tables, it can really be anything you want, but I chose this because it was less writing, I could of chose the painters name as the "Similar Column". The second "Table Variable" is a list of all the paintings with a primary key, a foreign key, that will be the key that relates to the painter of the painting, the painting name, and the year the painting was painted.

I then filled the tables with data, the first with three painters, and the other table with paintings relating to the three painters. After I ran a "SELECT" statement to pull the columns "painterName, paintingName, paintingYear" from both tables. I then have to specify my main table, and then the table i will join to it. After i have to tell it "ON" as in when the column "FP_id" of my "@FamousPainters" table equals my "@Paintings" "FP_id" column they will "Share" the data in both columns. I finish up by ordering it by the painters name.

CA.

Creation of a Frog Database

Here is a query for the creation of a database, the creation of a table and the filling of a table in the database for SQL Compact Edition...\/\/\/

 CREATE DATABASE USAFrogs  
 GO  
 USE [USAFrogs]  
 GO  
   
 CREATE TABLE Alaska(  
      id int PRIMARY KEY IDENTITY(1,1),  
      scientificName nvarchar(25),  
      redListStatus nvarchar(5),  
      vernacularName nvarchar(25),  
      family nvarchar(15)  
 )  
 GO  
   
 INSERT INTO Alaska (scientificName, redListStatus, vernacularName, family)  
 Values('Bufoboreas','NT','Western Toad','Bufonidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Pseudacris','LC','Pacific Treefrog','Hylidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Rana aurora','LC','Red-legged Frog','Ranidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Rana catesbeiana','LC','Bullfrog','Ranidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Rana luteiventris','LC','Columbia Spotted Frog','Ranidae')       
 GO  

More to Come, CA.