The Journey: Learn DBMS through 10 simple programs

EXPERIMENT-1

AIM: SQL to learn DDL commands.

1. Create a relation name EMPLOYEE having the following details:

Fieldname Datatype Size

Empno number 3

Ename varchar2 20

Job varchar2 20

Age number 2

Salary number 4

Deptno number 2

Use the above relation to perform the following queries :

2. Create a relation name EMP_DEPT having fields empno,ename and deptno from relation EMPLOYEE.

3. Add the field join_date which is a field that can hold date.

4. Modify the field salary to hold a maximum of 10 number values.

5. Delete the column age.

6. Rename the relation EMPLOYEE to EMPLOYEEDETAIL.

7. Delete all the records of EMPLOYEEDETAIL relation.

8. Drop the table EMPLOYEEDETAIL.

SOLUTION

1. Create a relation name EMPLOYEE having the following details:

Fieldname Datatype Size

· Empno number 3

· Ename varchar2 20

· Job varchar2 20

· Age number 2

· Salary number 4

· Deptno number 2

SQL> create table employee(empno number(3),ename varchar2(20),job varchar2(20),age number(2),salary number(4),deptno number(2));

INSERTING VALUES INTO THE TABLE NAMED EMPLOYEE

SQL> insert into employee values(&empno,’&ename’,’&job’,&age,&salary,&deptno);

TO DISPLAY THE TABLE NAMED EMPLOYEE

SQL> select * from employee;

2. Create a relation named EMP_DEPT having fields empno,ename,deptno from relation EMPLOYEE.

SQL> create table emp_dept as select empno,ename,deptno from employee;

DESCRIPTION OF TABLE NAMED EMP_DEPT

SQL> desc emp_dept

3. Add the field join_date which is a field that can hold date.

SQL> alter table employee add join_date date;

DESCRIPTION OF TABLE NAMED EMPLOYEE

SQL> desc employee

1. Modify the field salary to hold a maximum of 10 number value.

SQL> alter table employee modify salary number(10);

DESCRIPTION OF TABLE EMPLOYEE

SQL> desc employee

TO DISPLAY THE TABLE NAMED EMPLOYEE

SQL> select * from employee;

INSERT VALUES INTO FIELD JOIN_DATE

SQL> update employee

set join_date=’02-mar-2011’ where empno=1;

SQL> update employee

set join_date=’02-mar-2011’ where empno=2;

2. Delete the column age.

SQL> alter table employee

drop column age;

DESCRIPTION OF THE TABLE EMPLOYEE

SQL> desc employee

3. Rename the relation EMPLOYEE to EMPLOYEEDETAIL.

SQL> rename employee to employeedetail;

4. Delete all the records of EMPLOYEEDETAIL relation.

SQL> truncate table employeedetail;

8.Drop the table EMPLOYEEDETAIL.

SQL> drop table employeedetail;

INPUT/OUTPUT

SQL> create table employee(empno number(3),ename varchar2(20),job varchar2(20),age number(2),salary number(4),deptno number(2));

Table created.

SQL> insert into employee values(&empno,’&ename’,’&job’,&age,&salary,&deptno);

Enter value for empno: 1

Enter value for ename: vasudha

Enter value for job: admin

Enter value for age: 20

Enter value for salary: 7000

Enter value for deptno: 01

1 row created.

SQL> /

Enter value for empno: 2

Enter value for ename: sonam

Enter value for job: finance

Enter value for age: 20

Enter value for salary: 9000

Enter value for deptno: 02

1 row created.

SQL> select * from employee;

Empno Ename Job Age Salary Deptno

1 vasudha admin 20 7000 01

2 sonam finance 20 9000 02

SQL> create table emp_dept as select empno,ename,deptno, from employee;

Table created.

SQL> desc emp_dept

Name Null? Type

Empno number(3)

Ename varchar2(20)

Deptno number(2)

SQL> alter table employee add join_date date;

Table altered.

SQL> desc employee

Name Null? Type

Empno number(3)

Ename varchar2(20)

Job varchar2(20)

Age number(2)

Salary number(4)

Deptno number(2)

Join_date date

SQL> alter table employee modify salary number(10);

Table altered.

SQL> desc employee

Name Null Type

Empno number(3)

Ename varchar2(20)

Job varchar2(20)

Age number(2)

Salary number(10)

Deptno number(2)

Join_date date

