.1 What is an Aggregate function in SQL? Write SQL queries for aggregate functions.
1. Definition (points)
o An aggregate function computes a single result from a set of input values
(rows).
o Common aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX().
o Aggregates are often used with GROUP BY to compute summaries per group;
HAVING filters groups.
-- total number of students
SELECT COUNT(*) AS total_students FROM Student;
-- number of distinct branches
SELECT COUNT(DISTINCT Branch) AS num_branches FROM Student;
-- number of books by each publisher
SELECT Publisher, COUNT(*) AS books_by_publisher
FROM Book
GROUP BY Publisher;
-- average number of books issued per student (approx)
SELECT AVG(book_count) AS avg_books_per_student
FROM (
SELECT [Link], COUNT(*) AS book_count
FROM Issue
GROUP BY [Link]
) AS t;
-- minimum and maximum issue date (earliest and latest issues)
SELECT MIN(Date_of_Issue) AS earliest_issue, MAX(Date_of_Issue) AS latest_issue
FROM Issue;
Q.2 Difference between CROSS JOIN, NATURAL JOIN, LEFT OUTER JOIN and RIGHT OUTER
JOIN (with examples)
1. CROSS JOIN
o Assignment_2 — Detailed answers (Q1–Q10)
o Below are clear, point-by-point answers with examples and SQL + relational-
algebra for the schema shown in your image:
Student(RollNo, Name, Father_Name, Branch),
Book(ISBN, Title, Author, Publisher),
Issue(RollNo, ISBN, Date_of_Issue)
(If your actual column name uses a hyphen like Date-of-Issue, in SQL you must
quote it—e.g. "Date-of-Issue"—or rename it to Date_of_Issue which I use
below.)
o Q.1 What is an Aggregate function in SQL? Write SQL queries for aggregate
functions.
o Definition (points)
o An aggregate function computes a single result from a set of input values
(rows).
o Common aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX().
o Aggregates are often used with GROUP BY to compute summaries per group;
HAVING filters groups.
o Examples (SQL)
o -- total number of students
o SELECT COUNT(*) AS total_students FROM Student;
o -- number of distinct branches
o SELECT COUNT(DISTINCT Branch) AS num_branches FROM Student;
o -- number of books by each publisher
o SELECT Publisher, COUNT(*) AS books_by_publisher
o FROM Book
o GROUP BY Publisher;
o -- average number of books issued per student (approx)
o SELECT AVG(book_count) AS avg_books_per_student
o FROM (
o SELECT [Link], COUNT(*) AS book_count
o FROM Issue
o GROUP BY [Link]
o ) AS t;
o -- minimum and maximum issue date (earliest and latest issues)
o SELECT MIN(Date_of_Issue) AS earliest_issue, MAX(Date_of_Issue) AS
latest_issue
o FROM Issue;
o Notes
o Use HAVING to filter aggregated groups: ... GROUP BY Publisher HAVING
COUNT(*) > 10.
o Q.2 Difference between CROSS JOIN, NATURAL JOIN, LEFT OUTER JOIN and
RIGHT OUTER JOIN (with examples)
o CROSS JOIN
o Definition: Cartesian product of two tables — every row from left paired with
every row from right.
o Use: rarely useful directly; used to generate combinations.
o Example:
o SELECT * FROM A CROSS JOIN B;
o -- If A has 2 rows and B has 3 rows, result has 6 rows.
o NATURAL JOIN
o Definition: An equijoin automatically on all columns with the same names in
both tables. Duplicate join columns are eliminated in result.
o Danger: ambiguous if multiple same-named columns exist; better to use
explicit USING or ON.
o Example:
o -- Suppose Student and Issue both have RollNo column
o SELECT * FROM Student NATURAL JOIN Issue;
o -- Equivalent to an equijoin on all matching column names (here RollNo).
o LEFT OUTER JOIN (LEFT JOIN)
o Definition: All rows from left table retained; matching rows from right table if
exist; right columns NULL when no match.
o Use: find all left rows with optional matching right rows.
o Example:
o SELECT [Link], [Link], [Link]
o FROM Student s
o LEFT JOIN Issue i ON [Link] = [Link];
o -- Returns all students; ISBN is NULL if student hasn't issued any book.
o RIGHT OUTER JOIN (RIGHT JOIN)
o Definition: All rows from right table retained; matching left rows where exist;
left columns NULL when no match.
o Example:
o SELECT [Link], [Link]
o FROM Student s
o RIGHT JOIN Issue i ON [Link] = [Link];
o -- Returns all issue rows; student info NULL if issue points to unknown
student.
o Quick comparison bullets
o CROSS JOIN = Cartesian product (no condition).
o NATURAL JOIN = automatic equijoin on same-named columns; drops
duplicate columns.
o LEFT JOIN preserves left table rows; RIGHT JOIN preserves right table rows.
o Use INNER JOIN when only matched rows are wanted (not asked but useful).
o Q.3 Describe mapping constraints with its types
o Mapping constraints (cardinality constraints) define how many instances of
one entity relate to instances of another in a relationship.
o 1 : 1 (One-to-One)
o Each entity in A is associated with at most one entity in B and vice versa.
o Example: Person ↔ Passport (one person → one passport).
o 1 : N (One-to-Many)
o One entity in A may relate to many entities in B; any B relates to at most one
A.
o Example: Department (1) → Student (N): one dept has many students.
o N : 1 (Many-to-One)
o Reverse of 1:N (semantics same as above, orientation differs).
o M : N (Many-to-Many)
o Entities in A can relate to many in B and vice versa.
o Example: Student ↔ Course : a student may take many courses, a course has
many students. Implemented via bridge/junction table.
o Additional note: Participation constraint (total/partial) describes whether
every entity must participate in the relationship (total = every entity
participates; partial = optional).
o Q.4 What are different integrity constraints?
o Integrity constraints ensure correctness and consistency of data.
o Entity integrity
o Primary key values must be unique and NOT NULL.
o Example: RollNo as PRIMARY KEY in Student.
o CREATE TABLE Student(
o RollNo INT PRIMARY KEY,
o Name VARCHAR(100),
o Branch VARCHAR(50)
o );
o Referential integrity
o Foreign key must match a primary key in referenced table (or be NULL if
allowed).
o CREATE TABLE Issue(
o RollNo INT,
o ISBN VARCHAR(20),
o Date_of_Issue DATE,
o FOREIGN KEY (RollNo) REFERENCES Student(RollNo),
o FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
o );
o Domain constraints
o Column must be of declared type and (optionally) must satisfy value range or
format (e.g., CHECK).
o -- Branch must be one of specified values
o ALTER TABLE Student ADD CONSTRAINT chk_branch CHECK (Branch IN
('CSE','ECE','ME','CE'));
o Unique constraint
o Column values must be unique (other than primary key).
o ALTER TABLE Book ADD CONSTRAINT unique_title UNIQUE (Title);
o NOT NULL
o Column cannot have NULL values.
o ALTER TABLE Student MODIFY Name VARCHAR(100) NOT NULL;
o CHECK constraints
o Custom boolean expressions for validation.
o ALTER TABLE Issue ADD CONSTRAINT check_date CHECK (Date_of_Issue <=
CURRENT_DATE);
o Triggers & user-defined rules
o Use triggers for complex integrity logic (e.g., prevent deletion if outstanding
issues).
o Q.5 What is Relational Algebra? Explain different operations with example.
o Definition
o Relational algebra is a procedural (formal) query language that operates on
relations (tables) using a set of operators. Results are relations. It's the
theoretical foundation for SQL.
o Basic operations (with notation and short example)
o Selection (σ) — picks rows satisfying a predicate.
σ_{Branch='CSE'}(Student)
SQL equivalent: SELECT * FROM Student WHERE Branch='CSE';
o Projection (π) — picks columns.
π_{RollNo,Name}(Student)
SQL: SELECT RollNo, Name FROM Student;
o Union (∪) — set union (same schema).
R ∪ S — rows in R or S.
o Set difference (−) — rows in R not in S.
R−S
o Cartesian product (×) — pair every row from R with every row from S.
Student × Book
o Rename (ρ) — rename relation or attributes.
ρ_{S(Student)}(...)
o Join — combinations:
o Theta-join: R ⋈_{R.a = S.b} S
o Equijoin: theta-join with =.
o Natural join (⋈): implicitly join on all common attribute names and remove
duplicates.
o Outer joins (extensions of algebra to include NULL padding for unmatched
rows).
o Division (÷) — used for queries like “find X that are related to all Y”. (Less
common in practice.)
o Example using Student and Issue
o Query: find RollNo of students who issued at least one book.
o Algebra: π_{RollNo}(Issue)
o SQL: SELECT DISTINCT RollNo FROM Issue;
o Query: find names of students who issued ISBN '978-ABC'
o Algebra: π_{Name}(σ_{ISBN='978-ABC'}(Issue) ⋈_{[Link] =
[Link]} Student)
o SQL:
o SELECT [Link]
o FROM Student s
o JOIN Issue i ON [Link] = [Link]
o WHERE [Link] = '978-ABC';
o Q.6 What is relational calculus? What is tuple relational calculus and
domain relational calculus?
o Relational Calculus
o Non-procedural (declarative) formal query language: describes what to
retrieve, not how. Two forms:
o Tuple Relational Calculus (TRC)
o Domain Relational Calculus (DRC)
o Tuple Relational Calculus (TRC)
o Uses tuple variables that range over rows. Query form: { t | Student(t) ∧
[Link] = 'CSE' }
o Example: Names of CSE students:
o TRC: { [Link] | Student(s) ∧ [Link] = 'CSE' }
o Safety: only safe formulas that produce finite results allowed.
o Domain Relational Calculus (DRC)
o Uses domain variables (one variable per attribute). Example:
o { n | ∃r, f, b ( Student(r, n, f, b) ∧ b = 'CSE' ) }
o Here n is a domain variable for Name.
o Closer to predicate logic over attribute values.
o Comparison
o TRC is tuple-centric, simpler for many tasks; DRC is attribute-value centric.
o Both are theoretical bases for declarative query languages and influenced
SQL.
o Q.7 Queries on the given schema — SQL and relational algebra
o Schema recap: Student(RollNo, Name, Father_Name, Branch)
Book(ISBN, Title, Author, Publisher)
Issue(RollNo, ISBN, Date_of_Issue)
o I. List roll number and name of all students of the branch CSE.
o SQL:
o SELECT RollNo, Name
o FROM Student
o WHERE Branch = 'CSE';
o Relational algebra:
o π_{RollNo,Name} ( σ_{Branch='CSE'} ( Student ) )
o II. Find the name of student who has issued a book published by ABC
publisher.
o SQL:
o SELECT DISTINCT [Link]
o FROM Student s
o JOIN Issue i ON [Link] = [Link]
o JOIN Book b ON [Link] = [Link]
o WHERE [Link] = 'ABC';
o Relational algebra (one clear form):
o π_{Name} ( ( Student ⋈_{[Link] = [Link]} Issue )
o ⋈_{[Link] = [Link]} ( σ_{Publisher='ABC'}(Book) ) )
o (You can also reorder selection early for efficiency.)
o III. List title of all books and their authors issued to student RAM.
o SQL:
o SELECT DISTINCT [Link], [Link]
o FROM Book b
o JOIN Issue i ON [Link] = [Link]
o JOIN Student s ON [Link] = [Link]
o WHERE [Link] = 'RAM';
o Relational algebra:
o π_{Title,Author} ( ( σ_{Name='RAM'}(Student)
⋈_{[Link]=[Link]} Issue )
o ⋈_{[Link]=[Link]} Book )
o IV. List title of all books issued on or before December 1, 2020.
o SQL:
o SELECT DISTINCT [Link]
o FROM Book b
o JOIN Issue i ON [Link] = [Link]
o WHERE i.Date_of_Issue <= '2020-12-01';
o Relational algebra:
o π_{Title} ( Book ⋈_{[Link] = [Link]} ( σ_{Date_of_Issue <= '2020-12-
01'}(Issue) ) )
o V. List all books published by publisher ABC.
o SQL:
o SELECT ISBN, Title, Author, Publisher
o FROM Book
o WHERE Publisher = 'ABC';
o Relational algebra:
o σ_{Publisher='ABC'}(Book)
o Q.8 Difference between COMMIT and ROLLBACK in SQL
o COMMIT
o Permanently saves all changes made in the current transaction to the
database.
o After COMMIT, changes are visible to other transactions and cannot be
undone via ROLLBACK.
o Example:
o BEGIN TRANSACTION;
o UPDATE Account SET balance = balance - 100 WHERE acc_no = 1;
o UPDATE Account SET balance = balance + 100 WHERE acc_no = 2;
o COMMIT;
o ROLLBACK
o Undoes all changes since the start of the current transaction (or since last
SAVEPOINT).
o Use when an error occurs or to abort changes.
o Example:
o BEGIN TRANSACTION;
o INSERT INTO Issue(...) VALUES(...);
o -- error happened or validation failed
o ROLLBACK;
o Additional points
o SAVEPOINT allows partial rollback to a named point.
o Many DBMS have autocommit mode (every statement auto-committed)
unless you explicitly start a transaction.
o Q.9 What are the 5 types of commands in SQL?
o Common classification (5 groups):
o DDL — Data Definition Language
o Define/alter schema. CREATE, ALTER, DROP, TRUNCATE.
o Example: CREATE TABLE Book (...);
o DML — Data Manipulation Language
o Modify data rows. INSERT, UPDATE, DELETE.
o Example: INSERT INTO Student (...) VALUES (...);
o DQL — Data Query Language
o Retrieve data. SELECT (some lists treat SELECT under DML but many treat it as
its own DQL).
o Example: SELECT * FROM Book WHERE Publisher='ABC';
o TCL — Transaction Control Language
o Transaction commands. COMMIT, ROLLBACK, SAVEPOINT.
o Example: COMMIT;
o DCL — Data Control Language
o Access control: GRANT, REVOKE.
o Example: GRANT SELECT ON Book TO user1;
o Q.10 What are the 5 functions of SQL?
o Interpreting this as the five common aggregate functions (often taught
together):
o COUNT() — counts rows.
o SELECT COUNT(*) FROM Student;
o SUM() — sum of numeric column values.
o SELECT SUM(price) FROM Book; (if price existed)
o AVG() — average value.
o SELECT AVG(rating) FROM Book; (if rating existed)
o MIN() — minimum value.
o SELECT MIN(Date_of_Issue) FROM Issue;
o MAX() — maximum value.
o SELECT MAX(Date_of_Issue) FROM Issue;
o (If the question meant “functions” as broader SQL function categories —
scalar functions: CONCAT, SUBSTR, ROUND, UPPER, LOWER — let me know
and I’ll expand.)
o If you want, I can:
o convert these answers into a neatly formatted PDF or Word file,
o provide sample data and run sample SQL results, or
o add diagrammatic ER examples for mapping constraints.
o Tell me which of those you prefer and I’ll prepare it right away.
o Definition: Cartesian product of two tables — every row from left paired with
every row from right.
o Use: rarely useful directly; used to generate combinations.
o Example:
SELECT * FROM A CROSS JOIN B;
-- If A has 2 rows and B has 3 rows, result has 6 rows.
2. NATURAL JOIN
o Definition: An equijoin automatically on all columns with the same names in
both tables. Duplicate join columns are eliminated in result.
o Danger: ambiguous if multiple same-named columns exist; better to use
explicit USING or ON.
o Example:
-- Suppose Student and Issue both have RollNo column
SELECT * FROM Student NATURAL JOIN Issue;
-- Equivalent to an equijoin on all matching column names (here RollNo).
3. LEFT OUTER JOIN (LEFT JOIN)
o Definition: All rows from left table retained; matching rows from right table if
exist; right columns NULL when no match.
o Use: find all left rows with optional matching right rows.
o Example:
SELECT [Link], [Link], [Link]
FROM Student s
LEFT JOIN Issue i ON [Link] = [Link];
-- Returns all students; ISBN is NULL if student hasn't issued any book.
4. RIGHT OUTER JOIN (RIGHT JOIN)
o Definition: All rows from right table retained; matching left rows where exist;
left columns NULL when no match.
o Example:
SELECT [Link], [Link]
FROM Student s
RIGHT JOIN Issue i ON [Link] = [Link];
-- Returns all issue rows; student info NULL if issue points to unknown student.
5. Quick comparison bullets
o CROSS JOIN = Cartesian product (no condition).
o NATURAL JOIN = automatic equijoin on same-named columns; drops
duplicate columns.
o LEFT JOIN preserves left table rows; RIGHT JOIN preserves right table rows.
Q.3 Describe mapping constraints with its types
Mapping constraints (cardinality constraints) define how many instances of
one entity relate to instances of another in a relationship.
1. 1 : 1 (One-to-One)
o Each entity in A is associated with at most one entity in B and vice versa.
o Example: Person ↔ Passport (one person → one passport).
2. 1 : N (One-to-Many)
o One entity in A may relate to many entities in B; any B relates to at most one
A.
o Example: Department (1) → Student (N): one dept has many students.
3. N : 1 (Many-to-One)
o Reverse of 1:N (semantics same as above, orientation differs).
4. M : N (Many-to-Many)
o Entities in A can relate to many in B and vice versa.
o Example: Student ↔ Course : a student may take many courses, a course has
many students. Implemented via bridge/junction table.
Q.4 What are different integrity constraints?
Integrity constraints ensure correctness and consistency of data.
1. Entity integrity
o Primary key values must be unique and NOT NULL.
o Example: RollNo as PRIMARY KEY in Student.
CREATE TABLE Student(
RollNo INT PRIMARY KEY,
Name VARCHAR(100),
Branch VARCHAR(50)
);
2. Referential integrity
o Foreign key must match a primary key in referenced table (or be NULL if
allowed).
CREATE TABLE Issue(
RollNo INT,
ISBN VARCHAR(20),
Date_of_Issue DATE,
FOREIGN KEY (RollNo) REFERENCES Student(RollNo),
FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);
3. Domain constraints
o Column must be of declared type and (optionally) must satisfy value range or
format (e.g., CHECK).
-- Branch must be one of specified values
ALTER TABLE Student ADD CONSTRAINT chk_branch CHECK (Branch IN
('CSE','ECE','ME','CE'));
4. Unique constraint
o Column values must be unique (other than primary key).
ALTER TABLE Book ADD CONSTRAINT unique_title UNIQUE (Title);
5. NOT NULL
o Column cannot have NULL values.
ALTER TABLE Student MODIFY Name VARCHAR(100) NOT NULL;
6. CHECK constraints
o Custom boolean expressions for validation.
ALTER TABLE Issue ADD CONSTRAINT check_date CHECK (Date_of_Issue <=
CURRENT_DATE);
Q.5 What is Relational Algebra? Explain different operations with example.
1. Definition
o Relational algebra is a procedural (formal) query language that operates on
relations (tables) using a set of operators. Results are relations. It's the
theoretical foundation for SQL.
2. Basic operations (with notation and short example)
o Selection (σ) — picks rows satisfying a predicate.
σ_{Branch='CSE'}(Student)
SQL equivalent: SELECT * FROM Student WHERE Branch='CSE';
o Projection (π) — picks columns.
π_{RollNo,Name}(Student)
SQL: SELECT RollNo, Name FROM Student;
o Union (∪) — set union (same schema).
R ∪ S — rows in R or S.
o Set difference (−) — rows in R not in S.
R−S
o Cartesian product (×) — pair every row from R with every row from S.
Student × Book
o Rename (ρ) — rename relation or attributes.
ρ_{S(Student)}(...)
o Join — combinations:
▪ Theta-join: R ⋈_{R.a = S.b} S
▪ Equijoin: theta-join with =.
▪ Natural join (⋈): implicitly join on all common attribute names and
remove duplicates.
▪ Outer joins (extensions of algebra to include NULL padding for
unmatched rows).
o Division (÷) — used for queries like “find X that are related to all Y”. (Less
common in practice.)
3. Example using Student and Issue
o Query: find RollNo of students who issued at least one book.
▪ Algebra: π_{RollNo}(Issue)
▪ SQL: SELECT DISTINCT RollNo FROM Issue;
o Query: find names of students who issued ISBN '978-ABC'
▪ Algebra: π_{Name}(σ_{ISBN='978-ABC'}(Issue) ⋈_{[Link] =
[Link]} Student)
▪ SQL:
SELECT [Link]
FROM Student s
JOIN Issue i ON [Link] = [Link]
WHERE [Link] = '978-ABC';