Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Saturday, July 31, 2010

What is difference Truncate Vs Delete?


In T-SQL there are two ways in which you can delete records, one using a DELETE statement and the other using TRUNCATE statement. Though the both the statements are same in the task carried out by them, yet there are significant differences between both the statements.

                    DELETE Statement
                                           TRUNCATE Statement
Removes rows one at a time and records an entry in the transaction log for each deleted row
Removes the data by de-allocating the data pages used to store the table data and records only the page de-allocations in the transaction log
Log of the rows deleted is maintained in a transaction
Log of the data page references is maintained in the transaction
Execution slow compared to Truncate statement due to row by row deletion
Execution is fast as only the reference to the data pages is deleted
Conditional removal of rows is possible
Conditional removal of rows is not possible
Identity seed is not reset
Identity seed is reset
Executed using a row lock, each row in the table is locked for deletion
Locks the table and page but not each row
DML Query
DDL Query



However DELETE and TRUNCATE Statements cannot be used when a table, 
  • is referenced by a foreign key constraint
  • participates in an indexed view

Friday, July 30, 2010

SQL query for identifying Duplicates


Identifying duplicates is quite a cumbersome task when it comes to large databases. There are many ways in which one can identify duplicates present in a table. One of the popular ways of identifying duplicates is by grouping the data and counting the number of records for that group. To explain this, consider the table fact table below.



Employee table

Employee name
Company
Salary
Designation
Adam
ABC Corp
10000
Senior tester
Bill
IJK Corp
20000
Test Engg
Adam
PQR Corp
30000
Developer
Dave
TUV Corp
40000
Manager
Adam
XYZ Corp
50000
Lead


Here we can notice that Employee Name Adam is repeated several times. In order to identify the duplicate records, the following query can be helpful
SELECT E.[Employee Name], Company, Salary, Designation FROM EmployeeTable E INNER JOIN
(SELECT [Employee Name],
COUNT([Employee Name]) AS [Number of Occurances] FROM EmployeeTable GROUP BY [Employee Name] HAVING COUNT([Employee Name]) > 1)
AS A ON A.[Employee Name] = E.[Employee Name]


There are few Ranking functions that are made available in SQL Server 2005 onwards which makes identifying Duplicates more easier, consider the query below:

SELECT [Employee Name], Company, Salary, Designation, ROW_NUMBER() OVER (PARTITION BY [Employee Name] ORDER BY [Employee Name]) AS [Row Id] FROM EmployeeTable
In the above query, the derived column [Row Id], will have the rank allotted to each row and if there are more than one occurrences of a row, the ROW_NUMBER() function will increment the rank of the row, by which one can identify the number of times the row is repeated.

Sunday, July 25, 2010

Write a SQL query that displays relation between parent and child from the following table

Geography table
SKGeographyID
Geography
SKParentGeographyID
1 
Europe 
         NULL
2 
America 
         NULL
3 
Undisclosed 
         NULL
4 
Hong Kong 
          1
5 
New Zealand
          2
6 
Singapore 
          1
7 
Malaysia
          2
8 
Japan 
          3 
9 
Brazil
          3 

 
Select A.Geography, (SELECT Geography
FROM DW.DimGeography B WHERE B.SkGeographyId = A.SkParentGeographyId ) FROM DW.DimGeography A

Saturday, July 24, 2010

Write a SQL query that display 4th max salary from the following table

Employee table
Employee Name 
Salary 
Emp 1 
10000 
Emp 2 
13000 
Emp3 
12000 
Emp 4 
14000 
Emp 5 
15000 
 
select min (Salary) from DW.Dimemployee  where Salary in (select top 4 Salary from DW.Dimemployee order by Salary)

Write a SQL query which displays geography names where Value for same month data more than 70 from the following table

Geography table
Geography Name 
Period
Value 
Korea 
2009-04-30  
10 
Spain 
2009-06-30 
20 
Sweden 
2009-04-28
30 
Switzerland 
2009-06-30 
40
Taiwan 
2009-08-31 
70 
Thailand 
2009-04-16
100 
Sweden 
2009-08-13
120 


select
Geography,right(convert(varchar(30), Period,103),7),SUM(value) from DW.FactGeographyExposure                                                                                                                                                            group by Geography,right(convert(varchar(30), Period,103),7) having sum(value)>0

Which of the following correctly describes how to specify a column alias?



a.       Place the alias at the beginning of the statement to describe the table.
b.       Place the alias after each column, separated by white space, to describe the column.
c.        Place the alias after each column, separated by a comma, to describe the column.
d.       Place the alias at the end of the statement to describe the table.

Correct answer C.


 

Write a SQL query to display the names of all the employee whose slary is either less than 13000 or don’t have any salary from the following tables --


              Employee table                                                        Salary table
                                                 
EmployeeID
EmployeeName
1
Vinit
2
Deepak
3
Ram
4
Sudeep
5
Ajay
EmployeeID
EmployeeSalary
1
10000
2
12000
3
15000
4



                                                                                                                                                                 

Select EmployeeName, Salary  from Employee
left outer  join Salary on Employee.EmployeeID=Salary.EmployeeID
where isnull(salary,0) <13000


All Rights Reserved @ Raju Das