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.