Class 12 DBMS and Structured Query Language Revision Notes

Class 12 Computer Science Study Guide: Class 12 DBMS and Structured Query Language Revision Notes

Explore structured advanced study materials through the Class 12 DBMS and Structured Query Language Revision Notes. Tailored for Class 12 learners, utilizing these Computer Science resources ensures thorough preparation and strengthens foundational knowledge before final CBSE evaluations.

Download DBMS and Structured Query Language Study Material PDF

Navigate directly to the advanced Computer Science study materials using the digital viewer below. Each resource pack includes detailed conceptual summaries and solved practice questions, allowing students to instantly reinforce their learning.

DBMS and Structured Query Language

Unit III: Database Management System and SQL

Basic Database concepts

Data: Raw facts and figures which are useful to an organization. We cannot take decisions on the basis of data.

Information: Well processed data is called information. We can take decisions on the basis of information.

Field: Set of characters that represents a specific data element.

Record: Collection of fields is called a record. A record can have fields of different data types.

File: Collection of similar types of records is called a file.

Table: Collection of rows and columns that contains useful data/information is called a table. A table generally refers to the passive entity which is kept in a secondary storage device.

Relation: Relation (collection of rows and columns) generally refers to an active entity on which we can perform various operations.

Database: Collection of logically related data along with its description is termed as a database.

Tuple: A row in a relation is called a tuple.

Attribute: A column in a relation is called an attribute. It is also termed as field or data item.

Degree: Number of attributes in a relation is called the degree of a relation.

Cardinality: Number of tuples in a relation is called the cardinality of a relation.

Primary Key: A primary key is a key that can uniquely identify the records/tuples in a relation. This key can never be duplicated and never NULL.

Foreign Key: A non-key attribute of a table acting as the primary key in some other table is known as a foreign key in its current table. This key is used to enforce referential integrity in an RDBMS.

Candidate Key: Attributes of a table which can serve as a primary key are called candidate keys.

Alternate Key: All the candidate keys other than the primary key of a relation are alternate keys for the relation.

DBA: Data Base Administrator is a person (manager) that is responsible for defining the database schema, setting security features in the database, ensuring proper functioning of the databases etc.

Select Operation: The select operation selects tuples from a relation which satisfy a given condition. It is denoted by the lowercase Greek letter σ (sigma).

Project Operation: The project operation selects columns from a relation which satisfy a given condition. It is denoted by the lowercase Greek letter π (pi). It can be thought of as picking a subset of all available columns.

Union Operation: The union (denoted as ∪) of a collection of relations is the set of all distinct tuples in the collection. It is a binary operation that needs two relations.

Set Difference Operation: This is denoted by – (minus) and is a binary operation. It results in a set of tuples that are in one relation but not in another.

Structured Query Language

SQL is a non-procedural language that is used to create, manipulate and process the databases (relations).

  1. Data Definition Language (DDL)
    DDL contains commands that are used to create the tables, databases, indexes, views, sequences and synonyms etc.
    e.g., CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER TABLE etc.
  2. Data Manipulation Language (DML)
    DML contains commands that can be used to manipulate the database objects and to query the databases for information retrieval.
    e.g., SELECT, INSERT, DELETE, UPDATE etc.
  3. Transaction Control Language (TCL)
    TCL includes commands to control the transactions in a database system. The commonly used commands in TCL are COMMIT, ROLLBACK etc.

Operators in SQL

The following are the commonly used operators in SQL:

  1. Arithmetic Operators: +, -, *, /
  2. Relational Operators: =, <, >, <=, >=, <>
  3. Logical Operators: OR, AND, NOT

Data types of SQL

Just like any other programming language, the facility of defining data of various types is available in SQL also. Following are the most common data types of SQL:

  1. NUMBER, e.g. NUMBER(n,d), NUMBER(5,2)
  2. CHAR, e.g. CHAR(SIZE)
  3. VARCHAR / VARCHAR2, e.g. VARCHAR2(SIZE)
  4. DATE, format DD-MON-YYYY

Constraints

Constraints are the conditions that can be enforced on the attributes of a relation. The constraints come into play whenever we are trying to insert, delete or update a record in a relation.

Not null ensures that we cannot leave a column as null, i.e. a value has to be supplied for that column.
e.g. name varchar(25) not nullUnique constraint means that the values under that column are always unique.
e.g. Roll_no number(3) uniquePrimary key constraint means that a column cannot have duplicate values and not even a null value.
e.g. Roll_no number(3) primary key

The main difference between the unique and primary key constraint is that a column specified as unique may have a null value but the primary key constraint does not allow null values in the column.

