DBMS Interview Questions - Part 3

DBMS Interview Questions - Part 3

Click Here - DBMS Interview Questions - Part 2

Click Here - For DBMS Interview Questions - Part 1 

Suppose we want to insert multiple values in a table at the same time. How we can do this?

We can add multiple values at the same time using insert into statement.

Syntax :

INSERT ALL INTO table_name (col1, col2, ….., coln) VALUES (expr1, expr2, expr_n)

                   INTO table_name(col1, col2, ….., coln) VALUES (expr1, expr2, expr_n)

                  SELECT * FROM dual; 

What is the role of the DML Compiler? 

It translates DML statements in a query language into low-level

instructions that the query evaluation engine can easily understand.

List the difference between the following commands: drop, truncate, delete. 

Drop and truncate commands are the DDL commands, used to delete tables from the database. Once the table gets deleted, all the privileges and indexes that are related to the table also get deleted. These 2 operations cannot be rolled back. On the other hand, delete is a DML Command which is also used to delete rows from the table and this can be rolled back.

What is a Query?

A query is a statement that is used for the extraction of data from the database.

For example – select * from table1 is a query

What is Subquery?

A subquery is a query within a query.

For example – select * from students where marks = ( select max(marks) from students);

What are the ACID properties in DBMS?

    A => Atomicity
    C => Consistency
    I => Isolation
    D => Durability 

• Atomicity states that database modifications must follow an all-or-nothing rule. Each transaction is said to be atomic. If one part of the transaction fails, the entire transaction fails.
• Consistency states that only valid data will be written to the database. If a transaction is executed that violates the database's consistency rules, the entire transaction is rolled back, and the database is restored to a state consistent with those rules. 
• Isolation requires that multiple transactions occurring at the same time not impact each other's execution
• Durability ensures that any transaction committed to the database is not lost. Durability is ensured by using database backups and transaction logs that facilitate the restoration of committed transactions despite any subsequent software or hardware failures.

What is a Tuple? 

 A single row of a table, which contains a single record for that relation is called a tuple.

Explain degree and Cardinality?

The degree is the total number of attributes in relation to or table and cardinality is the total number of tuples/rows in a relation/table.

What is a relation in DBMS?

A database relation refers to an individual table in a relational database. A table is a relation because it stores the relation between data in its column-row format.

What are the different types of relationships in DBMS?

• There are basically three types of relationships, that can be defined among various different objects.
  
  
• One-To-One: In this, one record of an object relates to one record of
  another object.
  
  
• One-To-Many/ Many-To-One: In this, one record of an object relates to
  many records of other objects and vice versa.
  
  
• Many-To-Many: In this, more than one record of an object relates
  to ‘n’ number of records of another object.

Explain to me the role of using clauses for queries?

The clause enables you to specify conditions that filter the results as per the requirement. Some of the most commonly used clauses are: having, where, etc.