Saturday 12 April 2014

Query to display total number of rows for each tables in a database - SQL


Following query displays the total number of rows for each table in a database.


  • Open SSMS
  • Select database
  • Execute the below Query:

             select sysobjects.name 'Table Name',rowcnt 'Total # Rows' from sysindexes 
             inner join  sysobjects on sysindexes.id= sysobjects.id
             where indid < 2
             and sysobjects.xtype='U'
            Order by 'Total # Rows' desc

The above query will return the list of all the tables in a database along with total numbers of rows in it.

Saturday 5 April 2014

Delete duplicate rows using Row_number and CTE in SQL

                   We can use Row_Number to delete duplicate rows from a table, as we know Row_Number function allocates serial number based on a key column.

Consider a table named 'EmpDetails' which has data as below:



As seen in the above screen shot there are duplicate records for EmpId 1,2 and 3

By using CTE and Row_number function we can remove the duplicate records from the above table. The query to remove duplicate values is as follow:

Query:

With CTE as
(select *,ROW_NUMBER() over(partition by empid order by empid) as Row_num from dbo.EmpDetails)

delete from CTE where Row_num > 1

This query will delete the duplicate rows from the EmpDetails table

Now, if we see the data in the EmpDetails Table it will contain only unique values in EmpID column.

Select * from dbo.EmpDetails





Sunday 30 March 2014

Query to display names of all columns and data types in a table - SQL

Following is the query that returns column name and data type in a table in SQL:


Replace the string 'Table Name' with the actual table name

select first.name[Column Name],

case when second.name IN ('char', 'varchar', 'nchar', 'nvarchar') then second.name + 
' (' + convert(nvarchar(10),first.max_length ) + ')' 
when second.name IN ('decimal','numeric') then second.name + ' (' + convert( nvarchar(10),first.precision ) + 
', ' + convert( nvarchar(10),first.scale ) + ')'
else second.name end [Data Type],
case first.is_nullable when 1 then 'Yes' else 'No' end [Allow Nulls]
from sys.columns first
inner join sys.types second on first.system_type_id=second.system_type_id
where object_name(first.object_id)='Table Name'
order by first.column_id


Population by Country - Tableau

Ranking Functions in SQL Server with example

There are four types of Ranking Functions in SQL. All the Ranking Functions are used with Over Clause
  • Row_Number: The row_number function gives each a row in a table a serial number. 
Example: 

Consider below Table named 'EmpDetails', the table contains two columns 'EmpID' and 'EmpName'
  

Now we will implement Row_number to the above table:

Query:

select *, ROW_NUMBER() over (order by empid) as Rowno from dbo.EmpDetails 

The output of the query is as follows:


  • Rank: The Rank function gives rank to each rows in the result based on the over clause.

Example:
select *, Rank() over (order by empid) as Rowno from dbo.EmpDetails 

Ouput:

As seen above, the employees with empid as '1' has been given Rank 1, whereas the employee with empid '2' has rank 3, because there were two employees with empid 1 that has rank one.


  • Dense_Rank: This function is similar to Rank() function, the only difference is that it gives Rank without any gaps.
Example:



select *, dense_Rank() over (order by empid) as Rowno from dbo.EmpDetails 

Output:

If you observe the above result set, dense_rank() function has assigned rank 2 to the employees with Empid 2, unlike Rank() function which ranked them as 3.

  • Ntile: The Ntile function requires to specify a number in the parentheses and based on that the it divides the rows in the result set.
Example:


select *, Ntile(2) over (order by empid) as Rowno from dbo.EmpDetails 

Here, we have specified 2 in the parentheses, hence the result set with contain only two ranks i.e. 1 and 2

Output:

  




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


Sunday 26 January 2014

Stack Chart in Correlated Chart using Tableau

Tableau Sample View 10

Tableau Sample View 9

Connect SSRS with Excel Step by Step

                                                Connect Excel as source to SSRS

Click on create 'Add new data scource'



Choose ODBC as Connection Type and click OK


Choose Excel System Data source name

Select 'Use  Connection String'

Select Excel as 'Machine Data Source' and click on New


Select User Data Source and Click on Next



Select 'Microsoft Excel Driver'  and click on Next



Click On Finish


Give Data Source as Name



Select the Excel file and Click on OK


Tableau Sample View 8

two bar graph in one view - Tableau

Tableau Sample View 7

Tableau Sample View 6

Tableau Sample View 5