Untitled

 avatar
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