Samacheer Kalvi 12th Computer Science Chapter 15 Data Manipulation Through SQL Exercise Solutions | KalviGavani

Chapter 15 — Data Manipulation Through SQL: Exercise Solutions Guide

How to Use This Page

1. Attempt every exercise in your Samacheer Kalvi textbook on your own first. 2. Compare your answers with the solutions below — focus on the method, not just the final result. 3. Practise the SQL statements on a computer using SQLite or MySQL. 4. Review the Common Mistakes and Exam Checklist before your exam.

Key Concepts

Aggregate Functions

GROUP BY and HAVING

-- GROUP BY: Group rows sharing a column value
SELECT dept, COUNT(*) FROM student GROUP BY dept;

-- HAVING: Filter groups (must follow GROUP BY)
SELECT dept, AVG(mark) FROM student
GROUP BY dept
HAVING AVG(mark) > 75;

Subqueries (Nested Queries)

A query placed inside another query:

-- Subquery in WHERE
SELECT * FROM student
WHERE mark > (SELECT AVG(mark) FROM student);

JOINs

Set Operations

Views

A view is a virtual table based on a query:

CREATE VIEW honors AS
SELECT name, mark FROM student WHERE mark > 90;

Multiple Choice Questions

Q1. Which aggregate function returns the total of numeric values?

• (a) COUNT   (b) AVG   (c) SUM   (d) MAX

Q2. The HAVING clause is used to filter:

• (a) Individual rows   (b) Groups formed by GROUP BY   (c) Columns   (d) Tables

Q3. Which JOIN returns only matching rows from both tables?

• (a) LEFT JOIN   (b) RIGHT JOIN   (c) INNER JOIN   (d) FULL JOIN

Q4. A subquery is:

• (a) A query inside another query   (b) A join operation   (c) A table   (d) A view

Q5. UNION removes duplicate rows. Which keyword includes duplicates?

• (a) UNION ALL   (b) UNION DISTINCT   (c) MERGE   (d) COMBINE

Q6. A VIEW is:

• (a) A physical table stored on disk   (b) A virtual table based on a query   (c) An index   (d) A constraint

Fill in the Blanks

1. The ______ function returns the number of rows in a result. → COUNT() 2. The clause used to group rows that share a value is ______. → GROUP BY 3. ______ keyword removes duplicate rows from the UNION result. → UNION (UNION removes duplicates by default; UNION ALL keeps them) 4. A query nested inside another query is called a ______. → subquery (or nested query) 5. The ______ clause filters groups after aggregation. → HAVING 6. A ______ is a virtual table that displays data from one or more tables. → view

True or False

Match the Following

Short Answer Questions

Q1. Explain the five aggregate functions with examples.

Using a student table with columns: name, mark, dept.

-- COUNT: Number of students
SELECT COUNT(*) FROM student;

-- SUM: Total marks
SELECT SUM(mark) FROM student;

-- AVG: Average mark
SELECT AVG(mark) FROM student;

-- MAX: Highest mark
SELECT MAX(mark) FROM student;

-- MIN: Lowest mark
SELECT MIN(mark) FROM student;

Q2. Differentiate between WHERE and HAVING.

-- WHERE: filter rows before grouping
SELECT dept, AVG(mark) FROM student WHERE mark > 40 GROUP BY dept;

-- HAVING: filter groups after aggregation
SELECT dept, AVG(mark) FROM student GROUP BY dept HAVING AVG(mark) > 75;

Q3. What is a subquery? Give an example.

A subquery (nested query) is a SELECT statement embedded inside another query.

-- Find students scoring above average
SELECT name, mark FROM student
WHERE mark > (SELECT AVG(mark) FROM student);

-- Subquery in FROM (derived table)
SELECT dept, avg_mark
FROM (SELECT dept, AVG(mark) AS avg_mark FROM student GROUP BY dept) AS dept_avg
WHERE avg_mark > 70;

Q4. Explain INNER JOIN with an example.

INNER JOIN returns only the rows where there is a match in both tables.

-- Tables: student(roll, name, dept_id), department(dept_id, dept_name)

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

Only students with a matching dept_id in the department table are returned.

Q5. What is a view? Write the syntax to create and drop a view.

A view is a virtual table defined by a stored SELECT query. It simplifies complex queries and restricts data access.

-- Create a view
CREATE VIEW high_scorers AS
SELECT name, mark FROM student WHERE mark >= 90;

-- Query the view like a table
SELECT * FROM high_scorers;

-- Drop the view
DROP VIEW high_scorers;

Detailed Answer Questions

Q1. Explain GROUP BY with HAVING using a detailed example.

Consider a sales table: salesperson, region, amount.

-- Create and populate the table
CREATE TABLE sales (
    id INT PRIMARY KEY,
    salesperson VARCHAR(30),
    region VARCHAR(20),
    amount INT
);

INSERT INTO sales VALUES
(1, 'Arun', 'North', 5000),
(2, 'Bala', 'South', 7000),
(3, 'Arun', 'North', 3000),
(4, 'Chitra', 'South', 9000),
(5, 'Bala', 'North', 6000),
(6, 'Chitra', 'South', 8000);

