mssql.sql 4.72 KB
Newer Older
resurtm committed
1 2 3 4 5
IF OBJECT_ID('[dbo].[tbl_order_item]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_order_item];
IF OBJECT_ID('[dbo].[tbl_item]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_item];
IF OBJECT_ID('[dbo].[tbl_order]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_order];
IF OBJECT_ID('[dbo].[tbl_category]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_category];
IF OBJECT_ID('[dbo].[tbl_customer]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_customer];
6
IF OBJECT_ID('[dbo].[tbl_profile]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_profile];
resurtm committed
7
IF OBJECT_ID('[dbo].[tbl_type]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_type];
8
IF OBJECT_ID('[dbo].[tbl_null_values]', 'U') IS NOT NULL DROP TABLE [dbo].[tbl_null_values];
resurtm committed
9

10 11 12 13 14 15 16 17
CREATE TABLE [dbo].[tbl_profile] (
	[id] [int] IDENTITY(1,1) NOT NULL,
	[description] [varchar](128) NOT NULL,
	CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED (
		[id] ASC
	) ON [PRIMARY]
);

resurtm committed
18
CREATE TABLE [dbo].[tbl_customer] (
w  
Qiang Xue committed
19
	[id] [int] IDENTITY(1,1) NOT NULL,
resurtm committed
20
	[email] [varchar](128) NOT NULL,
21
	[name] [varchar](128),
resurtm committed
22 23
	[address] [text],
	[status] [int] DEFAULT 0,
24
  [profile_id] [int],
resurtm committed
25 26 27 28 29 30 31
	CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED (
		[id] ASC
	) ON [PRIMARY]
);

CREATE TABLE [dbo].[tbl_category] (
	[id] [int] IDENTITY(1,1) NOT NULL,
w  
Qiang Xue committed
32
	[name] [varchar](128) NOT NULL,
resurtm committed
33 34 35 36 37 38
	CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED (
		[id] ASC
	) ON [PRIMARY]
);

CREATE TABLE [dbo].[tbl_item] (
w  
Qiang Xue committed
39
	[id] [int] IDENTITY(1,1) NOT NULL,
resurtm committed
40
	[name] [varchar](128) NOT NULL,
w  
Qiang Xue committed
41
	[category_id] [int] NOT NULL,
resurtm committed
42 43 44 45
	CONSTRAINT [PK_item] PRIMARY KEY CLUSTERED (
		[id] ASC
	) ON [PRIMARY]
);
w  
Qiang Xue committed
46

resurtm committed
47 48 49
CREATE TABLE [dbo].[tbl_order] (
	[id] [int] IDENTITY(1,1) NOT NULL,
	[customer_id] [int] NOT NULL,
Alexander Kochetov committed
50
	[created_at] [int] NOT NULL,
resurtm committed
51 52 53 54 55
	[total] [decimal](10,0) NOT NULL,
	CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED (
		[id] ASC
	) ON [PRIMARY]
);
w  
Qiang Xue committed
56

resurtm committed
57 58 59 60 61 62 63 64 65 66
CREATE TABLE [dbo].[tbl_order_item] (
	[order_id] [int] NOT NULL,
	[item_id] [int] NOT NULL,
	[quantity] [int] NOT NULL,
	[subtotal] [decimal](10,0) NOT NULL,
	CONSTRAINT [PK_order_item] PRIMARY KEY CLUSTERED (
		[order_id] ASC,
		[item_id] ASC
	) ON [PRIMARY]
);
w  
Qiang Xue committed
67

68 69 70 71 72 73 74 75 76
CREATE TABLE [dbo].[tbl_null_values] (
  id [int] UNSIGNED NOT NULL,
  var1 [int] UNSIGNED NULL,
  var2 [int] NULL,
  var3 [int] DEFAULT NULL,
  stringcol [varchar](32) DEFAULT NULL,
  PRIMARY KEY (id)
);

resurtm committed
77 78 79 80 81 82 83 84
CREATE TABLE [dbo].[tbl_type] (
	[int_col] [int] NOT NULL,
	[int_col2] [int] DEFAULT '1',
	[char_col] [char](100) NOT NULL,
	[char_col2] [varchar](100) DEFAULT 'something',
	[char_col3] [text],
	[float_col] [decimal](4,3) NOT NULL,
	[float_col2] [float] DEFAULT '1.23',
resurtm committed
85
	[blob_col] [varbinary](MAX),
resurtm committed
86 87 88 89 90
	[numeric_col] [decimal](5,2) DEFAULT '33.22',
	[time] [datetime] NOT NULL DEFAULT '2002-01-01 00:00:00',
	[bool_col] [tinyint] NOT NULL,
	[bool_col2] [tinyint] DEFAULT '1'
);
w  
Qiang Xue committed
91

92 93 94 95
INSERT INTO [dbo].[tbl_profile] ([description]) VALUES ('profile customer 1');
INSERT INTO [dbo].[tbl_profile] ([description]) VALUES ('profile customer 3');

INSERT INTO [dbo].[tbl_customer] ([email], [name], [address], [status], [profile_id]) VALUES ('user1@example.com', 'user1', 'address1', 1, 1);
resurtm committed
96
INSERT INTO [dbo].[tbl_customer] ([email], [name], [address], [status]) VALUES ('user2@example.com', 'user2', 'address2', 1);
97
INSERT INTO [dbo].[tbl_customer] ([email], [name], [address], [status], [profile_id]) VALUES ('user3@example.com', 'user3', 'address3', 2, 2);
w  
Qiang Xue committed
98

resurtm committed
99 100
INSERT INTO [dbo].[tbl_category] ([name]) VALUES ('Books');
INSERT INTO [dbo].[tbl_category] ([name]) VALUES ('Movies');
w  
Qiang Xue committed
101

resurtm committed
102 103 104 105 106
INSERT INTO [dbo].[tbl_item] ([name], [category_id]) VALUES ('Agile Web Application Development with Yii1.1 and PHP5', 1);
INSERT INTO [dbo].[tbl_item] ([name], [category_id]) VALUES ('Yii 1.1 Application Development Cookbook', 1);
INSERT INTO [dbo].[tbl_item] ([name], [category_id]) VALUES ('Ice Age', 2);
INSERT INTO [dbo].[tbl_item] ([name], [category_id]) VALUES ('Toy Story', 2);
INSERT INTO [dbo].[tbl_item] ([name], [category_id]) VALUES ('Cars', 2);
w  
Qiang Xue committed
107

Alexander Kochetov committed
108 109 110
INSERT INTO [dbo].[tbl_order] ([customer_id], [created_at], [total]) VALUES (1, 1325282384, 110.0);
INSERT INTO [dbo].[tbl_order] ([customer_id], [created_at], [total]) VALUES (2, 1325334482, 33.0);
INSERT INTO [dbo].[tbl_order] ([customer_id], [created_at], [total]) VALUES (2, 1325502201, 40.0);
w  
Qiang Xue committed
111

resurtm committed
112 113 114 115 116 117
INSERT INTO [dbo].[tbl_order_item] ([order_id], [item_id], [quantity], [subtotal]) VALUES (1, 1, 1, 30.0);
INSERT INTO [dbo].[tbl_order_item] ([order_id], [item_id], [quantity], [subtotal]) VALUES (1, 2, 2, 40.0);
INSERT INTO [dbo].[tbl_order_item] ([order_id], [item_id], [quantity], [subtotal]) VALUES (2, 4, 1, 10.0);
INSERT INTO [dbo].[tbl_order_item] ([order_id], [item_id], [quantity], [subtotal]) VALUES (2, 5, 1, 15.0);
INSERT INTO [dbo].[tbl_order_item] ([order_id], [item_id], [quantity], [subtotal]) VALUES (2, 3, 1, 8.0);
INSERT INTO [dbo].[tbl_order_item] ([order_id], [item_id], [quantity], [subtotal]) VALUES (3, 2, 1, 40.0);