Transcript SQL Server Configuration Manager

SQL Server 2008 – Introduction to
Transact SQL
Learningcomputer.com
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 ..
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/Proj
ectReleases.aspx?ReleaseId=16040
 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
@source_path variable to bolded text above
DML statements for today
 SELECT
 INSERT
 UPDATE
 DELETE
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
SELECT statement syntax
SELECT <field list>
FROM <table list>
[WHERE <row selection specification>]
[GROUP BY <grouping specification>]
[HAVING <group selection specification>]
[ORDER BY <sorting specification>];
* Notice the bold code is required
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
A little more on SELECT
 Lets try SalesOrderHeader table with multiple condition
SELECT * FROM SALES.SALESORDERHEADER
WHERE TERRITORYID=1 AND ORDERDATE < '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
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
<>
not equal to
<
less than
<=
less than or equal to
>
greater than
>=
greater than or equal to
between used to show between two numbers
 Logical Operators
AND
used when both conditions are included
OR
used when either of the condition is true
NOT
opposite of the logical value
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.
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
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
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!
UPDATE Syntax
 In its simplest form, here is the syntax
UPDATE tablename
SET {column-name = {<expression> | NULL}},...
[WHERE <search-condition>]
 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
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 BusinessEntityID=20778
 This will update one record only
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
DELETE Syntax
 DELETE




FROM {table-name }
[WHERE <search-condition>];
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
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 BUSINESSENTITYID=20778
Review
 SELECT
 INSERT
 UPDATE
 DELETE