Execute the following T-SQL script in Microsoft SQL Server Management Studio Query Editor to search for the highest yield CD.

-- Microsft SQL Server T-SQL interest rate calculation
-- MSSQL find CD (Certificate of Deposit) with highest yield
-- T-SQL table create - T-SQL insert into table – populate table
USE AdventureWorks;
 
CREATE TABLE CDRate (
  Bank         CHAR(40),
  Headquarters CHAR(20),
  CD           CHAR(20),
  Years        INT,
  Rate         SMALLMONEY)
 
GO
/***** FOLLOWING IS DUMMY CD RATE DATA *****/
insert CDRate select 'Citigroup', 'New York, NY', '4YR CD', 4, 17.3
insert CDRate select 'Bank of America Corp.', 'Charlotte, NC', '4YR CD', 4, 17.3
insert CDRate select 'J. P. Morgan Chase & Company', 'Columbus, OH', '4YR CD', 4, 17.3
insert CDRate select 'Wachovia Corp.', 'Charlotte, NC', '4YR CD', 4, 17.3
insert CDRate select 'Taunus Corp.', 'New York, N.Y.', '5YR CD', 5, 23.0
insert CDRate select 'Wells Fargo & Company', 'San Fransisco, CA', '3YR CD', 3, 11.0
insert CDRate select 'HSBC North America Inc.', 'Prospect Heights,IL', '4YR CD', 4, 17.3
insert CDRate select 'U.S. Bancorp', 'Minneapolis, MN', '4YR CD', 4, 17.3
insert CDRate select 'Bank of the New York Mellon Corp.', 'New York, NY', '4YR CD', 4, 17.3
insert CDRate select 'Suntrust, Inc.', 'Atlanta, Ga.', '4YR CD', 4, 17.3
insert CDRate select 'Citizens Financial Group, Inc.', 'Providence, RI', '3YR CD', 3, 11.0
insert CDRate select 'National City Bank', 'Cleveland, OH', '4YR CD', 4, 17.3
insert CDRate select 'State Street Corp.', 'Boston, MA', '4YR CD', 4, 17.3
insert CDRate select 'Capital One Financial Corp.', 'McLean, VA', '4YR CD', 4, 17.3
insert CDRate select 'Regions Financial Corp.', 'Birmingham, AL', '4YR CD', 4, 17.3
insert CDRate select 'PNC Financial Services Group, Inc.', 'Pittsburg, PA', '5YR CD', 5, 23.0
insert CDRate select 'BB&T Corp.', 'Winston-Salem, NC', '4YR CD', 4, 17.3
insert CDRate select 'TD Bank North, INC.', 'Portland, ME', '4YR CD', 4, 17.3
insert CDRate select 'Fifth Third Bankcorp', 'Cincinatti, OH', '3YR CD', 3, 11.0
insert CDRate select 'Keycorp Cleveland,', 'OH', '4YR CD', 4, 17.3
insert CDRate select 'Northern Trust Corp.', 'Chicago, IL', '4YR CD', 4, 17.3
insert CDRate select 'Bancwest Corp.', 'Honolulu, HA', '5YR CD', 5, 23.0
insert CDRate select 'Harris Financial Corp.', 'Wilmington, DE', '4YR CD', 4, 17.3
insert CDRate select 'Comerica Incorporated', 'Dallas, TX', '4YR CD', 4, 17.3
insert CDRate select 'M&T Bank Corp.', 'Buffalo, NY', '4YR CD', 4, 17.3
insert CDRate select 'Marshall & Ilsley Corp.', 'Milwaukee, WI', '4YR CD', 4, 17.3
insert CDRate select 'BBVA USA Bancshares, Inc.', 'The Woodlands, TX', '3YR CD', 3, 11.0
insert CDRate select 'Unionbancal Corporation', 'San Fransisco, CA', '4YR CD', 4, 17.3
insert CDRate select 'Huntington Bancshares, Inc.', 'Columbus, OH', '4YR CD', 4, 17.3
insert CDRate select 'Zions Bancorporation', 'Salt Lake City, UT', '5YR CD', 5, 23.0
go
 
