Important SQL Queries

1./* To Copy an excel sheet(named temp in C Drive which is in the server) into a temporary table named tbltemp */(For Import)


EXEC( 'SELECT * INTO ' + 'tbltemp' + ' from OpenRowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=' + 'C:\temp.xls' + ''',''select * from [Sheet1$]'')')

2./* To Update a paricular column in one table by using other table */

update table1 set table1.mobile=table2.mobilephone
from table1 inner join table2 on table1.id=table2.id
where table1.cycle_flag=1

3./*To retrieve names of all the columns in table1*/

select name from syscolumns where id in (select id from sysobjects where name='table1')

4./*To retrieve column name,data type and length of each field in a table named check_length*/)

select syscolumns.name,systypes.name,syscolumns.length
from syscolumns inner join systypes on systypes.xusertype=syscolumns.xusertype
where syscolumns.id=object_id('check_length')

5./*To interchange: Where gender is Male that should be female and where Female that should be Male */

update tablename set gender=(case when gender='Male' then 'Female' else 'Male' end)

6./* To find how many tables contain ID as the column name */

select * from Information_schema.columns where column_name='ID'

7. /*To remove Duplicates Records from a table.
If you have a primary key in the table */

DELETE Authors
FROM authors auth
LEFT OUTER JOIN
(
SELECT AuthName, min(AuthorRowid) as AuthorRowid
FROM authors
GROUP BY Authorname
) gAuth
on gAuth.AuthorRowid = auth.AuthorRowid
WHERE gAuth.AuthorRowid is null


8./*To find how many book types are there for individual subject(No of Books for each subject) */

Id BookType BookName
1 Chem Organic
2 Bio lifescience
3 Phy Nootan
4 Chem Inorganic
5 Phy Bhatnagar
6 Chem Physical


select distinct(booktype),count(booktype) as 'No Of Books' from tbl_bookdetails group by booktype
or
select distinct(booktype),count(booktype) as 'No Of Books' from tbl_bookdetails group by booktype order by count(booktype)

O/P:
booktype No Of Books
Bio 1
Phy 2
Chem 3