©2017 Shelby Systems, Inc. Other brand and product names are trademarks or registered trademarks of the respective holders. 

Query:  SQL for Beginning Users of SQL Server Management Studio 

(Course E16) 

Presented by: Tim Wilson Shelby Software Developer 

    2 

Objective 

To learn how to use SQL Server Management Studio to write basic SQL scripts  

What This Session Covers: 

Basics of SQL Server components 

Instances of the Database Engine 

ShelbyDB, ArenaDB and ShelbyNext | Financials Table‐nomenclature  

Connecting to your ArenaDB or ShelbyDB database using SSMS  

Using the Query Builder tool to create a query 

Joining multiple tables using left and/or inner join  

Selecting information out of one or more tables and apply criteria 

Using manual SQL coding techniques to change the format of queried output 

   

    3 

INTRODUCING SQL SERVER COMPONENTS  

WHAT IS SQL SERVER?  SQL server is a Relational DataBase Management System (RDBMS) that Microsoft developed to manage and store information. It supports a wide variety of transaction processing, business intelligence, data analysis and other applications. As this graphic shows, there are many components available within SQL Server, but the object of interest for this class is SQL Server Management Studio. (SSMS). It is the means of accessing components of SQL Server, including the DataBase Engine.  

  The Database Engine is the central power plant of SQL Server. This core service is responsible for CRUD – create, read, update and delete – as well as providing controlled access and rapid transaction processing. It provides commit, rollback, and crash‐recovery capabilities to protect data.  Multiple “installations” of SQL server on a single computer is possible. Each installation is called an instance. An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance is independent and is started, stopped, deleted, modified, etc. without affecting other instances. Logging on to the correct instance is, therefore, crucial.  

    4 

Each instance of the Engine manages things like (not a comprehensive list): 

Logins and passwords 

User permissions 

Multiple Databases o Multiple Tables o Multiple Views o Multiple Stored Procedures o Multiple Functions, etc. 

Referential Integrity (A foreign key in a referencing table MUST refer to a valid row in the referenced table.) 

Constraints (rules that limit the type of data that can go into a table or column) o NOT NULL – Column MUST have data; it does not accept nulls. o PRIMARY KEY – A unique identifier for each record (namecounter / person_id) 

Identity – Value supplied and incremented by the Engine  Seed – Starting value 

o FOREIGN KEY – Field in one table that uniquely identifies a row of another table o INDEX – Method to speed up searching a database o CASCADING DELETE – When a record is deleted in a referencing table, related 

records in the referenced table(s) are also deleted. o CASCADING UPDATE – When a primary key is updated in one table, foreign keys 

in the referenced table(s) are also updated 

Triggers – SQL code that performs certain actions when a specified event takes place  We are dealing with NULLS, Primary, and Foreign Keys later in this session. The remainder of the listed constraints are just to illustrate the enormous power of SQL Server and some of the things it handles in the background that help keep your data clean and make information retrieval fast and efficient.    

    5 

LEARNING ABOUT SHELBYDB AND ARENADB TABLES   WHAT IS A DATABASE?  

Wikipedia – “…an organized collection of data”  

University System of Georgia – “…a collection of information organized to provide efficient retrieval”  

BusinessDictionary.com – “…systematically organized or structured repository of indexed information 

 A database can be as simple as a text file containing a list of names or as complex as ShelbyDB which has over 480 tables (depending on what modules have been purchased), each table holding a specific collection of information related to the whole. ArenaDB, with ShelbyNext | Financial tables included, has over 580 tables!  The Shelby/Arena/SNF database designs follow the rigid structure required of modern relational database management systems. They are organized by naming tables according to the module they serve and the subset of information they hold that relates to the module. For instance, NANames table (v.5) contains demographic information such as FirstMiddle, Lastname, Social Security, Gender, Marital status, etc. Each record (row) holds data for one person or organization that is unique to that entity.  The NAAddresses table (v.5), as the name suggests, holds street addresses, city, state, postal code, etc. for one address per row. Since each person (or organization) could have more than one address – Main/Home address, Seasonal address, Accounts Payable address, etc. – there is also a specific field per row that ties (relates) that row back to a specific row in NANames. This is called a relationship and is the heart of a relational database. Relationships are created using joins and are explained later in this session.   

      

    6 

