Learningcomputer.com - PowerPoint PPT Presentation

1 / 19
About This Presentation
Title:

Learningcomputer.com

Description:

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 ... – PowerPoint PPT presentation

Number of Views:51
Avg rating:3.0/5.0
Slides: 20
Provided by: learningsq
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com