ADVANCED DATABASE MANAGEMENT SYSTEMS UNIT III (BDU M.Sc CS SYLLABUS 2022-23)

 

ADVANCED DATABASE MANAGEMENT SYSTEMS

UNIT III

Basic Structure of SQL Queries - Additional Basics Operations, Set Operations - Null Values, Aggregate Functions - Nested Subqueries, Modification of the Database

SQL QUESTION BANK

Units Covered:

• Data Definition Language (DDL)
• Basic Structure of SQL Queries
• Additional Basic Operations
• Set Operations
• NULL Values
• Aggregate Functions
• Nested Subqueries
• Modification of the Database

---

1️⃣ OBJECTIVE TYPE QUESTIONS (MCQs)

1. Which SQL command is used to create a table?
a) INSERT
b) UPDATE
c) CREATE
d) SELECT
✅ Answer: c) CREATE

2. Which clause is used to filter records in SQL?
a) ORDER BY
b) WHERE
c) GROUP BY
d) HAVING
✅ Answer: b) WHERE

3. Which set operation returns common records from two queries?
a) UNION
b) INTERSECT
c) MINUS
d) ALL
✅ Answer: b) INTERSECT

4. Which aggregate function returns the total number of rows?
a) SUM()
b) AVG()
c) COUNT()
d) MAX()
✅ Answer: c) COUNT()

5. Which keyword is used to handle missing or unknown values?
a) DEFAULT
b) NULL
c) VOID
d) EMPTY
✅ Answer: b) NULL

---

2️⃣ FILL IN THE BLANKS

1. The SQL command used to remove a table permanently is ______.
   ✅ Answer: DROP
2. The ______ clause groups rows with the same values.
   ✅ Answer: GROUP BY
3. The UNION operation removes ______ rows by default.
   ✅ Answer: duplicate
4. Aggregate functions return a ______ value.
   ✅ Answer: single
5. A subquery written inside another query is called a ______.
   ✅ Answer: nested subquery

---

3️⃣ BLOOM’S TAXONOMY QUESTIONS WITH ANSWERS

🔹 Remembering

Q: List any four SQL DDL commands.
A: CREATE, DROP, ALTER, TRUNCATE

---

🔹 Understanding

Q: Explain the purpose of the WHERE clause.
A: The WHERE clause filters records based on specified conditions.

---

🔹 Applying

Q: Write a SQL query to display all students with marks greater than 80.
A:

SELECT * FROM student WHERE marks > 80;

---

🔹 Analyzing

Q: Differentiate between WHERE and HAVING clauses.
A:

• WHERE filters rows before grouping
• HAVING filters groups after GROUP BY

---

🔹 Evaluating

Q: Why are nested subqueries preferred in complex queries?
A: They improve query clarity, reusability, and allow conditional filtering.

---

🔹 Creating

Q: Create a table Employee with EmpID, Name, and Salary.
A:

CREATE TABLE Employee (

  EmpID INT,

  Name VARCHAR(50),

  Salary DECIMAL(10,2)

);

---

4️⃣ DETAILED ANSWERS (THEORY + EXAMPLES)

---

📘 Data Definition Language (DDL)

DDL commands define database structure.

Commands:

• CREATE – creates tables
• ALTER – modifies structure
• DROP – deletes structure
• TRUNCATE – removes all records

Example:

CREATE TABLE Student (

  RollNo INT,

  Name VARCHAR(30),

  Age INT

);

---

📘 Basic Structure of SQL Queries

SELECT column_name

FROM table_name

WHERE condition;

Example:

SELECT Name FROM Student WHERE Age > 18;

---

📘 Additional Basic Operations

• ORDER BY – sorts records
• DISTINCT – removes duplicates
• LIKE – pattern matching

---

📘 Set Operations

Operation	Description
UNION	Combines results, removes duplicates
UNION ALL	Keeps duplicates
INTERSECT	Common records
MINUS	Records in first query only

---

📘 NULL Values

NULL represents missing or unknown data.

Checking NULL:

SELECT * FROM Student WHERE Age IS NULL;

---

📘 Aggregate Functions

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Example:

SELECT AVG(Salary) FROM Employee;

---

📘 Nested Subqueries

A query inside another query.

Example:

SELECT Name FROM Employee

WHERE Salary > (SELECT AVG(Salary) FROM Employee);

---

 

📘 Modification of the Database

• INSERT – add records
• UPDATE – modify records
• DELETE – remove records

Example:

