SQL Reservations Database queries

use FunHotelz
go
--Show all rooms for a given hotel. Show the customer name that is in the room if booked.

select r.RoomNum, h.HotelName, c.CustFirstName, c.CustLastName
from Room r
left outer join xrefRoomCust xRC
on r.RoomID = xRC.RoomID
left outer join Hotel h
on r.HotelID = h.HotelID
left outer join Customer c
on xRC.CustID = c.CustID
where CustLastName = 'Hummer'

--Search for an available room with different options. (Room: # beds, smoking, fridge, hot tub.)

select r.roomNum, h.HotelName, r.NumBeds, r.Smoking, r.HotTub, xRC.DOA
from Room r
left outer join Hotel h
on r.HotelID = h.HotelID
left outer join xrefRoomCust xRC
on r.RoomID = xRC.RoomID
where r.NumBeds = 1
and xRC.DOA is null

SQL If/Case statement practice

use AdventureWorks
go

--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 SalesOrderID,
case OnlineOrderFlag
 when 0 then 'offline'
 when 1 then 'online'
 end online
from Sales.SalesOrderHeader SOD


--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 LastName, FirstName,
 case
  when TotalDue < 50000 then 'Tin Level'
  when TotalDue < 100000 then 'Silver Level'
  when TotalDue > 100000 then 'Gold Level'
 end
from Sales.SalesOrderHeader soh
 inner join Person.Contact p
  on soh.ContactID = p.ContactID
  


--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 @mode varchar (150)
set @mode = 'c' 
If @mode = 's'
 begin
 select distinct firstname, lastname
 from person.Contact pc
 inner join HumanResources.Employee hr
 on pc.ContactID = hr.ContactID
 inner join Sales.SalesOrderHeader so
 on hr.EmployeeID = so.contactID
 where so.OrderDate between '20040101' and '20041231'
 end

else if @mode = 'c'
 begin
 select distinct firstname, lastname 
 from Person.Contact pc
 inner join Sales.SalesOrderHeader so
 on pc.ContactID = so.ContactID
 where so.OrderDate between '20040101' and '20041231'
 end 

SQL If and Case statements

If Statements example:

declare @num int
set @num = 4

select @num

if @num = 3 
begin
 select 'yay...its 3'
end
else
begin
 select 'boo...no 3'
end

Case Statements example

use AdventureWorks
go

select FirstName, LastName,
 case EmailPromotion
  when 1 then 'EmailPromo'
  when 2 then 'TextPromo'
  else 'Leave Me Alone'
 end as promo
from Person.Contact

SQL Union operator

data types need to be the same but columns don't have to have the same name
The union operator brings back all rows that are the same in the tables
The union all operator brings back only the rows that aren't the same.

select bid, name, tid
from boys
where name like 'B%'
union
select bid, name, tid
from boys
where name like 'J%'


select bid, name, tid
from boys
where name like 'B%'
union all
select bid, name, tid
from boys
where name like 'J%'

self join

select name, el.name
from employees e
    inner join employees e1
    on e.mid = e1.empid

SQL Subqueries Practice 1

1. Select firstname, lastname, and email of employees who are sales people.

use adventureworks
go

select FirstName, LastName, EmailAddress
from person.Contact p
 inner join HumanResources.Employee HRE
 on p.ContactID = HRE.ContactID
where hre.EmployeeID in
 (select distinct SalesPersonID from Sales.SalesPerson)

2. Get emails of all sales people who have made a sale greater than $150K.

select emailaddress
from person.Contact p
 inner join HumanResources.Employee HRE
  on p.ContactID = HRE.ContactID
 inner join Sales.SalesPerson SP
  on HRE.EmployeeID = SP.SalesPersonID
where SP.SalesPersonID in
 (select SalesPersonID from Sales.SalesOrderHeader sh
where sh.TotalDue > 150000)

SQL Subqueries on the from clause

Subqueries on the From clause

use AdventureWorks
go


select avg(t.total)
 from (
  select sum(sh.TotalDue) total,sh.CustomerID
  from Sales.SalesOrderHeader sh
  where OrderDate between '20040101' and '20041231'
  group by CustomerID
  ) as t  

declare @range datetime
set @range = '20030101'
select AVG(a.total) averageCustSpend,
 (select COUNT(*)
  from Sales.SalesOrderHeader
  where OrderDate between @range and DATEADD(year,1,@range)
 )as count
from(
 select SUM(sh.TotalDue) total,sh.CustomerID
 from Sales.SalesOrderHeader sh
 where OrderDate between @range and DATEADD(year,1,@range)
 group by CustomerID
 )a

Inner Join Practice 2 Ice Cream table SQL

Ice cream table
Group members: Ben, Melissa, Jo

--drop table Brand
--drop table Flavor
--drop table xrefFlavorBrand
--drop table sales
--drop table distributors
--drop table xrefDistFlavorBrand
--drop table xrefDistSales

use Yummys
go

create table Brand
(
 BrandID int identity(1,1) primary key
 ,BrandName varchar(50)
 ,BrandLocation varchar(50)
)
insert into Brand (BrandName, BrandLocation) values ('BlueBunny', 'San Francisco')
insert into Brand (BrandName, BrandLocation) values ('PurpleMonkey', 'Detroit')
insert into Brand (BrandName, BrandLocation) values ('PinkHippo', 'Malibu')
insert into Brand (BrandName, BrandLocation) values ('GreenRabbit', 'Tucson')
insert into Brand (BrandName, BrandLocation) values ('RainbowLamb', 'Minneapolis')

select * 
from Brand

create table flavor
      (
      Flavor_ID int identity (1,1) PRIMARY KEY,
      flavor_name varchar (40),
      descraption varchar (100),
      quantity int
      )

      insert into flavor
      (flavor_name, descraption, quantity)
      
      values
      ('choclate', 'classic choclate', 2)
      
      insert into flavor
      (flavor_name, descraption, quantity)
      
      values
      ('Vanilla', 'Plain Vanilla', 4)
      
      insert into flavor
      (flavor_name, descraption, quantity)
      
      Values
      ('Neopolatin', '1/3 Choclate, 1/3 Vanilla. 1/3 Strawberry', 1)
      
      insert into flavor
      (flavor_name, descraption, quantity)
      
      Values
      ('Coffee', 'Fresh Cround Coffee Flavor', 0)
      
      insert into flavor
      (flavor_name, descraption, quantity)
      
      Values
      ('Sardine', 'Self-Explanatory', 7)
      
      
select *
from flavor

create table xrefFlavorBrand
(
      xrefID int identity (1,1) primary key
      , BrandID int
      , FlavorID int
)

Insert into xrefFlavorBrand (BrandID, FlavorID) values (1,1)
Insert into xrefFlavorBrand (BrandID, FlavorID) values (2,2)
Insert into xrefFlavorBrand (BrandID, FlavorID) values (3,3)
Insert into xrefFlavorBrand (BrandID, FlavorID) values (4,4)
insert into xrefFlavorBrand (BrandID, FlavorID) values (5,5)


create table sales
(
 SalesID int identity (1,1) primary key
 , xrefID int
 , ScoopNo int
 , Price decimal(4,2)
 , SalesDate int
 )
 
insert into sales
 (xrefID, ScoopNo, Price, SalesDate) values (1, 2, 2.00, 1/2/10)
insert into sales 
 (xrefID, ScoopNo, Price, SalesDate) values (2, 3, 3.00, 1/3/10)
insert into sales
 (xrefID, ScoopNo, Price, SalesDate) values (3, 2, 2.00, 1/3/10)
insert into sales
 (xrefID, ScoopNo, Price, SalesDate) values (2,3,3.00, 1/3/10)
insert into sales
 (xrefID, ScoopNo, Price, SalesDate) values (4,2,2.00, 1/4/10)
insert into sales
 (xrefID, ScoopNo, Price, SalesDate) values (5, 4, 4.00, 1/4/10)
insert into sales
 (xrefID, ScoopNo, Price, SalesDate) values (1, 5, 5.00, 1/5/10) 
insert into sales
 (xrefID, ScoopNo, Price, SalesDate) values (4, 3, 3.00, 1/5/10) 
 
select * from sales

create table xrefDistFlavorBrand
(
            xrefDistSalesID  int identity (1,1) primary key
            ,BrandID int
            ,FlavorID int
            ,distributor_ID  int
            
)
Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(1,1,1)
Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(2,2,2)
Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(3,3,3)
insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(4,4,4)
insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values (5,5,5)

select * 
from xrefDistFlavorBrand


--here is the second table

create table xrefDistSales
(
            xrefDistSalesID  int identity (1,1) primary key
            ,BrandID int
            ,SalesID int            
)

insert into xrefDistSales(BrandID,SalesID) values (1,1)
insert into xrefDistSales(BrandID,SalesID) values (2,2)
insert into xrefDistSales(BrandID,SalesID) values (3,3)
insert into xrefDistSales(BrandID,SalesID) values (4,4)
insert into xrefDistSales(BrandID,SalesID) values (5,5)

select *
from xrefDistSales


create table distributors
      (
            distributor_ID int identity (1,1) PRIMARY KEY,
            DistributorName varchar (30)
      );
      
insert into distributors
(DistributorName)

Values
('ND Icecream')

insert into distributors
(DistributorName)

Values
('GF Grocery')

insert into distributors
(DistributorName)

Values
('Henreys Foods')

insert into distributors
(DistributorName)

Values
('Mayville Grocery')

insert into distributors
(DistributorName)

Values
('Red River Valley Icecream')

select *
from distributors

--keep track of the number of tubs of ice cream on hand
Select * 
from flavor FL
inner join xrefDistFlavorBrand xDFB
on FL.Flavor_ID = xDFB.FlavorID
where quantity <=1

--search distributors by flavor and brand, get a list of distributors
select * 
from distributors D
inner join xrefDistFlavorBrand xDFB
on d.Distributor_ID = xDFB.Distributor_ID
inner join flavor F
on f.Flavor_ID = xDFB.FlavorID
inner join Brand B
on B.BrandID = xDFB.BrandID
where flavor_name = 'choclate'
and
BrandName = 'BlueBunny'

--Keep track of which flavors and brands were sold
select * 
from sales S
inner join xrefDistSales xDS
on s.SalesID = xDS.SalesID
inner join xrefDistFlavorBrand xDFB
on xDS.xrefDistSalesID = xDFB.xrefDistSalesID
inner join flavor F
on f.Flavor_ID = xDFB.FlavorID
where f.flavor_name = 'vanilla'


/*
select *
from xrefFlavorBrand

select FL.*, xFB.BrandID, B.BrandName
from flavor FL 
inner join xrefFlavorBrand xFB
on FL.Flavor_ID = xFB.xrefID
inner join Brand B
on xFB.BrandID = B.BrandID
where FL.flavor_name = 'Coffee'
*/

SQL Subqueries

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.

* SELECT
* INSERT
* UPDATE
* DELETE


For Example:

1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,

SELECT first_name, last_name, subject
FROM student_details
WHERE games NOT IN ('Cricket', 'Football');

The output would be similar to:
first_name last_name subject
------------- ------------- ----------
Shekar Gowda Badminton
Priya Chandra Chess

2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below,

SELECT id, first_name
FROM student_details
WHERE first_name IN ('Rahul', 'Stephen');

but, if you do not know their names, then to get their id's you need to write the query in this manner,

SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');

Output:
id first_name
-------- -------------
100 Rahul
102 Stephen

In the above sql statement, first the inner query is processed first and then the outer query is processed.

3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table 'maths_group'.

INSERT INTO maths_group(id, name)
SELECT id, first_name || ' ' || last_name
FROM student_details WHERE subject= 'Maths'

4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101
product_name supplier_name order_id
------------------ ------------------ ----------
Television Onida 5103
Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

NOTE:
1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle.
2) If a subquery is not dependent on the outer query it is called a non-correlated subquery.