GROUP BY — aggregate per group:

-- Total sales by region
SELECT region, SUM(amount) FROM sales GROUP BY region;

-- Result:
-- North | 14000
-- South | 24000

HAVING — filter groups:

-- Regions with total sales above 15000
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING SUM(amount) > 15000;

-- Result:
-- South | 24000

Order of execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Q2. Write Python code to create an SQLite database, create a table, insert data, and query using aggregate functions.

import sqlite3

# Step 1: Connect to database (creates file if not exists)
conn = sqlite3.connect("school.db")
cur = conn.cursor()

# Step 2: Create table
cur.execute('''
    CREATE TABLE IF NOT EXISTS student (
        roll_no INT PRIMARY KEY,
        name TEXT NOT NULL,
        dept TEXT,
        mark INT
    )
''')

# Step 3: Insert data
students = [
    (1, "Arun", "CS", 92),
    (2, "Bala", "CS", 85),
    (3, "Chitra", "IT", 78),
    (4, "Deepa", "IT", 91),
    (5, "Ezhil", "CS", 67)
]
cur.executemany("INSERT INTO student VALUES (?, ?, ?, ?)", students)
conn.commit()

# Step 4: Aggregate functions
print("Total students:", cur.execute("SELECT COUNT(*) FROM student").fetchone()[0])
print("Average mark:", cur.execute("SELECT AVG(mark) FROM student").fetchone()[0])
print("Highest mark:", cur.execute("SELECT MAX(mark) FROM student").fetchone()[0])
print("Lowest mark:", cur.execute("SELECT MIN(mark) FROM student").fetchone()[0])
print("Total marks:", cur.execute("SELECT SUM(mark) FROM student").fetchone()[0])

# Step 5: GROUP BY
print("\nAverage mark by department:")
for row in cur.execute("SELECT dept, AVG(mark) FROM student GROUP BY dept"):
    print(f"  {row[0]}: {row[1]:.2f}")

# Step 6: HAVING
print("\nDepartments with average above 80:")
for row in cur.execute("SELECT dept, AVG(mark) FROM student GROUP BY dept HAVING AVG(mark) > 80"):
    print(f"  {row[0]}: {row[1]:.2f}")

conn.close()

Q3. Explain subqueries with multiple examples.

Example 1: Subquery in WHERE clause — find students above average

SELECT name, mark FROM student
WHERE mark > (SELECT AVG(mark) FROM student);

Example 2: Subquery with IN — find students in specific departments

SELECT name FROM student
WHERE dept IN (
    SELECT dept_name FROM department WHERE location = 'Chennai'
);

Example 3: Subquery in FROM clause (derived table)

SELECT dept, avg_mark
FROM (
    SELECT dept, AVG(mark) AS avg_mark
    FROM student
    GROUP BY dept
) AS dept_avg
WHERE avg_mark > 80;

Example 4: Correlated subquery — find the highest scorer in each department

SELECT name, dept, mark
FROM student s
WHERE mark = (
    SELECT MAX(mark)
    FROM student
    WHERE dept = s.dept
);

Example 5: Subquery in SELECT clause

SELECT name, mark,
    (SELECT AVG(mark) FROM student) AS overall_avg
FROM student;

Q4. Explain all types of JOINs with examples.

Using two tables:

• student(roll, name, dept_id)
• department(dept_id, dept_name)

INNER JOIN — only matching rows:

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

LEFT JOIN — all rows from left table, matched from right:

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

RIGHT JOIN — all rows from right table, matched from left:

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

Comparison Table:

Q5. Explain set operations (UNION, INTERSECT, EXCEPT) with examples.

Using two tables:

• cricket_players(name)
• football_players(name)

UNION — all unique rows from both queries:

SELECT name FROM cricket_players
UNION
SELECT name FROM football_players;

UNION ALL — all rows including duplicates:

SELECT name FROM cricket_players
UNION ALL
SELECT name FROM football_players;

INTERSECT — rows common to both queries:

SELECT name FROM cricket_players
INTERSECT
SELECT name FROM football_players;

EXCEPT (MINUS in Oracle) — rows in first but not in second:

SELECT name FROM cricket_players
EXCEPT
SELECT name FROM football_players;

Rules for set operations: 1. Both queries must return the same number of columns. 2. Corresponding columns must have compatible data types. 3. Column names come from the first query.

Common Mistakes

Exam Checklist

• [ ] Can write all five aggregate functions (COUNT, SUM, AVG, MAX, MIN) with examples
• [ ] Can differentiate WHERE and HAVING with examples
• [ ] Can write GROUP BY queries with and without HAVING
• [ ] Can write subqueries in WHERE, FROM, and SELECT clauses
• [ ] Can write INNER JOIN, LEFT JOIN, and RIGHT JOIN queries
• [ ] Understand UNION, INTERSECT, and EXCEPT set operations
• [ ] Can create and query views using CREATE VIEW
• [ ] Can use sqlite3 module: connect, cursor, execute, commit, close
• [ ] Can write Python programs that interact with SQLite databases
• [ ] Know the order of SQL execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY