Slip2
Question
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;