Foreign key is used to enforce referential integrity and is declared as a primary key in some other table.
e.g. cust_id varchar(5) references master(cust_id)

It declares the cust_id column as a foreign key that refers to the cust_id field of table master. That means we cannot insert a value in the cust_id field whose corresponding value is not present in the cust_id field of the master table. Moreover, we cannot delete any row in the master table if a corresponding value of the cust_id field exists in the dependent table.

Check constraint limits the values that can be inserted into a column of a table.
e.g. marks number(3) check(marks>=0)

The above statement declares marks to be of type number and while inserting or updating, it is ensured that its value is always greater than or equal to zero.

Default constraint is used to specify a default value to a column of a table automatically. This default value will be used when the user does not enter any value for that column.
e.g. balance number(5) default = 0

SQL COMMANDS

1. Create Table command is used to create a table. The syntax of this command is:
CREATE TABLE <Table_name>
  ( column_name1 data_type1 [(size) column_constraints],
    column_name2 data_type2 [(size) column_constraints],
    [<table_constraint> (column_names)] );
2. The ALTER TABLE command is used to change the definition (structure) of an existing table.
ALTER TABLE <Table_name> ADD/MODIFY <Column_definition>;      -- to add or modify a column
ALTER TABLE <Table_name> DROP COLUMN <Column_name>;          -- to delete a column
3. The INSERT command: The rows (tuples) are added to a table by using the INSERT command. The syntax of the INSERT command is:
INSERT INTO <table_name> [(<column_list>)] VALUES (<value_list>);

e.g.,

INSERT INTO EMP (empno, ename, sex, sal, deptno) VALUES(1001, 'Ravi', 'M', 4500.00, 10);

If the order of values matches the actual order of columns in the table then it is not required to give the column_list in the INSERT command. e.g.

INSERT INTO EMP VALUES(1001, 'Ravi', 'M', 4500.00, 10);
4. The UPDATE command is used to change the value in a table. The syntax of this command is:
UPDATE <table_name>
SET column_name1=newvalue1/expression [, column_name2=newvalue2/expression, ......]
WHERE <condition>;

e.g., to increase the salary of all the employees of department no. 10 by 10%, the command will be:

UPDATE emp
SET sal=sal*1.1
WHERE Deptno=10;
5. The DELETE command removes rows from a table. This removes the entire rows, not individual field values. The syntax of this command is
DELETE FROM <table_name>
[WHERE <condition>];

e.g., to delete the tuples from EMP that have salary less than 2000, the following command is used:

DELETE FROM emp WHERE sal<2000;

To delete all tuples from the emp table:

DELETE FROM emp;
6. The SELECT command is used to make queries on a database. A query is a command that is given to produce certain specified information from the database table(s). The SELECT command can be used to retrieve a subset of rows or columns from one or more tables. The syntax of the SELECT command is:
SELECT <Column-list>
FROM <table_name>
[WHERE <condition>]
[GROUP BY <column_list>]
[HAVING <condition>]
[ORDER BY <column_list> [ASC|DESC]];

The select clause lists the attributes desired in the result of a query. e.g., to display the names of all employees in the emp relation:

select ename from emp;

To force the elimination of duplicates, insert the keyword distinct after select. Find the number of all departments in the emp relation, and remove duplicates:

select distinct deptno from emp;

An asterisk (*) in the select clause denotes “all attributes”:

SELECT * FROM emp;

The select clause can contain arithmetic expressions involving the operations +, –, * and /, operating on constants or attributes of tuples. The query:

SELECT empno, ename, sal * 12 FROM emp;

would display all values the same as in the emp relation, except that the value of the attribute sal is multiplied by 12.

The WHERE clause in the SELECT statement specifies the criteria for selection of rows to be returned.

Conditions based on a range (BETWEEN operator): The BETWEEN operator defines a range of values that the column values must fall in to make the condition true. The range includes both the lower value and the upper value. e.g., find the empno of those employees whose salary is between 90,000 and 100,000 (that is, 90,000 and 100,000 included):
SELECT empno FROM emp WHERE sal BETWEEN 90000 AND 100000;
Conditions based on a list (IN operator): To specify a list of values, the IN operator is used. The IN operator selects values that match any value in a given list of values. For example, to display a list of members from ‘DELHI’, ‘MUMBAI’, ‘CHENNAI’ or ‘BANGALORE’ cities:
SELECT * FROM members WHERE city IN ('DELHI', 'MUMBAI', 'CHENNAI', 'BANGALORE');

The NOT IN operator finds rows that do not match the list. So if you write