UPDATE Employee SET Salary = 50000 WHERE EmpID = 101;

---

SQL QUERIES WITH EXAMPLES

(Based on  syllabus)

---

1️⃣ DATA DEFINITION LANGUAGE (DDL)

🔹 CREATE TABLE

Purpose: Create a new table

CREATE TABLE Student (

    RollNo INT,

    Name VARCHAR(30),

    Age INT,

    Marks INT

);

Example:
Creates a table named Student with four columns.

---

🔹 ALTER TABLE

Purpose: Modify table structure

ALTER TABLE Student ADD Email VARCHAR(50);

Example:
Adds a new column Email to Student table.

---

🔹 DROP TABLE

Purpose: Delete table permanently

DROP TABLE Student;

Example:
Removes the Student table completely.

---

 

🔹 TRUNCATE TABLE

Purpose: Remove all records (structure remains)

TRUNCATE TABLE Student;

---

2️⃣ BASIC STRUCTURE OF SQL QUERY

🔹 SELECT Statement

SELECT * FROM Student;

Example:
Displays all records from Student table.

---

🔹 SELECT with WHERE

SELECT Name, Marks FROM Student WHERE Marks > 80;

Example:
Shows students scoring more than 80 marks.

---

3️⃣ ADDITIONAL BASIC OPERATIONS

🔹 ORDER BY

SELECT * FROM Student ORDER BY Marks DESC;

Example:
Displays students sorted by marks (highest first).

---

🔹 DISTINCT

SELECT DISTINCT Age FROM Student;

Example:
Displays unique ages only.

---

🔹 LIKE Operator

SELECT * FROM Student WHERE Name LIKE 'A%';

Example:
Displays names starting with letter A.

---

4️⃣ SET OPERATIONS

Assume two tables:
ClassA and ClassB

 

 

🔹 UNION

SELECT Name FROM ClassA

UNION

SELECT Name FROM ClassB;

Example:
Combines names from both tables (no duplicates).

---

🔹 UNION ALL

SELECT Name FROM ClassA

UNION ALL

SELECT Name FROM ClassB;

Example:
Includes duplicate names.

---

🔹 INTERSECT

SELECT Name FROM ClassA

INTERSECT

SELECT Name FROM ClassB;

Example:
Displays common students.

---

🔹 MINUS

SELECT Name FROM ClassA

MINUS

SELECT Name FROM ClassB;

Example:
Students only in ClassA.

---

5️⃣ NULL VALUES

🔹 IS NULL

SELECT * FROM Student WHERE Email IS NULL;

Example:
Shows students without email ID.

---

 

 

🔹 IS NOT NULL

SELECT * FROM Student WHERE Email IS NOT NULL;

---

6️⃣ AGGREGATE FUNCTIONS

🔹 COUNT()

SELECT COUNT(*) FROM Student;

Example:
Counts total number of students.

---

🔹 SUM()

SELECT SUM(Marks) FROM Student;

---

🔹 AVG()

SELECT AVG(Marks) FROM Student;

---

🔹 MAX() and MIN()

SELECT MAX(Marks), MIN(Marks) FROM Student;

---

7️⃣ NESTED SUBQUERIES

🔹 Subquery with SELECT

SELECT Name FROM Student

WHERE Marks > (SELECT AVG(Marks) FROM Student);

Example:
Displays students scoring above average marks.

---

🔹 Subquery with IN

SELECT Name FROM Student

WHERE RollNo IN (SELECT RollNo FROM Result WHERE Status = 'Pass');

---

8️⃣ MODIFICATION OF DATABASE

🔹 INSERT

INSERT INTO Student VALUES (1, 'Ravi', 20, 85);

---

 

🔹 UPDATE

UPDATE Student SET Marks = 90 WHERE RollNo = 1;

---

🔹 DELETE

DELETE FROM Student WHERE Marks < 40;

---

SQL QUERIES WITH INPUT DATA & OUTPUT

📘 ASSUME STUDENT TABLE DATA

SELECT * FROM Student;

RollNo	Name	Age	Marks	Email
1	Ravi	20	85	ravi@gmail.com
2	Anu	19	92	NULL
3	Kumar	21	70	kumar@gmail.com
4	Asha	20	40	NULL
5	Bala	22	95	bala@gmail.com

---

1️⃣ BASIC SELECT QUERY

SELECT * FROM Student;

✅ OUTPUT

