5 -- =================================== OBJECT NUMERIC_FAMILY =========================
6 -- TABLE : NUMERIC_FAMILY
7 -- data with id > 6000 is not gaurenteed to be read-only.
8 drop table if exists numeric_family;
11 create table numeric_family (
12 id int PRIMARY KEY NOT NULL,
14 type_tinyint tinyint NULL,
15 type_smallint smallint NULL,
17 type_bigint bigint NULL,
18 type_decimal decimal (38, 0) NULL,
19 type_numeric numeric (38, 0) NULL,
20 type_money numeric (38,0) NULL,
21 type_smallmoney numeric (12,0) NULL,
23 type_double float NULL);
24 -- does not have money & smallmoney types
27 insert into numeric_family values (1,1,255,32767,2147483647,9223372036854775807,1000,1000,922337203685477.5807,214748.3647,3.40E+38,1.79E+308);
28 insert into numeric_family values (2,0,0,-32768,-2147483648,-9223372036854775808,-1000,-1000,-922337203685477.5808,-214748.3648,-3.40E+38,-1.79E+308);
29 insert into numeric_family values (3,0,0,0,0,0,0,0,0,0,0,0);
30 insert into numeric_family values (4,null,null,null,null,null,null,null,null,null,null,null);
32 -- =================================== END OBJECT NUMERIC_FAMILY ========================
34 -- =================================== OBJECT BINARY_FAMILY =========================
35 -- TABLE : BINARY_FAMILY
36 -- data with id > 6000 is not gaurenteed to be read-only.
37 drop table if exists binary_family;
40 create table binary_family (
41 id int PRIMARY KEY NOT NULL,
42 type_binary binary NULL,
43 type_varbinary varbinary (255) NULL,
45 type_tinyblob tinyblob NULL,
46 type_mediumblob mediumblob NULL,
47 type_longblob_image longblob NULL);
50 insert into binary_family values (1, '555555', '0123456789012345678901234567890123456789012345678901234567890123456789', '66666666', '777777', '888888', '999999');
51 --insert into binary_family values (2,
52 --insert into binary_family values (3,
53 insert into binary_family values (4,null,null,null,null,null,null);
56 -- =================================== END OBJECT BINARY_FAMILY ========================
58 -- =================================== OBJECT STRING_FAMILY============================
59 -- TABLE : string_family
60 -- data with id above 6000 is not gaurenteed to be read-only.
61 drop table if exists string_family;
64 create table string_family (
65 id int PRIMARY KEY NOT NULL,
66 type_char char(10) NULL,
67 type_varchar varchar(10) NULL,
69 type_ntext longtext NULL);
72 grant all privileges on string_family to monotester;
75 insert into string_family values (1,"char","varchar","text","ntext");
76 insert into string_family values (2, '0123456789','varchar' ,'longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext ','ntext');
77 insert into string_family values (4,null,null,null,null);
79 -- =================================== END OBJECT STRING_FAMILY ========================
81 -- =================================== OBJECT DATETIME_FAMILY============================
82 -- TABLE : datetime_family
83 -- data with id above 6000 is not gaurenteed to be read-only.
85 drop table if exists datetime_family;
88 create table datetime_family (
89 id int PRIMARY KEY NOT NULL,
90 type_smalldatetime timestamp NULL,
91 type_datetime datetime NULL);
93 grant all privileges on datetime_family to monotester;
96 insert into datetime_family values (1,'2079-06-06 23:59:00','9999-12-31 23:59:59.997');
97 insert into datetime_family values (4,null,null);
100 -- =================================== END OBJECT DATETIME_FAMILY========================
103 -- =================================== OBJECT EMPLOYEE ============================
105 -- data with id above 6000 is not gaurenteed to be read-only.
106 drop table if exists employee;
109 create table employee (
110 id int PRIMARY KEY NOT NULL,
111 fname varchar (50) NOT NULL,
113 dob datetime NOT NULL,
114 doj datetime NOT NULL,
117 grant all privileges on employee to monotester;
119 insert into employee values (1, 'suresh', 'kumar', '1978-08-22', '2001-03-12', 'suresh@gmail.com');
120 insert into employee values (2, 'ramesh', 'rajendran', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com');
121 insert into employee values (3, 'venkat', 'ramakrishnan', '1977-06-12', '2003-12-11', 'ramesh@yahoo.com');
122 insert into employee values (4, 'ramu', 'dhasarath', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com');
126 -- SP : sp_clean_person_table
127 drop procedure if exists sp_clean_employee_table;
130 create procedure sp_clean_employee_table ()
132 delete from employee where id > 6000;
137 drop procedure if exists sp_get_age;
140 create procedure sp_get_age (
145 select age = datediff (day, dob, getdate ()) from employee where fname like fname;
150 -- =================================== END OBJECT EMPLOYEE ============================