-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path23 Dec 2019 (3).txt
124 lines (106 loc) · 3.86 KB
/
23 Dec 2019 (3).txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
create table demo3
(
id number(2) primary key
);
create table demo4
(
id number(2) constraints pk_id_demo4 primary key
);
select * from demo3;
insert into demo3 values(1);
insert into demo3 values(2);
insert into demo3 values(3);
insert into demo3 values(1);
select * from demo4;
insert into demo4 values(1);
insert into demo4 values(2);
insert into demo4 values(3);
insert into demo4 values(1);
create table TBL_MST_ITEM
(
ItemID Number(4) constraints pk_itemid_item primary key,
ItemName Varchar2(100) not null,
ItemBarCode Number(4) constraints unq_itbarcode_item unique,
ItemDesc varchar2(500),
ItemPrice number(10,3) constraints chk_price_item check (ItemPrice>=0),
Qty Number(3) constraints chk_qty_item check(Qty>=0),
ItemMovType char(1) constraints chk_itemmov_item check( ItemMovType in ('F','S','N')),
PurchDate Date,
CreateBy number(2),
UpdatedBy number(2)
);
desc TBL_MST_ITEM
-- to check constraints
select * from all_constraints where table_name like'TBL_MST_ITEM';
-- To Add new record into table used DML insert command
Insert into tbl_mst_item values
(1001, 'Mouse',1001, 'Logitech Mouse ',250, 20, 'F','12-Feb-19',1, 2);
commit;
select * from tbl_mst_item;
Insert into tbl_mst_item values
(1002, 'Keyboard',1002,' Dell Standard Keyboard' ,750.5,15, 'F','13-Mar-19',1,3);
Insert into tbl_mst_item values
(1003,'Printer',1003,' HP IP Printer', 20000,5,'S', '18-Mar-19', 1, 3);
commit;
Insert into tbl_mst_item values
(1004, 'Mouse Pad',null,'Mouse Pad',200.75, 200,'F','16-Jun-19',1,null);
Insert into tbl_mst_item (ItemID,ItemName,ItemPrice,Qty,ITEMMOVTYPE,PURCHDATE,CREATEBY)
values(1011,'Head Phone', 2000,50,'F','10-Sep-19',1 );
select * from tbl_mst_item;
-- sequence change for insertion
insert into tbl_mst_item (ItemName,ItemPrice,ItemID)
values('Levis Jeans',60000,1012);
commit;
select * from tbl_mst_item;
select ItemName,Itembarcode,ItemPrice from tbl_mst_item;
--
-- Testing the column value and attributes
-- Item Movement Type ('F','S','N')
insert into tbl_mst_item values
(1005, 'Scanner',1005,null,3500,25,'N','20-Jul-19',1,5);
-- Item Movement Type - M
insert into tbl_mst_item values
(1006,'CPU',1006,'Intell Central Process Unit',7500,5,'M','22-Jul-19',1,1);
-- Qty not negative
insert into tbl_mst_item values
(1006,'CPU',1006,'Intell Central Process Unit',7500,-5,'S','22-Jul-19',1,1);
-- Item Price not negative
insert into tbl_mst_item values
(1006,'CPU',1006,'Intell Central Process Unit',-7500,5,'S','22-Jul-19',1,1);
-- item bar code duplicate value 1001
insert into tbl_mst_item values
(1006,'CPU',1001,'Intell Central Process Unit',7500,5,'S','22-Jul-19',1,1);
-- item name can not be null
-- item bar code duplicate value 1001
insert into tbl_mst_item values
(1006,null,1006,'Intell Central Process Unit',7500,5,'S','22-Jul-19',1,1);
-- Itemid - can not be duplicate
-- item bar code duplicate value 1001
insert into tbl_mst_item values
(1001,'CPU',1006,'Intell Central Process Unit',7500,5,'S','22-Jul-19',1,1);
-- item bar code can not be null
insert into tbl_mst_item values
(null,'CPU',1006,'Intell Central Process Unit',7500,5,'S','22-Jul-19',1,1);
insert into tbl_mst_item values
(1006,'CPU',1006,'Intell Central Process Unit',7500,5,'S','22-Jul-19',1,1);
commit;
select * from tbl_mst_item;
create table TBL_MST_USER
(
UserId number(2) constraints pk_id_user primary key,
FirstName varchar2(50) not null,
LastName varchar2(50) not null,
Position varchar2(100) not null,
CratedDate date,
UpdateDate date
);
insert into tbl_mst_user
values(1,'Test','Test','Position',sysdate,sysdate);
select * from tbl_mst_user;
insert into tbl_mst_user
values(2,'Test1','Test1','Position',sysdate,sysdate);
insert into tbl_mst_user
select 3,'Test3','Test3','Manager',sysdate,sysdate from dual
union
select 4, 'Test4','Test4','Operator',sysdate,sysdate from dual;
commit;