Posts

Finding rows that only have duplicate values in a specific column

Image
  WITH cte AS (     SELECT         colb,         COUNT(*) AS dup     FROM         sales     GROUP BY         colb ) SELECT     cola FROM          data     INNER JOIN cte ON data.colb = cte.colb WHERE     cte.colb > 1;

SQL questions

  1- How to find duplicates in a table 2- How to delete duplicates from a table 3- Difference between union and union all 4- Difference between rank,row_number and dense_rank 5- Find records in a table which are not present in another table 6- Find second highest salary employees in each department 7- Find employees with salary more than their manager's salary 8- Difference between inner and left join 9- update a table and swap gender values.

Query Questions

Image
 

SQL Interview Questions

  Join results of T1 and T2 : T1 1 2 3 NULL  T2 1 4 NULL   SQL Query to Find All Employees Who Are Also Managers     SQL Query to Find All Employees Who Are not a Managers   How to find Nth highest salary from a table   Query to return only duplicate records   How will you delete duplicate records   Show string value coming between 2 dots. like input : hello.welcome.bye output : welcome   input: SubjectID       StudentName ----------      ------------- 1               Mary 1               John 1               Sam 2               Alaina 2               Edward output: SubjectID       StudentName ----------      ------------- 1               Mary, John, Sam 2               Alaina, Edward   x

Practice Queries

 1. How to join a table with like :  in one table we have data "CANADA" and in another table we have data "abc CANADA".  Now i want to apply a join using like clause.

Sequence

Generate Unique Sequential values     Sequence has 2 pseudocolumns     1. CURRVAL     2. NEXTVAL CURRVAL :  gives the most recently generated value NEXTVAL : gives the next available value in the sequence to generate sequence will start generating value from its MINVALUE Default MINVALUE is 1 default incremental value of sequence is +1 Same sequence can be used in multiple tables but not suggestable. One sequence for one table.