Difference between a "where" clause and a "having" clause?

Having can used only with the select statement .it is typically used with group by clause . when it is used without group by it work like where clause

WHERE clause is used to impose condition on SELECT statement as well as single row function and is used before GROUP BY clause where as HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query


1. Having clause is usually used with Group By clause although it can be used without it too.
2. 'Having' is just an additional filter to 'Where' clause.
3. 'Where' clause applies to the individual rows whereas 'Having' clause is used to test some condition on the group(usually aggregate methods) rather than on individual rows.

Where and Having are two different things. Having is NOT just another WHERE clause. DB2 will not let you use HAVING without GROUP BY . SYBASE and SQL Server allow HAVING to be used alone but the results can be very unexpected. Be very careful of this, it usually will not produce what you want.
WHERE applies to rows, HAVING applies to summarized rows (summarized with GROUP BY) if you wanted to find the average salary in each department GREATER than 333 you would code:
SELECTÂ DEPARTMENT, AVG(SALARY)
FROMÂ Â Â Â EMP
WHERE DEPARTMENT > 333
GROUP BY DEPARTMENT
IF you then wanted to filter the intermediate result to contain departments where the average salary was greater that 50,000 you would code:
SELECTÂ DEPARTMENT, AVG(SALARY)
FROMÂ Â Â Â EMP
WHERE DEPARTMENT > 333
GROUP BY DEPARTMENT
HAVING AVG(SALARY) > 50000.
Where executes first
GROUP BY next
and finally HAVING
How will you copy the structure of a table without copying the data?
This interview questions are belongs to SQL Server and people are giving answers to Oracle. Please be sure to understand the question and give the answer. Wrong answer may spoil a person interview. Here is the right answer Without Data : select * into temp2 from temp where 1=2 With Data : select * into temp2 from temp

Give some examples of Analytical functions?

return the infornation that about the relative possition of record (ROW_NUMBER,Rank,DENSE_RANK) and allow to campare with succesor and precedessors when they sort in spacific order.
select ename, deptno, sal, hiredate, row_number() over ( partition by deptno order by sal desc, comm desc , hiredate desc ) sal_rank_in_dept, row_number() over (order by sal desc) sal_rank_overallfrom emporderby deptno, sal desc, comm desc, hiredate desc

AVG, COMPUTE, DENSE RANK, NTile, Over, RANK, Row Number, STDEV, SUM, VAR, VARP, WITH ROLLUP


What is the difference between "translate" and "replace"?


Replace replace every instence of character with character sting by the given charator of string. this work for entire word and pattern,. Translate replace the one charecte at a time.Translating the nth character match with the nth character with the replacemnt string .

Please refer that here we are discussing about Sql Server only. There is no function named 'Translate' in Sql Server. 'Translate' function exists in Oracle for translating a single character to some other character.Replace fucntion in Sql server is used to replace a string as well as character to another string or character. No need to have a seperate function like Transalate in Oracle:Find below the examples to get the clear idea:Ex 1: Sql Server replace function similar to Oracle Replace function

Declare @Str varchar(100)
set @Str='India is my country'
print @Str
print replace(@Str,'India','Pakistan')Result
India is my country
Pakistan is my country

Declare @Str varchar(100)
set @Str='Think You'
print @Strprint replace(@Str,'i','a')

What is DYNAMIC SQL method ?


If we are speaking about SQL Server and Dynamic SQL, then the following is the answer.Some queries in SQL Server might be given during run-time as we may not know the table that we might use in the procedure or batch. And so we do a dynamic SQL Statement.CREATE PROC Some_sp(@tblname varchar(80) ----Input is table name)
asbeginDECLARE @Qry Varchar(32)
SET @Qry = 'Select * from ' + @tblnameEXEC (@Qry)EndCompile and run the above stored procedure and then run the following execute statement.
EXEC Some_sp 'Employees'This is a very simple dynamic SQL. You can make it more complex and they have few restrictions that you need to keep intact.

How to remove duplicate records from a table?

