Imp Sql Server Queries

Q1: write 3 way to accurate count of the number of records in the table?
Ans:
1:        Select *from tablename
2:        Select count(*) from tablename
3:      this is fastest way
Select rows from sysindexes where id=OBJECT_ID(‘tablename’) AND indid<2; 

Q2: create a database

Ans:
Create database newdatabasename

Q3: rename a database

Ans:
Sp_rename  ‘olddatabasename’ ,’newdatabasename’;

Q4: rename table

Ans: 
Sp_rename ‘oldtablename’,’newtablename’;

Q5:  rename column

Ans: 
Sp_rename ‘tablename.[oldcolumnname]’,’newname’,’column’;
Ex:   sp_rename ‘emp.sal’,’empsal’,’column’

Q6: select top n rows

Ans: 
Select top n * from tablename;

Q7: select top 2nd salary from table

Ans:
Select max(salary) from emp where salary not in(select max(salary) from emp);

Q8: select top nth salary from table

Ans:
Select top(1) from emp where sal in (select top(n) sal from emp order by sal desc) order by sal asc;

Q9: concatenate the fname & lname to give a complete name

Ans:
Select fname||??||lname from emp;

Q10: retrieve first five char of fname column of table emp

Ans: 
Select substring(fname,1,5) frm emp;

Q11: sql quary to delete duplicate records in table

Ans: 
Delete from table1 where empid in(select distinct a.empid from table1 a,table1 b
where(a.firstname=b.firstname AND a.lastname=b.lastname) AND a.empid<b.empid);

Q12: list all tables in database

Ans:
Select table_name from information_schema.tables;

Q13: list all columns in database

Ans: 
Select * from information_schema.columns

Q14: list all table constraints

Select *from information_schema.table_constraints

Q15: creating a new table using existing table

Ans:
Select *into newtablename from existingtablename;

Q16:  creating a new dummy table using existing table

Ans:
Select *into dummy_table from emp where 1=2;

Q17: copy data from existing table into another table

Ans:
Insert into anothertablename select *from existing tablename;

Q18: imp question

In a table we put by mistake m in place of f and f in place of m in gender column which
strategy u would like to apply to chane it?
Ans: we should apply cursor here
//database name adi12 use adi12                                                                   
create table gen (id int, g char(1)) 
insert into gen values(1,'m'),(2,'m'),(3,'f') (4,'f'),(5,'f'),(6,'m') 
select *from gen 
//cursor start from here
declare mycur cursor for select id,g from gen 
declare @g1 as char(1) 
declare @id as int  
open mycur fetch next from mycur  
into @id,@g1 
while(@@fetch_status=0) 
begin 
if(@g1='m') 
update gen set g='f' where id=@id  
else  
update gen set g='m' where id=@id 
fetch next from mycur into @id,@g1 
end  
close mycur