Search This Blog

Q31-Q35

Q31. In a database we have a table. There are 1000 records in that table. employee id, fname, lname,  I want to update the fname to lname and lname to fname. what would be your approach?
Q32. How can you find the duplicate records from a table? let say above table has multiple fname with same value?
Q33. Why we use the no-lock syntax while selecting data from tables? 
Q34. What is difference between Row_Number(), Rank(), Dense_Rank() in sql?
Q35. What is cross apply in SSIS and SQL?

=========================================================================
Q31. In a database we have a table. There are 1000 records in that table. employee id, fname, lname,  I want to update the fname to lname and lname to fname. what would be your approach?

Answer:
one way would be creating a temp table and then run the update query

select * into #temp 
 from Employees

update Employees
 set Employees.lastname = t.firstname, Employees.firstname = t.lastname
 from employees e
 join #temp t on e.employeeID = t.employeeID

=========================================================================
Q32. How can you find the duplicate records from a table? let say above table has multiple fname with same value?

Answer:

SELECT  fname, count(fname)
from employee
group by fname
having count(fname) > 1

=========================================================================
Q33. Why we use the no-lock syntax while selecting data from tables? 

Answer:
1) The NOLOCK allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
2) This can improve query performance, but also introduces the possibility of dirty reads.

Real-life scenario --

let say a user started a transaction but not yet committed 
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

Now if we try below sql. it will be never ending execution as the commit is yet to done.
SELECT * FROM Person.Contact

But in case we use no lock. We might be reading a dirty data but we still able to execute the command. 
SELECT * FROM Person.Contact WITH (NOLOCK)

=========================================================================
Q34. What is difference between Row_Number(), Rank(), Dense_Rank() in sql?

Answer:

Row_Number() will generate a unique number for every row, even if one or more rows has the same value.

RANK() will assign the same number for the row which contains the same value and skips the next number.

DENSE_RANK() will assign the same number for the row which contains the same value without skipping the next number.

for example for below query: 

SELECT name,company, power,
Row_Number() OVER(ORDER BY power DESC) AS Row_Number,
RANK() OVER(ORDER BY power DESC) AS Rank,
DENSE_RANK() OVER(ORDER BY power DESC) AS DENSE_RANK
FROM Cars

=========================================================================



=========================================================================
Posted by

Q26-Q30

Q26. Have you heard about the merge keyword in SQL?
Q27. You know what are transactions in SQL?
Q28. How to get data belong to TableA and TableB but not their command records ie get records from both table which is not common.
Q29. What are indexes in database?
Q30.There are 1000 records in a table. employee id, fname, lname, you have to get the last 5 entries from that table. 

=======================================================================
Q26. Have you heard about the merge keyword in SQL?

Answer:
if there is a Source table and a Target table that are to be merged, then with the help of MERGE statement, all the three operations (INSERT, UPDATE, DELETE) can be performed at once.

=======================================================================
Q27. You know what are transactions in SQL?

Answer:
A transaction is a logical unit of work that contains one or more SQL statements.

Transactions have the following four standard properties, usually referred to by the acronym ACID.
  1. Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
  2. Consistency − ensures that the database properly changes states upon a successfully committed transaction.
  3. Isolation − enables transactions to operate independently of and transparent to each other.
  4. Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

The following commands are used to control transactions.
  1. COMMIT − to save the changes.
  2. ROLLBACK − to roll back the changes.
  3. SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
  4. SET TRANSACTION − Places a name on a transaction.



=======================================================================
Q28. How to get data belong to TableA and TableB but not their command records ie get records from both table which is not common.

Answer:
select * from TableA as A
FULL JOIN from TableB as B on A.value == B.value
where A.value IS NULL or B.value IS NULL;

=======================================================================
Q30.There are 1000 records in a table. employee id, fname, lname, you have to get the last 5 entries from that table. 

Answer:

select top 5 * from Employees order by EmployeeID desc

=======================================================================
Posted by

Q21-Q25

Q21. What is Temporary table in sql server?
Q22. What is the difference between Temporary table and Temporary variable?
Q23. Does indexing occupy any space in memory in sql server? 
Q24. What are cursors. What is the disadvantage of cursors. 
Q25. Difference between varchar and Nvarchar?
=====================================================================
Q21. What is Temp table in sql server?

