t-SQL Basic Programming for beginners

starting with very basic programming.

topics covered 

Starting t-sql program



-- 1. declare a variable & fetching the value

declare @name varchar(10)
set @name = 'name-xyz'
select '"select - "the variable value is - " ' +@name + '"'

--print '"print ststmnt -" the variable value is - " ' +@name + '"'
go

--2. use 2 GO in code as it will run in batches. any mistake in each section will result in error

use DBAMONITOR
go

declare @SPID int
select @spid = count(*) from dbo.BlockedEvents;
print 'the total no# of SPID in table [dbo.BlockedEvents]- as of '+ cast(getdate() as varchar(11))+ ' is - ' + cast (@spid as varchar(5))
go

-- 3. current user name/login name

select original_login()

or

select suser_sname()
go

SQL JOINS


/*
SQL JOINS EXPLAINATION WITH SIMPLE EXAMPLES
*/
/*
-- create a sample database
create database DBAMONITOR;
go
*/

--1.) inner join

use dbamonitor
go
create table table1
(
emp_id int,
emp_name varchar(10)
)

use dbamonitor
go
create table table2
(
emp_id int,
emp_address varchar(10)
)

insert into table1 (emp_id,emp_name) values (111,'Albert'),
(112,'Belgin'),
(113,'catherine'),
(114,'Debra'),
(115,'Elisaveta'),
(116,'Freida')

insert into table2 (emp_id,emp_address) values (112,'alabama'),
(116,'Burmuda'),
(117,'chicago'),
(118,'Danbury'),
(115,'Edinburg'),
(119,'florida')

use dbamonitor
go
select * from table1
select * from table2

selecting both the table will fetch below records.
SQL_JOINS_select_both_tables_Untitled
INNER JOIN syntax & example
i.e rows/data common in both tables will be displayed as shown in below screen-shot

use dbamonitor
go
select * from table1 A
INNER JOIN
table2 B
on
A.emp_id=B.emp_id

INNER_JOIN_Untitled

LEFT outer JOIN
–all rows in TABLE1 will be displayed along with the matching rows from table2

select * from table1 A
LEFT OUTER JOIN
table2 B
on A.emp_id=B.emp_id

LEFT_OUTER_JOIN_Untitled

RIGHT outer JOIN
All rows in TABLE2 will be displayed along with the matching rows from table1

select * from table1 A
RIGHT OUTER JOIN
table2 B
on A.emp_id=B.emp_id

RIGHT_OUTER_JOIN_Untitled


FULL OUTER JOIN

-- i.e irrespective of wheather there is any matching rows on either table, all the rows of both table will be displayed.

select * from table1 A
FULL OUTER JOIN
table2 B
on A.emp_id=B.emp_id
FULL_OUTER_JOIN_Untitled











-- CROSS JOINS (each single row in TABLE1 will join all rows in TABLE@)

select * from table1 A
 CROSS JOIN 
table2 B
CROSS_JOIN_Untitled
Advertisements
This entry was posted in t-sql learnings and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s