SQL Basics

Today, I am writing this blog because I did not have a chance to conduct the SQL training with the support team yesterday before I departed for a new adventure; I feel like I owe it to them to give them something. This is part one of the training session. This part is about SQL basics. Part two is about using patterns to gain efficiencies in your day-to-day work. Let’s start with part one: SQL Basics.

The SELECT statement is probably the most used in the support team. It is used whenever looking up a record to verify the data or simply doing ad-hoc reports. The simplest statement is what I would call the wild card statement. You would use this when you don’t know the data table structure or if you want quick, natural results. The wild card is represented by an asterisk (*). You would protect the consumption of data and the memory used by limiting your results using the TOP clause. To filter your results, you will use the WHERE clause.

SELECT TOP 2 * FROM [TABLE1] WHERE [Name] LIKE '%Andrew%'   

-- This will look filter a result of rows that contain a column [Name] that contains the name Andrew and limit the results to two rows.

SELECT TOP 3 * FROM  TABLE1 WHERE [Name] = 'Andrew'   

-- This will look filter a result of rows that has a column [Name] that equals the name Andrew and limit it to three rows.

Let’s say we wanted to get the results where one table has results, and we want to show that a second table doesn’t contain. This was a recent customer request. We are going to use two queries. The inner query or the last query will be the positive in this scenario, and the outer query will be the negative. We are going to introduce the NOT IN statement.

SELECT * FROM [TABLE1] WHERE [CreatedBy] NOT IN (SELECT [CreatedBy] FROM [TABLE2] WHERE [Name] = 'Andrew') 

-- This query will grab all the rows with the name Andrew and give you all the rows in Table 1 that do not have rows created by Andrew

Now for the second most commonly used SQL statement in the support team: INSERT. I would recommend learning the table schema by either viewing it in Microsoft Azure Data Studio or SQL Management Studio. Find the database and expand it so you can see the Tables node in the tree view of the database. Find the table or tables and expand that to view the column names. You can also use the SELECT clauses from the above and have it output the data with the columns.

The construction of the INSERT statement is as of such

INSERT ( COLUMN(s) names INTO [TABLE1] VALUES ( value representing the columns in the first set of brackets))

INSERT  ([FIRSTNAME],[LASTNAME], [AGE])  INTO [TABLE1] VALUES('Someone', 'SMITH', 42)-- This will insert a record with the first name Someone, last name smith and age 42

Now assume we want to add a record to Table1 and it has an index column that we want to use in Table2 to establish a relationship. We are going to store the created identity from the first insert so that we can use it for subsequent inserts

DECLARE @LinkForTableTwoID AS UniqueIdentifier -- This Assumes the Generated ID from Table1 will be a UniqueIdentifier

INSERT  ([FIRSTNAME], [LASTNAME], [AGE])  INTO [TABLE1] VALUES('Someone', 'SMITH', 42)

SET @LinkForTableTwoID = SCOPE_IDENTITY() -- get the freshly generated identity for future use 

INSERT  ([Address1], [Address2], [City], [AddressType], [Table1ID]) INTO [TABLE2] VALUES('22 ThatRoad RD', '', 'Toronto', 'Primary', @LinkForTableTwoID)
INSERT  ([Address1], [Address2], [City], [AddressType],[Table1ID])  INTO [TABLE2] VALUES('1 SomeStreet Cres', 'Box 19712', 'Toronto', 'Mailing', @LinkForTableTwoID)
-- This will create a record with the name of a person and their age.   Then we will add two record2 to Table 2 linked to the first table by using a scope identity.

Recap Of Some Basics

Reviewing the current page, here are three questions and their answers in an FAQ format:

Q1: What is the purpose of the SELECT statement in SQL?

• A1: The SELECT statement is used to look up records, verify data, or generate ad-hoc reports. It’s the most commonly used statement by the support team.

Q2: How can you limit the results of a SQL query?

• A2: To limit the results of a query, you can use the TOP clause. For example, SELECT TOP 2 * FROM [TABLE1] it will limit the results to two rows.

Q3: What is the process for inserting a record into an SQL table and linking it to another table?

• A3: To insert a record and link it to another table, you first insert the record into the first table and store the generated identity using SCOPE_IDENTITY(). Then, use this identity to insert related records into the second table.

Since this meeting was only going to be an hour long and consisted of two parts, I now pause for Part Two: Looking for Patterns for Work Efficiencies. As always, with any of my training, my door is still open and happy to take questions 1:1 either in the comments of my LinkedIn post or directly from those who know how to get a hold of me.

Best of Luck!