Using SQL SELECT statement with Transact SQL or TSQL

SELECT sql statement returns the data from SQL tables. Does not make any changes to the underlying data. It is the most commonly used SQL statement. This statement can use a sub-select (sub query). Here is the syntax

SELECT fields FROM tables WHERE condition ORDERBY fields

Examples from the Northwind database
If you would like to download a copy of this Microsoft sample database here is the link:

Download Northwind sample database

Returning all data from a table

• SELECT * FROM CUSTOMERS returns all the customer records

Using a SQL WHERE clause

• SELECT * FROM CUSTOMERS WHERE COUNTRY='USA' return customers in USA

Using SQL ORDER BY clause

• SELECT * FROM CUSTOMERS ORDER BY COMPANYNAME sorts by Company Name in Ascending Order

Using a DISTINCT clause to eliminate duplicates

• SELECT DISTINCT(COUNTRY) FROM CUSTOMERS will return all the unique values for Country field

Using OR in WHERE clause

• SELECT * FROM CUSTOMERS WHERE COUNTRY='USA' OR COUNTRY='UK' returns customers in USA or UK

Using the SQL IN clause (similar to OR clause)

• SELECT * FROM CUSTOMERS WHERE COUNTRY IN ('USA','UK') same as above

Using LIKE clause to look for patterns

• SELECT * FROM CUSTOMERS WHERE CONTACTNAME LIKE 'JO%' returns Customers with name beginning with the pattern ‘JO___’ e.g. JOHN would be returned

Using my fav IS NULL clause

• SELECT * FROM CUSTOMERS WHERE REGION IS NULL will give all the customer where the Region is missing or not known

Searching for numeric value

• SELECT CUSTOMERID FROM ORDERS WHERE EMPLOYEEID=5 returns the Customer ID from the Orders table where EmployeeID is 5

Working with Dates in Transact SQL

• SELECT * FROM ORDERS WHERE ORDERDATE=’ 1996-07-04’ checks for the all the orders for 7/4/1996

Using SQL COUNT clause

• SELECT COUNT(*) FROM CUSTOMERS returns the number of records in Customer table. will return number

Using SQL MIN function

• SELECT MIN(UNITPRICE) FROM PRODUCTS; will return the lowest priced product (a number)

Using SQL MAX function

• SELECT MAX(UNITPRICE) FROM PRODUCTS; will return the highest priced product (a number)

Using SQL AVG clause to compute Average

• SELECT AVG(UNITPRICE) FROM PRODUCTS; will return the average Unit Price (a number)

Using SQL SUM function

• SELECT SUM(Quantity) FROM [ORDER DETAILS] WHERE ORDERID=10258 will sum all the items for this order

Using GROUP BY HAVING clause, mainly used for grouping data

• SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM [ORDER DETAILS] GROUP BY ProductID HAVING SUM(Quantity) >1000

will return two things: one the product id and second the sum of quantity gouped by Product ID where number of products ordered is greater than 1000

How do I change owner for SQL Server jobs to SA account

--How do I change owner for SQL Server jobs to SA account

/*

At work, I am in the process of migrating a sql server machine with all the objects to a new SQL server.
As it turns out we have more than 200 jobs that are created by different DBAs and developers!! One of my task
list is to change all the job owners to SA account. How am I going to do that? I’m going to use the stored
procedure sp_update_job to do this. Also I’m going to use a combination of temporary table and while loop,
so let’s go ahead and take a look of this next.

Syntax from MSDN

sp_update_job [ @job_id =] job_id | [@job_name =] 'job_name'
[, [@new_name =] 'new_name' ]
[, [@enabled =] enabled ]
[, [@description =] 'description' ]
[, [@start_step_id =] step_id ]
[, [@category_name =] 'category' ]
[, [@owner_login_name =] 'login' ]
[, [@notify_level_eventlog =] eventlog_level ]
[, [@notify_level_email =] email_level ]
[, [@notify_level_netsend =] netsend_level ]
[, [@notify_level_page =] page_level ]
[, [@notify_email_operator_name =] 'email_name' ]
[, [@notify_netsend_operator_name =] 'netsend_operator' ]
[, [@notify_page_operator_name =] 'page_operator' ]
[, [@delete_level =] delete_level ]
[, [@automatic_post =] automatic_post ]


*/


use master
go

set nocount on


--Declaration of variables


declare

@jobid uniqueidentifier,
@rec_count int,
@loop_counter int


--create temp table to hold the information on job id. We will pull this information from sysjobs table in
--the msdb database

create table
#t_sqljobs
(

id int identity(1,1),
job_id uniqueidentifier,
owner_sid varbinary(100)
)

--insert the data into a temporary table from sysjobs table

insert #t_sqljobs(job_id, owner_sid)
select job_id, owner_sid
from msdb..sysjobs

--Set up the loop counters so we can loop through all the jobs in the temporary table

set @loop_counter=1
select @rec_count=COUNT(*) from #t_sqljobs


--Using a while loop to go through all the records. We first get the job id and then using sp_update_job procedure,
--will change the job owner to SA


while @loop_counter<=@rec_count

begin

select @jobid=job_id from #t_sqljobs where id=@loop_counter
--print 'job id is ' + cast (@jobid as varchar(100))
exec msdb.dbo.sp_update_job @job_id = @jobid, @owner_login_name ='sa'

set @loop_counter=@loop_counter+1

end

--Cleanup and drop the temporary table


drop table #t_sqljobs

Ok so I was on vacation!!

Not really, just bz with work, school (getting a MIS degree), family, ..............
I am back and will try to add stuff here regularly.

Kash