Best Way to Write Basic SQL Queries

Share this & earn $10
Joes2Pros SQL Trainings
Published at : 27 Jan 2021
424052 views
2465
166

SQL Server Query writing strategies 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 complex queries increases immediately. Knowing how to narrow down what we are looking for amongst a vast list of choices helps immensely. Grabbing the right tables first and then the fields second is akin to grabbing the right menu before ordering an item from it. In fact, one student named Tim took this back to his team of SQL developers and they immediately implemented this process.

We are all used to following steps. Most of the time, actions are sequential from top to bottom or left to right. Other times we complete things in phases. The two phases we are going to use in this exercise apply to joining tables. This is easy to implement as we only need remember to organize first and clean up second.

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. These menus were each gathered at our table. Ultimately, in my head, a selection was narrowed to what was needed.

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 explore how this two-phase process of Organize and then Itemize can be a big time-saver. We are going to use one of the challenges from the last lab. In Lab 3.2 (Outer Joins), Skill Check 2, we needed to get four fields from two different tables. If we were to list all four desired fields and test one table at time, we will get an error as seen on the right side of the figure below.

In the figure below we write a SELECT statement and part of the FROM clause. When completed, the FROM clause will have two tables, but for now we just want to get the Location table working. By using the ‘SELECT *’ strategy, 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.

NOTE: SELECT * never results in an Error message stating “invalid column name”, however; a SELECT list with itemized field(s) can have this error.

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:

Since SELECT is always the first statement in a query, it’s natural to want to write the field names before writing the FROM clause. However; we can save time and trouble by using the ‘*’ until the entire query is working properly. When this is complete, it is very easy to itemize the field list, with the confidence of knowing it will not cause any problems.

Exercise:

Simple membership for a club:

Table1 (id_Member,name_Member, email_member, title, age, company)

Table2 (id_Club,club_name, club_Location, address)

Table3 (id_club, id_member,membership_date)


Q1: Show all member names, email, age, and company from Table1

Q2: Show all club name, location, and address data

Q3: Show member name, club name, club location (hint: Use joins and all 3 tables)
You can find all other classes related to this video here:
http://www.joes2pros.com/joes2pros/Course/Introduction_To_Microsoft_SQL_Server

Enroll Today & Get the First Month for only $1!
Use code: YOUTUBE1
SQL (Programming Language)Software (Industry)Learn SQL

5 previous of my draw few years ago~

5 previous of my draw few years ago~

James Washington EXPLOSIVE 50 Yard Touchdown

James Washington EXPLOSIVE 50 Yard Touchdown

Spoil le dernier Game of Thrones - Prank - Les Inachevés

Spoil le dernier Game of Thrones - Prank - Les Inachevés

A Fireside Christmas

A Fireside Christmas

8 Platforms To Sell Your eBooks Online You Simply Must Try

8 Platforms To Sell Your eBooks Online You Simply Must Try

Top 10 Things Dickinson Got Factually Right & Wrong

Top 10 Things Dickinson Got Factually Right & Wrong

Become A Member of the International Association for Identification

Become A Member of the International Association for Identification

Lil Mosey - Noticed (Lyrics)

Lil Mosey - Noticed (Lyrics)

INVASIVE PHONE CALLS PRANK!

INVASIVE PHONE CALLS PRANK!

out of this world

out of this world

Bryson Tiller - Let Em' Know (Audio)

Bryson Tiller - Let Em' Know (Audio)

GOOD JOB! ANNA FORCES CARRYING ANISSA TO GET RID OF A CAR! VERY FAST ACTION!

GOOD JOB! ANNA FORCES CARRYING ANISSA TO GET RID OF A CAR! VERY FAST ACTION!

why Muslim WOMEN wear HIJAB ? why Muslim MEN keep BEARD ? Brigitte Gabriel Vs Mufti Menk  VERY FUNNY

why Muslim WOMEN wear HIJAB ? why Muslim MEN keep BEARD ? Brigitte Gabriel Vs Mufti Menk VERY FUNNY

CUTEST and FUNNIEST BABIES on Youtube - The best baby compilation

CUTEST and FUNNIEST BABIES on Youtube - The best baby compilation

No Nerf Will Stop Cypher

No Nerf Will Stop Cypher

THUNK a cappella - Searching for a Feeling (Thirdstory)

THUNK a cappella - Searching for a Feeling (Thirdstory)

Aminé - Riri (Official Video)

Aminé - Riri (Official Video)

