SQl Server Quieries

select a.donorname,b.ename from donor_details a,emp_details b

select top 2 * from emp_details order by username

select (sum(sal)) from emp_details

select (max(sal)) from emp_details

select distinct(desg) from emp_details

select * from emp_details where eid in (4 , 5,6)

select * from emp_details where eid =4 or eid =18 or eid=6

 select * from emp_details where ename ='bhargav' and password='kiran'

select count(0) as records from emp_details

select ename, email ,count(*)from emp_details group by ename , email

  select ename, email ,count(*)from emp_details group by ename

select distinct ename,email from emp_details group by ename , email

 select  ename from emp_details group by ename

select  ename,email,desg from emp_details group by ename , email ,desg
having desg = 't2'

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

In recent interview sessions in hiring process I asked this question to every prospect who said they know basic SQL. Surprisingly, none answered me correct. They knew lots of things in details but not this simple one. One prospect said he does not know cause it is not on this Blog. Well, here we are with same topic online.

Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. (Reference :BOL)

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

What is difference between DISTINCT and GROUP BY?

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:
SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:
SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:
SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

instead of trigger raising in sqlserver 2005

A trigger is a database object similar to a stored procedure that executes in response to certain actions that occur in your database environment. SQL Server 2005 is packaged with three flavors of trigger objects: AFTER, data definition language (DDL), and INSTEAD-OF.
AFTER triggers are stored procedures that occur after a data manipulation statement has occurred in the database, such as a delete statement. DDL triggers are new to SQL Server 2005, and allow you to respond to object definition level events that occur in the database engine, such as a DROP TABLE statement. INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example, attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table.




alter trigger insteadofpro2 on pro instead of delete
as begin
declare @pno int;
declare @productname varchar(50);
declare @cost varchar(50);
declare @action1 varchar(100);
select @pno=d.pno from deleted d;
select @productname=d.productname from deleted d;
select @cost=d.cost from deleted d;
select @action1='delete record insted of ';
if(@cost=10000)
begin
raiserror ('Cannot deletd where sal =3000',16,1)
rollback
end
else
begin

commit
delete from pro where pno=@pno
insert into protrigger values(@pno,@productname,@cost,@action1,getdate())
print'record delete bfore insert';
end
end