DATABASE MANAGEMENT SYSTEM’S

VIVA QUESTION’S

1.What is SQL?
2.what is entity and relationship?
3.What is DBMS?
4.What is a Database system?
5.Advantages of DBMS?
6.How many types of database languages are available?
7.Describe the three levels of data abstraction?
8.Define the "integrity rules"
9.What are the usage of SQL?
10.What is the use of SELECT?
11.What is the difference between SUM and COUNT ?
12.What is VIEW ? and What will you get when you use VIEW
13.What is difference between TRUNCATE and DELETE?
14.Define Normalization?
15.What is 1 NF (Normal Form)?
16.What is Fully Functional dependency?
17.What is 2NF?
18.What is 3NF?
19.What is BCNF (Boyce-Codd Normal Form)?
20.What is DDL (Data Definition Language)?
21.What is VDL (View Definition Language)?
22.What is SDL (Storage Definition Language)?
23.What is DML (Data Manipulation Language)?
24.What is DML Compiler?
25.What is PL/SQL?
26.What are DML commands?
27.Write the syntax for insert command?
28.What is the syntax for update command?
29.Write the syntax for delete command?
30.Write the syntax for select command?
31.Difference between DCL and TCL ?
32.What is the result of String functions?
33.What is the result of Date functions?
34.What is the result of conversion function?
35.What is Concatenation?
36.What is the difference between LTRIM and RTRIM?
37.What is the difference between SUM and COUNT ?
38.What will you get when you use MIN ?
39.What will you get when you use MAX ?
40.What is VIEW ? and What will you get when you use VIEW
41.What is difference between DROP table and DELETE?
42.What will you get when you use AVG?
43.What is TRIGGER?
44.What is BEFORE Trigger?
45.What is AFTER Trigger?
46.What is the difference between BEFORE and AFTER?
47.What is ROW and Statement Triggers?
48.What is INSTEAD Triggers?
49.What are the types on Triggers?
50.It is possible to create Trigger on Views?
51.What is a stored procedure?
52.When would you use stored procedure or functions ?
53.What are external procedures?
54.What is input parameter?
55.How to use Stored Procedures ?
56.What is a cursor?
57.What are the types of cursor?
58.What is the use of parameterized cursor?
59.What is the use of cursor variable?
60.What is a normal cursor?
61.What are Explicit cursor attributes ?

ANSWERS :

1. What is SQL?
SQL (Structured Query Language) is a domain-specific language designed for
managing and manipulating relational databases. It helps in performing tasks
like querying, updating, inserting, and deleting data within a database.

2. What are Entity and Relationship?

An entity is any object, person, or concept about which data can be stored (e.g.,
Students, Employees). A relationship is the association between entities (e.g.,
Students taking Courses, Employees working in Departments).
3. What is DBMS?
A Database Management System (DBMS) is software that allows users to
define, create, maintain, and control access to a database. Examples include
MySQL, PostgreSQL, and Oracle.\

4. What is a Database System?

A database system refers to an organized collection of databases and the
software used to manage them. It includes the DBMS and the applications
interacting with the DBMS.

5. Advantages of DBMS
1. Data Redundancy Control: Reduces duplicated data.
2. Data Integrity: Ensures data accuracy and consistency.
3. Data Security: Restricts unauthorized access.
4. Concurrency Control: Manages simultaneous data access.
5. Data Backup and Recovery: Provides safeguards against data loss.

6. How Many Types of Database Languages are Available?

There are primarily four types of database languages:
 DDL (Data Definition Language): Defines the schema (e.g., CREATE,
ALTER)
 DML (Data Manipulation Language): Manages data (e.g., INSERT,
SELECT).
 DCL (Data Control Language): Controls access (e.g., GRANT,
REVOKE).
 TCL (Transaction Control Language): Manages transactions (e.g.,
COMMIT, ROLLBACK).

7. Describe the Three Levels of Data Abstraction

1. Physical Level: Describes how the data is physically stored (e.g., storage
devices, formats).
2. Logical Level: Describes what data is stored and the relationships among
them (e.g., tables, attributes).
3. View Level: Describes the user's interaction with the database, hiding the
complexity of the data (e.g., user interfaces, custom views).

8. Define the "Integrity Rules"

There are two main integrity rules in databases:
 Entity Integrity: Ensures that each table has a unique primary key, and
