It is primarily used for data availability and it has two machines. One machine gives the essential services and the second machine helps in running the service when the main system fails.

The primary system is occasionally monitored to review its work. This monitoring may be conducted by an independent system or a failover computer which is known as a cluster controller. In an occurrence of failure of the main computer, this failover system carries control.

It is a sort of database attack method where malevolent SQL statements are implanted into an entry field of a database so that once it is performed the database is unlocked for an attacker. This method is generally used for attacking Data-Driven Applications to reach sensitive data and accomplish administrative tasks on databases.

For Example: SELECT column_name(s) FROM table_name WHERE condition;

These are the ways through which dynamic SQL can be performed:

Use these parameters to write a query.
Using EXEC.
Using sp_executesql.

• Limiting access to data.
• Making complicated queries simple.
• Providing data independence.
• Delivering different views of the same data.

• Safe Access Sandbox: In this, a user can execute SQL operations like, developing stored procedures, triggers, etc. but can’t have credentials to the memory and is also not able to create files.
• External Access Sandbox: In this, a user can have access to files without having a right to manage the memory allocation.
• Unsafe Access Sandbox: This sandbox holds untrusted codes where a user has memory access.

• Shared locks: These locks permit data to be read-only (Select operations) and stop the data updation when it is in the shared lock.
• Update locks: These locks are applied to resources that are allowed to be updated. There can be a single update lock on a data at a time.
• Exclusive locks: These locks are used to lock data from being altered (INSERT, UPDATE, or DELETE) by one transaction, therefore, ensuring that numerous updates can’t be made to a similar resource at the same time.
• Intent locks: It is a notification mechanism that uses transaction conveys that plan to acquire a lock on data.
• Schema locks: It is used for operations when the schema or structure of the database is needed to be updated.
• Bulk Update locks: It is used for bulk operations when the TABLOCK hint is used.

Relations or connections between entities that anyhow relate with each other. Relationships are defined as the link between the tables in a database.

There are various relationships, namely:

• One-to-One Relationship.
• One-to-Many Relationship.
• Many-to-One Relationship.
• Self-Referencing Relationship.

• Implicitly by omitting column from column list.
• Explicitly by specifying NULL keyword in the VALUES clause

Views in SQL are sore of virtual tables. A view contains rows and columns similar to a real table in the database. We can make a view by specifying fields from one or more tables available in the database. A View can either have all the rows of a table or specific rows depending on a specific condition.

The CREATE VIEW statement of SQL is used for making Views.

Basic Syntax:

CREATE VIEW view_name AS
SELECT column1, column2…..
FROM table_name
WHERE condition;

view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows

• In RDBMS we have connections between the tables of the database. Whereas in DBMS there is no connection between the tables.
• RDBMS has primary keys and data is reserved in tables. DBMS has no idea of primary keys with data held in navigational or hierarchical form.
• RDBMS determines integrity constraints to follow ACID properties whereas DBMS doesn’t follow ACID properties.

SQL Server has an element for sending mails. Stored procedures can also be utilized for sending mail on request. With SQL Server 2005, the MAPI client is not required for sending mails.

Ensure that the SQL Server Mail account is configured perfectly and Database Mail is enabled.

Follow the below-given script to send an e-mail.
USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
abc@intellipaat.com;pqr@intellipaat.com’
@body = ‘ A warm wish for your future endeavor’,
@subject = ‘This mail was sent using Database Mail’ ;

• DDL – Data Definition Language
• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL – Transactional Control Language

• Click on Surface Area Configuration for Services and Connections.
• Click on SQLEXPRESS/Database Engine/RemoteConnections.
• Select the radio button: Local and Remote Connections and select ‘Using TCP/IP only’ under Local and Remote Connections.
• Click on the OK button / Apply button

• A null value is an area with no value.
• Zero is a number
• Blank space is the value we give. The ASCII value of space is CHAR(32).

• Atomicity
• Consistency
• Isolation
• Durability

Correlated subquery: These queries select the data from a table referenced in the external query. It is not viewed as a liberated query as it guides to another table and refers to the column in a table.

Non-Correlated subquery: This query is a liberated query where the output of the subquery is replaced in the main query.

Join keyword is used to bring data from related two or more tables. It yields rows where there is a minimum of one match in both the tables contained in the join.

Types of joins are:

• Right Join
• Outer Join
• Full Join
• Cross Join
• Self Join

System Privilege: System privileges deal with an object of a special type and determine the right to execute one or more actions on it in which Admin authorizes a user to execute administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP creates/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW, etc.

Object Privilege: This permits executing actions on an object or object of other users (s) viz. table, view, indexes, etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES, etc.

• String1 – String to be overwritten
• Position – Starting location for overwriting
• Length – Length of substitute string
• String2- String to overwrite.

• Database Connectivity
• Required Application Field and its size
• Constraint Check
• Stored Procedures
• Data Retrieval and Processing With DML operations
• Functional flow

• Efficient data access
• Better Database organization
• Greater Flexibility for Queries
• More Tables with smaller rows
• Easier to implement Security
• Quickly find the information
• Reduction of redundant and duplicate data
• Allows easy modification
• Ensure Consistent data after modification
• More Compact Database

Subquery within another subquery is known as Nested Subquery. If the result of a subquery is based on column values of the parent query table then the query is known as Correlated Subquery.

SELECT admin(SELEC Firstname+’ ‘+Lastname FROM Employee WHERE
empid=emp. admin)AS EmpAdminId FROM Employee

This query gets details of an employee from the Employee table.

First Normal Form (1NF): It deletes all duplicate columns from the table. Makes a table for related data and recognizes unique column values

Second Normal Form (2NF): Follows 1NF and forms and places data subsets in a particular table and describes the relationship between tables using the primary key

Third Normal Form (3NF): Follows 2NF and deletes those columns which are not related through the primary key

Fourth Normal Form (4NF): Follows 3NF and does not describe multi-valued dependencies. 4NF is also called BCNF

SQL Aggregate Functions computes values from numerous columns in a table and returns a single value.

There are seven aggregate functions we use in SQL:

• AVG(): Returns the average value from specified columns
• COUNT(): Returns number of table rows
• MAX(): Returns largest value among the records
• MIN(): Returns smallest value among the records
• SUM(): Returns the sum of specified column values
• FIRST(): Returns the first value
• LAST(): Returns Last value

• Each column should have a special name
• The series of columns and rows in relational databases are insignificant
• All values are atomic and each row is special

A cursor is a database object which is used to influence data in a row-to-row manner.

The cursor follows phases as given below

• Declare Cursor
• Open Cursor
• Retrieve row from the Cursor
• Process the row
• Close Cursor
• Deallocate Cursor