SQL Crash Course for Class 12 CBSE
This comprehensive crash course covers all essential SQL concepts for CBSE Class 12 Computer
Science students, worth 20 marks in the board examination.
Database Concepts
Understanding databases is fundamental to working with SQL [1] [2] [3] . A database is an
organized collection of data stored electronically in a computer system [1] [4] . The Database
Management System (DBMS) is software that enables users to create, maintain, and use
databases [1] [3] .
Key Terminology:
RDBMS: Relational Database Management System (MySQL, Oracle) [1] [5]
Table: Data stored in rows and columns [1] [6]
Row/Record/Tuple: Horizontal data entry [1] [7]
Column/Field/Attribute: Vertical data category [1] [7]
Primary Key: Unique identifier for records [1] [2]
Foreign Key: Links tables together [1] [2]
Degree: Number of columns in table [2] [8]
Cardinality: Number of rows in table [2] [8]
SQL Basics
SQL (Structured Query Language) is a language designed to manage database [1] [6] . Key
characteristics include:
Case insensitive (SELECT = select) [6]
Semicolon mandatory at end of statements [6]
Two main categories: DDL and DML [1] [7]
DDL Commands (Data Definition Language)
DDL commands define database structure [9] [10] [11] . Essential commands include:
Command Purpose Example
Creates new
CREATE DATABASE CREATE DATABASE school;
database
USE DATABASE Opens database USE school;
Command Purpose Example
Lists all
SHOW DATABASES SHOW DATABASES;
databases
CREATE TABLE student(roll INT PRIMARY KEY, name
CREATE TABLE Creates new table
VARCHAR(20), age INT);
Shows table
DESCRIBE DESC student;
structure
ALTER TABLE - Adds new
ALTER TABLE student ADD phone VARCHAR(15);
ADD column
ALTER TABLE - Changes column
ALTER TABLE student MODIFY name VARCHAR(30);
MODIFY type
ALTER TABLE -
Removes column ALTER TABLE student DROP COLUMN phone;
DROP
DROP TABLE Deletes table DROP TABLE student;
SHOW TABLES Lists all tables SHOW TABLES;
DML Commands (Data Manipulation Language)
DML commands manipulate data within databases [9] [10] [11] :
Command Purpose Example
INSERT Adds new records INSERT INTO student VALUES(1, 'Rahul', 17);
INSERT Adds to specific INSERT INTO student(roll, name) VALUES(2,
SPECIFIC columns 'Priya');
SELECT ALL Retrieves all data SELECT * FROM student;
SELECT Retrieves specific
SELECT name, age FROM student;
SPECIFIC columns
UPDATE Modifies existing data UPDATE student SET age = 18 WHERE roll = 1;
DELETE Removes records DELETE FROM student WHERE roll = 2;
Constraints
Constraints ensure data accuracy and reliability [12] [4] :
NOT NULL: Column cannot be empty
UNIQUE: No duplicate values allowed
PRIMARY KEY: NOT NULL + UNIQUE combination
DEFAULT: Provides default value if not specified
CHECK: Validates data conditions
WHERE Clause & Operators
The WHERE clause filters records based on conditions [7] [6] :
Operator Type Operators Example
Comparison =, !=, <>, <, >, <=, >= WHERE age > 18
Logical AND, OR, NOT WHERE age > 18 AND class = 'XII'
Pattern Matching LIKE ('%', '_') WHERE name LIKE 'R%'
Range BETWEEN...AND WHERE marks BETWEEN 70 AND 90
List IN (values...) WHERE class IN ('XI', 'XII')
NULL Check IS NULL, IS NOT NULL WHERE phone IS NULL
Aggregate Functions
Aggregate functions perform calculations on groups of rows [13] [14] [15] :
Function Purpose Example
COUNT() Counts rows SELECT COUNT(*) FROM student;
SUM() Sum of values SELECT SUM(marks) FROM student;
AVG() Average value SELECT AVG(marks) FROM student;
MIN() Minimum value SELECT MIN(marks) FROM student;
MAX() Maximum value SELECT MAX(marks) FROM student;
Important: Aggregate functions ignore NULL values except COUNT(*) [13] [15] .
Grouping and Sorting
ORDER BY Clause
Sorts result set in ascending (default) or descending order [7] [6] :
SELECT * FROM student ORDER BY name ASC;
SELECT * FROM student ORDER BY marks DESC;
GROUP BY Clause
Groups rows with same values for summary calculations [13] [16] :
SELECT class, COUNT(*) FROM student GROUP BY class;
SELECT class, AVG(marks) FROM student GROUP BY class;
HAVING Clause
Filters groups created by GROUP BY [13] [16] :
SELECT class FROM student GROUP BY class HAVING COUNT(*) > 5;
SQL Clause Order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY [16]
Table Joins
Joins combine rows from multiple tables [17] [18] [19] :
Join Type Description Example
Cartesian/Cross All possible
SELECT * FROM table1, table2;
Join combinations
Based on equality SELECT * FROM table1, table2 WHERE table1.id
Equi Join
condition = table2.id;
Automatic matching
Natural Join SELECT * FROM table1 NATURAL JOIN table2;
columns
Practice Questions
Basic DDL Questions
1. Write a command to create a database named 'SCHOOL'. [1 mark]
Solution: CREATE DATABASE SCHOOL;
2. Create a table 'STUDENT' with columns: RollNo (INT, Primary Key), Name
(VARCHAR(25)), Class (VARCHAR(5)), Marks (INT). [2 marks]
Solution: CREATE TABLE STUDENT (RollNo INT PRIMARY KEY, Name VARCHAR(25), Class
VARCHAR(5), Marks INT);
Basic DML Questions
3. Display names of all students who scored more than 80 marks. [2 marks]
Solution: SELECT Name FROM STUDENT WHERE Marks > 80;
4. Find the average marks of all students. [1 mark]
Solution: SELECT AVG(Marks) FROM STUDENT;
Advanced Questions
5. Display class-wise count of students. [2 marks]
Solution: SELECT Class, COUNT(*) FROM STUDENT GROUP BY Class;
6. Display classes having more than 5 students. [3 marks]
Solution: SELECT Class FROM STUDENT GROUP BY Class HAVING COUNT(*) > 5;
Sample Table-Based Questions
EMPLOYEE Table:
EmpID Name Dept Salary JoinDate
101 Amit IT 50000 2020-01-15
102 Priya HR 45000 2019-03-20
103 Rahul IT 55000 2021-06-10
104 Sunita Finance 48000 2020-08-25
105 Karan IT 52000 2022-01-12
Practice Questions:
1. Display details of employees working in IT department. [2 marks]
Answer: SELECT * FROM EMPLOYEE WHERE Dept = 'IT';
2. Display department-wise average salary. [3 marks]
Answer: SELECT Dept, AVG(Salary) FROM EMPLOYEE GROUP BY Dept;
Multiple Choice Questions
1. Which of the following is NOT an aggregate function?
A) SUM() B) COUNT() C) DISTINCT() D) AVG()
Answer: C - DISTINCT is a clause, not an aggregate function.
2. Which clause is used with GROUP BY to filter groups?
A) WHERE B) HAVING C) ORDER BY D) DISTINCT
Answer: B - HAVING clause filters groups created by GROUP BY.
Important Notes for Exam
Use single quotes for string values: 'Rahul' [20]
Date format: 'YYYY-MM-DD' [20]
DISTINCT eliminates duplicate records [6]
WHERE comes before GROUP BY [16]
HAVING comes after GROUP BY [16]
NULL values require IS NULL/IS NOT NULL (never = NULL) [20]
Exam Preparation Tips
1. Practice table creation with all constraint types
2. Master aggregate functions with GROUP BY and HAVING
3. Understand the difference between WHERE and HAVING clauses
4. Remember the correct order of SQL clauses
5. Practice JOIN operations with sample tables
6. Focus on NULL value handling in conditions
7. Learn to combine multiple conditions using AND/OR
8. Practice questions from NCERT and previous year papers [21] [22] [23]
This crash course provides comprehensive coverage of SQL concepts essential for CBSE Class
12 Computer Science examination, with practical examples and extensive practice questions to
ensure thorough preparation.
⁂
1. https://techtipnow.in/sql-class-12-notes/
2. https://www.geeksforgeeks.org/computer-science-fundamentals/cbse-class-12th-computer-science-u
nit-3-notes/
3. https://techtipnow.in/database-concepts-class-12/
4. https://cbseskilleducation.com/database-concepts-class-12-notes/
5. https://pythonschoolkvs.files.wordpress.com/2020/06/class-xii-unit-iii-dbms-session-2020-21-1.pdf
6. https://www.teachoo.com/17581/3908/SQL---Cheat-Sheet/category/Concepts/
7. https://ladderpython.com/lesson/sql-commands-for-class-xii-cbse/
8. https://www.scribd.com/document/684615115/Database-Concepts-Class-12-Important-Questions
9. https://keerthicomputerstudymaterials.files.wordpress.com/2016/10/chapter-14-sql-commands.pdf
10. https://www.programiz.com/sql/commands
11. https://www.almabetter.com/bytes/tutorials/sql/dml-ddl-commands-in-sql
12. https://ncert.nic.in/textbook/pdf/lecs108.pdf
13. https://www.geeksforgeeks.org/sql/aggregate-functions-in-sql/
14. https://www.w3schools.com/sql/sql_aggregate_functions.asp
15. https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions
16. https://python4csip.com/files/download/Database Query Using SQL.pdf
17. https://www.w3schools.com/sql/sql_join.asp
18. https://byjus.com/gate/join-query-in-sql-notes/
19. https://pythontrends.files.wordpress.com/2019/01/chapter-eng-table-joins-and-indexes-in-sql.pdf
20. https://python4csip.com/files/download/SOLUTION WORKSHEET SQL.pdf
21. https://ladderpython.com/lesson/sql-based-questions-with-solutions/
22. https://cbseacademic.nic.in/web_material/SQP/ClassXII_2023_24/InformaticsPractices-SQP.pdf
23. https://www.toprankers.com/cbse-class-12-computer-science-sql-questions
