SQL Important Queries

>I have a table with duplicate items in it. Write me a query which returns only duplicate rows with number of times they are repeating.

    select item,count(*) from tab group by item having count(*)>1;

>How to find the 2nd max sal from the table.

  To get 2nd highest salary.
  select max(salary) from employee where salary < (select max(salary) from employee);
  select max(salary) from employee where salary not in(select max(salary) from employee);

  To get 5th highest salary.
  select * from tab a where 5 =(select count(distinct salary) from tab b where a.salary<=b.salary);
 
> What command is used to create a table by copying the structure of another table?
   without data:
   CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1=2;

   with data:
   CREATE TABLE new_table AS SELECT * FROM old_table ;

> How to copy all data from tab1 to tab2 ,where tab1 and tab2 having same table structure.

   INSERT INTO emp_details1 SELECT * from emp_details where emp_id >1000;

> What operator tests column for the absence of data?
    NULL operator is used to check the absence of data in a column.
    Example:
    SELECT * FROM emp WHERE sal is NULL;
  
> How can we delete records in a table using update table?
  update tab set name=null where id=2;
 
>Identify error in following SQL:
    SELECT EMPNO, SUM(SAL)
    FROM EMP
    GROUP BY EMPNO
    HAVING ENAME= ?KENT?
a) Syntax Error
b) SUM() is not an aggregate function
c) HAVING can not be used with GROUP BY
d) The Having condition has to be based on some column that appears in the select list.
 
>How to remove the duplicate records from table which havting same id? Consider following table which having 2 rows with id=100.
  #select * from tab;
     id  |    name    |
    -----+------------+
     100 | mukesh     |
     101 | shivam     |
     102 | smrati     |
     100 | mukesh     |
     --in above table need to remove mukesh
--step1
  add one column num
  --ALTER TABLE tab2 ADD column num character varying;
--Step2
  Create sequence num_seq;
--Step3
  UPDATE TABLE tab set num=nextval('num_seq');
--Step4
  DELETE FROM tab where num in(SELECT MAX(num) from tab group by name having count(*)>1;
--Step5
  Check if still duplicate is there.
  Select count(*),name from tab group by name having count(*)>1;
  if above query returns any records ,repete step4.
 
 --Method 2 :
      acc_no |  name  | joint_account
    --------+--------+---------------
     100    | mukesh | 101
     200    | shivam | 102
     300    | smrati | 200
     400    | rity   | 100
     100    | mukesh | 122
     401    | rity   | 122
     402    | rity   | 122
  
--delete from emp_details1 where acc_no not in(select min(acc_no)from emp_details1 group by name);

    mydb=# select * from emp_details1;
     acc_no |  name  | joint_account
    --------+--------+---------------
     100    | mukesh | 101
     200    | shivam | 102
     300    | smrati | 200
     400    | rity   | 100
     100    | mukesh | 122