SQL ADDA

Q- Reverse a string without using reverse function. A-declare @Result varchar (max) declare @string varchar (50) = 'ABHISHEK'  DECLARE @i int         SET @Result=''     SET @i = 1     WHILE @i <= LEN(@string)     begin     SET @Result = SUBSTRING(@string,@i,1)+ @Result     SET @i=@i + 1 end print @Result  

Q- What is the difference between the  WHERE  and  HAVING  clauses? Ans-  When   GROUP BY   is not used, the   WHERE   and   HAVING   clauses are essentially equivalent. However, when  GROUP BY is  used: The  WHERE  clause is used to filter records from a result. The filtering occurs before any groupings are made. The  HAVING  clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

Q- What is a key difference between Truncate and Delete? Ans-  Truncate is used to delete table content and the action can  not  be rolled back, whereas Delete is used to delete one or more rows in the table and  can  be rolled back and in delete we use where clause.

Q- List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably. Ans- ACID (Atomicity, Consistency, Isolation, Durability)  is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows: Atomicity.  Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. Consistency.  The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. Isolation.  The isolation property ensures that the concurrent execution of transactions re...

Q- What will be the result of the query below? Explain your answer and provide a version that behaves correctly. select case when null = null then 'Yup' else 'Nope' end as Result; Ans-This query will actually yield “Nope”, seeming to imply that  null  is not equal to itself! The reason for this is that the proper way to compare a value to  null  in SQL is with the  is  operator, not with  = . Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows: select case when null is null then 'Yup' else 'Nope' end as Result;

Question- Find 4th highest salary of a employee in employee table. Ans - '' select top 1 salary, name from (select distinct top 4 salary, name from employee order by salary DESC) emp order by salary"