SQL Outer Joins

You'll generally use the OUTER JOIN form that asks for all the rows from one table or result set and any matching rows from a second table or result set. To do this, you specify either a LEFT OUTER JOIN or a RIGHT OUTER JOIN. What's the difference between LEFT and RIGHT? In INNER JOIN on two tables, you name the first table, include the JOIN keyword, and then name the second table. When you begin building queries using OUTER JOIN, the SQL Standard considers the first table you name as the one on the "left," and the second table as the one on the "right." So, if you want all the rows from the first table and any matching rows from the second table, you'll use a LEFT OUTER JOIN. Conversely, if you want all the rows from the second table and any matching rows from the first table, you'll specify a RIGHT OUTER JOIN.

 SELECT Recipe_Classes.RecipeClassDescription,  
 Recipes.RecipeTitle  
 FROM Recipe_Classes  
 LEFT OUTER JOIN Recipes  
 ON Recipe_Classes.RecipeClassID =  
 Recipes.RecipeClassID  

If you do outer join and then inner join, it ignores the nulls. If you want all the information use all outer joins.

Inner Join 6 tables in SQL - YummyTables

 --drop table Brand  
 --drop table Flavor  
 --drop table xrefFlavorBrand  
 --drop table sales  
 --drop table distributors  
 --drop table xrefDistFlavorBrand  
 --drop table xrefDistSales  
 use Yummys  
 go  
 create table Brand  
 (  
      BrandID int identity(1,1) primary key  
      ,BrandName varchar(50)  
      ,BrandLocation varchar(50)  
 )  
 insert into Brand (BrandName, BrandLocation) values ('BlueBunny', 'San Francisco')  
 insert into Brand (BrandName, BrandLocation) values ('PurpleMonkey', 'Detroit')  
 insert into Brand (BrandName, BrandLocation) values ('PinkHippo', 'Malibu')  
 insert into Brand (BrandName, BrandLocation) values ('GreenRabbit', 'Tucson')  
 insert into Brand (BrandName, BrandLocation) values ('RainbowLamb', 'Minneapolis')  
 select *   
 from Brand  
 create table flavor  
    (  
    Flavor_ID int identity (1,1) PRIMARY KEY,  
    flavor_name varchar (40),  
    descraption varchar (100),  
    quantity int  
    )  
    insert into flavor  
    (flavor_name, descraption, quantity)  
    values  
    ('choclate', 'classic choclate', 2)  
    insert into flavor  
    (flavor_name, descraption, quantity)  
    values  
    ('Vanilla', 'Plain Vanilla', 4)  
    insert into flavor  
    (flavor_name, descraption, quantity)  
    Values  
    ('Neopolatin', '1/3 Choclate, 1/3 Vanilla. 1/3 Strawberry', 1)  
    insert into flavor  
    (flavor_name, descraption, quantity)  
    Values  
    ('Coffee', 'Fresh Cround Coffee Flavor', 0)  
    insert into flavor  
    (flavor_name, descraption, quantity)  
    Values  
    ('Sardine', 'Self-Explanatory', 7)  
 select *  
 from flavor  
 create table xrefFlavorBrand  
 (  
    xrefID int identity (1,1) primary key  
    , BrandID int  
    , FlavorID int  
 )  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (1,1)  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (2,2)  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (3,3)  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (4,4)  
 insert into xrefFlavorBrand (BrandID, FlavorID) values (5,5)  
 create table sales  
 (  
      SalesID int identity (1,1) primary key  
      , xrefID int  
      , ScoopNo int  
      , Price decimal(4,2)  
      , SalesDate int  
      )  
 insert into sales  
      (xrefID, ScoopNo, Price, SalesDate) values (1, 2, 2.00, 1/2/10)  
 insert into sales   
      (xrefID, ScoopNo, Price, SalesDate) values (2, 3, 3.00, 1/3/10)  
 insert into sales  
      (xrefID, ScoopNo, Price, SalesDate) values (3, 2, 2.00, 1/3/10)  
 insert into sales  
      (xrefID, ScoopNo, Price, SalesDate) values (2,3,3.00, 1/3/10)  
 insert into sales  
      (xrefID, ScoopNo, Price, SalesDate) values (4,2,2.00, 1/4/10)  
 insert into sales  
      (xrefID, ScoopNo, Price, SalesDate) values (5, 4, 4.00, 1/4/10)  
 insert into sales  
      (xrefID, ScoopNo, Price, SalesDate) values (1, 5, 5.00, 1/5/10)       
 insert into sales  
      (xrefID, ScoopNo, Price, SalesDate) values (4, 3, 3.00, 1/5/10)       
 select * from sales  
 create table xrefDistFlavorBrand  
 (  
       xrefDistSalesID int identity (1,1) primary key  
       ,BrandID int  
       ,FlavorID int  
       ,distributor_ID int  
 )  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(1,1,1)  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(2,2,2)  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(3,3,3)  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(4,4,4)  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values (5,5,5)  
 select *   
 from xrefDistFlavorBrand  
 --here is the second table  
 create table xrefDistSales  
 (  
       xrefDistSalesID int identity (1,1) primary key  
       ,BrandID int  
       ,SalesID int        
 )  
 insert into xrefDistSales(BrandID,SalesID) values (1,1)  
 insert into xrefDistSales(BrandID,SalesID) values (2,2)  
 insert into xrefDistSales(BrandID,SalesID) values (3,3)  
 insert into xrefDistSales(BrandID,SalesID) values (4,4)  
 insert into xrefDistSales(BrandID,SalesID) values (5,5)  
 select *  
 from xrefDistSales  
 create table distributors  
    (  
       distributor_ID int identity (1,1) PRIMARY KEY,  
       DistributorName varchar (30)  
    );  
 insert into distributors  
 (DistributorName)  
 Values  
 ('ND Icecream')  
 insert into distributors  
 (DistributorName)  
 Values  
 ('GF Grocery')  
 insert into distributors  
 (DistributorName)  
 Values  
 ('Henreys Foods')  
 insert into distributors  
 (DistributorName)  
 Values  
 ('Mayville Grocery')  
 insert into distributors  
 (DistributorName)  
 Values  
 ('Red River Valley Icecream')  
 select *  
 from distributors  
 --keep track of the number of tubs of ice cream on hand  
 Select *   
 from flavor FL  
 inner join xrefDistFlavorBrand xDFB  
 on FL.Flavor_ID = xDFB.FlavorID  
 where quantity <=1  
 --search distributors by flavor and brand, get a list of distributors  
 select *   
 from distributors D  
 inner join xrefDistFlavorBrand xDFB  
 on d.Distributor_ID = xDFB.Distributor_ID  
 inner join flavor F  
 on f.Flavor_ID = xDFB.FlavorID  
 inner join Brand B  
 on B.BrandID = xDFB.BrandID  
 where flavor_name = 'choclate'  
 and  
 BrandName = 'BlueBunny'  
 --Keep track of which flavors and brands were sold  
 select *   
 from sales S  
 inner join xrefDistSales xDS  
 on s.SalesID = xDS.SalesID  
 inner join xrefDistFlavorBrand xDFB  
 on xDS.xrefDistSalesID = xDFB.xrefDistSalesID  
 inner join flavor F  
 on f.Flavor_ID = xDFB.FlavorID  
 where f.flavor_name = 'vanilla'  

