Hi,
The following questions are based ALTER statement .
1.Create a table with name “employee”. The table having two attributes “empid” accepts integers and “name” accepts a string of maximum length ’10’.
2.Print the schema of your table. It should be something like this.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3.Insert the following records in to the table.
1000,’sudhakar’
1001,’rathnam’
4.Print the total data present in the table. your output should be something like this.
+-------+----------+
| empid | name |
+-------+----------+
| 1000 | sudhakar |
| 1001 | rathnam |
+-------+----------+
5.Print the ’empid’ column in descending order. your output should be something like this.
+-------+
| empid |
+-------+
| 1001 |
| 1000 |
+-------+
6.Add a new column ‘description’ to the above table and print the schema of the table. your output should be something like this. The datatype of the description is ‘text’ type.
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| empid | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7.Insert one more record with name “Ramana”, print the data in the table.
1003 : ” The Full name of the Employee is T. V Rathnam and His designation is Assistant Professor”.
8.Print the description of employee 1003.
9.Insert a new field ‘gender’ in between ‘name’ and ‘description’ attributes. This field accept only one character.
print the schema and it is something like this.
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| empid | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
10.Insert a new field “Branch” as a first column. It should accepts 10 variable length characters.
Print the schema now and it is something like this.
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| branch | varchar(10) | YES | | NULL | |
| empid | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
11.Change the definition of the ‘name’. i.e. increase size limit of the name up to ’20’ characters.
Print the schema again and it is something like this.
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| branch | varchar(10) | YES | | NULL | |
| empid | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
12.Remove the column “branch” from the table.
13.Add two attributes “location” and “designation” at the end. Do this in a single statement. Your schema should be shown here.
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| empid | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
| description | text | YES | | NULL | |
| location | varchar(10) | YES | | NULL | |
| designation | varchar(10) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
14.Now drop the columns “location” and “designation”.
15.Update the ’empid’ with primary key.
16.Rename the ’empid’ to ’employee_id’ . print the schema and it should be like this.
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
| description | text | YES | | NULL | |
| location | varchar(10) | YES | | NULL | |
| designation | varchar(10) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
17.Change the table name ’employee’ to ’employee_details’.
18.Print the details of ’employee’ table.
19.Print the details of ’employee_details’.
20.Delete the complete data present in the table, but not the schema. For example if you use
select * from employee_table
it prints empty set.
when you print the schema, your output should be something like this.
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
| description | text | YES | | NULL | |
| location | varchar(10) | YES | | NULL | |
| designation | varchar(10) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
Example HTML page
