DBMS 1

CREATE TABLE Employee (
EMPNO INT,
ENAME VARCHAR(50),
JOB VARCHAR(50),
MANAGER_NO INT,
SAL DECIMAL(10, 2),
COMMISSION DECIMAL(10, 2)
);

1. Create a user and grant all permissions to theuser.
   -- Create a new user
   CREATE USER myuser IDENTIFIED BY password;
   -- Grant all permissions to the new user on the Employee table
   GRANT ALL PRIVILEGES ON Employee TO myuser;

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.
   -- Insert three records into the Employee table
   INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
   VALUES
   (1, 'John Doe', 'Manager', 1001, 5000.00, 1000.00),
   (2, 'Jane Smith', 'Analyst', 1001, 4000.00, NULL),
   (3, 'Mike Johnson', 'Clerk', 1002, 3000.00, 500.00);
   -- Use ROLLBACK to undo the changes (not committing the transaction)
   ROLLBACK;

3. Add primary key constraint and not null constraint to the employee table.
   -- Add Primary Key constraint on EMPNO
   ALTER TABLE Employee
   ADD CONSTRAINT PK_Employee PRIMARY KEY (EMPNO);
   -- Add NOT NULL constraint to EMPNO column
   ALTER TABLE Employee
   MODIFY EMPNO INT NOT NULL;
   -- Add NOT NULL constraint to ENAME column
   ALTER TABLE Employee
   MODIFY ENAME VARCHAR(50) NOT NULL;
   -- Add NOT NULL constraint to JOB column
   ALTER TABLE Employee
   MODIFY JOB VARCHAR(50) NOT NULL;

4. Insert null values into the Employee table and verify the result. (Note: Since we added NOT
   NULL constraints, inserting null values will result in an error):
   -- Try to insert a record with NULL values into the Employee table
   INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
   VALUES (4, NULL, 'Intern', NULL, NULL, NULL);