COMPLETE SQL & RELATIONAL ALGEBRA QA PDF
---------------------------------------------------
1. DDL OPERATIONS IN SQL
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Major VARCHAR(30)
);
ALTER TABLE Student ADD Email VARCHAR(100);
ALTER TABLE Student MODIFY Age SMALLINT;
ALTER TABLE Student DROP COLUMN Email;
DROP TABLE Student;
TRUNCATE TABLE Student;
RENAME TABLE Student TO Learner;
---------------------------------------------------
2. ELDEST EMPLOYEE ON PROJECT 'IOT'
SELECT e.eid, e.ename, e.dob
FROM emp e, works_on w, projects p
WHERE p.pname = 'iot'
AND w.pid = p.pid
AND w.eid = e.eid
AND e.dob = (SELECT MIN(dob) FROM emp);
---------------------------------------------------
3. EMPLOYEES IN 'KR CIRCLE'
SELECT eid, dob, super_eid
FROM emp
WHERE address = 'KR Circle';
---------------------------------------------------
4. EMPLOYEES BORN IN 1982
SELECT eid, ename
FROM emp
WHERE YEAR(dob) = 1982;
OR
WHERE dob BETWEEN '1982-01-01' AND '1982-12-31';
---------------------------------------------------
5. SUPERVISOR DETAILS OF ALL DEPARTMENTS
SELECT DISTINCT s.eid, s.ename, s.dob, d.did, d.dname
FROM emp e
JOIN emp s ON e.super_eid = s.eid
JOIN works_on w ON e.eid = w.eid
JOIN projects p ON w.pid = p.pid
JOIN dept d ON p.did = d.did;
Explanation:
e = employee, s = supervisor (e.super_eid = s.eid)
---------------------------------------------------
6. JOIN vs WHERE
Using JOIN (Modern):
SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.did = d.did;
Using WHERE (Older):
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.did = d.did;
Recommendation:
Use JOIN for combining tables.
Use WHERE for filtering (e.g., WHERE age > 30).
---------------------------------------------------
NOTE: Remaining answers like relational algebra examples, update operations, integrity constraints,
project-join-select examples, etc. were discussed earlier and will be included in a final compiled
revision version.
