Q1. Can you create a table without using create command ?
A. Yes, we can create table with the help of SELECT INTO statement. It
copies content of one table to another table. However, there should be
at least one table from where we can copy content.
Example : Copying all columns : select * into new_table from old_table where
condition
Copying specific column : select col1,col2 into new_table from old_table
where condition
Creating new empty table : select * into new_table from old_table where 1
= 0
Q2. What is Denormalization ?
A. It is the reverse process of Normalization. It is the process of trying to
improve the readability of the database by grouping data. Denormalization
is also used for speeding up the performance.
Q3. What are Joins ?
A. Join clause are used to combine rows from two or more tables,
depending upon the columns between them.
Q4. What are the different types of Joins ?
A. Different types of Joins are :
- INNER JOIN : It returns all records that are common in both tables.
- LEFT OUTER JOIN : It returns all records from the left table, and matched records from right table
- RIGHT OUTER JOIN : It returns all records from the right table, and matched records from left table.
- FULL OUTER JOIN : It returns all records when there is a match in either left or right table.
Q5. Explain Transaction ?
A. Transaction refers to the collection of multiple statements, that are
responsible for transferring a database from one consistent state to another
consistent state.
Q6. Explain the role of views in database ?
A.View refers to the virtual table. We can create view using create view
statement.
CREATE VIEW as Select col1
FROM table1
where CONDITION;
Q7. Explain Trigger ?
A. Triggers are defined as special kind of stored programs, which are
automatically executed whenever a specific operation occurs in the
database server.
Q8. What are Locks ?
A. Locking is the mechanism to protect data integrity and ensure data
consistency during transactions. Locks are the most common cause of
blocked processes. Stronger the Isolation level, more the chances of
blocking.
Q9. Explain different types of Locks ?
A. Locks are broadly characterized into following types :
Shared Locks : These locks are acquired by readers during read
operations. In other words, these locks exist when two transactions are
granted read access. Data updation is not allowed until shared lock is
released.
Exclusive Locks : In exclusive lock, data items can be both read as well
as written by the transaction. In Exclusive lock, multiple transactions do not
modify the same data simultaneously.
Q10. What is Super Key ?
A. An attribute or set of attributes that uniqueness in database is refered to
as Super key. It is the superset of Candidate key.
Q11. What is Candidate Key ?
A. A minimal set of attribute/attributes that can be used to uniquely identify
a single row in a given relation is refered to as Candidate key.
Q12. Explain Primary Key ?
A. DB Designer selects one of the candidate key as primary key for a
relation for the purpose of identification of a tuple uniquely. It is identified
during table creation.
Q13. What is Composite Key ?
A. If a primary key has more than one attribute, then it is referred to as
Composite key.
Q14. Explain Foreign Key ?
A. A set of attribute/attributes that is used to establish and enforce a link
between data in two or more relations.
Q15. Can a table have more than one primary key ?
A. No.
Q16. Can We Have NULL Value in Primary Key?
A. No.
Q17. What are cursors ?
A. A cursor is a temporary work area created in system memory when a
SQL statement is executed. A cursor can hold more than one row, but can
process only one row at a time.
Q18.What are the differences between Hash join, Merge join and Nested loops?
| Hash join | Merge join | Nested loops |
| The hash join is used when you have to join large tables. | Merge join is used when projections of the joined tables are sorted on the join columns. | The nested loop consists of an outer loop and an inner loop. |
Q19. What do you understand by Proactive, Retroactive and Simultaneous Update ?
- Proactive Update: These updates are applied to the database before it becomes effective in the real-world environment.
- Retroactive Update: These retroactive updates are applied to a database after it becomes effective in the real-world environment.
- Simultaneous Update: These updates are applied to the database at the same instance of time as it becomes effective in a real-world environment.
Q20.What do you understand by Data Independence?
A. When you say an application has data independence, it implies that the application is independent of the storage structure and data access strategies of data.