Important Links Related to SQL Server And SQL Programming

1) Microsoft SQL SERVER Programming FAQ :
This Link has many questions and there solutions on MS SQL SERVER..

2) akadia.com
This is an excellent source for guidelines on SQL Server as well as ORACLE and few MS tech like .NET etc.

3) Pinal Dave's Blog
This is an excellent blog, where one can find articles on SQL, and also several question and answers. Do go throught it once.

Important Links Related to SQL Server And SQL Programming

Reference for Convert function

Main References:

MSDN Library

SQLJunkies


Also look :
Performance tip



Return to Convert FUNC

Reference For Sub queries

Main reference :
akadia.com









Return to SubQueries

Sub queries

A subquery is a SELECT statement that is nested within another T-SQL statement.

A sub query may be in a column list, In 'HAVING' clause, 'WHERE' clause, FROM clause, FROM clause of UPDATE statement. A sub query may also be used to create Virtual tables that can further used in a join statement.

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query.


A subquery, when executed independent of the outer query, will return a result set, and is therefore not dependent on the outer query. Where as, a correlated subquery cannot be executed independently of the outer query because it uses one or more references to columns in the outer query to determine the result set returned from the correlated subquery.

This link is really good reference and has good examples on subqueries. Dont miss the conclusion at the bottom of the page. The lines above are a copy and paste from that.

Reference ..

Convert Function in SQL

The convert function is used to convert datatype into another in a particular pattern.
The syntax of convert is as follows :

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The datatype is a system supplied data type to which the expression is being converted.

The expression is any valid expression

The length is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

And the style is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). Also Refer

Note : If you use the CONVERT function to convert a value to a variable length datatype, such as VARCHAR, always specify the length of the variable datatype. If you do not, SQL Server assumes a default length of 30. Ideally, you should specify the shortest length to accomplish the required task. This helps to reduce memory use and SQL Server resources.

Example...

Reference ..



Padding text and numbers in SQL SERVER 2000

(Recently I had a job to search for mapping between 5 tables. There was no documentation and no FKs between them. So I had to do it the hard way out.
As soon as I began I found two different columns in two different tables, which looked to be same. Well both of them were customer numbers. But one of them was had a width of 8 characters while the other was embedded as Customer number alongwith two other fields.
Moreover the length customer number field in the second column was only 6. Well 'That would be an easy job of padding I thought' and used 'lpad' which gave me an error instantly.
Ohh yes I know what you think, just append '00' to that, smart UHH!. But heres the twist, some of the numbers were just 4 or 5 digits long in it. And the corresponding column had exactly eight digits in every row. )


You could as well skip that short story of mine above.
Well I could not find any function defined for SQL SERVER which would do left padding for me, But did get my hands on some forums that gave me the folowing Idea.

Consider a six digit cust_num say '103875', And I have to convert it to say '00103875'. well just use

SELECT RIGHT( REPLICATE('0',8)+'103875' , 8)

or rather

SELECT RIGHT( REPLICATE('0',8)+cust_num , 8) from table1

My job also included some playing with substrings as i had to cut the number from another number. But the above query would explain everything about padding.

Somethings I would like to mention about the above query are the function Replicate(), actually replicates the string parameter (1st) , (2nd) parameter number of times. I.e. '0' eight times in this case.
And RIGHT function gives you the n number of characters from the string. again 8 in this condition.

So even if the number would have been '98745' the following would have taken place

Replicate + '98745' --> '00000000'+'98745'

Right(,8)
'0000000098745' --> '00098745'

Ya I know it looks much more like the CHEMICAL formulae we use to write in school. BUT I hope I made my point.


Now to append something to numbers lets say 3 zeroes on number 6 on the left we use the following...


select right(convert(varchar , 10000+6 ),4)


I don't think this needs to be explained. And yes this would append zeroes on the left but what if we want zeroes on right... I know that must be easy now.. Just use '*' instead of '+' and 'left' instead of 'right'.

FUNCTIONS USED : Convert , Right , Replicate

