Experiment No: 1
1) Create a table Employee and Execute the following.
Employee (Empno ,ename,job,manager_no,sal,commission)
a) Create a user and grant all permissions to the user.
b) Insert the any three records in the employee table contains
attributes
EMPNO,ENAME,JOB,MANAGER_NO,SAL,COMMISSION
and use rollback. Check the result.
c) Add primary key constraint and not null constraint to the employee
table.
d) Insert null values to the employee table and verify the result.
SOLUTION :
a) Create a user and grant all permissions to the user.
Syntax :
SQL > create user username identified by password;
Example :
SQL > create user c##ABC identified by c##DEF;
SQL > user created.
Grant the permission :
Syntax :
SQL > grant resource,dba to username;
Example :
SQL > grant resource,dba to c##ABC;
b) Insert the any three records in the employee table contains attributes
EMPNO,ENAME,JOB,MANAGER_NO,SAL,COMMISSION and use
rollback. Check the result.
SQL> create table employee13(Empno number, Ename varchar2(20),job
varchar2(20),manager_no number, Sal number ,commission number);
SQL > TABLE CREATED.
SQL > desc employee13;
Name Null? Type
Empno number
Ename varchar2(20)
job varchar2(20)
Manager_no number
Sal number
commission number
SQL > insert into employee13 values(101,’king’,’ITmanager’,’100’,’20000’,’3000’);
SQL > 1 ROW CREATED
SQL > insert into employee13 values(102,’karan’,’senior manager’,’200’,’30000’,’4000’);
SQL > 1 ROW CREATED
SQL > insert into employee13 values(103,’manasa’,’manager’,’300’,’40000’,’5000’);
SQL > 1 ROW CREATED
SQL > select *from employee13;
Empno Ename job Manager_n Sal commission
o
101 King IT manager 100 20000 3000
102 Karan Senior 200 30000 4000
manager
103 Manasa Manager 300 40000 5000
SQL> commit;
SQL > Commit complete.
SQL> insert into employee14 values(104,’divya’,’ITmanager’,’400’,’50000’,’1000’);
SQL > 1 row created.
SQL> select *from employee14;
Empno Ename job Mgr Sal commission
101 King IT manager 100 20000 3000
102 Karan Senior manager 200 30000 4000
103 Manasa Manager 300 40000 5000
104 Divya IT manager 400 50000 1000
SQL> rollback;
SQL > Rollback complete.
SQL> select *from employee14;
Empno Ename job Manager_n Sal commission
o
101 King IT manager 100 20000 3000
102 Karan Senior 200 30000 4000
manager
103 Manasa Manager 300 40000 5000
c) Add primary key constraint and not null constraint to the
employeetable.
SQL> alter table employee14 modify(empno number
primary key, ename varchar2(10) not null);
SQL> Table altered.
SQL> desc employee14;
Name Null? Type
Empno NOT NULL number
Ename NOT NULL varchar2(20)
job varchar2(20)
Manager_no number
Sal number
commission number
d) Insert null values to the employee table and verify the result.
SQL> insert into employee14 values(105,'mohith','salesman',5678,null,50);
SQL > 1 row created.
SQL> insert into employee14 values(104,'dhanush','electrician',6678,null,100);
SQL > 1 row created.
SQL> select *from employee14;
Empno Ename job Mgr Sal commission
101 King IT manager 100 20000 3000
102 Karan Senior manager 200 30000 4000
103 Manasa Manager 300 40000 5000
105 mohith salesman 5678 50
104 dhanush electrician 6678 100
Experiment No: 2
2) Create A Table Called Employee With The Following Structure.
Name Type
Empno Number
Ename Varchar2(10)
Job Varchar2(10)
Mgr Number
Sal Number
a. Add a column commission with domain to the Employee table.
b. Insert any five records into the table.
c. Update the column details of job
d. Rename the column of Employ table using alter command.
e. Delete the employee whose Empno is 105.
SOLUTION :
SQL > create table employee12 (Empno number, Ename varchar2(20), job
varchar2(20),Mgr number, Sal number);
SQL > TABLE CREATED.
SQL > desc employee12;
Name Null? Type
Empno number
Ename varchar2(20)
job varchar2(20)
Mgr number
Sal number
a) Add a column commission with domain to the Employee table.
SQL > alter table employee12 add commission number;
SQL > TABLE ALTERED.
Name Null? Type
Empno number
Ename varchar2(20)
job varchar2(20)
Mgr number
Sal number
commission number
b) Insert any five records into the table.
SQL > insert into employee12 values(101,’king’,’ITmanager’,’100’,’20000’,’3000’);
SQL > 1 ROW CREATED
SQL > insert into employee12 values(102,’karan’,’senior
manager’,’200’,’30000’,’4000’);
SQL > 1 ROW CREATED
SQL > insert into employee12 values(103,’manasa’,’manager’,’300’,’40000’,’5000’);
SQL > 1 ROW CREATED
SQL > insert into employee12 values(104,’divya’,’ITmanager’,’400’,’50000’,’1000’);
SQL > 1 ROW CREATED
SQL > insert into employee12 values (105,’varsha’,’manager’,’500’,’60000’,’2000’);
SQL > 1 ROW CREATED
SQL > select *from employee12;
Empno Ename job Mgr Sal commission
101 King IT manager 100 20000 3000
102 Karan Senior manager 200 30000 4000
103 Manasa Manager 300 40000 5000
104 Divya IT manager 400 50000 1000
105 varsha Manager 500 60000 2000
c) Update the column details of job.
SQL > update employee12 set job=’manager’ where Empno=102;
SQL > 1 ROW UPDATED.
Empno Ename job Mgr Sal commission
101 King IT manager 100 20000 3000
102 Karan manager 200 30000 4000
103 Manasa Manager 300 40000 5000
104 Divya IT manager 400 50000 1000
105 varsha Manager 500 60000 2000
d) Rename the column of Employ table using alter command.
SQL > alter table employee12 rename column Ename to employeename;
SQL > TABLE ALTERED.
Empno employeename job Mgr Sal commission
101 King IT manager 100 20000 3000
102 Karan manager 200 30000 4000
103 Manasa Manager 300 40000 5000
104 Divya IT manager 400 50000 1000
105 varsha Manager 500 60000 2000
e) Delete the employee whose Empno is 105.
SQL > delete from employee12 where Empno=105;
SQL > ROWS DELETED.
Empno employeename job Mgr Sal commission
101 King IT manager 100 20000 3000
102 Karan manager 200 30000 4000
103 Manasa Manager 300 40000 5000
104 Divya IT manager 400 50000 1000
Experiment No: 3
3) Queries using aggregate functions(COUNT,AVG,MIN,MAX,SUM),Group
by,Orderby.
Employee(E_id, E_name, Age, Salary)
1. Create Employee table containing all Records E_id, E_name, Age, Salary.
2. Count number of employee names from employee table
3. Find the Maximum age from employee table.
4. Find the Minimum age from employee table.
5. Find salaries of employee in Ascending Order.
6. Find grouped salaries of employees.
SOLUTION :
a) Create Employee table containing all Records E_id, E_name, Age, Salary.
SQL > create table employee11 (Eid number, Ename varchar2(20), age number, Salary
number);
SQL > TABLE CREATED.
SQL > DESC EMPLOYEE11;
Name Null? Type
Eid number
Ename varchar2(20)
Age number
salary number
b) Count number of employee names from employeetable.
SQL > insert into employee11 values(101,’king’,’10’,’20000’);
SQL > 1 ROW CREATED
SQL > insert into employee11 values(102,’karan’,’20’,’30000’);
SQL > 1 ROW CREATED
SQL > insert into employee11 values(103,’manasa’,’30’,’40000’);
SQL > 1 ROW CREATED
SQL > insert into employee11 values(104,’divya’,’40’,’50000’);
SQL > 1 ROW CREATED
SQL > insert into employee11 values (105,’varsha’,’50’,’60000’);
SQL > 1 ROW CREATED
SQL > select *from employee11;
Eid Ename age Salary
101 King 10 20000
102 Karan 20 30000
103 Manasa 30 40000
104 Divya 40 50000
105 varsha 50 60000
SQL > select count(Ename) from employee11;
Count(Ename)
5
c) Find the Maximum age from employee table.
SQL> select max(age) from employee11;
Max(age)
50
d) Find the Minimum age from employeetable.
SQL> select min(age) from employee11;
Min(Age)
10
Display the Sum of age employeetable.
SQL> select sum(age) from employee11;
Sum(Age)
150
Display the Average of age from Employeetable.
SQL> select avg(age) from employee11;
Average(Age)
30
e) Find salaries of employee in Ascending Order.
SQL> select ename,salary from employee11 order by salary;
Ename Salary
King 20000
Karan 30000
Manasa 40000
Divya 50000
varsha 60000
f) Find grouped salaries of employees.
SQL> select salary from employee11 group by salary;
Salary
20000
30000
40000
50000
60000
g) Find salaries of employee in Descending Order.
SQL> select ename,salary from employee11 order by salary desc;
Salary
60000
50000
40000
30000
20000
Experiment No: 4
4) Create a row level trigger for the customers table that would fire for INSERT or
UPDATE or DELETE operations performed on the CUSTOMERS table. This
trigger will display the salary difference between the old & new Salary.
CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)
CUSTOMER TABLE
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- -------------------- ----------
1 Ramesh 23 Allahabad 20000
2 Suresh 22 Kanpur 22000
3 Mahesh 24 Ghaziabad 24000
4 Chandan 25 Noida 26000
5 Alex 21 Paris 28000
6 Sunitha 20 Delhi 30000
SOLUTION:
CREATE TABLE CUSTOMERS(ID NUMBER(20),NAME VARCHAR(20),AGE
NUMBER(20),ADDRESS VARCHAR(20),SALARY NUMBER(20));
SQL> INSERT INTO CUSTOMERS VALUES(1,'Ramesh',23,'Allahabad',20000);
1 row created.
SQL> INSERT INTO CUSTOMERS VALUES(2,'Suresh',22,'Kanpur',22000);
1 row created.
SQL> INSERT INTO CUSTOMERS VALUES(3,'Mahesh',24,'Ghaziabad',24000);
1 row created.
SQL> INSERT INTO CUSTOMERS VALUES(4,'Chandan',25,'Noida',26000);
1 row created.
SQL> INSERT INTO CUSTOMERS VALUES(5,'Alex',21,'Paris',28000);
1 row created.
SQL> INSERT INTO CUSTOMERS VALUES(6,'Sunita',20,'Delhi',30000);
1 row created.
SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE TRIGGER DISPLAY_SALARY_CHANGES23
BEFORE DELETE OR INSERT OR UPDATE ON CUSTOMERS
FOR EACH ROW
WHEN (NEW.ID>0)
DECLARE SAL_DIFF NUMBER;
BEGIN
SAL_DIFF:=:NEW.SALARY-:OLD.SALARY;
DBMS_OUTPUT.PUT_LINE('OLD SALARY:'||:OLD.SALARY);
DBMS_OUTPUT.PUT_LINE('NEW SALARY:'||:NEW.SALARY);
DBMS_OUTPUT.PUT_LINE('SALARY DIFFERENCE:'||SAL_DIFF);
END;
/
SQL> Trigger created.
SQL> INSERT INTO CUSTOMERS (id,name,age,address,salary) VALUES (7,
'Kriti',19,Bangalore,7500.00 );
SQL> INSERT INTO CUSTOMERS (id,name,age,address,salary) VALUES (7,
'Kriti',19,'Bangalore',7500.00 );
OLD SALARY:
NEW SALARY:7500
SALARY DIFFERENCE:
1 row created.
SQL> UPDATE customers SET salary = salary + 500 WHERE id = 2;
OLD SALARY:22000
NEW SALARY:22500
SALARY DIFFERENCE:500
1 row updated.
