Short, exam-ready answers you can explain orally. Basic and intermediate questions on RDBMS theory, SQL, Oracle, PL/SQL and every practical set. These are practice questions — no question is guaranteed to be asked in a GTU viva.
General RDBMS
Q1. What is a DBMS? How is an RDBMS different?
A DBMS is software to define, store, retrieve and manage data. An RDBMS stores data in related tables (relations) and follows the relational model: keys, relationships, integrity rules and SQL.
Q2. What is a relation, tuple and attribute?
A relation is a table, a tuple is a row and an attribute is a column.
Q3. What are degree and cardinality?
Degree is the number of columns (attributes) of a relation; cardinality is the number of rows (tuples). In ER modelling, cardinality also means the 1:1, 1:N or M:N ratio of a relationship.
Q4. What is data independence?
The ability to change the schema at one level without changing the next higher level: logical (conceptual schema changes without affecting applications) and physical (storage changes without affecting the conceptual schema).
Q5. Explain the three-schema architecture.
Internal level (physical storage), conceptual level (whole database structure for all users) and external level (individual user views). Mappings between levels provide data independence.
Q6. What is the difference between a schema and an instance?
The schema is the structure (design) of the database and rarely changes; the instance is the data stored at a particular moment and changes frequently.
Q7. Define super key, candidate key, primary key and alternate key.
A super key is any attribute set that uniquely identifies rows; a candidate key is a minimal super key; the primary key is the candidate key chosen to identify rows; the remaining candidate keys are alternate keys.
Q8. What is a foreign key?
A column (or set of columns) whose values must match the primary or unique key of another table, or be NULL. It implements relationships and referential integrity.
Q9. What is entity integrity?
The rule that a primary key value cannot be NULL.
Q10. What is normalization? Why is it needed?
The process of organising tables to reduce redundancy and avoid insertion, deletion and update anomalies by decomposing them according to functional dependencies (1NF, 2NF, 3NF, BCNF, 4NF).
Q11. Difference between 3NF and BCNF?
3NF allows X → A when A is a prime attribute even if X is not a superkey; BCNF requires X to be a superkey for every non-trivial dependency, so BCNF is stricter.
Q12. What is a functional dependency?
X → Y means that the value of X determines the value of Y: rows with the same X always have the same Y.
Q13. What is a weak entity?
An entity that cannot be identified by its own attributes; it depends on an owner entity and has only a partial key.
Q14. Explain the ACID properties.
Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions do not interfere), Durability (committed changes survive failures).
Q15. What is a serializable schedule?
A concurrent schedule whose effect is equivalent to some serial (one-after-another) execution of the same transactions. Conflict serializability is tested with a precedence graph: no cycle means serializable.
Q16. What is a cascading rollback?
When one transaction's abort forces other transactions that read its uncommitted data to abort too. Cascadeless schedules avoid it by reading only committed data.
SQL
Q17. What are DDL, DML, DCL and TCL? Give examples.
DDL defines structure (CREATE, ALTER, DROP, TRUNCATE, RENAME); DML manipulates data (SELECT, INSERT, UPDATE, DELETE); DCL controls access (GRANT, REVOKE); TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT).
Q18. Difference between DELETE, TRUNCATE and DROP?
DELETE removes selected rows and can be rolled back; TRUNCATE removes all rows quickly, keeps the structure and cannot be rolled back; DROP removes the table itself.
Q19. Difference between WHERE and HAVING?
WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after GROUP BY and can use aggregates.
Q20. What is the order of execution of a SELECT?
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY.
Q21. What is a join? Name its types.
A join combines rows of two or more tables based on a related column: inner (equi), non-equi, self, left/right/full outer, cross (Cartesian) and natural joins.
Q22. What is a subquery? What is a correlated subquery?
A subquery is a query inside another query. A correlated subquery refers to columns of the outer query and runs once for each outer row.
Q23. Difference between IN and EXISTS?
IN compares a value with a list returned by the subquery; EXISTS only checks whether the subquery returns any row. NOT EXISTS is safe with NULLs, while NOT IN returns no rows if the list contains NULL.
Q24. Difference between UNION and UNION ALL?
UNION removes duplicates; UNION ALL keeps all rows and is faster.
Q25. What is a view? Can we update a view?
A view is a stored query used like a table. Simple single-table views can be updated; views with joins, GROUP BY, DISTINCT or aggregates generally cannot, and WITH READ ONLY views never can.
Q26. What is an index? What is its disadvantage?
A structure that speeds up searching on columns. It uses space and slows down INSERT, UPDATE and DELETE because the index must also be maintained.
Q27. What is NULL? Is NULL equal to zero?
NULL means unknown or missing. It is not zero or a blank; comparisons with NULL are unknown, so use IS NULL / IS NOT NULL.
Q28. What do COUNT(*) and COUNT(column) return?
COUNT(*) counts all rows; COUNT(column) counts only rows where the column is not NULL; COUNT(DISTINCT column) counts different non-NULL values.
Oracle SQL
Q29. What is the DUAL table?
A special one-row, one-column table in Oracle used to select expressions, functions or pseudo-columns, e.g. SELECT SYSDATE FROM dual.
Q30. What is the difference between CHAR, VARCHAR and VARCHAR2?
CHAR is fixed-length and blank-padded; VARCHAR2 is variable-length and recommended; VARCHAR is currently a synonym of VARCHAR2 but reserved by Oracle for possible future changes.
Q31. What are ROWNUM and ROWID?
ROWNUM is a sequential number given to rows as a query returns them; ROWID is the physical address of a row in the database.
Q32. How do you get the top 5 rows in Oracle 10g?
Sort in an inline view and filter in the outer query: SELECT * FROM (SELECT ... ORDER BY sal DESC) WHERE ROWNUM <= 5. FETCH FIRST is only available from 12c.
Q33. How does Oracle generate auto-increment values?
With a SEQUENCE and NEXTVAL (often filled by a BEFORE INSERT trigger). Identity columns exist only from Oracle 12c.
Q34. What does NVL do? What is NVL2?
NVL(a, b) returns b when a is NULL. NVL2(a, b, c) returns b when a is not NULL and c when it is NULL.
Q35. What is DECODE?
Oracle's IF-THEN-ELSE function for equality: DECODE(expr, search1, result1, ..., default). CASE is the ANSI alternative.
Q36. What is SYSDATE?
A function returning the current date and time of the database server.
Q37. What is the (+) operator?
Oracle's traditional outer-join operator, placed on the side of the join condition that may have missing rows.
Q38. What is a substitution variable?
A SQL*Plus variable written as &name that prompts the user for a value before the statement is executed; &&name prompts only once.
Q39. What is the difference between SQL and SQL*Plus commands?
SQL commands (SELECT, CREATE...) are executed by the database and end with a semicolon; SQL*Plus commands (DESC, SET, SPOOL, @script) are interpreted by the client tool.
Q40. What is the recycle bin in Oracle 10g?
Dropped tables are kept in the recycle bin and can be restored with FLASHBACK TABLE t TO BEFORE DROP, unless DROP ... PURGE was used.
PL/SQL
Q41. What is PL/SQL? Why use it?
Oracle's procedural extension of SQL with variables, conditions, loops, exceptions and stored program units. It reduces network traffic by sending whole blocks, and allows business logic to be stored in the database.
Q42. What is the structure of a PL/SQL block?
DECLARE (optional) – BEGIN (mandatory) – EXCEPTION (optional) – END;
Q43. What is the difference between an anonymous block and a stored procedure?
An anonymous block has no name and is compiled each time it runs; a stored procedure is named, compiled once and stored in the database for reuse.
Q44. What are %TYPE and %ROWTYPE?
%TYPE gives a variable the data type of a column or variable; %ROWTYPE declares a record with the structure of a table or cursor row.
Q45. What is the use of DBMS_OUTPUT.PUT_LINE?
It writes a line of text to the output buffer, which SQL*Plus displays after the block when SERVEROUTPUT is ON.
Q46. What are the types of loops in PL/SQL?
Basic LOOP with EXIT/EXIT WHEN, WHILE loop and numeric FOR loop, plus cursor FOR loops.
Q47. What is an exception? Name some predefined exceptions.
A run-time error condition. Predefined: NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX, VALUE_ERROR, INVALID_NUMBER, CURSOR_ALREADY_OPEN.
Q48. What are SQLCODE and SQLERRM?
Functions that return the error number and error message of the most recent exception; used in WHEN OTHERS handlers.
Q49. What is a package?
A schema object grouping related procedures, functions, variables and cursors, with a public specification and a private body.
Q50. Can we use COMMIT inside PL/SQL?
Yes, in blocks and procedures (but not in triggers, unless using an autonomous transaction).
Triggers, cursors, functions and procedures
Q51. What is a cursor? Explain implicit and explicit cursors.
A cursor is a pointer to the result set of a query. Implicit cursors are created by Oracle for DML and SELECT INTO; explicit cursors are declared for multi-row queries and processed with OPEN, FETCH, CLOSE or a cursor FOR loop.
Q52. What are the cursor attributes?
%ISOPEN, %FOUND, %NOTFOUND and %ROWCOUNT (SQL%... for the implicit cursor).
Q53. What is a cursor FOR loop?
A loop that automatically opens a cursor, fetches each row into a record and closes it at the end.
Q54. What is a parameterised cursor?
A cursor that accepts parameters when opened, allowing one cursor definition to be used for different values.
Q55. Difference between a procedure and a function?
A function returns a value through RETURN and can be used in SQL expressions; a procedure performs an action, may return values via OUT parameters and is called as a statement.
Q56. What are IN, OUT and IN OUT parameters?
IN passes a value in (default, read-only); OUT passes a value back to the caller; IN OUT passes a value in and returns a changed value.
Q57. What is a trigger?
A stored PL/SQL block that runs automatically before or after an INSERT, UPDATE or DELETE on a table (or on system/DDL events).
Q58. What is the difference between BEFORE and AFTER triggers?
BEFORE triggers run before the change and can validate or modify :NEW values; AFTER triggers run after the change and are used for auditing or follow-up actions.
Q59. What are :OLD and :NEW?
Pseudo-records in row-level triggers containing the column values before and after the triggering change.
Q60. What is a mutating table error?
ORA-04091: a row-level trigger tries to query or modify the same table that fired it while that table is being changed.
Q61. Can a trigger contain COMMIT?
No; a normal trigger is part of the triggering transaction and cannot commit or roll back (ORA-04092).
Q62. What is RAISE_APPLICATION_ERROR?
A procedure that raises a user-defined error with a number from -20000 to -20999 and a message, stopping the current statement.
Set-wise viva questions
Set 1 — DEPARTMENT Table (open practical)
Q63. What is the difference between CHAR and VARCHAR2?
CHAR(n) is fixed length and pads with spaces up to n characters. VARCHAR2(n) is variable length and stores only the characters entered, so it saves space.
Q64. Is DESC a SQL command?
No. DESC is a SQL*Plus (client) command that shows column names, NULL constraint and data types. The SQL equivalent is a query on USER_TAB_COLUMNS.
Q65. What is the difference between DELETE and TRUNCATE?
DELETE is DML, can use WHERE, fires triggers and can be rolled back. TRUNCATE is DDL, removes all rows, is faster and cannot be rolled back.
Q66. Why do we use COMMIT?
COMMIT ends the transaction and makes all changes made since the last commit permanent and visible to other users.
Q67. What does % mean in LIKE?
It matches zero or more characters. 'A%' finds values starting with A; '%A' finds values ending with A.
Q68. Is BETWEEN inclusive?
Yes, both boundary values are included.
Set 2 — EMPLOYEE and DEPARTMENT (open practical)
Q69. What is the difference between a primary key and a unique key?
Both prevent duplicates. A table can have only one primary key and it cannot be NULL; it can have many unique keys and a unique column may contain NULLs.
Q70. What is referential integrity?
The rule that a foreign key value must match an existing primary/unique key value in the parent table, so there are no orphan child rows.
Q71. Why can't SYSDATE be used in a CHECK constraint?
A constraint must always give the same result for the same row. SYSDATE changes, so a row valid today could be invalid tomorrow. Oracle raises ORA-02436; a trigger is used instead.
Q72. How do you add or drop a constraint on an existing table?
ALTER TABLE t ADD CONSTRAINT name ...; and ALTER TABLE t DROP CONSTRAINT name;
Q73. What does ON DELETE CASCADE do?
When a parent row is deleted, the matching child rows are deleted automatically.
Q74. What are NEXTVAL and CURRVAL?
Pseudo-columns of a sequence. NEXTVAL generates and returns the next number; CURRVAL returns the current value in the session and works only after NEXTVAL has been used once.
Q75. How do you print a single quote inside a string?
Write two single quotes: 'It''s' prints It's.
Set 3 — Salesmen, Customers and Orders (open practical)
Q76. What is a substitution variable?
A SQL*Plus variable written with & (or &&). SQL*Plus prompts for its value and replaces it in the statement before sending it to Oracle.
Q77. Difference between & and &&?
& prompts every time the variable is used; && prompts once and keeps the value for the rest of the session (until UNDEFINE).
Q78. What is the difference between a join and a subquery?
A join combines columns of several tables into one result row; a subquery computes a value or list that the outer query uses in its condition. Q31 solves the same problem both ways.
Q79. What is a correlated subquery?
A subquery that uses a value from the current row of the outer query, so it is executed once for each outer row.
Q80. Difference between UNION and UNION ALL?
UNION removes duplicate rows; UNION ALL keeps all rows and is faster.
Q81. What do > ANY and > ALL mean?
> ANY: greater than at least one value (the minimum). > ALL: greater than every value (the maximum).
Q82. Are = ANY and IN the same?
Yes, both are true if the value matches at least one value in the list.
Q83. What is a self join?
Joining a table to itself, using different aliases, to compare rows in the same table — for example customers with the same rating.
Set 4 — PL/SQL Blocks, Functions and Procedures (open practical)
Q84. What are the sections of a PL/SQL block?
Declaration (DECLARE, optional), execution (BEGIN, mandatory) and exception handling (EXCEPTION, optional), ending with END;.
Q85. What is the difference between a procedure and a function?
A function must return a value with RETURN and can be called in a SQL statement; a procedure need not return a value and is called as a PL/SQL statement or with EXEC.
Q86. What is a cursor?
A pointer to the private memory area that holds the result of a query, allowing rows to be processed one at a time.
Q87. What does SET SERVEROUTPUT ON do?
It tells SQL*Plus to display the DBMS_OUTPUT buffer after each block or call.
Q88. What are IN, OUT and IN OUT parameters?
IN passes a value into the subprogram (read-only), OUT returns a value to the caller, IN OUT does both.
Q89. How do you select the top 5 rows in Oracle 10g?
Sort in an inline view and filter with ROWNUM <= 5 in the outer query.
Set 5 — Entrance Examination Database (open practical)
Q90. What is a composite key?
A primary key consisting of more than one column; the combination must be unique.
Q91. How do you change a primary key value that is referenced by a foreign key?
Oracle has no ON UPDATE CASCADE, so disable the foreign key, update the parent and child rows, then enable the constraint again (or insert a new parent, move the children, delete the old parent).
Q92. How do you write a 'for all' query in SQL?
Using relational division: double NOT EXISTS (no item that is not matched), or GROUP BY with HAVING COUNT equal to the total count.
Q93. What is a view? Does it store data?
A view is a named stored query. It does not store data (unless it is a materialized view); data comes from the base tables each time it is queried.
Q94. What is WITH READ ONLY / WITH CHECK OPTION?
WITH READ ONLY blocks all DML through the view. WITH CHECK OPTION allows DML but only if the resulting rows still satisfy the view's WHERE clause.
Q95. Difference between row-level and statement-level triggers?
A row-level trigger (FOR EACH ROW) fires once for every affected row and can use :NEW/:OLD; a statement-level trigger fires once per statement.
Q96. What are INSERTING, UPDATING and DELETING?
Boolean conditional predicates available inside a trigger to find out which DML operation fired it.
Set 6 — Employee and Department PL/SQL (open practical)
Q97. What is a parameterised cursor?
A cursor declared with parameters, e.g. CURSOR c(p NUMBER) IS ..., whose query uses the parameter; values are supplied when the cursor is opened.
Q98. How is user input taken in a PL/SQL block?
Through SQL*Plus substitution variables (&name); the value is substituted into the source text before the block is compiled. PL/SQL itself has no input statement.
Q99. What does ADD_MONTHS do?
Adds (or with a negative number subtracts) a number of months to a date, adjusting for month ends.
Q100. What is NO_DATA_FOUND?
A predefined exception raised when SELECT INTO returns no row.
Q101. What is %TYPE used for here?
To declare v_deptnm and v_deptno with the same data types as the DEPT columns.
Set 7 — Customer and Order (open practical)
Q102. Why can't a table be named ORDER?
ORDER is a reserved SQL keyword (ORDER BY). It can only be used if enclosed in double quotes, which makes the name case-sensitive and inconvenient, so another name is preferred.
Q103. What are :OLD and :NEW?
Pseudo-records available in row-level triggers. :OLD holds column values before the change, :NEW the values after it. INSERT has only :NEW, DELETE only :OLD.
Q104. What does UPDATE OF column in a trigger mean?
The trigger fires only if that column is listed in the UPDATE statement's SET clause.
Q105. Can a function return a string?
Yes. A function can return any SQL or PL/SQL type, e.g. VARCHAR2, NUMBER, DATE or a record.
Q106. Why is TRIM needed with 'Month'?
The 'Month' format pads names to the length of the longest month name (9 characters).
Set 8 — Employee Salary Slip (open practical)
Q107. What is %ROWTYPE?
An attribute that declares a record with the same structure as a table row or cursor row.
Q108. How do you declare a constant in PL/SQL?
name CONSTANT type := value; — it must be initialised and cannot be changed.
Q109. What is the difference between gross and net salary?
Gross is the total of all earnings; net is the take-home pay after deductions such as PF.
Q110. What does the FM format modifier do?
Fill Mode removes the leading and trailing blanks that TO_CHAR normally adds.
Q111. Can a function be declared inside an anonymous block?
Yes, local functions and procedures can be declared at the end of the declaration section and used only inside that block.
Set 9 — Bank Transaction (open practical)
Q112. Why use TO_DATE when calling the procedure?
It converts the text to a DATE using an explicit format, so the call works regardless of the session's default date format.
Q113. What is a user-defined exception?
An exception declared in the declaration section and raised explicitly with RAISE when a business rule is broken.
Q114. Difference between RAISE and RAISE_APPLICATION_ERROR?
RAISE raises a named exception that can be handled inside PL/SQL; RAISE_APPLICATION_ERROR raises an error with a custom number (−20000 to −20999) and message that is returned to the calling application.
Q115. What does TRUNC do to a date?
It removes the time portion (sets it to 00:00:00), or truncates to a unit such as 'MM' or 'YYYY' if a format is given.
Q116. What is DECODE?
A function that compares an expression with search values and returns the matching result: DECODE(expr, s1, r1, s2, r2, default).
Set 10 — Employee Cursors and Salary (open practical)
Q117. What is the difference between an implicit and an explicit cursor?
Oracle creates an implicit cursor (SQL%) for every DML and single-row SELECT INTO; an explicit cursor is declared by the programmer for a multi-row query.
Q118. What does a cursor FOR loop do automatically?
It declares the loop record, opens the cursor, fetches every row, exits when no rows remain and closes the cursor, even if an exception occurs inside the loop.
Q119. Name the cursor attributes.
%ISOPEN, %FOUND, %NOTFOUND and %ROWCOUNT.
Q120. Can a procedure return a value?
Not with RETURN; it can return values through OUT or IN OUT parameters.
Q121. Why use a cursor when SUM() exists?
In practice SUM is better; the cursor version is used to learn row-by-row processing, which is needed when each row requires different logic.
Independent educational resource for GTU MCA students — not an official GTU website. Disclaimer