Sunday 16 February 2014

Update statement in SQL

Update statement in SQL


Update statement in SQL is generally to update a value in a table. We should generally be very conscious while using this statement. If you do not specify where clause in update statement all the records in the specified column will be updated with the mentioned value and the entire column will contain only one value

Consider below table 'test'


If we use a Update statement as below without any where condition then all the values fname will be set to 'ABCD' i.e. all the four rows in the fname column will have 'ABCD' as value

update table test
set fname = 'ABCD'

Hence, it is always recommended that you specify a where condition which fulfill all the criteria, as below

update table test
set fname = 'ABCD' where lname = 'abc1'

Here, only the row which has 'abc1' as lname will be updated

We can also update multiple columns in a single update statement as below

Update table tablename
Set Column1= '', Column2='',......


how to add a column to an existing table in sql

Add a column in an already existing table in SQL


To add a column in an already existing table, you will need to alter the existing schema of that table. To achieve that we will have to use ALTER statement.

Consider a table named 'Test' which is as below:


Suppose we need to add another column called 'Ids' which is an identity column and give a unique number to each column, you can easily achieve this using the below query

alter table dbo.Input
add ids bigint identity(1,1) 

Select INTO in SQL

Select INTO in SQL


Select into is used to make a new table which will contain the data that is the result set of the query in which it is used.

For Example:

Consider a name named 'Test' structure of which is as below


Now, we will try this with a simple query as below

Select * into test123 from test

a new table named 'test123' will be created in the same database in which the 'test' table exists. The test123 table will be exactly like the test table. so both table will have the same schema and same data in it.

Now, we will add condition to the above query as follows:

Select fname,lname into test123 from test where lname like 'abc'

In this  case, a table will be created with name 'test123' which will have only two column fname and lname and also only two rows from the table test will be copied into test123 which satisfies the condition of lname like 'abc'

Select Into is generally used to make a backup of a particular data set.






Saturday 15 February 2014

T- SQL Interview Questions and Answer

T- SQL Interview Questions and Answer

  1. Different type of statements in SQL?
  2. What's the use of INTO keyword in Select statement in SQL?
  3. How do you add a column in an already existing table in SQL?
  4. How to update a value in a column in SQL?
  5. Can you rename an object in SQL?
  6. What is identity in SQL and whats the use of it?
  7. How to reset an identity?
  8. Difference between having and where clause?
  9. What data types have you used to setup a table?
  10. Whats the different between nvarchar and varchar?
  11. Difference between delete and truncate command ?
  12. Difference between truncate and drop command?
  13. What are joins in sql and explain different types of Joins?
  14. Difference between Union and Union All?
  15. What are Indexes?
  16. Different Types of indexes?
  17. Difference between clustered and non - clustered indexes?
  18. What are Views in SQL?
  19. What is CTE in SQL?
  20. Difference between CTE and Views?
  21. What is temp table?
  22. What is temporary variable?
  23. Difference between temp table and temp variable?
  24. Difference between temp table and CTE?
  25. What is Sub-Query in SQL?
  26. What is Nested query in SQL?
  27. What is inline Query in SQL?
  28. What are ranking functions in SQL?
  29. Difference between Rank and Dense Rank?
  30. Which Ranking functions is used to delete duplicate rows from a table? 
  31. what is store procedure?
  32. what is UDF in SQL?
  33. difference between a store procedure and function in SQL?
  34. whats the use of NO COUNT?
  35. What is charindex function used for in SQL?
  36. What is substring function used for in SQL?
  37. What is coalesce function in SQL?
  38. difference between coalesce and ISNULL function?
  39. what is datediff function?
  40. what is dateadd function?
  41. explain functionality of Case function?
  42. how to use Case function in Select statement?
  43. Write a query to generate Running Total?
  44. Write a query to generate Running Average Values?
  45. What is BCP in SQL?







Sunday 9 February 2014

Stack Chart in SSRS

Stack Chart in SSRS 2008


For stack chart we will try to show Expense, online profit and offline profit according to locales in SSRS

Stack Chart in SSRS 2008, SSRS basics
  • Drag n Drop charts from the toolbox to the report
  • select stack chart from the options
  • In values section, select expenses, online revenue and offline revenue and select locale in category
Now my main focus should be on expenses but since the number is too low it is not quite visible in stack chart, hence here I will have to use calculate vertical axis as logarithmic, to do show execute following steps


  • Right click on vertical axis and select vertical axis property
  • Select "Use logarithmic Scale" and click on OK. You will observe that know the vertical axis is calculated logarithmic-ally
  • Click on preview window and preview the chart




      

3D Pie chart in SSRS

                              3D Pie chart in SSRS


Here we will show Page Views according to Locales in Pie Chart

The locale column contains the locale names and page views column contains the respective number of page views 

  • Add a report and map the data set with report
  • From the toolbox drag n drop chart to the reports 

  • Select Exploded Pie chart

  • Enter Page Views in Values Section and locale in series section

  • Right Click and select show data labels
  • Select data labels and from the properties window set position property to Outside, so that the labels are displayed outside the pie chart


  • Right click and select chart properties
  • In the chart properties, select 3D display option and check the Enable 3D option

  • Click on preview window and view the chart



Saturday 1 February 2014

Pie Chart in Tableau

                                                            Pie Chart in Tableau

Arrange your data as below:


Open tableau workbook and connect to the data source

Drag and Drop date column into the Rows section


Drag and Drop Sections to Color field

 Drag and Drop Count to Size fields and your Pie cart will be ready
Download the sample Pie chart Tableau workbook from below