Example HTML page

MySQL Assignment 5

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest