Introduction to Transact-SQL- Chapter 4-A

What is Transact-SQL?
Transact-SQL ( or T-SQL)  provides most of the capabilities of ANSI-SQL 89 
and 92 as well as several extensions. 

Structured Query Language
SQL provides a language for accessing data objects, using substantially less
programming code than required by a third-programming language.

For example,

open file
while not (eof)
begin
	lock record
	read record
	if column-value = value
	write record
	unlock record
end
close file

Using SQL

update table-name
set column-name = new-value

This greater level of abstraction means better reliability, easier 
maintenance, and more readable, more meaningful code.

SQL provides some addtitional benefits:

. No need for explicit locking statements-the server manages all locking.
. No reference to the physical location of the data- the server translates
  a logical name into a linked set of physical locations.
. No specifying of indexing or search strategy.


T-SQL and ANSI-SQL

T-SQL extends SQL by adding program flow-control constructs 
(if and while, for example), local variables, and other capabilities
 that enable you to write more complex queries and to build code-based
objects that reside on the server, including stored procedures and triggers.

Who Uses SQL and T-SQL?

If you want to interact with SQL Server data, you need to submit a query 
in SQL. If you want to write a program to store as a SQL Server stored 
procedure, you need to write in T-SQL.

Statements, Batches, and Elements of T-SQL

Server Processing Steps

When you submit a SQL batch to the server, the batch is parsed as a 
whole, optimized and compiled as a whole, and then executed statement 
by statement.

How Do I Test SQL Queries?

You need a SQL editor that enables you to write SQL statements and submit
them to the server. 

. ISQL/W
. Use the Query Tool provided as part of SQL Enterprise Manager (SQL-EM).
. Use the MS-DOS-based utility ISQL

Logging in to a Server

You need a connection to a server to test T-SQL queries.
First, find out the three pieces of information you need to log in:

. The name of the server
. Your login name
. Your password

A Quick Tour of the ISQL/w Window

Using a Database

pubs or bigpubs

Retrieving Data with SELECT

Tables, Rows, and Columns

 What's in the pubs Database?

Selecting Data from a table

select column-list
from table-name

select au_lname, au_fname
from authors

SQL Result Sets

Column-Based Expressions

select title_id, type, price, price * 0.1
from titles

select au_lname +", "+au_fname
from authors

select title_id, type, price "original price", price * .1 discount
from titles

title_id type      original price   discount


select "Full Author Name" = au_lname +", "+au_fname
from authors

Using distinct

select type 
from titles

select distinct type
from titles

select distinct city, state 
from authors

* Don't use distinct if you don't need it. (Extra sorting)

 select *

select *
from publishers

Filtering Rows with where

select column-list
from table-name
where condition

select au_lname, au_fname
from authors
where state = "CA"

Equality and Inequality Operators

expression operator expression

=, <, >, >=, !=, !>, !<

select type title_id, price
from titles
where price * ytd_sales < advance

 Inequalities and Character Data

type < "abc"

Logical OR and AND

select au_id, city, state
from authors
where state ="CA" or city = "Salt Lake City"

BETWEEN and Ranges of Data

expression between expression and expression

select title_id, price
from titles
where price between $5 and $10

select title_id, price
from titles
where price >= $5 and price <= $10

select title_id, price
from titles
where price not between $5 and  <= $10


select title_id, price
from titles
where price < $5 or price > $10

Lists of Possible Values with in (...)
select title_id, price
from titles
where type = "mod_cook"
      or type = "trad_cook"
      or type = "Business"


select title_id, price
from titles
where type in ("mod_cook", "trad_cook", "Business")

select title_id, price
from titles
where type not in ("mod_cook", "trad_cook", "Business")

select title_id, price
from titles
where     type <> "mod_cook"
      and type <> "trad_cook"
      and type <> "Business"

Wildcards with LIKE

Wildcard              Meaning
%                     Any number (0 or more) of any character(s)
_ (underscore)        any single character
[ ] 		      Any single character listed in the brackers
                      (not ANSI SQL)

select au_lname, city
from authors
where city like "spring%"

select type, title_id
from titles
where title_id like "B_1342"

select type, title_id
from titles
where title_id like "B[UAN]1342"

Ordering Result Sets with order by

select au_lname, au_fname
from authors 
order by au_lname

select au_lname, au_fname
from authors 
order by au_lname, au_fname

 Ascending and Descending Ordering

select title_id, price, ytd_sales
from titles
order by price*ytd_sales desc

Ordering by Columns Not Appearing in the SELECT List

Retrieving Aggregate Data

sum()
avg()
min()
max()     Numbers, strings, date
count()   Returns the number of non-null expressions
count(*)  Returns the number of rows found


select avg(price)
from titles

select avg (price) "avg"
from titles
where type = "Business"

Counting Rows with count(*)

select count (*)
from authors
where state = "CA"

Aggregates and Null Values

c2
---
100
150
200
null

select sum (c2) "sum", count(c2), "count", avg(c2) "avg"
       min (c2) "min", max (c2), "max"  , count(*) "count(*)"
from test_table

sum   count  avg   min   max  count(*)

450     3     150   100  200    4

Sub-Aggregates with group by

select type, avg(price) "avg", sum(price) "sum"
from titles
where type in ("business","mod_cook")
group by type

type           avg            sum
------------------------------------------
business       13.73          54.92
mod_cook       11.49          22.98


select type, pub_id, avg(price) "avg", sum(price) "sum"
from titles
where type in ("business","mod_cook")
group by type, pub_id

type      pub_id        avg            sum
------------------------------------------
business   0736         2.99           2.99
business   1389        17.31          51.93
mod_cook   0877        11.49          22.98

Filtering  Results with having

select type, avg (price)
from titles
where price > $10
group by type

select type, avg (price)
from titles
where price > $10
group by type
having avg(price) > $20

select au_id, count(*)
from authors
group by au_id
having count (*) > 1

* Duplicate keys

Last update January 12, 1998.