Database

---

Database interview questions

Here I have capture important interview questions on database. 

---

1.    What are super, primary, candidate and foreign keys?

A superkey is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes.

A Candidate key is minimal superkey, i.e., no proper subset of Candidate key attributes can be a superkey.

A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more that one primary keys in a table.

Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.

---

2.    What is the difference between primary key and unique constraints?

Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains.

---

3.    What is database normalization?

It is a process of analyzing the given relation schemas based on their Functional Dependencies and primary key to achieve the properties like Minimizing redundancy, minimizing insertion, deletion and update anomalies.

---

4.    What are the differences between DDL, DML and DCL in SQL?

DDL Data definition language allows to CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.

DML Data manipulation language makes user able to access and manipulate data. It allows to insert, retrieve, Update and Delete data from the database

DCL Data control language allows you to control access to the database. It includes two commands GRANT and REVOKE.

---

5.    What is the difference between having and where clause?

HAVING is used to specify a condition for a group or an aggregate function used in select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions.

---

6.    What is Join?

An SQL Join is used to combine data from two or more tables, based on a common field between them

Equi-Joins

It is a sql join where we use the equal sign as the comparison operator i.e "=“ between two tables. By default this join is inner-equi-join.

Non-Equi Join:

A Non- Equi Join is a SQL Join where condition is established between table using all comparison operators (>=, <=, <, >) can used except the equal (=).

Self-Join

A self means joining columns of a table to itself. It fetches record from same table only and for that we have to mention table alias name twice times.

Outer-Join

Outer join returns all records from one table and those records from another table where meets joining condition.

There are three types of outer joins

Left Outer-join - In Left Outer-Join retrieves the unmatched rows and matched from the left (1st) table and only matched rows from right (2nd) table.

Right outer-join - In Right Outer-Join retrieves the unmatched rows and matched from the Right table and only matched rows from Left table.

Full outer-join - Full Outer-join is combination of left and right outer join. It returns both match and unmatched row from both tables.

Semi-Join (INTERSECT)

Semi-join between two tables returns rows from table A, where one or more matches are found in table B. It is known as Co-Related-Sub-query, where parent query depends upon Child query.

Anti-Join (MINUS)

Anti-Join Suppose there are two tables A and B Anti-join between two tables A AND B returns rows from the table A, for which there are no corresponding rows in the Table B. Anti-join is also a sub-query in which parent query doesn't depend upon child query.

Cross-Join or Cartesian Join

Cross join generally generate cross product between two tables. Each row of Table1, combined with each rows of Table2.

---

7.    What is the difference between clustered and non-clustered index in SQL?

Clustered Index	Non-Clustered Index
One table can have only one clustered index because data rows themselves can be stored in only one order	One table can have multiple non-clustered index
Clustered index determines how data is stored physically in table	Non-Clustered index determines how data is stored logically in table
In many relational databases, Clustered Index is automatically created on primary key column
Note - Indexes can actually harm the performance of INSERT and UPDATE queries because these requires rearrangement of indexes which can be time-consuming based on size of table or number of records on the table. so always create Indexes on columns which are frequently used in a SELECT query