--Example of Simple Duplicate Records Deletioin
-- To Create A Table Structure Create Table Emp( EmpNo Int, EmpName Varchar(20), Address Varchar(20), City Varchar(20))
--Insert the Emp Table in Values
Insert Into Emp Values(1,'Fazlur Rahiman','Al Maktum St','Dubai')Insert Into Emp Values(2,'Kalyan','Broad Way','Chennai')Insert Into Emp Values(3,'Vijay','Mount Road','Chennai')Insert Into Emp Values(4,'Kumar','Parys','Chennai')Go
-- To Insert a Duplicate Records Insert Into Emp Values(1,'Fazlur Rahiman','Al Maktum St','Dubai')GO
--Create temp table to hold duplicate Table (#EmpTemp)Create Table #EmpTemp( EmpNo Int, EmpName Varchar(20), Address Varchar(20), City Varchar(20))
--Identify and save duplicate data into #EmpTempInsert Into #EmpTempSelect * From EmpGroup By EmpNo,EmpName,Address,CityHaving Count(*) > 1
--Delete duplicate from original tableDelete From Emp Where Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) in (Select Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) From empGroup By EmpNo,EmpName,Address,CityHaving Count(*) > 1)
--Insert the delete data backInsert Into EmpSelect * From #EmpTemp
--Check for dup data.Select * From EmpGroup By EmpNo,EmpName,Address,CityHaving Count(*) > 1
--Check tableSelect * From Emp
--Drop temp tableDrop Table #EmpTemp

How to convert RAW data type into TEXT?

You can use Bulk copy program (BCP) to convert a data from row to text or text to row

Why can a "group by" or "order by" clause be expensive to process?

Group by class will have to group all the data returing from the query in our specifed groups... so it is definitly an overhead from execution point of view... imagine that the number of rows returned by the query are in millions and you have to group the data. its pretty much the same as if you are asked to line up the excactly matching cars in a lot of cars in a parking lot n it will definitly take you time to figure out the exactly matching cars and then park them accordingly.

What are "HINTS"? What is "index covering" of a query?


There are there types of hints : Query Hints, Index hints and lock hints and used for forcing the behavior of the statement as per our expectation.
Covering index will cover will have same columns in the select and where cause and index covers all the columns.

What is a VIEW? How to get script for a view?


View is a logical partition of one or more table in horizontally(by putting condition) or vertically (by including selected columns).
View can be used to update table, provides security mechanism.
view defionition(qurey) stored as plan text or Encrypted text
We can see view definition by using following stored procedure
exec sp_helptext viewname

Difference between "VARCHAR" and "VARCHAR2" datatypes?


What is the difference among "dropping a table”,” truncating a table" and deleting all records" from a table?


DROP Drops the table records and structure from the DB schema. Nothing else is leftout. Can't rollback.
Truncate Clears the records from the table but the table structure still remains. Can't roll back.
delete from : deletes the rows based on the query or will delete all the rows if no "where" clause is given. Can roll back i.e deleted records can restored.
In the first two cases transaction log is not done, but with delete transaction log is created. So it consumes more time compared to the above two.
What's the use of SQL Profiler?
It's a tool that ships with SQL Server to profile sql querie and fine tune slow performance degrading sql queries by looking at how sqlsever executes them . It contains tools like index tuning wizard to improve performance.Go to http://microsoft.msdn.com and search for SQL profiler for more detailed information on how to use it and what features are available with ample examples provided.

What is the use of database index (apart from fast searching of records);What is use of composite key ?


Indexes are useful for maintaining unique values in a column, since the database can easily search the index to see if an incoming value already exists. Primary keys are always indexed for this reason.
By default Primary keys will create Clustered Index(where tipical values are stored in Leaf level),
UNIQUE will create Non Clustred Index(where the references of the values are stored)
Since index entries are stored in sorted order, indexes also help when processing ORDER BY clauses.


Index is used for the fast retrieval of data from the table. there are two types of indecs Clustered and Non clustered. Cluster index is created on primary key and it can be only on per table where as non cluster index can be created on any column and it can be more than one in a table.
Composite key is the combination of more than one attribute. Composite key is is created when it is not possible to identify one column in a table that has unique values. In such situation combination of more than column is choosen by which a record can be identified easily and clearly.It is just like primary key