sqlchallenge 1

Give bookname which is more then one time and number of books also


create table books
(
id int identity(1,1),
custname varchar(25),
bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'naimish', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'ravi', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'jugal', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'jugal', N'3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'naimish', N'3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'nikhi', N'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'ram', N'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'misi', N'evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'michel', N'evil')

like this.

sqlchallenge 2

id           Bookname
----------------------
1           GlobleWorld
2           Tunner
3           Roster in city
4          GNom
5          Three mistake
6          other,Not specified
7          The Secret
8          inspiration

give result order by bookname but last row must be a "other,Not specified"
like this

-------------------------------------------------------------------------

id           Bookname
----------------------
1           GlobleWorld
4           GNom 
8           inspiration 
3           Roster in city 
7          The Secret  
5          Three mistake
2           Tunner
6          other,Not specified

Some inbuilt useful store procedures.

--Drop all table from database
EXEC sp_MSForEachTable "Drop TABLE ?"

--Delete data from all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

--truncate data from all tables
EXEC sp_MSForEachTable "Truncate table ?"

--Desable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

--Enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--we want to reseed identity
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

--Shrink Database
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Nth Highest query

create table emp
(
empid int identity(1,1) primary key,
empname varchar(25),
salary int,
deptid int references dept(deptid)
)
insert into emp values ('naimish',900,1)
insert into emp values ('ravi',2200,2)
insert into emp values ('vimal',1000,2)
insert into emp values ('ajay',1400,3)
insert into emp values ('jugal',1100,3)
insert into emp values ('ritesh',1500,1)
insert into emp values ('gati',3000,2)
insert into emp values ('mohit',1600,1)

select * from
(
select ROW_NUMBER() over(order by salary desc)rank,* from emp
)as t
where rank  = 1