Create inner join on 2 tables in SQL

Blog post: Research and test the creation of 2 table variables, insert 3 records into each, both having a column in common, and select all records from both tables by joining them together. You can use a join or an inner join for this.

 Declare @Frogs table  
 (  
 id int primary key identity(1,1) not null  
 , legs int  
 , color varchar(36)  
 , spots int  
 )  
 Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)  
 Insert into @Frogs (legs, color, spots) values (4, 'red', 7)  
 Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)  
 declare @Ponds table  
 (  
 pid int primary key identity (1,1) not null  
 , id int  
 , location varchar (30)  
 , color varchar (20)  
 , weeds varchar (30)  
 )  
 insert into @Ponds (id, location, color, weeds) values (1, 'Fargo', 'blue', 'Full')  
 insert into @Ponds (id, location, color, weeds) values (2, 'Grand Forks', 'green', 'Half full')  
 insert into @Ponds (id, location, color, weeds) values (3, 'Hillsboro', 'blue-green', 'Quarter full')  
 select f.legs  
      ,f.color  
      ,p.location  
 from @Ponds p  
    inner join @Frogs f  
      on f.id=p.id  
    order by f.id  

Creating a database and table in SQL

 --drop database CoFrogs 
create database CoFrogs 
go 
use CoFrogs 
create table Colorado 
( 
     [id] int identity(1,1) not null primary key 
     ,[ScientificName] varchar(50) 
     ,[IUCNRedListStatus] varchar(20) 
     ,[VernacularName] varchar(50) 
     ,[Family] varchar(30) 
) 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo Boreas' 
     ,'Near Threatened' 
     ,'Western Toad' 
     ,'Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo Cognatus' 
     ,'Least Concern' 
     ,'Great Plains' 
     ,'Toad     Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo debilis' 
     ,'Least Concern' 
     ,'Green Toad' 
     ,'Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo punctatus' 
     ,'Least Concern' 
     ,'Red-Spotted Toad' 
     ,'Bufonidae') 