SELECT Legend = 'BEST CD RATES (dummy data)',
       Bank,
       HeadQuarters,
       CD
FROM   CDRate
WHERE  Rate / Years IN (SELECT max(Rate / Years)
                        FROM   CDRate)
 
GO
/* Results
 

            
*/
 
 
------------
-- Lookup table for State Codes
------------
-- State/Possession/Provinces/Miliary Code Abbreviations
-- SQL Server table create
CREATE TABLE dbo.StateCode (
  StateCodeID INT    IDENTITY ( 1 , 1 )    UNIQUE,
  State       VARCHAR(40)    NOT NULL,
  Code        CHAR(2)    PRIMARY KEY);
 
GO
 
-- SQL populate lookup table
-- U.S. States & Posessions
INSERT INTO dbo.StateCode VALUES('ALABAMA','AL');
INSERT INTO dbo.StateCode VALUES('ALASKA','AK');
INSERT INTO dbo.StateCode VALUES('AMERICAN SAMOA','AS');
INSERT INTO dbo.StateCode VALUES('ARIZONA ','AZ');
INSERT INTO dbo.StateCode VALUES('ARKANSAS','AR');
INSERT INTO dbo.StateCode VALUES('CALIFORNIA ','CA');
INSERT INTO dbo.StateCode VALUES('COLORADO ','CO');
INSERT INTO dbo.StateCode VALUES('CONNECTICUT','CT');
INSERT INTO dbo.StateCode VALUES('DELAWARE','DE');
INSERT INTO dbo.StateCode VALUES('DISTRICT OF COLUMBIA','DC');
INSERT INTO dbo.StateCode VALUES('FEDERATED STATES OF MICRONESIA','FM');
INSERT INTO dbo.StateCode VALUES('FLORIDA','FL');
INSERT INTO dbo.StateCode VALUES('GEORGIA','GA');
INSERT INTO dbo.StateCode VALUES('GUAM ','GU');
INSERT INTO dbo.StateCode VALUES('HAWAII','HI');
INSERT INTO dbo.StateCode VALUES('IDAHO','ID');
INSERT INTO dbo.StateCode VALUES('ILLINOIS','IL');
INSERT INTO dbo.StateCode VALUES('INDIANA','IN');
INSERT INTO dbo.StateCode VALUES('IOWA','IA');
INSERT INTO dbo.StateCode VALUES('KANSAS','KS');
INSERT INTO dbo.StateCode VALUES('KENTUCKY','KY');
INSERT INTO dbo.StateCode VALUES('LOUISIANA','LA');
INSERT INTO dbo.StateCode VALUES('MAINE','ME');
INSERT INTO dbo.StateCode VALUES('MARSHALL ISLANDS','MH');
INSERT INTO dbo.StateCode VALUES('MARYLAND','MD');
INSERT INTO dbo.StateCode VALUES('MASSACHUSETTS','MA');
INSERT INTO dbo.StateCode VALUES('MICHIGAN','MI');
INSERT INTO dbo.StateCode VALUES('MINNESOTA','MN');
INSERT INTO dbo.StateCode VALUES('MISSISSIPPI','MS');
INSERT INTO dbo.StateCode VALUES('MISSOURI','MO');
INSERT INTO dbo.StateCode VALUES('MONTANA','MT');
INSERT INTO dbo.StateCode VALUES('NEBRASKA','NE');
INSERT INTO dbo.StateCode VALUES('NEVADA','NV');
INSERT INTO dbo.StateCode VALUES('NEW HAMPSHIRE','NH');
INSERT INTO dbo.StateCode VALUES('NEW JERSEY','NJ');
INSERT INTO dbo.StateCode VALUES('NEW MEXICO','NM');
INSERT INTO dbo.StateCode VALUES('NEW YORK','NY');
INSERT INTO dbo.StateCode VALUES('NORTH CAROLINA','NC');
INSERT INTO dbo.StateCode VALUES('NORTH DAKOTA','ND');
INSERT INTO dbo.StateCode VALUES('NORTHERN MARIANA ISLANDS','MP');
INSERT INTO dbo.StateCode VALUES('OHIO','OH');
INSERT INTO dbo.StateCode VALUES('OKLAHOMA','OK');
INSERT INTO dbo.StateCode VALUES('OREGON','OR');
INSERT INTO dbo.StateCode VALUES('PENNSYLVANIA','PA');
INSERT INTO dbo.StateCode VALUES('PUERTO RICO','PR');
INSERT INTO dbo.StateCode VALUES('RHODE ISLAND','RI');
INSERT INTO dbo.StateCode VALUES('SOUTH CAROLINA','SC');
INSERT INTO dbo.StateCode VALUES('SOUTH DAKOTA','SD');
INSERT INTO dbo.StateCode VALUES('TENNESSEE','TN');
INSERT INTO dbo.StateCode VALUES('TEXAS','TX');
INSERT INTO dbo.StateCode VALUES('UTAH','UT');
INSERT INTO dbo.StateCode VALUES('VERMONT','VT');
INSERT INTO dbo.StateCode VALUES('VIRGIN ISLANDS','VI');
INSERT INTO dbo.StateCode VALUES('VIRGINIA ','VA');
INSERT INTO dbo.StateCode VALUES('WASHINGTON','WA');
INSERT INTO dbo.StateCode VALUES('WEST VIRGINIA','WV');
INSERT INTO dbo.StateCode VALUES('WISCONSIN','WI');
INSERT INTO dbo.StateCode VALUES('WYOMING','WY');
-- US Armed Forces Geography Codes
INSERT INTO dbo.StateCode VALUES('Armed Forces AFRICA/CAN/EU/MidEast', 'AE');
INSERT INTO dbo.StateCode VALUES('Armed Forces Americas', 'AA');
INSERT INTO dbo.StateCode VALUES('Armed Forces Pacific', 'AP');
-- Canadian Postal Province Codes
INSERT INTO dbo.StateCode VALUES('Alberta', 'AB')
INSERT INTO dbo.StateCode VALUES('British Columbia', 'BC')
INSERT INTO dbo.StateCode VALUES('Manitoba', 'MB')
INSERT INTO dbo.StateCode VALUES('New Brunswick', 'NB')
INSERT INTO dbo.StateCode VALUES('Newfoundland and Labrador', 'NL')
INSERT INTO dbo.StateCode VALUES('Northwest Territories', 'NT')
INSERT INTO dbo.StateCode VALUES('Nova Scotia', 'NS')
INSERT INTO dbo.StateCode VALUES('Nunavut', 'NU')
INSERT INTO dbo.StateCode VALUES('Ontario', 'ON')
INSERT INTO dbo.StateCode VALUES('Prince Edward Island', 'PE')
INSERT INTO dbo.StateCode VALUES('Quebec', 'QC')
INSERT INTO dbo.StateCode VALUES('Saskatchewan', 'SK')
INSERT INTO dbo.StateCode VALUES('Yukon', 'YT')
GO
 
-- Query with state spelled out from StateCode lookup table
-- Select from select, correlated subquery, subselect  
SELECT Legend = 'BEST CD (dummy)',
       Bank,
       HeadQuarters,
       HQState = (SELECT State FROM StateCode WHERE HeadQuarters LIKE '%'+Code),
       CD,
       AvgRate= Rate / Years
FROM   CDRate
WHERE  Rate / Years IN (SELECT max(Rate / Years) FROM   CDRate)
GO
/* Results

 
*/
 
-- USPS State Codes - Canadian Province Codes
SELECT * FROM StateCode ORDER BY StateCodeID
GO

/* Results

*/
-- Cleanup
DROP TABLE dbo.StateCode
DROP TABLE CDRate
GO
------------