TABLE‐NAMING PREFIXES  ShelbyDB uses a two‐character prefix while ArenaDB uses five characters (4 letters + an 

underscore).  

Extensive documentation about the ShelbyDB Table Layout and Structure is available online at 

http://www.shelbyinc.com/Documents/pdfs/v5/HSQTBLs.pdf. This 380+ page document details 

each table and its fields, keys, indexes etc. Each table is prefixed with a two‐letter code that 

identifies which module it services. For example NA = Globafile (Names and Addresses); PR = 

Payroll; AP = Accounts Payable, etc. 

ShelbyNext | Financials tables are currently stored in ArenaDB and use a three‐character prefix 

closely related to ShelbyDB’s nomenclature: two letters and an underscore. In fact, SNF uses 

most of the same two letters as ShelbyDB, without the NA tables. “NA” information generally 

uses Arena’s core_person and similar tables as the main repository of demographic information. 

 Arena currently has no extensive documentation on table structure and relationships like that 

available for v.5. However, using SSMS, one can determine primary and foreign keys (table 

relationships), indexes, triggers, constraints etc. Table prefixes are shorthand for the module 

the table(s) serve. 

 The following charts show most of the table prefixes used in ShelbyDB and ArenaDB (without SNF tables).    

    7 

 SHELBYDB TABLE PREFIXES (largely shared with SNF) 

 

AM  Expense Amortization  MB  Membership 

AP  Accounts Payable  MG  Matching Gifts 

AR  Accounts Receivable  MI  Profiles for Advanced Search 

BR  Bank Reconciliation  NA  GlobaFILE 

CA  Appointments  NU  Check‐In 

CK  Check Express  PO  Purchase Orders 

CM  Commemoratives  PR  Payroll 

CN  Contributions  RC  Resource Calendar 

DH  Deposits on Hand  RE  Receipting 

FA  Fixed Assets  RG  Registrations 

GF  Donors and Gifts  RM  Remittance 

GL  General Ledger  SE  Servant 

IN  Inventory  SG  Organizations and Small Groups 

IR  Insurance and 

Retirement 

SS  System Tables 

LN  Loan Processing  ST  Statistics 

   

    8 

ARENADB TABLE PREFIXES (without SNF) 

 

appt_  HQ Appointments  peer_  Peer Structure 

asgn_  Assignments  phon_  PBX phone connections 

chkn_  Check‐In  phto_  Photos 

comp_  Computer Systems  pmnt_  Online payment info 

core_  Core Arena  port_  Portals 

cpgn_  Campaigns  prot_  Background checks and 

counseling 

ctnt_  Content  pryr_  Prayer module 

ctrb_  Contributions  rept_  Report registration 

cust_  Custom tables not part of 

stock Arena 

rmnt_  HQ Remittance 

evnt_  Event Tags  rsrc_  Resource module 

feed_  Feeds  secu_  Security settings 

form_  HQ Forms  smgp  Small Groups 

list_  Public and My Lists  sync_  v.5/Arena synching 

mktg_  Marketing  trip_  Mission Trips 

mtrc_  Metrics  util_  Utilities 

orgn_  Organizations  volu_  Volunteer Module 

    9 

Understanding Simple Joins 

There are four kinds of joins in Transact SQL (T‐SQL): Full Join, Right Join, Left Join, and Inner 

Join. 

Full Join is rarely used and is not discussed. 

Since the Right and Left Joins are mirrors of each other, we only consider the Left Join and Inner 

Join. 

Joins are created by connecting two tables on one or more common keys. Often, these are 

Primary/Foreign Key connections, but do not have to be. An inner join only returns records 

where the common key exists in both tables while the left join returns all records in the left 

table whether or not a common key exists in the right table PLUS the records an Inner Join 

returns. 

SELECT ALL RECORDS FROM TABLE A AND TABLE B, WHERE THE JOIN CONDITION IS MET.  

