Programming fun in database (SQL SERVER 2008)

when i was thinking about the programming logic ,

i suddenly remember my old graduation days where i generally used to run the program in C/CPP the programs like printing of * in piramid or in reverse of it.

as now i am SQL Developer i tried to implement the same in SQL SERVER 2008.

i tried the above code, if you find other approch or find some any good solution please replly here.

Please run the following or attached code in text mode of SQL SERVER MANAGEMENT STUDIO 2008.

      –/*  code is generated on the basis of geralization method

      —    you can check by taking as much as column you want and check like

      —    say suppose i am taking 9 columns where i am inserting *   

—    it is printing Pyramid like

      —    but while running following code select Result to text

      —    * * * * * * * * *

      —      * * * * * * *

      —        * * * * *

      —          * * *  

      —           * *

      —            *

      –*/

     

      IF(EXISTS(SELECTOBJECT_ID(‘Astrick’)))

            DROPTABLE Astrick

 

      CREATETABLE Astrick

      (

            id INTIDENTITY(1,1)

            ,C1 VARCHAR(10)

            ,C2 VARCHAR(10)

            ,C3 VARCHAR(10)

            ,C4 VARCHAR(10)

            ,C5 VARCHAR(10)

            ,C6 VARCHAR(10)

            ,C7 VARCHAR(10)

            ,C8 VARCHAR(10)

            ,C9 VARCHAR(10)

      )

      DECLARE @i INT,@i1 INT,@HOLD INT,@i2 INT,@HOLD2 INT,@loop INT

      DECLARE @INSERT VARCHAR(MAX)   — column name of insert query

      DECLARE @Insert_Query VARCHAR(MAX)— Insert query

      DECLARE @VALUE VARCHAR(MAX)    — value part of insert query

      SET @I1=1

     

      SELECT @I2=COUNT(*)FROMsys.columns S WITH(NOLOCK)

      JOINsys.all_objects O WITH(NOLOCK)ON S.object_id=O.object_id

      WHERE O.name LIKE’Astrick’and collation_name isnotnull

      SET @loop = @i2

      SET @i = @i2

 

      SET @INSERT=”

      SET @VALUE=”

      WHILE(@i >0)

      BEGIN

            SET @HOLD = @I1 

            SET @HOLD2 = @I2

            WHILE(@I1 <=@I2)

            BEGIN

                 

                  IF(@I1=@I2)

                  BEGIN

                        SET @INSERT = @INSERT +’C’+CONVERT(VARCHAR(10),@I1)       

                        SET @VALUE=@VALUE +”’     *”’

                  END

                  ELSE

                  BEGIN

                        SET @INSERT=@INSERT +C’+CONVERT(VARCHAR(10),@I1)+’,’

                        SET @VALUE =@VALUE +”’     *”,’

                  END

                  SET @I1 = @I1 +1

            END

           

            SET @Insert_Query =

‘INSERT INTO Astrick(‘+@INSERT+’) VALUES(‘+@VALUE+’)’

           

            EXECUTE(@Insert_Query)

            SET @i = @i -2

            SET @I1 = @HOLD + 1

            SET @I2 = @HOLD2 -1

            SET @INSERT=” 