RollNo	Name	Age	Marks	Email
1	Ravi	20	85	ravi@gmail.com
2	Anu	19	92	NULL
3	Kumar	21	70	kumar@gmail.com
4	Asha	20	40	NULL
5	Bala	22	95	bala@gmail.com

---

2️⃣ SELECT WITH WHERE

SELECT Name, Marks FROM Student WHERE Marks > 80;

✅ OUTPUT

Name	Marks
Ravi	85
Anu	92
Bala	95

---

3️⃣ ORDER BY

SELECT Name, Marks FROM Student ORDER BY Marks DESC;

✅ OUTPUT

Name	Marks
Bala	95
Anu	92
Ravi	85
Kumar	70
Asha	40

---

4️⃣ DISTINCT

SELECT DISTINCT Age FROM Student;

✅ OUTPUT

Age
19
20
21
22

---

5️⃣ LIKE OPERATOR

SELECT Name FROM Student WHERE Name LIKE 'A%';

✅ OUTPUT

Name
Anu
Asha

---

6️⃣ NULL VALUES

SELECT Name FROM Student WHERE Email IS NULL;

✅ OUTPUT

Name
Anu
Asha

---

7️⃣ AGGREGATE FUNCTIONS

🔹 COUNT

SELECT COUNT(*) FROM Student;

✅ OUTPUT

COUNT(*)
5

---

🔹 SUM

SELECT SUM(Marks) FROM Student;

✅ OUTPUT

SUM(Marks)
382

---

🔹 AVG

SELECT AVG(Marks) FROM Student;

✅ OUTPUT

AVG(Marks)
76.4

---

🔹 MAX & MIN

SELECT MAX(Marks), MIN(Marks) FROM Student;

✅ OUTPUT

MAX(Marks)	MIN(Marks)
95	40

---

8️⃣ NESTED SUBQUERY

SELECT Name FROM Student

WHERE Marks > (SELECT AVG(Marks) FROM Student);

✅ OUTPUT

Name
Ravi
Anu
Bala

---

9️⃣ INSERT QUERY

INSERT INTO Student VALUES (6, 'Sita', 19, 88, 'sita@gmail.com');

✅ OUTPUT (After Insert)

RollNo	Name	Age	Marks	Email
6	Sita	19	88	sita@gmail.com

---

🔟 UPDATE QUERY

UPDATE Student SET Marks = 90 WHERE RollNo = 1;

✅ OUTPUT (Updated Row)

RollNo	Name	Marks
1	Ravi	90

---

1️⃣1️⃣ DELETE QUERY

DELETE FROM Student WHERE Marks < 50;

✅ OUTPUT (Remaining Records)

RollNo	Name	Marks
1	Ravi	90
2	Anu	92
3	Kumar	70
5	Bala	95
6	Sita	88

---

 

 

 

Intermediate SQL: Join Expression, Views - Transactions, Integrity Constraints - Data Types and Schemas, Authorization

INTERMEDIATE SQL – COMPLETE QUESTION BANK

Syllabus Covered:

• Join Expressions
• Views
• Transactions
• Integrity Constraints
• Data Types & Schemas
• Authorization

---

1️⃣ OBJECTIVE TYPE QUESTIONS (MCQs)

1. Which SQL join returns only matching rows from both tables?
a) LEFT JOIN
b) RIGHT JOIN
c) FULL JOIN
d) INNER JOIN
✅ Answer: d) INNER JOIN

---

2. Which command is used to create a view?
a) CREATE TABLE
b) CREATE VIEW
c) ALTER VIEW
d) DROP VIEW
✅ Answer: b) CREATE VIEW

---

3. Which command permanently saves transaction changes?
a) ROLLBACK
b) SAVEPOINT
c) COMMIT
d) DELETE
✅ Answer: c) COMMIT

---

 

4. PRIMARY KEY constraint ensures that a column is
a) NULL
b) Duplicate
c) Unique and NOT NULL
d) Indexed only
✅ Answer: c) Unique and NOT NULL

---

5. Which SQL statement is used to grant permission to a user?
a) REVOKE
b) ALLOW
c) GRANT
d) AUTHORIZE
✅ Answer: c) GRANT

---

2️⃣ FILL IN THE BLANKS

1.     A ______ join returns all rows from the left table and matching rows from the right table.
✅ Answer: LEFT JOIN

2.     A view is a ______ table based on SQL query.
✅ Answer: virtual

3.     The ______ command undoes changes in a transaction.
✅ Answer: ROLLBACK

4.     FOREIGN KEY constraint maintains ______ integrity.
✅ Answer: referential

