CT 2 Question Bank with Model Answers

2 Marks questions

Q1. Define nested queries. Enlist types of nested queries.

Answer:
A nested query is a query written inside another query (usually inside the WHERE, FROM, or SELECT clause).
It is also called a subquery.

Types of Nested Queries:

Single-row subquery
Multiple-row subquery
Multiple-column subquery
Correlated subquery

Marking Scheme:

✅ 1st mark: Definition of nested query
✅ 2nd mark: Listing any two or more types

Q2. Write any two DDL commands with syntax.

Answer:
DDL (Data Definition Language) commands are used to define and manage database structures.

Examples:

CREATE TABLE

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

ALTER TABLE

ALTER TABLE table_name
ADD column_name datatype;

Marking Scheme:

✅ 1st mark: Correct mention and syntax of first DDL command
✅ 2nd mark: Correct mention and syntax of second DDL command

Q3. Write syntax for creating and dropping views.

Answer:

Creating a View:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Dropping a View:
```
DROP VIEW view_name;
```

Marking Scheme:

✅ 1st mark: Syntax of CREATE VIEW
✅ 2nd mark: Syntax of DROP VIEW

Q4. List Control structures in PL/SQL.

Answer:
PL/SQL provides three types of control structures:

Sequential control (default order of execution)
Conditional control (IF, IF-THEN-ELSE)
Iterative control (LOOP, FOR, WHILE)
Exception control (EXCEPTION block)

Marking Scheme:

✅ 1st mark: Listing any two correct control structures
✅ 2nd mark: Listing all major types (conditional, iterative, exception)

Q5. Draw the block structure of PL/SQL.

Answer:

PL/SQL Block Structure:

DECLARE
   -- Declarations (optional)
BEGIN
   -- Executable statements
EXCEPTION
   -- Exception handling (optional)
END;

Explanation:

DECLARE: Used to declare variables and cursors.
BEGIN: Contains executable statements.
EXCEPTION: Handles runtime errors.
END;: Marks the end of the block.

Marking Scheme:

✅ 1st mark: Correct structure (block diagram or code format)
✅ 2nd mark: Correct labeling/explanation of each section

Q6. State any two advantages of PL/SQL.

Answer:
Advantages of PL/SQL:

Block structure: Allows grouping of SQL and procedural statements into logical blocks.
Error handling: Provides an EXCEPTION block to handle runtime errors effectively.
Improved performance: Reduces communication between application and database.
Portability: Can run on any Oracle database.

Marking Scheme:

✅ 1st mark: Any one correct advantage
✅ 2nd mark: Any second correct advantage

Q7. Enlist the types of Operators.

Answer:
Types of Operators in SQL:

Arithmetic Operators (+, -, *, /, %)
Comparison Operators (=, >, <, >=, <=, <>)
Logical Operators (AND, OR, NOT)
Assignment Operator (:= in PL/SQL)
Set Operators (UNION, INTERSECT, MINUS)

Marking Scheme:

✅ 1st mark: Any two correct operator types
✅ 2nd mark: Remaining correct operator types or complete list

Q8. Write queries for creating and deleting user.

Answer:

Creating a User:

CREATE USER username IDENTIFIED BY password;

Deleting a User:
```
DROP USER username;
```

Example:

CREATE USER atul IDENTIFIED BY atul123;
DROP USER atul;

Marking Scheme:

✅ 1st mark: Correct CREATE USER syntax
✅ 2nd mark: Correct DROP USER syntax

Q9. Define failure. Enlist types of failure.

Answer:

Definition:
A failure is an unexpected event that causes the database system to stop working correctly, possibly leading to data loss or inconsistency.

Types of Failures:

Transaction failure
System failure
Media (disk) failure
Communication failure

Marking Scheme:

✅ 1st mark: Correct definition of failure
✅ 2nd mark: Listing any two or more types of failure

Q10. Define two recovery techniques.

Answer:

Recovery Techniques:

Deferred Update:
All updates are recorded in a log and applied to the database only after the transaction commits.
Immediate Update:
Changes are applied to the database as they occur, but logs are maintained for rollback if needed.

Marking Scheme:

✅ 1st mark: First recovery technique with short explanation
✅ 2nd mark: Second recovery technique with short explanation

4 Marks questions

Q1. Explain any four types of joins in SQL with examples.

Answer:

A JOIN is used to combine rows from two or more tables based on a related column between them.

Types of Joins:

INNER JOIN:
Returns only matching rows from both tables.

SELECT s.name, d.dept_name
FROM student s
INNER JOIN department d
ON s.dept_id = d.dept_id;

