Wednesday, April 1, 2026

About Me ...

 

RGK CVBrief Introduction About You (Tell Me About Yourself)

You can present yourself like this:

“I am Dr. R. Gnanakumaran, currently working as an Assistant Professor in the Department of Computer Science at Annai Vailankanni Arts & Science College, Thanjavur. I completed my Ph.D. in Computer Science from Bharathidasan University, with research focused on computer networks, wireless networks, and energy-efficient protocols.

I have over 15 years of experience in both academia and industry, including my earlier role as a Software Test Engineer at COBS Technologies. My academic contributions include multiple international publications, IEEE papers, and patents in AI-based education and energy systems.

I am actively involved in institutional responsibilities such as ICT Academy coordination, examination committees, and research development activities.

My areas of interest include AI in education, network optimization, and emerging technologies like IoT and machine learning.”


🎯 Your Key Strengths (Highlight in Interview)

  • 🎓 Ph.D. holder with strong research background
  • 📚 10+ international publications (IEEE included)
  • 💡 Patents in AI & energy systems
  • 👨‍🏫 15+ years teaching + industry experience
  • 🧠 Expertise in:
    • Computer Networks
    • Wireless Networks (Wi-Fi, WiMAX)
    • AI & Machine Learning
    • IoT
  • 🏛 Active in academic committees & governance

🧭 Step-by-Step: How to Ask/Answer Questions in Interview

🔹 1. Before Answering

  • Listen carefully 👂
  • Pause 2–3 seconds
  • Understand what exactly is asked

👉 Example:
If asked about AI, clarify if they want teaching / research / applications


🔹 2. Structure Your Answer (Very Important)

Use this simple structure:

✔ Point → Explanation → Example

👉 Example:

“In my research, I focused on energy-efficient routing protocols (Point). These protocols reduce power consumption in wireless networks (Explanation). For example, my work on AODV optimization improved network efficiency (Example).”


🔹 3. If You Want to Ask Questions (Very Powerful Move)**

Ask smart academic questions like:

  • “What are the research expectations for this role?”
  • “Are there opportunities for funded projects or collaborations?”
  • “What teaching methodologies are preferred in your institution?”
  • “Is there support for publishing in indexed journals?”

👉 This shows:

  • Confidence 💪
  • Research mindset 🧠
  • Leadership quality

🔹 4. Handling Technical Questions

Use this method:

Concept → Application → Your Work

👉 Example:

“Machine learning models like GRU are used for sequential data (Concept). They are widely applied in healthcare diagnostics (Application). In my IEEE paper, I used GRU with optimization techniques for COVID-19 diagnosis (Your Work).”


🔹 5. If You Don’t Know an Answer

Never say “I don’t know” directly ❌

Say:

“I am not fully certain about that, but based on my understanding…”
OR
“I would approach it this way…”

👉 Shows thinking ability 👍


🔹 6. Body Language Tips

  • Sit straight 🪑
  • Maintain eye contact 👀
  • Speak slowly and clearly 🗣
  • Avoid rushing

🔹 7. Closing the Interview

End with:

“Thank you for the opportunity. I would be glad to contribute to both teaching and research activities in your institution.”


🚀 Bonus: Common Questions You Should Prepare

  • Tell me about your research work
  • Explain one of your IEEE papers
  • How do you handle weak students?
  • What teaching methods do you use?
  • Future research plans?
  • Why should we hire you?

If you want, I can also:
✅ Create mock interview questions with answers based on your CV
✅ Prepare HR + Technical Q&A script
✅ Help you with demo lecture preparation

Just tell me 👍

Create mock interview questions with answers based on your CV ✅ Prepare HR + Technical Q&A script ✅ Help you with demo lecture preparation

Great—let’s build a complete interview kit tailored to your CV. I’ll keep it practical, polished, and ready to use.


🎯 1. MOCK INTERVIEW QUESTIONS WITH ANSWERS (Based on Your Profile)

🔹 Q1: Tell me about your research work

Answer:

“My research primarily focuses on computer networks, especially energy-efficient routing in wireless and ad-hoc networks. During my Ph.D., I worked on optimizing AODV protocols to reduce power consumption. I have also extended my work into AI-based applications, including sentiment analysis and healthcare diagnosis using machine learning models like GRU. My recent work also explores Human-AI collaboration in education.”


🔹 Q2: Explain one of your IEEE papers

Answer:

“One of my IEEE papers focuses on sentiment analysis using Extreme Learning Machine. The goal was to improve classification accuracy while reducing training time. We applied optimized techniques to enhance performance compared to traditional models. This work is particularly useful in social media analytics and decision-making systems.”


🔹 Q3: What is AODV protocol?

Answer:

“AODV, or Ad Hoc On-Demand Distance Vector routing, is a reactive routing protocol used in mobile ad-hoc networks. It establishes routes only when needed, which reduces overhead. My research improved its energy efficiency by minimizing unnecessary transmissions and link failures.”


🔹 Q4: How do you integrate AI in teaching?

Answer:

“I use AI tools to personalize learning, automate assessments, and provide adaptive content. My patent on Human-AI collaboration focuses on integrating AI into EFL classrooms to improve student engagement and learning outcomes.”


🔹 Q5: What are your future research plans?

Answer:

“I plan to work on AI-driven network optimization, smart IoT systems, and interdisciplinary research combining education and artificial intelligence. I also aim to secure funded research projects and publish in high-impact journals.”


🔹 Q6: How do you handle weak students?

Answer:

“I identify their learning gaps through assessments and provide simplified explanations, practical examples, and mentoring. I also use blended learning techniques and digital tools to support them individually.”


🔹 Q7: Why should we hire you?

Answer:

“I bring a strong combination of teaching experience, research contributions, and administrative involvement. My ability to integrate emerging technologies like AI into both teaching and research makes me a valuable asset to the institution.”


🎯 2. HR + TECHNICAL Q&A SCRIPT

🔸 HR ROUND

Q: Strengths?

“Strong research orientation, effective teaching methodology, and commitment to continuous learning.”

Q: Weakness?

“I tend to focus deeply on research details, but I am improving my time management to balance multiple responsibilities.”

Q: Why do you want this job?

“I am looking for an environment that supports research, innovation, and quality teaching, where I can contribute meaningfully.”


🔸 TECHNICAL ROUND

Q: Difference between Wi-Fi and WiMAX

“Wi-Fi is used for short-range communication, while WiMAX provides long-range broadband access. WiMAX also supports better QoS and scalability.”


Q: What is Machine Learning?

“Machine learning is a subset of AI that enables systems to learn patterns from data and make predictions without explicit programming.”


Q: What is GRU?

“GRU, or Gated Recurrent Unit, is a type of recurrent neural network used for sequential data. It is simpler and faster than LSTM while maintaining performance.”


Q: What is IoT?

“IoT refers to interconnected devices that communicate and exchange data over the internet, enabling automation and smart systems.”


🎯 3. DEMO LECTURE PREPARATION (HIGH IMPACT)

🔥 Suggested Topic:

“Introduction to Machine Learning”


🧑‍🏫 Demo Lecture Structure (10–15 mins)

🔹 1. Introduction (2 mins)

“Machine Learning is a technique where machines learn from data instead of being explicitly programmed.”


🔹 2. Types of ML (3 mins)

  • Supervised Learning
  • Unsupervised Learning
  • Reinforcement Learning

👉 Example:

  • Spam detection (Supervised)
  • Customer segmentation (Unsupervised)

🔹 3. Real-Life Applications (3 mins)

  • Healthcare diagnosis
  • Recommendation systems
  • Autonomous vehicles

🔹 4. Simple Example (3 mins)

“Predicting student performance based on previous marks”


🔹 5. Your Research Connection (2 mins)

“In my research, I used ML models like GRU for healthcare diagnosis and sentiment analysis.”


🔹 6. Conclusion (1 min)

“Machine learning is transforming every field, and understanding its basics is essential.”