Answer:
They are used to store any temporary data. They are created with pound(hash) keyword.  In
SQL Server Management Studio you can see at the following path System Databases > tempdb > Temporary Tables 
  • In Temporary Table we can see entry for both ##GlobalTemporaryTable and #TemporaryTable
  • Within current session you can query data from #temp table as many times. With in current session means, for same tab. if you query the #temp in second tab it will not be avaliable. once you close the initial tab, we can not get #temp again. 
  • #temp are auto dropped with end of execution of Stored Procedure. 
  • If you try querying from another connection or infect tab, it will give Invalid object name error. 
  • Global temporary tables are used to access across all connections. it remain active around all tabs. it will be deleted once we close the sql server instance. 
=====================================================================
Q22. What is the difference between Temporary table and Temporary variable?

Answer:

Temporary Tables
1. There are two types of temporary tables. Local temporary table (#table) and Global Temporary table (##table)
2. Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables. 
3. Indexing can be done on Temporary tables. 
4. Not allowed in User Defined functions. 
5. Little slower in performance because they are physical tables

Table Variables:
1. Only single type. 
2. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb. 
3. Indexing can not be created on Table variable. 
4. Allowed in UDF
5. Little faster because they are made in memory tables. 
=====================================================================
Q23. Does indexing occupy any space in memory in sql server?

Answer:
YES
=====================================================================
Q24. What are cursors. What is the disadvantage of cursors. 

Answer:
Cursors are used to fetch the data from a table in a row by row manner. 

Disadvantages of using Cursor: 
  1. Cursor in SQL is temporary work area created in the system memory, thus it occupies memory from your system that may be available for other processes. So occupies more resources and temporary storage. 
  2. Each time when a row is fetched from the cursor it may result in a network round trip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE etc that makes only one round trip. 
  3. Repeated network round trips can degrade the speed of the operation using the cursor.
=====================================================================
Q25. Difference between varchar and Nvarchar?

Answer:
Varchar stores 1 byte each character while Nvarchar stores two bytes. 
Nvarchar is used to store different language characters like german, Chinese etc


=====================================================================
Posted by

Q16-Q20

Q16. What is order of statement execution in SQL?
SELECT DISTINCT <TOP_specification> <select_list>
FROM <left_table>
<join_type> JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_list>

Q17. How you do exception handling in your SQL stored procedures?
Q18. How I can fetch the 10th highest salary in a table?
Q19. What are views. How they are different from Stored procedures. Pros and Cons of using view in SQL?
Q20. How to run Execution plan in sql server? What input you can take from it?

=======================================================================
Q16. What is the order of statement execution in SQL?

Answer:

FROM clause
JOIN..ON clause OUTER clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
DISTINCT clause
ORDER BY clause
TOP clause

=======================================================================
Q17. How you do exception handling in your SQL stored procedures?

Answer:
    We can have TRY-CATCH block in sql SP to handle errors. below is the syntax for placing a try catch block. 
We also make sure the Transaction is rollback in case of error. 

BEGIN TRY
    BEGIN TRANSACTION;
    
    -- Business logic here
    INSERT INTO Orders (...) VALUES (...);
    UPDATE Inventory SET ...;
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    RAISERROR('Error %d: %s', 16, 1, @ErrorNumber, @ErrorMessage);
END CATCH


=======================================================================
Q18. How I can fetch the 10th highest salary in a table?

Answer:
Two ways 
1) using CTE with ROW_NUMBER() and 
2) using sub queries. 

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow
=======================================================================
Q19. Pros and Cons of using view in SQL?

Answer: 
Views are also called virtual tables in SQL. S
Stored procedure usually have better performance because they are pre compiled code for sql server and it will simply return data on execution. but With views you still have to write your sql query to return data as view act as a table in front. 

Advantages:
  1. Security. We make user to see specific columns of tables using View. 
  2. Simplicity. Complex query around multiple table could be convert into sinlge query to a single view. 
  3. Data Integrity. If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.
Disadvantages of views
  1. Performance - Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time.
  2. Update restrictions - When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.
=======================================================================
Q20. How to run Execution plan in sql server? What input you can take from it?

Answer:
Execution plan can be run with the button near "Execute" in SSMS. 
You can check the order of execution of queries from right to left.  We have cost in execution plan. 




Posted by

