-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathexercise2
139 lines (104 loc) · 4.75 KB
/
exercise2
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
1.Add a column ‘SSN’ at the first
position in the table EMPLOYEE
mysql> ALTER TABLE EMPLOYEE ADD SSN INT FIRST;
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC EMPLOYEE;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| SSN | int(11) | YES | | NULL | |
| emp_id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| dob | date | YES | | NULL | |
| adhaar_no | int(12) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
2.Change the data type of the column
‘DOB’ in year the table EMPLOYEE
mysql> ALTER TABLE EMPLOYEE MODIFY dob INT;
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC EMPLOYEE;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| SSN | int(11) | YES | | NULL | |
| emp_id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| dob | int(11) | YES | | NULL | |
| adhaar_no | int(12) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
3.Delete the column ‘emp_id’ the table EMPLOYEE.
mysql> ALTER TABLE EMPLOYEE DROP emp_id ;
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc EMPLOYEE;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| SSN | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| dob | int(11) | YES | | NULL | |
| adhaar_no | int(12) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
4.Add the constraint unique for columns ‘SSN, ‘adhar_no’
the table EMPLOYEE
mysql> ALTER TABLE EMPLOYEE ADD CONSTRAINT UNIQUE(SSN,adhaar_no);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc EMPLOYEE;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| SSN | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| dob | int(11) | YES | | NULL | |
| adhaar_no | int(12) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
5.Delete
the NULL constraint of ‘age’ in the STUDENT table.
mysql> ALTER TABLE STUDENT MODIFY AGE INT NULL;
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> DESC STUDENT;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ROLL_NO | int(11) | NO | PRI | NULL | |
| NAME | varchar(20) | NO | | NULL | |
| AGE | int(11) | YES | | NULL | |
| GENDER | char(1) | YES | | M | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
6.Change
the table name EMPLOYEE as PERSON
mysql> ALTER TABLE EMPLOYEE RENAME TO PERSON;
Query OK, 0 rows affected (0.10 sec)
mysql> DESC PERSON;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| SSN | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| dob | int(11) | YES | | NULL | |
| adhaar_no | int(12) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
7.Delete
the duplicate table OF EMPLOYEE
mysql> DROP TABLE EMPLOYEE1;
Query OK, 0 rows affected (0.21 sec)
mysql> DESC EMPLOYEE1;
ERROR 1146 (42S02): Table 'Aswin.EMPLOYEE1' doesn't exist
//VERIFIED