2021 will be worse than 2020. It will only be better for attractive/rich people. (incel/doomer/rant)

2021 will be worse than 2020. It will only be better for attractive/rich people. (incel/doomer/rant)

Specialised Knowledge

Specialised Knowledge

Abbu Laye Motor Car (Urdu Poem) | (ابّو لائے موٹر کار (اردو نظم

Abbu Laye Motor Car (Urdu Poem) | (ابّو لائے موٹر کار (اردو نظم

Daily Challenge #34 / Easy Art  /  Power lines at sunset painting

Daily Challenge #34 / Easy Art / Power lines at sunset painting

roblox tik tok (often)

roblox tik tok (often)

Many Vintage 20W Greenback Speakers are ”Fake”!

Many Vintage 20W Greenback Speakers are ”Fake”!

Amazon Inventory Management

Amazon Inventory Management

Take land for reinforcing

Take land for reinforcing

I Want to be Like Mommy | CoComelon Nursery Rhymes & Kids Songs

I Want to be Like Mommy | CoComelon Nursery Rhymes & Kids Songs

IOTP Summary: Stagflation Is Occurring Before Your Very Eyes

IOTP Summary: Stagflation Is Occurring Before Your Very Eyes

How to Use Notability on iPad Pro

How to Use Notability on iPad Pro

របៀប Fix Your Advertising Access is Restricted | How to fix ads account restrict in Facebook Page

របៀប Fix Your Advertising Access is Restricted | How to fix ads account restrict in Facebook Page

Damaged Skin Repair in 30 Days - Close LARGE OPEN PORES & Remove Dark Spots | Get GLASS Skin

Damaged Skin Repair in 30 Days - Close LARGE OPEN PORES & Remove Dark Spots | Get GLASS Skin

Invisalign -The clear alternative to braces, says Parineeti Chopra. Invisalign treatment in India

Invisalign -The clear alternative to braces, says Parineeti Chopra. Invisalign treatment in India

Mind-Blowing Facts About Space

Mind-Blowing Facts About Space

STUDIO VLOG #14 | A GIFT?! & packaging more orders!

STUDIO VLOG #14 | A GIFT?! & packaging more orders!

Components of a Computer Network

Components of a Computer Network

5 Reasons to Take Supplements (AND WHEN YOU NEVER SHOULD!)

5 Reasons to Take Supplements (AND WHEN YOU NEVER SHOULD!)

You definitely should wear latex!/ 10 reasons why you should - by Beyla Hughes

You definitely should wear latex!/ 10 reasons why you should - by Beyla Hughes

LinkedIn Australia | Take Your Next Step

LinkedIn Australia | Take Your Next Step

How I Spend my NYC Bulge Bracket Investment Banking Salary | How much I ACTUALLY Make & How

How I Spend my NYC Bulge Bracket Investment Banking Salary | How much I ACTUALLY Make & How

ICT Policies

ICT Policies

Support and Resistance in Forex. Key Level and Significant Support and Resistance

Support and Resistance in Forex. Key Level and Significant Support and Resistance

Ebola: Inside An Outbreak - Documentary

Ebola: Inside An Outbreak - Documentary

Paragraphs on"A VISIT TO A FAIR" in easy words. let's learn English and Paragraphs.

Paragraphs on"A VISIT TO A FAIR" in easy words. let's learn English and Paragraphs.

EVS Most Important 50 Questions for CTET, DSSSB, KVS, UP-TET, MPTET-2020

EVS Most Important 50 Questions for CTET, DSSSB, KVS, UP-TET, MPTET-2020

Little Thieves - The Choice You Made (Official Video)

Little Thieves - The Choice You Made (Official Video)

Hair Mistakes That Age You Faster (LOOK MORE YOUTHFUL IN 2020)

Hair Mistakes That Age You Faster (LOOK MORE YOUTHFUL IN 2020)

Limp Bizkit - Intro (Significant Other)

Limp Bizkit - Intro (Significant Other)

AKAZAMZARAK : Un excellent héros ?

AKAZAMZARAK : Un excellent héros ?

SEEMS LIKE ( OFFICIAL VIDEO) | SAU JEET | LATEST PUNJABI SONG 2020|

SEEMS LIKE ( OFFICIAL VIDEO) | SAU JEET | LATEST PUNJABI SONG 2020|

GTA 5 Gunrunning DLC (Mobile Operations Center Purchase and Customization)

GTA 5 Gunrunning DLC (Mobile Operations Center Purchase and Customization)