Most commonly asked SQL interview questions to Data Scientists

Difference between tables and views

Table: Table is a preliminary storage for storing data and information in RDBMS. A table is a collection of related data entries and it consists of columns and rows.

View: A view is a virtual table whose contents are defined by a query. Unless indexed, a view does not exist as a stored set of data values in a database. Advantages over table are

• We can combine columns/rows from multiple table or another view and have a consolidated view.
• Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view
• It acts as abstract layer to downstream systems, so any change in schema is not exposed and hence the downstream systems doesn't get affected

Difference between functions and stored procs

Stored Procedures

Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved, which executes (compiled code) whenever it is called.

Functions

A function is compiled and executed every time whenever it is called. A function must return a value and cannot modify the data received as parameters.

Basic Differences between Stored Procedure and Function in SQL Server

• The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.
• Functions can have only input parameters for it whereas Procedures can have input or output parameters.
• Functions can be called from Procedure whereas Procedures cannot be called from a Function.

SQL execution plan

Estimated and actual execution plans

There are two types of execution plans:

Estimated execution plan: Estimated plans provide an estimation of the work that SQL server is expected to perform to get the data.

Actual execution plan: Actual execution plans are generated after the Transact-SQL queries or the batches are executed. Because of this, an actual execution plan contains runtime information, such as the actual resource usage metrics and any runtime warnings.

Search Post

Label :