UNIT II - RELATIONAL MODEL & SQL

TOPIC 1: RELATIONAL DATA MODEL BASICS

What is a Relational Model?

Simple Explanation:

Data organized as tables (relations) with rows (tuples) and columns (attributes).

Think of it like an Excel spreadsheet:

●​ Each table = one sheet
●​ Each row = one record
●​ Each column = one field/attribute
Real-Life Example:

STUDENT Table:
text

rollno | name | email | branch

-------|---------|-------------------|--------
1 | Ram | ram@[Link] | CSE
2 | Priya | priya@[Link] | ECE
3 | Raj | raj@[Link] | CSE

●​ Table name: STUDENT

●​ Attributes: rollno, name, email, branch
●​ Tuples: 3 rows (Ram, Priya, Raj)
●​ Each row uniquely identified by rollno (primary key)

Key Terms
1. Relation (Table):​
A table with rows and columns.
2. Tuple (Row):​
One record in the table.​
Example: (1, Ram,

ram@[Link]

, CSE)

3. Attribute (Column):​
A field/property.​
Example: rollno, name, email, branch

4. Domain:​
Set of valid values for an attribute.​
Example: Domain of rollno = {1, 2, 3, ...}

5. Degree:​
Number of attributes (columns) in a relation.​
Example: STUDENT degree = 4 (4 columns)

6. Cardinality:​
Number of tuples (rows) in a relation.​
Example: STUDENT cardinality = 3 (3 rows)

TOPIC 2: INTEGRITY CONSTRAINTS

What are Integrity Constraints?

Rules that ensure data consistency and validity.

Real-Life Example:

Bank account:
●​ Age must be > 18 (domain constraint)
●​ Account number must be unique (key constraint)
●​ Account must have a balance (not null constraint)
●​ Account's customer must exist (referential constraint)

Four Main Types

1. Entity Integrity Constraint
Rule: Primary key cannot be NULL, must be UNIQUE

Why? Primary key identifies each tuple. If NULL, can't identify.

Example:
text

