DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to keep track of the stock market?

Execute the following Microsoft SQL Server T-SQL example scripts in SQL Server Management Studio Query Editor to setup tables for historical stock price tracking.


------------
-- Microsoft SQL Server stock price database architecture
------------
USE AdventureWorks;
GO
-- SQL table create for Stock Exchanges
CREATE TABLE Exchange (
  ExchangeID TINYINT    IDENTITY ( 1 , 1 )    PRIMARY KEY,
  Name       VARCHAR(64),
  DateModified DATETIME    DEFAULT (getdate()))
GO
 
-- MSSQL INSERT INTO - populated Exchange table
INSERT Exchange (Name) VALUES('New York Stock Exchange')
INSERT Exchange (Name) VALUES('NASDAQ')
GO
 
-- Test Exchange table population
SELECT *  FROM   Exchange
GO
/*
ExchangeID  Name  DateModified
1     New York Stock Exchange 2009-03-03 19:17:39.093
2     NASDAQ      2009-03-03 19:17:39.093
*/
 
-- SQL table create for historical stock prices
CREATE TABLE StockPrice (
  StockPriceID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,
  TradingDate  SMALLDATETIME,
  ExchangeID   TINYINT REFERENCES Exchange(ExchangeID),
  Symbol       CHAR(6),
  [Open]       SMALLMONEY,
  High         SMALLMONEY,
  Low          SMALLMONEY,
  [Close]      SMALLMONEY,
  Volume       INT,
  DateModified SMALLDATETIME    DEFAULT (getdate()));
 
GO
 
-- Dummy data - historical stock price for a day
-- T-SQL insert into table
INSERT INTO StockPrice
      (TradingDate,
       ExchangeID,
       Symbol,
       [Open],
       High,
       Low,
       [Close],
       Volume)
VALUES('2018/11/19',1,'IBM',76.0,77.0,75.0,76.2,11222444)
GO
 
SELECT *  FROM   StockPrice
GO
/*

StockPriceID TradingDate ExchangeID Symbol Open High Low Close Volume DateModified
1 11/19/2018 0:00 1 IBM    76 77 75 76.2 11222444 3/3/2009 19:18

*/
 
-- Cleanup
DROP TABLE dbo.StockPrice
DROP TABLE dbo.Exchange

 

------------

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE