Untitled
unknown
plain_text
a month 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