SQL> select * from employee;

Empno Ename Job Age Salary Deptno Join_date

1 vasudha admin 20 7000 01

2 sonam finance 20 9000 02

SQL> update employee

Set join_date=’02-mar-2011’ where empno=1;

1 row updated.

SQL> update employee

Set join_date=’02-mar-2011’ where empno=2;

1 row updated.

SQL> alter table employee

drop column age;

Table altered.

SQL> desc employee

Name Null Type

Empno number(3)

Ename varchar2(20)

Job varchar2(20)

Salary number(10)

Deptno number(2)

Join_date date

SQL> rename employee to employeedetail;

Table renamed.

SQL> truncate table employeedetail;

Table truncated.

SQL> drop table employeedetail;

Table dropped.

RESULT:

Thus the DDL commands has been executed successfully.

EXPERIMENT - 2

AIM: SQL to learn DML commands.

1. Create a table employee as shown below:

FIELD NAME DATATYPE SIZE

Empno number 3

Ename varchar2 20

Job varchar2 20

Salary number 4

Deptno number 2

Age number 2

Use the above relation to perform the following:

2. Display the details of empno,ename,salary of employees who are drawing salary between 1500 and 2500.

3. Write the query to find out how many different job title are stored in the employee relation.

4. How many employees are titled with the job name.

5. Calculate the total salary of employees.

6. Calculate the average salary.

7. List the maximum and minimum salary.

8. Determine how many records are there in the above relation.

9. The ename John is transferred to deptno-20 and his salary is increased by Rs.1000.

10. All the employees working in deptno-6 get a 15% salary increased.

11. Increase the salary of all employees by 25%.

12. Delete the employees having empno-4 and deptno-7.

13. Delete all the employees having empno-3 or deptno-5.

SOLUTION

1.Create a table employee as shown below:

FIELD NAME DATATYPE SIZE

Empno number 3

Ename varchar2 20

Job varchar2 20

Salary number 4

Deptno number 2

Age number 2

SQL>create table employee(empno number(3) primary key,ename varchar2(20),job varchar2(20),salary number(4),deptno number(2),age number(2));

Table created.

SQL>insert into employee values(&empno,’&ename’,’&job’,&salary,&deptno,&age);

Enter value for empno:1

Enter value for ename:vasudha

Enter value for job:account

Enter value for salary:1000

Enter value for deptno:7

Enter value for age:20

1 row created.

SQL>/

Enter value for empno:2

Enter value for ename:john

Enter value for job:admin

Enter value for salary:2000

Enter value for deptno:5

Enter value for age:30

1 row created.

SQL>/

Enter value for empno:3

Enter value for ename:sonam

Enter value for job:finance

Enter value for salary:1700

Enter value for deptno:6

Enter value for age:30

1 row created.

SQL>/

Enter value for empno:4

Enter value for ename:vaibhav

Enter value for job:admin

Enter value for salary:3000

Enter value for deptno:7

Enter value for age:28

1 row created.

SQL>/

Enter value for empno:5

Enter value for ename:sid

Enter value for job:finance

Enter value for salary:5000

Enter value for deptno:5

Enter value for age:39

1 row created.

2.Display the details of empno,ename,salary of employees who are drawing salary between 1500 and 2500.

SQL>select empno,ename,salary from employee where salary between 1500 and 2500;

3.Write the query to find out how many different job title are stored in the employee relation.

SQL>select count(distinct job) from employee;

4.How many employees are titled with the job name.

SQL>select job,count(ename) from employee group by job;

5.Calculate the total salary of employees.

SQL>select sum(salary) from employee;

6.Calculate the average salary.

SQL>select avg(salary) from employee;

7.List the maximum and minimum salary.

SQL>select max(salary),min(salary) salary from employee;

8.Determine how many records are there in the above relation.

SQL>select count(*) from employee;

9.The ename John is transferred to deptno-20 and his salary is increased by Rs.1000.

SQL>update employee set deptno=20,salary=salary+1000 where ename=’john’;

1 row updated.

10.All the employees working in deptno-6 get a 15% salary increased.

SQL>update employee set salary=salary+((15/100)*salary) where deptno=6;

1 row updated.

11.Increase the salary of all employees by 25%.

SQL>update employee set salary=salary+((25/100)*salary);

5 rows updated.

12.Delete the employees having empno-4 and deptno-7.

