This is the 2nd post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.
You can get that in Paperback (USA) and Kindle (Worldwide).
Running SQL Code
When we run SQL code, it is often a series of SQL statements created by someone else. Still we are often tasked with adding to the code to customize it for our system or testing the code and making suggestions. For this reason the SQL Admin must know basic SQL coding skills. This section will focus on the most common types of queries. If being great at all types of queries is important to you, then we recommend a deeper study with the Joes 2 Pros SQL Queries series starting with Volume 1 for beginners and work through to Volume 5.
Single Table Queries
When we want information from a database, we write a query, such as SELECT filename(s) FROM TableName
. In English we might say, “Show us the information!”
Let’s write a simple query. We will use the dbBasics database context and request all records and fields from the ShoppingList table as part of the result set. The keyword SELECT requests information to be displayed and the asterisk (*) sign is a shortcut for all the field names. The keyword FROM chooses a specific table.
We are able to run this query to retrieve the information we want by either clicking on the ! Execute button in the toolbar above the query window, or by pressing the F5 button on our keyboard (some keyboard configurations require pressing the fn (function) + F5 key simultaneously).
Basic Query Syntax
A group of students in one of my weekend classes helped to write a neatly summarized guide listing the meaning of words in a basic SQL query. Here is the code sample:
USE dbBasics --choose DATABASE Context
GO --complete USE statement
SELECT * --choose Field(s) to display
FROM ShoppingList --choose the Table
Now is a good time to point out that words can be placed in a query window that have nothing to do with the SQL code itself. We can write non-SQL words and notes like “I wrote this query today” above the SELECT statement. In order to do this we must tell SQL Server to ignore this text since it is not intended to be code. In fact, it should be non-executing code known as comments. To make comments, we need to begin the line with two hyphen signs, one after the other with no spaces between them. Change the database context back to JProCo with the drop-down window in the toolbar.
Use this example of code:
-- I wrote this query today
SELECT * FROM Location
The preceding code runs fine in JProCo. SQL Server ignores the first line because of the double hyphens. The comment is there only for the benefit of the human reading it. This is a useful way to make notes that later provide us, or our team with key hints or details explaining what the code is attempting to accomplish.
Table names can optionally have square brackets around them and it will not change the result set. Changing back to the dbBasics context, we can run the following two queries. Although one query uses square brackets and the other does not use brackets, they will both operate identically by returning a result set.
SELECT * FROM [ShoppingList]
SELECT * FROM ShoppingList
When coding, we rarely use square brackets because it requires extra typing. The only time it is helpful is when we can’t tell when a table name starts or stops. For this demo, the dbBasics database has two identical tables named ‘ShoppingList’ and ‘Shopping List’. The latter contains a space in its name, which is generally a bad naming practice for any database object.
For situations like this we must use square brackets as a delimiter. Without these delimiters, SQL Server will think the table is named ‘Shopping’ and there is a command named ‘List’, which it does not recognize and will result in an error message.
Of course, as shown in the two earlier queries, we can put square brackets around any table. In fact, code generated automatically by SQL Server always creates these delimiters for every object in the database. The only time we must use the square bracket delimiters is when table names are separated by a space, have the same name as a SQL Server keyword (bad idea), or are otherwise named in a way that is not obvious or easily interpreted by SQL Server.
We can easily correct this error message by placing square bracket delimiters around the Shopping List table name. Once this is complete, the query will run without an error and we will get the same seven records in our result set as the first query using the ShoppingList table (not the [Shopping List] table).
Action Item
Get the book for yourself and your friend. This is just a reference everyone must have it.
Available in Paperback (USA), Kindle (Worldwide)
Reference: Pinal Dave (https://blog.sqlauthority.com)
