SQL SERVER – Query Writing Strategy – SQL Queries 2012 Joes 2 Pros Volume 1 – The SQL Queries 2012 Hands-On Tutorial for Beginners

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 1. 

Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

Why Buy this Book: Weather you are a tester, developer, or administrator of SQL there are some basic terms and skill they are all expected to know. The core of design, permissions, queries, and SQL objects is often many separate books. But what if you want the proficient base across all these displaces. If you are starting out or are self-thought this will help fill in the pieces you may not know was missing.

What will I learn after reading this book: Queries with all types of Joins, Creating tables and stored procedures, Transactions, Login Permissions, and workplace tips?

Here is something I have yet to find in any book. When enthusiastic SQL students do this, they experience a revelation. The number of errors drops significantly and the speed at writing queries increases immediately. Knowing how to narrow down what we are looking for amongst a vast list of choices helps immensely.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 1 in the file SQLQueries2012Vol1Chapter4.0Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Query Writing Strategy

When visiting a new restaurant, we will ask to see the menu, because we want to see all they have to offer. The odds are that we might be interested in half of the items, but only need a few dishes for our group at the table. Looking at the menu is like starting a query with a ‘SELECT *’ statement. Once we have looked at all the fields, we narrow our choice(s) to only the items we want at the time.

Sometimes restaurants have multiple menus. My favorite restaurant has a kids’ menu, an adult menu, a gluten-free menu and a drink menu. After looking this over a selection of what I liked was narrowed a few items. The Menu organizes what we have to choose from then we pick the one we want. We can write queries this way using the same two phases.

Phase I: Organize. When building a new query from many tables, we often find ourselves wondering, “Where do I start?” First, lay the steps out by identifying which tables contain the essential data. Second, get all the table joins working with a basic ‘SELECT *’ statement. Third, add any basic filtering criteria.

Phase II: Itemize. Once all joins and criteria, such as SELECT, FROM and WHERE are working, we are ready for Phase II. This entails going back and changing our ‘SELECT *’ to an itemized SELECT field list as the final step.

Let’s say we know what fields we want in our query even before we start. In this figure we write a SELECT statement and just have one of the two tables needed in FROM clause. When completed, the FROM clause will have both tables, but for now we just want to get the Location table working. By using the ‘SELECT *’ strategy on the left, we remove any possible errors from line 1. From there, we can focus on the more complicated logic used for joining tables together. We can add tables one at a time until everything is working. This is the Organize phase.

After our query is organized and working, we can go back and itemize the SELECT field list to display only the fields that are necessary. This is done during Phase II (Itemize). The steps for this system are broken down as follows:

Phase I: ORGANIZE (write a SELECT * query statement with joins)

--Test first table logic
SELECT *
FROM Location
--Test second table with join
SELECT *
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
--Test all tables with criteria
SELECT *
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
WHERE [State] = 'WA'

Phase II: ITEMIZE (itemize the SELECT field list)

--Choose the fields
SELECT FirstName, LastName, City, [State] FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
WHERE [State] = 'WA'


Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

Reference: Pinal Dave (https://blog.sqlauthority.com)