Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Between function in SQL Server

This function is used to select the values within a specified range. These values can be of Int type or Date data type or Text data type.

Syntax:-

    SELECT column_name(s)
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2


In this article, we are going to use…

Read more

0 comments, 73 reads

Posted in Vivek's SQL Notes on 11 August 2014

LEFT() function in SQL Server

Left():- This function returns the specified number of characters from the left part of the given character string.

Syntax:-


Select LEFT ( character_expression , integer_expression )
   
For example, the below query will return two characters from the beginning

Select LEFT  ('Vivek',2)

Result

Vi

Read more

0 comments, 94 reads

Posted in Vivek's SQL Notes on 11 August 2014

RIGHT() function in SQL Server

Right():- This function returns the specified number of characters from the Right part of the given character string.

Syntax:-

 Select RIGHT ( character_expression , integer_expression )

 For example, the below query will return two characters from the last.

 Select RIGHT('Vivek',2) 

 
 Result

 ek

Read more

0 comments, 76 reads

Posted in Vivek's SQL Notes on 11 August 2014

Intersection command in SQL Server

This function is used to returns distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. For intersection command, below basic rules must be followed.
  1. The number and the order of the columns must be the same in all queries.
  2. The…

Read more

0 comments, 228 reads

Posted in Vivek's SQL Notes on 11 August 2014

Exception handling in SQL Server

                    
SQL server gives the exception/error handling mechanism which is similar to c#, Try..Catch block. In SQL Server, we can write the SQL Statements within the Try block and if any exception occurred control automatically goes to the group of SQL Statements written inside the next Catch block.

The syntax…

Read more

1 comments, 8,062 reads

Posted in Vivek's SQL Notes on 11 August 2014

Question of the Week -08/04/2014


Suppose we need to find out the name of all the months in a given year (Year will be given as an input parameter) where first day of the month falls on weekend (Saturday or Sunday). 

So what should be the query/stored procedure to get the above result?

Read more

5 comments, 201 reads

Posted in Vivek's SQL Notes on 8 April 2014

Answer - Objective Question (How many Null values in an unique constraint can be inserted in SQL Server)

Question:- How many NULL value allowed in a column in which Unique constraint is defined in SQL Server?

Options:-

a) 1

b) 2

c) More than 1

Answer :- Option a) 1

In SQL Server, we can Insert only one null value in a column where we have defined the…

Read more

0 comments, 200 reads

Posted in Vivek's SQL Notes on 8 April 2014

Happy Holi



Holi is the time to unwind de-stress and bond with sweets,Thandai and colours.Come let’s rejuvenate by immersing ourselves in the colour of joy,happiness and laughter!Happy Holi!

Read more

0 comments, 153 reads

Posted in Vivek's SQL Notes on 16 March 2014

Question of the Week - 11/1/2014

Question:- Suppose there are two tables A and B and we need to write 3 SQL queries which returns the record set as shown in the below figure.


First Query:-

In this case, you have to write a SQL Query using SQL JOIN which return the data which is not common to…

Read more

9 comments, 274 reads

Posted in Vivek's SQL Notes on 11 January 2014

Answer - Objective Question (In which database temporary tables are created in SQL Server)

Question :- In which database temporary tables are created in SQL Server?

1) User database (where temporary table are defined)
   
2) Master database
   
3) TempDB (Correct Answer)

4) Model Database

5) None of the above

Answer
 Thanks to all who attempted this question. 84%…

Read more

1 comments, 636 reads

Posted in Vivek's SQL Notes on 11 January 2014

Answer -Question of the Week - 2/1/2014 - Question 1

Question :-

Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 

For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee…

Read more

0 comments, 325 reads

Posted in Vivek's SQL Notes on 10 January 2014

Answer - Objective Question (Which table is called Heap table in SQL Server)

Question:-

   Which table is called Heap table in SQL Server.

1) Table without any indexes

2) Table without any clustered index (Correct Answer)

3) Table without any non clustered Index

4) None of the above


Many people attempted this question and mostly gives the correct answer. The…

Read more

0 comments, 258 reads

Posted in Vivek's SQL Notes on 6 January 2014

Question of the Week - 2/1/2014 - Question 1

Question :-

Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee can…

Read more

4 comments, 264 reads

Posted in Vivek's SQL Notes on 2 January 2014

Answer - Objective Question (Number of non clustered indexes possible on a Table in SQL Server 2008R2)

Question :- 

How many non clustered indexes are possible in a table in SQL Server 2008R2


1)  1

2) 249

3) 999  (Correct answer)

4) None of these


Explanation


 21 people gives answer to this Question and most of them is correct.

Correct answer is 999 (Option 4).

In…

Read more

0 comments, 298 reads

Posted in Vivek's SQL Notes on 1 January 2014

Answer - Question of the Week - 28/12/2013 - Question 2

Question:- Suppose we have a table named tbl_charactername have only one column say "charactername".
It contains 10 rows. First 5 rows contains "Vivek Johari" and the next 5 rows contains "Abhinav Golwalkar"

tbl_charactername
---------------------
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar

Read more

0 comments, 183 reads

Posted in Vivek's SQL Notes on 1 January 2014

While loop in SQL server


In While loop, we set a condition for the repeated execution of a SQL Code block or a SQL Statement. The SQL Statement or SQL Block keep on repeatedly executing till the condition in the while loop is satisfied. Once the condition is failed, the repeated execution of the block…

Read more

3 comments, 5,199 reads

Posted in Vivek's SQL Notes on 1 January 2014

Happy New Year 2014



                              HAPPY NEW YEAR 2014



Wishing all of the readers of this blog a very happy New Year. Thanks for supporting me and my blog last year 2013. I hope this support will continue this year…

Read more

0 comments, 135 reads

Posted in Vivek's SQL Notes on 31 December 2013

Deleting Duplicate rows using CTE

Many times we caught in a situation where we have a table where there is no primary or unique column defined in it and rows with duplicate data inserted in the table. In this example I will tries to show how to delete the duplicate rows using CTE.

Suppose we…

Read more

0 comments, 2,105 reads

Posted in Vivek's SQL Notes on 29 December 2013

Answer -Question of the Week - 25/12/2013- Question 1


Question:- Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid  Studentname
1               Vivek Johari
2               Chandra Singh
4…

Read more

1 comments, 197 reads

Posted in Vivek's SQL Notes on 28 December 2013

Question of the Week - 28/12/2013 - Question 2

Question:- Suppose we have a table named tbl_charactername have only one column say "charactername".
It contains 10 rows. First 5 rows contains "Vivek Johari" and the next 5 rows contains "Abhinav Golwalkar"

tbl_charactername
---------------------
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar

Read more

9 comments, 152 reads

Posted in Vivek's SQL Notes on 28 December 2013

Newer posts

Older posts