SQL interview Questions for Fresher and Experienced developer

Here are detailed answers with explanations and examples for each SQL interview topic, especially suited for experienced candidates:

1. Table Type in SQL

A Table Type is a user-defined data type in SQL Server that can store a result set for later use in functions, procedures, or batch execution.

CREATE TYPE EmployeeType AS TABLE (EmpID INT, EmpName NVARCHAR(50));

2. Non-Clustered Index

A Non-Clustered Index maintains a separate structure with a pointer to the actual data. Unlike a clustered index, it doesn't rearrange the data.

CREATE NONCLUSTERED INDEX idx_empname ON Employees(EmpName);

3. Temp Table

Temporary tables store intermediate data temporarily for further processing. They exist only during the session.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));

4. EDMX for Oracle DB

Yes, EDMX can be generated for Oracle using Entity Framework via third-party providers like Devart dotConnect for Oracle.

5. Difference Between Primary and Unique Key

Primary Key: Cannot contain NULL values and uniquely identifies each record.
Unique Key: Can contain NULL values and also ensures uniqueness.

CREATE TABLE Example (
  ID INT PRIMARY KEY,
  Email NVARCHAR(100) UNIQUE
);


6. Clustered vs. Non-Clustered Index

Clustered Index: Data is physically sorted in the table.

Non-Clustered Index: Stores a pointer to the actual data.

Use Clustered when you query ranges often, and Non-Clustered when you need to search for specific values frequently.

7. Indexes

Indexes speed up data retrieval by providing quick access paths. They can be Clustered or Non-Clustered.

CREATE INDEX idx_name ON Employees(Name);

8. Joins

Joins combine data from two or more tables.

  • Inner Join: Returns matching rows.
  • Left Join: Returns all rows from the left table and matching rows from the right.

SELECT * FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID;

9. Triggers

A Trigger is a stored procedure that automatically runs when an event (like INSERT, UPDATE, DELETE) occurs.

CREATE TRIGGER trg_Audit ON Employees
AFTER INSERT
AS
BEGIN
  INSERT INTO AuditTable VALUES (GETDATE(), 'New Employee Inserted');
END;

10. Subquery

A Subquery is a query within another query.

SELECT EmpName FROM Employees WHERE DeptID = (SELECT DeptID FROM Departments WHERE DeptName = 'HR');

11. Highest Record

To get the highest value (e.g., salary):

SELECT MAX(Salary) FROM Employees;

12. GROUP BY

Groups rows sharing a property and applies aggregate functions like COUNT, SUM, AVG.

SELECT DeptID, COUNT(*) FROM Employees GROUP BY DeptID;

13. SQL Optimization Techniques

  • Indexes: Create appropriate indexes.
  • Query Rewriting: Simplify complex queries.
  • Partitioning: Break large tables into smaller partitions.
  • Avoid SELECT*: Use only required columns.


14. SQL Transaction Syntax
Transactions ensure ACID properties.


BEGIN TRANSACTION;
  UPDATE Employees SET Salary = Salary * 1.1 WHERE DeptID = 3;
COMMIT;

15. Duplicate Records Deletion Query

DELETE FROM Employees
WHERE ID NOT IN (SELECT MIN(ID) FROM Employees GROUP BY EmpName);

16. Normalization

Normalization reduces redundancy and dependency. The most common forms are 1NF, 2NF, and 3NF.


17. Merge in SQL

Used to perform INSERT, UPDATE, or DELETE operations based on conditions.


MERGE INTO Employees AS Target
USING NewEmployees AS Source
ON Target.EmpID = Source.EmpID
WHEN MATCHED THEN UPDATE SET Target.Salary = Source.Salary
WHEN NOT MATCHED THEN INSERT (EmpID, EmpName) VALUES (Source.EmpID, Source.EmpName);

18. Second Highest Salary

SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

19. Nth Highest Salary

SELECT Salary FROM (
  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
  FROM Employees
) AS Ranked
WHERE Rank = 3;

20. Inner Join vs Outer Join

  • Inner Join: Returns only matching rows.
  • Outer Join: Returns all rows from one or both tables, with NULL for missing matches.

21. Table Variable vs Temp Table

  • Table Variable: Exists only in memory.
  • Temp Table: Exists in tempdb.

DECLARE @TableVar TABLE (ID INT);
CREATE TABLE #TempTable (ID INT);

22. Stored Procedure vs Function

  • Stored Procedure: May or may not return a value.
  • Function: Must return a value and can be used in SELECT statements.

CREATE PROCEDURE GetEmployees AS BEGIN SELECT * FROM Employees; END;


