Database Management Systems Lab BCS403

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 table employee(empno number, ename varchar2(10),job varchar2(10), mgr number, sal number,
commission number);

Table created.

SQL> desc employee;

Name Null? Type

EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER
SAL NUMBER
COMMISSION NUMBER

1. Create a user and grant all permissions to the user.

Create user query:

CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;

Example: SQL> create user c##scott identified by tiger;

Provide roles:

GRANT RESOURCE,DBA TO <USER NAME>;

Example: SQL>grant resource,dba to c##scott;

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.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------- -------------------- ----------------- --------------------- ----------- --------------------
101 abhi manager 1234 1100 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55

Dept. of CSE, RLJIT Page 1

 Database Management Systems Lab BCS403

SQL> insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission);

Enter value for empno: 105

Enter value for ename: aravind
Enter value for job: salesman
Enter value for manager_no: 5678
Enter value for sal: 5000
Enter value for commission: 50

old 1: insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission)

new 1: insert into employee values(105,'aravind','salesman',5678,5000,50)

1 row created.

SQL> rollback;

Rollback complete.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------- -------------------- ----------------- --------------------- ----------- --------------------
101 abhi manager 1234 1100 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55

3. Add primary key constraint and not null constraint to the employee table.

SQL> alter table employee modify(empno number primary key, ename varchar2(10) not null);

Table altered.

SQL> desc employee;

Name Null? Type

EMPNO NOTNUL NUMBER

ENAME NOTNUL VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER
SAL NUMBER
COMMISSION NUMBER

4. Insert null values to the employee table and verify the result.

SQL> insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission);

Dept. of CSE, RLJIT Page 2

 Database Management Systems Lab BCS403

Enter value for empno: 105

Enter value for ename: mohith
Enter value for job: salesman
Enter value for manager_no: 5678
Enter value for sal: null
Enter value for commission: 50

old 1: insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission)

new 1: insert into employee values(105,'mohith','salesman',5678,null,50)
insert into employee values(105,'mohith','salesman',5678,null,50)
*

Dept. of CSE, RLJIT Page 3

 Database Management Systems Lab BCS403

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(empno number,ename varchar2(10),job varchar2(10), mgr number,sal number);

Table created.

SQL> desc employee;

Name Null? Type

EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER
SAL NUMBER

1. Add a column commission with domain to the Employee table.

SQL> alter table employee add(commission number);

Table altered.

SQL> desc employee;

Name Null? Type

------------------------------------------ ------------- ----------------------
EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER
SAL NUMBER
COMMISSION NUMBER

2. Insert any five records into the table.

SQL> insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission');

Enter value for empno: 101

Enter value for ename: abhi
Enter value for job: manager
Enter value for mgr: 1234
Enter value for sal: 10000
Enter value for commission:70

old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')

new 1: insert into employee values(101,'abhi','manager',1234,10000,'70')

Dept. of CSE, RLJIT Page 4

 Database Management Systems Lab BCS403

1 row created.

SQL> /
Enter value for empno: 102
Enter value for ename: rohith
Enter value for job: analyst
Enter value for mgr: 2345
Enter value for sal: 9000
Enter value for commission: 65

old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')

new 1: insert into employee values(102,'rohith','analyst',2345,9000,'65')

1 row created.

SQL> /
Enter value for empno: 103
Enter value for ename: david
Enter value for job: analyst
Enter value for mgr: 3456
Enter value for sal: 9000
Enter value for commission: 65

old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')

new 1: insert into employee values(103,'david','analyst',3456,9000,'65')
1 row created.

SQL> /
Enter value for empno: 104
Enter value for ename: rahul
Enter value for job: clerk
Enter value for mgr: 4567
Enter value for sal: 7000
Enter value for commission: 55

old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')

new 1: insert into employee values(104,'rahul','clerk',4567,7000,'55')

1 row created.

SQL> /
Enter value for empno: 105
Enter value for ename: pramod
Enter value for job: salesman
Enter value for mgr: 5678
Enter value for sal:5000
Enter value for commission: 50
old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')
new 1: insert into employee values(105,'pramod','salesman',5678,5000,'50')

1 row created.

Dept. of CSE, RLJIT Page 5

 Database Management Systems Lab BCS403

SQL> select * from employee;

EMPNO ENAME JOB MGR SAL COMMISSION

------------ ------------ ------------- ------------- ------------ --------------------
101 abhi manager 1234 10000 70
102 rohith analyst 2345 9000 65
103 david analyst 3456 9000 65
104 rahul clerk 4567 7000 55
105 pramod salesman 5678 5000 50

3. Update the column details of job.

SQL> update employee set job='trainee' where empno=103;

1 row updated.

SQL> select * from employee;

EMPNO ENAME JOB MGR SAL COMMISSION

------------ ------------ ------------- ------------- ------------ --------------------
101 abhi manager 1234 10000 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55
105 pramod salesman 5678 5000 50

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

SQL> alter table employee rename column mgr to manager_no;

Table altered.

SQL> desc employee;

Name Null? Type

------------------------------------------ ------------- ----------------------
EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MANAGER_NO NUMBER
SAL NUMBER
COMMISSION NUMBER

5.Delete the employee whose Empno is105.

SQL> delete employee where empno=105;

1 row deleted.

SQL> select * from employee;

Dept. of CSE, RLJIT Page 6

 Database Management Systems Lab BCS403

