Share via

How to quickly create a useful developer database for SQL Server 2008 R2

  • Article

Introduction

Why we are doing this?

A simple database is required for most of my applications. It seems everytime I write a blog post I end up re-explaining how to create a basic database. I'm doing this post so I can use it from other posts.

What will the database hold?

The database we will create will essentially be holding stock market information. Before we can create anything, we must make sure it does not already exist. So we need to make sure that MarketIndexData.mdf does not exist. If it already exists and you would like to delete it, I have posted detailed steps here:

We will run a large script

Next we will run a large script file that creates all the necessary: (1) Tables (2) Indices (3) Relationships and constraints

It will include some sample data

Next, we will insert some sample data. Finally, we will run a query that displays the data.

What is useful about the implementation

What makes this database useful is that it contains a one-to-many relationship between two tables. Most developers create business applications that model a 'one-to-many' relationship. Later, I will use the ADO.NET Entity Framework which will require a physical database.

To make all this work, I will use:

(1) SQL Server 2008 R2

 

Let’s Get Started

Step 1

Verify that ‘MarketIndexData’ does not exist.

snap0341 copy[4]

Step 2

Run the following script.

snap0342 copy[4]

snap0343 copy[4]

 

You can get the script here:

 

hyperlink

SQL Query To Create Our Database

https://brunoblogfiles.com/sql/Create_MarketIndexData.sql

See Appendix A for a printout

 

Step 3

Refresh the ‘Database’ Table Listing

snap0344 copy[4]

 

Step 4

Verify the two tables exist (Asset and AssetPrices).

snap0345 copy

Step 5

Add a sequence diagram to verify correctness.

snap0347 copy Select both of the tables that we previously created. snap0348 copy Notice the one-to-many relationship. ‘Assets’ have many ‘AssetPrices.’snap0349 copy

Step 6

Query to get some test data

 SELECT  Assets.AssetID, Assets.Description,      AssetPrices.DatePrice, AssetPrices.[OpenPrice]FROM     AssetPrices INNER JOIN               Assets ON AssetPrices.AssetID = Assets.AssetIDORDER BY AssetPrices.AssetId, AssetPrices.DatePrice
snap0351

Database Completed

We now have a ready database for other projects Other blog posts that I write will point you here.

 

Appendix A

