SQL Joins

Very good article regarding basics of JOINS...

SQL JOINS

OUTPUT Clause in SQL SERVER 2008 - Update

 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
OLDNAME VARCHAR(100),
PRICE Decimal(18,2),
OLDPRICE Decimal(18,2) 
  ); 
  
  UPDATE  dbo.SourceTable
  SET Name='TestUpdate',
 Price=64
  OUTPUT
 inserted.ID,
 inserted.Name,
 deleted.Name,
 inserted.Price,
 deleted.Price
  INTO @OUTPUTTABLE(ID,NAME,OLDNAME,PRICE,OLDPRICE)
  WHERE ID=3
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE

OUTPUT Clause in SQL SERVER 2008 - INSERT

In SQL Server 2008, you can add an OUTPUT clause to your data manipulation language (DML) statements. The clause returns a copy of the data that you have inserted into or deleted from your tables. This contains the copy of data you are inserting or updating or deleting.


Here is the example code of OUTPUT clause.


CREATE TABLE [dbo].[SourceTable1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Price] [money] NULL
)


 Here is the output clause code


 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
PRICE decimal(18,2)
  );  
  
   INSERT INTO [dbo].[SourceTable]
OUTPUT 
INSERTED.*
INTO @OUTPUTTABLE
  VALUES('Test','25')  
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE


You can also define the column names in the query.


 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
PRICE decimal(18,2)
  ); 
  
  
  INSERT INTO [dbo].[SourceTable]
OUTPUT
INSERTED.Name,
inserted.Price
INTO @OUTPUTTABLE(NAME,Price)
  VALUES('Test','25')  
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE

APPLY OPERATOR IN T-SQL

APPLY operator is used in the FROM clause of a query. It allows you to call a Table valued function for each row of your outer TABLE. We can pass outer table's columns as function arguments.

It has two options:

1. CROSS APPLY, and
2. OUTER APPLY

CROSS APPLY will not return the outer tables row if function table has no row corresponding to it, whereas OUTER APPLY returns NULL values instead of function columns.

Let's take an example.

First I will make a table valued function and then I will try to show you how to apply the "APPLY" operator.