EMPNO ENAME JOB MGR SAL COMMISSION

------------ ------------ ------------- ------------- ------------ --------------------
101 abhi manager 1234 10000 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55

Dept. of CSE, RLJIT Page 7

 Database Management Systems Lab BCS403

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.

1. Create Employee table containing all Records E_id, E_name, Age, Salary.

SQL>create table emp(eid number, ename varchar2(10),age number, salary number);

Table created.

SQL> desc emp;

Name Null? Type

--------------------------------- --------------- ---------------------------------
EID NUMBER
ENAME VARCHAR2(10)
AGE NUMBER
SALARY NUMBER

2. Count number of employee names from employee table.

SQL>select count(ename) from emp;

COUNT (ENAME)
-------------------------
7

3. Find the Maximum age from employee table.

SQL> select max(age) from emp;

MAX(AGE)
------------------------
44

4. Find the Minimum age from employee table.

SQL> select min(age) from emp;

MIN(AGE)

22

Dept. of CSE, RLJIT Page 8

 Database Management Systems Lab BCS403

5. Find salaries of employee in Ascending Order.

SQL> select ename,salary from emp order by salary;

ENAME SALARY

rohan 6000
alex 7000
shane 8000
abhi 8000
tiger 8000
anu 9000
scott 10000

7 rows selected.

6. Find grouped salaries of employees.

SQL> select ename,salary from emp where age<29 group by ename,salary having salary<10000;

ENAME SALARY
------------- ----------------
alex 7000
anu 9000

Dept. of CSE, RLJIT Page 9

 Database Management Systems Lab BCS403

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)

CUSTOMERS table:

ID NAME AGE ADDRESS SALARY

---------- ----------------- ----------- --------------------- -------------------
1 Alive 24 Khammam 2000
2 Bob 27 Kadappa 3000
3 Catri 25 Guntur 4000
4 Dena 28 Hyderabad 5000
5 Eeshwar 27 Kurnool 6000
6 Farooq 28 Nellur 7000

CREATE OR REPLACE TRIGGER display_salary_changes 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;
/
Trigger created.

Here following two points are important and should be noted carefully:

OLD and NEW references are not available for table level triggers, rather you can use them for record level triggers.

If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can
query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

Above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation
on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE,
which will fire whenever a record will be deleted using DELETE operation on the table.

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create
a new record in the table:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in CUSTOMERS table, above create trigger display_salary_changeswill be fired and it will
display the following result:

Old salary:
New salary: 7500
Salary difference:

Dept. of CSE, RLJIT Page 10

 Database Management Systems Lab BCS403

5. Create cursor for Employee table & extract the values from the table. Declare the variables , Open the cursor &
extract the values from the cursor. Close the cursor. Employee (E_id, E_name, Age, Salary)

EMP_ID ENAME AGE SAL

------------- -------------- -------------- ------------
101 abhi 34 10000
102 rohith 28 9000
103 david 25 9000
104 rahul 35 7000
105 pramod 38 5000

DECLARE
var_record employees%ROWTYPE;
CURSOR cur_test (max_sal NUMBER) IS
SELECT * FROM employees WHERE salary < max_sal;
BEGIN
OPEN cur_test(5800);
LOOP
ETCH cur_test INTO var_record;
EXIT WHEN cur_test%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || var_record.first_name || chr(9)||' salary: ' || var_record.salary);
END LOOP;
CLOSE cur_test;
END;
/

Dept. of CSE, RLJIT Page 11

 Database Management Systems Lab BCS403

6. Write a PL/SQL block of code using parameterized Cursor, that will merge the data available in the newly created
table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the
second table then that data should be skipped.

set autoprint on;

set serveroutput on;
set verify off;
declare
cursor cu1 is
select Roll,Name from Student;
cursor cu2 is
select Roll from CompDep;
rno int;
nm varchar(20);
rno2 int;
begin open cu1;
open cu2;
loop
fetch cu1 into rno,nm;
fetch cu2 into rno2;
exit when cu1%found = false;
if rno2 < > rno then
insert into CompDep values(rno,nm);
end if;
end loop;
close cu1;
close cu2;
end;
/
Output:
SQL> create table CompDep(Roll int,Name varchar(20));
Table created.
SQL> create table Student(Roll int,Name varchar(20));
Table created.
SQL> insert into Student values(1,'a');
1 row created.
SQL> insert into Student values(2,'b');
1 row created.
SQL> insert into Student values(3,'c');
1 row created.
SQL> insert into Student values(4,'d');
1 row created.
SQL> insert into CompDep values(2,'b');
1 row created.
SQL> insert into CompDep values(5,'e');
1 row created.

Dept. of CSE, RLJIT Page 12

 Database Management Systems Lab BCS403

SQL> insert into CompDep values(6,'f');

1 row created.

SQL> @C:\Users\Premraj\Desktop\DBMS\premraj\ass6.txt PL/SQL procedure successfully completed.

SQL> select * from CompDep;

ROLL NAME
---------- --------------------
2 b
5 e
6 f
1 a
2 b
3 c
4 d

7 rows selected.
SQL>

Dept. of CSE, RLJIT Page 13

 Database Management Systems Lab BCS403

7. Install an Open Source NoSQL Data base Mango DB & perform basic CRUD(Create, Read, Update & Delete)
operations. Execute Mango DB basic Queries using CRUD operations.

Dept. of CSE, RLJIT Page 14