-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDatabaseSQLFile.sql
More file actions
233 lines (199 loc) · 10.5 KB
/
DatabaseSQLFile.sql
File metadata and controls
233 lines (199 loc) · 10.5 KB
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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
mysql> create database SHsystem;
Query OK, 1 row affected (0.02 sec)
mysql> use shsystem;
Database changed
mysql> show tables;
Empty set (0.02 sec)
mysql> create table HOD(name varchar(30) not null, username varchar(30) not null, password varchar(30) not null);
Query OK, 0 rows affected (0.10 sec)
mysql> desc HOD;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(30) | NO | | NULL | |
| username | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from hod;
Empty set (0.01 sec)
mysql> drop table HOD;
Query OK, 0 rows affected (0.07 sec)
mysql> create table HOD(HODId int auto_increment primary key,name varchar(30) not null, username varchar(30) not null, password varchar(30) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc HOD;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| HODId | int | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| username | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> create table complaints( complaintId int not null, empId int,complain
tType varchar(30), engId int, status varchar(12),dataRaised date, dataResolv
ed date);
Query OK, 0 rows affected (0.04 sec)
mysql> desc complaints;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| complaintId | int | NO | | NULL | |
| empId | int | YES | | NULL | |
| complaintType | varchar(30) | YES | | NULL | |
| engId | int | YES | | NULL | |
| status | varchar(12) | YES | | NULL | |
| dataRaised | date | YES | | NULL | |
| dataResolved | date | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> use SHsystem;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_shsystem |
+--------------------+
| complaints |
| hod |
+--------------------+
2 rows in set (0.03 sec)
mysql> desc complaints;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| complaintId | int | NO | | NULL | |
| empId | int | YES | | NULL | |
| complaintType | varchar(30) | YES | | NULL | |
| engId | int | YES | | NULL | |
| status | varchar(12) | YES | | NULL | |
| dataRaised | date | YES | | NULL | |
| dataResolved | date | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
mysql> Create Table Department(deptID int primary key auto_increment, dname
varchar(20) unique not null, location varchar(20) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> desc department;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| deptID | int | NO | PRI | NULL | auto_increment |
| dname | varchar(20) | NO | UNI | NULL | |
| location | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> Create table Employee(EmpId int primary key auto_increment, deptId in
t,Foreign key(deptId) references Department(deptID), name varchar(20) not nu
ll, username varchar(30) not null, password varchar(12) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc Employee;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| EmpId | int | NO | PRI | NULL | auto_increment |
| deptId | int | YES | MUL | NULL | |
| name | varchar(20) | NO | | NULL | |
| username | varchar(30) | NO | | NULL | |
| password | varchar(12) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> Create table Employee(EnggId int primary key auto_increment, name varchar(20) not null,username varchar(30) not null, password varchar(20) not null, type varchar(30) , location varchar(12) not null);
ERROR 1050 (42S01): Table 'employee' already exists
mysql> Create table Engineer(EnggId int primary key auto_increment, name varchar(20) not null,username varchar(30) not null, password varchar(20) not null, type varchar(30) , location varchar(12) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc Engineer;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| EnggId | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| username | varchar(30) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| type | varchar(30) | YES | | NULL | |
| location | varchar(12) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> desc hod;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| HODId | int | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| username | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)
mysql> insert into hod values(11,"Pritam","pritamkumar","Pritam@123");
Query OK, 1 row affected (0.02 sec)
mysql> insert into engineer values(101,"Suraj","surajkumar","Suraj@123","active","Noida");
Query OK, 1 row affected (0.01 sec)
mysql> select * from hod;
+-------+--------+-------------+------------+
| HODId | name | username | password |
+-------+--------+-------------+------------+
| 11 | Pritam | pritamkumar | Pritam@123 |
+-------+--------+-------------+------------+
1 row in set (0.00 sec)
mysql> select * from Engineer;
+--------+-------+------------+-----------+--------+----------+
| EnggId | name | username | password | type | location |
+--------+-------+------------+-----------+--------+----------+
| 101 | Suraj | surajkumar | Suraj@123 | active | Noida |
+--------+-------+------------+-----------+--------+----------+
1 row in set (0.00 sec)
mysql> desc employee;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| EmpId | int | NO | PRI | NULL | auto_increment |
| deptId | int | YES | MUL | NULL | |
| name | varchar(20) | NO | | NULL | |
| username | varchar(30) | NO | | NULL | |
| password | varchar(12) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> insert into employee values(101,11,"suraj","surajkumar","Suraj@123");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shsystem`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptId`) REFERENCES `department` (`deptID`))
mysql> desc department;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| deptID | int | NO | PRI | NULL | auto_increment |
| dname | varchar(20) | NO | UNI | NULL | |
| location | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> insert into department values(1,"HR","Noida");
Query OK, 1 row affected (0.01 sec)
mysql> insert into department values(2,"SSM","New Delhi");
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(101,1,"suraj","surajkumar","Suraj@123");
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(102,2,"rohan","rohankumar","rohan@123");
Query OK, 1 row affected (0.00 sec)
mysql> select* from employee;
+-------+--------+-------+------------+-----------+
| EmpId | deptId | name | username | password |
+-------+--------+-------+------------+-----------+
| 101 | 1 | suraj | surajkumar | Suraj@123 |
| 102 | 2 | rohan | rohankumar | rohan@123 |
+-------+--------+-------+------------+-----------+
2 rows in set (0.00 sec)
mysql> select* from department;
+--------+-------+-----------+
| deptID | dname | location |
+--------+-------+-----------+
| 1 | HR | Noida |
| 2 | SSM | New Delhi |
+--------+-------+-----------+
2 rows in set (0.00 sec)
mysql> select* from employee;
+-------+--------+-------+------------+-----------+
| EmpId | deptId | name | username | password |
+-------+--------+-------+------------+-----------+
| 101 | 1 | suraj | surajkumar | Suraj@123 |
| 102 | 2 | rohan | rohankumar | rohan@123 |
| 103 | 2 | Mohan | mohankumar | Mohan@123 |
+-------+--------+-------+------------+-----------+
3 rows in set (0.01 sec)