Q41. What are the various return types of stored procedure in SQL?
Q42. What are the difference between Delete and Truncate?
Q43. Write a sql query to remove duplicate records from table?
Q44. What are aggregate functions in sql?
Q45. How we can update View in sql?
==============================================================================
Q41. What are the various return types of stored procedure in SQL?
Answer:
==============================================================================
Q42. What are the difference between Delete and Truncate?
Answer:
Delete
1. It can be used to delete specific data.
2. It locks the table row before deleting it.
3. We can rollback the changes.
4. It is slower than truncate
5. Indexes doesnt get reset.
6. Triggers can get fired.
Truncate
1. Only used to delete whole table.
2. It locks the entire table before deleting it.
3. We can not rollback the changes.
4. It is faster than Delete as it make less use of transaction log and it does not worry about rollback point.
5. Indexes got reset.
6. Triggers do not get fired on Truncate.
==============================================================================
Q43. Write a sql query to remove duplicate records from table?
Answer:
So we have to delete only duplicate means one entry must still remain intable.
By Max we will get max of duplicate entries. So we have to delete all enteries which are not part of max (id). in case of single entry it will become part of max(id) as well.
2 3 4 5 6 7 8 9 | DELETE FROM [SampleDB].[dbo].[Employee] WHERE ID NOT IN ( SELECT MAX(ID) AS MaxRecordID FROM [SampleDB].[dbo].[Employee] GROUP BY [FirstName], [LastName], [Country] ); |
==============================================================================
Q44. What are aggregate functions in sql?
Answer:
Below are the list of aggregate functions in sql
1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()
An aggregate function performs a calculation one or more values and returns a single value. The aggregate function is often used with the GROUP BY clause and HAVING clause of the SELECT statement.
==============================================================================
Q45. How we can update View in sql?
Answer:
We can update view just like table.
UPDATE < view_name > SET<column1>=<value1>,<column2>=<value2>,..... WHERE <condition>;
==============================================================================