SELECT ALL RECORDS FROM TABLE A, ALONG WITH RECORDS FROM TABLE B FOR WHICH THE JOIN CONDITION IS MET (IF AT ALL). 

    10 

SQL Server Management Studio 

Per Microsoft, SSMS is an “integrated environment for managing any SQL infrastructure, 

from SQL Server to SQL Database. SSMS provides tools to configure, monitor, and administer 

instances of SQL. Use SSMS to deploy, monitor, and upgrade the data‐tier components used 

by your applications, as well as build queries and scripts.” It is included in most SQL server 

installations, but if not included in yours, it can be downloaded as a separate component. It can also be 

installed on a local machine that has remote access to an instance of SQL server. SSMS is backward 

compatible to all currently supported versions of SQL Server. 

Getting Started with Query Designer in SSMS 

1) Start SSMS by going to your start menu and locating Microsoft SQL Server {version}.  

 

2) Expand Microsoft SQL Server {version} and locate SQL Server {version} Management 

Studio. 

 

3) Connect SSMS to the correct instance of a Database Engine: 

 

a. The first time you run SSMS, you have to supply a servername and instance. 

b. You can either type in the name (servername\instance name), or 

c. Use the pick arrow and select “<Browse for more…>” to have SSMS find available 

SQL servers on your system. 

i. Local Servers are installations on the computer to which you are 

connected. 

ii. Network Servers are installations on a computer accessible over the 

network to which your workstation is attached. 

 

    11 

  

 

  

   

    12 

Note, in this case, there are four instances of a Database Engine, while only two instances of 

Analysis and Reporting Services. The machine name on the computer hosting SQL Server is (in 

this example) VM02‐SQL. The instance we want is \SHELBY. This is the usual instance name for 

both v.5 and Arena/SNF installations, but yours could be different. Check with your IT 

department if you are not sure. 

Once logged in, the Object Explorer window should appear on the left side. If you do not see it, 

press F8 or click the View menu item and select “Object Explorer”. It shows, as the name 

implies, objects inside the Database Engine. 

   

    13 

4) Choose a database.  

a. Arena and ShelbyNext | Financials tables are stored in ArenaDB. 

b. V.5 uses ShelbyDB. 

 

Right‐click and select 

New Query or  

right‐click on  

ShelbyDB for v.5.

    14 

  

5) We are going to use the Query Designer for now, so select it as shown above. 

 

6) After a few moments the following screen should appear. Be patient. It may take a little 

while to populate the table structure. 

    15 

Of course, if you are connecting to ShelbyDB, you will see different tables than what 

shows here.

 

 

7) Do not despair. It takes a while to become familiar with which table contains 

information you need and even more time to be acquainted with the relationship key(s). 

Do not forget the documentation for v.5 referenced above. 

   

    16 

Let’s Build a Query  

While this information already exists in a view in both v.5 and Arena, we are going to build a 

query that returns Title, FirstName, LastName, Suffix, and Address block for Main/Home 

address (v.5 query) or Primary Address (Arena query). 

The following tables are needed for Arena: 

Table  Foreign Keys  Alias  Fields 

core_person  person_id, suffix_luid, 

title_luid, 

Organization_id 

CP  First_name, 

last_name 

core_lookup  Lookup_id, 

Organization_id 

T  Lookup_value 

core_lookup  Lookup_id, 

Organization_id 

S  Lookup_value 

core_address  Address_id, 

Organization_id 

CA  Street_address_1, 

street_address_2, 

city, state, 

postal_code 

core_person_address  Address_id, 

Person_id, 

Organization_id 

CPA  No fields from this 

table are in the select 

list. It is for Join only. 

Aliases are used to give a table, or a column in a table, a 

temporary name. 

An alias is necessary if you are going to use a single 

table more than once in a query as shown for 

core_lookup, above. 

An alias only exists for the duration of the query. 

   

What’s an alias

    17 

For Shelby v.5: 

Table  Foreign Keys  Alias  Fields 

NAnames  NameCounter, 

TitleCounter, 

SuffixCounter, 

MainAddress 

A  FirstMiddle, 

Lastname 

NATitles  Counter  T  Descr 

