{"id":2620,"date":"2024-05-01T11:36:31","date_gmt":"2024-05-01T15:36:31","guid":{"rendered":"http:\/\/andrewpallant.ca\/wordpress\/?p=2620"},"modified":"2024-05-01T11:46:26","modified_gmt":"2024-05-01T15:46:26","slug":"sql-basics","status":"publish","type":"post","link":"https:\/\/andrewpallant.ca\/wordpress\/sql-basics\/","title":{"rendered":"SQL Basics"},"content":{"rendered":"\n

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<\/strong>.<\/p>\n\n\n\n\n\n\n\n

The SELECT<\/strong> 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 (*<\/strong>). You would protect the consumption of data and the memory used by limiting your results using the TOP<\/strong> clause. To filter your results, you will use the WHERE<\/strong> clause.<\/p>\n\n\n\n

SELECT TOP<\/span> 2 * FROM<\/span> [TABLE1] WHERE<\/span> [Name] LIKE<\/span> '%Andrew%'   \n\n-- 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.<\/span><\/code><\/pre>\n\n\n
SELECT TOP<\/span> 3 * FROM<\/span>  TABLE1 WHERE<\/span> [Name] =<\/span> 'Andrew'   \n\n-- This will look filter a result of rows that has a column [Name] that equals the name Andrew and limit it to three rows.<\/span><\/code><\/pre>\n\n\n\n\n
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<\/strong> statement.<\/p>\n\n\n\n
SELECT<\/span> * FROM<\/span> [TABLE1] WHERE<\/span> [CreatedBy] NOT IN<\/span> (SELECT<\/span> [CreatedBy] FROM<\/span> [TABLE2] WHERE<\/span> [Name] = 'Andrew') \n\n-- 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<\/code><\/pre>\n\n\n\n
Now for the second most commonly used SQL statement in the support team: INSERT<\/strong>.  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<\/strong> clauses from the above and have it output the data with the columns.<\/p>\n\n\n\n
The construction of the INSERT statement is as of such<\/p>\n
INSERT ( COLUMN(s) names INTO [TABLE1] VALUES ( value representing the columns in the first set of brackets))<\/p>\n\n
INSERT <\/span> ([FIRSTNAME],[LASTNAME], [AGE])  INTO<\/span> [TABLE1] VALUES<\/span>('Someone', 'SMITH', 42)-- This will insert a record with the first name Someone<\/i>, last name smith<\/i> and age 42<\/i><\/span><\/code><\/pre>\n\n
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<\/p>\n\n
\nDECLARE<\/span> @LinkForTableTwoID AS UniqueIdentifier -- This Assumes the Generated ID from Table1 will be a UniqueIdentifier<\/span>\n\nINSERT <\/span> ([FIRSTNAME], [LASTNAME], [AGE])  INTO<\/span> [TABLE1] VALUES<\/span>('Someone', 'SMITH', 42)\n\nSET @LinkForTableTwoID = SCOPE_IDENTITY() -- get the freshly generated identity for future use <\/span>\n\nINSERT <\/span> ([Address1], [Address2], [City], [AddressType], [Table1ID]) INTO<\/span> [TABLE2] VALUES<\/span>('22 ThatRoad RD', '', 'Toronto', 'Primary', @LinkForTableTwoID)\nINSERT <\/span> ([Address1], [Address2], [City], [AddressType],[Table1ID])  INTO<\/span> [TABLE2] VALUES<\/span>('1 SomeStreet Cres', 'Box 19712', 'Toronto', 'Mailing', @LinkForTableTwoID)\n-- 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.<\/span>\n<\/code><\/pre>\n\n\n\n\n\n
Recap Of Some Basics<\/strong><\/p>\n\n\n\n
Reviewing the current page, here are three questions and their answers in an FAQ format:<\/p>\n\n\n\n
Q1: What is the purpose of the SELECT statement in SQL?<\/strong><\/p>\n\n\n\n