-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathfms.sql
158 lines (131 loc) · 6.18 KB
/
fms.sql
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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
CREATE DATABASE fms;
USE fms;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
CREATE TABLE `admintb` (
`username` varchar(50) NOT NULL,
`password` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `admintb` (`username`, `password`) VALUES
('admin', 'admin123');
CREATE TABLE `contact` (
`name` varchar(30) NOT NULL,
`email` text NOT NULL,
`contact` varchar(10) NOT NULL,
`message` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `contact` (`name`, `email`, `contact`, `message`) VALUES
('Anu', '[email protected]', '7896677554', 'Hey Admin'),
(' Viki', '[email protected]', '9899778865', 'Good Job, Pal'),
('Ananya', '[email protected]', '9997888879', 'How can I reach you?'),
('Aakash', '[email protected]', '8788979967', 'Love your site'),
('Mani', '[email protected]', '8977768978', 'Want some coffee?'),
('Karthick', '[email protected]', '9898989898', 'Good service'),
('Abbis', '[email protected]', '8979776868', 'Love your service'),
('Asiq', '[email protected]', '9087897564', 'Love your service. Thank you!'),
('Jane', '[email protected]', '7869869757', 'I love your service!');
CREATE TABLE `emptb` (
`eid` integer primary key AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`salary` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `emptb` (`username`, `password`, `email`, `phone`, `salary`) VALUES
('ashok', 'ashok123', '[email protected]', '9900000001', 500),
('arun', 'arun123', '[email protected]', '9900000002', 600),
('Dinesh', 'dinesh123', '[email protected]', '9900000003', 700),
('Ganesh', 'ganesh123', '[email protected]', '9900000004', 550),
('Kumar', 'kumar123', '[email protected]', '9900000005', 800),
('Amit', 'amit123', '[email protected]', '9900000006', 1000),
('Abbis', 'abbis123', '[email protected]', '9900000007', 1500),
('Tiwary', 'tiwary123', '[email protected]', '9900000008', 450);
CREATE TABLE `userreg` (
`pid` int(11) NOT NULL,
`fname` varchar(20) NOT NULL,
`lname` varchar(20) NOT NULL,
`gender` varchar(10) NOT NULL,
`email` varchar(30) NOT NULL,
`contact` varchar(10) NOT NULL,
`password` varchar(30) NOT NULL,
`cpassword` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `userreg` (`pid`, `fname`, `lname`, `gender`, `email`, `contact`, `password`, `cpassword`) VALUES
(1, 'Ram', 'Kumar', 'Male', '[email protected]', '9876543210', 'ram123', 'ram123'),
(2, 'Alia', 'Bhatt', 'Female', '[email protected]', '8976897689', 'alia123', 'alia123'),
(3, 'Shahrukh', 'khan', 'Male', '[email protected]', '8976898463', 'shahrukh123', 'shahrukh123'),
(4, 'Kishan', 'Lal', 'Male', '[email protected]', '8838489464', 'kishan123', 'kishan123'),
(5, 'Gautam', 'Shankararam', 'Male', '[email protected]', '9070897653', 'gautam123', 'gautam123'),
(6, 'Sushant', 'Singh', 'Male', '[email protected]', '9059986865', 'sushant123', 'sushant123'),
(7, 'Nancy', 'Deborah', 'Female', '[email protected]', '9128972454', 'nancy123', 'nancy123'),
(8, 'Kenny', 'Sebastian', 'Male', '[email protected]', '9809879868', 'kenny123', 'kenny123'),
(9, 'William', 'Blake', 'Male', '[email protected]', '8683619153', 'william123', 'william123'),
(10, 'Peter', 'Norvig', 'Male', '[email protected]', '9609362815', 'peter123', 'peter123'),
(11, 'Shraddha', 'Kapoor', 'Female', '[email protected]', '9768946252', 'shraddha123', 'shraddha123');
CREATE TABLE plant(
plant_id VARCHAR(10) PRIMARY KEY,
plant_name VARCHAR(20) NOT NULL,
plant_type VARCHAR(20),
plant_desc TEXT,
soil_type VARCHAR(20)
);
CREATE TABLE medicines(
plant_id VARCHAR(10) NOT NULL,
med_id VARCHAR(10) PRIMARY KEY,
med_name VARCHAR(20) NOT NULL,
med_type VARCHAR(20),
med_cost VARCHAR(20),
med_desc TEXT,
FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON DELETE CASCADE
);
CREATE TABLE method(
method_id VARCHAR(10) PRIMARY KEY,
method_name VARCHAR(20) NOT NULL,
method_type TEXT,
method_desc TEXT
);
INSERT INTO plant VALUES
("P001","Tomato","Food crop","Requires 10 months to grow","Red loam soil"),
("P002","Sugarcane","Food crop","Requires 1 year to grow","Black soil"),
("P003","Paddy","Feed and Food crop","Requires 4 months to grow","Alluvium soil"),
("P004","Sunflower","Oil crop","Requires 3 months to grow","Sandy loam soil"),
("P005","Cotton","Fiber crop","Requires 5 months to grow","Black soil");
INSERT INTO medicines VALUES
("P001","M001","Tafaban","Insecticide","450rs/l","Chloropyriphos 20% EC"),
("P001","M002","Fame","Insecticide","600rs/30ml","Fluendiamide 39.35% SC"),
("P001","M003","Sectin","Fungicide","450rs/100g","Fenamidone 10% + mancozeb 50% WG"),
("P002","M004","Atrataf","Herbicide","500rs/kg","Atrazine 50% Wp"),
("P002","M005","Tata Metri","Herbicide","1000rs/kg","Metribuzin 70% Wp");
INSERT INTO method VALUES
("Md001","Traditional Method","Check basin method","Used in level fields"),
("Md002","Traditional Method","Furrow Irrigation method","Cheap labour"),
("Md003","Traditional Method","Strip Irrigation method","Used in raise platform"),
("Md004","Modern Method","Sprinkler system","Used in uneven lands"),
("Md005","Modern Method","Drip irrigation method","Used when there is scarcity of water");
ALTER TABLE `userreg`
ADD PRIMARY KEY (`pid`);
ALTER TABLE `userreg`
MODIFY `pid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;
create table manages(
eid integer not null,
ename varchar(20) not null,
plant_id varchar(20) not null,
plant_name varchar(20) not null,
med_id varchar(20) not null,
med_name varchar(20) not null,
salary integer not null
);
INSERT INTO manages VALUES
(1,"ashok","P001","Tomato","M001","Tafaban",500),
(2,"arun","P002","Sugarcane","M002","Fame",600),
(3,"Dinesh","P003","Paddy","M003","Sectin",700),
(4,"Ganesh","P004","Sunflower","M004","Atrataf",550),
(5,"Kumar","P005","Cotton","M005","Tata Metri",800),
(6,"Amit","P001","Tomato","M002","Fame",1000),
(7,"Abbis","P002","Sugarcane","M003","Sectin",1500),
(8,"Tiwary","P003","Paddy","M004","Atrataf",450)
;