Create a table called Employee & execute the following.
Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)
1. Create a user and grant all permissions to theuser.
2. Insert the any three records in the employee table contains attributes
EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback.
Check the result.
3. Add primary key constraint and not null constraint to the employee table.
4. Insert null values to the employee table and verify the result.
mysql> CREATE DATABASE 4thsem;
Query OK, 1 row affected (1.05 sec)
mysql> USE 4thsem;
Database changed
mysql> CREATE TABLE Employee (Empno int(10),Ename varchar(20),job varchar(20),Mgr int(10),Sal int(10),Comm int(10));
Query OK, 0 rows affected, 4 warnings (1.24 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 | |
| Comm | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.45 sec)
mysql> INSERT INTO Employee VALUES (1, 'King', 'ITmanager', 100, 20000, 1000);
Query OK, 1 row affected (0.53 sec)
mysql> INSERT INTO Employee VALUES (5, 'blake', 'IT', 200, 30000, 2000);
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO Employee VALUES (9, 'raj', 'manager', 300, 40000, 3000);
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO Employee VALUES (19, 'clarke', 'Assistant', 400, 50000, 4000);
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO Employee VALUES (25, 'mohan', 'clerk', 500, 60000, 5000);
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM Employee;
+-------+--------+-----------+------+-------+------+
| Empno | Ename | job | Mgr | Sal | Comm |
+-------+--------+-----------+------+-------+------+
| 1 | King | ITmanager | 100 | 20000 | 1000 |
| 5 | blake | IT | 200 | 30000 | 2000 |
| 9 | raj | manager | 300 | 40000 | 3000 |
| 19 | clarke | Assistant | 400 | 50000 | 4000 |
| 25 | mohan | clerk | 500 | 60000 | 5000 |
+-------+--------+-----------+------+-------+------+
5 rows in set (0.00 sec)
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'impact';
Query OK, 0 rows affected (0.07 sec)
mysql> GRANT ALL PRIVILEGES ON test.* TO 'newuser'@'localhost';
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT CREATE ON test3.* TO 'newuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT CREATE ON 4thsem.* TO 'newuser'@'localhost';
Query OK, 0 rows affected (0.04 sec)
mysql> GRANT USAGE ON *.* TO 'newuser'@'localhost';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL PRIVILEGES ON 4thsem.* TO 'newuser'@'localhost';
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.09 sec)
mysql> GRANT ALL PRIVILEGES ON 4thsem.Employee TO 'newuser'@'localhost';
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW GRANTS FOR 'newuser'@'localhost';
+----------------------------------------------------------------------+
| Grants for newuser@localhost |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `newuser`@`localhost` |
| GRANT ALL PRIVILEGES ON `4thsem`.* TO `newuser`@`localhost` |
| GRANT ALL PRIVILEGES ON `test`.* TO `newuser`@`localhost` |
| GRANT CREATE ON `test3`.* TO `newuser`@`localhost` |
| GRANT ALL PRIVILEGES ON `4thsem`.`Employee` TO `newuser`@`localhost` |
+----------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> CREATE USER 'xxx'@'localhost' IDENTIFIED BY 'impact';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON company.Employee TO 'xxx'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'xxx'@'localhost';
+-------------------------------------------------------------------+
| Grants for xxx@localhost |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `xxx`@`localhost` |
| GRANT ALL PRIVILEGES ON `company`.`Employee` TO `xxx`@`localhost` |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> SAVEPOINT p1;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM Employee WHERE Empno='1';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM Employee;
+-------+--------+-----------+------+-------+------+
| Empno | Ename | job | Mgr | Sal | Comm |
+-------+--------+-----------+------+-------+------+
| 5 | blake | IT | 200 | 30000 | 2000 |
| 9 | raj | manager | 300 | 40000 | 3000 |
| 19 | clarke | Assistant | 400 | 50000 | 4000 |
| 25 | mohan | clerk | 500 | 60000 | 5000 |
+-------+--------+-----------+------+-------+------+
4 rows in set (0.00 sec)
mysql> ROLLBACK TO p1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM Employee;
+-------+--------+-----------+------+-------+------+
| Empno | Ename | job | Mgr | Sal | Comm |
+-------+--------+-----------+------+-------+------+
| 1 | King | ITmanager | 100 | 20000 | 1000 |
| 5 | blake | IT | 200 | 30000 | 2000 |
| 9 | raj | manager | 300 | 40000 | 3000 |
| 19 | clarke | Assistant | 400 | 50000 | 4000 |
| 25 | mohan | clerk | 500 | 60000 | 5000 |
+-------+--------+-----------+------+-------+------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE Employee ADD PRIMARY KEY(Empno);
Query OK, 0 rows affected (1.86 sec)
mysql> DESC Employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Empno | int | NO | PRI | NULL | |
| Ename | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| Mgr | int | YES | | NULL | |
| Sal | int | YES | | NULL | |
| Comm | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
mysql> ALTER TABLE Employee MODIFY COLUMN Mgr INT(10) NOT NULL;
Query OK, 0 rows affected, 1 warning (1.13 sec)
mysql> DESC Employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Empno | int | NO | PRI | NULL | |
| Ename | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| Mgr | int | NO | | NULL | |
| Sal | int | YES | | NULL | |
| Comm | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> INSERT INTO Employee VALUES (8, 'ram', 'ITlead', 10000, 30000, NULL);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM Employee;
+-------+--------+-----------+-------+-------+------+
| Empno | Ename | job | Mgr | Sal | Comm |
+-------+--------+-----------+-------+-------+------+
| 1 | King | ITmanager | 100 | 20000 | 1000 |
| 5 | blake | IT | 200 | 30000 | 2000 |
| 8 | ram | ITlead | 10000 | 30000 | NULL |
| 9 | raj | manager | 300 | 40000 | 3000 |
| 19 | clarke | Assistant | 400 | 50000 | 4000 |
| 25 | mohan | clerk | 500 | 60000 | 5000 |
+-------+--------+-----------+-------+-------+------+
6 rows in set (0.00 sec)