5.     The GRANT command gives ______ to users.
✅ Answer: privileges

---

3️⃣ BLOOM’S TAXONOMY QUESTIONS & ANSWERS

---

🔹 Remembering

Q: List different types of SQL joins.
A: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

---

 

🔹 Understanding

Q: Explain views in SQL.
A: A view is a virtual table created using a SELECT query to simplify complex queries.

---

🔹 Applying

Q: Write a SQL query to join Student and Department tables.
A:

SELECT Student.Name, Department.DeptName

FROM Student

INNER JOIN Department

ON Student.DeptID = Department.DeptID;

---

🔹 Analyzing

Q: Compare INNER JOIN and LEFT JOIN.
A:

• INNER JOIN returns only matching rows
• LEFT JOIN returns all rows from left table

---

🔹 Evaluating

Q: Why are transactions important in databases?
A: They ensure data consistency and allow rollback during errors.

---

🔹 Creating

Q: Create a view for students scoring above 80 marks.
A:

CREATE VIEW HighScorers AS

SELECT Name, Marks FROM Student WHERE Marks > 80;

---

 

 

4️⃣ EXAMPLES WITH QUERY AND OUTPUT

---

📘 ASSUME TABLE DATA

Student Table

RollNo	Name	DeptID	Marks
1	Ravi	101	85
2	Anu	102	90
3	Kumar	101	70

Department Table

DeptID	DeptName
101	CSE
102	IT

---

🔹 JOIN EXPRESSION (INNER JOIN)

SELECT Name, DeptName

FROM Student

INNER JOIN Department

ON Student.DeptID = Department.DeptID;

✅ OUTPUT

Name	DeptName
Ravi	CSE
Anu	IT
Kumar	CSE

---

🔹 VIEW

CREATE VIEW TopStudents AS

SELECT Name, Marks FROM Student WHERE Marks > 80;

SELECT * FROM TopStudents;

✅ OUTPUT

Name	Marks
Ravi	85
Anu	90

---

🔹 TRANSACTION

BEGIN TRANSACTION;

UPDATE Student SET Marks = 95 WHERE RollNo = 1;

ROLLBACK;

✅ OUTPUT

Marks remain unchanged after rollback.

RollNo	Name	Marks
1	Ravi	85

---

🔹 INTEGRITY CONSTRAINTS

CREATE TABLE Course (

  CourseID INT PRIMARY KEY,

  CourseName VARCHAR(30) NOT NULL,

  DeptID INT,

  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

);

---

🔹 DATA TYPES & SCHEMA

CREATE SCHEMA College;

CREATE TABLE College.Faculty (

  FacID INT,

  Name VARCHAR(30),

  Salary DECIMAL(10,2));

🔹 AUTHORIZATION

GRANT SELECT, INSERT ON Student TO User1;

REVOKE INSERT ON Student FROM User1;

 

Advanced SQL: Accessing SQL from Programming Language, Functions and Procedures, Triggers

• Objective‑type questions (MCQs)
• Fill‑in‑the‑blanks
• Bloom’s‑taxonomy‑style questions (Knowledge → Analysis)
• PL/SQL code examples with detailed step‑by‑step explanation

All are framed around Oracle‑style PL/SQL (you can adapt to PostgreSQL/SQL Server if needed).

---

1. Objective‑type questions (MCQs)

A. Accessing SQL from a programming language

1.     Which of the following is used to embed SQL statements directly inside a host programming language like C or Java?
a) Dynamic SQL
b) Embedded SQL
c) Native SQL
d) Inline SQL

2.     In JDBC, which interface is used to call a stored procedure?
a) Statement
b) PreparedStatement
c) CallableStatement
d) Connection

3.     Which of the following is not a way to access SQL from a host language?
a) ODBC
b) JDBC
c) PL/SQL blocks only
d) Embedded SQL

4.     What is the main advantage of using stored procedures instead of plain SQL in the application?
a) Faster network transfer
b) Better security and code reuse
c) Simpler UI design
d) Easier debugging in the browser

Answers:

1.     b) Embedded SQLsanfoundry+1

2.     c) CallableStatement[sanfoundry]​

3.     c) PL/SQL blocks onlydb-book+1

4.     b) Better security and code reusedevitpl+1

---

B. Functions and Procedures

5.     A PL/SQL function must return:
a) A table
b) A cursor
c) A value of a specified data type
d) Nothing

6.     Which keyword is used to define a PL/SQL procedure?
a) FUNCTION
b) PROCEDURE
c) SUBPROGRAM
d) ROUTINE

