|
0
|
1 --
|
|
|
2 -- Helper table
|
|
|
3 --
|
|
|
4 DROP TABLE IF EXISTS Dual;
|
|
|
5 CREATE TABLE Dual (Dummy VARCHAR(10));
|
|
|
6 INSERT INTO Dual (Dummy) VALUES ('X');
|
|
|
7
|
|
|
8 --
|
|
|
9 -- Person Table
|
|
|
10 --
|
|
|
11 DROP TABLE IF EXISTS Person;
|
|
|
12 CREATE TABLE Person
|
|
|
13 (
|
|
|
14 PersonID integer NOT NULL CONSTRAINT PK_Person PRIMARY KEY AUTOINCREMENT,
|
|
|
15 FirstName nvarchar(50) NOT NULL,
|
|
|
16 LastName nvarchar(50) NOT NULL,
|
|
|
17 MiddleName nvarchar(50) NULL,
|
|
|
18 Gender char(1) NOT NULL CONSTRAINT CK_Person_Gender CHECK (Gender in ('M', 'F', 'U', 'O'))
|
|
|
19 );
|
|
|
20
|
|
|
21 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M');
|
|
|
22 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M');
|
|
|
23
|
|
|
24 --
|
|
|
25 -- Doctor Table Extension
|
|
|
26 --
|
|
|
27 DROP TABLE IF EXISTS Doctor;
|
|
|
28 CREATE TABLE Doctor
|
|
|
29 (
|
|
|
30 PersonID integer NOT NULL CONSTRAINT PK_Doctor PRIMARY KEY,
|
|
|
31 Taxonomy nvarchar(50) NOT NULL
|
|
|
32 );
|
|
|
33
|
|
|
34 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry');
|
|
|
35
|
|
|
36 --
|
|
|
37 -- Patient Table Extension
|
|
|
38 --
|
|
|
39 DROP TABLE IF EXISTS Patient;
|
|
|
40 CREATE TABLE Patient
|
|
|
41 (
|
|
|
42 PersonID integer NOT NULL CONSTRAINT PK_Patient PRIMARY KEY,
|
|
|
43 Diagnosis nvarchar(256) NOT NULL
|
|
|
44 );
|
|
|
45 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution');
|
|
|
46
|
|
|
47 --
|
|
|
48 -- BinaryData Table
|
|
|
49 --
|
|
|
50 DROP TABLE IF EXISTS BinaryData;
|
|
|
51 CREATE TABLE BinaryData
|
|
|
52 (
|
|
|
53 BinaryDataID integer NOT NULL CONSTRAINT PK_BinaryData PRIMARY KEY AUTOINCREMENT,
|
|
|
54 Stamp timestamp NOT NULL,
|
|
|
55 Data blob(1024) NOT NULL
|
|
|
56 );
|
|
|
57
|
|
|
58 --
|
|
|
59 -- Babylon test
|
|
|
60 --
|
|
|
61 DROP TABLE IF EXISTS DataTypeTest;
|
|
|
62 CREATE TABLE DataTypeTest
|
|
|
63 (
|
|
|
64 DataTypeID integer NOT NULL CONSTRAINT PK_DataType PRIMARY KEY AUTOINCREMENT,
|
|
|
65 Binary_ binary(50) NULL,
|
|
|
66 Boolean_ bit NULL,
|
|
|
67 Byte_ tinyint NULL,
|
|
|
68 Bytes_ varbinary(50) NULL,
|
|
|
69 Char_ char(1) NULL,
|
|
|
70 DateTime_ datetime NULL,
|
|
|
71 Decimal_ decimal(20,2) NULL,
|
|
|
72 Double_ float NULL,
|
|
|
73 Guid_ uniqueidentifier NULL,
|
|
|
74 Int16_ smallint NULL,
|
|
|
75 Int32_ int NULL,
|
|
|
76 Int64_ bigint NULL,
|
|
|
77 Money_ money NULL,
|
|
|
78 SByte_ tinyint NULL,
|
|
|
79 Single_ real NULL,
|
|
|
80 Stream_ varbinary(50) NULL,
|
|
|
81 String_ nvarchar(50) NULL,
|
|
|
82 UInt16_ smallint NULL,
|
|
|
83 UInt32_ int NULL,
|
|
|
84 UInt64_ bigint NULL,
|
|
|
85 Xml_ text NULL
|
|
|
86 );
|
|
|
87
|
|
|
88 INSERT INTO DataTypeTest
|
|
|
89 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
90 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
91 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
|
|
|
92 VALUES
|
|
|
93 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
94 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
95 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
|
|
|
96
|
|
|
97 INSERT INTO DataTypeTest
|
|
|
98 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
99 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
100 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
|
|
|
101 Xml_)
|
|
|
102 VALUES
|
|
|
103 (randomblob(16), 1, 255, zeroblob(16), 'B', DATETIME('NOW'), 12345.67,
|
|
|
104 1234.567, '{64e145a3-0077-4335-b2c6-ea19c9f464f8}', 32767, 32768, 1000000, 12.3456, 127,
|
|
|
105 1234.123, randomblob(64), 'string', 32767, 32768, 200000000,
|
|
|
106 '<root><element strattr="strvalue" intattr="12345"/></root>');
|
|
|
107
|
|
|
108
|
|
|
109 DROP TABLE IF EXISTS Parent;
|
|
|
110 DROP TABLE IF EXISTS Child;
|
|
|
111 DROP TABLE IF EXISTS GrandChild;
|
|
|
112
|
|
|
113 CREATE TABLE Parent (ParentID int, Value1 int);
|
|
|
114 CREATE TABLE Child (ParentID int, ChildID int);
|
|
|
115 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int);
|
|
|
116
|
|
|
117 DROP TABLE IF EXISTS LinqDataTypes;
|
|
|
118 CREATE TABLE LinqDataTypes
|
|
|
119 (
|
|
|
120 ID int,
|
|
|
121 MoneyValue decimal(10,4),
|
|
|
122 DateTimeValue datetime,
|
|
|
123 DateTimeValue2 datetime2,
|
|
|
124 BoolValue boolean,
|
|
|
125 GuidValue uniqueidentifier,
|
|
|
126 BinaryValue binary(5000) NULL,
|
|
|
127 SmallIntValue smallint,
|
|
|
128 IntValue int NULL,
|
|
|
129 BigIntValue bigint NULL
|
|
|
130 );
|
|
|
131
|
|
|
132 DROP TABLE TestIdentity
|
|
|
133 GO
|
|
|
134
|
|
|
135 CREATE TABLE TestIdentity (
|
|
|
136 ID integer NOT NULL CONSTRAINT PK_TestIdentity PRIMARY KEY AUTOINCREMENT
|
|
|
137 )
|
|
|
138 GO
|