USE [AdventureWorks]
GO
/****** Object:  UserDefinedFunction [dbo].[fnGetDepartmentId]    Script Date: 07/15/2012 21:54:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetDepartmentId] (@empid int)
RETURNS TABLE
AS
RETURN
(
--Outer Query
SELECT D.DepartmentID,D.Name 
FROM [AdventureWorks].[HumanResources].[EmployeeDepartmentHistory] EDH
INNER JOIN [AdventureWorks].[HumanResources].[Department] D
ON EDH.DepartmentID=D.DepartmentID
WHERE Employeeid=@empid
) 


This function will return the department name and its ID according to the each employee ID.


Now I will use the APPLY operator to get my required results.


SELECT E.EmployeeID,E.Title,DepartmentID,Name AS DepartmentName

FROM [AdventureWorks].[HumanResources].[Employee] E
CROSS APPLY
dbo.fnGetDepartmentId(E.EmployeeID)

Adventureworks Sample Database ER Diagrams

Adventureworks is the sample database from the Microsoft for the SQL Server. It is very helpful to understand any database if you have its ER diagram. So here is the all the ER diagrams I searched from internet.
ER diagrams

Restoring SQL server database ".bak" file

Right click on the database tab –> All Tasks –> Restore Database –> From device –> Select Devices –> Add your .bak file. Click OK. 

Now click the options tab. Here you will see the logical file name of your database. At its right side there will be a physical path of this file. Make sure this path is redirecting to the location where your database files exist, normally default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Data" at the end of this path concatenate your file name. After concatenation your path should be something like this

C:\Program Files\Microsoft SQL Server\MSSQL\Data\test.mdf

C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_Log.ldf.

Now click ok. Large databse normally takes lot of time.

Relationship types

Association

 Association is the relationship in which each object has its own life cycle but have no ownership. It is a weak relationship.

For example Teacher and Student relationship. A teacher can have more than one teacher and one student can have more than one teachers. So there is no ownership.

Aggregation

It is a stronger relationship. It is a specialization of Association. Objects have their own life cycle but there is ownership. Let's take example of Teacher and Department. A department has many teachers but a teacher can have only one department. If we delete the department then teacher would exist.

INSERT, DELETE, UPDATE in VIEW

A view can be Updated, deleted and inserted only if it has only one base table.

For example consider there is a table called book and it has view called Book_View. So we can update, delete and insert in that view and this view will insert the data in actual table “Book”. So if we have a view which contains more than one table then we can't perform these operations on that view.

Consider the view that list the books on the basis of category. So there would be join on book table and category table on categoryID column in this view. So we can’t perform updating,inserting and deleting operations on this view because SQL server will get confuse and will give error.

Using SQL SERVER Templates

SQL SERVER management studio has very handy feature using and creating Template Scripts. It has many built in template script which we can use e.g. creating Tables, Stored Procedures Functions etc. You can see the SQL SERVER built in template list by going to View à Template Explorer or using the short key Ctrl+Alt+T

T-SQL String Functions

 --[SUBSTRING( string, start, length )]
    SELECT SUBSTRING('abcdef', 4, 3);   -- ## OUTPUT -- def ##--
   
 --[RUGHT( string, n )]
    SELECT RIGHT('abcde', 3);           

-- ## OUTPUT -- cde ##--
   
 ---[LEFT( string, n )]
    SELECT LEFT('abcde', 3);            

-- ## OUTPUT -- abc ##--
   
 --[LEN( string );]
    SELECT LEN('abcde');              

  -- ## OUTPUT -- 5 ##--
   
 --[CHARINDEX( substring, string [, start_pos] )]
    SELECT CHARINDEX('-','abcdef ghi-jk');   

-- ## OUTPUT -- 11 ##--
   
 --[PATINDEX( pattern, string )]
    SELECT PATINDEX('%[0-9]%', 'abcdfe124563efgh');   

-- ## OUTPUT -- 7 ##--
   
 --[REPLACE( string, substring1, substring2 )]
    SELECT REPLACE('1*3 2*87', '*', '-');           

-- ## OUTPUT -- 1-3 2-87 ##--
   
 --[REPLICATE( string, n )]
    SELECT REPLICATE('abcdef', 2);                

-- ## OUTPUT -- abcdefabcdef ##--
   
 --(The STUFF function allows you to remove a substring from a string
 -- and insert a new substring)
 -- [STUFF( string, pos, delete length, inserting string )]
    SELECT STUFF('tabc', 2, 3, 'his');    

-- ## OUTPUT -- this ##--
   
-- [UPPER( string ), LOWER( string )]
    SELECT UPPER('This is a BOOK');       

-- ## OUTPUT -- THIS IS A BOOK ##--
    SELECT LOWER('This is a BOOK');        

-- ## OUTPUT -- this is a book ##--
   
 --RTRIM( string ), LTRIM( string )
    SELECT LTRIM(' abc');                  

-- ## OUTPUT -- abc ##--
    SELECT RTRIM('abc ');
   
 -- [The LIKE predicate]
    SELECT NAME FROM TBLNAME WHERE NAME LIKE '_d%'  
 -- ## OUTPUT --    Will return names where second character will be d ##-- 
    SELECT NAME FROM TBLNAME WHERE NAME LIKE '[ABC]%' 
-- ## OUTPUT -- Return the names where starting characters will be A,B or C  ##-- 
    SELECT NAME FROM TBLNAME WHERE NAME LIKE '[A-F]%'   
-- ## OUTPUT -- Return the names where starting characters will be A to F  ##-- 
    SELECT NAME FROM TBLNAME WHERE NAME LIKE '[^A-F]%' -- ## OUTPUT -- Return the names where starting characters will not from A to F  ##--

The difference between require() and include()

Here is the explaination from PHP manual.

From PHP manual:-   

require() and include() are identical in every way except how they handle failure. include() produces a Warning while require() results in a Fatal Error. In other words, don’t hesitate to use require() if you want a missing file to halt processing of the page. include() does not behave this way, the scriptwill continue regardless. Be sure to have an appropriate include_path setting as well.

The CSS Box Model

                                                                                               
 
In the middle there is an HTML element like a <p>, <h> or <div> with some height and width. Then there is padding around it and then some border and then finally outside there is margin, which decides the room between an element and its surroundings. Consider the following example of CSS to understand how it works:

.MyMainDIVClass

{

               Width:100px; Height:100px; padding:10px; border: 2px solid gray;

}

…

<div class=’MyMainDIVClass’>

This is box model explanation.

</div>

So what will be the width of this div? So answer is 124px. Because 100 + Padding on both sides =20  makes 100+20=120 and then border on both sides =4 makes 120+4=124

Similarly height will also be 124 by same calculation.

So this how CSS box model works.

.NET Framework Building Blocks

.NET framework is a cluster of several technologies. These include the following:

.NET Languages:

These languages are Visual Basic, C# , J# and C++.

Common Language Runtime (CLR):

The engine that execute all the .NET programs and provide them automatic service like memory management and security checking.

Class Library:

Thousands of pieces of prebuilt functionality that you can use in your web application. Such as ADO.NET – The technology for creating Database application and Windows Forms – The technology for desktop applications.

ASP.NET:

This is the engine that hosts all the web applications.

SQL Server CTE Basics

The very good article written about the CTE in SQL server. I found it very useful so I thought to share it with you.

SQL SERVER CTE Basics 

Efficient Text Searching Query

Normally when you want to write a query for searching something then you use the like operator. Consider for example that you want to write an algorithm for book search. Consider a situation where you ask BOOK TITLE from the user and then user enter the title the you write query something like this

SELECT BOOK_NAME WHERE TITLE LIKE ‘%@title%’

Now what if you want that if user write some title then your query perform above operations and if user write nothing , leave the field empty then you want to show all the books available in your system, so here is the query which will perform the same operation

SELECT

BOOK_NAME

WHERE

          ((@title IS NULL) OR (@title = ‘’) OR ([TITLE] LIKE '%'+@title+'%'))

Now when you will enter some string then last part of the query will be executed and if you enter nothing then first part of the query will be executed.

SQL Writing and Execution Order

I'm writing this post because developers often get confused about the SQL clauses execution order. The most important and basic SQL clauses are 

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY 

And there order in SQL query is

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING 
6. ORDER BY

 But the database engine processes them in different ways. And the order of these clauses in DB is

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Sending HTML email through PHP

We can use mail function of PHP to send emails. For sending HTML email we've to set some headers.

Here is the syntax..

    $from="test@test.com";
    $to="test@test.com";
    $subject="your subject";



    $headers  = 'MIME-Version: 1.0' . "\r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";    

    $headers .= "From: ".$from."\r\n";
    $headers .= "Reply-To: ".$from."\r\n";
    $headers .= "Return-Path: ".$from."\r\n";



   if (mail($to,$subject,$message,$headers) )
   {
   echo 'Successfully sent'; 

    } else {

       echo "Email address is not valid";

    } 

CSS HACKS

Cross browsing is the main issue for the web developer because each browser has its own standards. So sometimes there are some formatting issues in different browsers. There are several techniques to handle these issues. One of them is CSS hacks. You make different CSS files and then check the browser and then load the corresponding CSS.

Here is the syntax of CSS hacks..

<link href="all_browsers.css" rel="stylesheet" type="text/css"> 

<!--[if IE 7]> <link href="IE7.css" rel="stylesheet" type="text/css"> <![endif]-->
<!--[if IE 8]> <link href="IE8.css" rel="stylesheet" type="text/css"> <![endif]-->
<!--[if !IE]>--> <link href="style.css" rel="stylesheet" type="text/css"> <!--<![endif]-->

Now these if statements will check the browser and then load the corresponding CSS file.

Making a two-column webtemplate using HTML and CSS

Consider this structure

<div class="mainDiv">
        <div class="headerDiv">
            <h2>This is my header</h2>
        </div>
        <div class="navigationDiv">
            <ul>
                <li><a href="#">Link1</a></li>
                <li><a href="#">Link2</a></li>
                <li><a href="#">Link3</a></li>
                <li><a href="#">Link4</a></li>
                <li><a href="#">Link5</a></li>
             </ul>
        </div>
        <div class="mainBodyDiv">
            <div class="leftContentDiv">Left</div>
            <div class="rightContentDiv">Right</div>
        </div>
        <div class="footerDiv">Footer</div>     
      
    </div>

 

Read full article

How to Centre a DIV Block Using CSS

Consider this structure.

<body>

<div id="wrapper">
</div>

</body>

CSS Property:

body {
    text-align: center;
    min-width: 600px;
    background:green;
}

#wrapper {
    margin:0 auto;
    width:600px;
    text-align: left;
    background:red;
    height:100px;
}

The technique will center the DIV because we are setting the margins to auto, web browsers are required by the CSS standard to give them equal width from both sides.