STUDENT table:
rollno | name
-------|-------
1 | Ram
NULL | Priya ← INVALID (rollno is PK, can't be NULL)

2. Domain Constraint

Rule: Attribute value must be within defined domain (data type, range).

Example:
text

Age must be INT

Age must be between 18-80
Email must have "@"

age = 25 ← Valid
age = "old" ← Invalid (not INT)
age = 150 ← Invalid (out of range)

In SQL:
sql

CREATE TABLE STUDENT (

rollno INT,
name VARCHAR(50),
age INT CHECK (age >= 18 AND age <= 80),
...
);
3. Key Constraint (Uniqueness)

Rule: Primary key and unique attributes must have unique values.

Example:
text

STUDENT table:
rollno | email
-------|-------------------
1 | ram@[Link]
2 | priya@[Link]
3 | ram@[Link] ← Invalid (email not unique)

In SQL:
sql

CREATE TABLE STUDENT (

rollno INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
...
);

4. Referential Integrity Constraint

Rule: Foreign key value must either be NULL or exist as primary key in referenced
table.

Why? Ensures relationships are valid. Can't have enrollment for non-existent student.

Example:

STUDENT table:
text

rollno | name
-------|------
1 | Ram
2 | Priya

ENROLLMENT table:
text

rollno | course_id
-------|----------
1 | CS101
2 | CS102
5 | CS103 ← Invalid (student 5 doesn't exist)

In SQL:
sql

CREATE TABLE ENROLLMENT (

rollno INT,
course_id INT,
FOREIGN KEY (rollno) REFERENCES STUDENT(rollno),
...
);

TOPIC 3: RELATIONAL ALGEBRA (Brief Overview)

Relational algebra = mathematical operations on relations (tables).

Main Operations:

1. Selection (σ) - Filter rows

●​ Get rows where condition is true
●​ Example: Select students from CSE branch
2. Projection (π) - Select columns
●​ Get specific columns
●​ Example: Get only name and email from STUDENT
3. Join (⋈) - Combine tables
●​ Link tables on common key
 ●​ Example: Join STUDENT and ENROLLMENT on rollno
4. Union (∪) - Combine rows
●​ Merge two relations
●​ Example: Get all CSE students AND all ECE students
5. Difference (-)
●​ Rows in first relation but not in second
●​ Example: Students NOT enrolled in any course
These are for exam understanding. SQL queries do the same things.

TOPIC 4: SQL COMMANDS OVERVIEW

DDL - Data Definition Language (CREATE, ALTER, DROP)

CREATE TABLE:
sql

CREATE TABLE STUDENT (

rollno INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT CHECK (age >= 18),
branch VARCHAR(10)
);

ALTER TABLE:
sql

ALTER TABLE STUDENT ADD COLUMN phone INT;

ALTER TABLE STUDENT DROP COLUMN phone;

DROP TABLE:
sql

DROP TABLE STUDENT;

DML - Data Manipulation Language (INSERT, UPDATE,
DELETE)
INSERT:
sql

INSERT INTO STUDENT VALUES (1, 'Ram', 'ram@[Link]', 20,

'CSE');
INSERT INTO STUDENT (rollno, name, email)
VALUES (2, 'Priya', 'priya@[Link]');

UPDATE:
sql

UPDATE STUDENT SET branch = 'ECE' WHERE rollno = 1;

UPDATE STUDENT SET age = age + 1;

DELETE:
sql

DELETE FROM STUDENT WHERE rollno = 1;

DELETE FROM STUDENT; -- Delete all rows

TCL - Transaction Control Language

COMMIT: Save changes permanently​
ROLLBACK: Undo changes​
SAVEPOINT: Mark a point to rollback to

TOPIC 5: SQL SELECT QUERIES & JOINs

Basic SELECT
sql

SELECT * FROM STUDENT; -- All columns, all rows

SELECT name, email FROM STUDENT; -- Specific columns
SELECT * FROM STUDENT WHERE branch = 'CSE'; -- With condition

JOINS - Combining Multiple Tables

Analogy: Merging two Excel sheets based on common columns.

Real Example:

STUDENT table:
text

rollno | name | dept_id

-------|--------|--------
1 | Ram | 10
2 | Priya | 20
3 | Raj | 10

DEPARTMENT table:
text

dept_id | dept_name
--------|----------
10 | CSE
20 | ECE

Goal: Get student names WITH their department names.

Four Types of JOINs

1. INNER JOIN (⋈)

Returns: Rows with matching values in BOTH tables.

sql

SELECT [Link], D.dept_name

FROM STUDENT S
INNER JOIN DEPARTMENT D
ON S.dept_id = D.dept_id;

Result:
text

name | dept_name
------|----------
Ram | CSE
Priya | ECE
Raj | CSE

Venn Diagram: Only overlapping part

2. LEFT JOIN (LEFT OUTER JOIN)

Returns: ALL rows from LEFT table + matching rows from RIGHT table.
sql

SELECT [Link], D.dept_name

FROM STUDENT S
LEFT JOIN DEPARTMENT D
ON S.dept_id = D.dept_id;

If we add a student with non-existent dept_id:

text

rollno | name | dept_id

-------|--------|--------
1 | Ram | 10
2 | Priya | 20
4 | Vicky | 30 (dept 30 doesn't exist)
Result:
text

name | dept_name
------|----------
Ram | CSE
Priya | ECE
Vicky | NULL ← No matching dept, so NULL

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns: ALL rows from RIGHT table + matching rows from LEFT table.
sql

SELECT [Link], D.dept_name

FROM STUDENT S
RIGHT JOIN DEPARTMENT D
ON S.dept_id = D.dept_id;

If a department has no students:

text

dept_id | dept_name
--------|----------
10 | CSE
20 | ECE
30 | IT (no students in this dept)

Result:
text

name | dept_name
------|----------
Ram | CSE
Priya | ECE
NULL | IT ← Dept exists but no student, so NULL
4. CROSS JOIN

Returns: ALL combinations (Cartesian product) of rows.

sql

SELECT [Link], D.dept_name

FROM STUDENT S
CROSS JOIN DEPARTMENT D;

Result:
text

name | dept_name
------|----------
Ram | CSE
Ram | ECE
Ram | IT
Priya | CSE
Priya | ECE
Priya | IT
...

(3 students × 3 depts = 9 rows)

JOIN Comparison Table:

JOIN Rows from LEFT Rows from RIGHT Matching Non-matching

INNE
Matching only Matching only YES NO
R
 LEFT as-is, RIGHT as
LEFT ALL Matching only YES
NULL

RIGH LEFT as NULL, RIGHT

Matching only ALL YES
T as-is

CROS
N/A (all combinations) N/A N/A All combos
S

NATURAL JOIN
Returns: Rows where all columns with same name match (automatic join condition).
sql

-- Instead of:
SELECT * FROM STUDENT S
INNER JOIN DEPARTMENT D
ON S.dept_id = D.dept_id;

-- Use:
SELECT * FROM STUDENT
NATURAL JOIN DEPARTMENT;

DBMS automatically joins on dept_id (common column).

TOPIC 6: AGGREGATE FUNCTIONS

What are Aggregate Functions?

Functions that operate on a SET of rows and return one value.
Real-Life Example:

Calculate student statistics:

●​ COUNT: How many students? → 50
●​ SUM: Total of all salaries? → $50,000
●​ AVG: Average GPA? → 3.5
●​ MAX: Highest score? → 98
●​ MIN: Lowest score? → 45

Common Aggregate Functions

1. COUNT()
sql

SELECT COUNT(*) FROM STUDENT; -- Count all rows: 50

SELECT COUNT(email) FROM STUDENT; -- Count non-NULL
emails
SELECT COUNT(DISTINCT branch)
FROM STUDENT; -- Count unique
branches: 3

2. SUM()
sql

SELECT SUM(salary) FROM EMPLOYEE; -- Total salary paid

SELECT SUM(marks) FROM EXAM WHERE class = '10th'; -- Total
class 10 marks

3. AVG()
sql

SELECT AVG(salary) FROM EMPLOYEE; -- Average salary

SELECT AVG(marks) FROM EXAM
WHERE subject = 'Math'; -- Avg math score
4. MAX() / MIN()
sql

SELECT MAX(salary) FROM EMPLOYEE; -- Highest salary

SELECT MIN(age) FROM STUDENT; -- Youngest student
SELECT MAX(marks), MIN(marks) FROM EXAM; -- Range

GROUP BY with Aggregate Functions

Get aggregate for each group.

Real Example:
sql

SELECT branch, COUNT(*)

FROM STUDENT
GROUP BY branch;

Result:
text

branch | COUNT(*)
-------|----------
CSE | 20
ECE | 15
IT | 15

More Complex:
sql

SELECT dept_id, AVG(salary)

FROM EMPLOYEE
GROUP BY dept_id
HAVING AVG(salary) > 50000;
 ●​ GROUP BY: Group employees by department
●​ HAVING: Keep only depts with avg salary > 50000

TOPIC 7: VIEWS

What is a VIEW?
A virtual table — doesn't store data, just a saved query.

Analogy: Like a saved search in Gmail. Not a new folder, just a filter showing specific
emails.

Real-Life Example:
sql

CREATE VIEW CSE_STUDENTS AS

SELECT name, email FROM STUDENT WHERE branch = 'CSE';

Using the view:

sql

SELECT * FROM CSE_STUDENTS; -- Shows only CSE students

Benefits:
●​ Simplifies queries (reuse common filters)
●​ Security (show only certain columns)
●​ Hides complexity

TOPIC 8: INDEXES

What is an INDEX?
A data structure that speeds up data retrieval (like a book's index).

Analogy: Instead of reading entire book to find "Concurrency Control," go to index,

find page 250, jump there directly.
Real-Life Example:
sql

CREATE INDEX idx_rollno ON STUDENT(rollno);

Now searching by rollno is fast:

sql

SELECT * FROM STUDENT WHERE rollno = 5; -- Uses index, very

fast

Without index, would scan entire table (slow).

Trade-off: Faster reads, but slower writes (index must be updated).

TOPIC 9: TRIGGERS

What is a TRIGGER?
A piece of code that automatically executes when a specific event occurs.

Analogy: Like an alarm — when condition is met, alarm goes off.

Real-Life Example:

Requirement: When student marks drop below 40%, send email alert.

Without trigger: Manual check every day

With trigger: Automatic

sql

CREATE TRIGGER alert_low_marks

AFTER UPDATE ON EXAM
FOR EACH ROW
BEGIN
IF [Link] < 40 THEN
-- Send alert email
EXEC send_email(NEW.student_id);
 END IF;
END;

When exam marks updated:

●​ If new marks < 40 → Email sent automatically

Types of Triggers
1. BEFORE Trigger - Executes BEFORE event
●​ Validate data before insert/update
●​ Example: Check age > 18 before insert student
2. AFTER Trigger - Executes AFTER event
●​ Log changes, send notifications
●​ Example: Send confirmation email after order placed
3. INSERT Trigger - On INSERT event

4. UPDATE Trigger - On UPDATE event

5. DELETE Trigger - On DELETE event

Trigger Example: Automatic Audit Log

Requirement: Log every salary change.
sql

CREATE TABLE SALARY_LOG (

emp_id INT, old_salary INT, new_salary INT, change_date DATE
);

CREATE TRIGGER log_salary_change

AFTER UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO SALARY_LOG
VALUES (NEW.emp_id, [Link], [Link], NOW());
END;
When salary updated:
●​ Old value saved to log
●​ New value saved to log
●​ Timestamp recorded
●​ Automatic audit trail

TOPIC 10: SQL DIFFERENCES - Key Commands

DROP vs DELETE vs TRUNCATE

DROP:
●​ Removes entire table structure + data
●​ Space released
●​ Slower (removes structure)
●​ Cannot rollback (without backup)
sql

DROP TABLE STUDENT; -- Table gone, structure gone

DELETE:
●​ Removes only data rows, keeps structure
●​ Slower (logs each row deleted)
●​ Can rollback (with ROLLBACK)
●​ Can use WHERE clause
sql

DELETE FROM STUDENT WHERE age > 50; -- Data gone, table
structure remains
DELETE FROM STUDENT; -- All rows deleted

TRUNCATE:
●​ Removes all data rows quickly, keeps structure
●​ Faster (doesn't log each row)
 ●​ Usually cannot rollback (some DBs allow with backup logs)
●​ Cannot use WHERE clause
sql

TRUNCATE TABLE STUDENT; -- All rows gone, very fast

Comparison Table:

Aspect DROP DELETE TRUNCATE

What removed Structure + Data Data only Data only

Speed Slow Slow Fast

Space released Yes No (usually) Yes

WHERE clause N/A Yes No

Rollback Difficult Yes Usually no

Triggers Not fired Fired Not fired

NOW: ALL UNIT II PYQS WITH ANSWERS

2-MARK QUESTIONS (Section A)

Q 2022-23(c): "What is the difference between DROP and
DELETE command?"
ANSWER:

Aspect DROP DELETE

Removes Table structure + data Data only

Speed Slower Slower than TRUNCATE

Space Released Not released (usually)

WHERE clause N/A Can use WHERE

Rollback Very difficult Can rollback with ROLLBACK

Triggers Not fired Fired for each row

Usage Remove table entirely Remove specific/all rows

Example:
●​ DROP TABLE STUDENT; — Table definition gone
●​ DELETE FROM STUDENT; — All rows deleted, table structure remains

Q 2022-23(d): "What are different Integrity Constraints?"

ANSWER:
1. Entity Integrity Constraint
●​ Primary key cannot be NULL
●​ Primary key must be UNIQUE
●​ Ensures each tuple uniquely identified
2. Domain Constraint
●​ Attribute must be within defined data type and range
●​ Example: age INT CHECK (age >= 18)
3. Key Constraint (Uniqueness)
●​ Primary and unique keys must have unique values
●​ No duplicates allowed
4. Referential Integrity Constraint
●​ Foreign key value must exist as primary key in referenced table or be NULL
●​ Example: dept_id in EMPLOYEE must exist in DEPARTMENT

Q 2024-25(b): "Differentiate TRUNCATE and DELETE

command"
ANSWER:

Aspect DELETE TRUNCATE

Data removed YES YES

Structure kept YES YES

Speed Slow (logs each row) Very Fast

Space
No Yes
released
 WHERE
Allowed NOT allowed
clause

Triggers Fired NOT fired

Rollback Can rollback Usually cannot

TRUNCATE TABLE
Example DELETE FROM STUDENT WHERE age < 18;
STUDENT;

Q 2024-25(c): "Define triggers and its types."

ANSWER:

Trigger Definition:

A piece of code that automatically executes when a specific event occurs on a table
(INSERT, UPDATE, DELETE).

Types:

1. BEFORE Trigger:
●​ Fires BEFORE the event
●​ Used for validation, prevention
●​ Example: Check data before insert
2. AFTER Trigger:
●​ Fires AFTER the event
●​ Used for logging, notifications
●​ Example: Log salary change after update
3. INSERT Trigger: Fires on INSERT

4. UPDATE Trigger: Fires on UPDATE

5. DELETE Trigger: Fires on DELETE

Example:
sql

CREATE TRIGGER audit_employee_delete

AFTER DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO deleted_employees VALUES (OLD.emp_id, [Link],
NOW());
END;

Q 2023-24(c): "Explain referential integrity."

ANSWER:

Referential Integrity Constraint:

Rule that foreign key value must either be NULL or exist as a primary key value in the
referenced table.

Ensures relationships between tables are valid.

Example:
text

STUDENT(rollno PK, name)

ENROLLMENT(rollno FK, course_id FK)

Rule: Every rollno in ENROLLMENT must exist in STUDENT

OR be NULL

Violation Example:
text

ENROLLMENT(5, CS101) → Invalid if student 5 doesn't exist in

STUDENT
Prevents: Orphaned records (enrollment for non-existent student)

10-MARK QUESTIONS (Section B & C)

Q 2023-24(2b): "What is Aggregate function in SQL? Write

SQL query for aggregate function."
ANSWER:

Aggregate Function Definition:

A function that operates on a set of values and returns a single result.

Common Aggregate Functions:

1. COUNT() - Count rows

sql

SELECT COUNT(*) FROM STUDENT; -- Total students: 50

SELECT COUNT(email) FROM STUDENT; -- Non-NULL emails
SELECT COUNT(DISTINCT branch) FROM STUDENT; -- Unique branches:
3

2. SUM() - Total
sql

SELECT SUM(salary) FROM EMPLOYEE; -- Total paid salaries

SELECT SUM(marks) FROM EXAM
WHERE subject = 'Math'; -- Total math marks

3. AVG() - Average
sql

SELECT AVG(salary) FROM EMPLOYEE; -- Average salary

SELECT AVG(marks) FROM EXAM
WHERE class = '10th'; -- Average class 10
marks

4. MAX() - Maximum
sql

SELECT MAX(salary) FROM EMPLOYEE; -- Highest salary

SELECT MAX(marks) FROM EXAM; -- Highest score

5. MIN() - Minimum
sql

SELECT MIN(age) FROM STUDENT; -- Youngest student

SELECT MIN(marks) FROM EXAM; -- Lowest score

GROUP BY Example:
sql

SELECT branch, COUNT(*), AVG(marks)

FROM STUDENT
GROUP BY branch;

Result:
text

branch | COUNT(*) | AVG(marks)

-------|----------|----------
CSE | 20 | 75.5
ECE | 15 | 73.2
IT | 15 | 74.8
Q 2023-24(2c): "Describe triggers and multivalued
dependency (MVD)."
ANSWER:

Triggers: (Already covered above)

Q 2023-24(4b): "Write difference between cross join,

natural join, left outer join, and right outer join with suitable
example."
ANSWER:

Given Tables:

STUDENT:
text

sid | name | branch

----|--------|-------
1 | Ram | CSE
2 | Priya | ECE
3 | Raj | CSE

DEPARTMENT:
text

branch | dept_name
--------|----------
CSE | Computer Science
ECE | Electronics
IT | Information Tech

1. INNER JOIN (Regular JOIN)

sql
SELECT [Link], D.dept_name
FROM STUDENT S
INNER JOIN DEPARTMENT D
ON [Link] = [Link];

Result: Only matching rows

text

name | dept_name
------|-------------------
Ram | Computer Science
Raj | Computer Science
Priya | Electronics

2. CROSS JOIN
sql

SELECT [Link], D.dept_name

FROM STUDENT S
CROSS JOIN DEPARTMENT D;

Result: ALL combinations (3 students × 3 depts = 9 rows)

text

name | dept_name
------|-------------------
Ram | Computer Science
Ram | Electronics
Ram | Information Tech
Priya | Computer Science
Priya | Electronics
Priya | Information Tech
Raj | Computer Science
Raj | Electronics
Raj | Information Tech
3. NATURAL JOIN
sql

SELECT [Link], D.dept_name

FROM STUDENT S
NATURAL JOIN DEPARTMENT D;

Result: Automatic join on common column (branch)

text

name | dept_name
------|-------------------
Ram | Computer Science
Raj | Computer Science
Priya | Electronics

(Same as INNER JOIN, but automatic join condition)

4. LEFT OUTER JOIN

sql

SELECT [Link], D.dept_name

FROM STUDENT S
LEFT JOIN DEPARTMENT D
ON [Link] = [Link];

Result: ALL students + matching departments

text

name | dept_name
------|-------------------
Ram | Computer Science
Priya | Electronics
Raj | Computer Science
(If a student had non-existent branch, would show as NULL in dept_name)

5. RIGHT OUTER JOIN

sql

SELECT [Link], D.dept_name

FROM STUDENT S
RIGHT JOIN DEPARTMENT D
ON [Link] = [Link];

Result: ALL departments + matching students

text

name | dept_name
------|-------------------
Ram | Computer Science
Raj | Computer Science
Priya | Electronics
NULL | Information Tech (No students in IT)

Comparison Table:

JOIN Type Rows from LEFT Rows from RIGHT Matching Use Case

INNER Matching only Matching only YES Get matched data

LEFT ALL Matching only YES All left, related right

RIGHT Matching only ALL YES All right, related left

 CROSS All (combos) All (combos) NO All combinations

NATURAL (automatic join) (automatic join) YES Easier join syntax

Q 2022-23(4a): "Library schema - Write SQL and RA

queries"
Given Schema:
text

Student (RollNo, Name, Father_Name, Branch)

Book (ISBN, Title, Author, Publisher)
Issue (RollNo, ISBN, Date-of-Issue)

(i) List roll number and name of all students of branch 'CSE'

SQL:
sql

SELECT RollNo, Name

FROM Student
WHERE Branch = 'CSE';

Relational Algebra:
text

π RollNo, Name (σ Branch='CSE' (Student))

(ii) Find name of student who issued book published by 'ABC' publisher

SQL:
sql

SELECT DISTINCT [Link]

FROM Student S
INNER JOIN Issue I ON [Link] = [Link]
INNER JOIN Book B ON [Link] = [Link]
WHERE [Link] = 'ABC';

Relational Algebra:
text

π Name (Student ⋈ Issue ⋈ (σ Publisher='ABC' (Book)))

(iii) List title of all books and their authors issued to student 'RAM'

SQL:
sql

SELECT [Link], [Link]

FROM Book B
INNER JOIN Issue I ON [Link] = [Link]
INNER JOIN Student S ON [Link] = [Link]
WHERE [Link] = 'RAM';

(iv) List title of all books issued on or before December 1, 2020

SQL:
sql

SELECT [Link]
FROM Book B
INNER JOIN Issue I ON [Link] = [Link]
WHERE I.Date_of_Issue <= '2020-12-01';
(v) List all books published by publisher 'ABC'

SQL:
sql

SELECT * FROM Book WHERE Publisher = 'ABC';

Q 2024-25(4a): "Employee schema - SQL statements"

Given Schema:
text

Employee (ename, street, city)

Worksfor (ename, company_name, salary)
Company (company_name, city)

(i) Create the tables

sql

CREATE TABLE Employee (

ename VARCHAR(50),
street VARCHAR(100),
city VARCHAR(50)
);

CREATE TABLE Company (

company_name VARCHAR(50),
city VARCHAR(50)
);

CREATE TABLE Worksfor (

ename VARCHAR(50),
company_name VARCHAR(50),
salary INT,
FOREIGN KEY (ename) REFERENCES Employee(ename),
 FOREIGN KEY (company_name) REFERENCES Company(company_name)
);

(ii) Find employees who live in same city where they work
sql

SELECT [Link]
FROM Employee E
INNER JOIN Worksfor W ON [Link] = [Link]
INNER JOIN Company C ON W.company_name = C.company_name
WHERE [Link] = [Link];

(iii) Find employees with salary > Rs. 50000

sql

SELECT [Link]
FROM Employee E
INNER JOIN Worksfor W ON [Link] = [Link]
WHERE [Link] > 50000;

(iv) Find employees who don't work in "tcs" company

sql

SELECT DISTINCT [Link]

FROM Employee E
WHERE [Link] NOT IN (
SELECT ename FROM Worksfor WHERE company_name = 'tcs'
);

-- OR:
SELECT DISTINCT [Link]
FROM Employee E
LEFT JOIN Worksfor W ON [Link] = [Link] AND W.company_name =
'tcs'
WHERE [Link] IS NULL;

(v) Find employees whose name has second letter 'A'

sql

SELECT [Link]
FROM Employee E
WHERE SUBSTRING([Link], 2, 1) = 'A';

-- OR:
SELECT [Link] FROM Employee E WHERE [Link] LIKE '_A%';

(vi) Find employee with second highest salary

sql

SELECT [Link]
FROM Employee E
INNER JOIN Worksfor W ON [Link] = [Link]
WHERE [Link] = (
SELECT MAX(salary) FROM Worksfor
WHERE salary < (SELECT MAX(salary) FROM Worksfor)
);

-- OR using LIMIT (MySQL, PostgreSQL):

SELECT [Link]
FROM Employee E
INNER JOIN Worksfor W ON [Link] = [Link]
ORDER BY [Link] DESC
LIMIT 1 OFFSET 1;
Q 2024-25(4b): "Supplier-Part schema - Relational Algebra
queries"
Given Schema:
text

S(S#, SNAME, SCITY, TURNOVER)

P(P#, WEIGHT, COLOR, COST, SELLING PRICE)
SP(S#, P#, QTY)

(i) Get all details of supplier in CALCUTTA with TURNOVER=80

RA:
text

σ SCITY='CALCUTTA' ∧ TURNOVER=80 (S)

(ii) Get part numbers with weight between 25 and 55

RA:
text

π P# (σ WEIGHT ≥ 25 ∧ WEIGHT ≤ 55 (P))

(iii) Get part # with cost > selling price

RA:
text

π P# (σ COST > SELLING_PRICE (P))

(iv) Get part numbers with color red or black

RA:
text

π P# (σ COLOR='red' ∨ COLOR='black' (P))

(v) Get SNAME where S# = 101

RA:
text

π SNAME (σ S#=101 (S))