SELECT * FROM members WHERE city NOT IN ('DELHI', 'MUMBAI', 'CHENNAI', 'BANGALORE');

it will list members not from the cities mentioned in the list.

Conditions based on pattern: SQL also includes a string-matching operator, LIKE, for comparison on character strings using patterns. Patterns are described using two special wildcard characters:
  1. Percent (%): ‘%’ matches any substring (one, more than one or no character).
  2. Underscore (_): ‘_’ matches exactly one character.

Patterns are case-sensitive. The LIKE keyword is used to select rows containing columns that match a wildcard pattern. The keyword NOT LIKE is used to select the rows that do not match the specified patterns of characters.

Searching for NULL: The NULL value in a column is searched for in a table using IS NULL in the WHERE clause (relational operators like =, <> etc. cannot be used with NULL). For example, to list details of all employees whose department is NULL (i.e., no value), you use the command:
SELECT empno, ename FROM emp WHERE Deptno IS NULL;
ORDER BY clause: Whenever a select query is executed the resulting rows are displayed in the order in which they exist in the table. You can sort the result of a query in a specific order using the ORDER BY clause. The ORDER BY clause allows sorting of the query result by one or more columns. The sorting can be done either in ascending or descending order.Note: If the order is not specified then by default the sorting will be performed in ascending order.GROUP BY clause: The GROUP BY clause groups the rows in the result by columns that have the same values. Grouping is done on column name. It can also be performed using aggregate functions, in which case the aggregate function produces a single value for each group.Aggregate functions: These functions operate on the multiset of values of a column of a relation, and return a value.
  1. avg: average value
  2. min: minimum value
  3. max: maximum value
  4. sum: sum of values
  5. count: number of values

These functions are called aggregate functions because they operate on aggregates of tuples. The result of an aggregate function is a single value.

HAVING clause: The HAVING clause places conditions on groups in contrast to the WHERE clause that places conditions on individual rows. While a WHERE condition cannot include aggregate functions, HAVING conditions can do so. e.g.,
select avg(sal), sum(sal) from emp group by deptno having deptno=10;
select job, count(*) from emp group by job having count(*)<3;
7. The DROP command: The DROP TABLE command is used to drop (delete) a table from the database. But there is a condition for dropping a table: it must be an empty table, i.e. a table with rows in it cannot be dropped. The syntax of this command is:
DROP TABLE <Table_name>;

e.g.,

DROP TABLE EMP;
8. Query based on two tables (Join):
SELECT <Column-list>
FROM <table_name1>, <table_name2>
WHERE <Join_condition> [AND condition];
9. Qualified names:
<tablename>.<fieldname>

This type of field name is called a qualified field name and is used for identifying a field if the two joining tables have fields with the same name.

6 – Marks Questions

Consider the following tables GAMES and PLAYER. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).

Table: GAMES

GCodeGameNameNumberPrizeMoneyScheduleDate
101Carom Board2500023-Jan-2004
102Badminton21200012-Dec-2003
103Table Tennis4800014-Feb-2004
105Chess2900001-Jan-2004
108Lawn Tennis42500019-Mar-2004

Table: PLAYER

PCodeNameGcode
1Nabi Ahmad101
2Ravi Sahai108
3Jatin101
4Nazneen103
  1. To display the name of all Games with their Gcodes.
  2. To display details of those games which are having PrizeMoney more than 7000.
  3. To display the content of the GAMES table in ascending order of ScheduleDate.
  4. To display the sum of PrizeMoney for each of the Number of participation groupings (as shown in column Number 2 or 4).
  5. SELECT COUNT(DISTINCT Number) FROM GAMES;
  6. SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM GAMES;
  7. SELECT SUM(PrizeMoney) FROM GAMES;
  8. SELECT DISTINCT Gcode FROM PLAYER;

Answer:

  1. SELECT GameName, Gcode FROM GAMES;
  2. SELECT * FROM GAMES WHERE PrizeMoney>7000;
  3. SELECT * FROM GAMES ORDER BY ScheduleDate;
  4. SELECT SUM(PrizeMoney), Number FROM GAMES GROUP BY Number;
  5. 2
  6. MAX(ScheduleDate)MIN(ScheduleDate)
    19-Mar-200412-Dec-2003
  7. 59000
  8. Gcode
    101
    103
    108

Consider the following tables FACULTY and COURSES. Write SQL commands for the statements (i) to (v) and give outputs for SQL queries (vi) to (vii).

FACULTY

F_IDFnameLnameHire_dateSalary
102AmitMishra12-10-199812000
103NitinVyas24-12-19948000
104RakshitSoni18-5-200114000
105RashmiMalhotra11-9-200411000
106SulekhaSrivastava5-6-200610000

