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.

Create a database

This will create a database when executed in a database query...

 CREATE DATABASE your_db   
 GO  

More to Come, CA.

Table Variable-The Updating

This script will show the creation of a "Table Variable", the inserting of three records into that table, and then the changing of one record. The end of the script shows all the records currently in the table, with the changed  record.

  DECLARE @varTable table(    
  Id int IDENTITY(1,1),    
  UserName VARCHAR(25),    
  Passwd VARCHAR(15)    
  )    
   
  INSERT into @varTable(UserName,Passwd)    
  Values('Jib','JibsPasswd')    
   
  INSERT into @varTable(UserName,Passwd)    
  Values('Jax','JaxsPasswd')   
    
  INSERT into @varTable(UserName,Passwd)    
  Values('Who','WhosPasswd')   
    
  SELECT *  
  From @varTable  
    
  UPDATE @varTable  
  Set Passwd='awesome'  
  Where UserName='Jib'  
    
  SELECT *  
  From @varTable  
    
  GO    
    

More to Come, CA.

Table Variable-The Deletion

Here is the creation of a "Table Variable" table, the inserting of 3 records, and the deletion of one of those records.

  DECLARE @varTable table(  
  Id int IDENTITY(1,1),  
  UserName VARCHAR(25),  
  Passwd VARCHAR(15)  
  )  
    
  INSERT into @varTable(UserName,Passwd)  
  Values('Jib','JibsPasswd')  
    
  INSERT into @varTable(UserName,Passwd)  
  Values('Jax','JaxsPasswd')  
    
  INSERT into @varTable(UserName,Passwd)  
  Values('Who','WhosPasswd')  
    
  SELECT *   
  From @varTable 

  DELETE From @varTable  
  Where id=1  
    
  SELECT *   
  From @varTable  

  GO   

More to Come, CA.

Table Vairable-The Creation

This script creates a "Table Variable", then inserted 3 records into it the table, and selects all the records from the table.

  DECLARE @varTable table(   
  Id int IDENTITY(1,1),   
  UserName VARCHAR(25),   
  Passwd VARCHAR(15)   
  )   

  INSERT into @varTable(UserName,Passwd)   
  Values('Jib','JibsPasswd')   

  INSERT into @varTable(UserName,Passwd)   
  Values('Jax','JaxsPasswd')  
 
  INSERT into @varTable(UserName,Passwd)   
  Values('Who','WhosPasswd') 
  
  SELECT *
  From @varTable   
  
  GO    

More to come, CA.

Some Usefull Linux Terminal Commands

I have a few Linux Commands that i used that are very helpful.

 #yum search <name> -> Used to search what packages are available for anything you enter in "<name>".  
 #yum install <name> -> Used to install the package that is called "<name>".  
 #rm -rf <name> -> Will remove a whole directory without give any warnings ex. [y/n]  
 #git --bare init -> I use this to create a bare git repository in my current directory  
 #git update-server-info -> I use this command to setup my git repository for remote access  
 #chown -R "name:group" <dir> -> I use this command to change the ownership of a specified directory  
 #ls -al -> This command is useful when you need to know the access information of all directories and files of the current directory  
 #service <name> restart -> This is used to restart the "<name>" service  

CA