— resetting string to blank to use it agin in the same loop

            SET @VALUE=”

      END

      — code to print Asteric table

      SETNOCOUNTON

     

      DECLARE @Asterik_Code VARCHAR(MAX),@Select_Code VARCHAR(MAX)

      DECLARE @c INT

     

      SET @c=1

      SET @Asterik_Code =”

      WHILE(@c<=@loop)

      BEGIN

           

            IF(@c=@loop)

            BEGIN

           

            SET @Asterik_Code=@Asterik_Code+’isnull(‘+ (

SELECT a.name FROM (                                             

                  SELECT s.name,ROW_NUMBER()OVER(ORDERBY s.name) id

FROMsys.columns S WITH(NOLOCK)

                  JOINsys.all_objects O WITH(NOLOCK)

ON S.object_id=O.object_id

                  WHERE O.name LIKE’Astrick’and collation_name isnotnull

) a WHERE a.id=@c)+’,””)’

                                                     

            END

            ELSE

            BEGIN

            SET @Asterik_Code=@Asterik_Code+’isnull(‘+ (

SELECT a.name FROM (                                                          SELECT s.name,ROW_NUMBER()OVER(ORDERBY s.name) id

FROMsys.columns S WITH(NOLOCK)

                  JOINsys.all_objects O WITH(NOLOCK)

ON S.object_id=O.object_id

WHERE O.name LIKE’Astrick’and collation_name isnotnull) a WHERE a.id=@c)+’,””),’                                  

            END  

            SET @c = @c + 1

      END

      SET @Select_Code =’SELECT ‘+@Asterik_Code +’ FROM Astrick’

      EXECUTE(@Select_Code)

 

70-433 MCTS Database Development Certification Topics

Most of the us don’t know about the certification topics for 70-433 or on the basis of 70-433 book of microsoft is not only sufficient to study and clear certification. To upgrade with the knowledge and the technology we need to do a study in deep.
so following are the 7 topics which focus on the certificaion of MCTS for database developer(70-433).

70-433 Exam Requirements

The MCTS Exam 70-433 focuses on 7

main areas:

• Implementing tables and views
• Implementing programming objects
• Working with query fundamentals
• Applying additional query techniques
• Working with additional SQL Server components
• Working with XML data
• Gathering performance information

Let’s take a closer look at these 7 areas.

1. Implementing Tables and Views (14%)

Implementing tables and views takes up 14% of the total exam questions and assumes that you are familiar with:

• creating and altering tables including computed and persisted columns, schemas, scripts to deploy changes to multiple environments and more
• creating and altering views such as WITH ENCRYPTION, WITH SCHEMABINDING, WITH CHECK OPTION and others
• creating and altering indexes including filtered, unique, clustered, non-clustered, FILL FACTOR, CREATE STATISTICS, indexing views and more
• creating and modifying constraints including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling and more
• implementing data types such as FILESTREAM, spatial, structured, and semi-structured, collations and others
• implementing partitioning solution including partitioned tables and indexes, distributed partitioned views, etc

2. Implementing Programming Objects (16%)

The second largest focus point of the 70-433 exam focuses on implementing programming objects and makes up 16% of the total exam. This section measures your ability to:

• create and alter stored procedures including able-valued parameters, parameter direction, manage permissions and more
• create and alter user-defined functions (UDFs) including WITH SCHEMABINDING, EXECUTE AS, managing permissions, etc
• create and alter DML (data manipulation language) triggers such as INSERTED, DELETED, INSTEAD OF, EXECUTE AS and others
• create and alter DDL (data definition language) triggers such as enabling/disabling, return event data and others
• create and deploy CLR-based objects including permission sets, SET TRUSTWORTHY and more
• implement error handling including TRY/CATCH, RAISERROR, retrieving error information, custom error messages and more
• manage transactions such as BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL and others

3. Working with Query Fundamentals (21%)

Working with query fundamentals takes up the largest portion of the 70-433 exam and requires that you are familiar with:

• query data by using SELECT statements such as LIKE, WHERE, ORDER BY, and INTO
• modify data by using INSERT, UPDATE, and DELETE statements
• return data by using the OUTPUT clause as well as INSERTED, DELETED, and INTO statements
• modify data by using MERGE statements such as INSERTED, DELETED, and OUTPUT
• implement aggregate queries including built-in aggregate functions, GROUPING SETS, and GROUP BY, among others
• combine datasets using CROSS APPLY, OUTER APPLY, UNION, UNION ALL, INTERSECT, EXCEPT and more
• apply built-in scalar functions such as CAST and CONVERT, REPLACE, PATINDEX and CHARINDEX and others

4. Applying Additional Query Techniques (15%)

