DBMS-Slip24

Slip24

Consider the following Entities and Relationships                                   
Employee (emp_id, emp_name, address)
Investment (inv_no, inv_name, inv_date, inv_amount)
Relation between Employee and Investment is One to Many.
Constraint: Primary key, inv_amount should be > 0.

Solution:-

Create a Database in 3NF & write queries for following.

•Display employee details who have invested  more than 100000.
SQL>Select ename,address from emps,investment where emps.eid=investment.eid and amount >100000;

•Display employee wise total investment amount.
SQL>Select  ename,sum(amount) from emps,investment where emps.eid=investment.eid group by ename  order by ename;

•Display the employee names who invest on date 2nd Jan 2013.
SQL>Select ename  from emps,investment where emps.eid=investment.eid and idate=’2/1/2013’;

•Display employee whose investment are more than 3.
SQL>Select ename  from emps,investment where emps.eid=investment.eid group by ename having count(investment.ino)>3;

•Find average investment of employees of Pune.
SQL>select avg(amount)from emps,investment where emps.eid=investment.eid  and address=’pune’;