Click here to get free chapters (PDF) in the mailbox
Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.
Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.
SQL Quiz
Writing a WHERE clause has been the easiest thing. Most of the time, we learn doing this as a basic lesson in SQL. Many times, I frequently see a simple question asking where one has to write a WHERE clause. This brings many to either failure or just plain confusion. Let us see how many different ways you can write the following WHERE clause-related question:
How can you write a WHERE Clause to search names between A-J?
Link to participate in SQL Quiz
Notes of Vinod Kumar
No developer can work inside SQL Server and not perform a SELECT query without a WHERE clause. This is a basic requirement to become a successful programmer. The usages of clauses can be simple and complex as possible. In this context, it is also worth reading some content around short-circuiting for query.
Notes of Pinal Dave
The WHERE clause is indeed crucial in any query. I have seen many accident scenarios where a developer forgot to mention the WHERE clause and an innocent-looking query was then turned into a very dangerous query. If the DELETE statement is executed without the WHERE clause, and if deleting a complete table is not intended, there will be some disaster recovery needed to be done for sure. At that point in time, the recovery will be likely used to rollback the database to the pre-disaster moment. A SELECT statement without a WHERE clause can lead to a table scan, which is surely not desired if you want to achieve optimal performance.
Additional Notes
SQL Server Interview Questions and Answers ISBN: 1466405643 Page#38-40
Does Order of Column in WHERE clause Matter?
Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
Logical Query Processing Phases – Order of Statement Execution
Prize
There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.
Link to participate in SQL Quiz
Note: The SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
Question:
How can you write a WHERE Clause to search names between A-J?
Actually it should names starts with characters between A and J.
Answer :
Where ASCII(Left(Name,1)) Between ASCII(‘A’) AND ASCII(‘J’)
Note :- it is case sensitive
SELECT *
FROM [AdventureWorks].[Person].[Contact]
Where FirstName like ‘[A-J]%’