Q11-Q15

Q11. What is ACID in SQL?
Q12. How to identify slow running queries in SQL. 
Q13. How to increase the performance of your database queries. 
Q14. Things to keep in mind while creating a database architecture?
Q15. What are CTEs. What are the benefits of CTEs?
-----------------------------------------------------------------------------------------------------------------------------
Q11. What is ACID in SQL?

Answer:
ACID -  Atomic, Consistent, Isolation, and Durability

Atomic – Transaction acting on several pieces of information complete only if all pieces successfully save.   Here, “all or nothing” applies to the transaction. The transaction is Atomic. Meaning, the operation to deduct funds from checking cannot complete unless the operation to deposit into savings succeeds. Any interruption cancels the entire transaction.

Consistent – guarantees that a transaction never leaves your database in a half-finished state. In atomic is for all whole transactions. Consistency is for running single transaction

Isolation – No other transactions take place and affect the transaction in process.   This prevents “mid-air collisions. ”Isolation ensures that other banking transactions don’t affect the outcome of your transfer. Other transaction to alter your checking balance must wait until your transaction completes.

Durable – System failures or restarts do not affect committed transactions. Being Durable, once the transaction is saved or committed, it can’t be “lost.” Data won’t go missing after a power outage or system crash.

-----------------------------------------------------------------------------------------------------------------------------
Q12. How to identify slow running queries in SQL. 

Answer:
1. Generate an Execution plan. It is part of SSMS - Sequel server management studio.
2. Monitor Resource usages. If running windows machine for SQL server, enable system monitor tool to get the performance data. 
3. SQL Azure query performance insight. 

-----------------------------------------------------------------------------------------------------------------------------
Q13. How to increase the performance of your database queries. 

Answer:
  1. Use the actual column names in the SQL query instead of selecting all columns (using SELECT *) FROM a table, so that only necessary columns are selected.
  2. Indexing is usually helpful in large tables. In case of small tables it could be burden.
  3. Do not use cursors instead use CTE. common table expression. 
  4. Avoid nchar and nvarchar if possible since both the data types takes just double memory as char and varchar.
  5. Choose appropriate Data Type. E.g. To store strings use varchar in place of text data type. Use text data type, whenever you need to store large data (more than 8000 characters)
  6. use joins instead of sub-queries
  7. Use WITH (NOLOCK) while querying the data from any table.
  8. Use SET NOCOUNT ON and use TRY- CATCH to avoid deadlock condition.
  9. Use Table variable in place of Temp table. Use of Temp tables required interaction with TempDb database which is a time taking task.
  10. Use UNION ALL in place of UNION if possible. UNION removes the duplicate records thus has less performance than UNIONALL
-----------------------------------------------------------------------------------------------------------------------------
Q14. Things to keep in mind while creating a database architecture?

Answer:
  1. Keep a prefect balance between Normalization and denormalization. 
  2. Establish naming conventions before hand. 
  3. Make sure you use constraints (CHECK, NOT NULL, FOREIGN KEY, PRIMARY KEY, and DEFAULT) to ensure that only correct data is stored in the database in the first place. You can always buy faster hardware but you cannot buy more correct data.
  4. Do not use a GUID as your primary key as it can kill performance. If you need a guid for replication also consider having an int or big int primary key.
  5. Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names. This will give issues with collections name in .net like entity framework
  6. Use bit fields for Boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
  7. Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
  8. Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.
--------------------------------------------------------------------------------------------------------------------------------
Q15. What are CTEs. What are the benefits of CTEs?

Answer:
CTE: Common table expression. 
They are of two type 1) recursive and 2) non recursive. 

This SQL CTE is used to generate a temporary named set (like a temporary table) that exists for the duration of a query unlike temporary tables in which data remain accessible through the life of procedure, CTE dies with query completion. 

CTE recursive can be used to solve hierarchy types of problem like boss of A is B and boss of B is C and so on. 

--------------------------------------------------------------------------------------------------------------------------------
Posted by

My Other Blogs

Posted by

Q6-Q10

Q6. Stu_name Varchar(8). What is output of LEN(Stu_name)?
Q7. Different types of Indexes. 
Q8. Maximum number of Indexes we can have in table?
Q9. Difference between clustered index and non-clustered indexes?
Q10. What are magic tables in SQL?
 ------------------------------------------------------------------------------------------------------------------------