The next topic covers additional query techniques and accounts for 15% of the 70-433 exam. This area will test your ability to:

• implement subqueries such as simple, correlated, scalar, list, and table valued
• implement CTE (common table expression) queries including recursive and non-recursive queries
• apply ranking functions such as RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, and NTILE
• control execution plans like table hints and query hints
• manage international considerations including collations, defining custom errors, filtering data, sort order, nvarchar, and more

5. Working with Additional SQL Server Components (11%)

Working with additional SQL Server components such as database mail, full-text search, SMOs and PowerShell scripts among others, accounts for 11% of the total exam. To prepare for this section of the exam you must be familiar with:

• integrating database mail
• implementing full-text search (CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST)
• implementing scripts using PowerShell and SQL Server Management Objects (SMOs)
• implementing service broker solutions including services, queues, messages, message types, message validation, contracts and activation procedures
• tracking data changes including database audit specification and CHANGETABLE
  http://www.sqldbatips.com/showarticle.asp?ID=136

6. Working with XML Data (12%)

The next focus area takes up 12% of the 70-433 exam and centers on working with XML data. For this portion of the exam you will need to know how to retrieve relational data as XML (FOR XML), transform XML data into relational data (OPENXML, sp_xml_preparedocument, sp_xml_removedocument), query XML data (XQUERY, XPATH) and manage XML data (XML datatype, XML indexes, XML schema collections).

7. Gathering Performance Information (11%)

The final section of the 70-433 exam focuses on gathering performance information and takes up just 11% of the total exam. To prepare for this portion of the exam you will need to know how to capture execution plans (SHOWPLAN), gather trace information by using the SQL Server Profiler, collect output from the Database Engine Tuning Advisor and collect information from system metadata (Dynamic Management Views (DMVs) and catalog views).

 

Temporary Tables in sql server

Temporary Tables are created under the tempdb database. 

Types of Temporary Table