7.     Which of the following is true about PL/SQL functions?
a) They can be called only inside PL/SQL blocks
b) They can be used in SQL SELECT clauses
c) They cannot have parameters
d) They always modify the database

8.     What is the purpose of IN, OUT, and IN OUT in PL/SQL parameter modes?
a) To specify data types
b) To specify the direction of parameter passing
c) To specify table names
d) To specify transaction behavior

Answers:
5. c) A value of a specified data typesimplilearn+1
6. b) PROCEDUREgeeksforgeeks+1
7. b) They can be used in SQL SELECT clausessimplilearn+1
8. b) To specify the direction of parameter passinggeeksforgeeks+1

---

 

C. Triggers

9.     A trigger is automatically executed when:
a) A user logs in
b) A specific SQL event occurs on a table
c) A stored procedure is compiled
d) The database starts

10. Which of the following is not a valid trigger timing in Oracle?
a) BEFORE
b) AFTER
c) INSTEAD OF
d) DURING

11. A row‑level trigger fires:
a) Once per statement
b) Once per row affected by the statement
c) Only on SELECT
d) Only on DROP

12. Which of the following is a typical use of a trigger?
a) To create a new table
b) To enforce complex business rules
c) To compile PL/SQL code
d) To connect to another database

Answers:
9. b) A specific SQL event occurs on a tablesanfoundry+1
10. d) DURINGdevitpl+1
11. b) Once per row affected by the statementsanfoundry+1
12. b) To enforce complex business rulesdevitpl+1

---

2. Fill‑in‑the‑blanks (with answers)

1.     In Embedded SQL, SQL statements are processed by a ________ before the host‑language compiler runs.
Answer: preprocessordb-book+1

2.     In JDBC, the ________ interface is used to execute SQL queries and retrieve results.
Answer: Statement (or PreparedStatement)[sanfoundry]​

3.     A PL/SQL ________ returns a value, while a ________ does not.
Answer: function, proceduresimplilearn+1

4.     The keyword ________ is used to define a trigger in PL/SQL.
Answer: TRIGGERgeeksforgeeks+1

5.     A trigger that fires before an INSERT is called a ________ trigger.
Answer: BEFOREsanfoundry+1

6.     The pseudo‑records ________ and ________ can be used inside a row‑level trigger to access old and new values.
Answer: :OLD, :NEWdevitpl+1

7.     A ________‑level trigger fires once per SQL statement, regardless of how many rows are affected.
Answer: statementsanfoundry+1

---

3. Bloom’s‑taxonomy‑style questions (with answers)

Level 1–2: Remember / Understand

8.     Knowledge: List three ways to access SQL from a host programming language.
Answer: Embedded SQL, Dynamic SQL (via APIs such as JDBC/ODBC), calling stored procedures/functions from the host language.db-book+1

9.     Understand: Explain the difference between a PL/SQL function and a procedure.
Answer: A function returns a single value and can be used in SQL expressions (e.g., SELECT), while a procedure performs actions and may have OUT or IN OUT parameters but does not return a value in the same way.simplilearn+1

Level 3–4: Apply / Analyze

10. Apply: Write a PL/SQL function that takes an employee ID and returns the employee’s salary.
Answer (step‑by‑step):

o    Declare a function get_salary with parameter p_emp_id of type NUMBER.

o    Use RETURN NUMBER to specify the return type.

o    In the IS/AS block, declare a local variable v_salary.

o    Use SELECT salary INTO v_salary FROM employees WHERE emp_id = p_emp_id;.

o    Return v_salary.

o    Call it in SQL: SELECT get_salary(101) FROM dual;.geeksforgeeks+1

11. Analyze: Why might you prefer to call a stored procedure from a Java application instead of writing SQL directly in Java?
Answer: Stored procedures centralize business logic, improve security (least‑privilege access), reduce network round trips, and make maintenance easier (change logic in one place).sanfoundry+1

Level 5–6: Evaluate / Create

12. Evaluate: Compare the use of triggers versus application‑level code for enforcing a business rule such as “salary cannot be decreased by more than 10% in a single update.”
Answer:

o    Triggers enforce the rule at the database level, so all applications (even ad‑hoc tools) are covered.

o    Application code can be bypassed or duplicated across clients; triggers avoid that but can make debugging harder and may impact performance if not designed well.devitpl+1

