lab1

1-- Create the Employee table

CREATE TABLE Employee (

 EMPNO INT,

 ENAME VARCHAR(50),

 JOB VARCHAR(50),

 MANAGER_NO INT,

 SAL DECIMAL(10,2),

 COMMISSION DECIMAL(10,2)

);

-- Create a user

CREATE USER theuser IDENTIFIED BY password;

-- Grant all permissions to the user

GRANT ALL PRIVILEGES ON Employee TO theuser;

Note: This series of SQL commands should accomplish what you've asked for. Make sure to 

replace 'password' with a secure password for the user 'theuser'.

2- Insert three records into the Employee table

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)

VALUES

 (1, 'John Doe', 'Manager', NULL, 50000.00, 5000.00),

 (2, 'Jane Smith', 'Developer', 1, 40000.00, NULL),

 (3, 'Michael Johnson', 'Salesperson', 1, 30000.00, 2000.00);

-- Rollback to undo the inserts

ROLLBACK;

3- Add Primary Key constraint to EMPNO column

ALTER TABLE Employee

DBMS Manual

Dept. of CSE, RNSIT Page 2

ADD CONSTRAINT PK_Employee_EMPNO PRIMARY KEY (EMPNO);

-- Add Not Null constraints

ALTER TABLE Employee

ALTER COLUMN EMPNO INT NOT NULL,

ALTER COLUMN ENAME VARCHAR(50) NOT NULL,

ALTER COLUMN JOB VARCHAR(50) NOT NULL,

ALTER COLUMN SAL DECIMAL(10,2) NOT NULL;

4-- Inserting null values

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)

VALUES

 (NULL, 'Chris Brown', NULL, NULL, 45000.00, NULL);

-- Selecting all records to verify

SELECT * FROM Employee