NASuffixes  Counter  S  Descr 

NAAddresses  AddressCounter  Ad  Adr1, Adr2, city, 

state, postalcode 

 

In this example aliases are being used to give a table a shorter name. The reason will become 

evident when we start to manually create queries. 

USING QUERY DESIGNER TO CREATE A QUERY FOR V.5 

1) Right‐click on ShelbyDB. 

2) Select New Query. 

3) Select Design in Query Editor. 

4) When the table list pops up, scroll to a table in the chart above and double‐click. 

5) Repeat for each table in the chart above. 

 

   

    18 

Your screen should look similar to this: 

 

 

 

 

CHECKING ASSUMED RELATIONSHIP CREATION 

SSMS uses Primary/Foreign Keys in tables and assumes a relationship by creating an inner join 

between [what appears to be] obvious key pairs. In the case above, both NATitles and 

NASuffixes have Counter as a primary key, but the database designer(s) never intended for 

these two keys to be used for joining these two tables. 

 

   

    19 

  

The correct key pairing is to the NANames table. Below is the NATitles page from the v.5 Table 

Layout document referenced earlier. It clearly shows the key pairing that is to be used. 

NANames.TitleCounter pairs with NATitles.Counter and NANames.SuffixCounter pairs with 

NASuffix.Counter. 

 

1) To create the correct pairing, place your cursor on the Counter field in NATitles and drag 

it to the TitleCounter in NANames table. Do the same for NASuffixes. 

For this class, we are not dealing with getting address blocks from other than 

Main/Home address; we are only extracting the Main/Home address block. To do that, 

we drag and drop the NAAddress.AddressCounter to the NANames.MainAddress field. 

2) Next, we change the join type to LEFT JOINS 

 

Since the assumed relationship is 

incorrect, with your mouse on 

the diamond, RIGHT‐click and 

choose Remove.

    20 

Not every name in NANames has a title, but we want 

all records from NANames regardless. A left join will 

return all records from NANames plus those records 

from NATitles where there is a match. An inner join 

would return only records where there is a match, 

eliminating most of the NANames records. 

The same is true for NASuffixes and NAAddresses. 

Where there is no match, a NULL is returned. 

 

3) After dragging and dropping to create the new relationship, put your cursor on the 

diamond as shown, RIGHT‐click, and Select All Rows from NANames.  

 

4) Do the same for the other two relationships. 

 

 

   

Why Left 

Joins 

RIGHT‐click and choose Select 

All Rows from NANames. 

    21 

CREATING TABLE ALIASES 

    

RIGHT‐CLICK to create table alias. 

    22 

SELECTING COLUMNS TO OUTPUT, CREATING COLUMN ALIASES, AND SORTING 

1) To tell the Query Builder which fields you want to see, check the boxes that are 

highlighted. Looking at the v.5 Table Layout on page 19, we see that titles (Mr., Mrs., 

etc.) are stored in the T.Descr field. Similarly, Suffixes (Jr., Sr., III, etc.) are stored in the 

S.Descr field. 

 

2) To distinguish between the two Descr fields, type a column alias as shown below. 

 

3) Last, we want to sort the output in alphabetical order – LastName+Suffix+FirstMiddle. 

 

4) Notice the query has been written for you. Click OK. 

    23 

      

UNDERSTANDING THE QUERY  Every SQL query has structure, vocabulary and syntax. The order of construction is important. A basic query looks like this: 

SELECT (one or more columns) FROM (a table) 

 Then, we add relationships:   SELECT( column(s) from Table A, column(s) from Table B)   FROM (Table A) {type of} JOIN Table B ON Table A’s key = Table B’s key  Then, we add limiting filters {optional}:   SELECT   FROM   WHERE a column = {some criteria}     {optional} AND another column = {some criteria)     You can also use OR.  

The WHERE clause must evaluate to Yes or No.    

    24 

A GROUP BY clause is only required if one or more columns in the select list are aggregated:   SELECT   FROM   WHERE   GROUP BY Columns in the SELECT list must be included here, except the aggregations.  Finally, to present the results in a meanigful order, the ORDER BY clause is used {optional): 

