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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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