Object Relational Mapping (ORM)

(ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm

An ORM library is a completely ordinary library written in your language of choice that encapsulates the code needed to manipulate the data, so you don't use SQL anymore; you interact directly with an object in the same language you're using

E.G.

book_list = new List();

sql = "SELECT book FROM library WHERE author = 'Linus'";

data = query(sql); // I over simplify ...

while (row = data.next())

{

     book = new Book();

     book.setAuthor(row.get('author');

     book_list.add(book);

}

With an ORM library, it would look like this:

book_list = BookTable.query(author="Linus");

Disadvantage of ORM

·         ORM libraries are not lightweight tools;

·         Performance is OK for usual queries, but a SQL master will always do better with his own SQL for big projects.

Example of ORM

Entity Framework

SQL Theory Interview Questions

1. Advantages and Disadvantages of Stored Procedure

Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required.

This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

2. What are different SQL statement

There are 3 types of SQL statements

1) DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.

Some of the DDL Commands are listed below

CREATE: It is used for creating the table.

CREATE TABLE table_name

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

ALTER: The ALTER table is used for modifying the existing table object in the database.

ALTER TABLE table_name

ADD column_name datatype

ALTER TABLE table_name

DROP COLUMN column_name

2) DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.

The Select statement is used as partial DML statement that is used to select all or relevant records in the table.

3) DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific user.

What are different JOINS used in SQL?

·         Right Join

·         Outer Join

·         Full Join

·         Cross Join

·         Self Join

3. What is SQL Injection?

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases.

4. What is the difference between DELETE and TRUNCATE?

The basic difference in both is DELETE is DML command and TRUNCATE is DDL

DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table

We can use DELETE with WHERE clause but cannot use TRUNCATE with it

5. What is the difference between DROP and TRUNCATE?

TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.

6. What is Trigger?

Ans. Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)

A SQL trigger is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

7. Advantages of using SQL triggers

SQL triggers provide an alternative way to check the integrity of data.

SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked automatically before or after a change is made to the data in the tables.

SQL triggers are very useful to audit the changes of data in tables.

Types of Trigger

DDL Triggers

In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.

DML Triggers

In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types

After Trigger (using FOR/AFTER CLAUSE)

This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.

Instead of Trigger (using INSTEAD OF CLAUSE)

This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

8. What are the properties of a transaction?

ACID (an acronym for Atomicity, Consistency Isolation, Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all these four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

SQL

• Normalization
• View
• Index
• SQL Theory Interview Questions
• SQL Queries Interview Questions

Index - SQL

Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements.

Single Column index

A single-column index is created based on only one table column. The basic syntax is as follows.

E.G.

CREATE INDEX index_name

ON table_name (column_name);

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.

E.G.

CREATE UNIQUE INDEX index_name

on table_name (column_name);

Composite Indexes

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

E.G.

CREATE INDEX index_name

on table_name (column1, column2);

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Drop Index

DROP INDEX index_name;

Clustered Index.

A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

E.G.

CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score

ON student(gender ASC, total_score DESC)

NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data.

A non-clustered index doesn’t sort the physical data inside the table.

In fact, a non-clustered index is stored at one place and table data is stored in another place.

This is similar to a textbook where the book content is located in one place and the index is located in another.

This allows for more than one non-clustered index per table.

Each table can have 999 nonclustered indexes.

E.G.

CREATE NONCLUSTERED INDEX IX_tblStudent_Name

ON student(name ASC)

Differences between clustered and non-clustered indexes.

There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.

Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.

Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.

View - SQL

·         A view is the result set of a stored pre-established which stored in the database dictionary.

·         It is a virtual table.

·         When the view is requested, it computed data dynamically from the database.

·         A view really doesn't do anything until you query it.

·         Views can hide the complexity of data.

·         Using view, we can display customized data as a virtual table from various physical tables.

·         Views take very little space to store, the database contains only the definition of a view, not a copy of all the data that it presents.

Limitations

·         Since a view is a virtual table – columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again.

·         The select statement on the view cannot contain ORDER BY or INTO TEMP.

·         When a table or view is dropped, any views in the same database are also dropped.

Operation with View

Data can be insert/update/delete using views if it is not read-only.

E.G. Updating view.

Update V_Employee Set Salary = 90000 WHERE EmpId = 9

To make any view readonly in sql we have to append some constant value in the view

E.G.

CREATE VIEW V_Employee

AS

SELECT * FROM Employee

UNION

SELECT 0,'',0,'' FROM Employee WHERE 1 <> 1 -- constant values

Index VIEW

Similarly, as adding index in table, we can also add index in the view. But the performance of maintaining the index in view is much higher than maintaining the index in the table.

We should need to add the index on the table or view which are not frequently inserted/updated (means non transactional). They are more commonly used in scenarios when data modification is less.

Basically we use indexes on reporting purpose.

The response time to a query is fast at the cost of space consumption.

To Create index view please follow below steps-

Session Options that must be on

·         ANSI_NULLS 

·         ANSI_PADDING

·         ANSI_WARNINGS

·         ARITHABORT

·         CONCAT_NULL_YEILDS_NULL

·         QUOTED_IDENTIFIERS

Session options that must be off

·         NUMERIC_ROUNDABORT

--Create a deterministic view with new SCHEMABINDING syntax.

CREATE VIEW Sales.vOrders  

WITH SCHEMABINDING  

AS  

    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,  

        OrderDate, ProductID, COUNT_BIG(*) AS COUNT  

    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o  

    WHERE od.SalesOrderID = o.SalesOrderID  

    GROUP BY OrderDate, ProductID;  

GO  

--Create an unique Clustered index on the view.  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   

    ON Sales.vOrders (OrderDate, ProductID);  

GO  

Partitioned views

Partitioned views allow data in a large table to be split into smaller tables.

These small tables are called as member tables. The split is done based on range of data values in one of the columns.

E.G.

--- History

CREATE TABLE FACT.sales_history (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_history

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

--- Current year

CREATE TABLE FACT.sales_thisyear (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_thisyear

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

--- Current month

CREATE TABLE FACT.sales_thismonth (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_thismonth

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

CREATE VIEW FACT.sales

WITH SCHEMABINDING

AS

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_history

UNION ALL

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_thisyear

UNION ALL

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_thismonth

Distributed Portioned View

In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.