Tag Archives: WHERE Condition

“WHERE” Condition with LIKE in ACCESS:

“WHERE” Condition with LIKE in ACCESS:

Generally we used to work with SQL-Server or ORACLE database so we are very well aware with its SQL syntaxes but when sometime we have to deal with ACCESS database, we don’t care to learn it and use our SQL knowledge in ACCESS database. This might lead to logical or syntax error sometime. Once, I have been asked by one of my colleague that simple LIKE is not working in ACCESS database. I had a quick look of it and found one syntax problem in his SQL statement.

Unlike SQL-Server ACCESS database won’t accept”‘%” wildcard. Rather it will accept “*” (asterisk) sign. So if you want to select data from EMPLOYEE table whose first name starts with “R” then you should go for:

SELECT * FROM Employee WHERE FirstName like ‘R*’

Rather than SELECT * FROM Employee WHERE FirstName like ‘R%’

Second SQL statement is wrong in ACCESS database. It’s a logical error.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

WHERE and HAVING clause in Microsoft SQL-Server

WHERE and HAVING clause in Microsoft SQL-Server
People may get confused many time about usage of WHERE and HAVING clause. I am just trying to throw little bit light on WHERE and HAVING clause to make its concept clear. In my many interview session, I asked this simple question to lot of candidate and many of them are aware with “WHERE” condition but not or partial aware with “HAVING” so I felt to write this simple article for them.
“WHERE” is a condition used with T-SQL statement. It always apply to the row and “HAVING” is a clause which always apply to the summarized row (should use with group by).
“WHERE” could be used to make condition on SELECT statement or else can be used with single row function as well. You must have to specify “WHERE” condition before GROUP BY clause.
Whereas, “HAVING” clause is used to make condition on “GROUP BY” data and suppose to be used after “GROUP BY” clause.
Moreover, “HAVING” clause can be used in SELECT statement with “GROUP BY” clause and whenever “GROUP BY’ is not present, “HAVING” will work as “WHERE”.
That’s enough theory, right???? J Anyway, let us see this practically with creating one demo table and query it along with WHERE and HAVING clause.

–Create one table

CREATE TABLE BlogCount

(

BloggerName VARCHAR(10),

Topic VARCHAR(15),

[Year] INT,

Total INT

)

–Insert records in above table

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2005,10)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2006,17)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2007,124)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2008,124)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘.NET’,2008,24)

INSERT INTO BlogCount VALUES(‘Alka’,‘SQL’,2007,14)

INSERT INTO BlogCount VALUES(‘Alka’,‘.NET’,2007,18)

INSERT INTO BlogCount VALUES(‘Alka’,‘SQL’,2008,14)

–Query with WHERE and HAVING clause together

SELECT BloggerName,AVG(total) AS ‘Average’ FROM blogcount

WHERE Topic=‘SQL’

GROUP BY BloggerName

HAVING AVG(total)>25


First WHERE clause filter the record set and then it will be passed to GROUP BY and HAVING for further filtering.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah