Learningcomputer.com

1
SQL Server 2008 Introduction to Transact SQL

• Learningcomputer.com

2
What is Transact SQL?

• It is pronounced as Transact SQL or Transact
  SEQUEL
• Transact SQL or TSQL is a powerful database
  language
• It provides programmatic functionality within the
  relational databases provided by Microsoft SQL
  SERVER and Sybase database products
• It is SQL-92 compliant as set by ANSI (American
  National Standards Institute)
• It can be broken down into two main areas
• DML (Data manipulation language) e.g. SELECT,
  INSERT ..
• DDL (Data definition language) e.g. CREATE TABLE,
  DROP DATABASE ..

3
Sample database AdventureWorks2008

• Adventure Works Cycles, the fictitious company on
  which the database is based on. It primarily
  sells bicycle and parts
• The downloads can be found here
  http//www.codeplex.com/MSFTDBProdSamples/Release/
  ProjectReleases.aspx?ReleaseId16040
• I had some issues running the download program
  and had to run this command from C\ regsvr32
  vbscript.dll
• After the download, I was able to run this script
  RestoreAdventureWorks2008.sql. This was found
  here C\Program Files\Microsoft SQL
  Server\100\Tools\Samples I had to replace value
  of _at_source_path variable to bolded text above

4
DML statements for today

• SELECT
• INSERT
• UPDATE
• DELETE

5
Mighty SELECT statement

• The SELECT statement forms the core of the SQL
  database language
• Most commonly used SQL command by far
• You use the SELECT statement to select or
  retrieve rows and columns from database tables
• It does not make any changes to the underlying
  data
• This statement can use a sub-select (sub query)
• The SELECT statement syntax contains five major
  clauses, generally constructed as follows

6
SELECT statement syntax

• SELECT ltfield listgt
• FROM lttable listgt
• WHERE ltrow selection specificationgt
• GROUP BY ltgrouping specificationgt
• HAVING ltgroup selection specificationgt
• ORDER BY ltsorting specificationgt
• Notice the bold code is required

7
SELECT Example

• We will be using the Adventureorks2008 sample
  database for SQL Server
• Launch SQL Server Management Studio and connect
  to your local SQL instance
• Expand the database tab and select
  Adventureorks2008
• Right click and select New Query
• Type the following
• SELECT
• FROM PERSON.PERSON
• WHERE LASTNAME'WOOD
• It should return 87 rows also known as records

8
A little more on SELECT

• Lets try SalesOrderHeader table with multiple
  condition
• SELECT FROM SALES.SALESORDERHEADER
• WHERE TERRITORYID1 AND ORDERDATE lt '2002-01-03'
• Quick way to copy data from one table to another
  is SELECT INTO. Why would you need this?
• SELECT INTO HUMANRESOURCES.EMPLOYEE2
• FROM HUMANRESOURCES.EMPLOYEE

9
SQL Operators

• Operators are used to limit the number of results
  from the query. Also they are used for
  mathematical and logical operations. Here are a
  few types of operators.
• Standard Operators          equal to ltgt not
  equal to lt less than lt         less than or
  equal to gt          greater than gt
          greater than or equal to between used
  to show between two numbers
• Logical Operators
• AND       used when both conditions are
  includedOR          used when either of the
  condition is trueNOT       opposite of the
  logical value

10
INSERT statement

• Inserts one or more new rows into the specified
  table
• When you use the VALUES clause, only a single row
  is inserted.
• Typically used to insert single row of data.
  However INSERT can use a sub-select (sub query)
  to insert multiple records.

11
INSERT Syntax

• INSERT INTO
• table-name (column-name,...)
• VALUES
• (literal,...) select-statement
• If you use a sub-select statement, the number of
  rows inserted equals the number of rows returned
  by the select statement Makes it easier to manage
  the permissions in your databases

12
INSERT Example

• We will be using the Person.Person table
• For BusinessEntityID, I had to insert a record
  into BusinessEntity for referential integrity,
  more on this later
• Right click and select New Query
• Type the following
• INSERT INTO PERSON.PERSON
• (BUSINESSENTITYID, PERSONTYPE, NAMESTYLE,FIRSTNAME
  , LASTNAME, EMAILPROMOTION, MODIFIEDDATE)
• VALUES
• (20778, 'EM', 0, 'KASH', 'MUGHAL', 0, GETDATE())
• This should insert one record into Person table

13
UPDATE statement

• This is used to update data in the tables
• Uses a WHERE clause to seek the rows that need to
  be updated
• Can use a sub-select (sub query) to update data
  from another underlying table
• In the absence of WHERE clause, all the records
  are updated
• Be VERY careful with this one!

14
UPDATE Syntax

• In its simplest form, here is the syntax
• UPDATE tablename
• SET column-name ltexpressiongt NULL,...
• WHERE ltsearch-conditiongt
• Notice the Where clause is optional (enclosed in
  ) however I would strongly recommend using it
  regularly
• If you use a sub-select statement, the number of
  rows inserted equals the number of rows returned
  by the select statement

15
UPDATE Example

• We will update the record that we just inserted
  into Person.Person table
• Right click and select new query
• Type the following
• UPDATE Person.Person
• SET FirstName'KASHEF'
• WHERE BusinessEntityID20778
• This will update one record only

16
And my favorite DELETE statement

• Used to delete data from tables
• Like the Update statement uses the WHERE clause
  to locate the records to delete.
• In case of No WHERE clause, deletes all the
  records in the table
• Be very careful with this one also!
• Remember there is no Undo or Control Z in SQL
  Server!!
• Funny but TRUE story

17
DELETE Syntax

• DELETE
• FROM table-name
• WHERE ltsearch-conditiongt
• Notice how you do not need in fact it will give
  you an error
• Where clause is optional (enclosed in ) however
  I would strongly recommend using it regularly
• Delete statement can use a sub-select (sub query)
• In the absence of where clause deletes all rows,
  enough said

18
DELETE Example

• Expand the database tab and select
  AdventureWorks2008
• We will delete one row from Person table, the one
  we inserted earlier
• Right click and select new query
• Type the following
• DELETE FROM PERSON.PERSON
• WHERE BUSINESSENTITYID20778

19
Review

• SELECT
• INSERT
• UPDATE
• DELETE