Insert into Colorado 
     (ScientificName 
     ,IUCNRedListStatus 
     ,VernacularName 
     ,Family) 
values 
     ('Bufo woodhousii' 
     ,'Least Concern' 
     ,'Woodhouses Toad' 
     ,'Bufonidae') 
select * from Colorado 

Alter one row of a table variable

Research and test the creation of a table variable, insert 3 records into it and change one of the values of a column in only 1 row. Then select all records.

Declare @Frogs table
(
id int primary key identity(1,1) not null
, legs int
, color varchar(36)
, spots int
)
Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)
Insert into @Frogs (legs, color, spots) values (4, 'red', 7)
Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)

select * from @Frogs

UPDATE @Frogs
SET color='green', spots=9
WHERE color='purple' AND spots=22

select * from @Frogs

Delete one record of a table variable

Research and test the creation of a table variable, insert 3 records into it and delete only one of the records. The select all records.

Declare @Frogs table
(
id int primary key identity(1,1) not null
, legs int
, color varchar(36)
, spots int
)
Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)
Insert into @Frogs (legs, color, spots) values (4, 'red', 7)
Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)

select * from @Frogs

DELETE FROM @Frogs
WHERE id=1

select * from @Frogs

Create table variable with 3 columns and 3 rows

Research and test the creation of a table variable, insert 3 records into it, and select all records. Use 3 columns and 3 records.

Declare @Frogs table
(
id int primary key identity(1,1) not null
, legs int
, color varchar(36)
, spots int
)
Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)
Insert into @Frogs (legs, color, spots) values (4, 'red', 7)
Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)

select * from @Frogs

1/19/11 Notes

Use DB
Create Table Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)


mdf - data ROM
ldf - logging

two types of tables, temp tables and table variables
syntax and when to use each type

There are two types of Temp Tables, they are local and global.
Local temp tables have a single # sign as the first character in their names, they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instances of the server.
Global temp tables have a double # sign as the first character in their names, they are visible to any user after they are created, and they are deleted when all users referencing the tables have disconnected from the server.
Local temp table:

Use DB
Create Table #Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)

Global temp table:
Use DB
Create Table ##Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)

A table variable uses the @ symbol as the first character in the name and it deletes when the script you are looking at finishes running. (As long as the script takes to run)

declare @mynumber int
set @mynumber = 3
select @mynumber

Declare @Frogs table;
id int primary key identity(1,1)
, legs int not null
)
Insert into @Frogs (legs) values (@mynumber)
Insert into @Frogs (legs) values (4)

select * from @Frogs

go