🎤 Delivery Tips

  • Ask 1–2 questions to audience
  • Use board or slides
  • Give real-life examples
  • Keep it interactive

🚀 BONUS: SMART QUESTIONS YOU CAN ASK PANEL

  • “What are the expectations for research publications?”
  • “Are there interdisciplinary research opportunities?”
  • “Does the institution support patents and funding proposals?”

If you want next level preparation, I can:
✅ Create PPT for your demo lecture
✅ Conduct a mock interview simulation with feedback
✅ Prepare subject-specific deep technical questions

Just tell me 👍

“What are the expectations for research publications?” “Are there interdisciplinary research opportunities?” “Does the institution support patents and funding proposals?”

These are excellent, high-impact questions. But in an interview, how you ask matters just as much as what you ask. Here’s how to ask them professionally, along with when and why 👇


🎯 Polished Ways to Ask These Questions

🔹 1. About Research Publications

❌ Basic:

“What are the expectations for research publications?”

✅ Better Version:

“Could you please share the institution’s expectations regarding research publications, particularly in terms of indexed journals such as Scopus or SCI?”

💡 Why this works:

  • Shows you understand quality publishing
  • Signals research seriousness

🔹 2. About Interdisciplinary Research

❌ Basic:

“Are there interdisciplinary research opportunities?”

✅ Better Version:

“Are there opportunities for interdisciplinary research collaborations, especially in areas like AI integration with other domains such as education or healthcare?”

💡 Why this works:

  • Connects directly with your profile (AI + education + networks)
  • Shows forward-thinking mindset

🔹 3. About Patents & Funding

❌ Basic:

“Does the institution support patents and funding proposals?”

✅ Better Version:

“Does the institution provide support for filing patents and applying for funded research projects, such as guidance, financial assistance, or collaboration opportunities?”

💡 Why this works:

  • Shows you are action-oriented
  • Indicates long-term contribution mindset

🧠 When to Ask These Questions

👉 Ask at the end of the interview, when they say:

  • “Do you have any questions for us?”

👉 Don’t ask too early (it may look self-focused)


Pro Tip: Combine All 3 Into One Powerful Question

If time is limited, say:

“I would like to understand the research ecosystem here—particularly expectations for publications, opportunities for interdisciplinary collaboration, and support for patents or funded projects.”


🚀 Delivery Tips

  • Speak calmly and confidently
  • Maintain eye contact
  • Don’t rush
  • Ask 2–3 questions max (not too many)

🎯 Final Impression Line

After asking, you can close with:

“I am keen to contribute actively to both teaching and research initiatives in your institution.”


If you want, I can next:
✅ Simulate real interview panel conversation (Q&A live style)
✅ Give subject-wise deep technical grilling questions
✅ Prepare answers for tricky HR questions (stress interview)

Just tell me 👍


MOCK INTERVIEW SIMULATION (Panel Style)

👉 Instructions for you:

  • Answer each question out loud or type your answer
  • Keep answers 1–2 minutes max
  • After you respond, compare with my model answer

👨‍⚖️ PANEL ROUND STARTS

🔹 Panelist 1 (HR + Intro)

Q1:
“Dr. Gnanakumaran, please introduce yourself briefly.”

👉 (Pause and answer)

Model Answer:

“I am Dr. R. Gnanakumaran, an Assistant Professor in Computer Science with over 15 years of experience in teaching and industry. I completed my Ph.D. from Bharathidasan University, focusing on energy-efficient routing in wireless networks. I have published several international papers including IEEE and hold patents in AI-based education and energy systems. My interests include AI, IoT, and network optimization, and I actively contribute to academic administration and research activities.”


🔹 Panelist 2 (Research Focus)

Q2:
“What is the key contribution of your Ph.D. work?”

👉 (Answer)

Model Answer:

“My Ph.D. focused on improving energy efficiency in mobile ad-hoc networks. I proposed optimized routing mechanisms based on AODV protocol to reduce power consumption and improve network lifetime. The contribution lies in balancing performance and energy usage, which is critical in wireless communication systems.”


