List of Most Important 500+ DBMS Interview Questions

We might have heard about the word DATA which basically means a collection of unorganized numbers, characters, text etc. Some common examples of data include marks, phone numbers, weights, prices, costs, number of items sold, product names, addresses, registration marks etc. Data is likely to be managed more efficiently when they are stored in a database.

Also Read: Top 250+ Technical Software Engineering Interview Questions

Databases play a critical role in almost all areas where computers are used including education, library, science, medicine, business, law, engineering and so on. You may encounter database in your daily life. When you purchase goods from your local market, it is likely that a database is accessed. The computer at the cash counter is linked to an application program that uses a barcode to find the price of an item from the product database. Similarly, when you access your credit/debit card, all your transactions are recorded into a database. Besides, these when you make your booking for airlines/railway from your computer, you again access some databases.

Database and Database technology have a major impact on the growing use of computers. Due to recent developments, the storage capacity and the computing speed of the computers have increase, so now a large amount of data is being handled by the computers.

In this article, we will discuss the most important DBMS Interview Questions in order to make you familiar with the type of questions that can be asked during a job interview related to the Database Management System (DBMS).

1. Introduction to DBMS

What is the difference between data and information? Explain with example.
Why do we need information?
What do you understand by term ‘Database’?
What are features of data in the database? What operations can be performed on a database?
Why is data important for an organization?
Discuss the disadvantages of manual databases?
Why should the database stored on computers?
Discuss the file system approach? What are its disadvantages?
When do we prefer file system approach over database approach?
Define the following terms:
- Data
- Database
- Database System
- DBMS
What are the components of Database System?
What are the different types of users in the Database System? What do they do?
What are the primary requirements for designing the DBMS.
What are the advantages and disadvantages of DBMS?
What is the difference between DDL and DML?
What is the difference between File System Approach and Database Approach.
What is a data dictionary? Explain its importance.
What do you understand by term ‘Data Integrity’ and ‘Data Consistency’?
What are the objectives of using three level architecture of DBMS?
Explain the three level of architecture of DBMS diagrammatically?
Define the following terms
- Schema
- Mapping
- Internal Level.
What do you mean by Data Independence? Explain the differences between logical and physical data independence?
What do you understand by mapping? Explain the different types of mapping?
What are the different steps involved in database access?
Who is DBA? What are its functions?
Write short notes on:
- Data Manager
- File Manager
- Disk Manager
- Buffer Manager

2. Data Models

What is a Data Model? Why is it important?
What characteristics should a data model process?
Explain the various categories of data models?
Explain the object based logical data models?
What do you mean by record based data models? Explain the different types of these models?
Explain the hierarchical model? How the various operations are performed on it?
What are the advantages and disadvantages of hierarchical model?
Explain the various problems associated with hierarchical model?
Explain the hierarchical model with the help of a sample database?
Explain network model? How the various operations are performed on it.
Discuss the advantages and disadvantages of the network model.
Explain what problems are incurred while performing operations in the network model?
Discuss the network model using a sample database.
Explain the concept of a table.
Explain the relational model. Why was it developed?
What operations are performed on the relational model?
Discuss the advantages and disadvantages of the relational model.
Explain the various difference between the hierarchical, network and the relational model?
How will you perform “Delete Operation” in case of hierarchical model?
How will you perform “Insert Operation” in case of hierarchical model? What problems are faced?
Discuss the terminology used in relational model.
Discuss the retrieval operation in network model.
How will you perform “Update operation” in case of hierarchical model?
Write short notes on:
- Degree of table
- Cardinality of table
- Attributes
- Tuples
- Data values
- Physical Models
What are characteristics that help us to choose which data model to use among record based models?
Relational model posses data independence. Explain.
What are the properties of a table.
Explain the primary key and the foreign key?
What is the domain of a table?

3. Entity-Relationship Model

What is E-R modelling? Discuss its features.
What is an entity? What are its features.
Explain the concept of entity set.
What is the difference between an Entity, Entity type and Entity Sets?
What is an attribute? Discuss different type of attributes?
Explain the differences between the following:
- Simple and Composite attribute.
- Single-valued and Multi-valued attribute.
What is a Relationship?
Write short note on:
- Degree of Relationship
- Cardinality of Relationship
- Connectivity of Relationship
How are the links between entity sets and relationship represented?
Discuss the various symbols used in ER diagrams to represent various type of information.
What is a ER diagram? How can it be used for modelling?
What are the different types of entities?
Explain the weak entity set and the strong entity set. Also show their differences.
Explain the terms:
- Identifying key or discriminator
- Identifying Relationship
Explain the concept of specialization with the help of an example.
Discuss the properties of specialization.
Why do we need aggregation?
Discuss the properties of aggregation with help of example.
How are EER diagrams for Specialization, Generalization and Aggregation drawn.
Discuss the steps for designing an ER diagram.
Consider the following entities and their relationships. Using these draw their ER diagrams.
- Entities:- Author, Publisher
  Relationships:- Publishers publishes ‘works’ of different authors.
- Entities:- Subject, Teacher, Student
  Relationships:- Teacher ‘teaches’ a student a subject.
- Entities:- Exam, Course, Section, Room of University database.
  Relationships:- Exam for a particular course of a section is conducted in a Room.
- Entities:- Author, Book, Publisher, Edition.
  Relationships:- Author writes Books; Author reviews Book; Book is published by Publisher; Book has Edition.
- Entities:- Company, Project, Employee.
  Relationships:- Company controls projects; Employee supervises employees; Employee works on Project.
- Design a generalization, specialization hierarchy for an EMPLOYEE whose pay is determined by its status i.e. hourly employee, salaried employee and Exempt Employee. Justify your placement of attributes at each level of hierarchy.
What are the problems associated with ER model?
Consider a situation where a single branch of a company has many employees who oversee a property for rent. Not all employees oversee property and not all properties are overseen by employees. Does a chasm trap exist? If Yes, then how to remove it.
What is a fan trap? Explain using an example.
How is ER Model Mapped to relational model? Explain.

