Que : You are given a table named Employee with columns EmployeeId, Name , ManagerId in it. Write an SQL statement to find out all the manager who have more than two subordinate under them.
Suppose we have the following data in our table :
Que : Given two table as in the above question . Write a SQL statement to find the department name in which there is no employee.
Please comment if you like the above post or find mistake or have some other way of solving the questions .
Select managerid from
Employee group by managerid having count(*)>=2;
| EmpId | Name | ManagerId |
| 1 | Ishant | 4 |
| 2 | Gaurav | 2 |
| 3 | Rahul | 4 |
| 4 | Kamal | 2 |
| 5 | Aman | 1 |
If we execute the above query for the above table then the output would be 4 and 2. Because ManagerId=4 and 2 are the only Managers who have atleast two subordinate under them.
Que : You are given a table named Employee with columns EmployeeId, Name , DepartmentId. Write a SQL statement to find out the number of emloyee in each department .
Select
count(Employee_id), DepartmentId from Employee group by
DepartmentId;
Que : You are given a table named Employee with columns EmployeeId, Name , DepartmentId. Write a SQL statement to print the department together with their employee name .
Que : Suppose if the same question( Write a query to find the number of employee in each department.) but we have two different table of employee and department as following . Then how does the query goes for it. Schema of two tables are follows :
( Employee)
Select
DepartmentId,Name from Employee group by
DepartmentId,EmloyeeId;
Que : Suppose if the same question( Write a query to find the number of employee in each department.) but we have two different table of employee and department as following . Then how does the query goes for it. Schema of two tables are follows :
| empId | name | deptId |
| deptId | deptname |
(Department)
Select count(*),deptId from
Employee,Department where Employee.deptId=.Departemnt.deptId group
by Department.deptId;
Que : Given two table as in the above question . Write a SQL statement to find the department name in which there is no employee.
Select deptname from Employee where deptId not in(select
distinct deptId from Employee);
Please comment if you like the above post or find mistake or have some other way of solving the questions .
Admin please post queries related to join of three database tables.
ReplyDeletetable1 table 2
ReplyDeletepatient_id name paitient_id account_no
1 John 1 45665
2 Simon 1 565856
2 123896
Display patient_id and name for whom there is more than one account.