Assignment

Create a table called Employee & execute the following.
Employee(Emp_No,Emp_Name,Designation, Designation_NO, SAL, COMMISSION)

1. 1. 1. Create a user and grant all permissions to the user.

   2. Insert any three records in the employee table contains attributes 

   3. Use rollback

   4. Add primary key constraint and not null constraint to the employee table.

   5. Insert null values to the employee table and verify the result.

Solution

Lets login with the root 

Create a database COMPANY 

Switch to it using the USE command.

$ sudo mysql -u root

mysql> CREATE DATABASE COMPANY;

Query OK, 1 row affected (0.14 sec)

mysql> USE COMPANY;

Database changed

Creating the Employee Table

Within the Database COMPANY create a table Employee as follows. 

Use the SHOW TABLES; command to confirm that the table was indeed created.

mysql> CREATE TABLE COMPANY.Employee (

    ->     Emp_No INT,

    ->     Emp_Name VARCHAR(255),

    ->     Designation VARCHAR(255),

    ->     Designation_NO INT,

    ->     SAL DECIMAL(10, 2),

    ->     COMMISSION DECIMAL(10, 2)

    -> );

Query OK, 0 rows affected (0.91 sec)

mysql> SHOW TABLES;

+-------------------+

| Tables_in_COMPANY |

+-------------------+

| Employee          |

+-------------------+

1 row in set (0.00 sec)

You can verify the structure of this newly created Employee table using the DESC command.

mysql> DESC COMPANY.Employee;

Create a User and Grant Permissions

mysql> CREATE USER IF NOT EXISTS 'dbuser'@'localhost' IDENTIFIED BY 'T0p5E(RET';

mysql> GRANT ALL PRIVILEGES ON COMPANY.Employee TO 'dbuser'@'localhost';

Now logout 

Login with the new account credentials. 

Press Ctrl+D to logout.

 Command to login with a new user account is shown below.

Now you have successfully logged with your new account. 

Change the current database to COMPANY database using USE command. 

Now we will illustrate how to insert records and also the COMMIT and ROLLBACK facilities.

-- Change the current database to COMPANY

mysql> USE COMPANY;

Database changed

mysql> SELECT * FROM Employee;

Query OK, 0 rows affected (0.00 sec)

-- START A TRANSACTION

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Employee (Emp_No, Emp_Name, Designation, Designation_NO, SAL, COMMISSION)

    -> VALUES (1, 'XYZ', 'Manager', NULL, 5000.00, 1000.00);

Query OK, 1 row affected (0.00 sec)

-- COMMIT DATABASE, db CONTENTS ARE WRITTEN TO THE DISK

mysql> COMMIT;

Query OK, 0 rows affected (0.06 sec)

-- DISPLAY TABLE CONTENTS

mysql> SELECT * FROM Employee;

+-------+---------------+---------+------------+---------+------------+

| Emp_No | Emp_Name         | Designation     | Designation_NO | SAL     | COMMISSION |

+-------+---------------+---------+------------+---------+------------+

|     1 | XYZ | Manager |       NULL | 5000.00 |    1000.00 |

+-------+---------------+---------+------------+---------+------------+

1 row in set (0.00 sec)

-- START ANOTHER TRANSACTION

mysql> START TRANSACTION;

-- INSERT MORE RECORDS

mysql> INSERT INTO Employee (Emp_No, Emp_Name, Designation, Designation_NO, SAL, COMMISSION)

VALUES (2, 'PQR', 'Developer', 1, 4000.00, NULL);

mysql> INSERT INTO Employee (Emp_No, Emp_Name, Designation, Designation_NO, SAL, COMMISSION)

VALUES (3, 'ABC', 'Salesperson', 2, 3000.00, 500.00);

mysql> SELECT * FROM Employee;

+-------+---------------+-------------+------------+---------+------------+

| Emp_No | Emp_Name         | Designation         | Designation_NO | SAL     | COMMISSION |

+-------+---------------+-------------+------------+---------+------------+

|     1 | XYZ | Manager     |       NULL | 5000.00 |    1000.00 |

|     2 | PQR    | Developer   |          1 | 4000.00 |       NULL |

|     3 | ABC   | Salesperson |          2 | 3000.00 |     500.00 |

+-------+---------------+-------------+------------+---------+------------+

3 rows in set (0.00 sec)

mysql> DELETE FROM Employee where Emp_Name = 'XYZ';

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Employee;

+-------+-------------+-------------+------------+---------+------------+

| Emp_No | Emp_Name       | Designation         | Designation_NO | SAL     | COMMISSION |

+-------+-------------+-------------+------------+---------+------------+

|     2 | PQR  | Developer   |          1 | 4000.00 |       NULL |

|     3 | ABC | Salesperson |          2 | 3000.00 |     500.00 |

+-------+-------------+-------------+------------+---------+------------+

2 rows in set (0.00 sec)

-- ROLLBACK 2 INSERTS AND 1 DELETE OPERATIONS

mysql> ROLLBACK;

Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM Employee;

+-------+---------------+---------+------------+---------+------------+

| Emp_No | Emp_Name         | Designation     | Designation_NO | SAL     | COMMISSION |

+-------+---------------+---------+------------+---------+------------+

|     1 | XYZ | Manager |       NULL | 5000.00 |    1000.00 |

+-------+---------------+---------+------------+---------+------------+

1 row in set (0.00 sec)