DBMS Study Hub

How to use this hub

The running example: College database

These four tables appear in every unit, in class notes, quizzes and the lab, so the examples build on each other.

Theory plan, class by class

Tap a class to open its notes. Classes without a link are coming soon.

Lab plan: 20 sessions (MySQL)

Related experiments are grouped into 20 lab sessions. Students can practise every session at home in the SQL Lab, which has a browser SQL editor and 38 checked exercises.

The SQL Lab has a real database engine running inside this page, already loaded with the College database. Type SQL, press Run, and see the result immediately. You can also create your own databases. You cannot break anything: Reset all brings back the original data. Use it to prepare before each lab session and to revise for the practical exam.

1How to use the editor

2The College database

Five related tables. Arrows go from a foreign key to the primary key it refers to. This is the same design you studied in Units II and III, with a few extra columns.

3Your first five queries

Press Try it to load each query into the editor.

1. See what tables exist.

   SHOW TABLES;

   Try it
2. See a table's columns and types.

   DESCRIBE Student;

   Try it
3. See all rows.

   SELECT * FROM Department;

   Try it
4. Choose columns and rows.

   SELECT name, semester
   FROM Student
   WHERE dept_id = 'CSE';

   Try it
5. Sort the result.

   SELECT name, semester
   FROM Student
   ORDER BY semester DESC, name;

   Try it

4Creating your own database

When the Editor opens, the college database is already selected. In Lab 1 you will create your own, exactly as in MySQL:

1. Create a library database with one table, then look inside it.

   CREATE DATABASE library;
   USE library;
   
   CREATE TABLE Book (
     book_id INT PRIMARY KEY,
     title   VARCHAR(100) NOT NULL,
     author  VARCHAR(60),
     price   DECIMAL(8,2) CHECK (price > 0)
   );
   
   INSERT INTO Book VALUES
     (1, 'Database System Concepts', 'Silberschatz', 850.00),
     (2, 'Fundamentals of Database Systems', 'Elmasri', 720.50);
   
   SHOW TABLES;
   SELECT * FROM Book;

   Try it
2. Switch back to the college database.

   SHOW DATABASES;
   USE college;
   SELECT DATABASE();

   Try it

5How a SELECT is built

You must write the clauses in this order. Only SELECT and FROM are required; leave out what you do not need.

The database runs them in a different order. This explains two common errors.

• WHERE runs before grouping, so it cannot use COUNT, SUM or AVG. Use HAVING for conditions on groups.
• SELECT runs after WHERE, so a column alias made in SELECT cannot be used in WHERE. It can be used in ORDER BY.

6Beginner rules

7Reading error messages

8Browser editor vs MySQL in the lab

The editor runs SQLite, a small database engine, with a layer that accepts MySQL-style commands. Almost everything in this course works the same. The differences:

The editor shows a note whenever it translates or skips something, so you always know.

9Writing your lab record

10Keyboard shortcuts

Each task runs on a fresh copy of the College database, so earlier tasks never affect later ones. Write your SQL, press Check answer, and read the feedback. Use Hint before Show solution. Your progress is saved in this browser.

1.1The file-processing system

Before databases, every office in the college kept its own files and wrote its own program to handle them. The Accounts office had a fees file, the Exam cell had a marks file, and the Library had a members file. Each file stored the student's name and address again.

1.2Problems with the file-processing system

Learn these seven. They are the most common 5-mark question in Unit I.

1.3What a DBMS does

1.4Client–server architecture

A client is the program the user works with (a browser, a mobile app, MySQL Workbench). A server is the machine and program that stores the database and answers requests. The number of layers between them is called the number of tiers.

1-tier means the user, application and database are all on one machine, for example MySQL installed and used on your own laptop.

2.1Data vs information

2.2Database, DBMS and database system

2.3Database users

Different people use the same database in different ways. Classify them by how they interact with it.

Elmasri–Navathe divide end users a little differently: casual, naive (parametric), sophisticated and standalone users. Either classification earns marks if you explain each type.

Duties of the DBA

1. Schema definition — creates the table design using DDL.
2. Storage structure and access methods — decides files and indexes.
3. Schema and physical changes — adapts the design as needs change.
4. Granting authorization — decides who can read or change what (GRANT, REVOKE).
5. Routine maintenance — backups, free disk space, monitoring, performance tuning.
6. Recovery — restores data after a crash.

2.4Data models

Some textbooks (Navathe) group models by level instead: high-level/conceptual (ER), representational/implementation (relational, network, hierarchical) and low-level/physical (how data sits on disk). Both groupings are standard: learn the four Korth categories and mention the Navathe levels for full marks.

-- The same student in the relational model (a table row)
roll_no | name   | dept_id | semester
101     | Ananya | CSE     | 3

-- …and in the semi-structured model (JSON)
{ "roll_no": 101, "name": "Ananya", "dept": "CSE",
  "phones": ["98xxxxxx01", "98xxxxxx02"] }