Q6. Stu_name Varchar(8). What is output of LEN(Stu_name)?

 Answer:
That will depend on the content available in column Stu_name. 

 example:
 Stu_name Varchar(8) -  Represents that table has a column named 'Stu_name'. which can hold maximum varchar of up to 9 charters. 

 LEN(Stu_name) - Represents the length of string present in Stu_name column. it must be always less than the size of column can hold. 

 ------------------------------------------------------------------------------------------------------------------------
Q7. Different types of Indexes. 

Answer:
There are mainly two types of indexes in MS SQL server. 
  1. Clustered Indexes
  2. Non Clustered Indexes
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15
------------------------------------------------------------------------------------------------------------------------
Q8. Maximum number of Indexes we can have in table?

Answer:
Clustered - 1
Non clustered - 999 was allowed SQL Server 2008 R2 so now with 2016. We do not need to worry around number of non clustered indexs. 
------------------------------------------------------------------------------------------------------------------------
Q9. Difference between clustered index and non-clustered indexes?

Answer:
 clustered index determines how the data is stored in the rows of a table physically. On the other hand, the non-clustered index stores the data at a single place and the indexes are stored at another place. Additionally, each table can have only one clustered index. As against, In case of a non-clustered index, a table can have multiple non-clustered indices

1)  One table can only have only one clustered index.One table can only have many non-clustered index.
2) A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key.A non-clustered index requires separate storage than the table storage to store the index information.
3) The default index created as part of the primary key column is a clustered index.
------------------------------------------------------------------------------------------------------------------------
Q10. What are magic tables in SQL?

Answer:
Magic tables are tables created for insertion, deletion and update internally by server,. In case of new row , a new entry is made in INSERTED magic table. 
In case of deletion of a row, entry is made in DELETED magic table'
In case of update of a row, one new entry is made in DELETED magic table for old data and one new entry is made in INSERTED magic table for new entry. 

Before SQL 2008R2. they were used only in case of triggers but now they are available for all tables. 


Posted by

Q1-Q5

Q1. Can we do data insertion using sql functions?
Q2. What are the Differences between stored procedure and functions?
Q3. Explain cross join in SQL.
Q4. Can we return table from a user defined function? 
Q5. How to get top 3rd or Nth element in table.

 ------------------------------------------------------------------------------------------------------------------------
Q1. Can we do data insertion using sql functions?

 Answer:
User-defined functions cannot be used to perform actions that modify the database state. So Insert, Update, Delete are not allowed in SQL functions.

 ------------------------------------------------------------------------------------------------------------------------
Q2. What are the Differences between stored procedure and functions?

Answer:
1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2. Functions can have only input parameters (could be multiple input parameters) for it whereas Procedures can have input/output parameters.
3. Functions can be called from Procedure whereas Procedures cannot be called from Function.
4. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
5. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
------------------------------------------------------------------------------------------------------------------------
Q3. Explain cross join in SQL.

Answer:

  • Also known as Cartesian join of two tables. 
  • The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.  
  • if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set.

SELECT *  FROM table1
CROSS JOIN table2
where table1.id = table2.fk_id;

is similar ton-->

select * from table1 
inner join table2 
on table1.id = table2.fk_id

------------------------------------------------------------------------------------------------------------------------
Q4. Can we return table from a user defined function? 

 Answer:
Yes, There are three types of function. 

  1. Scalar Function - returns a single value as a result of actions performed by the function. We return any datatype value from a function.
  2. Inline Table Valued Function - The user-defined inline table-valued function returns a table variable as a result of actions performed by the function. The value of the table variable should be derived from a single SELECT statement.
  3. Multi Statement Table Valued Function - A user-defined multi-statement table-valued function returns a table variable as a result of actions performed by the function. In this, a table variable must be explicitly declared and defined whose value can be derived from multiple SQL statements.
------------------------------------------------------------------------------------------------------------------------
Q5. How to get top 3rd or Nth element in table.

 Answer:
Using "row_number" 

 Syntax:

 SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ProjectID ASC) AS RowNo
      , ProjectName 
    FROM Projects1 ) AS FOO

Where FOO.RowNo = 3
------------------------------------------------------------------------------------------------------------------------
Posted by
Subscribe to: Comments (Atom)