4. Relational Database Management System

Discuss in brief the evolution of RDBMS.
Name some commonly available RDBMS packages.
What are major aspects of the relational model?
Explain the Relational Data Structure?
Explain the following terms: Relation, Tuple, Attribute, Domain, Cardinality, Degree.
What is a Domain? How it related to data value?
Discuss the properties of attributes of a table.
Discuss the properties of tuples?
Explain the concept of keys? Name different types of keys?
What do you mean by uniqueness and irreducability? Explain.
Name the different types of keys?
Explain the following keys with example:
- Candidate Key
- Super Key
- Primary Key
- Foreign Key
- Artificial key
What is the difference between:
- Super Key and Candidate Key
- Primary key and Foreign Key
- Primary Key, Candidate Key and Alternate Key.
Can foreign key refer to primary key in same table? If yes, explain.
Write short note on:
- Alternate Key
- Composite Key
Explain the concept of null values with proper example.
What is the difference between unknown value and inapplicable value in concept of Null?
What are integrity rules? Explain with examples?
What do you understand by referential integrity? Explain.
What is the purpose of relational data manipulation?
Explain the different CODD rules.
What is the difference between entity integrity and referential integrity rules? Give examples.
What is requirement for a DBMS to be a RDMS?
How are non applicable values handled in a table?
What do you understand by:
- Non Subversion rule
- Information rule
- View updating rule
- Data Description rule.

5. Relational Algebra and Relational Calculus

What is a Relational Algebra and What are its uses?
How can you classify the Relational Algebraic operations?
What is Union compatibility? Which operations require union compatibility? Why?
Explain the following operations with examples:-
- Union
- Intersection
- Difference
- Cartesian Product
- Division
Discuss the features of cartesian product operation?
What do you mean by statement’ union and intersection operations are commutative and associative operations’?
Explain the difference between selection and projection operations with examples?
Discuss the properties of projection operation?
What a join operations? Discuss various types of JOIN operations?
What is the difference between
- EQUIJOIN and NON-EQUIJOIN
- THETA JOIN and NATURAL JOIN
- INNER JOIN and OUTER JOIN
- LEFT OUTER JOIN and RIGHT OUTER JOIN
Discuss the division operation with example? Also discuss its properties.
What is a self join? Why renaming of relation is necessary in it.
What is the similarities and differences between relation algebra and relational calculus?
What are the different types of relational calculus? Explain.
How does tuple relational calculus differ from domain relational calculus?
Explain the term:-
- Tuple Variable
- Well formed formulas
- Atoms
- Free and bound variables
- Domain Variable
What are existential and universal qualifiers and what are they used for?
How is a query expressed in relational calculus and what are they used for?
Explain the different conditions in Domain Calculus?
Consider the following Relations (Primary Keys are highlighted)
EMP (EMP_Id, EName, Salary, Position, Dept_Id)
DEPT(Dept_Id, Dname, Mang_Id, Location)
Use relational algebra, tuple relational calculus, domain relational to represent the following queries:
- List all the employees who work for ‘Research’ Department.
- List is the manager name of Employee “Anurag” who works in “Delhi”.
- List the are salaries of all employees working in Delhi.
Consider the following relations (Primary Keys highlighted) and their relationships.
AUTHOR (Aname, Address, Specialization)
PUBLISHER (PName, Address, Type, Bk_Id)
BOOK (Bk_Id, Book_name, ISBN_no)
Writes (AName, Bk_Id, Royality)
Use relational algebra, tuple relational calculus, domain relational calculus to represent following queries:-
- List al titles published by ‘Lakhanpal’ publisher?
- List the specialization of all authors publishing a book with ‘Lakhanpal’.
- List the location of the publisher of a book ‘Learn Prog. in C’?
- Get the names of all publisher publishing a book by Anshuman and by Anurag.
- List the addresses of all the authors publishing a book with all publishers located in Delhi?

6. Normalization

