SQL Query interview question part 2

  create table tblemployee
        (
      ID                        Bigint NOT NULL,
      First_Name         NVARCHAR(50),
      Last_Name          NVARCHAR(50),
      Start_Date           DATE,
      End_Date            DATE,
      Salary                  float(8,2),
      City                     NVARCHAR(50),
      Description         NVARCHAR(50)
      )


1. Ques: In above employee table write a query to find in which city do we have the most Employees?
Answer:
Select count(*), city from employee group by city having count(*) =(Select  max(count(*))FROM tblemployee GROUP BY city);


2. Ques: Write a query to List out department who has more than 4 employee born after 1960
Answer:
select  deptno, count(empno)  from   employees  where    bdate > date '1960-01-01'  group by deptno  having   count(*) >= 4;


3. Ques: Write a query to display department number and its employee count if it has more than 4 employees.
Answer:
select deptno, count(empno)  from   employees  group  by deptno  having count(*) >= 4;


3. Ques: There is a studentmarks  table having the following columns :-

ID  Marks1  Marks2   Maxmarks
1     10           20              20
2     25           30              30
3     30           10               30
4     35           25               35
5     20           40               40

4. Write a query to update column maxmarks such that maxmarks column contains whatever be the greater value among marks1 and marks2 
Answer:
Update studentmarks   set Maxmarks = case when Marks1>Marks2 then Marks1 else Marks2


5. Ques: Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments? 
Answer:
Select customer_no, payments from(Select  customer_no, payments from customer C1 order by payments desc) where ROWNUM <4;


6. Ques :Write a query to retrieve the last row of a employee table?
Answer:
Select * from tblemployee where EmpId = (select max(EmpId ) from tblemployee );


7. Ques:  There is two table EMP and Dept given bellow In which Deptno in both table as primary and forign key releationship
               EMP Tables consist coloumn of Empno, Deptno, Mgrid, Empname, Location
               Dept Table Consist coloumn of Deptno, Deptname.

Write a query to select Department name for which there is more that 50 employees in a specific Department. List the deptname and no of employees.
Answer:
Select d.deptname count(e.empname) from emp e dept d where e.deptno=d.deptno group by deptno having count(empname) > 50


8. Ques: Write a query to select Manager name for each employee. MGRID column in the emp table is the empid of the Manager. 
Answer:
Select e.ename employee m.ename manager from emp e emp m where e.mgr = m.empno