SQL Theory Interview Question

SQL Theory Interview Question for Fresher or Beginners here you are get all SQL Interview Question which will help you preparation of “Full Stack Developer” Position in IT Companies. My lots of Student working in different – different company across India. Some of them Interview Taker, that help me & my student to prepare for Interviews.

What are different types of sql comments? Explain DDL, DML, TCL Commands.

• There are different commands which we use to communicate with the database to perform specific tasks.

Data Definition Language (DDL) – These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Data Manipulation Language (DML) – These SQL commands are used for storing, retrieving, modifying, and deleting data.
These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE.

Transaction Control Language (TCL) – These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL) – These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Difference between Delete, Truncate and Drop commands?

• The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
• Delete is a DML command.
• We can undo the delete operation by using ROLLBACK transaction command.
• This operation causes all DELETE Triggers to fire on a table.

Truncate

• TRUNCATE removes all rowsfrom a table.
• The operation cannot be rolled back and no triggers will be fired.
• TRUCATE is a DDL command and it is faster.

Drop

• The DROP command removes a table from the database.
• All the tables’ rows, indexes and privileges will also be removed.
• No DML triggers will be fired.
• The Drop operation cannot be rolled back.

Why Truncate is faster ?

When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann’t get back the data.

SQL Theory Interview Question for Fresher or Beginners

What is difference between Primary key and Unique key?

• Both unique key and Primary key are used to enforce uniqueness in the column records.
• We can find 2 differences in both the keys :

1. We can have a single Primary across the table whereas we can have multiple Unique Key across the table.
2. Primary Key does not allow NULL value whereas Unique key allows a single NULL value.

What is cascading referential integrity? [Most Imp Question]

• Cascading referential integrity constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys points.
• We have the following options when setting up Cascading referential integrity constraint

1. No Action: This is the default behavior. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.
2. Cascade: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.
3. Set NULL: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

D. Set Default: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.

What is composite key in SQL?

• A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.
• Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one attribute is known as a composite key.

Can we insert identity column value explicitly? If yes, then how?

• Yes, We can explicitly insert identity value whenever required.
• We can use Identity_Insert to achieve this.

SET Identity_Insert [tblName] ON

SQL Theory Interview Question for Fresher or Beginners

What are different way to get last generated identity column value?

• There are three ways to fetch last generated identity column value

SCOPE_IDENTITY() – returns the last identity value that is created in the same session and in the same scope.

@@IDENTITY – returns the last identity value that is created in the same session and across any scope.

IDENT_CURRENT(‘TableName’) – returns the last identity value that is created for a specific table across any session and any scope.

What will be the value of identity column value if all rows deleted from the table? Will it reset to default values automatically? If No then, How to reset identity column seed and increment value to default values?

• If all rows get deleted from table but still we able to see last generated identity value.
• We can reset this identity column value to default value by using DBCC CHECKIDENT command.

DBCC CHECKIDENT(tblName, RESEED, 0)

What is Normalization in SQL? Why do we need of Normalization? What are different forms of Normalization? Explain 1st , 2nd and 3rd Normal form. [Most Imp Questoin]

• Database normalizationis the process of organizing data to minimize data redundancy, which in turn ensures data consistency.
• There are 6 different forms of normalizations from First Normal Form thru Sixth Normal Form most of the databases are normalized till Normal form 3.
• A table is said to be in 1NF, if
  The data in each column should be atomic. No multiple values, separated by comma.
  2. The table does not contain any repeating column groups
  3. Identify each record uniquely using primary key.
• A table is said to be in 2NF, if
  The table meets all the conditions of 1NF
  2. Move redundantdata to a separate table
  3. Create relationship between these tables using foreign keys.
• A table is said to be in 3NF, if the table
  Meets all the conditions of 1NF and 2NF
  2. Does not contain columns (attributes) that are not fully dependent upon the primary key.