Skip to main content

Posts

Showing posts from October, 2011

Some tricky SQL queries (Interview Questions)

1.Select nth highest salary and name from emp table ? 1 2 3 SELECT DISTINCT (a.empsalary),a.Employee_Name FROM empData A WHERE  N =(SELECT COUNT (DISTINCT (b.empsalary)) FROM empData B WHERE a.empsalary<=b.empsalary); --where N is your number (2nd or 5th) e.g SELECT DISTINCT (a.empsalary),a.Employee_Name FROM empData A WHERE  5 =(SELECT COUNT (DISTINCT (b.empsalary)) FROM empData B WHERE a.empsalary<=b.empsalary); 2.Select query without using "Like" operator . For eg select employee name start with 'j' and city is 'Noida' in company_name ? 1 select * from empdata where CHARINDEX('j',Employee_name)=1 and CHARINDEX('Noida',company_name)>0 3.Select query for grouping 2 coulmns in one column using Case statement ? 1 select result=case when p1 is not null then p1 when p2 is not null then p2 end from tbltest 4.Create identity like coulmn in query ? 1 select row_number () over( order by p1) as sno, result=case when p1 is n

SQL Query to list down the tables and all coumhs in database,

Here is a query which can fetch all the table names and all the colunm names in each table.  select table_name, column_name, data_type, character_maximum_length, is_nullable from information_schema.columns where table_name in (select name from sysobjects where xtype='U') order by table_name Comments Friday, April 16, 2004 11:12 AM by  M. Keith Warren #  re: SQL Query to list down the tables and all coumhs in database, Much easier way... SELECT * FROM INFORMATION_SCHEMA.COLUMNS  Monday, August 27, 2007 2:40 AM by Rajeev #  re: SQL Query to list down the tables and all coumhs in database, This query is not working in sapdb. any help will be appreciated.. Tahnks & Regards Rajeev Monday, October 15, 2007 8:01 AM by  shailesh #  re: SQL Query to list down the tables and all coumhs in database, Please give me fast comment Thursday, November 22, 2007 9:51 AM by Vinod #  re: SQL Query to list down the tables and all coumhs in database, Above query is not working in TOAD( Datab