Links Helpful for SQL Join

Article on Join Clause In SQL


Refered sites :
http:/en.wikipedia.org

MSDN library (Transact SQL JOINS)

Craig Freedman's SQL SERVER BLOG (Introduction & details Quite good)

Ward Pond's SQL SERVER BLOG (Quite good for Introduction & details )

Some more pages of interests:
Joins Visual Explanation
MSDN search (SQL JOINS)
Advanced Query tuning Concepts

JOIN clause in SQL

Referenced Links

A JOIN clause in SQL combines records from two tables in a relational database and results in a new (temporary) table, also called a "joined table". SQL specifies two types of joins: inner and outer.

A predicate, i.e. a condition used to join two tables, is used to create a join between two tables.
SQL:2003 specifies two different syntactical ways to express joins.
The ' EXPLICIT JOIN notation' uses the keyword join.
The 'IMPLICITJOIN notation' lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them.

A> INNER JOIN : Represents the default join type, and occurs most commonly in applications.
It returns the all the records from the tables which satisfy the predicate.
Inner joins are fully commutative. “A inner join B” and “B inner join A” are equivalent.
One can further classify inner joins as equi-joins, as natural joins, or as cross-joins.
eg : Explicit Inner join :

SELECT
table1.*, table2.*
FROM table1
INNER JOIN table2
ON ( table2.table1ID = table1.ID ) -- predicate
-- Here table2.table1ID refers to (FK) table1.ID column.

Thus this query would return all the rows in table 2 whose table1ID matches the values of table1.ID, alongwith the corresponding table1 rows.

Implicit Inner Join:

SELECT table1.*, table2.*
FROM table1, table2
where ( table2.table1ID = table1.ID ) -- predicate

This query would return the same results as the one above.


Types Of Inner JOIN :
1> EQUI-JOIN [equijoin, theta join] :
is specific type of comparator-based join which uses only equality based join-predicates.
eg :
SELECT
table1.*, table2.*
FROM table1
INNER JOIN table2
ON ( table2.table1ID = table1.ID ) -- predicate
-- Here only the equality operator is used.


2) Natural join :
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

eg: consider both tables have only one column with same name say com_col , then.

SELECT *
FROM table1
NATURAL JOIN table2

will return the following structure:

table1.cols, com_col , table2.cols

3) CROSS JOIN [cartesian join] : Provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus it equates to an inner join where the join-condition always evaluates to True. i.e. it returns all the rows in one table for every row in the another.
eg:
SELECT * FROM table1 CROSS JOIN table2 -- Explicit

SELECT * FROM table1,table2

Both the queries would return rows equal to the product of rows in both tables.





B> OUTER JOIN :

An outer join does not require each record in the two joined tables to have a matching record in the other table. The joined table retains each record — even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Full outer joins are commutative. In addition, “A left outer join B” and “B right outer join A” are equivalent.

1> LEFT OUTER JOIN :
Its result contains all the rows satisfying the predicate and also all the remaining rows from the left table for which there are no matching records in the right table

eg:
SELECT
table1.*, table2.*
FROM table1
LEFT OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

This query would return all the rows in table1 but only matching rows from table2. For all the rows in table1, that do not have corresponding rows in table2, the values for table2 columns will be NULL.

2> RIGHT OUTER JOIN :
This is similar to the LEFT OUTER JOIN, except that it will return all the rows from the table on right, but only the rows from left table which have matching values in the right table.

eg:
SELECT
table1.*, table2.*
FROM table1
RIGHT OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

Now in this case all the rows from table2 two will be returned, whereas from table1 only those rows will be populated which will have matching values in table2.


3> FULL OUTER JOIN :
The result in this case combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

eg:
SELECT
table1.*, table2.*
FROM table1
FULL OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

In this case all the rows from table1 as well as from table2 are returned. Also the rows which dont have any matching values in the other table will be populated with null values.
i.e. for the rows from table1 which do not have corresponding values in table2, will have the columns from table2 poulated as NULL.



Referenced Links