Database and SQL Interview Questions

Database and SQL Questions

  1. What is a database?

Ans: Database is nothing but an organized collection of data.

For Example, a Hospital maintains the database of all the information related with the patient, doctors, and medicine.

  1. What is a database management system (DBMS)?

Ans: DBMS is a collection of data and a set of programs to access those data.

  1. Most Popular DBMS Software?

Ans: MySQL, Oracle, Microsoft SQL Server, Sybase, IBM DB2, MS Access, etc.;

  1. What is RDBMS?

Ans: Relational Database Management System (RDBMS) is based on a relational database model which store the data in the form of tables. Data can be accessed easily from the relational database using Structured Query Language (SQL).

  1. What are the different levels of abstraction in the DBMS?

Ans: There are 3 levels of Abstraction which is as follows

  • Physical Level: This is the lowest level of the data abstraction which states how the data is stored in the database.
  • Logical Level: This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.
  • View Level: This is the highest level in the data abstraction which shows/states only a part of the database.
  1. What is the advantage of DBMS over file system?
  • Data is stored in a structured way and hence redundancy is controlled.
  • Validates the data entered and provide restrictions on unauthorized access to the database.
  • Provides backup and recovery of the data when required.
  • Provides multiple user interfaces.
  1. What are the different types of languages that are available in the DBMS?

Ans: There are 3 types of languages in the DBMS, which are as follows

  • DDL: DDL is Data Definition Language which is used to define the database and schema structure by using some set of SQL Queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.
  • DCL: DCL is Data Control Language which is used to control the access of the users inside the database by using some set of SQL Queries like GRANT and REVOKE.
  • DML: DML is Data Manipulation Language which is used to do some manipulations in the database like Insertion, Deletion, etc. by using some set of SQL Queries like SELECT, INSERT, DELETE and UPDATE.
  1. What is the ER model in the DBMS?

Ans: E-R model is called an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.

  1. In a relational database model, relations are called as:

Ans: Tables

  1. In the architecture of a database system, the external level is the

Ans: view level.

  1. In an E-R diagram attributes are represented by

Ans: Ellipse.

  1. In an E-R diagram, an entity set is represented by a

Ans: Rectangle

  1. In E-R diagram generalization is represented by

Ans: Triangle

  1. Key to represent the relationship between tables is called

Ans: Foreign Key

  1. The file organization that provides very fast access to any arbitrary record of a file is

Ans: Hashed file

  1. In the relational database management system, the cardinality is termed as:

Ans: Number of tuples in Relational Tables.

  1. Relational calculus is called as

Ans: Non- Procedural language.

  1. Relational Algebra is

Ans: Procedural query Language

  1. What is Normalization?

Ans: Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.

Purpose of normalization in DBMS

  • To minimize the redundancy of the Data.
  • To minimize the Insert, Delete and Update Anomalies.
  1. What are the different types of normal forms?

Ans: First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce & Codd normal form (BCNF) and Fourth Normal Form (4NF).

  1. What is First Normal Form (1NF)?

Ans: A relation is said to be in 1NF only when all the entities of the table contain unique or atomic values.

  1. What is the Second Normal Form (2NF)?

Ans: A relation is said to be in 2NF only if it is in 1NF and all the non-key attribute of the table is fully dependent on the primary key (Table should be free from partial dependencies).

  1. What is the Third Normal Form (3NF)?

Ans: A relation is said to be in 3NF only if it is in 2NF and every non-key attribute of the table is not transitively dependent on the primary key (Table should be free from transitive dependencies).

  1. What is Boyce & Codd normal form (BCNF)?

Ans: A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.

  1. What is Fourth Normal Form (4NF)?

Ans: A relation is said to be in 4NF only if it is in 3NF and table should not have multivalued dependencies.

  1. What is Primary key in DBMS?

Ans: It is a minimal set of attributes (columns) in a table or relation that can uniquely identifies tuples (rows) in that table.

For example, Student (Stud_Roll_No, Stud_Name, Addr)

In the student relation, attribute Stud_Roll_No alone is a primary key as each student has a unique id that can identify the student record in the table.

  1. What is Super key in DBMS?

Ans: A superkey is a set of one or more attributes (columns), which can uniquely identify a row in a table.

For example, Student (Stud_Roll_No, Stud_Name, Addr)

In the student relation, all of the following sets of super key are able to uniquely identify a row of the Student table.