LEFT JOIN (LEFT OUTER JOIN):
Returns all rows from the left table and matching rows from the right table.
```
SELECT s.name, d.dept_name
FROM student s
LEFT JOIN department d
ON s.dept_id = d.dept_id;
```
RIGHT JOIN (RIGHT OUTER JOIN):
Returns all rows from the right table and matching rows from the left table.
```
SELECT s.name, d.dept_name
FROM student s
RIGHT JOIN department d
ON s.dept_id = d.dept_id;
```

FULL OUTER JOIN:
Returns all rows from both tables, matching when possible.

SELECT s.name, d.dept_name
FROM student s
FULL OUTER JOIN department d
ON s.dept_id = d.dept_id;

Marking Scheme:

✅ 1st mark: Definition of JOIN
✅ 2nd mark: Explanation of first two joins
✅ 3rd mark: Explanation of next two joins
✅ 4th mark: Correct SQL examples for joins

Q2. Explain set operators with suitable examples.

Answer:

Set Operators are used to combine the results of two or more SELECT statements.

Types of Set Operators:

UNION:
Combines results of two queries and removes duplicates.
```
SELECT name FROM student
UNION
SELECT name FROM alumni;
```

UNION ALL:
Combines results and includes duplicates.

SELECT name FROM student
UNION ALL
SELECT name FROM alumni;

INTERSECT:
Returns rows common to both queries.

SELECT name FROM student
INTERSECT
SELECT name FROM alumni;

MINUS (EXCEPT):
Returns rows present in first query but not in second.
```
SELECT name FROM student
MINUS
SELECT name FROM alumni;
```

Marking Scheme:

✅ 1st mark: Definition of set operators
✅ 2nd mark: Explanation of UNION & UNION ALL
✅ 3rd mark: Explanation of INTERSECT & MINUS
✅ 4th mark: Suitable examples for all

Q3. Explain the use of GROUP BY and HAVING clauses with example.

Answer:

GROUP BY Clause:

Used to group rows having the same values in one or more columns.
It is often used with aggregate functions (SUM, COUNT, AVG, etc.).

Example:

SELECT dept_id, COUNT(*) AS total_students
FROM student
GROUP BY dept_id;

HAVING Clause:

Used to filter groups created by GROUP BY based on a condition.
It works like WHERE, but on grouped data.

Example:

SELECT dept_id, COUNT(*) AS total_students
FROM student
GROUP BY dept_id
HAVING COUNT(*) > 10;

Marking Scheme:

✅ 1st mark: Explanation of GROUP BY
✅ 2nd mark: Example for GROUP BY
✅ 3rd mark: Explanation of HAVING
✅ 4th mark: Example for HAVING

Q4. Explain sequences in PL/SQL. Write syntax to create, alter and drop sequence.

Answer:

Definition:

A sequence is a database object that automatically generates unique numeric values, often used for primary keys.

Syntax:

1. Create Sequence

CREATE SEQUENCE seq_name
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
NOCYCLE;

2. Alter Sequence

ALTER SEQUENCE seq_name
INCREMENT BY 5;

3. Drop Sequence

DROP SEQUENCE seq_name;

Example:

INSERT INTO student VALUES (seq_name.NEXTVAL, 'Atul');

Marking Scheme:

✅ 1st mark: Definition & purpose of sequence
✅ 2nd mark: Syntax for CREATE
✅ 3rd mark: Syntax for ALTER
✅ 4th mark: Syntax for DROP & example

Q5. Write step-by-step syntax to create, open, fetch and close cursor in PL/SQL with example.

Answer:

A cursor is a pointer that allows row-by-row processing of query results.

Steps and Syntax:

Declare Cursor

CURSOR c1 IS SELECT name, dept FROM student;

Open Cursor
```
OPEN c1;
```
Fetch Data
```
FETCH c1 INTO v_name, v_dept;
```
Close Cursor
```
CLOSE c1;
```

Example:

DECLARE
   v_name student.name%TYPE;
   v_dept student.dept%TYPE;
   CURSOR c1 IS SELECT name, dept FROM student;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO v_name, v_dept;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_name || ' - ' || v_dept);
   END LOOP;
   CLOSE c1;
END;

Marking Scheme:

✅ 1st mark: Declaration and purpose of cursor
✅ 2nd mark: Syntax for CREATE/OPEN
✅ 3rd mark: Syntax for FETCH & CLOSE
✅ 4th mark: Complete working example

Q6. Write a PL/SQL program to find largest of three numbers (accept numbers from user).

Answer:

Program:

DECLARE
   a NUMBER;
   b NUMBER;
   c NUMBER;
   largest NUMBER;
