Posts

SQL Queries

 --1. Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table. --select * from EmployeeDetails e inner join EmployeeDetails m on e.EmpId = m.ManagerId --2. Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId). --select *, ROW_NUMBER() over (partition by [FullName], [DateOfJoining], [City] order by FullName) as rownum from EmployeeDetails --select [FullName], [DateOfJoining], [City],count(*) from EmployeeDetails group by [FullName], [DateOfJoining], [City] HAVING COUNT(*) > 1; --3. Write an SQL query to remove duplicates from a table without using a temporary table. --WITH cteRowNum AS  --(select *, ROW_NUMBER() over (partition by [FullName], [DateOfJoining], [City] order by FullName) as rownum from EmployeeDetails) --delete from cteRowNum where rownum >1 --4. Write an SQL query to fetch only odd rows from the table. --WITH cteRowNum AS  --(select *, ROW_NUMBER() ov...
Post a Comment
Read more

Cursor

Image
  Cursor in SQL Server A cursor in SQL Server is a d atabase object that allows us to retrieve each row at a time and manipulate its data . A cursor is nothing more than a pointer to a row. It's always used in conjunction with a SELECT statement. It is usually a collection of  SQL  logic that loops through a predetermined number of rows one by one. A simple illustration of the cursor is when we have an extensive database of worker's records and want to calculate each worker's salary after deducting taxes and leaves. The  SQL Server   cursor's purpose is to update the data row by row, change it, or perform calculations that are not possible when we retrieve all records at once . It's also useful for performing administrative tasks like SQL Server database backups in sequential order. Cursors are mainly used in the development, DBA, and ETL processes. This article explains everything about SQL Server cursor, such as cursor life cycle, why and when the cursor is us...
Post a Comment
Read more

Index

1. What is Index https://csharp-video-tutorials.blogspot.com/2013/08/part-35-indexes.html 2. Clustered and non-clustered Index   Sql server, .net and c# video tutorial: Part 36 - Clustered & NonClustered indexes (csharp-video-tutorials.blogspot.com)
Post a Comment
Read more

Top SQL Interview Questions

 Q1: Define a Temp Table Q2: What is a VIEW? Q3: What is PRIMARY KEY? Q4: What is Normalization? Related To: Databases Q5: What is DEFAULT? Q6: What is FOREIGN KEY? Q7: What is the difference between TRUNCATE and DELETE? Related To: MySQL Q8: What is the difference between Data Definition Language (DDL) and Data Manipulation Language (DML)? Related To: MySQL, T-SQL, Databases Q9: Define ACID Properties Related To: Databases Q10: Discuss INNER JOIN ON VS WHERE clause (with multiple FROM tables) Q11: What is the difference between INNER JOIN and OUTER JOIN? Q12: What is the difference between JOIN and UNION? Q13: What is the difference between UNION and UNION ALL? Q14: What is the difference between WHERE clause and HAVING clause? Q15: What is the difference between INNER JOIN, OUTER JOIN, FULL OUTER JOIN? Q16: Describe the difference between truncate and delete? Q17: What is Denormalization? Related To: Databases Q18: What is the difference between Clustered and a Non-clustered inde...
Post a Comment
Read more