How to install SQLPLUS client in linux

 How to install SQL * PLUS client in Linux ?

Its really very simple steps to do this, and very crazy while dependency for compatibility with OS (x32 bit or x64 bit).

I have done for x64 bit Linux UBUNTU. Usally Oracle provides .rpm packages , and you need to download those packages into  your machine.

Download here.

Assuming that you have already installed Oracle Database , else you are connecting some remote database. Because, this post only show the sqlplus installation to connect existing database.

Downloaded files :-

oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm
Copy these files to your prefered location. I have  used below location.

/usr/java

Now time for convert these .rpm packages into debian specific package and install on your Ubuntu. Use alien command to convert.

Follow the below steps and install one-by-one.

First install the sqlplus :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm

[sudo] password for dev:

dpkg --no-force-overwrite -i oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb

Selecting previously unselected package oracle-instantclient12.1-sqlplus.

(Reading database ... 226951 files and directories currently installed.)

Unpacking oracle-instantclient12.1-sqlplus (from oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-sqlplus (12.1.0.1.0-2) ...

Second install the basic (packages) :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
dpkg --no-force-overwrite -i oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb

Selecting previously unselected package oracle-instantclient12.1-basic.

(Reading database ... 226963 files and directories currently installed.)

Unpacking oracle-instantclient12.1-basic (from oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-basic (12.1.0.2.0-2) ...

Processing triggers for libc-bin ...

ldconfig deferred processing now taking place 

Third install the devel :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

dpkg --no-force-overwrite -i oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb

Selecting previously unselected package oracle-instantclient12.1-devel.

(Reading database ... 226981 files and directories currently installed.)

Unpacking oracle-instantclient12.1-devel (from oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-devel (12.1.0.2.0-2) ...
Once these installation complete, you can start sqlplus using below command. Either you can use your specific user/password with correct server address.

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus / as sysdba

Good to go if no error, else follow with fixing below !! Good luck.


Note - You may face the below library missing issue while executing sqlplus command. Error below :-

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus / as sysdba

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory


Now this issue saying the lib is not loading, the sqlplus is complaining about the missing library.

So you need to edit the oracle.conf and provide the correct lib path (i.e : /usr/lib/oracle/12.1/client64/lib).Add this path into oracle.conf file.



dev@javadevelopersguide-developer-desktop /usr/java $ sudo vi /etc/ld.so.conf.d/oracle.conf

/usr/lib/oracle/12.1/client64/lib

Now, you should be able to connect to your preferred Database. I have connected to our remote server as below :



dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus my_user/my_password@z2customdb01.ztest/z2custom.zenv.mycompanyadd.com.in
SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 23 16:30:02 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Wed Dec 23 2015 16:18:22 +11:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning option
SQL>

SQL>


Yes, its working. Hope it will help you.

Follow for more details on Google+ and @Facebook!!!

Find More :-

• How  to install Jboss Fuse on Linux ?
• How to install jd-gui (Java Decompiler with graphics mode)?
• How to install Jboss Developer Studio on Linux ?
• Find And Remove files with one command on Linux. 
• How to set Java version on Linux. 
• Vi editor not working properly - ubuntu.  

Find a sub string from as string splited by delimiter in MYSQL

String operation with MYSQL database is quite simple. But some times it found very ridiculous to find a expected result.That exactly happens with me. Actually I was expecting the result like below :-

But when I run my query it generates the out put like below:-

But i want only the name before the first comma(,) occurrence like :-

 Jon kumar Pattnaik from first row.

To find the expected result from database I have used SUBSTRING_INDEX(str,delim,index count) method.A string operation method .

Query :-

select SUBSTRING_INDEX(GROUP_CONCAT(s.vchSName,' ',vchMidName,' ',vchLastName),',',1) as nam,vchSGudian,vchSGRelation  from t_emp_details ;


Now its running fine with a good expected result.



SUBSTRING_INDEX(str,delim,index count)

Notes :- Parameters

str- is the main string from which we need to find the substring.
delim- is the separator of main string.
index count- is the number of separator you want to find.

Example:-
You,are,a,programmer.

select SUBSTRING_INDEX( 'You,are,a,programmer',',',1) from myTable;

Out put:- You

Here in the above query :-

You,are,a,programmer.--( Main String)
comma (,) -- (Separator or delim)
1 --(Index Count)

MySQL LIMIT is Not working

Case Studies:-
I got question that LIMIT is not working properly,but actually as per the manual kit of mysql LIMIT work like below :

The first parameter indicates the starting record (first record is 0). The second parameter is the number of records to return, NOT the last record to return.

LIMIT 0, 10
This will return 10 records starting at record 0 (i.e. 0 - 9)

LIMIT 10, 20
This will return 20 records starting at record 10 (i.e. 10 - 29)

LIMIT 10, 10
This will return the 10 records starting at record 10 ( 10 - 19).


Example :--
select * from dbName.emp limit 10,10;




Posted By:-javadevelopersguide
Follow Link-http://javadevelopersguide.blogspot.com

MYSQL ->> Use of LIMIT keyword

• Sometimes Normally it would prefer to do a full table scan.

• If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast.
  

• When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

• In some cases, a GROUP BY can be resolved by reading the key in order (or doing a sort on the key) and then calculating summaries until the key value changes. In this case, LIMIT row_count does not calculate any unnecessary GROUP BY values.

• As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS.
  



• LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns. (This trick does not work in the MySQL Monitor (the mysql program), which merely displays Empty set in such cases; you should instead use SHOW COLUMNS or DESCRIBE for this purpose.)

• When the server uses temporary tables to resolve the query, it uses the LIMIT row_count clause to calculate how much space is required.

Example :-

select * from dbName.emp limit 10;

The above query retrive only 10 record from emp table.But some times we need interval type of record such as:- 'emp' table has 100 record but , Every time we need 10 record at that time the query like below :

select * from dbName.emp limit 0,10;
select * from dbName.emp limit 10,10;
...
..
..
..
..
Like this.

Case Studies:-MySQL LIMIT is Not working
I got question that LIMIT is not working properly,but actually as per the manual kit of mysql LIMIT work like below :

The first parameter indicates the starting record (first record is 0). The second parameter is the number of records to return, NOT the last record to return.

LIMIT 0, 10
This will return 10 records starting at record 0 (i.e. 0 - 9)

LIMIT 10, 20
This will return 20 records starting at record 10 (i.e. 10 - 29)

LIMIT 10, 10
This will return the 10 records starting at record 10 ( 10 - 19).


Example :--
select * from dbName.emp limit 10,10;




Posted By:-javadevelopersguide
Follow Link-http://javadevelopersguide.blogspot.com

SQL: User ERROR orUSER STORED PROCEDURE

--Create a table TEST1 having 2 field T_ID,T_NAME
CREATE TABLE TEST1
(
T_ID INT IDENTITY(1,1) PRIMARY KEY,
T_NAME VARCHAR(25)
)


--Create another table TEST2_DEPEND having 2 field TD_ID,TD_ADDRESS
GO
CREATE TABLE TEST2_DEPEND
(
TD_ID INT FOREIGN KEY REFERENCES TEST1(T_ID),
TD_ADDRESS VARCHAR(25)
)
GO

--Insert into TEST1 Values
GO
INSERT INTO TEST1 VALUES ('Manoj')
GO
INSERT INTO TEST1 VALUES ('Kumar')
GO
INSERT INTO TEST1 VALUES ('Bardhan')
GO

--User Stored Procedure For Insert/Delete (Pass 'D' for Delete,'I' for Insert)
--This Below defined procedure raiserror user defined error to user when some Error
--like Foreign Key Violation,Duplicate Key Entry (Primary key) or any other errors.
--I have first get the Error No. and then check the Error and Error No given in sys.messages message_id,language_id.
--Language id is defined in sys.syslanguages msglangid for us_english language(1033).ERROR_NUMBER() function is used
--to get the Error No. from the current session.

--I have used SQL Transaction for better accuracy of data in database .I used Begin Tran,ROllback Tran,Commit for
--implementing TCL structure in SQL Database.If any error occurs during execution the wrong data may not violate the
--database implemented rules.

GO
CREATE PROC USP_TEST1
(
@OP_TYP VARCHAR(3),
@ID INT,
@ADDRESS VARCHAR(30)=NULL
)
AS
BEGIN TRY
BEGIN TRAN
IF @OP_TYP='I'
BEGIN
INSERT INTO TEST1 VALUES(@ADDRESS)
END
ELSE IF @OP_TYP='D'
BEGIN
DELETE FROM TEST1 WHERE T_ID=@ID
END
COMMIT
END TRY
BEGIN CATCH
DECLARE @TMP VARCHAR(MAX)
SET @TMP=ERROR_NUMBER();
IF @TMP=547
BEGIN
--RAISERROR raise Error For user (If Any)
RAISERROR('Cannot Delete! Value Dependency (Foreign key)??? ',16,1)
--Transaction Rollback for keeping only Old Data
ROLLBACK TRAN
END
ELSE IF @TMP=2627
BEGIN
RAISERROR('Cannot Insert! Cannot Insert Duplicate Value ???',16,1)
--Transaction Rollback for keeping only Old Data
ROLLBACK TRAN
END
ELSE
BEGIN
--RAISERROR raise Error For user (If Any)
RAISERROR('Some Error in DataBase??',16,1)
--Transaction Rollback for keeping only Old Data
ROLLBACK TRAN
END
END CATCH
GO



--Insert value Into Foreign key Table Value
INSERT INTO TEST2_DEPEND VALUES(2,'Bhubaneswar')

--Execute the Procedure For Delete From Primary Table(D=Delete,I=insert)
EXEC USP_TEST1 'D',2

--Expected Output Error

(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure USP_TEST1, Line 26
Cannot Delete! Value Dependency (Foreign key)???




I have used all this type of error handling error for the developers which will not hamper the frontend developer to handle error from
application frontend.For better knowledge about Errors & Languages in SQL Follow the below lines.


SELECT * FROM SYS.MESSAGES

SELECT * FROM SYS.SYSLANGUAGES


Posted By:-javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com

SQL: Adding IDENITY property to Existing Column

--Adding IDENITY property to Existing Column
--Steps to Add Identity
1.Add new Column with IDENTITY
2.Drop Constraint (if Any)
3.Drop the Old Column
4.Rename the New Column with Old Column Name


--Existing Table
CREATE TABLE Employee
(
Emp_Id INT PRIMARY KEY,
Emp_Name VARCHAR(100)
)

--1St Add a New Column with Identity
ALTER TABLE Employee
ADD EmpNew_ID INT IDENTITY (1,1)


--2Nd Drop The Primary Key Constraints(if Primary key is exist)
ALTER TABLE Employee
DROP CONSTRAINT PK__Employee__262359AB07020F21

--3Rd Drop the Old ID from Table
ALTER TABLE Employee
DROP COLUMN Emp_ID

--4Th Rename the New Column with OLD Column Name
SP_RENAME 'Employee.EmpNew_ID','Emp_Id','COLUMN'



--Now enjoy the Column & Table with new Flavour of IDENTITY.Really I had so much curisity to know when I got this from one
--of my friend as Co-worker.Firstly I though that it is so simple as we alter any column of any table but after 20-30 min.I
--realize that this is the proper way for accomplish the Task.But , be sure that that table donot have any data and always consider
--for Primary key ,if necessary first drop the Primary key the proceed.May be the cadinal position of the changed column.



--Show the Table with New Form
SELECT * FROM TEST_TABLE


















--Posted By: javadevelopersguide
--Link:-http://javadevelopersguide.blogspot.com

SQL: Rename Column or Tables,Use of SP_RENAME

--Rename a Column Name replacing Old_Name

EXEC SP_RENAME 'TABLE_NAME.COLUMN_NAME','COLUMN_NAME','COLUMN'

OR

SP_RENAME 'TABLE_NAME.COLUMN_NAME','COLUMN_NAME','COLUMN'




ALTER TABLE Employee
ALTER COLUMN EMP_NAME VARCHAR(100)



--Renaming a table

EXEC SP_RENAME 'Employee', 'New_employee'

OR

SP_RENAME 'Employee', 'New_employee'




SP_RENAME, Procedure Changes the name of a user-created object in the current database.


Remarks:

Changing any part of an object name can break scripts and stored procedures.
I recommend you do not use this statement to rename stored procedures, triggers,
user-defined functions, or views; instead, drop the object and re-create it with the
new name.

To rename objects, columns, and indexes, requires ALTER permission on the object.
To rename user types, requires CONTROL permission on the type. To rename a database,
requires membership in the sysadmin or dbcreator fixed server roles .



Posted By:- javadevelopersguide
Link: http://javadevelopersguide.blogspot.com

SQL: Fetching Record One-by-One without using Cursor

--Fetching Record One-by-One without using Cursor


DECLARE @EMP_ID CHAR( 11 )
SET ROWCOUNT 0
SELECT * INTO #MYTEMP FROM Employee

SET ROWCOUNT 1

SELECT @EMP_ID = EMP_ID FROM #Employee

WHILE @@ROWCOUNT <> 0
BEGIN
SET ROWCOUNT 0
SELECT * FROM #MYTEMP WHERE EMP_ID = @EMP_ID
DELETE #MYTEMP WHERE EMP_ID = @EMP_ID

SET ROWCOUNT 1
--Set one id to @EMP_ID from #MYTEMP
SELECT @EMP_ID= EMP_ID FROM #MYTEMP
END
SET ROWCOUNT 0



Remarks:

Always avoid to use cursor due to slower execution.In the above query Cursor can be
replaced by above way.I used a tempory table and Rowcount property.When ROWCOUNT is 0 ,the sql statement
can execute with any number of record operation,But when ROWCOUNT is 1
at that time the the sql statement can work with only one Record.So I have used this for my
requirement ,without using CURSOR record fetching One-By-One is possible.




Posted By:javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com

SQL:Disable Or Enable a Trigger

Triggers are enabled by default when they are created. Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger does not fire when any Transact-SQL statements on which it was programmed are executed. Triggers can be re-enabled by using ENABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.


Purpose of the following Trigger
---------------------------------
--AVOIDE TO DROP TABLES FROM DATABASE

USE Test_Manoj
Go

CREATE TRIGGER TRG_DB_SAFTY
ON DATABASE
FOR DROP_TABLE
AS
RAISERROR('DROP CANNOT POSSIBLE,PLEASE CONTACT ADMIN..',16,1)
ROLLBACK ;

After creation of trigger any point of time a trigger can be disable or enable by the user.For Disable a trigger(de-activate) DISABLE keyword is used as follows:-

--Disable Trigger on Database

USE Test_Manoj
Go
disable TRIGGER TRG_DB_SAFTY on database

For Enable a trigger on Database,any other object we need ENABLE keyword as follows:-


--Enable trigger on Database

USE Test_Manoj
Go
ENABLE TRIGGER TRG_DB_SAFTY on database

Disabling all triggers

----------------------------
--Disable all triggers on all server

USE Test_Manoj
Go
DISABLE Trigger ALL ON ALL SERVER

OR

--Disable all triggers in Database

USE Test_Manoj
Go
DISABLE Trigger ALL ON DATABASE
Go


Disable Trigger on Table
---------------------------------
USE Test_Manoj
Go
DISABLE Trigger ALL ON DBO.MY_EMP
Go





Posted By:-javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com

SQL: Function for finding Number of Column in a Given Table

Below a function which can be used to find the no.of column in a specified Table.This function returns an INT(Scalar Value).This Type of function is known as scalar valued function.


--FUNCTION FOR FINDING NO OF COLUMN FROM GIVEN TABLE

--Create a function with Table_Name as Parameter
CREATE FUNCTION FUN_COL_COUNT(@T_NAME VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @CNT INT
SELECT @CNT=MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@T_NAME
RETURN @CNT
END

--Run the Function

SELECT DBO.FUN_COL_COUNT('Table_name')

This above statement return the no.of column in the Table_name .Here DBO represents DataBase Object(Schema).More about Schema read some other articles/Posts.


Here Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

Posted By: javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com

SQL:Make your Database Read/Write Only

This post help you to make database read/write only,Before make read only be sure that no user is connected to that database.After confirm SP_DBOPTION will successfully set the status of Database.

1) By using ALTER TABLE
--SET DATABASE READ/WRITE ONLY
If the read-only requirements for the database are temporary, you will need to reset the configuration option following any procedures undertaken. This is achieved with a small modification to the ALTER DATABASE statement to indicate that the database should return to a writeable mode.
ALTER DATABASE Database_name SET READ_WRITE

The status read-only requirements for the database are temporary, but sometimes we need it for better administrative or Command over multiple user.
ALTER DATABASE Test_Manoj set READ_ONLY

2) By using SP_DBOPTION
EXEC SP_DBOPTION "Database_name", "READ ONLY", "TRUE"
EXEC SP_DBOPTION "Test_Manoj" ,"READ ONLY","TRUE"

The other possibility is to DENY tthe INSERT and UPDATE rights on the table that you want to set Read-Only. The Deny always override the Allow right. This way, your user will be part of the db_datawriter and you will explicitly override (and deny) the insert and update rights on some table.


Follow Link-http://javadevelopersguide.blogspot.com



By Manoj

SQL:Script for Drop All Foreign Key Constraints from Current Database

--Generate Script For Drop All Foreign Key Constraints From your Current Database
SELECT 'ALTER TABLE '+OBJECT_NAME(F.parent_object_id)+' DROP CONSTRAINT '+F.NAME FROM sys.foreign_keys F
Run the above Query.
Output-
ALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_PTCDALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_TNIDALTER TABLE COM_MST DROP CONSTRAINT FK__COM_MST__COM_TPC
Run the generated Above script
This Script will help you for Dropping all foreign key.Really, I was in trouble when my project Manager ask me for writing the query for Droping all Constraints and recreate.Really it was one of my one of the best effort to work with Database.I took the question of my project Manager and did it.


Posted By: Manoj K. Bardhan
Follow Link-http://javadevelopersguide.blogspot.com

SQL:Show Execution Plan of SQL Statement--SHOWPLAN_ALL

--show the execution plan of any statement --set Showplan_all OFF OR ShowPlan_text ON--Then Run your query check and set the Showplan to OFF
--Syntax
SET SHOWPLAN_ALL { ON OFF }
--Example
SET SHOWPLAN_ALL ON--Table is not created ,only Execution plan of the 'Create Table' type will displayCREATE TABLE TEST_TABLE(T_ID INT,T_NAME VARCHAR(50))
--Set the SHOWPLAN_ALL OFF SET SHOWPLAN_ALL OFF
--Table CreatedCREATE TABLE TEST_TABLE(T_ID INT,T_NAME VARCHAR(50))
Select Statement:
--On StatusSET SHOWPLAN_ALL ONSELECT * FROM TEST_TABLE
--Off StatusSET SHOWPLAN_ALL OFFSELECT * FROM TEST_TABLE

This Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resourcerequirements for the statements.
When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, informationabout all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table, informing users that the specifiedtable does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.SET SHOWPLAN_ALL is intended to be usedby applications written to handle its output. Use SET SHOWPLAN_TEXT to return readable output for MicrosoftWin32 command prompt applications, such as the osql utility.SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified inside a stored procedure; they must be the only statements in a batch.With SHOWPLAN_ALL statement:-
Parallel
0 = Operator is not running in parallel.(Not Sucessfully Executed)
1 = Operator is running in parallel (Sucessfully Executed)
SET SHOWPLAN_ALL returns information as a set of rows that form a hierarchical tree representing the steps taken by the SQL Server query processor as it executes each statement. Each statement reflected in the output contains a single row with the text of the statement, followed by several rows with the details of the execution steps. The table shows the columns that the output contains.Also follow SHOWPLAN_TEXT & SHOWPLAN_XML.

Posted By: Manoj K. Bardhan
Follow Link-http://javadevelopersguide.blogspot.com

SQL:FINDING ALL THE TABLE NAME & COLUMN NAME WHICH HAVING IDENTITY FIELD(AUTO INCREMENT)

--FINDING ALL THE TABLE NAME & COLUMN NAME WHICH IDENTITY IS ON(1)
SELECT C.NAME AS COLNAME,T.NAME AS TABLENAME,C.IS_IDENTITY FROM SYS.COLUMNS C INNER JOIN SYS.TABLES T ON C.OBJECT_ID=T.OBJECT_ID WHERE C.IS_IDENTITY=1


This post has so many uses for manages databases.

Posted By:-javadevelopersguide
Follow Link:-http://javadevelopersguide.blogspot.com

SQL:MAKE YOUR DATABASE OFFLINE/ONLINE

--SET YOUR DATABASE OFFLINE/ONLINE FOR DISPLAY --THIS COMMAND WILL HIDE --THE DATABASE FROM ALL USER
Setting a Database Offline/Online by using 3 way we can do.
Way 1.(By using Alter DataBase)
ALTER DATABASE SET OFFLINE
--Make ofline
ALTER DATABASE Test_Manoj SET OFFLINE
--Make online
ALTER DATABASE Test_Manoj SET OFFLINE

Way 2.(By using sp_dboptions)
--Set the Database Offline
sp_dboption database_name,'offline',true
--Set the Database Offline
sp_dboption database_name,'offline',false
Ex:-
sp_dboption Test_Manoj,'offline',true
Way 3.(By SQL Management Studio)
=>View Menu
=>Object Explorer
=>Right Click on Database
=>Task
=>Take Offline


Now , Your Database is in Offline Mode/Online Mode and enjoy the administrative command on SQL Server.

Posted By :javadevelopersguide
Follow Link-:http://javadevelopersguide.blogspot.com

SQL SERVER :Delaying Sql Execution-Use of WAITFOR Cluse

--Example:-1

--WHILE LOOP

--USE WAITFOR FOR DELAYING THE EXECUTION FOR SPECIFIED TIME

GO

DECLARE @T INT

SET @T=1

WAITFOR DELAY '00:00:10'

WHILE @T<=10

BEGIN

PRINT 'MANOJ_KUMAR'

SET @T = @T+1

END

GO

--Example:-2

GO

WAITFOR DELAY '00:00:02'

SELECT 'THIS IS DB BLOG'

GO

Descriptions:-

It block/slow down the execution Process of any sql statement(Procedure/Commands).The delay specified period of time that must pass, up to a maximum of 24 hours, before execution of a batch, stored procedure, or transaction proceeds. While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.

The actual time delay may vary from the time to time specified and depends on the activity level of the server. The time counter starts when the thread associated with the WAITFOR statement is scheduled. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time.

WAITFOR does not change the semantics of a query. If a query cannot return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified. Cursors cannot be opened on WAITFOR statements and Views cannot be defined on WAITFOR statements.

Every WAITFOR statement has a thread associated with it. If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run. SQL Server monitors the number of threads associated with WAITFOR statements, and randomly selects some of these threads to exit if the server starts to experience thread starvation.

Conclusion:- You can create a situation for Deadlock Condition.

SQL SERVER: Finding nth Highest Value

--Select 4th Highest Salary (1-way)

SELECT TOP 1 E_ID FROM (SELECT DISTINCT TOP 4 E_ID FROM EMP ORDER BY E_ID DESC)A ORDER BY E_ID ASC

--Select 4th Highest Salary (2-way)

SELECT MIN(E_ID) FROM (SELECT TOP 4 E_ID FROM EMP ORDER BY E_ID DESC)E

Description:-

This is one of the best query that I posted ,when I got an interesting question regarding this.Use of sub-queries makes complex perforemance.