SELECT   FROM   WHERE   GROUP BY   ORDER BY (column 1, column 2, ect)   So, in our query, we have not limited the results nor do we have any columns being aggregated, so there is neither a Where clause nor a Group By clause.  SELECT T.Descr AS Title,  

A.FirstMiddle,  A.LastName,  S.Descr AS Suffix,  Ad.Adr1,  Ad.Adr2,  Ad.City,  Ad.State,  Ad.PostalCode 

FROM Shelby.NANames AS A LEFT JOIN     Shelby.NAAddresses AS Ad ON A.MainAddress = Ad.AddressCounter LEFT JOIN     Shelby.NASuffixes AS S ON A.SuffixCounter = S.Counter LEFT JOIN     Shelby.NATitles AS T ON A.TitleCounter = T.Counter ORDER BY A.LastName, Suffix, A.FirstMiddle 

       

    25 

Manual Coding  In this part of the class you are going to: 

1) Add a filter to display only personal records (no organizations) 2) Create a single name line 3) Eliminate the word NULL where it exists 4) Add a 3‐digit Zip code prefix 

     ADD A FILTER  

In ShelbyDB, organizations are identified by a ‐1 in the NANames.TitleCounter field. We do not want any records returned having a ‐1 in that field. 

 SELECT T.Descr AS Title,  

A.FirstMiddle,  A.LastName,  S.Descr AS Suffix,  Ad.Adr1,  Ad.Adr2,  Ad.City,  Ad.State,  Ad.PostalCode 

FROM Shelby.NANames AS A LEFT JOIN     Shelby.NAAddresses AS Ad ON A.MainAddress = Ad.AddressCounter LEFT JOIN     Shelby.NASuffixes AS S ON A.SuffixCounter = S.Counter LEFT JOIN     Shelby.NATitles AS T ON A.TitleCounter = T.Counter WHERE A.TitleCounter <> ‐1 ‐‐This is a comment. It is ignored at runtime. ‐‐Note the use of a table alias (A) instead of the actual table name ‐‐“<>” means “not equal to” ‐‐WHERE TitleCounter in the Aliased NANames table does not equal ‐1  ORDER BY A.LastName, Suffix, A.FirstMiddle     

    26 

CREATE A SINGLE NAME LINE  In SQL, a plus sign is used to “add” one string value to another; this is called concatenation. You want to add Title + space + FirstMiddle +space+ Lastname + space + Suffix.   

    

But wait, you have a problem!    

 Most of the names returned have NULL in the Title and/or Suffix column. A NULL is nothing – there is no data. How do you add something to nothing?  We must identify those fields where there is no data and put a value there. This is a very common issue when dealing with LEFT JOINS. This is done using the ISNULL() function: 

 ISNULL(T.Descr,’’) puts a blank value in the title column for all non‐existent titles. ISNULL(S.Descr,’’) puts a blank value in the suffix column for all non‐existent suffixes.   Here is the concatenation and results: ISNULL(T.Descr,’’) + ‘ ‘+A.FirstMiddle+’ ‘+A.Lastname+’ ‘ +ISNULL(S.Descr,’’) AS CombinedName  

 Note the highlighted records have a leading space! 

This is caused by the ISNULL() function replacing a NULL with a space.  

We can use an LTRIM() function to remove all spaces to the LEFT,  making all the names equally left‐justifed. 

   

    27 

SELECT T.Descr AS Title,  A.FirstMiddle,  A.LastName,  S.Descr AS Suffix,   ‐‐This is where the combined name concatenation starts LTRIM(ISNULL(T.Descr,'') +' '+ A.FirstMiddle +' '+ A.Lastname +' '+ ISNULL(S.Descr,'')) AS CombinedName, ‐‐This is where the combined name concatenation ends  Ad.Adr1,  Ad.Adr2,  Ad.City,  Ad.State,  Ad.PostalCode FROM Shelby.NANames AS A LEFT JOIN     Shelby.NAAddresses AS Ad ON A.MainAddress = Ad.AddressCounter LEFT JOIN     Shelby.NASuffixes AS S ON A.SuffixCounter = S.Counter LEFT JOIN     Shelby.NATitles AS T ON A.TitleCounter = T.Counter WHERE A.TitleCounter <> ‐1   Results: 

      

   

    28 

