Top 10 SQL Interview Questions: Part 2

Hello Friends,

I will be providing few Interview questions every week so that easily to learn. If get any doubts comment and if you have any questions, please feel free to ask.

In this I will concentrate more on definitions.

1. What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

Syntax:

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

SELECT * FROM [view_name];

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

DROP VIEW view_name;

2.What is an Index?

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

Syntax:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

DROP INDEX index_name ON table_name;

• Unique Index.

This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

• Clustered Index.

This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.

• NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

 

3. what is Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

Syntax:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

ALTER PROCEDURE procedure_name
AS
sql_statement
GO;

EXEC SelectAllCustomers;

DROP PROCEDURE procedure_name

4.  What is the difference between DROP and TRUNCATE commands?

DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.

5. What is Trigger?

Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table.

Syntax:

CREATE [OR REPLACE ] TRIGGER trigger_name 

{BEFORE | AFTER | INSTEAD OF } 

{INSERT [OR] | UPDATE [OR] | DELETE} 

[OF col_name] 

ON table_name 

[REFERENCING OLD AS o NEW AS n] 

[FOR EACH ROW] 

WHEN (condition)  

DECLARE

   Declaration-statements

BEGIN 

   Executable-statements

EXCEPTION

   Exception-handling-statements

END;

6. What is CLAUSE?

SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.

Example – Query that has WHERE condition

Query that has HAVING condition.

7. How can you create an empty table from an existing table?

  SELECT * INTO NEWTABLE FROM OLDTABLE WHERE 1=2

8. How to fetch common records from two tables?

SELECT STUDENTID FROM STUDENT INTERSECT SELECT STUDENTID FROM EXAM

9. How Transaction and rollback works in SQL?

Transactions in SQL Server are used to execute a set of SQL statements in a group. With transactions, either all the statements in a group execute or none of the statements execute.

In the case where one of the queries in a group of queries executed by a transaction fails, all the previously executed queries are rollbacked. Transactions in the SQL server are rollbacked automatically. However, with the rollback SQL statement, you can manually rollback a transaction based on certain conditions.

BEGIN TRANSACTION

 

  INSERT INTO Books

  VALUES (20, 'Book15', 'Cat5', 2000)

 

  UPDATE Books

  SET price = '25 Hundred' WHERE id = 20

 

  DELETE from Books

  WHERE id = 20

 

COMMIT TRANSACTION

10. What is Denormalization. What are all different normalizations?

   Denormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

•     First Normal Form (1NF):.

This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.

• Second Normal Form (2NF):.

Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

• Third Normal Form (3NF):.

This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

• Fourth Normal Form (4NF):.

Meeting all the requirements of third normal form and it should not have multi- valued dependencies.

 

Thanks for reading friends. If any questions on above, feel free to comment.

We are here to learn and grow. Follow for more updates.

Share to Friends .