BEGIN
   -- Accept input from user
   a := &a;
   b := &b;
   c := &c;

   -- Find largest number
   IF (a > b) AND (a > c) THEN
      largest := a;
   ELSIF (b > c) THEN
      largest := b;
   ELSE
      largest := c;
   END IF;

   DBMS_OUTPUT.PUT_LINE('Largest number is: ' || largest);
END;
/

Explanation:

The program accepts three numbers using substitution variables (&a, &b, &c).
Uses IF–ELSEIF–ELSE to compare and find the largest.
Displays the largest number using DBMS_OUTPUT.PUT_LINE.

Marking Scheme:

✅ 1st mark: Correct declaration and input statements
✅ 2nd mark: Correct logic/condition for largest number
✅ 3rd mark: Correct output statement
✅ 4th mark: Complete working program without syntax error

Q7. Explain exception handling in PL/SQL. Describe predefined and user-defined exceptions with example.

Answer:

Definition:

Exception handling in PL/SQL is used to handle runtime errors and ensure the program continues or terminates gracefully.

Types of Exceptions:

Predefined Exceptions:

Automatically raised by Oracle.
Examples:
- NO_DATA_FOUND → When SELECT returns no rows.
- ZERO_DIVIDE → Division by zero.

Example:

BEGIN
   SELECT name INTO v_name FROM student WHERE id = 50;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No such student found.');
END;
/

User-Defined Exceptions:

Declared and raised manually using RAISE.

Example:

DECLARE
   e_invalid EXCEPTION;
   marks NUMBER := -10;
BEGIN
   IF marks < 0 THEN
      RAISE e_invalid;
   END IF;
EXCEPTION
   WHEN e_invalid THEN
      DBMS_OUTPUT.PUT_LINE('Invalid marks entered.');
END;
/

Marking Scheme:

✅ 1st mark: Definition and purpose of exception handling
✅ 2nd mark: Explanation and example of predefined exception
✅ 3rd mark: Explanation and example of user-defined exception
✅ 4th mark: Clear syntax and correctness of both examples

Q8. Explain GRANT and REVOKE commands with syntax and example.

Answer:

GRANT Command:

Used by the database administrator or owner to provide privileges to other users.

Syntax:

GRANT privilege_name ON object_name TO user_name;

Example:

GRANT SELECT, INSERT ON student TO atul;

→ Gives user Atul permission to select and insert records in the student table.

REVOKE Command:

Used to remove previously granted privileges from users.

Syntax:

REVOKE privilege_name ON object_name FROM user_name;

Example:

REVOKE INSERT ON student FROM atul;

→ Removes INSERT privilege from user Atul.

Marking Scheme:

✅ 1st mark: Definition/purpose of GRANT
✅ 2nd mark: Syntax and example of GRANT
✅ 3rd mark: Definition/purpose of REVOKE
✅ 4th mark: Syntax and example of REVOKE

Q9. Explain ACID properties of transaction.

Answer:

A transaction is a logical unit of work that must be either fully completed or fully aborted.
To ensure reliability, transactions follow ACID properties.

A → Atomicity

Ensures all operations in a transaction are completed or none are done.
“All-or-nothing” rule.
🟢 Example: If money is debited but not credited, the transaction is rolled back.

C → Consistency

Ensures database moves from one valid state to another valid state after a transaction.
All integrity constraints must be satisfied.

I → Isolation

Concurrent transactions are executed independently without interference.
Intermediate results of one transaction are not visible to others.

D → Durability

Once a transaction is committed, its changes are permanent, even if the system fails.

Marking Scheme:

✅ 1st mark: Definition of transaction & ACID
✅ 2nd mark: Explanation of Atomicity & Consistency
✅ 3rd mark: Explanation of Isolation
✅ 4th mark: Explanation of Durability

Q10. Draw states of transaction diagram in DBMS

Explanation of States:

Active State:
- Transaction is executing its operations.
- It can read or write data items.
Partially Committed State:
- All instructions are executed, but changes are not yet permanent.
- System performs final checks before commit.
Committed State:
- Transaction is successfully completed.
- All changes are permanently saved in the database.
Failed State:
- Transaction cannot proceed due to an error or system crash.
Aborted State:
- Transaction is rolled back; changes are undone.
- Control may restart the transaction or terminate it.

Marking Scheme (4 Marks):

Marks	Criteria
✅ 1st mark	Correct heading and definition of transaction states
✅ 2nd mark	Well-labeled diagram of transaction states
✅ 3rd mark	Explanation of any two states correctly
✅ 4th mark	Explanation of remaining states with clarity