1. There are some duplicate records in a table. How do you delete those duplicate rows(without Java)
Ans: using rowid by selecting min or max row id of duplicate rows.
Delete from table1 where (col1,rowid) in (sel col1,min(rowid),count(rowid) from table1 group by col1 having count(rowid) > 1)
Or
Select distinct rows from the table and put it to a temporary table.
Drop the original table and rename the temporary table to original table.
2. How to fetch the top 5th row from a table containing 100 records?
Ans: Using top function or using qualify row_number() over (partition by order by) <=6,
or using rank() function.
3. A table contains various departments, employees & their salaries. How do you fetch the maximum salary of each department and the salary must be greater than 5000
Ans: select dept,max(sal) from department group by dept having max(sal) > 5000.
hadoop jira, hadoop command, hadoop icon, hadoop with python, ubuntu install hadoop, hadoop in the cloud, hadoop fs, hadoop hdfs commands, hadoop data, hadoop capacity scheduler
Ans: using rowid by selecting min or max row id of duplicate rows.
Delete from table1 where (col1,rowid) in (sel col1,min(rowid),count(rowid) from table1 group by col1 having count(rowid) > 1)
Or
Select distinct rows from the table and put it to a temporary table.
Drop the original table and rename the temporary table to original table.
2. How to fetch the top 5th row from a table containing 100 records?
Ans: Using top function or using qualify row_number() over (partition by order by) <=6,
or using rank() function.
3. A table contains various departments, employees & their salaries. How do you fetch the maximum salary of each department and the salary must be greater than 5000
Ans: select dept,max(sal) from department group by dept having max(sal) > 5000.
hadoop jira, hadoop command, hadoop icon, hadoop with python, ubuntu install hadoop, hadoop in the cloud, hadoop fs, hadoop hdfs commands, hadoop data, hadoop capacity scheduler
Post a Comment