no primary key value is null.
 Referential Integrity: Ensures that a foreign key in one table
corresponds to a valid primary key in another table.
9. What Are the Usages of SQL?
SQL is used for:
 Retrieving data from databases (e.g., SELECT).
 Manipulating data (e.g., INSERT, UPDATE, DELETE).
 Defining database structures (e.g., CREATE, ALTER, DROP).
 Controlling access to the database (e.g., GRANT, REVOKE).

10. What Is the Use of SELECT?

The SELECT statement retrieves data from one or more tables. It’s used for
querying specific columns and rows based on conditions.

11. What Is the Difference Between SUM and COUNT?

 SUM: Adds up all the numeric values in a column (e.g., total salaries).
 COUNT: Counts the number of rows or entries in a column (e.g., number
of employees).

12. What Is a VIEW, and What Do You Get When You Use It?
A VIEW is a virtual table created based on a SELECT query. It doesn't store
data but provides a simplified representation of data. By using a view:
 You can simplify complex queries.
 You enhance data security by showing only specific data to users.

13. What Is the Difference Between TRUNCATE and DELETE?

 TRUNCATE: Removes all rows from a table without logging individual
row deletions (faster, no rollback).
 DELETE: Removes selected rows and logs each deletion, allowing a
rollback.

14. Define Normalization

Normalization is a process of organizing data in a database to:
1. Minimize redundancy.
2. Ensure data integrity.
3. Optimize storage efficiency.

15. What is 1NF (Normal Form)?

The First Normal Form (1NF) ensures that a database table has a unique
identifier (Primary Key) and that all columns contain atomic values
(indivisible). It eliminates duplicate columns and repeating groups.

16. What is Fully Functional Dependency?

Fully functional dependency occurs when a non-key attribute is functionally
dependent only on the whole primary key, and not a subset of it. This ensures
the attribute is uniquely determined by the primary key.
17. What is 2NF (Second Normal Form)?
Second Normal Form (2NF) builds on 1NF by:
1. Eliminating partial dependencies (attributes dependent on part of the
primary key).
2. Ensuring that non-key attributes are fully dependent on the primary key.

18. What is 3NF (Third Normal Form)?

Third Normal Form (3NF) ensures that:
1. The table is in 2NF.
2. There is no transitive dependency, meaning non-key attributes should not
depend on other non-key attributes.

19. What is BCNF (Boyce-Codd Normal Form)?

BCNF is an enhanced version of 3NF. It ensures:
1. There are no redundancies in the functional dependencies.
2. All determinants in the table are candidate keys.

20. What is DDL (Data Definition Language)?

DDL is a set of SQL commands used to define and manage the database
structure. Examples include:
 CREATE: Creates tables and objects.
 ALTER: Modifies the structure.
 DROP: Deletes objects.

21. What is VDL (View Definition Language)?

VDL is used to specify user views and their mapping to conceptual schema. It's
a part of database architecture but not explicitly used as standalone commands.

22. What is SDL (Storage Definition Language)?

SDL is used to specify the internal schema of the database, such as data storage
structures. It defines how data is stored physically.

23. What is DML (Data Manipulation Language)?

DML includes commands used to manipulate and retrieve data from the
database. Examples include:
 INSERT: Adds data.
 UPDATE: Changes data.
 DELETE: Removes data.
 SELECT: Queries data.

24. What is DML Compiler?

A DML compiler converts high-level queries into low-level instructions that the
database management system can execute efficiently.
25. What is PL/SQL?
PL/SQL (Procedural Language for SQL) is an extension of SQL used in Oracle
databases. It combines SQL commands with procedural constructs like loops
and conditions.

26. What Are DML Commands?

DML (Data Manipulation Language) commands are used to manipulate and
retrieve data from a database:
 INSERT: Adds new records.
 UPDATE: Modifies existing records.
 DELETE: Removes records.
 SELECT: Retrieves data.

27. Write the Syntax for INSERT Command

INSERT INTO table_name (column1, column2, ...) VALUES (value1,
value2, ...);

28. What Is the Syntax for UPDATE Command?

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE
condition;

29. Write the Syntax for DELETE Command

DELETE FROM table_name WHERE condition;

30. Write the Syntax for SELECT Command

SELECT column1, column2, ... FROM table_name WHERE condition;

