DBMS-Slip15

Slip15
Consider the following Entities and Relationships                                   
Employee (eno, ename, deptname, salary)
Project (pno, name, budget)
Relation between Employee and Project is Many to Many.
Constraint: Primary key, salary should be > 0.

Solution:-

Create a Database in 3NF & write queries for following.
•List the name of employee and department having salary > 50000.
SQL>select ename ,dname from Employee where salary >50000;

•List names of all employees who works with ‘Ramesh’ on same project.
SQL> select ename ,pname from Employee,project ,ep where employee.eno=ep.eno and project.pno=ep.pno  and ename=’Ramesh’;

•Find the names of employees who are working on project having budget greater than 30000.
SQL>select ename ,pname,budget from Employee,project ,ep where employee.eno=ep.eno and project.pno=ep.pno and budget>300000;       

•List name of department that have at least two projects under them.
SQL>select  dname,pname from Employee,project ,ep where employee.eno=ep.eno and project.pno=ep.pno group by dname,pname having count(project.pname)>2;

•Updatebudget of a project done by employees of Computer Department by 15%.
SQL>