Interview Questions and answers - SQL Server Day 5

Question: What is the difference between Truncate and Delete in SQL Server?

Answer: If you wish to remove records on rows basis on the condition, then we have to use the DELETE command, while truncate will remove all the rows from a table.

TRUNCATE

• Truncate is a DDL command.
• The truncate command removes all data from the table.
• Truncate does not keep the logs, so it fast then DELETE.
• The truncate statement is not conditional means we cannot use the WHERE clause in the truncate statement.
• Rollback is not possible.
• Truncate reset the table to its empty state. 

DELETE

• Delete is a DML command.
• We can use the WHERE clause in the delete statement.
• Delete statement delete specific rows if where the condition exists.
• Delete command lock the row when executed, each row in the table is locked for deletion.
• The delete operation is logged individually, so delete activates the trigger.
• Rollback is possible in delete.
• Delete keeps a log, so delete is slower than truncate.

Question: What are DML, DDL, DCL, and TCL in SQL- Server?

Answer: 

DML stands for Data Manipulation Language. DML is responsible for retrieving, storing, modifying, delete, insert, and update data in the database. Examples: SELECT, UPDATE, and INSERT statements.

DDL stands for Data Definition Language. DDL is responsible for creating and modifying the structure of database objects in the database. Examples: CREATE, ALTER, and DROP statements.

DCL stands for Data Control Language. DCL is responsible for roles, permissions, and referential integrity and also use to control access to the database by securing it. Examples: GRANT, REVOKE statements.

TCL stands for Transactional Control Language. TCL is responsible for managing different transactions occurring within a database. Examples: COMMIT, ROLLBACK statements.

Question: Write query SELECT, INSERT, DELETE, and UPDATE Statements in SQL Server?

Answer: Suppose we have a table Employee with column 'FNAME', 'LNAME', 'ADDRESS' and CREATEDDATE

SELECT Statement: SELECT * FROM Employee

INSERT Statement:

INSERT INTO Employee (FNAME,LNAME,ADDRESS,CREATEDDATE) VALUES('DILIP','SINGH','DELHI',GETDATE())

INSERT INTO Employee (FNAME,LNAME,ADDRESS,CREATEDDATE) VALUES('VIPUL','BHATT','NOIDA',GETDATE())

INSERT INTO Employee (FNAME,LNAME,ADDRESS,CREATEDDATE) VALUES('RAJ','SINGH','BOKARO',GETDATE())

DELETE Statement: 

DELETE FROM Employee

Note: this query will delete all records of the table.

Delete records with the condition, where clause use for the condition

DELETE FROM Employee WHERE FNAME='DILIP'

UPDATE Statement:

UPDATE Employee SET ADDRESS='GORAKHPUR'

Note: It will update the ADDRESS column with 'GORAKHPUR' of all table row.

Update Statement with condition

UPDATE Employee SET ADDRESS='GORAKHPUR' WHERE FNAME='DILIP'

Question: What is the difference between a HAVING clause and a WHERE clause in SQL Server?

Answer: Only with the SELECT statement we can use the HAVING Clause.  Basically HAVING Clause is used with GROUP BY Clause. Without used of GROUP BY clause, HAVING behaves like a WHERE clause.

Where Clause Example: SELECT empName,empDept,empSalary WHERE empSalary=20000

HAVING Clause Example: SELECT empName,AVG(empSalary) GROUP BY empName,empSalary HAVING AVG(empSalary)>20000

For more click here 

Question: What is the difference between the Primary key and Unique Key in SQL Server?

Answer:

1. The primary key doesn’t have a null value, while a Unique key can hold null value.
2. A table can have only one primary key, while a table can have more than one unique key.
3. By default primary key has clustered index while by default unique key has a non-clustered index

Question: What is the difference between @@IDENTITY, SELECT SCOPE_IDENTITY(), SELECT IDENT_CURRENT in SQL Server?

Answer:

SELECT @@IDENTITY: it’s responsible to returns the last identity value generated for any table in the current session, across all scopes (i.e., global scope).

SELECT SCOPE_IDENTITY(): It’s responsible to returns the last identity value generated for any table in the current session and the current scope(i.e., local scope).

SELECT IDENT_CURRENT(‘table_name’): It’s responsible to returns the last identity value generated for a specific table in any session and any scope (i.e., global scope). For more with an example, click here. 

Question: How to get first & last day of the previous Week in SQL Server?

Answer: the Following query may help you

This query will return the first day of the previous Week

SELECT CONVERT(DATE,DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 1, 0)) First_Day_Of_Prev_Week

This query will return last day of the previous Week

SELECT CONVERT(DATE,DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 1, 0) + 6) Last_Day_OF_Prev_Week