DBMS-Slip2

Slip2

Consider the following Entities and Relationships                                     
Department (dept_no, dept_name, location)
Employee (emp_no, emp_name, address, salary, designation)
Relation between Department and Employee is One to Many
Constraint: Primary key, salary should be > 0.

Solution

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

•Find total salary of all computer department employees.
SQL>select sum(Employee.salary),dept_name from Department,Employee where Department. dept_no=Employee.dept_no and dept_name='Computer' group by dept_name;

•Find the name of department whose salary is above 10000.
SQL>select  dept_name,salary from Department, Employee where Department. dept_no  = Employee. dept_no  and salary >10000;

•Count the number of employees in each department.
SQL>select count(emp_no ), dept_name  from Department, Employee where Department. dept_no  = Employee. dept_no  group by dept_name  ;

•Display the maximum salary of each department.
SQL>select max(salary),dept_name from Department, Employee  where Department. dept_no  = Employee. dept_no  group by dept_name  ;

•Displaydepartment wise employee list.
SQL>select emp_name, dept_name  from Department, Employee where Department. dept_no  = Employee. dept_no;