Ans: Windows authentication mode allows users to connect using Windows domain like NT/2000 account. The access to the SQL server is controlled by Windows NT/2000 account or group used when logged in. this means that Windows username and password are used to access the database server.
Mixed mode authentication: - Mixed mode allows use of Windows credentials and the local SQL server account.

Ans: SQL Server supports two security (authentication) modes:
Windows Authentication and Mixed mode
Windows Authentication mode connects to an instance of SQL Server through a Windows NT 4.0 or Windows 2000 user account.
Mixed mode (Windows Authentication and SQL Server Authentication) connect to an instance of SQL Server by using either Windows Authentication or SQL Server Authentication.
Users who connect through a Windows NT or 2000 user account can make use of trusted connections in either Windows Authentication mode or mixed mode.

Ans:
Temporary Stored Procedure
Temporary Stored Procedure is stored in TempDB database.
It is volatile and is deleted once connection gets terminated or server is restarted.
Temporary stored procedures are prefixed with pound sign #.
One pound sign means that it is temporary within the session.
Two pound signs ## means it is a global temporary procedure.
Global temporary procedure can be called by any connection to the SQL server during its lifetime.
Extended Stored Procedure
It is basically created to expand features of stored procedure.
It uses external program and compiles as DLL. Mostly xp_ prefix is used as naming convention for extended stored procedure.

Ans: Cursors behavior can be controlled by dividing them into cursor types: forward-only, static, keyset-driven, and dynamic. SQL server supports keywords SCROLL and INSENSITIVE to define cursor behavior in terms of scroll ability and sensitivity.

Ans:
Capabilities of cursors:
Cursor reads every row one by one.
Cursors can be used to update a set of rows or a single specific row in a resultset
Cursors can be positioned to specific rows.
Cursors can be parameterized and hence are flexible.
Cursors lock row(s) while updating them.

Ans: A correlated sub query is a type of sub query. Unlike sub query, a correlated sub query is dependent upon the outer query, which returns a value. When a reference to the outer query is found in the sub query, then the outer query will execute first, and the result will be returned to the correlated sub query. The sub query is executed alter for every row that is selected in the outer query.

Ans: Views can be created referencing tables and views only in the current database.
A view name must not be the same as any table owned by that user.
You can build views on other views and on procedures that reference views.
Rules or DEFAULT definitions can't be associated with views.
Only INSTEAD OF triggers can be associated with views.
The query that defines the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
You cannot define full-text index definitions for views.
You cannot create temporary views
You cannot create views on temporary tables.

Ans: Aggregate Functions return a single value by operating against a group of values.
Scalar functions operate against a single value.

Ans: A cursor is a variable that represents an area where the result of a query is stored. A single row query is stored in a cursor called ‘implicit cursor’. If a relation that fetches multiple rows, the cursor is called explicit cursor. An explicit cursor is handled in a PL/SQL block and each row can be handled by putting the relation fetched by the cursor, in a loop.

Ans:
UNION is a set operator combines the relations of 2 or more select statements into one relation. Each select statement of UNION operator must have same number of columns with similar data types. Also the columns in both the select statements must in the same order. UNION selects only distinct values.
UNION ALL is a set operator performs the same functionality that of UNION, with one exception – allowing duplicates values in the relations.