Define Normalization? Why do we need to normalize the database? Explain.
What are the anomalies which lead to bad design of the database?
What are the advantages and disadvantages of normalization?
Discuss in brief the various types of normalization?
How do we convert an unnormalized relation to that in 1NF?
Define First Normal form? Explain with help of example.
what are the anomalies in the First Normal form?
Explain the concept of functional dependency and fully functional dependency with an example.
What is functional dependency diagram? Explain with an example.
A relation R (A,B,C,D) is given. Given the following functional dependencies. If a student decomposes this relation as follows tell whether he/she is correct or not?
Define second normal form? Explain with help of example.
Why do we need to convert a relation in 2NF to that of 3NF?
How are anomalies of the First Normal form rectified? Explain with help of an example.
Explain the concept of transitive dependencies with help of an example.
Define the third normal form with the help of an example.
How are the anomalies of the second normal form removed in 3NF? Explain.
What are the anomalies of the third normal form (3NF)?
Using an appropriate example explain the BCNF? Why is it considered stronger than 3NF?
Draw the comparison and contrast between a relation in BCNF and 3NF?
Explain dependency preservation problem with example?
Explain multivalued dependency using an appropriate example? How is it represented?
What are the various steps for normalizing a database?
What are the properties of the MVD’s?
Define Fourth Normal form? When is it desirable?
Explain the concept of join dependency with example?
What are the conditions required for a relation to be in fifth normal form?
What are anomalies which occur due to multivalued dependencies? How are they removed.
Explain the steps for normalization in brief?
What do you mean by denormalization? Why do we need it? Explain with example.
Consider a relation
WORK_ALLOC (@EMP# + Name + Role + Dept + @Project + Cost)
Here @ symbol tells that it is a primary key.
Let following dependencies hold
Emp# → Name ; Emp# → Dept; Project → Cost
Normalize WORK_ALLOC into 3NF?
Consider a relation
CUST_ACC (@ CUST_Id + Name + Address + @Branch_Code + @ACC# + Balance)
Let following dependencies hold
Cust_Id → Name; Cust_Id → Address;
(Branch_Code, ACC#) → Balance
Normalize CUST_ACC into BCNF?
Consider a relation R (@A, @B, C, D, E)
The following dependencies hold
(A,B) → C;B → E;B → C
Normalize R into 2NF?
Consider a relation R (@A + @B + C + D + E + F) and dependencies shown in diagram.
Normalize R into 3NF?
Consider a relation R (@A + @B + C + D) and dependencies as shown. The relation is in 3NF. Is the Relation in BCNF? If No, then Normalize it to BCNF?
Consider a relation STUDENT (Course, Student, Faculty, Term, Grade). The following conditions holds true.
- A student can take many courses and each course can have move than one faculty for a term.
- Each student receives only one grade in a term.
  Decompose the relation into 4NF?
Consider a relation
SUPPLIES (SUPPLIER, PART, CONTRACT, QTY)
Here the following dependencies hold
CONTRACT → PART; PART → SUPPLIER; SUPPLIER, CONTRACT → QTY
Normalize the relation?
Consider a relation LIBRARY_USERS (UNIVERSITY, CAMPUS, LIBRARY, STUDENT).
Assume university can have number of campuses and a campus has one library and each library is on one campus. Moreover, each library has a distinct name. A student is at one university only and can used the Libraries at some, but not all of the campuses. Normalize the relation?
Consider a University database which keeps tracks of students record
STUDENT (STUDENT_NAME, @STUDENT_ROLLNO, ADDRESS, CONTACT NUMBER, DATEOFBIRTH, SEX, CLASS, DEPARTMENT)
DEPARTMENT (@DEPT_NAME, @DEPT_CODE, DEP_PHONENO, COLLEGE)
COURSE (COURSE_NAME, COURSE_DESC, @COURSE_NO, COURSE_DEPT)
Making appropriate assumptions show all the functional dependencies that hold among attributes.
A relation R(A,B,C,D) has functional dependency C→B. Is R in 3NF? Justify.
A relation R(A,B,C,D) has functional dependencies B→D, AB→C. Is R in 2NF? Justify.
A relation R(A,B,C,D,E) is in 2NF and has functional dependencies, AB→CE, E→AB, C→D? Is R in 3NF? Justify.

7. Transaction Management and Concurrency Control

What is a transaction? Explain with examples.
What are the different types of operations which can be performed on the transaction? Explain with examples.
Explain the ACID properties and the usefulness of each with examples?
Discuss the actions takes by the read and the write operations on the database.
Discuss the atomicity property of a transaction with suitable example?
What do you mean by consistency of a transaction. Explain with suitable example?
Explain the isolation property of a transaction with suitable example?
Using suitable example explain the durability property of a transaction?
Draw a state diagram and discuss the various states that a transaction goes through during execution?
What happens when
- Transaction is partially committed?
- Transaction is failed?
- Transaction is roll backed?
What is concurrency? Why we need to control concurrency?
Discuss the following with examples-
- Lost update problem
- Dirty Read
- Incorrect analysis problem.
What is a schedule? Explain with example?
What is a serial schedule and a serializable schedule?
Why is serializable schedule considered correct?
Explain the following:-
- Complete schedule
- Non Serial Schedule
- Recoverable Schedules
- Non cascading schedules
- Strict schedules
What do you understand by serializability?
What is the difference between conflict serializable schedules and view serializable schedules.
Define the concepts of Recoverable, cascadeless and strict schedules? Compare them in terms of their recoverability.
What do you understand by concurrency control? What are different techniques of concurrency control?
What is a lock? What are the different types of locks?
Why are the exclusive/shared locks preferable over binary locks?
Explain the two phase locking protocol with examples? How does it gurantees serializibility?
Explain the compatibility relation between shared lock, exclusive lock and unlock?
Using an appropriate example explain the precedence graph? why do we need it?
What is non-two phase locking protocol? Explain with example?
Explain tree protocol with example?
What is a deadlock? Explain deadlock handling techniques.
What are the necessary conditions for deadlock to occur?
Explain the different approaches to prevent a deadlock?
Compare deadlock detection and prevention techniques?
Describe the wait-die and wound-wait schemes for deadlock prevention?
Compare the wait-die and wound-wait schemes for deadlock prevention?
What is Time-out based protocol?
Using appropriate example explain the concept for wait for Graph?
How will you recover from the deadlock?
What are the advantages and disadvantages of Graph based protocol?
What is a timestamp? How does a system generates a timestamp?
Discuss the timestamp ordering protocol? How does strict timestamp ordering differ from basic timestamp ordering?
What is Thomas write rule? Explain with example?
Discuss the concurrency control based on optimistic scheduling?
Discuss the different phase in validation based protocol?
How do optimistic concurrency control technique differ from other concurrency control techniques? Why are they also called validation techniques.
Discuss two multiversion techniques for concurrency control?
Explain using appropriate example the concept of tree locking protocol?

8. Database Recovery Techniques

What is Recovery? Why it is needed?
What is the difference between failure, fault and error?
What are the different types of failures? Discuss.
What is the difference between catastrophic and Non-Catastrophic failures?
Explain the following terms:-
- Volatile storage
- Non-volatile storage
- Stable storage
What do you mean by ‘RAID’ Why is it used?
What is a system log?
What is the purpose of system log in the system recovery? What are typical entries made in the log?
What is meant by log based recovery techniques? What are the different types of log based recovery?
What are the similarities and differences between immediate update and deferred update? Why are the called so?
Compare the two log based recovery schemes on terms of ease of implementation and overhead cost?
What is Database Cache? What is its use?
What is the use of dirty bit and pin-unpin bit?
What do you mean by in place updating and shadowing. Explain with proper diagram?
What is write-ahead logging?
What is the difference between BFIM and AFIM?
What is the difference steal and No steal Approach?
What do you mean by Force and No-Force approaches?
What is the advantage of using Steal/No Force approach?
What is the purpose of checkpointing techniques?
What are the steps performed during checkpointing?
How does the frequency of checkpointing affect:
- System performance when no failure occurs?
- Time it takes to recover from system crash?
What do you understand by Transaction Rollback?
Why is backward scanning preferred in transaction rollback instead of forward scanning?
Explain the different recovery techniques?
What do you mean by deffered update technique? How is it performed? Give proper examples?
What do you mean by Immediate update technique? How is it performed? Give proper examples?
Explain the Undo/Redo Logging Scheme?
What is Shadow Paging? How it is performed?
What are the advantages and disadvantages of Shadow Paging?
How are databases recovered from catastrophic failures?
What is the difference between Current Page Table and Shadow Page Table?
Compare the Shadow Paging Recovery scheme with the log based recovery schemes in terms of ease of implementation and overhead cost?
What is meant by forward and backward recovery technique?
Explain the recovery procedure that needs to take place after a disk crash?
What do you understand by term Fuzzy Checkpoints?
Explain undo/redo logging recovery for the following log as it appears at three instances of time:

9. Database Security and Integrity

What is database security? Why is database security important for an organization?
What is a threat? What are the potential threats in a database system?
What do you mean by eavesdropping and data theft?
What is a data tampering?
How are user’s identity falsified?
How do we people gain unauthorized access to database objects?
Explain the different types of defence mechanisms?
What are the basic data security requirements?
What do you mean by confidentiality of data?
How is confidentiality of data ensured in a database system?
What do you mean by access control?
What do you mean by database availability and what are its different aspects?
How can you protect data within a database?
What are the differences between discretionary access control and mandatory access control?
What do you understand by authorization? How are users authenticated to the database?
What is a privilege? What are the different types of privileges?
What is the difference between the object privilege and a system privilege?
What are roles? What are advantages of roles?
What is a view? How does it helps in security of data?
How does DBA ensures database security?
What is Data Encryption? What are the different techniques used for encryption of data?
How is the data encrypted in a conventional technique i.e. DES?
What are the different techniques used for data encryption?
Explain the public key encryption technique?
What is a statistical database? How can the statistical database security be enforced?
What do you mean by database integrity?
What are the Integrity constraints? What are the different types of integrity constraints.
Differentiate between database security and database integrity?
Explain the terms:-
- Domain Integrity Rules
- Plaintext
- Public key
- Private key
- Domain

10. Distributed Databases

What is a distributed database? What is the need of distributed database?
What is a distributed database system?
What do you mean by distributed database management system? What are its functions?
What are the components of a DDBMS?
Explain the distributed system with the help of an example?
What is the difference between distributed processing and DDBMS?
Compare DDBMS with parallel processing?
Does shared nothing architecture resembles distributed database environment? If yes then how?
What are the advantages and disadvantages of a DDBMS?
What is the difference between between homogeneous and heterogeneous DBMS?
What are the difference issues that are considered while designing a distributed database?
What is a fragment of a relation? What are the main types of fragments?
Why is fragmentation of a useful concept in distributed database design?
What is the difference between horizontal and vertical fragmentation?
What issues should be considered for the correctness of fragmentation?
What are the advantages and disadvantages of fragmentation?
Why is data replication useful in DDBMS?
What are the different techniques for replicating a database?
What are the advantages and disadvantages of data replication?
What is meant by data allocation in distributed database design?
What are the different types of strategies used while allocating the data?
What is a Transparency? What are the different types of Transparencies?
Explain the following terms:-
- Distribution transparency
- Fragmentation transparency
- Replication transparency
- Location transparency
- Naming transparency
How is reliability and availability improved with distributed databases?
Explain the following concepts
- Remote transaction
- Distributed transaction
Explain the performance transparency?
How will you evaluate query optimization in Performance transparency?
Compare and contrast the client/server architecture and DDBMS?
Explain the date’s twelve rules for DDBMS?
What are the applications of distributed database?

11. Object Oriented Databases

What is the need for OODBMS?
How have the computer related areas benefited from object orientated concepts?
Explain the terms- Object, Object Identity, attributes, objects, state, methods, messages.
What is the difference between object and classes?
What do you mean by abstraction and Encapsulation?
What is Inheritance? What are the different types of Inheritance?
Explain the difference between superclasses and subclasses with proper examples?
Explain the concept of polymorphism.
Compare the concept of ER Model and OO Model?
How do you map object classes to tables? What are the different approaches used for this purpose.
What do you mean by Object oriented DBMS?
What are the applications of OODBMS?
What are the different criteria used by DBMS to become OODBMS?
How object orientation effects the database design?
What are the advantages and disadvantages of OODBMS?
What do you mean by Object relational DBMS?
What are the advantages and disadvantages of ORDBMS?
Differentiate between RDBMS, OODBMS, ORDBMS?

12. Expert Systems

What is knowledge? What are different types of knowledge?
What is the difference between knowledge and data?
What are the different types of knowledge representation schemes?
Write short notes on
- Semantic networks
- First order predicate logic
- Frames
- Rule based system
What is an Expert System? What are the characteristics of expert system?
What is an expert system shell?
What are the different types of expert systems?
Discuss the Expert System Architecture.
What do you understand by
- Forward chaining
- Backward chaining
What are the applications of Expert System?
Name a few classical expert systems?
What are the advantages of Expert system?
Discuss the disadvantages of Expert system?
Compare Expert System with conventional programs and human experts.
What is knowledge Base Management System?
Discuss the various types of reasoning facilities available?
Compare KBMS and DBMS?

13. Introduction to Oracle

Discuss the history of Oracle?
Who developed Oracle?
What are different products available in the Oracle family?
What are the different types of architectures available for oracle database server?
Discuss the Oracle Client/Server architecture?
Discuss the various sections of Multitier Architecture? When it is used?
What are the advantages of Client/Server computing?
Discuss the features of Oracle?
What are the various tools provided by the Oracle?
What are the system requirements for the installation of Oracle 8i Enterprise Edition?
Discuss the steps for installation of Oracle 8i Enterprise Edition?
How will you remove Oracle 8i Enterprise Edition?
What is a SQL*PLUS? How will you run it?
How will you run SQL*PLUS on Unix platform?
Discuss the various types of commands executed from SQL*PLUS prompt?
List the SQL*PLUS commands?
Name a few default users and their passwords?

14. Understanding Oracle Architecture

What are the main components of Oracle Server?
What are the Oracle database structure consists of?
Explain the following logical structures:
- Tablespace
- Segment
- Extent
- Data blocks
Explain the following physical database structure:-
- Data Files
- Redo Log Files
- Control Files
Explain the Oracle Memory structure?
What is a SGA? What are its components?
What is PGA?
What is a background process? Explain the various background process?
How does Oracle Server works in a dedicated server configuration?
How does Oracle Server works in a multithread configuration?

15. SQL Fundamentals

What is a SQL? Discuss in brief history of SQL?
What do you understand by SQL1, SQL2 and SQL3?
Discuss the following:-
- DDL statements
- DML statements
- DCL statements
- TCL statements
What do you mean by 3GL and 4GL?
What are the rules when writing SQL statements?
Discuss the various Oracle data types?
What is the difference between CHAR and VARCHAR2 datatype? Which one is preferred?
Explain the format of NUMBER datatype in terms of precision and scale?
What is the purpose of using LOB data types?
What considerations should be considered while designing tables.
What rules should be followed while naming tables?
What is the purpose of CREATE TABLE statement? Discuss its Syntax.
What is a constraint? Why we use it?
What are the different types of constraints used with CREATE TABLE statement?
What are properties that a primary key constraint should specify?
What is a foreign key? Why do we use it.
What is a difference between Primary Key constraint and Unique Key constraint?
What is a check constraint? How do you specify it?
How do you specify a default value for a column?
How can you view the constraints created?
How can you Create table using existing table?
What is the purpose of ALTER TABLE statement?
How can you add column and constraints to your table?
How can you modify columns in an existing table?
What is the purpose of CASCADE option in the ALTER TABLE statement.
How can you drop a table?
How can a table be renamed?
What is a Data Dictionary? What does it contains? Name the one existing in Oracle.
What is the difference between DELETE, DROP and TRUNCATE?
What is the purpose of INSERT statement?
What points should be kept in mind while inserting the data into the tables.
What is a substitution variable? How can you add rows using substitution variables.
What is the purpose of UPDATE statement?
What is the difference between DELETE statement and TRUNCATE TABLE statement?
Discuss various TCL statements?
What is the purpose of ROLLBACK and COMMIT statements?
What is a SAVEPOINT? Explain with the help of an example?
What does the SET TRANSACTION statement do?
Discuss the common errors that occurs while using DDL, DML, DCL and TCL statements.
Create a table STUDENT having columns STU_ID(Number), SNAME(Varchar2), CLASS (Varchar2), DOB(date), PHONE_NO (Number). Use the following DDL statements on it.
- Set the STU_ID as primary key?
- Insert 5 records into it.
- Delete any record.
- Update the PHONE_NO of any student.
- Set the NOT NULL constraint for DOB Column.
Create a BIRTHDAY table having columns
NAME(Varchar2), DOB(date), Time_OB(date), ENAME(Varchar2), Place(Varchar2), Sex(Char)
- Set the Name, DOB as primary key?
- Insert five records into it?
- Delete any record.
How will you insert image stored at ‘C:\mango.gif’ into a EMPLOYEE table having fields photo (datatype is BFILE)?
(Hint:
SQL> Insert into EMPLOYEE (Photo) values
(bfilename (‘c:\’,’mango.gif’));
)
How will you insert current time into a table?
(Hint:
Step1- Alter session set nls_date_format = ‘dd-mon-yy hh:mi;ss’;
Step2- Insert into student (dot) values (to_date (to_char (sysdate, ‘hh:mi:ss’), ‘hh:mi:ss’));
)

16. SQL Fundamentals

What is an operator? Discuss the various categories of operators available in SQL?
Discuss the various comparison operators available in SQL?
Write short notes on:
- Logical operators
- Concatenation operator
- Set operators
What is Precedence? Discuss the precedence of operators?
Discuss the syntax of SELECT statement with various clauses?
Explain with the help of example how the WHERE clause in the SELECT statement with various clauses?
What is Aliasing? Why is it used?
How can you use expressions in the SELECT statement?
Explain using example the purpose of the following:
- Concatenation operator
- LIKE operator
- IN operator
- BETWEEN operator.
Explain the purpose of the ORDER BY clause?
What is a DUAL table? Where it is used?
What are the different types of SQL built in functions?
What is the purpose of the number functions? Explain atleast four number functions.
What is the difference single row and group functions.
Explain any four character functions?
Explain any two date functions?
What are conversion functions? How do these help us to convert data from one date type to another?
What are aggregate functions? Why are they called so?
What is the use of GROUP BY clause? Explain with Examples.
Explain the use of HAVING clause?
Explain the order of execution of the various clauses in the SELECT statement?
Explain the Oracle tree walking feature with example?
Execute the following queries on EMP table
- List the ENAME of employee whose salary is greater than 2000 from EMP table.
- List the ENAME, JOB, SAL of an Employee whose DEPT_NO=10 and SAL>2000 from EMP table.
- List all the columns of EMP table whose department number is other than 10.
- List all employees of EMP table whose ENAME start with ‘A’ and end with ‘N’.
- List all employees whose JOB post in ‘Clerk’ or ‘Manager’.
- List all employees whose SAL lies between 1250 and 3000.
- List all employees who is getting a commission. (HINT: COMM IS NOT NULL)
- List all the employee’s according to the date of joining.
- List the last three characters of ENAME of each employee.
- List all the names and EMPNO’s as ‘<EMPNO> name is <ENAME>’.
- List hiredate’s of each employee in the format ‘DD/MM/YYYY’.
- List the number of months between the system’s date and date of joining of the employee.
- List the current system time
  (Hint: SQL>SELECT TO_CHAR(SYSDATE,’HH:MI:SS’) FROM DUAL;)
- Find the date 7 days after the today’s date.
- List the months of joining of each employee.
- Using decode function list job as ‘President’ as ‘P’, ‘Manager’ as ‘M’.
- List all employee information in which null values in COMM column is changed into-99.
- List all maximum, minimum and average of Salary.
- List the sum SAL and COMM of each of the employee.
- Count the number of JOB titles in EMP tables.
- Calculate the total number of employees in each department except the DEPTNO=30.
- Find all the department number where total number of employees equal to 4.
- Create a tree with EMP NO starting with 7566.
- List all the employees who joined in 1981, department wise.
- List all the employees who joined in 1987. (HINT: LIKE ‘%87’)
- List employees who are not clerks.
- List all the employees who do not have a manager.
- List all employees who are not working in any department.
- List all employees who have two L’s in their name.
- List employees information such that HIREDATE is in the format “Fifth of June, 2008”.
- List the HIREDATE and employees’s review date if review date is first Monday after three months of service.
  [Hint: TO_CHAR (NEXT_DAY (ADD_MONTHS (HIREDATE,3),’MONDAY’),’MON-DAY-YYYY’)]
- List the MGR and the salary of the lowest paid employees for that
  manager. Exclude anyone whose manager is not known.
Consider a Supplier_Parts database. Create three tables having columns as given:
SUPP Table (SID, SNAME, LOCATION) where SID is the Primary Key.
PARTS Table (PID, PNAME, COLOR, WEIGHT, CITY) where PID is the primary key.
SUP_PART Table (SID, PID, QUANTITY) where (SID, PID) is the primary key and SID and PID are foreign keys to SUPP and PARTS tables respectively.
Now insert 5 records in each table and run the following queries
- List all the parts information?
- Get the total quantity of part P1 supplied by supplier S1?
- List are parts information whose color is ‘Red’?
- List all the suppliers whose name start with ‘S’ and whose location ends with ‘R’?
- Get the total quantity supplier by each supplier?
- Get the total quantity whose supplier is ‘S2’ and part is ‘P2’?
Create a matrix query to display the Job, Salary for that Job based upon department number and the total salary for that job for all departments, giving each column and appropriate headings.
[Hint: SQL> SELECT JOB, SUM (DECODE (DEPTNO, 10, SAL)) AS “DEPT 10”,
SUM (DECODE (DEPTNO, 20, SAL)) AS “DEPT 20”,
SUM (DECODE (DEPTNO, 30, SAL)) AS “DEPT 30”,
SUM (DECODE (DEPTNO, 10, SAL, NULL, 0, 0)) +
SUM (DECODE (DEPTNO, 20, SAL, NULL, 0, 0)) +
SUM (DECODE (DEPTNO, 30, SAL, NULL, 0, 0)) AS TOTAL
FROM EMP GROUP BY JOB;
]
Create a query that displays the total number of employees and also the total number of employees hired in 1980, 1981, 1982 and 1987.
[Hint: Select Count (*)
SUM (DECODE (TO_CHAR(HIREDATE, ‘YYYY’), 1980, 1, 0))
…………………….
…………………….
FROM EMP;
]

17. SQL Fundamentals

What is a Join? How can we join multiple tables?
What is a Equi Join?
Differentiate between Equijoin and Cartesian Join? Explain with Example.
What are the various types of join?
What is a Self Join? Explain with examples.
Discuss the various set operators with examples?
What is a subquery? What are different types of subqueries?
How does single row subquery differ from multiple row subquery?
What are the various operators which we can use in multiple row subqueries?
What is a Correlated subquery? Give examples.
What is the purpose of Exists Operator?
Execute the following queries on EMP and DEPT tables.
- List EMPNO, ENAME, SALE (including COMM) AND DNAME of all the employees.
- List employee information of all the employees who work in ‘CHICAGO’.
- List all employees who do not have a manager.
- List all employees who do not work in the same department as that of its MGR.
- List all employees who have been hired after employee ‘CLARK’.
- List all employees who earn more than the average salary of all the employees.
- List all employees which is same as those of employees whose name end with ‘R’.

**18. Advanced SQL*PLUS and Reports**

What is a command buffer?
What is a Scripts? What are its advantages.
Explain the following SQL * PLUS commands-
- SAVE
- GET
- RUN
- START
Write a short note on ED command?
What is the difference between @ and @@?
What is purpose of SQL * PLUS editor?
Explain the commands used in SQL * PLUS editor?
Write short note on SQL * PLUS system variables?
Explain the different SQL * PLUS system variables?
What is a Report? How do you generate a report in SQL * PLUS?
What are the various command used for report writing?
Explain the following command used in report writing:-
- Title
- Btitle
- BREAK
- SPOOL
- COMPUTE
What is a substitution variable? What are the various types of substitution variables?
What is the difference between Site profiles and user profiles.
Explain the difference between SQL and SQL * PLUS.
Create a report job wise for EMP table. Also print the total salary.
Create a report for printing the employees of a particular department pagewise?

19.Views, Sequences, Indexes and Synonyms

What is a view? Why do we need it? Explain with example.
What is the syntax of creating a view? Using an example show how to create a view.
What is a Join view? Explain.
Why do we use ‘Replace View’ in the commands for creating a view?
What are the uses of view? Explain.
How can you change the view definition.
What is an index?
What does ROWID consists of?
What are the various types of indexes?
How do you create an index? Explain with the help of exmaple?
What are the steps for searching a particular value in Index unique scans?
How do you explain a unique index?
Explain using example the composite index?
How do you drop an existing index?
What points should be remembered while using indexes?
What is a sequence? Explain its syntax?
What is the purpose of CURRVAL and NEXTVAL?
What are the uses of sequences?
How do you alter a sequences?
What is a synonym? What are its uses.
How do you drop a synonym?
Create a view called EMP_VU based on the employee number, employee name, and department number from the EMP table. Change the heading for the employee name to EMPLOYEE.
Select the ‘view_name’ and ‘text’ from the data dictionary USER_VIEWS.
Using the EMP_VU view, enter a query to display all employees names and department numbers.
Create a view called SALARY_VU based on the employee name, department name, salary and salary grade for all employees lable the columns Employee, Department, Salary and Grade respectively.
Create a sequence to be used with the DEPT table primary key columns. The sequence should start at 60 and have a maximum value of 200. Your sequence should increment by 10 mumbers. Name the sequence DEPT_ID_SEQ.
Create a non-unique index on the FOREIGN KEY column in the EMP table.
Display the INDEX_NAME, TABLE_NAME, UNIQUENESS columns that exist in the USER_INDEXES data dictionary view for the EMP table.
Create a synonym in Scott schema who access the table STUDENT created by USER ‘Anshu’?

20. Managing Users and Roles

What is a user? How can we create it?
Write the syntax of the command for creating a user? Explain.
How can you view all the users created?
How can you alter the user?
How can you delete a user?
What is a privilege? Explain its types.
How does object privilege differs from the system privilege?
Explain with the help of example the steps for granting and revoking system privileges?
Using example explain the steps for granting or revoking object privilege?
What is a role? What are the benefits of using roles?
How do you create a role?
Write the steps for granting a role to another role?
How do you enable and disable roles?
How do you revoke privileges from a role?
How do you delete a role?
What is a data dictionary? What are the various types of data dictionary views available?
Create a Role ‘Clerk’ and give the roles CONNECT, RESOURCE to him?
Create a Role ‘Analyst’ and give the update privilege on all the columns except salary.

21. Introduction to PL/SQL

What is PL/SQL? What are its advantages?
Explain the architecture of PL/SQL?
Explain the structure of PL/SQL?
What are the basic elements of PL/SQL?
How can you specify comments in PL/SQL?
What is a literal? What are the various types of literals?
What operators are used in PL/SQL?
What data types are supported in PL/SQL?
What are the various Number data types? Explain.
What is a variable? How do you declare it?
What is a constant? How do you declare it?
What restrictions hold while declaring the variables?
Which variable attributes are used in PL/SQL?
What is the purpose of %TYPE from % ROWTYPE attributes.
How does %TYPE from % ROWTYPE attributes.
How do you display messsages in PL/SQL?
Write a program to calculate the sum of two numbers.
Write a program to Insert and then delete that row from the EMP table?
What are the difference between SQL and PL/SQL?
What are different methods of running a program.
Determine whether the following are legal or not?
- DECLARE
  v Number (4);
- DECLARE
  JOIN_DATE DATE NOT NULL;
- DECLARE
  A, B, C, VARCHAR2(10);
- SUM : = $100 + $200;
- a : = b > (3 * c);
- days_lef : = a_date – sysdate;
Create a PL/SQL block that selects the maximum department number in the DEPT table and print the result on the screen?

22. Control Structures

What is conditional control? What are the various types of conditional control statements used in PL/SQL?
Explain the working of IF-THEN-ELST statement with the help of an example.
Explain the working of IF-THEN-ELSEIF statement with the help of an example.
What are Iterative control statements? Explain.
Explain the working of SIMPLE loop statement with example?
What is the purpose of EXIT and EXIT WHEN statements? Do they differ?
Explain the working WHILE LOOP statement with the help of an example?
What is the purpose of SET SERVEROUTPUT ON in PL/SQL program’s execution?
Expalin the working of FOR LOOP statement with the help of example.
What points should be kept in mind while using FOR loop statement?
What is the purpose of Loop labels? Explain with the help of examples.
What are sequential control statements?
What is the purpose of GOTO statement? Explain using an example.
What points should be kept in mind while using GOTO statements.
What is NULL statements used for?
Write a PL/SQL program to calculate the simple interest?
S.I. = P*r*t/100
Write a PL/SQL program to calculate the telephone bill according to the number of calls made?
Print the series using PL/SQL program
1
22
333
[Hint : Using PUT and NEW_LINE procedures of DBMS_OUTPUT package]
Write a PL/SQL program to print the Ename of the employee from EMP table if the SAL of the given empno is greater than 3000?
Write a PL/SQL program to print the digits of a number in reverse order?
Write a PL/SQL program to calculate the factorial of a given number?

23. Handling Errors and Exceptions

What is an Exception? When is it raised?
What happens when no execution handler is declared in the block and error occurs?
With the help of syntax of exception section discuss it?
What are the different types of exceptions?
List a few predefined exceptions?
What are user-defined exceptions?
How do you declare user-defined exceptions?
Discuss the scope rules of user defined exceptions?
What is the purpose of Pragma EXCEPTION?
How do you use raise_application_error procedure?
What are the purpose of SQL CODE and SQLERRM in handler clauses?
Using a example show how do exception propagates?
What do you mean by reraising an exception? Explain with the help of example?
How are exception raised in declarations? Explain with the help of examples.
Explain the difference between predefined exceptions and user-defined exceptions.
Write a PL/SQL program that handles TOO_MANY_ROWS exception?
Write an exception handler for the error to pass a message to the user that the specified department does not exist?
Write a PL/SQL block that displays the name of the employee whose salary is between two given ranges. Handle all types of exceptions that can occur during the execution of the block?
Write a PL/SQL block to select the name of the employee with a given salary value.
- If the salary entered returns more than one row, handle the exception with an appropriate exception handler an insert into the MESSAGES table, the message “More than one employee with salary of <salary>”.
- If the salary entered does not return any rows, handle the exception with appropriate exception handler and insert into the MESSAGES table, the message “No employee with salary of <salary>”.

24. Retrieving Data with Cursors

What is a Cursor? Why do we need it?
Explain the functioning of cursors?
What are the various types of cursors?
What are cursor attributes? Explain the importance of each?
What are implicit cursors? How do you handle it?
Explain the various attributes of Implicit Cursors?
What is an Explicit Cursor? Explain the various steps for handling them?
How do you open an Explicit cursor?
How do you fetch an Explicit cursor?
Explain the Explicit cursor attributes?
What is the importance of cursor FOR loop? How does it simplifies operations?
Explain with the help of example how can you select statement in cursor FOR loop?
How can you pass parameters to cursors? Explain with the help of example.
What are the advantages of using parameters?
How will you pass parameters in a cursor FOR loop? Explain using example.
Write a PL/SQL program to display the total salary jobwise using cursors?
Write a PL/SQL program to display the names of the employees starting with a given character?
Write a PL/SQL program to display the message ‘Cursor is open’ each time the cursor is opened and after closing it, display the message ‘Cursor is closed’.
Write a PL/SQL program which prompts the user to enter the range limits of the salary i.e. upper value and lower value and displays the records that lies between that range.

25. Procedures, Functions and Packages

What is a Subprogram? What are its types?
What is Modularization? Explain.
What are the parts of a subprogram?
What are the advantages of using subprograms?
What is a Procedure?
How do you declare a local procedure? Explain with the help of example.
What are the different modes of parameters? Compare each mode.
What is the difference between actual and formal parameters. Explain using an example.
What is a stored procedure? What is the use of Replace clause in it?
Using an example explain the working of stored procedure?
How can you execute a stored procedure?
What is the use of function? Write the syntax to create local and stored functions?
Using an example explain the significance of Return statements.
What is the difference between Procedure and a Function?
What is a package? What are its important components?
Create a package and explain its working?
How can we reference the package contents.
How can you alter the contents of existing package?
What are the advantages of Packages?
How can you drop a function?
How can you drop a Procedure?
What is the significance of USER_OBJECTS data dictionary view?
Write a function to calculate the area of the Circle.
Write a procedure to fetch information about employees who is getting commission?
Write a function that returns the balance of a particular account number?
Create a procedure that increases the salary of the employee by 5000 who have been working for more than four years.
Create a package STUDENT which consists of procedure that updates the marks of the students and a function that returns the percentage of marks of a given student.

26. Database Triggers

What is a database Trigger?
What functions are performed by Triggers?
Give the syntax for creating a Trigger?
Explain the in brief the various components of a Trigger?
What are the various types of Triggers?
Explain the difference between Row and a Statement trigger?
Write a short note on before and after triggers?
What restrictions apply on triggers?
How do you access the old and new values of columns in Row Triggers?
What are Conditional Predicates? Why do we use it?
How do you drop a trigger?
How do you enable and disable triggers?
What trigger information can you get from USER_TRIGGERS data dictionary view?
Create a trigger which when fired displays the difference between updated salary and the old salary for an employee?
Create a trigger which is fired each time a record is inserted in SALE table. The trigger updates the quantity on hand field in the ITEM table by subtracting the quantity sold from it.
Create a trigger which is fires each time when quantity on hand in INVENTORY table drops below the recorder point. In that cans a part with given quantity and that data is recorded. The PENDING_ORDERS maintains this record.
Write a trigger on the EMP table so that no insertion or updation takes place on the table, on Sunday? (This is a typical example showing how database triggers help to control security).
[Hint: ……..
CREATE OR REPLACE TRIGGER check_time
BEFORE INSERT ON Emp
BEGIN
IF (TO_CHAR(SYSDATE, ‘DY’) IN (‘SAT’,’SUN’)) OR (TO_CHAR (SYSDATE, ‘HH24;MT’) NOT BETWEEN ’09:00′ AND ’17:00′)
THEN RAISE_APPLICATION_ERROR (‘-20010’, ‘Insertion possible during business hours only’);
END IF;
END;
/
]
Create a trigger on EMP table which will ensure that the salary entered is valid (i.e. less than 10000).

27. Oracle Utilities

What is a backup? What are the different types of backups.
What is an Export Utility? What are the various types of parameters which can be used with it?
What are the different ways of using the Export Utility.
Discuss the role of Parfile, Inctype, Grants parameter of export utility?
How will you Export the entire database to the different user?
What is the purpose of Import Utility in Recovery of the database?
Discuss the role of Full, From User and To User Parameters of Import Utility?
Export the ‘Emp’ and ‘Dept’ tables of the ‘Scott’ user such that all the constraints are also copied to the user ‘Anshu’. (Which is created).
Export the contents of both the users ‘Anshu’ and ‘Scott’ to the user ‘Deepak’?

You may also like:

List of Most Important 500+ DBMS Interview Questions

1. Introduction to DBMS

2. Data Models

3. Entity-Relationship Model

4. Relational Database Management System

5. Relational Algebra and Relational Calculus

6. Normalization

7. Transaction Management and Concurrency Control

8. Database Recovery Techniques

9. Database Security and Integrity

10. Distributed Databases

11. Object Oriented Databases

12. Expert Systems

13. Introduction to Oracle

14. Understanding Oracle Architecture

15. SQL Fundamentals

16. SQL Fundamentals

17. SQL Fundamentals

**18. Advanced SQL*PLUS and Reports**

19.Views, Sequences, Indexes and Synonyms

20. Managing Users and Roles

21. Introduction to PL/SQL

22. Control Structures

23. Handling Errors and Exceptions

24. Retrieving Data with Cursors

25. Procedures, Functions and Packages

26. Database Triggers

27. Oracle Utilities

Sarcastic Writer

Leave a Reply Cancel reply

1. Introduction to DBMS

2. Data Models

3. Entity-Relationship Model

4. Relational Database Management System

5. Relational Algebra and Relational Calculus

6. Normalization

7. Transaction Management and Concurrency Control

8. Database Recovery Techniques

9. Database Security and Integrity

10. Distributed Databases

11. Object Oriented Databases

12. Expert Systems

13. Introduction to Oracle

14. Understanding Oracle Architecture

15. SQL Fundamentals

16. SQL Fundamentals

17. SQL Fundamentals

18. Advanced SQL*PLUS and Reports

19.Views, Sequences, Indexes and Synonyms

20. Managing Users and Roles

21. Introduction to PL/SQL

22. Control Structures

23. Handling Errors and Exceptions

24. Retrieving Data with Cursors

25. Procedures, Functions and Packages

26. Database Triggers

27. Oracle Utilities

Related Posts

Leave a Reply Cancel reply

**18. Advanced SQL*PLUS and Reports**