SQL>delete from employee where empno=4 and deptno=7;

1 row deleted.

13.Delete all the employees having empno-3 or deptno-5.

SQL>delete from employee where empno=3 or deptno=5;

2 rows deleted.

INPUT/OUTPUT:

1. SQL> create table employee(empno number(3) primary key,ename varchar2(20),job varchar2(20),salary number(4),deptno number(2),age number(2));

2. SQL>insert into employee values(&empno,’&ename’,’&job’,&salary,&deptno,&age);

Empno Ename Job Salary Deptno Age

1 Sonam Account 1000 7 20

2 John Admin 2000 5 30

3 Vasudha Finance 1700 6 30

4 Vaibhav Admin 3000 7 28

5 Sid Finance 5000 5 39

3. SQL>select empno,ename,salary from employee where salary between 1500 and 2500;

Empno Ename Salary

2 John 2000

3 Vasudha 1700

4. SQL>select count(distinct job) from employee;

Count(DISTINCT JOB)

5. SQL>select job,count(ename) from employee group by job;

Job Count(Ename)

Account 1

Admin 2

Finance 2

6. SQL>select sum(salary) from employee;

Sum(Salary)

12700

7. SQL>select avg(salary) from employee;

Avg(Salary)

2540

8. SQL>select max(salary),min(salary) salary from employee;

max(Salary) min(Salary)

5000 1000

9. SQL>select count(*) from employee;

Count(*)

10. SQL>update employee set deptno=20,salary=salary+1000 where ename=’john’;

1 row updated.

Empno Ename Job Salary Deptno Age

1 Sonam Account 1000 7 20

2 John Admin 3000 20 30

3 Vasudha Finance 1700 6 30

4 Vaibhav Admin 3000 7 28

5 Sid Finance 5000 5 39

11. SQL>update employee set salary=salary+((25/100)*salary);

rows updated.

Empno Ename Job Salary Deptno Age

1 Sonam Account 1250 7 20

2 John Admin 3750 20 30

3 Vasudha Finance 2125 6 30

4 Vaibhav Admin 3750 7 28

5 Sid Finance 6250 5 39

12. 11. SQL>update employee set salary=salary+((15/100)*salary) where deptno=6;

1 row updated.

Empno Ename Job Salary Deptno Age

3 Vasudha Finance 1955 6 30

14. SQL>delete from employee where empno=4 and deptno=7;

1 row deleted.

Empno Ename Job Salary Deptno Age

1 Sonam Account 1000 7 20

2 John Admin 3000 20 30

3 Vasudha Finance 1700 6 30

5 Sid Finance 5000 5 39

15. SQL>delete from employee where empno=3 or deptno=5;

2 rows deleted.

Empno Ename Job Salary Deptno Age

1 Sonam Account 1000 7 20

2 John Admin 3000 20 30

RESULT:

Thus the program to perform the DML commands in SQL has been executed successfully.

EXPERIMENT:-3

Aim:-To create a table named areas having two fields as radius and area. Also write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 5-10 and then store the radius and then store the radius and the corresponding values of calculated areas in that table.

Solution:

1. Creation of table:

SQL>create table area62 (radius number(2),area number(10,2)).

Table created.

PL/SQL code block:

1.SQL>declare

2. radius number(2);

3.area number(10,2);

4. pi constant number(3,2):=3.14;

5. begin

6. radius:=5;

7.while radius<=10

8.loop

9.area :=pi*radius* radius;

10.insert into area values(radius,area);

11. radius:= radius+1;

12.end loop;

13.end;

14./

Pl/sql procedure successfully completed.

Input / output:

Sql> select*from area;

Radius area

5 78.5

6 113.04

7 153.86

8 200.96

9 254.34

10 314

6 rows selected

Result:

Thus the pl/sql code block to find and store the area of circle with corresponding radius has been executed successfully.

EXPERIMENT:-4

AIM: -To write a PL/SQL code block to calculate the factorial of given number.

SOLUTION:

1. Declare

2. I number(2);

3. fact number(6);

4. begin

5. i:=5;

6 fact:=1;

7. while i>=1

8. loop

9. fact:=fact*i;

10. i:=i-1;

11. end loop;

12. dbms_output.put_line(‘factorial is ‘||fact);

13.end;

14. /

PL/SQL procedure successfully completed

Input/Output:

SQL>set serveroutput ON;

SQL> /

