Samacheer Kalvi 12th Computer Science Chapter 12 SQL Exercise Solutions | KalviGavani

Chapter 12 — Structured Query Language (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 whenever possible. 4. Review the Common Mistakes and Exam Checklist before your exam.

Key Concepts

Constraints ensure data integrity: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN().

Clauses: WHERE (row filter), GROUP BY (grouping), HAVING (group filter), ORDER BY (sorting), DISTINCT (unique values).

Multiple Choice Questions

Q1. Which of the following is a DDL command?

• (a) INSERT   (b) SELECT   (c) ALTER   (d) UPDATE

Q2. The SQL keyword used to eliminate duplicate rows is:

• (a) UNIQUE   (b) DISTINCT   (c) DIFFERENT   (d) REMOVE

Q3. Which clause is used to filter groups after aggregation?

• (a) WHERE   (b) GROUP BY   (c) HAVING   (d) ORDER BY

Q4. A PRIMARY KEY constraint implies:

• (a) UNIQUE only   (b) NOT NULL only   (c) UNIQUE + NOT NULL   (d) CHECK

Q5. Which command removes all rows from a table but keeps its structure?

• (a) DROP   (b) DELETE   (c) TRUNCATE   (d) ALTER

Q6. The foreign key references:

• (a) the same table   (b) another table's primary key   (c) any column   (d) an index

Fill in the Blanks

1. The ______ clause sorts the result set in ascending or descending order. → ORDER BY 2. ______ function returns the number of rows in a result set. → COUNT() 3. The command to add a new column to an existing table is ______. → ALTER TABLE ... ADD COLUMN 4. To insert a row into a table, the command used is ______. → INSERT INTO 5. ______ constraint ensures that a column cannot contain null values. → NOT NULL 6. The ______ operation combines rows from two tables based on a related column. → JOIN

True or False

Match the Following

Short Answer Questions

Q1. Differentiate between DDL and DML with two examples each.

Q2. What is a constraint? List any four constraints with their purpose.

A constraint is a rule enforced on table columns to maintain data accuracy and integrity.

Q3. Explain the difference between WHERE and HAVING clauses.

• WHERE filters individual rows before grouping; cannot use aggregate functions.
• HAVING filters groups after GROUP BY aggregation; can use aggregate functions.

-- WHERE filters rows
SELECT dept, AVG(salary) FROM emp WHERE salary > 10000 GROUP BY dept;

-- HAVING filters groups
SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING AVG(salary) > 30000;

Q4. Write the syntax to create a table with constraints.

CREATE TABLE student (
    roll_no   INT PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    dept      VARCHAR(30) DEFAULT 'CS',
    mark      INT CHECK (mark >= 0 AND mark <= 100),
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Q5. What is the difference between DELETE and TRUNCATE?

Detailed Answer Questions

Q1. Explain DDL commands (CREATE, ALTER, DROP) with examples.

CREATE — defines a new table (or database, view, index):

CREATE TABLE employee (
    emp_id    INT PRIMARY KEY,
    emp_name  VARCHAR(50) NOT NULL,
    salary    DECIMAL(10,2),
    dept      VARCHAR(30)
);

ALTER — modifies an existing table's structure:

-- Add a new column
ALTER TABLE employee ADD COLUMN joining_date DATE;

-- Modify a column's data type
ALTER TABLE employee MODIFY COLUMN salary DECIMAL(12,2);

-- Drop a column
ALTER TABLE employee DROP COLUMN dept;

DROP — removes the entire table (structure + data):

DROP TABLE employee;

Once dropped, the table cannot be recovered (unless a backup exists).

Q2. Explain DML commands (INSERT, UPDATE, DELETE) with examples.

INSERT — adds new rows to a table:

-- Insert a full row
INSERT INTO employee VALUES (101, 'Arun', 55000.00, 'IT');

-- Insert specific columns
INSERT INTO employee (emp_id, emp_name) VALUES (102, 'Bala');

UPDATE — modifies existing data:

-- Update specific rows
UPDATE employee SET salary = 60000 WHERE emp_id = 101;

-- Update all rows (dangerous without WHERE!)
UPDATE employee SET dept = 'HR';

DELETE — removes rows:

-- Delete specific rows
DELETE FROM employee WHERE emp_id = 102;

-- Delete all rows (table structure remains)
DELETE FROM employee;

Q3. Explain SELECT queries with WHERE, ORDER BY, GROUP BY, and aggregate functions.

Basic SELECT:

SELECT emp_name, salary FROM employee;
SELECT * FROM employee;

WHERE clause — row-level filtering:

SELECT emp_name, salary FROM employee WHERE dept = 'IT';
SELECT * FROM employee WHERE salary BETWEEN 40000 AND 70000;
SELECT * FROM employee WHERE emp_name LIKE 'A%';

ORDER BY — sorting results:

SELECT * FROM employee ORDER BY salary DESC;
SELECT * FROM employee ORDER BY dept ASC, salary DESC;

Aggregate Functions:

SELECT COUNT(*) FROM employee;
SELECT AVG(salary) FROM employee WHERE dept = 'IT';
SELECT MAX(salary), MIN(salary) FROM employee;
SELECT SUM(salary) FROM employee;

GROUP BY — grouping rows:

SELECT dept, COUNT(*) FROM employee GROUP BY dept;
SELECT dept, AVG(salary) FROM employee GROUP BY dept;

HAVING — filtering groups:

SELECT dept, AVG(salary)
FROM employee
GROUP BY dept
HAVING AVG(salary) > 50000;

DISTINCT — removing duplicates:

SELECT DISTINCT dept FROM employee;

Q4. Explain SQL Joins with examples.

Joins combine rows from two or more tables based on a related column.

-- Sample tables
-- employee: emp_id, emp_name, dept_id
-- department: dept_id, dept_name

INNER JOIN — returns only matching rows from both tables:

SELECT e.emp_name, d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;

LEFT JOIN — all rows from the left table, matched rows from the right (NULL if no match):

SELECT e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id;

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

SELECT e.emp_name, d.dept_name
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.dept_id;

Q5. Explain subqueries with examples.

A subquery is a query nested inside another query.

Subquery in WHERE clause:

-- Employees earning more than the average salary
SELECT emp_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

Subquery with IN:

-- Employees in IT or HR department
SELECT emp_name
FROM employee
WHERE dept_id IN (
    SELECT dept_id FROM department WHERE dept_name IN ('IT', 'HR')
);

Subquery in FROM clause (derived table):

SELECT dept, avg_sal
FROM (
    SELECT dept, AVG(salary) AS avg_sal
    FROM employee
    GROUP BY dept
) AS dept_avg
WHERE avg_sal > 50000;

Common Mistakes

Exam Checklist

• [ ] Can write CREATE TABLE with all six constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT)
• [ ] Can differentiate DDL, DML, DQL, DCL, TCL with examples
• [ ] Can write INSERT, UPDATE, DELETE with and without WHERE
• [ ] Can write SELECT with WHERE, ORDER BY, GROUP BY, HAVING, DISTINCT
• [ ] Can use all five aggregate functions (COUNT, SUM, AVG, MAX, MIN)
• [ ] Can write INNER JOIN and LEFT JOIN queries
• [ ] Can write subqueries in WHERE and FROM clauses
• [ ] Know the difference between DELETE and TRUNCATE
• [ ] Know the difference between WHERE and HAVING
• [ ] Can explain PRIMARY KEY vs FOREIGN KEY vs UNIQUE constraints