Friday, December 16, 2011

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 

No comments:

Post a Comment