SQL - DEPT table who has no employees assigned in EMP table

Answers collected from the below site.Please refer to the site for details.
To take out those dept who has no employees assigned to it
Table Structure:EMP:( EMPNO   ENAME    DEPTNO)  DEPT:DEPTNO   DNAME
Ms Sql Server:->select distinct d.dname from dept d  EXCEPT  select d.dname from dept d JOIN emp e where d.deptno=e.deptno
SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle
select dname from dept where deptno not in (select deptno from emp)
Notes:i.Above to work assume all emplyoees have a deptno.CEO deptno could be NULL = fail, for that the below query.Not in uses Full scan of the table very slow,instead use NOT EXISTS.
select distinct d.dname from dept d left join emp e on d.deptno=e.deptno where e.deptno is null
Oracle:->select distinct d.dname from dept d where  NOT EXISTS ( select * from emp e where d.deptno=e.deptno)

Reference:T-SQL for Starters: Simple and Correlated Subqueries

subquery:A subquery is a query that SQL Server must evaluate before it can process the main query. note:Instead of IN operator JOIN is more efficient.
correlated subquery:A correlated subquery is one that depends on a value in the outer query.The net effect is that the subquery runs once for every row in the main query; this situation is inefficient. Correlated subqueries evaluate once for each row of the outer query, so use these queries only for single-row results.
EXISTS:But the convention with EXISTS is that you use an asterisk, rather than specifying a column, because you do not expect SQL Server to return any data values—EXISTS returns only Boolean values (i.e., true or false).

Get employees who worked in more than one department with SQL query

Employee (employeeName, wage, contactNo)Department (employeeName, departmentNo,hours,startDate)

select  e.employeename from Employee e INNER JOIN Department d ON e.employeename=d.employeename GROUP BY e.employeename HAVING COUNT(departmentNo) > 2
sql query to display dept name,location name,number of employees
select dname as DNAME, loc as LOC, SUM(1) as Number_of_People, avg(sal) as AvgSalary from dept,emp group by dname, loc;