Before going through SQL Server Interview Questions and Answers Day – 3, I would like to recommend you to go through previous article :
SQL Server Interview Questions and Answers Day – 1
SQL Server Interview Question and Answers Day -2
What is View ?
View is nothing but a Virtual table which is used the retrieve data from one table or multiple tables. There is no physical existence view in a database. Basically view is used to create a subset of a table or multiple tables.
View is used to summarize data from various tables which can be used to generate reports.
What is Stored Procedure?
Stored Procedure is a database object enables the programmer to simply call the stored procedure as a function instead of repeatedly executing the statements inside the stored procedure. Stored procedures are a type of function that contain potentially large grouping of SQL statements which in important for the professional database programmer.
What is trigger?
It is a special type of stored procedure that can be executed in response to one to the three conditions:
• An Update
• An Insert
• A Delete
Triggers are most useful to enforce referential integrity during creation and maintenance of database tables. The actions executed within a trigger are implicitly executed as part of a transaction. All the operation done through trigger like insert, update or delete. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS(Database Management System) automatically fires the trigger as a result of a data modification to the associated table.
What is Cursor?
A cursor is a Database object which is declare within a compound statement. We use cursors to process a table’s data row-by-row. In other words a cursor is used by applications to manipulated data in a set one row at a time.
In order to work with a cursor, we need to perform some steps which are as follows:
• Declare Cursor
• Open cursor
• Fetch row from the Cursor
• Process fetched row
• Close cursor
• Deallocate cursor
What is Functions in SQL?
A function is a database object which is a set of SQL statements that accepts only input parameters, do the actions and return the result as per the input given. Function can return only a single value. Function can use only for select statements.
What is Data Types in SQL?
In SQL Data Types is an attribute that defines the type of data of any object used in SQL. In SQL every column, variable and expression has their own related data type.
We can use the data type during creation of table as per our requirement.
SQL provide six categories of data type for our use:
• Exact Numeric Data Type
• Approximate Numeric Data Types
• Date and Time Data Types
• Character String Data Types
• Unicode Character Strings Data Types
• Binary Data Types
• Misc Data Types
What is an Operator in SQL?
An operator is reserved word or a character used primarily in an SQL statement’s WHERE clause to perform operation(s), such as comparisons and arithmetic operations.
The following Operators are used to specify condition in an SQL statements and to provide conjunctions for multiple conditions in a statements.
• Arithmetic operators
• Comparison operators
• Logical operators
• Operators used to negate conditions
What do you mean by SQL Expression?
An expression is a combination of one ir more values, operators and SQL functions that evaluate to a value. These SQL EXPRESSIONs are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.
There are following types of SQL expressions:
• Boolean expression fetch the data based on matching a single value.
Select col1, clo2 from tbl_Name where single_value_matching_expression;
• Numeric Expression are used to perform any mathematical operation in any query.
Select numerical_expression as OPERATION_Name[/su_note]
Like Select (25+30) as Addition
• Date : expression return the current system date and time values:
What is SQL Constraints?
Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be either on a column level or a table level. The column level constrains are applied only to one column, whereas the table level constraints are applied to whole table.
The most commonly used constraints are following:
• [su_highlight]NOT NULL CONSTRAINST:[/su_highlight] Ensure that column cannot have a NULL value.
• [su_highlight]DEFAULT CONSTRAINST:[/su_highlight] Provides a default value for a column when non is specified
• [su_highlight]UNIQUE CONSTRAINT:[/su_highlight] Ensure that all values in a column are different
• [su_highlight]PRIMARY Key:[/su_highlight] Uniquely identifies each row / record in a database table.
• [su_highlight]FOREIGH Key:[/su_highlight] Uniquely identifies row / record in any of the given database tables.
• [su_highlight]CHECK CONSTRAINT:[/su_highlight] The CHECK constraint ensures that all the values in a column satisfy certain conditions.
• [su_highlight]INDEX:[/su_highlight] used to create and retrieve data from the database very quickly.
What is Wildcard Operators?
In SQL we used and operator which is used to compare a value to similar values.
SQL supports two wildcard operators in conjunction with the like Operator which are as follows:
• The percent sign(%) used to match one or more characters
• The underscore (_) used to match one character.