DBMS Practical Slips

9. Employee_Investment

create table emp

(

e_id number(5) primary key ,

e_name varchar(20) ,

address varchar(40)

) ;

create table inv

(

i_no number(5)  primary key ,

i_name varchar(20) ,

i_date varchar(20) ,

i_amt number(15) ,

e_id number(5) references emp(e_id)

) ;

insert into emp values(1,'rucha','pune') ;

insert into emp values(2,'kavita','jaipur') ;

insert into emp values(3,'aditi','nashik') ;

insert into emp values(4,'sainath','nanded') ;

insert into inv values(10,'bond',2000,100000,1) ;

insert into inv values(20,'property',215,150000,2) ;

insert into inv values(30,'estate',2005,200000,3) ;

insert into inv values(40,'industry',1999,250000,4) ;

1. select emp.* from emp,inv

where emp.e_id=inv.e_id AND

inv.i_amt>150000;

      E_ID E_NAME               ADDRESS

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

         3 aditi                nashik

         4 sainath              nanded

..........................................................................................................................

2. select SUM(inv.i_amt),e_name from emp,inv GROUP BY e_name;

SUM(INV.I_AMT) E_NAME

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

        700000 kavita

        700000 rucha

        700000 aditi

        700000 sainath

3. select e_name from emp,inv

where emp.e_id=inv.e_id AND

inv.i_date='1999';

E_NAME

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

sainath

......................................................................................................

4. select e_name from emp,inv

where emp.e_id=inv.e_id AND

inv.i_no>=3;

E_NAME

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

rucha

kavita

aditi

sainath

.......................................................................................

5. select AVG(inv.i_amt) from emp,inv

where emp.e_id=inv.e_id AND

emp.address='jaipur';

AVG(INV.I_AMT)

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

        150000

....................................................................................................