1. Global Temporary Table (represented by ##)
2. Local Temporary Table (represented by #)
3. Table Variable

1. Global Temporary Table

Global Temporary tables are created under the tempdb and will be represented by ##.

Say suppose I am creating Global Temporary table named as employee so it will be like ##employee.

we can use Global Temporary table in everywhere i.e. in query, SP etc except in Functions.

When we used Global Temporary Table in Query the scope of the table is through all the active sessions in Management Studio. we can create index on Global Temporary Table. We can use nolock clause with Global Temporary Table.

Syntax to create Global Temporary Table,

create table ##employee
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

2. Local Temporary Table

Local Temporary tables are created under the tempdb and will be represented by #.

Say suppose I am creating Local Temporary table named as employee so it will be like #employee.

We can use Local Temporary table in everywhere i.e. in query, SP etc except in Functions.

When we used Local Temporary Table in Query the scope of the table is to the active session only in Management Studio. Where we are created Local Temporary table or we are directly inserting into it. we can create index on Local Temporary Table. We can use nolock clause with Local Temporary Table.

Syntax to create Local Temporary Table,

create table #employee
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

3. Table Variable

Table variable is used like a variable where we declare a variable of type table.

Say suppose I am declaring the table variable.

declare @employee Table
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

The table variable is worked like a table.

Table variable we can use in everywhere including functions. We can’t create the indexes on table variable.

We can’t use no lock clause with Table Variables.

Generally we used above #1,#2 & #3 when we are dealing with performance because using this in query improves the performance of a query.

—- Global Temporary Table
DROP TABLE ##employee

CREATE TABLE ##employee

(

empid INT IDENTITY(1,1),

empname VARCHAR(100),

empaddress VARCHAR(100)

)

INSERT INTO ##employee(empname,empaddress)

SELECT ‘Vikrant’,’Pune’

UNION ALL

SELECT ‘Shrikant’,’Ahamadnagar’

UNION ALL

SELECT ‘Ashish’,’Nagpur’

SELECT * FROM ##employee

—- Local Temporary Table
DROP TABLE #employee

CREATE TABLE #employee

(

empid INT IDENTITY(1,1),

empname VARCHAR(100),

empaddress VARCHAR(100)

)

INSERT INTO #employee(empname,empaddress)

SELECT ‘Vikrant’,’Pune’

UNION ALL

SELECT ‘Shrikant’,’Ahamadnagar’

UNION ALL

SELECT ‘Ashish’,’Nagpur’

SELECT * FROM #employee

— Table Variable
DECLARE @employee TABLE

(

empid INT IDENTITY(1,1),

empname VARCHAR(100),

empaddress VARCHAR(100)

)

INSERT INTO @employee(empname,empaddress)

SELECT ‘Vikrant’,’Pune’

UNION ALL`

SELECT ‘Shrikant’,’Ahamadnagar’

UNION ALL

SELECT ‘Ashish’,’Nagpur’

SELECT * FROM @employee

 

Adding and Removing the Default Constraint on a perticular table.

Default Constraints :- 
Default value is something like to adding the values into specific columns and if not specify then it will automatically insert some value it means that the column should not fill with the null values.

Adding the default constraints,

in my case i have an table MergeMe

CREATE TABLE [dbo].[MergeMe](
[id] [int] IDENTITY(1,1) NOT NULL,
[Sname] [varchar](100) NULL,
[salary] [money] NULL,
[dtdate] [datetime] NOT NULL
)

to add default constraint ,

alter table MergeMe add default getdate() for dtdate

insert into MergeMe(Sname,salary) values(‘vikrant’,10000)

it will insert records and in dtdate it will insert the getdate() which we have set as default.

now if we try to delete the column it will gives us an error,

after running this query

alter table MergeMe drop column dtdate

give error,

Msg 5074, Level 16, State 1, Line 2
The object ‘DF__MergeMe__dtdate__689D8392’ is dependent on column ‘dtdate’.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE DROP COLUMN dtdate failed because one or more objects access this column.

to find out the dependencies, run the following query ,

select
col.name,
col.id,
OBJECTPROPERTY(col.id, N’dtdate’) as is_ID,
dobj.name as def_name
from syscolumns col
left outer join sysobjects dobj
on dobj.id = col.cdefault and dobj.xtype = ‘D’
where col.id = object_id(N’dbo.mergeme’)
and dobj.name is not null

name id                 is_ID     def_name
dtdate 1739153241 NULL    DF__MergeMe__dtdate__689D8392

now we got the dependent name from the error or from the above query,

drop the constraint using following query,

alter table MergeMe drop constraint DF__MergeMe__dtdate__689D8392

after this now we can drop the column,

alter table MergeMe drop column dtdate

 

JOINS IN SQL SERVER

By this article i am trying to show you the joins in different way i.e. by in Maths Terms by the sets as well as the query which will give you the return in one single cilck of Execute in Management Studio.

Joins :- Joins is basically returns the resultset by making a merge between two table (which is at row level).
Types Of Joins :-

1] Inner Join
2] Outer Join
3] Self Join

Lets consider the 2 sets that we have in maths

1] Natural Number {Column name = iNATURAL_NUMBER int}
2] Even-Odd Numbers (shuffle i.e. some from Even and some from ODD) {Column name = iEVEN_ODD_NUMBER int}

#1 is our set/Table A={0,3,4,7,8,9,10,11,12,13}
#2 is our next set/Table B={0,2,3,6,7,10,11,14}

Lets consider we have above data in 2 tables that is
in table A we have data 0,3,4,7,8,9,10,11,13 and in table B we have data 0,2,3,6,7,10,11,14

1] Inner Join :- it is an simple join between two tables which returns the resultsets for matching of rows based on the on clause of join.

Math Term Inner Join will be for A & B is
A Inner JOIN B ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,3,7,10,11}

2] Outer Join :- it has 3 sub types
2.1] Left Outer Join
2.2] Right Outer Join
2.3] Full Outer Join

2.1] Left Outer Join :- it is an join applied on Left table which returns the resultsets for rows based on the on clause of join and for non matching give NULL from right table. It give all the records from the Left table and Null from the nonmatching in Right table.

A Left Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,3,4,7,8,9,10,11,12,13}

if we used Left outer Join on query gives NULL for the Right Table in our example for (4,8,9,12,13) gives (NULL,NULL,NULL,NULL,NULL) from query which is not in the table B.

2.2] Right Outer Join :- it is an join applied on Right table which returns the resultsets for rows based on the on clause of join and for non matching give NULL from Left table. It give all the records from the Right table and Null from the nonmatching in Left table.

A Right Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,2,3,6,7,10,11,14}

if we used Right outer Join on query gives NULL for the Left Table in our example for (2,6,14) gives (NULL,NULL,NULL) from query which is not in the table A.

2.3] Full Outer Join :- it is an join applied on both table which returns the resultsets for rows based on the on clause of join and for non matching give NULL from Right as well as Left table. It give all the records from the Right as well as Left table and Null from the nonmatching in right as well as left table.

A Full Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,2,3,4,6,7,8,9,10,11,12,13,14}

if we used Full outer Join on query gives NULL for the right as well as left Table in our example for Right (4,8,9,12,13) gives (NULL,NULL,NULL,NULL,NULL) and for Left (2,6,14) gives (NULL,NULL,NULL) from query which is not in the table A and B.
2.4] Left Outer Join With NULL :- it will use the out put from the #2.1 and returns for only non matching from Right table

A Left Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER WHERE B.iEVEN_ODD_NUMBER is NULL = {4,8,9,12,13}

if we used Left outer Join with NULL on query gives all the records from the left table having null from right table in our example for (4,8,9,12,13) gives (NULL,NULL,NULL,NULL,NULL) from query which is not in the table B.

2.5] Right Outer Join With NULL :- it will use the out put from the #2.2 and returns for only non matching from Left table

A Right Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER WHERE A.iNATURAL_NUMBER is NULL = {2,6,14}

if we used Right outer Join with NULL on query gives all the records from the left table having null from Left table in our example for (2,6,14) gives (NULL,NULL,NULL) from query which is not in the table A.

2.6] Full Outer Join With NULL :- it will use the out put from the #2.3 and returns for only non matching from Left table

A Right Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER WHERE A.iNATURAL_NUMBER is NULL OR B.iEVEN_ODD_NUMBER IS NULL = {4,8,9,12,13,2,6,14}

if we used Full outer Join with NULL on query gives all the records from the left & Right table having null from Left table in our example for (2,6,14) gives (NULL,NULL,NULL) and Right table in our example for (4,8,9,12,13) gives (NULL,NULL,NULL,NULL,NULL) from query which is not in the table A and B.
——————-
The query which you can directly run for Joins

IF EXISTS (SELECT * FROM sys.databases WHERE name =’JOINS’)
BEGIN
USE master
DROP DATABASE JOINS
CREATE DATABASE JOINS
END
ELSE CREATE DATABASE JOINS

USE JOINS

CREATE SCHEMA JOIN_DEMO

–DROP TABLE JOINS.JOIN_DEMO.NATURAL_NUMBERS
CREATE TABLE JOINS.JOIN_DEMO.NATURAL_NUMBERS
(
iNATURAL_ID INT IDENTITY(1,1) PRIMARY KEY,
iNATURAL_NUMBER INT
)

INSERT INTO JOINS.JOIN_DEMO.NATURAL_NUMBERS VALUES(0),(3),(4),(7),(8),(9),(10),(11),(12),(13)

–DROP TABLE JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS

CREATE TABLE JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS
(
iEVEN_ODD_ID INT IDENTITY(1,1) PRIMARY KEY,
iEVEN_ODD_NUMBER INT
)
INSERT INTO JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS VALUES(2),(11),(6),(7),(10),(14),(3),(0)

SELECT iNATURAL_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS
SELECT iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS

—Types of joins
—- 1] Inner Join
—- 2] Outer Join
—- 3] Self Join
—- 4] Cross Join

—-#1] Inner Join

SELECT NN.iNATURAL_NUMBER,EN.iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS NN WITH(NOLOCK)
JOIN JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS EN WITH(NOLOCK) ON NN.iNATURAL_NUMBER = EN.iEVEN_ODD_NUMBER

—-#2] Outer Join has sub joins…
—- #2.1] Left Outer Join
—- #2.2] Right Outer Join
—- #2.3] Full Outer Join
—- #2.4] Left Outer Join WITH NULL
—- #2.5] Right Outer Join WITH NULL
—- #2.6] Full Outer Join WITH NULL

—- #2.1] Left Outer Join

SELECT NN.iNATURAL_NUMBER,EN.iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS NN WITH(NOLOCK)
LEFT OUTER JOIN JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS EN WITH(NOLOCK) ON NN.iNATURAL_NUMBER = EN.iEVEN_ODD_NUMBER

—- #2.2] Right Outer Join

SELECT NN.iNATURAL_NUMBER,EN.iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS NN WITH(NOLOCK)
RIGHT OUTER JOIN JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS EN WITH(NOLOCK) ON NN.iNATURAL_NUMBER = EN.iEVEN_ODD_NUMBER

—- #2.3] Full Outer Join

SELECT NN.iNATURAL_NUMBER,EN.iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS NN WITH(NOLOCK)
FULL OUTER JOIN JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS EN WITH(NOLOCK) ON NN.iNATURAL_NUMBER = EN.iEVEN_ODD_NUMBER

—- #2.4] Left Outer Join WITH NULL

SELECT NN.iNATURAL_NUMBER,EN.iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS NN WITH(NOLOCK)
LEFT OUTER JOIN JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS EN WITH(NOLOCK) ON NN.iNATURAL_NUMBER = EN.iEVEN_ODD_NUMBER
WHERE EN.iEVEN_ODD_NUMBER IS NULL

—- #2.5] Right Outer Join WITH NULL

SELECT NN.iNATURAL_NUMBER,EN.iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS NN WITH(NOLOCK)
RIGHT OUTER JOIN JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS EN WITH(NOLOCK) ON NN.iNATURAL_NUMBER = EN.iEVEN_ODD_NUMBER
WHERE NN.iNATURAL_NUMBER IS NULL

—- #2.6] Full Outer Join WITH NULL

SELECT NN.iNATURAL_NUMBER,EN.iEVEN_ODD_NUMBER FROM JOINS.JOIN_DEMO.NATURAL_NUMBERS NN WITH(NOLOCK)
FULL OUTER JOIN JOINS.JOIN_DEMO.EVEN_ODD_NUMBERS EN WITH(NOLOCK) ON NN.iNATURAL_NUMBER = EN.iEVEN_ODD_NUMBER
WHERE EN.iEVEN_ODD_NUMBER IS NULL OR NN.iNATURAL_NUMBER IS NULL

 

How to run the SQL Queries on local system / Remote Queries without installing the license version of Microsoft SQL SERVER

Here is my first blog on Microsoft SQL SERVER,

How to run the SQL Queries on local system / Remote Queries without installing the license version of Microsoft SQL SERVER.
we can run remote queries as well as an necessary SQL to learn on the freely available versions of SQL SERVER provided by the Microsoft here are the link by downloading it and following the steps given in the link below we can run the remote queries without installing full version of SQL SERVER on System.  (NOTE to run remote queries you need VPN to connect might be rather than VPN there will be other available and you are using it.)

download SQL SERVER 2008 Express with Advanced Services

download this will be a Management Studio Express without server

this is SQL server with everything

this will give us the Management Studio through which we can connect to the local as well as Remote Server.

Thanks!

Vikrant More.