| 
View
 

Lecture - Lookup Tables Part 1

Page history last edited by Dr. Ron Eaglin 11 years, 9 months ago
 

Lookup Tables Part 1

 

Summary of Video

 

I demonstrate multiple methods of creating and using lookup tables. Examples are a single lookup table per field and using a 2 table lookup table system.

 

Prerequisites

 

The Data Dictionary used in this lecture is available at http://www.census.gov/acs/www/Downloads/data_documentation/pums/DataDict/PUMS_Data_Dictionary_2009-2011.pdf 

 

The Data used in this lecture is uploaded here FL_Housing_Data_2006.zip

 

 

Video Link

 

http://online1.daytonastate.edu/player2.php?id=87f4d79e36d68c3031ccf6c55e9bbd39 

 

 

Support Materials

Table Structure of using a 2 table design for handling look-up function.

CREATE TABLE Categories (
 [Name] NVARCHAR(50) PRIMARY KEY,
 [Description] NVARCHAR(200) 
)
CREATE TABLE Codes (
 [Name] NVARCHAR(50),
 [CategoryName] NVARCHAR(50) FOREIGN KEY REFERENCES Categories(Name),
 [Description] NVARCHAR(200) ) 

 

The CatCodes View

 

CREATE VIEW CatCodes AS
SELECT    Codes.Name AS CodeName,
        Categories.Name AS CategoryName,
        [Codes].[Description] AS CodeDescription,
        [Categories].[Description] AS CategoryDescription
FROM    Categories, Codes
WHERE    Codes.CategoryName = Categories.Name        
 

 

 

 

 

Queries used in the Video

SELECT DISTINCT(ACR) FROM RawData
 
SELECT COUNT(*) FROM RawData WHERE RawData.ACR = '1' 
        
SELECT   [Codes].[Description],  COUNT(*) As 'Number Units' 
FROM     RawData, Codes, Categories 
WHERE    RawData.ACR = Codes.Name
  AND    Codes.CategoryName = 'ACR'
  AND    Categories.Name = Codes.CategoryName
GROUP BY [Codes].[Description]   
    
SELECT   [ACR].[Description],  COUNT(*) As 'Number Units' 
FROM     RawData, ACR
WHERE    RawData.ACR = [ACR].[Code]
GROUP BY [ACR].[Description]
   
SELECT * FROM CatCodes
SELECT     [CatCodes].[CodeDescription], COUNT(*) As 'Number Units' 
FROM     RawData, CatCodes 
WHERE     RawData.ACR = CatCodes.CodeName
    AND  CatCodes.CategoryName = 'ACR'
GROUP BY [CatCodes].[CodeDescription] 

 

 

 

 

All Materials Copyright 2012 Dr. Ron Eaglin

Comments (0)

You don't have permission to comment on this page.