BLOG: TSQL

All the following examples are in database “Pubs”. You can use “Use database;” or “Select db_name();” to determine whether you are in the database.

• select au_fname+’ ‘ + au_lname, city + ’,’ + state + ’ ‘+zip from authors
• select title_id, price from titles where price between 10 and 20
• select au_lname, state from authors where state in ('CA','KS','MI','UT')
• select au_fname, au_lname from authors where au_fname like 'M%'
• select au_fname, au_lname from authors where au_fname like 'M_'
• select notes from titles where notes like '%@%%' escape '@'
• select au_lname, au_fname from authors where au_lname like '[LMS]%'
• select au_lname, au_fname from authors where au_lname like '[A-Z][a-z][a-z][a-z]'
• select title_id, titles from titles where title like '%[Ww][Ii][Tt][Hh][Oo][Uu][Tt]%'
• select * from authors where au_fname like '[^ ] [^ ] [^ ] [^ ]'
• select * from authors where state <> 'CA' might be written in select * from authors where state != 'CA'
• select au_fname,au_lname from authors order by au_lname,au_fname
• select distinct type from titles group by type order by 1 desc,
• Aliases can reside in “select” or “from”.
• select * from titles where royalty = null
• select pub_id,sum(advance) from titles group by pub_id having sum(advance) > 10000
• select title_id,type,price from titles where type like '%cook%' compute avg(price)
• select title_id, type, price from titles where type like '%cook%' order by type compute avg(price) by type
• select title_id,type,price from titles where type in ('business','mod_cook') order by type compute sum(price) by type compute sum(price)

• select * from authors where state = 'CA' union select * from authors where state = 'MA'
• select sum(qty) from stores,sales where stores.stor_id = sales.stor_id and state = 'CA'
• select stores.stor_id,stor_name, ord_num, qty from stores,sales where stores.stor_id *= sales.stor_id
• Don’t try to use full outer join. Your DBA will be very angry about the full outer join because it might produce A x B rows of output.

•
• Sub Query (Non-correlated or Correlated). Most of subqueries you can write them as join.

select [distinct] select_list

from table_list

where {expression {[not] in comparison [anyall]}[not] exists}

(select [distinct] subquery_select_list from table_list where conditions)

[group by group_by_list]

[having conditions]

[order by order_by_list]

· Temp table: session temp tables preceeded by a # ex: #authors. Global temp tables ##authors which must be dropped explicitly. (SELECT INTO). You can also create an empty table by using “SELECT INTO” where 1=2.

· Views are nothing more than a name for a select statement that is stored in the database.
· create table people (SSN char(11) not null constraint chk_ssn check (SSN like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]', low int, high, constraint chk_low_high check (LowQty <= HighQty))

· constraint constraint_name] references ref_table [ref_column] / [constraint constraint_name] foreign key (column [{,column}…]) references ref_table [(column [{, column}…])]

· select convert(varchar(8),pubdate,2) from titles where title_id = 'MC3026' (2 yy.mm.dd 3 dd/mm/yy 4 dd.mm.yy 5 dd-mm-yy 102 yyyy.mm.dd 103 dd/mm/yyyy 104 dd.mm.yyyy 105 dd-mm-yyyy).

· Local variables: declare @myvar int select @myvar = 42. Global Variables @@error @@identity @@rowcount @@version @@max_connections @@servername

· The ANSI standard defines four level of isolation for transactions @@isolation
· Level 0 allows dirty reads (You can see data that has been changed, but not necessarily committed, by another user (DEFAULT)

· Level 1 prevents dirty reads
· Level 2 prevents non-repeatable reads
· Level 3 prevents phantom reads
· begin tran; rollback tran; commit tran
· rollback {transaction tran work} savepoint_name @@transtate
· if @@error != 0
· Use the holdlock/noholdlock to override an isolation level setting
· Cursor Example

declare mycursor cursor for select title from titles for read only

declare @title_name varchar(80)

open mycursor

while @@fetch_status = 0

begin

fetch mycursor into @title_name

end

close mycursor

deallocate mycursor

· Stored Procedure Example

create procedure proc_name

(@parameter datatype = default

[,@parameter datatype output]

[,@parameter datatype…])

as

SQL batch

return

· Trigger Example (SPECIAL TABLE: inserted, deleted)

create trigger trigger_name on table_name

for {insert update delete}

[,{insert update delete}]…

as

SQL statements

· A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
o By using the NEWID function.
o By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

o Comparison operators can be used with uniqueidentifier values. However, ordering is not implemented by comparing the bit patterns of the two values. The only operations that can be performed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.

Tony