13. Create: Design a trigger that logs every update to an EMPLOYEES table into an EMP_LOG table, recording the old salary, new salary, and timestamp.
Answer outline:

o    Create an EMP_LOG(emp_id, old_sal, new_sal, change_date) table.

o    Define a row‑level AFTER UPDATE trigger on EMPLOYEES.

o    Inside the trigger body, insert into EMP_LOG using :OLD.emp_id, :OLD.salary, :NEW.salary, and SYSDATE.sanfoundry+1

---

4. PL/SQL examples with step‑by‑step working

Example 1: Simple function

sql

CREATE OR REPLACE FUNCTION get_salary(p_emp_id NUMBER)

RETURN NUMBER

IS

    v_salary NUMBER;

BEGIN

    SELECT salary INTO v_salary

    FROM employees

    WHERE emp_id = p_emp_id;

 

    RETURN v_salary;

END;

/

Step‑by‑step how it works:

1.     CREATE OR REPLACE FUNCTION get_salary(p_emp_id NUMBER)

o    Defines a function named get_salary that takes one input parameter p_emp_id of type NUMBER.simplilearn+1

2.     RETURN NUMBER

o    Declares that the function will return a numeric value.[geeksforgeeks]​

3.     IS (or AS)

o    Starts the declaration section where local variables are defined.[geeksforgeeks]​

4.     v_salary NUMBER;

o    Declares a local variable v_salary to hold the retrieved salary.[geeksforgeeks]​

5.     BEGIN ... END;

o    The executable block:

§  SELECT salary INTO v_salary ... fetches the salary for the given emp_id into v_salary.[geeksforgeeks]​

§  RETURN v_salary; sends the value back to the caller.[geeksforgeeks]​

6.     Usage in SQL:

sql

SELECT get_salary(101) FROM dual;

o    The SQL engine calls the function, passes 101, and displays the returned salary.[geeksforgeeks]​

---

Example 2: Simple procedure

sql

CREATE OR REPLACE PROCEDURE raise_salary(

    p_emp_id    IN  NUMBER,

    p_percent   IN  NUMBER,

    p_new_sal   OUT NUMBER

)

IS

BEGIN

    UPDATE employees

    SET salary = salary * (1 + p_percent / 100)

    WHERE emp_id = p_emp_id;

 

    SELECT salary INTO p_new_sal

    FROM employees

    WHERE emp_id = p_emp_id;

END;

/

Step‑by‑step how it works:

1.     PROCEDURE raise_salary(...)

o    Defines a procedure that takes two IN parameters (p_emp_id, p_percent) and one OUT parameter p_new_sal.simplilearn+1

2.     UPDATE employees ...

o    Increases the salary by the given percentage for the specified employee.[geeksforgeeks]​

3.     SELECT salary INTO p_new_sal ...

o    Reads the updated salary and assigns it to the OUT parameter.[geeksforgeeks]​

4.     Calling from PL/SQL block:

 

 sql

DECLARE

    v_new_sal NUMBER;

BEGIN

    raise_salary(101, 10, v_new_sal);

    DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new_sal);

END;

o    The procedure modifies the row and returns the new salary via v_new_sal.simplilearn+1

---

Example 3: Trigger (row‑level audit)

sql

CREATE TABLE emp_log (

    emp_id      NUMBER,

    old_sal     NUMBER,

    new_sal     NUMBER,

    change_date DATE

);

 

CREATE OR REPLACE TRIGGER audit_salary_update

    AFTER UPDATE OF salary ON employees

    FOR EACH ROW

BEGIN

    INSERT INTO emp_log (emp_id, old_sal, new_sal, change_date)

    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);

END;

/

Step‑by‑step how it works:

1.     CREATE TABLE emp_log ...

o    Creates a log table to store changes.[devitpl]​

2.     CREATE OR REPLACE TRIGGER audit_salary_update

o    Names the trigger audit_salary_update.sanfoundry+1

3.     AFTER UPDATE OF salary ON employees

o    Specifies that the trigger fires after an UPDATE on the salary column of employees.devitpl+1

4.     FOR EACH ROW

o    Makes it a row‑level trigger; it runs once per updated row.sanfoundry+1

5.     Inside BEGIN ... END;

o    INSERT INTO emp_log ...

§  Uses :OLD.emp_id, :OLD.salary (values before update) and :NEW.salary (values after update).devitpl+1

§  SYSDATE records the timestamp of the change.[devitpl]​

6.     Effect:

o    Every time someone updates an employee’s salary, a log entry is created automatically.simplilearn+1

---