Welcome to Database Laboratory
Welcome to Database Laboratory
Assignment 3: Create Table with primary key and foreign key constraints for assignment no 2 and apply DCL, DDL and DML
Assignment
Create a table called Employee & execute the following.
Employee(Emp_No,Emp_Name,Designation, Designation_NO, SAL, COMMISSION)
Create a user and grant all permissions to the user.
Insert any three records in the employee table contains attributes
Use rollback
Add primary key constraint and not null constraint to the employee table.
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
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;
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)