CREATE FUNCTION GetEmployeeCount() RETURNS INT AS BEGIN RETURN (SELECT COUNT(*) FROM Employees); END;

23. SQL Isolation Levels

  • Read Uncommitted: Dirty reads allowed.
  • Serializable: Highest isolation, prevents dirty reads and phantom reads.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

24. jQuery Select Without ID

Use class or attribute selectors in jQuery.

$('input[name="username"]').val();

25. Common Table Expressions (CTE)

CTEs simplify complex queries.

WITH EmpCTE AS (
  SELECT EmpID, EmpName, ManagerID FROM Employees
)
SELECT * FROM EmpCTE WHERE ManagerID IS NULL;

26. Performance Tuning

Optimize queries using indexes, query plans, and efficient joins.

27. Cursor

Cursors allow row-by-row processing.

DECLARE emp_cursor CURSOR FOR SELECT EmpName FROM Employees;

28. Rank and Dense Rank

  • Rank: Skips ranks when there’s a tie.
  • Dense Rank: Doesn't skip ranks.

SELECT EmpID, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;

29. Types of Views

  • Simple View: Based on a single table.
  • Complex View: Based on multiple tables or contains aggregates.

30. Subquery vs Correlated Subquery

  • Subquery: Executes independently.
  • Correlated Subquery: Uses values from the outer query.

SELECT EmpName FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

31. Nested Stored Procedures

You can call one stored procedure from another.

EXEC spAnotherProcedure;

32. Nested Transactions

Transactions can be nested, but committing a nested transaction doesn’t affect the outer one.

BEGIN TRANSACTION;
  BEGIN TRANSACTION;
    COMMIT;
  ROLLBACK; -- Rolls back entire transaction

33. Employee Hierarchy

You can use a self-referencing table for employee-boss relationships.


SELECT e1.EmpName, e2.EmpName AS BossName FROM Employees e1 LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmpID;

34. Types of Constraints
    

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.

35. SQL Default Database and DB Users

  • The master database stores system-level information like server configuration and login credentials. The tempdb is used for temporary storage and tables.

Comments

Post a Comment

Popular posts from this blog

Border Radius not working in Outlook Email template

Hi techies, We can have the different ways to search it on google or stack overflow that border radius not working in outlook CSS , how to make image as round or roundrect, oval, circle in outlook,  how to put border radius to image  in outlook email template,  etc. I assure you that at the end of this tech blog you can able to find all the answers related to the Outlook email template and its CSS behavior. Lets get started... Just a little brief that why the border radius is not working in the outlook email template Outlook still support the Vector markup language (VML)  when it comes to support the email template CSS which doesn't support most of the CSS3 or HTML5 features that most of the web browsers do. As Border-radius is a feature of CSS3 so its not is not supported by Microsoft outlook. It means to meet your requirement you have to take reference from VML. Actually, I made it easier for you. Below is the code snippet that will work to solve the problem. Code ...
Read more

C# .Net Core Interview Questions for Freshers and Experienced Developers

  C#, .NetFramework, .Net Core interview Questions For Angular and SQL Interview Questions, click here...! 1. IOC (Inversion of Control) Container in .NET Core The IOC container in .NET Core is responsible for managing object creation and their dependencies. It's used for Dependency Injection (DI), making it easy to manage lifecycles (transient, scoped, singleton). Example of registering a service: public void ConfigureServices(IServiceCollection services) {     services.AddScoped<IMyService, MyService>();  // DI registration } 2. Configure and ConfigureServices - When to Use What ConfigureServices: Used to register services with the dependency injection container. Typically includes services like database context, authentication, and MVC. Configure: Used to define the middleware pipeline (how requests are processed). Example: public void ConfigureServices(IServiceCollection services) {     services.AddControllers();     services.AddDbContex...
Read more

15 ways to keep your team motivated while working remotely ?

Image
Remote teams is a new trend after the Coronavirus pandemic and it changed how the world sees the Work from Home option. Its not only safe but also a cost effective measure which most of the companies are taking. Thus, many companies like salesforce and twitter has already made it as a company policy and option to go office for work. Handling a team is not everyone's cup of tea. It is even more tougher while a team is working remotely. So, here are few ways that make it easy for you to connect with your team efficiently and help you to have a engaged and motivated team.  15 ways to motivate your team while working remotely -  1. Practice Emotional Intelligence 2. Empathize with people 3. Remember the old time which you spent in office and rejoice those moments. 4. Talk about things other than office work. 5. Appreciate people and show gratitude. 6. Help them to solve their problems. 7. Motivate them to do things which they like other than office work 8. Be with them if they nee...
Read more