The SQL Script 
 USE [master]GO/****** Object:  Database [MarketIndexData]    Script Date: 07/22/2010 11:17:42 ******/IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MarketIndexData')BEGINCREATE DATABASE [MarketIndexData] ON  PRIMARY ( NAME = N'MarketIndexData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MarketIndexData.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'MarketIndexData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MarketIndexData_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)ENDGOALTER DATABASE [MarketIndexData] SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [MarketIndexData].[dbo].[sp_fulltext_database] @action = 'enable'endGOALTER DATABASE [MarketIndexData] SET ANSI_NULL_DEFAULT OFFGOALTER DATABASE [MarketIndexData] SET ANSI_NULLS OFFGOALTER DATABASE [MarketIndexData] SET ANSI_PADDING OFFGOALTER DATABASE [MarketIndexData] SET ANSI_WARNINGS OFFGOALTER DATABASE [MarketIndexData] SET ARITHABORT OFFGOALTER DATABASE [MarketIndexData] SET AUTO_CLOSE OFFGOALTER DATABASE [MarketIndexData] SET AUTO_CREATE_STATISTICS ONGOALTER DATABASE [MarketIndexData] SET AUTO_SHRINK OFFGOALTER DATABASE [MarketIndexData] SET AUTO_UPDATE_STATISTICS ONGOALTER DATABASE [MarketIndexData] SET CURSOR_CLOSE_ON_COMMIT OFFGOALTER DATABASE [MarketIndexData] SET CURSOR_DEFAULT  GLOBALGOALTER DATABASE [MarketIndexData] SET CONCAT_NULL_YIELDS_NULL OFFGOALTER DATABASE [MarketIndexData] SET NUMERIC_ROUNDABORT OFFGOALTER DATABASE [MarketIndexData] SET QUOTED_IDENTIFIER OFFGOALTER DATABASE [MarketIndexData] SET RECURSIVE_TRIGGERS OFFGOALTER DATABASE [MarketIndexData] SET  DISABLE_BROKERGOALTER DATABASE [MarketIndexData] SET AUTO_UPDATE_STATISTICS_ASYNC OFFGOALTER DATABASE [MarketIndexData] SET DATE_CORRELATION_OPTIMIZATION OFFGOALTER DATABASE [MarketIndexData] SET TRUSTWORTHY OFFGOALTER DATABASE [MarketIndexData] SET ALLOW_SNAPSHOT_ISOLATION OFFGOALTER DATABASE [MarketIndexData] SET PARAMETERIZATION SIMPLEGOALTER DATABASE [MarketIndexData] SET READ_COMMITTED_SNAPSHOT OFFGOALTER DATABASE [MarketIndexData] SET HONOR_BROKER_PRIORITY OFFGOALTER DATABASE [MarketIndexData] SET  READ_WRITEGOALTER DATABASE [MarketIndexData] SET RECOVERY FULLGOALTER DATABASE [MarketIndexData] SET  MULTI_USERGOALTER DATABASE [MarketIndexData] SET PAGE_VERIFY CHECKSUMGOALTER DATABASE [MarketIndexData] SET DB_CHAINING OFFGOEXEC sys.sp_db_vardecimal_storage_format N'MarketIndexData', N'ON'GOUSE [MarketIndexData]GO/****** Object:  ForeignKey [FK_AssetPrices_Assets]    Script Date: 07/22/2010 11:17:43 ******/IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AssetPrices_Assets]') AND parent_object_id = OBJECT_ID(N'[dbo].[AssetPrices]'))ALTER TABLE [dbo].[AssetPrices] DROP CONSTRAINT [FK_AssetPrices_Assets]GO/****** Object:  StoredProcedure [dbo].[DefineTablesWithData]    Script Date: 07/22/2010 11:17:43 ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DefineTablesWithData]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[DefineTablesWithData]GO/****** Object:  Table [dbo].[AssetPrices]    Script Date: 07/22/2010 11:17:43 ******/IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AssetPrices_Assets]') AND parent_object_id = OBJECT_ID(N'[dbo].[AssetPrices]'))ALTER TABLE [dbo].[AssetPrices] DROP CONSTRAINT [FK_AssetPrices_Assets]GOIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AssetPrices]') AND type in (N'U'))DROP TABLE [dbo].[AssetPrices]GO/****** Object:  Table [dbo].[Assets]    Script Date: 07/22/2010 11:17:43 ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Assets]') AND type in (N'U'))DROP TABLE [dbo].[Assets]GO/****** Object:  Table [dbo].[Assets]    Script Date: 07/22/2010 11:17:43 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Assets]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[Assets](    [AssetID] [nchar](16) NOT NULL,    [Description] [nvarchar](max) NOT NULL, CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED (    [AssetID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]ENDGO/****** Object:  Table [dbo].[AssetPrices]    Script Date: 07/22/2010 11:17:43 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AssetPrices]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[AssetPrices](    [AssetPricesId] [int] IDENTITY(1,1) NOT NULL,    [AssetID] [nchar](16) NULL,    [DatePrice] [smalldatetime] NOT NULL,    [OpenPrice] [real] NOT NULL, CONSTRAINT [PK_AssetPrices] PRIMARY KEY CLUSTERED (    [AssetPricesId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]ENDGO-- Add some data/****** Object:  ForeignKey [FK_AssetPrices_Assets]    Script Date: 07/22/2010 11:17:43 ******/IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AssetPrices_Assets]') AND parent_object_id = OBJECT_ID(N'[dbo].[AssetPrices]'))ALTER TABLE [dbo].[AssetPrices]  WITH NOCHECK ADD  CONSTRAINT [FK_AssetPrices_Assets] FOREIGN KEY([AssetID])REFERENCES [dbo].[Assets] ([AssetID])GOIF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AssetPrices_Assets]') AND parent_object_id = OBJECT_ID(N'[dbo].[AssetPrices]'))ALTER TABLE [dbo].[AssetPrices] CHECK CONSTRAINT [FK_AssetPrices_Assets]GO---------------------------------------------------------------------------insert into Assets(AssetID, Description) values ('SP500', 'Standard & Poors 500 Stock Market Index')insert into Assets(AssetID, Description) values ('DJ', 'Dow Jones Industrials Index')insert into Assets(AssetID, Description) values ('EUROSTOXX', 'EuroZone Index')-- First the prices table    insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jul 16, 2010',1077.23)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jul 09, 2010',1022.58)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jul 02, 2010',1077.5)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jun 25, 2010',1122.79)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jun 18, 2010',1095)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jun 11, 2010',1065.84)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jun 04, 2010',1089.41)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','May 28, 2010',1084.78)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','May 21, 2010',1136.52)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','May 14, 2010',1122.27)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','May 07, 2010',1188.57)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Apr 30, 2010',1217.06)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Apr 23, 2010',1192.06)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Apr 16, 2010',1194.93)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Apr 09, 2010',1178.71)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Apr 01, 2010',1167.7)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Mar 26, 2010',1157.25)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Mar 19, 2010',1148.53)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Mar 12, 2010',1138.4)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Mar 05, 2010',1105.36)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Feb 26, 2010',1110)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Feb 19, 2010',1075.51)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Feb 12, 2010',1065.51)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Feb 05, 2010',1073.89)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jan 29, 2010',1092.4)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jan 22, 2010',1136.03)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jan 15, 2010',1145.96)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jan 08, 2010',1116.56)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Dec 31, 2009',1127.5)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Dec 24, 2009',1105.31)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Dec 18, 2009',1107.84)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Dec 11, 2009',1105.52)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Dec 04, 2009',1091.06)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Nov 27, 2009',1094.86)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Nov 20, 2009',1094.13)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Nov 13, 2009',1072.31)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Nov 06, 2009',1036.18)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Oct 30, 2009',1080.36)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Oct 23, 2009',1088.22)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Oct 16, 2009',1071.63)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Oct 09, 2009',1026.87)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Oct 02, 2009',1045.38)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Sep 25, 2009',1067.14)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Sep 18, 2009',1040.15)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Sep 11, 2009',1018.67)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Sep 04, 2009',1025.21)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Aug 28, 2009',1026.59)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Aug 21, 2009',998.18)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Aug 14, 2009',1008.89)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Aug 07, 2009',990.22)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jul 31, 2009',978.63)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('SP500','Jul 24, 2009',942.07)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jul 16, 2010',10199.24)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jul 09, 2010',9686.48)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jul 02, 2010',10143.05)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jun 25, 2010',10452.46)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jun 18, 2010',10211.83)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jun 11, 2010',9931.75)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jun 04, 2010',10136.63)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','May 28, 2010',10193.46)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','May 21, 2010',10616.98)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','May 14, 2010',10386.18)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','May 07, 2010',11009.6)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Apr 30, 2010',11205.11)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Apr 23, 2010',11018.36)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Apr 16, 2010',10996.75)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Apr 09, 2010',10927.45)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Apr 01, 2010',10849.23)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Mar 26, 2010',10741)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Mar 19, 2010',10623.41)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Mar 12, 2010',10563.78)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Mar 05, 2010',10326.1)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Feb 26, 2010',10402.43)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Feb 19, 2010',10099.14)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Feb 12, 2010',10005.43)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Feb 05, 2010',10068.99)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jan 29, 2010',10175.1)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jan 22, 2010',10609.65)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jan 15, 2010',10620.31)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jan 08, 2010',10430.69)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Dec 31, 2009',10517.91)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Dec 24, 2009',10330.1)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Dec 18, 2009',10471.28)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Dec 11, 2009',10386.86)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Dec 04, 2009',10309.77)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Nov 27, 2009',10320.13)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Nov 20, 2009',10267.53)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Nov 13, 2009',10020.62)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Nov 06, 2009',9712.13)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Oct 30, 2009',9972.33)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Oct 23, 2009',9996.67)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Oct 16, 2009',9865.24)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Oct 09, 2009',9488.73)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Oct 02, 2009',9663.23)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Sep 25, 2009',9818.61)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Sep 18, 2009',9598.08)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Sep 11, 2009',9441.27)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Sep 04, 2009',9542.91)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Aug 28, 2009',9506.18)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Aug 21, 2009',9313.85)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Aug 14, 2009',9368.41)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Aug 07, 2009',9173.65)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jul 31, 2009',9093.09)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('DJ','Jul 24, 2009',8746.05)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jul 16, 2010',33.23)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jul 09, 2010',30.87)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jul 02, 2010',32.16)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jun 25, 2010',33.87)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jun 18, 2010',33)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jun 11, 2010',30.72)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jun 04, 2010',32.22)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','May 28, 2010',31.77)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','May 21, 2010',32.98)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','May 14, 2010',35.08)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','May 07, 2010',36.77)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Apr 30, 2010',38.56)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Apr 23, 2010',38.75)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Apr 16, 2010',40)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Apr 09, 2010',39.91)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Apr 01, 2010',38.89)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Mar 26, 2010',37.94)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Mar 19, 2010',38.88)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Mar 12, 2010',38.76)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Mar 05, 2010',36.77)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Feb 26, 2010',37.32)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Feb 19, 2010',36.04)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Feb 12, 2010',35.8)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Feb 05, 2010',38.21)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jan 29, 2010',39.27)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jan 22, 2010',41.63)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jan 15, 2010',43.3)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jan 08, 2010',42.48)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Dec 31, 2009',42.38)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Dec 24, 2009',40.8)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Dec 18, 2009',41.84)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Dec 11, 2009',42.29)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Dec 04, 2009',41.63)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Nov 27, 2009',42.52)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Nov 20, 2009',43.08)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Nov 13, 2009',41.8)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Nov 06, 2009',39.89)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Oct 30, 2009',42.7)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Oct 23, 2009',43.1)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Oct 16, 2009',42.37)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Oct 09, 2009',39.84)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Oct 02, 2009',41.25)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Sep 25, 2009',41.19)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Sep 18, 2009',40.37)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Sep 11, 2009',40.05)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Sep 04, 2009',39.21)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Aug 28, 2009',39.1)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Aug 21, 2009',36.37)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Aug 14, 2009',37.67)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Aug 07, 2009',37.64)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jul 31, 2009',36.36)insert into AssetPrices(AssetID, DatePrice,OpenPrice) values('EUROSTOXX','Jul 24, 2009',35.08)SELECT  Assets.AssetID, Assets.Description,      AssetPrices.DatePrice, AssetPrices.[OpenPrice]FROM     AssetPrices INNER JOIN               Assets ON AssetPrices.AssetID = Assets.AssetIDORDER BY AssetPrices.AssetId, AssetPrices.DatePrice

Comments

  • Anonymous
    April 23, 2012
    It's is very  usefull.. Thanx

  • Anonymous
    October 18, 2014
    Not useful. He started as if it was going to be a step by step tutorials when he started by saying we are going to create a database from nowhere he got the database in place. For me he need to delete this and he dident even used the visual studio 2010 he said we are going to use

  • Anonymous
    November 10, 2014
    In the statement "IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MarketIndexData')", what is N'MarketIndexData? What's the N' ??? Is not the name of the database just MarketIndexData? Is N some cryptic indicator that I am not aware of or is this a typo