🔹 Panelist 3 (Technical)

Q3:
“Explain AODV and its limitations.”

👉 (Answer)

Model Answer:

“AODV is a reactive routing protocol that establishes routes only when required, reducing unnecessary overhead. However, it suffers from high latency during route discovery, frequent link breakages in dynamic environments, and increased control message overhead. My research addressed these issues by optimizing route selection and energy usage.”


🔹 Panelist 4 (AI / Modern Tech)

Q4:
“How does GRU differ from LSTM?”

👉 (Answer)

Model Answer:

“GRU is a simplified version of LSTM with fewer gates—reset and update gates instead of three gates in LSTM. It has fewer parameters, making it faster to train while still capturing sequential dependencies effectively.”


🔹 Panelist 5 (Teaching Ability)

Q5:
“How do you teach complex topics like Machine Learning to undergraduate students?”

👉 (Answer)

Model Answer:

“I use a step-by-step approach starting with basic concepts, real-life examples, and visual explanations. I incorporate tools, case studies, and interactive sessions to make learning engaging. I also use continuous assessment to ensure understanding.”


🔹 Panelist 1 (HR)

Q6:
“Why do you want to join our institution?”

👉 (Answer)

Model Answer:

“I am looking for an institution that values both teaching excellence and research. I believe my experience in AI, networks, and academic administration can contribute effectively, and I am particularly interested in collaborative research opportunities here.”


🔹 Panelist 2 (Situational)

Q7:
“If students are not interested in your class, what will you do?”

👉 (Answer)

Model Answer:

“I would first identify the reason—whether it’s difficulty level or engagement. Then I would adapt my teaching using interactive methods, real-world examples, and technology tools to make the subject more relatable.”


🧠 📊 FEEDBACK FRAMEWORK (Self-Evaluate)

After answering, check yourself:

✅ Content

  • Did you include concept + example + your work?

✅ Clarity

  • Was your answer simple and structured?

✅ Confidence

  • Did you avoid hesitation words (“uh”, “maybe”)?

✅ Relevance

  • Did you connect answers to your research/experience?

🔥 🎯 SUBJECT-SPECIFIC DEEP TECHNICAL QUESTIONS

These are panel-level tough questions 👇


🌐 Computer Networks (Core Area)

  1. What are the differences between proactive vs reactive routing protocols?
  2. How does AODV handle route maintenance?
  3. Explain QoS in wireless networks
  4. What is congestion control in TCP?
  5. Compare OSI vs TCP/IP models in real implementation
  6. What are challenges in MANETs?

📡 Wireless Networks / WiMAX

  1. What is IEEE 802.16e?
  2. Explain handover mechanisms in WiMAX
  3. How do you reduce energy consumption in wireless networks?
  4. What is throughput vs latency tradeoff?

🤖 AI & Machine Learning

  1. Difference between supervised vs unsupervised learning
  2. What is overfitting and how to prevent it?
  3. Explain Gradient Descent
  4. What is activation function?
  5. Compare GRU vs LSTM vs RNN
  6. What is model evaluation (precision, recall, F1-score)?

🌍 IoT

  1. Architecture of IoT
  2. What are IoT protocols (MQTT, CoAP)?
  3. Security challenges in IoT
  4. Applications of IoT in smart systems

🎓 Teaching & Research

  1. How do you guide Ph.D./PG students?
  2. How do you identify research gaps?
  3. What is impact factor vs indexing?
  4. How do you write a research proposal?

🚀 NEXT LEVEL (If you want)

I can now:
✅ Evaluate your real answers (you type them)
✅ Conduct a stress interview round
✅ Prepare demo lecture PPT + script
✅ Give answers for all deep technical questions

SVEEP -ECI


As part of SVEEP activities, People Election Commission of India are requested to scan the QR code, take the voter pledge, upload a selfie, and download their certificate 

behalf of the Thanjavur District Administration


 

Wednesday, February 4, 2026

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