Untitled
unknown
plain_text
23 days ago
11 kB
3
Indexable
1. Create a table called Employee & execute the following. Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) 1. Create a user and grant all permissions to the user. 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. 3. Add primary key constraint and not null constraint to the employee table. 4. Insert null values to the employee table and verify the result. SQL> create user jade identified by jade; User created. SQL> grant connect, resource to jade identified by jade; Grant succeeded. SQL> grant create view to jade identified by jade; Grant succeeded. SQL> create table employee(empno int, ename varchar(10), job varchar(10), manager_no int, salary int , comm int); Table created. SQL> insert into employee values (1,'abc','ss',1,25000,3); 1 row created. SQL> create table emp( empno int primary key, ename varchar(10) not null, job varchar(10), manager_n o int, sal int, comm int); Table created. SQL> insert into employee values (1,'abc','ss',1,25000,3); 1 row created. SQL> / insert into emp values (1,'abc','ss',1,25000,3) * ERROR at line 1: ORA-00001: unique constraint (JADE.SYS_C005461) violated SQL> insert into emp values (2,'xyz','os',2,30000,2); 1 row created. SQL> insert into emp values (3,'pqr','cs',1,18000,2); 1 row created. SQL> select * from emp; EMPNO ENAME JOB MANAGER_NO SAL COMM ---------- ---------- ---------- ---------- ---------- ---------- 1 abc ss 1 25000 3 2 xyz os 2 30000 2 3 pqr cs 1 18000 2 SQL> insert into emp values ('&empno','&enma','&job','&ma_no','&sal','&comm'); Enter value for empno: 5 Enter value for enma: ee Enter value for job: op Enter value for ma_no: 2 Enter value for sal: 12000 Enter value for comm: 1 old 1: insert into emp values ('&empno','&enma','&job','&ma_no','&sal','&comm') new 1: insert into emp values ('5','ee','op','2','12000','1') 1 row created. SQL> / Enter value for empno: 4 Enter value for enma: rr Enter value for job: yy Enter value for ma_no: 3 Enter value for sal: 10000 Enter value for comm: old 1: insert into emp values ('&empno','&enma','&job','&ma_no','&sal','&comm') new 1: insert into emp values ('4','rr','yy','3','10000','') 1 row created. SQL> / Enter value for empno: Enter value for enma: Enter value for job: tr Enter value for ma_no: 2 Enter value for sal: 1200 Enter value for comm: old 1: insert into emp values ('&empno','&enma','&job','&ma_no','&sal','&comm') new 1: insert into emp values ('','','tr','2','1200','') insert into emp values ('','','tr','2','1200','') * ERROR at line 1: ORA-01400: cannot insert NULL into ("JADE"."EMP"."EMPNO") SQL> select * from emp; EMPNO ENAME JOB MANAGER_NO SAL COMM ---------- ---------- ---------- ---------- ---------- ---------- 1 abc ss 1 25000 3 2 xyz os 2 30000 2 3 pqr cs 1 18000 2 5 ee op 2 12000 1 4 rr yy 3 10000 SQL> commit; Commit complete. 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 Employee table. 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. SQL> create table employee(empid int primary key, ename varchar(10) not null, mgr_id int, 2 salary int, job varchar(8)); Table created. SQL> alter table employee add comm int; Table altered. SQL> desc employee; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPID NOT NULL NUMBER(38) ENAME NOT NULL VARCHAR2(10) MGR_ID NUMBER(38) SALARY NUMBER(38) JOB VARCHAR2(8) COMM NUMBER(38) SQL> insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm'); Enter value for empid: 1001 Enter value for ename: jay Enter value for mgrid: 123 Enter value for salary: 1200 Enter value for job: os Enter value for comm: 3 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('1001','jay','123','1200','os','3') 1 row created. SQL> / Enter value for empid: 123 Enter value for ename: veer Enter value for mgrid: 123 Enter value for salary: 30000 Enter value for job: manager Enter value for comm: 50 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('123','veer','123','30000','manager','50') 1 row created. SQL> / Enter value for empid: 1002 Enter value for ename: raj Enter value for mgrid: 123 Enter value for salary: 2000 Enter value for job: turner Enter value for comm: 3 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('1002','raj','123','2000','turner','3') 1 row created. SQL> / Enter value for empid: 1003 Enter value for ename: vijay Enter value for mgrid: 123 Enter value for salary: 3200 Enter value for job: operator Enter value for comm: 5 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('1003','vijay','123','3200','operator','5') 1 row created. SQL> / Enter value for empid: 1001 Enter value for ename: f Enter value for mgrid: 123 Enter value for salary: 2300 Enter value for job: ee Enter value for comm: 2 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('1001','f','123','2300','ee','2') insert into employee values ('1001','f','123','2300','ee','2') * ERROR at line 1: ORA-00001: unique constraint (JADE.SYS_C005467) violated SQL> / Enter value for empid: 1004 Enter value for ename: seshu Enter value for mgrid: 123 Enter value for salary: 2100 Enter value for job: analyst Enter value for comm: 4 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('1004','seshu','123','2100','analyst','4') 1 row created. SQL> / Enter value for empid: 1005 Enter value for ename: john Enter value for mgrid: 123 Enter value for salary: 2100 Enter value for job: ttainer Enter value for comm: 2 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('1005','john','123','2100','ttainer','2') 1 row created. SQL> commit; Commit complete. SQL> select * from employee; EMPID ENAME MGR_ID SALARY JOB COMM ---------- ---------- ---------- ---------- -------- ---------- 1001 jay 123 1200 os 3 123 veer 123 30000 manager 50 1002 raj 123 2000 turner 3 1003 vijay 123 3200 operator 5 1004 seshu 123 2100 analyst 4 1005 john 123 2100 ttainer 2 6 rows selected. SQL> update employee set job='trainer' where empid=1005; 1 row updated. SQL> select * from employee; EMPID ENAME MGR_ID SALARY JOB COMM ---------- ---------- ---------- ---------- -------- ---------- 1001 jay 123 1200 os 3 123 veer 123 30000 manager 50 1002 raj 123 2000 turner 3 1003 vijay 123 3200 operator 5 1004 seshu 123 2100 analyst 4 1005 john 123 2100 trainer 2 6 rows selected. SQL> desc employee Name Null? Type ----------------------------------------- -------- ---------------------------- EMPID NOT NULL NUMBER(38) ENAME NOT NULL VARCHAR2(10) MGR_ID NUMBER(38) SALARY NUMBER(38) JOB VARCHAR2(8) COMM NUMBER(38) SQL> ALTER TABLE employee RENAME COLUMN MGR_ID to manager; Table altered. SQL> desc employee Name Null? Type ----------------------------------------- -------- ---------------------------- EMPID NOT NULL NUMBER(38) ENAME NOT NULL VARCHAR2(10) MANAGER NUMBER(38) SALARY NUMBER(38) JOB VARCHAR2(8) COMM NUMBER(38) SQL> insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm'); Enter value for empid: 105 Enter value for ename: sam Enter value for mgrid: 123 Enter value for salary: 2100 Enter value for job: fun Enter value for comm: 1 old 1: insert into employee values ('&empid','&ename','&mgrid','&salary','&job','&comm') new 1: insert into employee values ('105','sam','123','2100','fun','1') 1 row created. SQL> select * from employee; EMPID ENAME MANAGER SALARY JOB COMM ---------- ---------- ---------- ---------- -------- ---------- 1001 jay 123 1200 os 3 123 veer 123 30000 manager 50 1002 raj 123 2000 turner 3 1003 vijay 123 3200 operator 5 1004 seshu 123 2100 analyst 4 1005 john 123 2100 trainer 2 105 sam 123 2100 fun 1 7 rows selected. SQL> delete from employee where empid=105; 1 row deleted. SQL> select * from employee; EMPID ENAME MANAGER SALARY JOB COMM ---------- ---------- ---------- ---------- -------- ---------- 1001 jay 123 1200 os 3 123 veer 123 30000 manager 50 1002 raj 123 2000 turner 3 1003 vijay 123 3200 operator 5 1004 seshu 123 2100 analyst 4 1005 john 123 2100 trainer 2 6 rows selected.
Editor is loading...
Leave a Comment