ELIMINATING NULLS ELSEWHERE  Since it is possible to enter a person’s name without an address, the LEFT JOIN will cause a NULL to be returned in such cases. Let’s clean that up with the ISNULL() function. Note the function strips the column name, so you have to add a column alias.  

        

    29 

ADD A 3‐DIGIT ZIP CODE PREFIX LINE  Since the NAAddresses.PostalCode field is a string value, you can use one of the string functions to extract the left‐most three “digits” the USPS uses as zip code prefixes. Those familiar with Excel formulas may recognize the LEFT() function:  LEFT( ISNULL(Ad.PostalCode,'')  ,3) AS Zip3  

    

    30 

For Arena Users 

Here is a way you can find the key combinations for joining tables together. 

1) In Object Explorer 

a. Expand the table you are investigating. 

b. Expand the Keys section. 

c. RIGHT‐click on an FK_ that looks promising.

 d. Click Modify. 

e. A more expansive list will appear. Pick one that seems aptly named. In this case 

you are looking for how core_person gets its suffix information. 

f. Expand the “Tables and Columns Specifications” section. 

    31 

g. The Foreign Keys are specified here. (Referential Integrity, anyone?) 

  

h. Note in this case, there are two keys involved in each table. When you create a 

join, you should use every Foreign Key in your statement. The organization_id 

keeps multi‐tenant data separate. Default organization_id is 1. 

 

2) Create a JOIN from this information: 

 

SELECT S.lookup_value AS Suffix FROM core_person CP LEFT JOIN    core_lookup S ON CP.suffix_luid = S.lookup_id AND        CP.organization_id = S.organization_id 

  

Here is the v.5 query as it would appear in ArenaDB.   SELECT ISNULL(T.lookup_value,'') AS Title, CP.first_name, CP.last_name,  ISNULL(S.lookup_value,'') AS Suffix,  LTRIM(ISNULL(T.lookup_value,'')+' '+ CP.first_name +' '+ CP.last_name +' '+ ISNULL(S.lookup_value,'')) AS combinedname, ISNULL(CA.street_address_1,'') AS Adr1,  ISNULL(CA.street_address_2,'') AS Adr2,  ISNULL(CA.city,'') AS city,  ISNULL(CA.state,'') AS state, ISNULL(CA.postal_code,'') AS postalcode, LEFT(ISNULL(CA.postal_code,''),3) AS Zip3 

    32 

‐‐Two instances of core_lookup have different aliases because one supplies Title, the other Suffix ‐‐To get address block, we need to join core_person to core_person_address and then to core_address FROM core_person CP LEFT JOIN    core_person_address CPA ON CPA.person_id = CP.person_id AND                 CPA.organization_id = CP.organization_id INNER JOIN   core_address CA ON CA.address_id = CPA.address_id AND ca.organization_id = cpa.organization_id LEFT JOIN    core_lookup S ON CP.suffix_luid = S.lookup_id AND            CP.organization_id = S.organization_id LEFT JOIN   core_lookup T ON CP.title_luid = T.lookup_id AND            CP.organization_id = T.organization_id  ‐‐core_address stores street addresses,etc. Each person can have more than one address ‐‐core_person address records the address_id and primary_address {logical} field. ‐‐primary_address = 1 in the Where clause filters to only the primary address  WHERE CP.business = 0 AND CPA.primary_address = 1  ORDER BY CP.last_name, Suffix, CP.first_name  

  

    

Addendum   There are a lot of tutorials on youtube. Check out this one for SQL beginners:  https://www.youtube.com/watch?v=2‐1XQHAgDsM  

 

 

 

 

Tim Wilson Shelby Software Developer tim.wilson@shelbyinc.com 

 Tim is a Software Developer at Shelby Systems and has over 21 years of software development 

experience with a focus on and a passion for database design and development. Tim has a love 

for Christ and was excited to join the Shelby team in 2014 and use his skills to develop software 

solutions for faith‐based organizations.