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

 

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

  • Comments 1

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:

hyperlink[4]

How to completely delete a SQL Server Database

http://blogs.msdn.com/b/brunoterkaly/archive/2010/07/22/how-i-completely-remove-a-sql-server-database-guaranteed-to-work-at-least-on-my-current-setup.aspx

 

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
(2) Visual Studio 2010

 

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

http://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.AssetID
ORDER 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')
BEGIN
CREATE 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%)
END
GO
ALTER DATABASE [MarketIndexData] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MarketIndexData].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [MarketIndexData] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MarketIndexData] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MarketIndexData] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MarketIndexData] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MarketIndexData] SET ARITHABORT OFF
GO
ALTER DATABASE [MarketIndexData] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MarketIndexData] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MarketIndexData] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MarketIndexData] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MarketIndexData] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MarketIndexData] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [MarketIndexData] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MarketIndexData] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MarketIndexData] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MarketIndexData] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MarketIndexData] SET  DISABLE_BROKER
GO
ALTER DATABASE [MarketIndexData] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MarketIndexData] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MarketIndexData] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MarketIndexData] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MarketIndexData] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MarketIndexData] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [MarketIndexData] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [MarketIndexData] SET  READ_WRITE
GO
ALTER DATABASE [MarketIndexData] SET RECOVERY FULL
GO
ALTER DATABASE [MarketIndexData] SET  MULTI_USER
GO
ALTER DATABASE [MarketIndexData] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MarketIndexData] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'MarketIndexData', N'ON'
GO
USE [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]
GO
IF  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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Assets]') AND type in (N'U'))
BEGIN
CREATE 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]
END
GO
/****** Object:  Table [dbo].[AssetPrices]    Script Date: 07/22/2010 11:17:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AssetPrices]') AND type in (N'U'))
BEGIN
CREATE 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]
END
GO
-- 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])
GO
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] 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.AssetID
ORDER BY AssetPrices.AssetId, AssetPrices.DatePrice



  • It's is very  usefull.. Thanx

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post