INS UPD DEL SQL

 

EX 2:

Create a table called Employee that contain attributes EMPNO,ENAME,JOB, MGR,SAL & execute the following. 

 1. Add a column commission with domain to the Employeetable. 

 2. Insert any five records into the table. 

 3. Update the column details of job 

 4. Rename the column of Employ table using alter command. 

 5. Delete the employee whose Empno is 105.

 

Create Table Employee:

CREATE TABLE Employee2 (

    EMPNO INT PRIMARY KEY,

    ENAME VARCHAR(50),

    JOB VARCHAR(50),

    MGR INT,

    SAL DECIMAL(10,2)

);

 Add Column COMMISSION with Domain
(Example: Commission must be numeric and greater than or equal to 0)

 Insert Five Records

INSERT INTO Employee2 VALUES (101, 'John', 'Manager', 100, 50000, 5000);

INSERT INTO Employee2 VALUES (102, 'Smith', 'Clerk', 101, 25000, 2000);

INSERT INTO Employee2 VALUES (103, 'David', 'Analyst', 101, 40000, 3000);

INSERT INTO Employee2 VALUES (104, 'Sara', 'Salesman', 102, 30000, 2500);

INSERT INTO Employee2 VALUES (105, 'Linda', 'Clerk', 101, 22000, 1500);

 Update the Column Details of JOB

(Example: Change JOB of employee 102)

UPDATE Employee2

SET JOB = 'Senior Clerk'

WHERE EMPNO = 102;

Rename Column of Employee Table Using ALTER

(Example: Rename ENAME to EMP_NAME)

ALTER TABLE Employee2

RENAME COLUMN ENAME TO EMP_NAME;

 Delete Employee Whose EMPNO is 105

DELETE FROM Employee2

WHERE EMPNO = 105;