31. Difference Between DCL and TCL

 DCL (Data Control Language): Controls access to the database
(GRANT, REVOKE).
 TCL (Transaction Control Language): Manages transactions
(COMMIT, ROLLBACK).

32. What Is the Result of String Functions?

String functions perform operations on text data. For example:
 CONCAT(): Combines two strings.
 LENGTH(): Returns the length of a string.
 UPPER() / LOWER(): Converts case.

33. What Is the Result of Date Functions?

Date functions perform operations on dates. For example:
 NOW(): Returns the current date and time.
 DATEDIFF(): Calculates the difference between dates.
 CURDATE(): Returns the current date.

34. What Is the Result of Conversion Functions?

Conversion functions convert data from one type to another. For example:
 CAST() and CONVERT(): Change data types.
 Example: Convert an integer to a string.

35. What Is Concatenation?

Concatenation combines two or more strings into one. For example:
SELECT CONCAT('Hello', ' World'); -- Output: "Hello World"

36. What Is the Difference Between LTRIM and RTRIM?

 LTRIM(): Removes leading (left) whitespace.
 RTRIM(): Removes trailing (right) whitespace.

37. What Is the Difference Between SUM and COUNT?

 SUM: Adds numeric values in a column.
 COUNT: Counts the number of rows.

38. What Will You Get When You Use MIN?

The MIN() function retrieves the smallest value in a column.

39. What Will You Get When You Use MAX?

The MAX() function retrieves the largest value in a column.

40. What Is VIEW, and What Will You Get When You Use It?
A VIEW is a virtual table based on a SQL query. It simplifies data
representation and enhances security.

41. What Is the Difference Between DROP Table and DELETE?

 DROP: Removes the entire table structure.
 DELETE: Removes rows but retains the table.

42. What Will You Get When You Use AVG?

The AVG() function calculates the average of numeric values in a column.

43. What Is TRIGGER?

A trigger is an automated SQL procedure that executes in response to an event,
such as data modification.
44. What Is BEFORE Trigger?
A BEFORE trigger executes before the triggering event (e.g., INSERT,
UPDATE, DELETE) is applied.

45. What Is AFTER Trigger?

An AFTER trigger executes after the triggering event has been applied.

46. What Is the Difference Between BEFORE and AFTER?

 BEFORE: Executes before the event and can prevent changes.
 AFTER: Executes after the event and works with committed data.

47. What Are ROW and Statement Triggers?

 ROW Trigger: Fires once for each affected row.
 Statement Trigger: Fires once per statement, regardless of row count.

48. What Is INSTEAD Trigger?

An INSTEAD trigger executes instead of the triggering event, overriding the
operation.

49. What Are the Types of Triggers?

 BEFORE Trigger
 AFTER Trigger
 INSTEAD OF Trigger

50. Is It Possible to Create Trigger on Views?

Yes, triggers can be created on views but only INSTEAD OF triggers are
supported.

51. What Is a Stored Procedure?

A stored procedure is a precompiled set of SQL queries stored in the database,
which can be executed to perform a task.

52. When Would You Use Stored Procedures or Functions?

They are used for:
 Reducing network traffic.
 Reusing code.
 Improving performance.

53. What Are External Procedures?

External procedures are database functions that invoke programs outside the
database using languages like C, Java, or Python.
54. What Is Input Parameter?
An input parameter passes data into a procedure or function for execution.

55. How to Use Stored Procedures?

Use the CALL statement:
CALL procedure_name(parameters);

56. What Is a Cursor?

A cursor is a database object used to retrieve and manipulate row-by-row results
of a query.

57. What Are the Types of Cursors?

1. Implicit Cursor: Automatically created.
2. Explicit Cursor: Defined by the user for specific operations.

58. What Is the Use of Parameterized Cursor?

Parameterized cursors accept arguments to filter data dynamically.

59. What Is the Use of Cursor Variable?

A cursor variable allows flexibility to assign and reuse cursors dynamically.

60. What Is a Normal Cursor?

A normal cursor processes one row at a time from the query result set.

61. What Are Explicit Cursor Attributes?

Explicit cursors have attributes like:
 %FOUND: True if rows are retrieved.
 %NOTFOUND: True if no rows are retrieved.
 %ISOPEN: Checks if the cursor is open.
 %ROWCOUNT: Returns the count of retrieved rows.