5 -- =================================== OBJECT 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);
22 -- does not have money & smallmoney types
25 insert into numeric_family values (1,1,255,32767,2147483647,9223372036854775807,1000,1000,922337203685477.5807,214748.3647);
26 insert into numeric_family values (2,0,0,-32768,-2147483648,-9223372036854775808,-1000,-1000,-922337203685477.5808,-214748.3648);
27 insert into numeric_family values (3,0,0,0,0,0,0,0,0,0);
28 insert into numeric_family values (4,null,null,null,null,null,null,null,null,null);
31 -- =================================== END OBJECT NUMERIC_FAMILY ========================
33 -- =================================== OBJECT BINARY_FAMILY =========================
35 -- data with id > 6000 is not gaurenteed to be read-only.
36 drop table if exists binary_family;
39 create table binary_family (
40 id int PRIMARY KEY NOT NULL,
41 type_binary binary NULL,
42 type_varbinary varbinary (255) NULL,
44 type_tinyblob tinyblob NULL,
45 type_mediumblob mediumblob NULL,
46 type_longblob_image longblob NULL);
49 insert into binary_family values (1, '555555', '0123456789012345678901234567890123456789012345678901234567890123456789', '66666666', '777777', '888888', '999999');
50 --insert into binary_family values (2,
51 --insert into binary_family values (3,
52 insert into binary_family values (4,null,null,null,null,null,null);
55 -- =================================== END OBJECT BINARY_FAMILY ========================
58 -- =================================== OBJECT EMPLOYEE ============================
60 -- data with id above 6000 is not gaurenteed to be read-only.
61 drop table if exists employee;
64 create table employee (
65 id int PRIMARY KEY NOT NULL,
66 fname varchar (50) NOT NULL,
68 dob datetime NOT NULL,
69 doj datetime NOT NULL,
72 grant all privileges on employee to monotester;
74 insert into employee values (1, 'suresh', 'kumar', '1978-08-22', '2001-03-12', 'suresh@gmail.com');
75 insert into employee values (2, 'ramesh', 'rajendran', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com');
76 insert into employee values (3, 'venkat', 'ramakrishnan', '1977-06-12', '2003-12-11', 'ramesh@yahoo.com');
77 insert into employee values (4, 'ramu', 'dhasarath', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com');
81 -- SP : sp_clean_person_table
82 drop procedure if exists sp_clean_employee_table;
85 create procedure sp_clean_employee_table ()
87 delete from employee where id > 6000;
92 drop procedure if exists sp_get_age;
95 create procedure sp_get_age (
100 select age = datediff (day, dob, getdate ()) from employee where fname like fname;
105 -- =================================== END OBJECT EMPLOYEE ============================