SQL Basic Concepts interview questions and answers

1. What is Index?

Keyword:
A data structure that improves the speed of data retrieval operations,
Avoid full table scan,
Need extra storage space.

Answer:
An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

2. What are the differences between Clustered Index and Nonclustered Index?

Keyword:
Clustered Index determines the physical storage order of records.
Nonclustered Index stores the column's value and a pointer to the actual record.
One clustered index per table, multiple nonclustered indexes per table.

Answer:
Clustered indexes sort and store the records in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the records themselves can be sorted in only one order.
The only time the records in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its records are stored in an unordered structure called a heap.

Nonclustered indexes have a structure separate from the records. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the record that contains the key value. There can be more than one nonclustered index per table.

Clustered indexes are faster to read than non clustered indexes as records are physically stored in index order. Nonclustered indexes are quicker for insert and update operations than a clustered index.

3. What is the difference between Primary Key Constraint and Unique Constraint?

Keyword:
A table can have multiple unique constraints, only one primary key.
unique constraints allows NULL value.

Answer:
You can use unique constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a unique constraint and a primary key constraint enforce uniqueness, use a unique constraint instead of a primary key  constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
Multiple unique constraints can be defined on a table, whereas only one primary key constraint can be defined on a table.
Also, unlike primary key constraints, unique constraints allow for the value NULL. However, as with any value participating in a unique constraint, only one null value is allowed per column.
A unique constraint can be referenced by a foreign key constraint.

4. What is INNER JOIN?

Keyword:
return only matched rows in both tables

Answer:
INNER JOIN is a commonly used join operation. It returns all rows from both tables only when there is a match between the columns. If there are rows in one table that do not have matches in other table, these records will NOT be returned.

For example, a INNER JOIN query on table employee and table department:
SELECT * FROM employee
INNER JOIN department
ON employee.department_id = department.department_id;

5. What is LEFT OUTER JOIN?

Keyword:
return left table all rows + right table matched rows

Answer:
LEFT OUTER JOIN (or simply LEFT JOIN) returns all rows from the left table, with the matching rows in the right table. For the unmatched rows in the left table, the value for each column of the right table is NULL.

For example, a LEFT OUTER JOIN query on table employee and table department:
SELECT * FROM employee
LEFT OUTER JOIN department
ON employee.department_id = department.department_id;

6. What is RIGHT OUTER JOIN?

Keyword:
return right table all rows + left table matched rows

Answer:
RIGHT OUTER JOIN (or simply RIGHT JOIN) returns all rows from the right table, with the matching rows in the left table. For the unmatched rows in the right table, the value for each column of the left table is NULL.

For example, a RIGHT OUTER JOIN query on table employee and table department:
SELECT * FROM employee
RIGHT OUTER JOIN department
ON employee.department_id = department.department_id;

7. What is the difference between JOIN and UNION?

Answer:

A Join is used for displaying columns with the same or different names from different tables. The output displayed will have all the columns shown individually.
The UNION set operator is used for combining data from two tables which have columns with the same data type. When a UNION is performed the data from both tables will be collected in a single column having the same data type.

8. What is the difference between CHAR and VARCHAR?

Keyword:
fix length vs variable length

Answer:
CHAR is a fixed-length character data type. The storage size of the CHAR value is equal to the maximum size for this column.
VARCHAR is a variable-length character data type. The storage size of the VARCHAR value is the actual length of the data, not the maximum size for this column.

Use CHAR when the data entries in a column are expected to be the same size, such as phone number column. Use VARCHAR when the data entries in a column are expected to vary considerably in size, such as description column.

More SQL Basic Concepts interview questions and answers: SQL Interview Notes

• What are DBMS and RDBMS?
• How do indexes work?
• What is Foreign Key?
• What is Check Constraint?
• What is INTERSECT in SQL?
• Explain general data types in SQL.
• What is the difference between TINYINT, SMALLINT, INT and BIGINT?
• ......

SQL Interview Notes

100+ frequently asked SQL and Database interview questions with concise summaries and detailed answers.  

Topics include: SQL Basic Concepts, SQL DDL & DML, Advanced SQL, Database Design and Performance Tuning.  

Download on the AppStore    Get it On Google Play

JavaScript Interview Notes

100+ frequently asked JavaScript interview questions with concise summaries and detailed answers. Topics include: JavaScript Basics, DOM, BOM, Object-Oriented JavaScript, Function, Scope, Closure, JSON, XML, Ajax, jQuery. 
Download on the AppStore    Get it On Google Play

Java Interview Notes

300+ frequently asked Java interview questions with concise summaries and detailed answers. Topics include: Java & OOP, Strings & Collections, IO JVM & GC, Multithreading, Generics Reflection & Annotations, Design Patterns, Java EE, Spring, JPA & Hibernate.

Download on the AppStore    Get it On Google Play