50 Interview Questions

Question 1

What is a database?

Answer 1

An organized collection of data

Question 2

What is DBMS?

Answer 2

Database Management System. The software used to create and manage the database.

Question 3

What is RDBMS?

Answer 3

Relational Database Management System. RDMS stores data in tables and the relationships between those tables can be defined by common fields.

Question 4

What is SQL?

Answer 4

Structured Query Language. The standard language for RDBMS

Question 5

What is the difference between SQL and mySQL?

Answer 5

SQL is the language used to manipulate relational databases. MySQL is a RDBMS

Question 6

What are Tables and Fields?

Answer 6

Tables are collections of data stored in rows and columns. Fields are the columns. Rows are the records.

Question 7

What are constraints in SQL?

Answer 7

Rules for data in a table. Can be applied during creation of a table or after creation using ALTER TABLE command

Question 8

Constraint 1: NOT NULL

Answer 8

Prevents NULL values from being inserted into a column

Question 9

Constraint 2: CHECK

Answer 9

Verifies that all values in a field satisfy a condition

Question 10

Constraint 3: DEFAULT

Answer 10

Automatically assigns a default value if no value has been specified for the field.

Question 11

Constraint 4: UNIQUE

Answer 11

Ensures unique values to be inserted into the field. Unlike primary key, there can be multiple unique constraints defined per table.

Question 12

Constraint 5: INDEX

Answer 12

Indexes a field providing faster retrieval of records.

Question 13

Constraint 6: PRIMARY KEY

Answer 13

Indexes a field providing faster retrieval of records. Must contain UNIQUE values and has implicit NOT NULL constraint. A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields

Question 14

Constraint 7: FOREIGN KEY

Answer 14

Comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

Question 15

What is a JOIN?

Answer 15

The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.

Question 16

INNER JOIN

Answer 16

Retrieves records that have matching values in both tables involved in the join.
SELECT *
FROM Table_A
INNER JOIN Table_B;

Question 17

LEFT JOIN

Answer 17

Retrieves all the records/rows from the left and the matched records/rows from the right table.
SELECT *
FROM Table_A A
LEFT JOIN Table_B B
ON A.col = B.col;

Question 18

RIGHT JOIN

Answer 18

Retrieves all the records/rows from the right and the matched records/rows from the left table.
SELECT *
FROM Table_A A
RIGHT JOIN Table_B B
ON A.col = B.col;

Question 19

FULL (OUTER) JOIN

Answer 19

Retrieves all the records where there is a match in either the left or right table.
SELECT *
FROM Table_A A
FULL JOIN Table_B B
ON A.col = B.col;

Question 20

What is a SELF-JOIN?

Answer 20

regular join where a table is joined to itself based on some relation between its own column(s).
SELECT A.emp_id AS “Emp_ID”,A.emp_name AS “Employee”,
B.emp_id AS “Sup_ID”,B.emp_name AS “Supervisor”
FROM employee A, employee B
WHERE A.emp_sup = B.emp_id;

Question 21

What is a CROSS-JOIN?

Answer 21

a cartesian product of the two tables included in the join.
SELECT stu.name, sub.subject
FROM students AS stu
CROSS JOIN subjects AS sub;

Question 22

What is an INDEX?

Answer 22

data structure that provides quick lookup of data in a column or columns of a table.An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

CREATE INDEX index_name 	 /* Create Index */
ON table_name (column_1, column_2);

DROP INDEX index_name; /* Drop Index */

Question 23

What is data integrity?

Answer 23

the assurance of accuracy and consistency of data over its entire life-cycle

Question 24

What is a query?

Answer 24

is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.

Question 25

What is a subquery?

Answer 25

a query within another query

Question 26

What are the two types of subqueries?

Answer 26

A correlated subquery cannot be considered as an independent query, but it can refer the column in a table listed in the FROM of the main query.

A non-correlated subquery can be considered as an independent query and the output of subquery is substituted in the main query.

Question 27

UNION

Answer 27

SELECT name FROM Students /* Fetch the union of queries */
UNION
SELECT name FROM Contacts;

SELECT name FROM Students /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;

Question 28

MINUS

Answer 28

SELECT name FROM Students /* Fetch names from students /
MINUS / that aren’t present in contacts */
SELECT name FROM Contacts;

Question 29

INTERSECT

Answer 29

SELECT name FROM Students /* Fetch names from students /
INTERSECT / that are present in contacts as well */
SELECT name FROM Contacts;

Question 30

What are the different types of relationships in sequel?

Answer 30

One-to-One - This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table.

One-to-Many & Many-to-One - This is the most commonly used relationship where a record in a table is associated with multiple records in the other table.

Many-to-Many - This is used in cases when multiple instances on both sides are needed for defining a relationship.

Self Referencing Relationships - This is used when a table needs to define a relationship with itself.

Question 31

What is an alias?

Answer 31

It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. An alias is represented explicitly by the AS keyword but in some cases the same can be performed without it as well.

Question 32

What is a view?

Answer 32

A view in SQL is a virtual table based on the result-set of an SQL statement. A table contains data, a view is just a SELECT statement which has been saved in the database

Question 33

What is normalization?

Answer 33

Normalization represents the way of organizing structured data in the database efficiently.

Question 34

DELETE statement

Answer 34

statement is used to delete rows from a table.

DELETE FROM Candidates
WHERE CandidateId > 1000;

Question 35

TRUNCATE statement

Answer 35

used to delete all the rows from the table and free the space containing the table.

TRUNCATE TABLE Candidates;`

Question 36

DROP statement

Answer 36

used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database.

Question 37

What is the difference between DROP and TRUNCATE statements?

Answer 37

If a table is dropped, all things associated with the tables are dropped as well. This includes - the relationships defined on the table with other tables, the integrity checks and constraints, access privileges and other grants that the table has. To create and use the table again in its original form, all these relations, checks, constraints, privileges and relationships need to be redefined. However, if a table is truncated, none of the above problems exist and the table retains its original structure.