Class 12 Computer Science (Sumita Arora) Ultra-Detailed Notes: Chapters 10–13
Chapter 10: Relational Database
1. Introduction to Databases
• A Database is a well-organized collection of data that is related in some way and is stored so that it
can be easily accessed, managed, and updated.
• A DBMS (Database Management System) is software that allows users to create, define,
manipulate, and maintain databases. Examples: MySQL, Oracle, MS Access.
• RDBMS (Relational Database Management System) is a DBMS based on the relational model
(developed by E.F. Codd). Data is stored in tables called relations.
2. Features of Relational Database
• Data is organized into tables with rows and columns.
• Tables are related to each other through keys.
• Provides support for SQL (Structured Query Language).
• Supports data integrity, redundancy reduction, security, concurrency, and recovery.
3. Basic Terminology
• Relation: A table in a relational database.
• Tuple: A row in a table; represents a single record.
• Attribute: A column in a table; represents a field.
• Domain: Set of valid values an attribute can take.
• Degree: Number of attributes (columns) in a relation.
• Cardinality: Number of tuples (rows) in a relation.
4. Keys in RDBMS
• Primary Key: A column (or a set of columns) that uniquely identifies each row in a table. Must be
UNIQUE and NOT NULL.
• Candidate Key: All columns that can qualify as a primary key.
• Alternate Key: Candidate keys not chosen as the primary key.
• Composite Key: A key that consists of more than one attribute.
• Foreign Key: A column in one table that refers to the primary key in another table; used to enforce
referential integrity.
5. Integrity Constraints
• NOT NULL: Ensures a column cannot have NULL values.
• UNIQUE: Ensures all values in a column are different.
• CHECK: Ensures the value in a column meets a specific condition.
• DEFAULT: Assigns a default value if no value is specified.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE.
• FOREIGN KEY: Ensures referential integrity between tables.
1
6. Advantages of RDBMS
• Eliminates data redundancy.
• Ensures data consistency.
• Provides better data integrity.
• Allows multi-user access.
• Supports security and authorization.
• Allows data recovery in case of failure.
Chapter 11: Simple Queries in SQL
1. Introduction to SQL
• SQL: Structured Query Language used to interact with databases.
• It is non-procedural, meaning you specify what you want, not how to get it.
2. Types of SQL Commands
• DDL (Data Definition Language):
• CREATE : To create database objects.
• ALTER : To modify database structure.
• DROP : To delete database objects.
• DML (Data Manipulation Language):
• SELECT : To retrieve data.
• INSERT : To add data.
• UPDATE : To modify data.
• DELETE : To remove data.
• DCL (Data Control Language):
• GRANT : To give privileges.
• REVOKE : To remove privileges.
3. SELECT Statement
SELECT column1, column2 FROM table_name;
• * selects all columns.
• DISTINCT removes duplicates.
4. WHERE Clause
SELECT * FROM students WHERE age > 17;
• Filters records.
• Uses operators: =, <>, >, <, >=, <=
• Supports logical conditions: AND, OR, NOT
2
5. Special Operators
• BETWEEN : Specifies a range.
• IN : Checks for presence in a list.
• LIKE : Used for pattern matching ( % , _ ).
• IS NULL , IS NOT NULL : For handling NULL values.
6. ORDER BY Clause
SELECT * FROM students ORDER BY name ASC;
• ASC for ascending (default), DESC for descending.
Chapter 12: Table Creation and Data Manipulation Commands
1. Creating Tables
CREATE TABLE students (
rollno INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
marks FLOAT CHECK (marks >= 0)
);
• Defines structure, data types, and constraints.
2. Altering Table Structure
• Add column: ALTER TABLE students ADD gender CHAR(1);
• Modify column: ALTER TABLE students MODIFY marks DOUBLE;
• Drop column: ALTER TABLE students DROP COLUMN age;
3. Dropping a Table
• Deletes the table and all data: DROP TABLE students;
4. Inserting Data
INSERT INTO students VALUES (101, 'Amit', 17, 87.5);
INSERT INTO students (rollno, name) VALUES (102, 'Neha');
• Can specify columns explicitly.
3
5. Updating Records
UPDATE students SET marks = 90 WHERE rollno = 101;
• Updates values based on conditions.
6. Deleting Records
DELETE FROM students WHERE name = 'Neha';
• Deletes specific rows from a table.
Chapter 13: Grouping Records, Joins in SQL
1. Aggregate Functions
• Operate on a set of values and return a single value.
• COUNT() , SUM() , AVG() , MIN() , MAX()
2. GROUP BY Clause
SELECT dept, COUNT(*) FROM employees GROUP BY dept;
• Groups rows with same values in specified columns.
• Often used with aggregate functions.
3. HAVING Clause
SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary) >
50000;
• Filters records after grouping (like WHERE for groups).
4. JOIN Operations
a. INNER JOIN
• Returns rows where there is a match in both tables.
SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON
e.dept_id = d.dept_id;
4
b. LEFT JOIN (LEFT OUTER JOIN)
• Returns all rows from the left table and matched rows from right table.
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON
e.dept_id = d.dept_id;
c. RIGHT JOIN (RIGHT OUTER JOIN)
• Returns all rows from the right table and matched rows from left table.
d. FULL OUTER JOIN
• Returns rows when there is a match in one of the tables.
• Not supported in MySQL (use UNION of LEFT and RIGHT JOINs).
5. Table Aliases
• Used to shorten table names or improve readability.
SELECT e.name FROM employees e;
6. Nested Queries (Subqueries)
• A query within another query.
SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
• Can be used in SELECT, FROM, WHERE clauses.
These comprehensive notes include every critical concept, SQL command, and best practice from Chapters
10–13 of Sumita Arora’s Class 12 Computer Science. Let me know if you’d like to generate a PDF, quiz
questions, or flashcards from these!
