Using the following tables, write the SQL statements (any 5):
Employee(Empno, Name, Deptno, Job, Salary)
Department(Deptno, Dname, Location)
a) List department-wise no. of employee.
b) Display distinct job in the company.
c) Display list of employees getting maximum salary.
d) List empno, name, Dname and salary for each employee.
e) List the maximum, minimum & average salary given in each department.
f) List empno, name, salary for production department.
Create the two tables as given below.
Employee ( Empno, Name, Deptno, Job, Salary)
Department (Deptno, Dname, Location)
a) List department-wise no. of employee.
Select COUNT(EmpNo) EmployeeCount, d.Dname from Employee 'e' innerjoin Department d on e.Deptno = d.Deptno group by d.Dname
b) Display distinct job in the company.
Select distinct job from Employee
c) Display list of employees getting maximum salary.
Select * from Employee orderby Salary desc
d) List empno, name, Dname and salary for each employee.
Select Employee.Empno, Employee.Name, Department.Dname, Employee.Salary from Employee innerjoin Department on Employee.Deptno = Department.Deptno
e) List the maximum, minimum & average salary given in each department.
Select MAX(Salary) MaxSalary, MIN(Salary) MinSalary, AVG(Salary) AvgSalary, Department.Dname from Employee innerjoin Department on Employee.Deptno = Department.Deptno groupby Department.Dname
f) List empno, name, salary for production department.
Select Empno, Name, Salary, DName from Employee innerjoin Department on Employee.Deptno=Department.Deptno where Department.Dname ='Production'