Factorial is 120

PL/SQL procedure successfully completed

Result: Thus ,the PL/SQL code block to calculate factorial of a given no. has been executed successfully.

EXPERIMENT :-7

AIM : -Create a relation named employee having the following details

Fieldname Datatype Size

Eno number 3

Ename varchar2 20

Salary number 4

Write a PL/SQL code that inserts some records in the above table and then update the salary of employee name jack to Rs 3000. Then calculate the total salary. If the total salary exceeds Rs 20000

Then undo the updates made to the salary otherwise save it.

SOLUTION

SQL> create table emp (eno number(3), ename varchar2(20),salary number(4));

Table created

DECLARE

Totalsalary number(10);

BEGIN

Insert into emp values( 1,’jack’,2000);

Insert into emp values( 2,’samrat’,8000);

Insert into emp values ( 3,’deepak’,9000);

SAVEPOINT sp1;

Update emp set salary = 3000 where ename= ‘jack’;

Select sum(salary) into totalsalary from emp;

If totalsalary> 20000 then

Rollback to save point sp1;

End if;

Commit;

End;

INPUT/OUTPUT

SQL> select *from emp;

EMPNO ENAME SALARY

--------------------------------------------------------------------

1 jack 3000

2 samrat 8000

3 Deepak 9000

RESULT :

Thus the program for using savepoint and rollback has been executed successfully.

EXPERIMENT:-9

AIM :- BEFORE ROW TRIGGER

Create a table student master and audit student as shown below

student master

Fieldname Datatype Size

Rollno number 2

Name varchar2 20

Age number 2

Phone number 15

City varchar2 20

audit student

Fieldname Datatype Size

Rollno number 2

Name varchar2 20

Operation varchar2 15

Date_of_operation date

Create a transparent audit system for the relation student master. The system must keep the track of records that are being deleted or updated along with the operation and date on which the operation is performed in the relation audit student.

SOLUTION

SQL> create table master48 (rollno number(2) constraint pk primary key,name varchar2(20),age number(2),phone number(15),city varchar2(20));

SQL> create table audit48( rollno number(2), name varchar2(20), operation varchar2(15), date_of_operation date);

SQL> insert into master48 values (15,’name‘,18,72587368,’Gurgaon‘);

CREATE OR REPLACE TRIGGER trigger t948

BEFORE INSERT OR DELETE OR UPDATE on master48

FOR EACH ROW

DECLARE

Operation varchar2(15);

Name varchar2(20);

Rollno number(2);

BEGIN

If updating then

Operation := ‘update’;

End if;

If deleting then

Operation := ‘delete’;

End if;

Rollno :=: old.Rollno;

Name :=: old. Name;

Insert into audit48 values( Rollno, Name, Operation, sysdate);

End;

INPUT/OUTPUT

SQL> select *from master48;

ROLLNO NAME AGE PHONE CITY

------------------------------------------------------------------------

2 soni 20 3476839 delhi

3 swati 19 2369864 newDelhi

4 Vasudha 20 32867891 noida

5 sonam 20 723868 meerut

4 rows selected.

SQL> update master set city= ‘Bhopal’ where name =‘sonam’;

1 row updated.

SQL> select *from master48;

ROLLNO NAME AGE PHONE CITY

------------------------------------------------------------------------

2 soni 20 3476839 delhi

3 swati 19 2369864 newDelhi

4 Vasudha 20 32867891 noida

5 sonam 20 723868 bhopal

4 rows selected.

SQL> select *from audit48;

ROLLNO NAME OPERATION DATE_OF_OPER

------------------------------------------------------------------------------

5 sonam update 10-MAR-11

SQL> delete from master48 where rollno =4;

1 row deleted.

SQL> select *from master48;

ROLLNO NAME AGE PHONE CITY

------------------------------------------------------------------------

2 soni 20 3476839 delhi

3 swati 19 2369864 newDelhi

5 sonam 20 723868 bhopal

3 rows selected.

SQL> select *from audit48;

ROLLNO NAME OPERATION DATE_OF_OPER

------------------------------------------------------------------------------

5 sonam update 10-MAR-11

4 Vasudha delete 10-MAR-11

RESULT :

Thus the program for BEFORE ROW TRIGGER has been executed successfully.

The Journey

Monday, August 13, 2012

Learn DBMS through 10 simple programs

1 comment:

Blog Archive