COURSES

C_IDF_IDCnameFees
C21102Grid Computing40000
C22106System Design16000
C23104Computer Security8000
C24106Human Biology15000
C25102Computer Network20000
C26105Visual Basic6000
  1. To display details of those Faculties whose salary is greater than 12000.
  2. To display the details of courses whose fees is in the range of 15000 to 50000 (both values included).
  3. To increase the fees of the “System Design” course by 500.
  4. To display details of those courses which are taught by ‘Sulekha’ in descending order of courses.
  5. SELECT COUNT(DISTINCT F_ID) FROM COURSES;
  6. SELECT Fname, Cname FROM FACULTY, COURSES WHERE COURSES.F_ID = FACULTY.F_ID;

Answer:

  1. SELECT * FROM faculty WHERE salary > 12000;
  2. SELECT * FROM courses WHERE fees BETWEEN 15000 AND 50000;
  3. UPDATE courses SET fees = fees + 500 WHERE Cname = 'System Design';
  4. SELECT * FROM faculty fac, courses cour WHERE fac.f_id = cour.f_id AND fac.fname = 'Sulekha' ORDER BY cname DESC;
  5. 4
  6. FnameCname
    AmitGrid Computing
    RakshitComputer Security
    RashmiVisual Basic
    SulekhaHuman Biology

2 – Marks Questions

Define the following terms: (2 marks)

  1. DDL
  2. DML
  3. Primary Key
  4. Candidate Key
  5. Alternate Key
  6. Foreign Key
  7. Cardinality of relation
  8. Degree of relation
  9. Relation
  10. Attribute
  11. Tuple

Answer:

  1. DDL (Data Definition Language) contains the commands used to create and change the structure of database objects such as tables, views and indexes, e.g. CREATE TABLE, ALTER TABLE.
  2. DML (Data Manipulation Language) contains the commands used to manipulate the data in the database and to retrieve information from it, e.g. SELECT, INSERT, DELETE, UPDATE.
  3. Primary Key is a key that can uniquely identify the records (tuples) in a relation. It can never be duplicated and never NULL.
  4. Candidate Key: the attributes of a table which can serve as a primary key are called candidate keys.
  5. Alternate Key: all the candidate keys other than the primary key of a relation are its alternate keys.
  6. Foreign Key is an attribute of a table which is the primary key of some other table. It is used to enforce referential integrity.
  7. Cardinality of a relation is the number of tuples (rows) in the relation.
  8. Degree of a relation is the number of attributes (columns) in the relation.
  9. Relation is a collection of rows and columns on which various operations can be performed.
  10. Attribute is a column of a relation. It is also called a field or data item.
  11. Tuple is a row of a relation.

 

 
 

Free CBSE Study Guides: Class 12 Computer Science DBMS and Structured Query Language

Essential Notes for Class 12 Computer Science

Explore essential learning tools for Class 12 Computer Science DBMS and Structured Query Language. This curated collection features in-depth notes and targeted practice questions built around the active 2026 curriculum to streamline your daily revision.

Verified Solutions for Class 12 Computer Science

Each resource draws directly from authorized textbooks to maintain academic accuracy. Evaluating solved examples allows Class 12 students to master the formal presentation and answer-writing standards expected in upcoming school exams.

Enhance Accuracy with Online Tests

Maximize your grade potential by utilizing Class 12 Computer Science practice papers in tandem with these structured notes. All printable assignments and interactive mock tests on our platform are updated for the 2026 session and available free of charge.

FAQs

What is included in the advanced study material for Class 12 Computer Science DBMS and Structured Query Language?

Our advanced study package for DBMS and Structured Query Language includes detailed concepts, diagrams, Mind Maps, and explanation of complex topics to ensure Class 12 students learn as per syllabus for 2026 exams.

How do Mind Maps for Computer Science DBMS and Structured Query Language help in revision?

The Mind Maps provided for DBMS and Structured Query Language act as visual anchors which will help faster recall during high-pressure exams.

Are these Computer Science resources suitable for both classroom teaching and self-study?

Yes, teachers use our Class 12 Computer Science resources for lesson planning as they are in simple language and have lot of solved examples.

Is this advanced study material for DBMS and Structured Query Language free to download in PDF?

Yes, You can download the complete, mobile-friendly PDF of the Computer Science DBMS and Structured Query Language advanced resources for free.

Does this material cover rationalized content for the 2026-27 CBSE session?

Yes, our subject matter experts have updated the DBMS and Structured Query Language material to align with the rationalized NCERT textbooks and have removed deleted topics and added new competency-based questions.