Joins in SQL

Different Types of Joins in SQL with Syntax and their uses.
This Pictures Explains it all.

Natural Sorting Using RIGHT, SUBSTRING and PATINDEX in SQL.

In this example
patindex gets the position of first numeric character using the [0-9] wild card expression, then substring gets the part of string from first numeric character to end of the string which is appended with 4 '0's from left.
then RIGHT gets the last 5 character of the newly formed substring.

if the string is abc1
then the string will be now '00001'
or
if the string is abc12
then the string will be now '00012'

etc.
Hence, we can use this expression for Natural sorting.

example:
ORDER BY RIGHT( '0000' + SUBSTRING( L_ID , ISNULL( NULLIF( PATINDEX( '%[0-9]%' , L_ID
                                                                                 ) , 0
                                                                       ) , LEN( L_ID
                                                                              ) + 1
                                                               ) , LEN( L_ID
                                                                      )
                                                ) , 5
                            )



Patindex:
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

PATINDEX ( '%pattern%' , expression )

Right:
Returns the right part of a character string with the specified number of characters.

RIGHT ( character_expression , integer_expression )

[]Wild Card for Patindex:
Matches any single character within the specified range or set that is specified between the brackets. These wildcard characters can be used in string comparisons that involve pattern matching, such as LIKE and PATINDEX.

Update from Another table or select query in SQL

In case when we need to Update a table from another table or select query we can use this syntax.

For update from another Table. 

Syntax:

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

Update From Select Query. 

example:

update 

    tbl_JournalMaster 

set 

    Remarks=dbj.pname 

from 

    tbl_JournalMaster 

inner join 

(
select 

    distinct DJ.vno,

            (select 

                 top(1) pname 

             from 

                 DBASEJournal 

             where 

                 vno=dj.vno) as pname 

             from 

                 DBASEJournal DJ

 ) dbj 

on 

  dbj.VNO=tbl_JournalMaster.JVNo  ;

Feel Free To Contact For any Comments

Gautam Kumar Gupta

Go To Home Page

T-Sql Interview Questions

1. What are DMVs? - Dynamic Management Views (DMVs), are functions that give you information on the state of the server. DMVs, for the most part, are used to monitor the health of a server. They really just give you a snapshot of what’s going on inside the server. They let you monitor the health of a server instance, troubleshoot major problems and tune the server to increase performance.
2. Define a temp table - In a nutshell, a temp table is a temporary storage structure. What does that mean? Basically, you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.
3. What’s the difference between a local  temp table and a global temp table? - Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.
4. How do you use transactions? - In general, there are three types of transactions that you can use in the SQL Server environment: BEGIN TRANSACTION, ROLL BACK TRANSACTION and COMMIT TRANSACTION. The gist behind deploying transactions is that they allow you to group multiple SQL commands into a single unit. From there, each transaction begins with a certain task, and ends when all the tasks within the transaction are complete. BEGIN TRANSACTION gets the ball rolling. ROLLBACK TRANSACTION functions a lot like an “undo” command, and COMMIT TRANSACTION completes all of the tasks within that transaction.
5. What’s the difference between a clustered and a non-clustered index? - A clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.
6. What are DBCC commands? - In very basic terms the Database Consistency Checker (DBCC) is used to aid in server maintenance. DBCC commands, many of which are completely undocumented, provide a set of commands that let you perform routing maintenance, status and validation checks. The most common DBCC commands are: DBCC CHECKALLOC (Lets you check disk allocation); DBCC OPENTRAN (Lets you check any open transactions); and DBCC HELP (shows a list of available DBCC commands to aid your server maintenance processes).
7. Describe the difference between truncate and delete - The difference between these two processes is fairly simple. Truncate means to simply empty out a table. On the other hand, the delete command lets you delete entire rows from within a table, but not all of the data within that table.
8. What is a view? - A view is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.
9. What is a Query Execution Plan? - SQL Server has several built-in tools that optimize how queries are executed within their databases. A query execution plan is exactly what it sounds like – a snapshot of how the optimizing tools will execute and deploy specific queries within the database. This service helps you troubleshoot problems with jobs that don’t necessarily execute perfectly.
10. What is the default port number for SQL Server? - While this is kind of a softball question – if you know anything about SQL Server you should at least know the basic configuration options – it’s an important one to nail in the interview. Basically, when SQL Server is enabled the server instant listens to the TCP port 1433.
11.  How to use sql query to copy only structure?
    Ans: select * into table2 from table1 where 1 = 2 
12.  How do we handle Error?
    Ans: I think we can use @@Error. Right after the query condition is executed we can check for @@Error <> 0, if it is not returning zero mean some error occured. Raiserror is another command for raising error We can also use Try catch block 
13. What is PatIndex?
    Ans: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
    Syntax - PATINDEX ( '%pattern%' , expression )
    Eg: USE AdventureWorks;
    USE AdventureWorks;
    GO
    SELECT PATINDEX('%ensure%',DocumentSummary)
    FROM Production.Document
    WHERE DocumentID = 3;
    GO 
14. How to query a string contains %?
    Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']' 
15. How to get values from a table with comma seperated?
    Ans: declare @vName nvarchar(100)
    set @vName = ''
    select @vName = @vName + ','+ [Name] from HumanResources.Shift
    select @vName 
16. How to update 'Yes' to 'No' and viceversa in a query?
    Ans: Update tablename set ColumnName1 = (case ColumnName1 when 'Yes'
    then 'No'else 'Yes' end) 
17. Consider you have a table with columns ID(primary key), Country and State.
    Now if you have some rows with combination of country and state repeating,
    ie, two rows with combination India, Kerala. Write a query for deleting
    duplicate records?
    
    Ans: With T1 as
    (Select *,Row_Number() over (partition by Country, State order by ID)
    as 'RowNo' From TableName)
    Delete from T1 where RowNo > 1; 
18. How to create temporary table? How do we apply noncluster index? What is nolock? When and where is nolock applied normally?
    
    Ans. Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.
    
    1.
    CREATE TABLE #tmpTable
    (
    OfficeName varchar(50)
    , officeid int
    , CustID int
    , AgentID int
    , mlsid varchar(4)
    , RequestMoreDetails int null
    , Emails int null
    )
    CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)
    
    2.
    select
    OfficeName
    , officeid
    , o.CustID
    , AgentID -
    , o.mlsid
    , PrintBrochure_Views = null
    , RequestMoreDetails = null
    , Emails = null
    into #ForOffices from #Offices o
    LEFT JOIN dbo.planparts WITH (NOLOCK)
    ON bppa.officeid = o.RID
    CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID) 
19. Another simple example for using temporary table.
        Also how to split a single column in to multiple columns based on column value.
        Here date_format function format date to yyyymm format.
        period_diff function returns number of months between two date parameters passed. Also parameter expect value in either yymm or yyyymm format.