This is the page for Top SQL Interview Questions. Coming Soon..

1.What is SQL?

SQL stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as insert, update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.,

2.What is a primary key?

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields.

3.What is a foreign key?

A foreign key is one table which can be related to the primary key of another table.

4.What is a unique key?

A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.

A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.

There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

5.What is a join?

An JOIN clause combines columns from one or more tables in a relational database. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. In other words, A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

6.What are the different types of Joins available in SQL?

CROSS JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN

7. What is CROSS JOIN?

CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table.

8. What is INNER JOIN?

The INNER JOIN returns records that have matching values in both tables.

9. What is LEFT JOIN?

The LEFT JOIN returns all records from the left table and the matching records from the right table. If there are no matches on right table it returns NULL from the right table.

10. What is RIGHT JOIN?

The RIGHT JOIN returns all records from the right table and the matching records from the left table. If there are no matches on left table it returns NULL from the left table.

11. What is the difference between “explicit join notation” and the “implicit join notation”?

The “explicit join notation” uses the JOIN keyword, optionally preceded by the INNER keyword, to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM employee 
INNER JOIN department ON
employee.DepartmentID = department.DepartmentID

The “implicit join notation” simply lists the tables for joining, in the FROM clause of the SELECT statement, using commas to separate them. Thus it specifies a cross join and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation). The “implicit join notation” is no longer considered a best practice, although database systems still support it.

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

12. What is a View?

A view is the result set of a stored query in a database. In other words, A view is a virtual table which consists of a subset of data contained in a table.

13. What are the advantages of view?

  • A view can limit the degree of exposure of the underlying tables to the outer world
  • A view can be used to restrict or hide some of columns in the tables
  • Views make a database secure and security: Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user’s access to stored data
  • It makes a query simple: A view can take data from several tables and create a simple single table.

14. What are the disadvantages of view?

  • The principal disadvantage of views is complexity where performance suffers because views may be based upon multiple tables. In these cases, queries based upon such views will experience increases in execution time because the database’s query processor has to translate these queries in order to query the actual tables of the database from which the columns of the view are derived.
  • We cannot use DML operations such as Insert, Update and Delete
  • When a base table used in view is dropped the view becomes inactive
  • A query fired on a view will run slower than a query fired on a base table
  • If the structure of the tables in the database a view is based change, then the definition of the view may also need update.

15. What are the different types of views available in SQL server?

  • Indexed Views
  • Partitioned Views
  • System Views

16. What is Materialized View?

Materialized views are also the logical view of data-driven by the select query but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.

When we see the performance of Materialized view it is better than normal View because the data of materialized view will be stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

17. What is the difference between View vs Materialized View in database?

  • Views query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table
  • Views always get latest data but in case of Materialized view need to refresh the view for getting latest data.
  • Materialized view performance is far better than View
  • Materialized view need an extra trigger or some automatic method so that we can keep refreshed, this is not required for views in the database.

18. When to Use View vs Materialized View in SQL?

Views are mostly used in OLTP applications because they are more feasible, only logical representation of table data no extra space needed.

We easily get a replica of data and we can perform our operation on that data without affecting actual table data but when we see a performance which is crucial for a large application they use materialized view where Query Response time matters.

So, Materialized views are used mostly with data warehousing or business intelligence application.

19. What is the Difference between Truncate and Delete in SQL Server?

TRUNCATE

  • TRUNCATE is a DDL command
  • TRUNCATE is executed using a table lock and whole table is locked for remove all records.
  • We cannot use Where clause with TRUNCATE.
  • TRUNCATE removes all rows from a table.
  • Minimal logging in transaction log, so it is performance wise faster.
  • TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
  • Identify column is reset to its seed value if table contains any identity column.
  • To use Truncate on a table you need at least ALTER permission on the table.
  • Truncate uses the less transaction space than Delete statement.
  • Truncate cannot be used with indexed views.

DELETE

  • DELETE is a DML command.
  • DELETE is executed using a row lock, each row in the table is locked for deletion.
  • We can use where clause with DELETE to filter & delete specific records.
  • The DELETE command is used to remove rows from a table based on WHERE condition.
  • It maintain the log, so it slower than TRUNCATE.
  • The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
  • Identity of column keep DELETE retain the identity.
  • To use Delete you need DELETE permission on the table.
  • Delete uses the more transaction space than Truncate statement.
  • Delete can be used with indexed views.

20. What is an index in SQL?

An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.

An index in a database is very similar to an index in the back of a book.

The CREATE INDEX statement is used to create indexes in tables.

21. What are the different types of indexes available in SQL?

Clustered, Non Clustered, Unique, Column store, Covering, Composite index

22. When should indexes be avoided?

The indexes are intended to enhance a database’s performance however there are times they should be avoided.

  • Tables that have frequent, large batch updates or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.
  • Indexes should not be used on small tables.

23. What is Clustered Index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

24. What is Non Clustered index?

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

25. What is Unique Index?

Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values.

Example:

CREATE TABLE Employee (EmpID int, Salary int);
CREATE UNIQUE INDEX ux_Employee_EmpID ON Employee(EmpID);  -- Create unique index on EmpID.

INSERT INTO Employee (Emp ID, Salary) VALUES (1, 100 ); -- OK
INSERT INTO Employee (Emp ID, Salary) VALUES (2, 200); -- OK
INSERT INTO Employee (Emp ID, Salary) VALUES (3, 100); -- OK
INSERT INTO Employee (Emp ID, Salary) VALUES (1, 400); -- Fails!

Duplicate entry '1' for key 'ux_Employee_EmpID'

The last insert fails because it violates the unique index on column EmpID when it tries to insert the value 1 into this column for a second time.

When you create a unique index for an existing table with data, values in the columns or expressions that comprise the index key are checked for uniqueness. If the table contains rows with duplicate key values, the index creation process fails.

26. What is Column Store Index?

The Columnstore index stores and manages data by using column-based data storage and column-based query processing.

Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

27. What is Covering Index?

A covering index is a non-clustered index which includes all columns referenced in the query and the optimizer does not have to perform an additional lookup to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

To be considered as a covering index, all columns referenced by the query must be contained in the index. So this means all columns in the SELECT, JOIN, WHERE, GROUP BY, HAVING etc.,

28. What is Composite Index?

An index that consists of more than one columns is referred as composite index. Both clustered and non-clustered indexes can be composite index.

29. What is Table Scan?

A table scan is the reading of every row in a table and is caused by queries that don’t properly use indexes. Table scans on large tables take an excessive amount of time and cause performance problems.

30. What is Index Scan?

Index Scan retrieves all the rows from the table. Index scan touches every row in the table, whether or not it qualifies, the time this takes is proportional to the total number of rows in the table or to the size of the index. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

In other words, An Index Scan is nothing but scanning on the data pages from the first page to the last page. In certain situations an index scan can be faster than an index seek (sometimes significantly faster) – usually when the table is very small, or when a large percentage of the records match the predicate.

31. What is Index Seek?

Index Seek retrieves selective rows from the table. Index seek only touches rows that qualify and pages that contain these qualifying rows, the time this takes is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

In other words, An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records the time taken is only proportional to the number of matching records.

In general, an index seek is preferable to an index scan (when the number of matching records is proportionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the total number of records in your table.