3.1Database languages

A DBMS gives us languages to talk to it. Standard textbooks name two main parts, the Data Definition Language (DDL) and the Data Manipulation Language (DML). In SQL (Structured Query Language) these sit beside commands for access control and transactions, giving four groups.

DDL — Data Definition Language

Creates and changes the structure (schema). In MySQL, DDL commands commit automatically and cannot be rolled back.

CREATE DATABASE college;
USE college;

CREATE TABLE Department (
  dept_id   VARCHAR(5)  PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL,
  hod       VARCHAR(50)
);

CREATE TABLE Student (
  roll_no  INT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL,
  dept_id  VARCHAR(5),
  semester TINYINT,
  FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

ALTER TABLE Student ADD email VARCHAR(80);   -- add a column
TRUNCATE TABLE Student;   -- delete all rows, keep the table
DROP TABLE Student;       -- delete rows AND the table

DML — Data Manipulation Language

DML lets users retrieve and change data. Its retrieval part is called the query language (SELECT). Textbooks also classify DMLs as procedural (the user says what data is needed and how to get it) or declarative / non-procedural (the user says only what is needed). SQL's DML is declarative.

INSERT INTO Department VALUES ('CSE', 'Computer Science', 'Dr. Sen');
INSERT INTO Student (roll_no, name, dept_id, semester)
  VALUES (101, 'Ananya', 'CSE', 3), (102, 'Rahul', 'CSE', 3);

UPDATE Student SET semester = 4 WHERE roll_no = 101;
DELETE FROM Student WHERE roll_no = 102;

DML query — SELECT

SELECT name, semester
FROM   Student
WHERE  dept_id = 'CSE';

DCL — Data Control Language

GRANT SELECT ON college.Student TO 'clerk'@'localhost';
REVOKE SELECT ON college.Student FROM 'clerk'@'localhost';

TCL — Transaction Control Language

START TRANSACTION;
UPDATE Student SET semester = 5 WHERE roll_no = 101;
SAVEPOINT s1;
DELETE FROM Student WHERE roll_no = 101;
ROLLBACK TO s1;   -- the DELETE is undone, the UPDATE stays
COMMIT;           -- the UPDATE is now permanent

3.2Schema vs instance

A database has schemas at three levels — physical, logical and view (subschema). These lead straight into Day 4.

4.1Why three levels?

A student, the accounts clerk and the DBA all use the same college database, but each needs to see something different. The student sees only their own marks. The clerk sees fees. The DBA cares about disk files and indexes. The three-schema architecture separates what users see from what the data is from how it is stored.

4.2The three levels

In MySQL, an external schema is usually written as a view:

-- External level: the accounts office never sees grades
CREATE VIEW accounts_view AS
SELECT roll_no, name, dept_id
FROM   Student;

4.3Mappings

A mapping is the rule the DBMS uses to convert a request and its result from one level to another.

• External/conceptual mapping — converts a query on a view into a query on the full tables.
• Conceptual/internal mapping — converts a query on tables into reads of files and indexes on disk.

4.4Data independence

This class pulls Unit I together. Use the one-page summary to revise, practise the three diagrams, then take the 10-question test. The written questions below match the pattern of university papers.

5.1Unit I on one page

5.2Diagrams you must be able to draw

1. File system vs database approach (Figure 1.1)
2. 2-tier and 3-tier architecture (Figure 1.2)
3. SQL language groups tree (Figure 3.1)
4. Three-schema architecture with mappings (Figure 4.1) — most important

Practise each one from memory in under three minutes, then compare with the figure.

5.3Unit I test

5.4Written questions with answer outlines

Everything to revise for Unit I in one place: the exam quick points from every class, the diagrams to practise drawing, and one quiz that mixes all the day quizzes. Tap Open notes whenever a point needs more explanation.

Exam quick points, class by class

Diagrams to practise

Draw each one from memory in under three minutes, then open the class to compare.

1. Figure 1.1 — The same three offices, before and after a database. Day 1
2. Figure 1.2 — 2-tier and 3-tier client–server architecture. Day 1
3. Figure 2.1 — A query turns data into information. Day 2
4. Figure 3.1 — The four groups of SQL commands. SELECT belongs to DML, as in standard textbooks. Day 3
5. Figure 4.1 — Three-schema architecture for the College database. Draw this in every answer on the topic. Day 4

Mixed quiz

In Unit I we learnt what a DBMS is. Before we create any table, we must design the database: what things we store, what we know about them, and how they are connected. The Entity–Relationship (ER) model, proposed by Peter Chen in 1976, is the drawing language for this design. In Unit III the ER diagram becomes tables, and in the lab those tables become MySQL code.

Unit map

ER symbol cheat sheet (Chen notation)

These are the symbols used in most Indian university papers (Korth/Navathe style). Draw them neatly with a scale; marks are lost for wrong shapes.

6.1Entity and entity set

College entity sets we will use: Student, Department, Course, Faculty, Section.

6.2Attributes and domain

6.3Types of attributes

These pairs are independent: address is composite and single-valued; phone is simple and multivalued.

6.4NULL values

An attribute takes the special value NULL when a value does not exist for that entity. NULL means one of two things:

• Not applicable — a day scholar has no hostel_room.
• Unknown — the value exists but is not known yet (a missing email), or we do not know whether it exists.

NULL is not zero and not an empty string. Key attributes can never be NULL.

7.1Why we need keys

Two students can both be called "Rahul Das". The college still has to tell them apart, find the right marks and send the right result. A key is an attribute, or a set of attributes, whose values are different for every entity.

We use this table all class. Assume roll_no is unique, email is unique, and two students may share a name or a department.

7.2Types of keys

7.3Foreign key and referential integrity

Referential integrity: every foreign key value must match an existing primary key value in the parent table, or be NULL. MySQL rejects the student with MEC because no such department exists.

CREATE TABLE Student (
  roll_no  INT         PRIMARY KEY,          -- primary key
  email    VARCHAR(80) UNIQUE NOT NULL,      -- alternate key
  name     VARCHAR(50) NOT NULL,
  dept_id  VARCHAR(5),
  FOREIGN KEY (dept_id) REFERENCES Department(dept_id)  -- foreign key
);

CREATE TABLE Enrollment (
  roll_no   INT,
  course_id VARCHAR(8),
  grade     CHAR(2),
  PRIMARY KEY (roll_no, course_id)                     -- composite key
);

8.1Relationship and relationship set

A relationship can have its own descriptive attributes. grade belongs to neither Student nor Course alone; it belongs to the fact that a student enrolled in a course, so it is an attribute of enrolls.

When an entity set takes part twice in the same relationship, we write role names on the lines. In Student — mentors — Student, one side is the mentor and the other is the mentee.

8.2Degree of a relationship

8.3Constraint 1: mapping cardinality

8.4Constraint 2: participation

Some books also write (min, max) pairs on the lines: (1, N) means at least one, at most many; a minimum of 1 means total participation, 0 means partial.

9.1Drawing an ER diagram: six steps

Problem statement: The college has departments. Each student belongs to exactly one department. Each department offers courses, and every course is offered by one department. Students enroll in many courses and get a grade in each. A course runs in several sections (A, B, C…), and section letters repeat across courses.

1. Find entity sets — nouns that have their own data: Department, Student, Course, Section.
2. Find attributes and underline the key of each entity set.
3. Find relationships — verbs: belongs_to, offers, enrolls, has_section.
4. Mark cardinality on every line using the two-question test.
5. Mark participation — "exactly one", "every" → double line.
6. Check for weak entities — anything without its own key (Section).

9.2Strong and weak entity sets

Section "A" exists in DBMS and also in Data Structures. The letter alone cannot identify a section; we need to know which course it belongs to.

In MySQL the existence dependency is enforced with a composite primary key and cascade delete. When a course is dropped, its sections disappear too:

CREATE TABLE Section (
  course_id VARCHAR(8),
  sec_id    CHAR(1),
  room      VARCHAR(10),
  PRIMARY KEY (course_id, sec_id),
  FOREIGN KEY (course_id) REFERENCES Course(course_id)
    ON DELETE CASCADE
);

The basic ER model cannot easily show that a Student and a Faculty member are both kinds of Person, or that a relationship itself takes part in another relationship. The Extended ER (EER) model adds three features for this.

10.1Specialization and generalization

Both produce the same diagram. The difference is the direction in which the designer thinks.

Attribute inheritance

A subclass inherits all attributes and relationships of its superclass. A Faculty entity has person_id, name, phone (inherited) plus salary and designation (its own).

Constraints on specialization

10.2Aggregation

Students work on projects (works_on). A faculty member guides each student–project pair, not the student alone or the project alone. ER does not allow a line from a relationship to another relationship, so we treat Student works_on Project as a single higher-level entity.

10.3Unit II test

10.4Unit II question bank

Everything to revise for Unit II in one place: the exam quick points from every class, the diagrams to practise drawing, and one quiz that mixes all the day quizzes. Tap Open notes whenever a point needs more explanation.

Exam quick points, class by class

Diagrams to practise

Draw each one from memory in under three minutes, then open the class to compare.

1. Figure 6.1 — The Student entity set with every type of attribute. Day 6
2. Figure 7.1 — How the key types fit inside each other. Day 7
3. Figure 8.1 — The four mapping cardinalities. Each dot is one entity; each line is one relationship. Day 8
4. Figure 8.2 — Partial and total participation in the same relationship. Day 8
5. Figure 9.1 — The College ER diagram. Double lines show total participation; Section is a weak entity set. Day 9
6. Figure 10.1 — Person is the superclass; Student and Faculty are subclasses that inherit person_id, name and phone. Day 10
7. Figure 10.2 — Aggregation: Faculty guides a (Student, Project) pair. Day 10

Mixed quiz

In Unit II we drew the College database. In this unit the drawing becomes relations (tables), and we learn two formal languages for asking questions of them: relational algebra (step-by-step operations) and relational calculus (describe what you want). SQL, which you use in the lab, is built on both.

Unit map

The sample database

Every query in this unit runs on these four relations. Keep this tab open while you solve problems. Note that PHY has no students and that student 101 has taken both CSE courses; both facts matter later.

Operator cheat sheet

11.1Relational model terms

The relational model, proposed by E. F. Codd in 1970, stores all data as relations. Informally a relation is a table, but the formal words are what the examiner looks for.

11.2Properties of a relation

• No duplicate tuples — a relation is a set.
• Order of tuples does not matter — sorting Student by name gives the same relation.
• Order of attributes does not matter — attributes are identified by name.
• Values are atomic — one value per cell; no lists (that is First Normal Form, Unit V).
• Each attribute has a distinct name within the relation.

11.3Integrity constraints

11.4ER diagram to relations

Apply these rules to the College ER diagram from Day 9. In the schemas below, underlined attributes form the primary key.

Department(dept_id, dept_name, hod)
Student   (roll_no, name, semester, dept_id → Department)
Course    (course_id, title, credits, dept_id → Department)
Enrollment(roll_no → Student, course_id → Course, grade)
Section   (course_id → Course, sec_id, room)

12.1What is relational algebra?

Think of it like arithmetic: (3 + 4) × 2 applies operations to numbers; π_name(σ_semester=3(Student)) applies operations to relations.

12.2Select (σ)

Returns the tuples that satisfy a condition. Conditions use =, ≠, <, ≤, >, ≥ joined by ∧ (and), ∨ (or), ¬ (not).

12.3Project (π)

Returns only the listed attributes. Because the result is a relation (a set), duplicate tuples are removed.

12.4Rename (ρ)

Gives a name to a relation (and optionally its attributes). Needed when the same relation is used twice in one query.

12.5Union (∪) and set difference (−)

Let A ← π_{roll_no}(σ_{course_id='CS201'}(Enrollment)) = {101, 102, 103} and B ← π_{roll_no}(σ_{course_id='MA201'}(Enrollment)) = {101, 105}.

Union is commutative (A ∪ B = B ∪ A); difference is not (B − A = {105}).

12.6Cartesian product (×)

Pairs every tuple of R with every tuple of S. If R has n₁ tuples and degree d₁, and S has n₂ tuples and degree d₂, then R × S has n₁ × n₂ tuples and d₁ + d₂ attributes.

Let P = π_{dept_id}(σ_{dept_id='CSE' ∨ dept_id='ECE'}(Department)) = {CSE, ECE} and Q = π_{course_id}(σ_credits=3(Course)) = {EC201, MA201}.

On its own × produces meaningless pairs (CSE with EC201). It becomes useful when followed by σ to keep only matching pairs; that is a join, the topic of Day 13.

12.7Practice: write the expression

13.1Intersection (∩)

R ∩ S returns tuples present in both relations (union-compatible). It is not fundamental, because:

With A and B from Day 12: A ∩ B = {101}, the only student who took both DBMS and Discrete Maths.

13.2Joins

13.3Division (÷)

Use division when the question contains "all" or "every": "students who took all CSE courses".

13.4Assignment (←)

Long queries are easier to write in steps. The assignment operator stores an intermediate result under a temporary name.

Result: Ananya.

13.5Practice

14.1Algebra vs calculus

14.2Symbols you need

14.3Tuple relational calculus (TRC)

1. All semester-3 students.

   { t | t ∈ Student ∧ t[semester] = 3 }

   Result: tuples 101, 102, 103.
2. Only their names (need a new tuple with one attribute):

   { t | ∃ s ∈ Student ( s[semester] = 3 ∧ t[name] = s[name] ) }

   Result: Ananya, Rahul, Priya.
3. Names of students enrolled in CS201 (∃ plays the role of a join):

   { t | ∃ s ∈ Student ( t[name] = s[name] ∧ ∃ e ∈ Enrollment ( e[roll_no] = s[roll_no] ∧ e[course_id] = 'CS201' ) ) }

   Result: Ananya, Rahul, Priya.
4. Students who took all CSE courses (∀ plays the role of division):

   { t | ∃ s ∈ Student ( t[roll_no] = s[roll_no] ∧ ∀ c ∈ Course ( c[dept_id] = 'CSE' ⇒ ∃ e ∈ Enrollment ( e[roll_no] = s[roll_no] ∧ e[course_id] = c[course_id] ) ) ) }

   Result: 101.

A variable bound by ∃ or ∀ is a bound variable; t, which appears to the left of the bar, is the free variable.

14.4Domain relational calculus (DRC)

1. Roll number and name of semester-3 students.

   { <r, n> | ∃ d, m ( <r, n, d, m> ∈ Student ∧ m = 3 ) }

2. Names of students enrolled in CS201.

   { <n> | ∃ r, d, m ( <r, n, d, m> ∈ Student ∧ ∃ g ( <r, 'CS201', g> ∈ Enrollment ) ) }

   The shared variable r does the join.

14.5Safety of expressions

The expression { t | ¬ ( t ∈ Student ) } asks for every tuple in the universe that is not a student — an infinite answer. Such an expression is unsafe.

14.6Expressive power

Codd showed that relational algebra, safe TRC and safe DRC are equally powerful: any query in one can be written in the others. A language that can express everything relational algebra can is called relationally complete. SQL is relationally complete (and adds more, such as aggregation).

The basic operations cannot answer "how many students are in each department?" or "list every department, even those with no students". The extended operations add these abilities. They are the direct ancestors of SQL's GROUP BY and LEFT JOIN.

15.1Generalized projection

Allows arithmetic expressions in the projection list.

15.2Aggregate functions and grouping (G)

PHY does not appear: grouping only forms groups for values present in Student.

15.3Outer joins

Student ⟖ Department gives the same tuples (the preserved side is now on the right). In this sample every student has a department, so the full outer join equals the left outer join; it would differ if some student had dept_id = NULL.

15.4Unit III test

15.5Unit III question bank

Everything to revise for Unit III in one place: the exam quick points from every class, the diagrams to practise drawing, and one quiz that mixes all the day quizzes. Tap Open notes whenever a point needs more explanation.

Exam quick points, class by class

Diagrams to practise

Draw each one from memory in under three minutes, then open the class to compare.

1. Figure 11.1 — The parts of a relation. The highlighted row is one tuple. Day 11
2. Figure 12.1 — Select cuts across rows; project cuts down columns. Day 12
3. Figure 13.1 — A join is a product followed by a selection. Day 13
4. Figure 15.1 — Inner join keeps only matched tuples; an outer join also keeps the unmatched ones. Day 15

Mixed quiz

Units I–III gave us the ideas: schemas, keys, relational algebra. This unit is about SQL, the language every relational DBMS speaks. Every example here runs on the full College database used in the SQL Lab (Department, Faculty, Student, Course, Enrollment), and most have a Try in SQL Lab button so you can run them yourself.

Unit map

From relational algebra to SQL

SQL is declarative: you describe the result and the DBMS plans the steps. Each relational-algebra operator from Unit III has a direct SQL form.

16.1Common MySQL data types

CHAR vs VARCHAR: CHAR(10) always uses 10 characters of space; VARCHAR(10) uses only what the value needs. Use CHAR for fixed codes (grade, PIN), VARCHAR for names and emails.

16.2CREATE TABLE with every constraint

Constraints are rules the DBMS enforces on every insert and update, so bad data never gets in. A constraint written next to a column is a column-level constraint; one written after all columns is a table-level constraint (needed when it involves two or more columns). CONSTRAINT name gives it a name, which makes error messages and later changes easier.

-- Student clubs: each club may have a faculty in charge
CREATE TABLE Club (
  club_id INT PRIMARY KEY,                          -- entity integrity
  name    VARCHAR(40) NOT NULL UNIQUE,              -- required, no duplicates
  fee     DECIMAL(6,2) DEFAULT 0 CHECK (fee >= 0),  -- default + domain rule
  fac_id  INT,
  CONSTRAINT fk_club_fac FOREIGN KEY (fac_id)
    REFERENCES Faculty(fac_id) ON DELETE SET NULL    -- referential integrity
);

CREATE TABLE Membership (
  roll_no INT,
  club_id INT,
  joined  DATE NOT NULL,
  PRIMARY KEY (roll_no, club_id),                   -- table-level, composite
  FOREIGN KEY (roll_no) REFERENCES Student(roll_no) ON DELETE CASCADE,
  FOREIGN KEY (club_id) REFERENCES Club(club_id)    ON DELETE CASCADE
);

INSERT INTO Club VALUES (1, 'Coding Club', 200, 5), (2, 'Robotics', 300, 6);
INSERT INTO Club (club_id, name) VALUES (3, 'Quiz Club');   -- fee uses DEFAULT
INSERT INTO Membership VALUES (101, 1, '2026-08-01'), (106, 1, '2026-08-03'),
                              (103, 2, '2026-08-02'), (104, 2, '2026-08-05');
SELECT * FROM Club;

16.3Referential actions

What should happen to child rows when a parent row is deleted or its key changes? You choose with ON DELETE and ON UPDATE.

The College tables use the default, so deleting a department that still has students is refused:

DELETE FROM Department WHERE dept_id = 'PHY';

Membership uses CASCADE, so deleting a club removes its memberships automatically:

CREATE TABLE Club (club_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE Membership (
  roll_no INT REFERENCES Student(roll_no),
  club_id INT REFERENCES Club(club_id) ON DELETE CASCADE,
  PRIMARY KEY (roll_no, club_id));
INSERT INTO Club VALUES (1, 'Coding Club'), (2, 'Robotics');
INSERT INTO Membership VALUES (101, 1), (106, 1), (103, 2), (104, 2);

DELETE FROM Club WHERE club_id = 2;     -- Robotics closes
SELECT * FROM Membership;               -- its two members are gone too

16.4Changing a table: ALTER TABLE

ALTER TABLE Student ADD phone VARCHAR(15);                 -- add a column
ALTER TABLE Student MODIFY phone VARCHAR(20);              -- change its type
ALTER TABLE Student RENAME COLUMN phone TO mobile;         -- rename it
ALTER TABLE Student ADD CONSTRAINT uq_mobile UNIQUE (mobile);
ALTER TABLE Student DROP COLUMN mobile;                    -- remove it

In the SQL Lab, ADD, RENAME COLUMN and DROP COLUMN work; MODIFY and adding a constraint to an existing table work only in MySQL.

16.5INSERT, UPDATE and DELETE in depth

-- 1. Name the columns: safer, and the rest get NULL or DEFAULT
INSERT INTO Student (roll_no, name, dept_id, semester)
VALUES (113, 'Nikhil', 'ECE', 1);

-- 2. Insert the result of a query (Data Structures re-exam list)
CREATE TABLE ReExam (roll_no INT, course_id VARCHAR(8));
INSERT INTO ReExam
SELECT roll_no, course_id FROM Enrollment WHERE marks < 70;

-- 3. Update with an expression: 5 grace marks in DBMS, capped at 100
UPDATE Enrollment
SET marks = CASE WHEN marks + 5 > 100 THEN 100 ELSE marks + 5 END
WHERE course_id = 'CS201';

-- 4. Delete using a subquery
DELETE FROM ReExam
WHERE roll_no IN (SELECT roll_no FROM Student WHERE dept_id = 'ECE');

SELECT * FROM ReExam;

17.1Set operations

SQL set operations combine the results of two SELECTs, exactly like ∪, ∩ and − in relational algebra. Both SELECTs must return the same number of columns with compatible types.

SELECT roll_no FROM Enrollment WHERE course_id = 'CS201'
UNION
SELECT roll_no FROM Enrollment WHERE course_id = 'MA201';

SELECT roll_no FROM Enrollment WHERE course_id = 'CS201'
UNION ALL
SELECT roll_no FROM Enrollment WHERE course_id = 'MA201';

SELECT roll_no FROM Enrollment WHERE course_id = 'CS201'
INTERSECT
SELECT roll_no FROM Enrollment WHERE course_id = 'MA201';

SELECT roll_no FROM Enrollment WHERE course_id = 'CS201'
EXCEPT
SELECT roll_no FROM Enrollment WHERE course_id = 'MA201';

MySQL supports INTERSECT and EXCEPT from version 8.0.31. Oracle calls EXCEPT MINUS. On older MySQL, write INTERSECT with IN and EXCEPT with NOT IN (Day 18).

17.2Aggregate functions

An aggregate function takes a whole column of values and returns one value.

SELECT COUNT(*)                AS enrollments,
       COUNT(marks)            AS with_marks,
       COUNT(DISTINCT course_id) AS courses,
       SUM(marks)              AS total,
       ROUND(AVG(marks), 2)    AS average,
       MIN(marks)              AS lowest,
       MAX(marks)              AS highest
FROM Enrollment;

Notice COUNT(*) = 18 but COUNT(marks) = 17: Sneha's Data Structures marks are NULL, and every aggregate except COUNT(*) ignores NULL.

17.3GROUP BY and HAVING

GROUP BY splits the rows into groups with the same value and computes the aggregate for each group. HAVING then filters the groups.

SELECT course_id, COUNT(*) AS students, ROUND(AVG(marks), 1) AS avg_marks
FROM Enrollment
WHERE marks IS NOT NULL          -- 1. filter rows
GROUP BY course_id               -- 2. form groups
HAVING COUNT(*) >= 2             -- 3. filter groups
ORDER BY avg_marks DESC;         -- 4. sort the result

17.4NULL values and three-valued logic

NULL means "unknown" or "not applicable". It is not zero and not an empty string, so SQL treats it specially:

SELECT 5 + NULL      AS arithmetic,   -- unknown + 5 is unknown
       NULL = NULL    AS equal,        -- is unknown equal to unknown? unknown
       NULL IS NULL   AS is_null,      -- the right way to test
       COALESCE(NULL, 'none') AS replaced;

Any comparison involving NULL gives a third truth value, UNKNOWN. WHERE and HAVING keep a row only when the condition is TRUE, so UNKNOWN rows are dropped.

NOT UNKNOWN = UNKNOWN. That is why WHERE marks >= 50 OR marks < 50 still misses Sneha's row:

SELECT COUNT(*) FROM Enrollment WHERE marks >= 50 OR marks < 50;   -- 17, not 18

18.1What is a nested query?

A subquery is a SELECT written inside another statement. The DBMS uses its result as part of the outer query. Subqueries are classified by what they return:

18.2Scalar and IN subqueries

-- Faculty who earn more than the average
SELECT name, salary
FROM Faculty
WHERE salary > (SELECT AVG(salary) FROM Faculty);

-- Students enrolled in a 4-credit course
SELECT name
FROM Student
WHERE roll_no IN (
  SELECT roll_no FROM Enrollment
  WHERE course_id IN (SELECT course_id FROM Course
                      WHERE credits = 4));

ANY and ALL

> ANY (subquery) is true if the value is greater than at least one value in the list; > ALL if greater than every value. SOME means the same as ANY. They can always be rewritten with MIN and MAX:

-- Faculty paid more than everyone in ECE (ALL written with MAX)
SELECT name, dept_id, salary FROM Faculty
WHERE salary > (SELECT MAX(salary) FROM Faculty WHERE dept_id = 'ECE');

18.3Correlated subqueries and EXISTS

-- Students who scored above the average of THAT course
SELECT e.roll_no, e.course_id, e.marks
FROM Enrollment e
WHERE e.marks > (SELECT AVG(e2.marks) FROM Enrollment e2
                 WHERE e2.course_id = e.course_id)   -- refers to outer e
ORDER BY e.course_id;

EXISTS (subquery) is true when the subquery returns at least one row; NOT EXISTS when it returns none. It is almost always correlated.

-- Departments with at least one student
SELECT dept_name FROM Department d
WHERE EXISTS (SELECT * FROM Student s
              WHERE s.dept_id = d.dept_id);

-- Courses nobody has taken
SELECT title FROM Course c
WHERE NOT EXISTS (SELECT * FROM Enrollment e
                  WHERE e.course_id = c.course_id);

SELECT s.roll_no, s.name FROM Student s
WHERE NOT EXISTS (
  SELECT * FROM Course c
  WHERE c.dept_id = 'CSE'
    AND NOT EXISTS (SELECT * FROM Enrollment e
                    WHERE e.roll_no = s.roll_no
                      AND e.course_id = c.course_id));

18.4Subqueries in FROM (derived tables)

-- Students whose average marks are above 80
SELECT t.roll_no, t.avg_marks
FROM (SELECT roll_no, AVG(marks) AS avg_marks
      FROM Enrollment
      GROUP BY roll_no) AS t          -- a derived table needs an alias
WHERE t.avg_marks > 80;

18.5Views

CREATE VIEW result_sheet AS
SELECT s.roll_no, s.name, c.title, e.marks, e.grade
FROM Enrollment e
JOIN Student s ON s.roll_no = e.roll_no
JOIN Course  c ON c.course_id = e.course_id;

SELECT * FROM result_sheet WHERE grade = 'A' ORDER BY marks DESC;

Updatable views

You can INSERT, UPDATE or DELETE through a view only if each view row maps to exactly one base-table row. In MySQL a view is not updatable if it uses aggregates, GROUP BY, HAVING, DISTINCT, UNION, or certain joins and subqueries. result_sheet above is a join of three tables, so most changes through it are refused. A materialized view stores its result physically and is refreshed periodically; Oracle and PostgreSQL support it, MySQL does not.

19.1Assertions

CHECK constraints look at one row. Some rules involve many rows or many tables, such as "no student may take more than 6 courses". Standard SQL expresses these as assertions.

CREATE ASSERTION max_six_courses CHECK (
  NOT EXISTS (SELECT roll_no FROM Enrollment
              GROUP BY roll_no
              HAVING COUNT(*) > 6)
);

Assertions are part of the SQL standard and appear in exams, but they are expensive to check, so MySQL, Oracle, PostgreSQL and SQL Server do not implement them. In practice the same rule is enforced with a trigger (below).

19.2Stored procedures

DELIMITER //

-- List the students of one department
CREATE PROCEDURE dept_students(IN d VARCHAR(5))
BEGIN
  SELECT roll_no, name, semester FROM Student
  WHERE dept_id = d ORDER BY roll_no;
END //

-- Count students in a department and return the number
CREATE PROCEDURE count_students(IN d VARCHAR(5), OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM Student WHERE dept_id = d;
END //

DELIMITER ;

CALL dept_students('ECE');
CALL count_students('CSE', @n);
SELECT @n AS cse_students;      -- 5

Why DELIMITER? The procedure body contains semicolons. DELIMITER // tells the MySQL client to treat // as the end of the CREATE statement, so the inner semicolons are not taken as the end. Switch back with DELIMITER ;.

19.3Triggers

MySQL: fill the grade automatically and enforce the 6-course rule

DELIMITER //
CREATE TRIGGER set_grade BEFORE INSERT ON Enrollment
FOR EACH ROW
BEGIN
  SET NEW.grade = CASE WHEN NEW.marks >= 80 THEN 'A'
                       WHEN NEW.marks >= 70 THEN 'B'
                       ELSE 'C' END;
END //

CREATE TRIGGER max_six BEFORE INSERT ON Enrollment
FOR EACH ROW
BEGIN
  IF (SELECT COUNT(*) FROM Enrollment WHERE roll_no = NEW.roll_no) >= 6 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A student may take at most 6 courses';
  END IF;
END //
DELIMITER ;

Try a trigger in the SQL Lab: an audit log

The browser engine needs no DELIMITER and cannot change NEW, so this AFTER trigger records every change of marks in a log table instead.

CREATE TABLE marks_log (roll_no INT, course_id VARCHAR(8),
                        old_marks INT, new_marks INT, changed_on DATE);

CREATE TRIGGER log_marks AFTER UPDATE OF marks ON Enrollment
FOR EACH ROW
BEGIN
  INSERT INTO marks_log
  VALUES (OLD.roll_no, OLD.course_id, OLD.marks, NEW.marks, '2026-10-25');
END;

UPDATE Enrollment SET marks = 70 WHERE roll_no = 102 AND course_id = 'CS202';
UPDATE Enrollment SET marks = 60 WHERE roll_no = 103 AND course_id = 'CS201';
SELECT * FROM marks_log;

20.1Goals and threats

Two different questions protect these goals: authentication asks "who are you?" (user name and password), and authorization asks "what are you allowed to do?" (privileges).

20.2Privileges: GRANT and REVOKE

SQL uses discretionary access control: the owner of an object decides who else may use it, by granting privileges.

-- 1. Create accounts (authentication)
CREATE USER 'clerk'@'localhost'   IDENTIFIED BY 'Clerk@2026';
CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'Teach@2026';

-- 2. Grant privileges (authorization)
GRANT SELECT ON college.Student TO 'clerk'@'localhost';
GRANT SELECT, UPDATE (marks, grade) ON college.Enrollment TO 'teacher'@'localhost';
GRANT SELECT ON college.Course TO 'teacher'@'localhost' WITH GRANT OPTION;

SHOW GRANTS FOR 'teacher'@'localhost';

-- 3. Take a privilege back
REVOKE UPDATE ON college.Enrollment FROM 'teacher'@'localhost';

WITH GRANT OPTION lets the receiver pass the privilege on to others. In standard SQL, REVOKE … CASCADE also removes privileges that were passed on, while RESTRICT refuses if any were; MySQL does not cascade revokes.

20.3Roles

Granting the same ten privileges to 60 teachers one by one is error-prone. A role is a named set of privileges granted to users as a group (MySQL 8.0+).

CREATE ROLE 'exam_cell';
GRANT SELECT, UPDATE ON college.Enrollment TO 'exam_cell';
GRANT 'exam_cell' TO 'teacher'@'localhost';
SET DEFAULT ROLE 'exam_cell' TO 'teacher'@'localhost';

20.4Views for security

Give a user a view instead of the table, and they see only the rows and columns the view shows. The accounts clerk needs names and departments, not dates of birth or email addresses:

CREATE VIEW clerk_view AS
SELECT roll_no, name, dept_id FROM Student;

SELECT * FROM clerk_view;
-- In MySQL: GRANT SELECT ON college.clerk_view TO 'clerk'@'localhost';

20.5SQL injection

A program that glues user input straight into SQL text can be tricked into running a different query. Suppose a portal builds this query from a search box:

-- Program code (unsafe):  "SELECT * FROM Student WHERE name = '" + input + "'"
-- A normal user types:  Priya
SELECT roll_no, name, email FROM Student WHERE name = 'Priya';

An attacker types x' OR '1'='1. The program glues it in and the condition becomes always true, so every student's email leaks:

SELECT roll_no, name, email FROM Student WHERE name = 'x' OR '1'='1';

20.6Other security measures

• Mandatory access control (MAC): data and users get security levels (top secret, secret, …); used in military systems. SQL's GRANT system is discretionary (DAC).
• Encryption of stored data and network connections (SSL/TLS).
• Audit trail: a log of who accessed or changed what (triggers can build one, Day 19).
• Backups for availability (recovery is Unit VII).

20.7Unit IV test

20.8Unit IV question bank

Everything to revise for Unit IV in one place: the exam quick points from every class, the diagrams to practise drawing, and one quiz that mixes all the day quizzes. Tap Open notes whenever a point needs more explanation.

Exam quick points, class by class

Diagrams to practise

Draw each one from memory in under three minutes, then open the class to compare.

Mixed quiz