.NET Code Catto Blogger Blog: T-SQL Database Development IDE & Queries (1 of 4)

Hey Now Everybody,

This is the first T-SQL post of four to provide a stellar understanding of database development using T-SQL. We'll break it up into two major sections the tool itself & querying data.

1. IDE SSMS SQL Server Management Studio
     a. Installation
     b. Connoting to Server
     c. Object Explorer
     d. Query Editor Window
     e Design View
2. Queries (Common Commands)
     a. Syntax
     b. Comments
     c. Data Types
     d. Create
     e. Select
     f. Insert
     g. Where
     h. Update
     i. Delete
     j. AND
     k. OR
     l. Order By
     m. Group By
     n. Count
     o. Joins
-->> T-SQL
1. IDE

a. Installation - SSMS in my opinion is the toughest windows application to install. This posts prereq is Management Studio is installed

b. Connecting to SQL Server- After opening the app the first thing commonly done is to connect to a SQL Server. Under the File menu there is an option 'Connect to Object Explorer' (Alt+F, Enter). Then the user is prompted enter the Server Name & authentication credentials.

c. Object Explorer - This is a powerful file folder explorer in a tree structure (expand collapse nodes). We can view it by pressing F8 or on the view menu. This is where we will commonly view the servers, databases, objects, tables, stored procedures. The parent node is the SQL Server once expanded we'll spend most of our time in the Databases node. When we expand the databases node we'll spend most time in the Tables node & Programmability --> Stored Procedures node.
SQL Server
     Databases
          Tables!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
          Programmability
               Stored Procedures!!!!!!!!!!!!!!!!!

d. Query Editor Window - The query editor window is extremely powerful. We can open on by highlighting/selecting the database then pressing Ctrl+N (or File Menu -> New --> Query)

e. Design View - This is a view when querying data is useful for people who enjoy a more graphical view when developing queries. We can open this view by selecting/highlighting a table then right clicking -> select Edit top 200 rows. Then press Ctrl + 1, Ctrl + 2, Ctrl + 3. We can now see 4 panes Diagram, Criteria, SQL & Results.

       2. Queries - Common Commands

a. Syntax Square Brackets [] - Important in T-SQL when referencing objects

b. Comments - T-SQL Comments can be done in two ways.
      a.1 /* Multiline comment any text between a slash & asterisk then asterisk & slash will be commented */
     a.2 -- Single Line comment any text on a line after two dashes will be comments
-- When on a line or lines of text is selected Ctrl+K, Ctrl+C will comment the line & Ctrl+K, Ctrl+U will uncomment a line or lines

c. Data Types - There are many data types & more get added in each release. Let's take a look at some common data types for variables in T-SQL
    int Integer
    char character
    datetime Date Time

d. Create - Create is a good command to create a table or stored procedure. In the following example we see the Create command to create a table:
-- Following Script will create a new table called newTablePeopleName w/ 2 columns an ID & Name
CREATE TABLE [dbo].[newTablePeopleName]
(
[newTableid] [int] NOT NULL
, [nameOfPerson] [char](50) NULL
)

e. Select - Select is an extremely powerful & commonly used command Below are two simple select statements from the table we created:
-- Select all columns from table newTablePeopleName
Select *
FROM newTablePeopleName
-- Select NameOfPerson columns from table newTablePeopleName
Select nameOfPerson
FROM newTablePeopleName

f. Insert - Insert is a useful command to insert data into a table. In the following example we display adding a row to our table:
-- Insert a row into newTablePeopleName
INSERT INTO [dbo]. [newTablePeopleName]
( newTableid
, nameOfPerson
)
VALUES
(
1
, 'Catto'
)

g. Where - The Where clause is a powerful command used to refine & select more specific data in a query. In the following example we use the where clause to select only names that match 'Catto':
-- Where example, we select only records that have name 'Catto'
Select *
From [newTablePeopleName]
WHERE nameOfPerson = 'Catto'

h. Update - Update is a useful command to update a record in a table. The example here displays to update the name of our record where id is 1:
-- Update record in table from name Catto to Catto the Tomato
UPDATE [newTablePeopleName]
SET nameOfPerson = 'Catto the Tomato'
WHERE (newTableid = 1)

i. Delete - Delete is a useful command to delete data such as a row or rows from a table In the following example a row is deleted from our table
-- Delete rows from table where the name is Catto
DELETE FROM [dbo.]newTablePeopleName]
WHERE (nameOfPerson = 'Catto')

j. AND - AND is a command great to use when querying data & want to select narrow search results. The following example selects records only with the id = 1 and name = Catto
-- AND is used to select only rows with specific criteria
SELECT newTableid
           , nameOfPerson
FROM [newTablePeopleName]
WHERE (newTableid = 1)
         AND (nameOfPerson = 'Catto')

k. OR - Or is similar to and which is used to narrow search results. The following select statement could select more records since it will select a record if the id = 1 OR name = Catto
-- OR is used to select records with newTableid = 1 OR name is Catto
SELECT newTableid
           , nameOfPerson
FROM [newTablePeopleName]
WHERE (newTableid = 1)
OR (nameOfPerson = 'Catto')

l. Order by - Order by is a useful command & will order results. The following select statement will return the results & order them by id:
-- Order by is used to order the results set
SELECT newTableid
, nameOfPerson
FROM [newTablePeopleName]
ORDER BY newTableid
-- DESCending Order is possible too
SELECT newTableid
, nameOfPerson
FROM [newTablePeopleName]
ORDER BY newTableid DESC

m. Group by - Group by is useful to refine a query to narrow the result set and group them by a field's value.
-- Select & Group the results set
SELECT newTableid
           , nameOfPerson
FROM [dbo].[newTablePeopleName]
GROUP BY nameOfPerson
, newTableid

n. Count - Count is a good command to count all the rows of a table or combined with the group to count groups of data. The following select statement would return the # of each name
SELECT COUNT(newTableid) AS TotalCountOfName
                     , nameOfPerson
FROM [newTablePeopleName]
GROUP BY nameOfPerson

o. Joins - Joins are a key command to understand when selecting data from multiple tables, there are inner & outer joins. In the following example we select data from 2 tables:
SELECT newTablePeopleName.nameOfPerson
           , oldTablePeeps.nameOfPerson
FROM [newTablePeopleName]
INNER JOIN [oldTablePeeps]
ON newTablePeopleName.newTableid = oldTablePeeps.newTableid