Program-2
CREATE A TABLE CALLED EMPLOYEE THAT CONTAIN
ATTRIBUTES EMPNO,ENAME,JOB, MGR,SAL & EXECUTE THE
FOLLOWING.
1. Add a column commission with domain to the Employee table.
2. Insert any five records into the table.
3. Update the column details of job
4. Rename the column of Employ table using alter command.
5. Delete the employee whose Empno is 105.
mysql> CREATE TABLE Employee (Empno int(10),Ename varchar(20),job varchar(20),Mgr int(10),Sal int(10));
Query OK, 0 rows affected, 3 warnings (0.63 sec)
mysql> DESC employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Empno | int | YES | | NULL | |
| Ename | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| Mgr | int | YES | | NULL | |
| Sal | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> INSERT INTO Employee VALUES (1, 'King', 'ITmanager', 100, 20000);
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO Employee VALUES (5, 'blake', 'IT', 200, 30000);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO Employee VALUES (9, 'raj', 'manager', 300, 40000);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO Employee VALUES (19, 'clarke', 'Assistant', 400, 50000);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO Employee VALUES (25, 'mohan', 'clerk', 500, 60000);
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM employee;
+-------+--------+-----------+------+-------+
| Empno | Ename | job | Mgr | Sal |
+-------+--------+-----------+------+-------+
| 1 | King | ITmanager | 100 | 20000 |
| 5 | blake | IT | 200 | 30000 |
| 9 | raj | manager | 300 | 40000 |
| 19 | clarke | Assistant | 400 | 50000 |
| 25 | mohan | clerk | 500 | 60000 |
+-------+--------+-----------+------+-------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE employee ADD commission int(10);
Query OK, 0 rows affected, 1 warning (0.39 sec)
mysql> SELECT * FROM employee;
+-------+--------+-----------+------+-------+------------+
| Empno | Ename | job | Mgr | Sal | commission |
+-------+--------+-----------+------+-------+------------+
| 1 | King | ITmanager | 100 | 20000 | NULL |
| 5 | blake | IT | 200 | 30000 | NULL |
| 9 | raj | manager | 300 | 40000 | NULL |
| 19 | clarke | Assistant | 400 | 50000 | NULL |
| 25 | mohan | clerk | 500 | 60000 | NULL |
+-------+--------+-----------+------+-------+------------+
5 rows in set (0.00 sec)
mysql> UPDATE EMPLOYEE SET JOB='HR' WHERE EMPNO='1';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM employee;
+-------+--------+-----------+------+-------+------------+
| Empno | Ename | job | Mgr | Sal | commission |
+-------+--------+-----------+------+-------+------------+
| 1 | King | HR | 100 | 20000 | NULL |
| 5 | blake | IT | 200 | 30000 | NULL |
| 9 | raj | manager | 300 | 40000 | NULL |
| 19 | clarke | Assistant | 400 | 50000 | NULL |
| 25 | mohan | clerk | 500 | 60000 | NULL |
+-------+--------+-----------+------+-------+------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE Employee RENAME COLUMN Ename TO Employname;
Query OK, 0 rows affected (0.36 sec)
mysql> SELECT * FROM employee;
+-------+------------+-----------+------+-------+------------+
| Empno | Employname | job | Mgr | Sal | commission |
+-------+------------+-----------+------+-------+------------+
| 1 | King | HR | 100 | 20000 | NULL |
| 5 | blake | IT | 200 | 30000 | NULL |
| 9 | raj | manager | 300 | 40000 | NULL |
| 19 | clarke | Assistant | 400 | 50000 | NULL |
| 25 | mohan | clerk | 500 | 60000 | NULL |
+-------+------------+-----------+------+-------+------------+
5 rows in set (0.00 sec)
mysql> DELETE FROM Employee WHERE empno='105';
Query OK, 0 rows affected (0.07 sec)
mysql> SELECT * FROM employee;
+-------+------------+-----------+------+-------+------------+
| Empno | Employname | job | Mgr | Sal | commission |
+-------+------------+-----------+------+-------+------------+
| 1 | King | HR | 100 | 20000 | NULL |
| 5 | blake | IT | 200 | 30000 | NULL |
| 9 | raj | manager | 300 | 40000 | NULL |
| 19 | clarke | Assistant | 400 | 50000 | NULL |
| 25 | mohan | clerk | 500 | 60000 | NULL |
+-------+------------+-----------+------+-------+------------+
5 rows in set (0.00 sec)