DBMS - Answers & SQL (27 Questions)

Generated responses for all 27 questions

Question 1: Create db 'company' with Emp and Dept; sample data and queries

DDL: CREATE TABLE dept ( deptno INT PRIMARY KEY, deptname VARCHAR(50), location VARCHAR(50) ); CREATE
TABLE emp ( eno INT PRIMARY KEY, ename VARCHAR(50), job VARCHAR(50), hiredate DATE, salary
DECIMAL(12,2), commission DECIMAL(12,2), deptno INT, FOREIGN KEY (deptno) REFERENCES dept(deptno) );
Sample INSERTs: INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept VALUES (20,
'RESEARCH', 'DALLAS'); INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO emp VALUES (7369,
'SMITH', 'CLERK', DATE '1980-12-17', 800.00, NULL, 20); INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN',
DATE '1981-02-20', 1600.00, 300.00, 30); INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', DATE '1981-02-22',
1250.00, 500.00, 30); INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', DATE '1981-04-02', 2975.00, NULL, 20);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', DATE '1981-05-01', 2850.00, NULL, 30); Queries & Solutions:
1) Max salary paid to salesman: SELECT MAX(salary) FROM emp WHERE job = 'SALESMAN'; 2) Names starting with
'I': SELECT ename FROM emp WHERE ename LIKE 'I%'; 3) Employees joined before '1981-09-30': SELECT * FROM emp
WHERE hiredate < DATE '1981-09-30'; 4) Emp details in descending order of salary: SELECT * FROM emp ORDER BY
salary DESC; 5) No. of emp & avg salary for dept 20: SELECT COUNT(*) AS num_emp, AVG(salary) AS avg_salary
FROM emp WHERE deptno = 20; 6) Avg salary, min salary hiredate-wise for dept 10: SELECT hiredate, AVG(salary)
AS avg_sal, MIN(salary) AS min_sal FROM emp WHERE deptno = 10 GROUP BY hiredate; 7) Emp name and its
department: SELECT [Link], [Link] FROM emp e JOIN dept d ON [Link] = [Link]; 8) Total salary paid to
each department: SELECT deptno, SUM(salary) AS total_salary FROM emp GROUP BY deptno; 9) Details of emp
working in 'Dev' department (assuming deptname='Dev'): SELECT e.* FROM emp e JOIN dept d ON [Link]=[Link]
WHERE [Link]='Dev'; 10) Update salary of all employees in deptno 10 by 5%: UPDATE emp SET salary = salary
* 1.05 WHERE deptno = 10;

Question 2: employee/works/company/manages - sample DDL, INSERTs & SQL

DDL: CREATE TABLE employee (employee_name VARCHAR(50) PRIMARY KEY, street VARCHAR(100), city VARCHAR(50));
CREATE TABLE company (company_name VARCHAR(100) PRIMARY KEY, city VARCHAR(50)); CREATE TABLE works
(employee_name VARCHAR(50), company_name VARCHAR(100), salary DECIMAL(12,2), FOREIGN KEY (employee_name)
REFERENCES employee(employee_name), FOREIGN KEY (company_name) REFERENCES company(company_name)); CREATE TABL
manages (employee_name VARCHAR(50), manager_name VARCHAR(50)); Sample INSERTs: INSERT INTO employee VALUES
('Alice', '1 High St', 'Pune'); INSERT INTO employee VALUES ('Bob', '2 Park Ave', 'Mumbai'); INSERT INTO
company VALUES ('First Bank Corporation', 'Mumbai'); INSERT INTO company VALUES ('OtherCo', 'Pune'); INSERT
INTO works VALUES ('Alice', 'First Bank Corporation', 12000); INSERT INTO works VALUES ('Bob', 'OtherCo',
9000); INSERT INTO manages VALUES ('Alice', 'Carol'); Queries & Solutions: 1) Employees who work for First
Bank Corporation: SELECT w.employee_name FROM works w WHERE w.company_name = 'First Bank Corporation'; 2)
Employees who do not work for First Bank Corporation: SELECT e.employee_name FROM employee e WHERE
e.employee_name NOT IN (SELECT employee_name FROM works WHERE company_name='First Bank Corporation'); 3)
Company that has most employees: SELECT company_name FROM works GROUP BY company_name ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROWS ONLY; 4) Companies located in every city in which 'Small Bank Corporation' is located: --
Relational division pattern SELECT c2.company_name FROM company c2 WHERE NOT EXISTS ( SELECT [Link] FROM
company c1 WHERE c1.company_name='Small Bank Corporation' AND NOT EXISTS ( SELECT 1 FROM company c3 WHERE
c3.company_name=c2.company_name AND [Link]=[Link] ) ); 5) Details of employees having salary > 10000:
SELECT e.* FROM employee e JOIN works w ON e.employee_name=w.employee_name WHERE [Link] > 10000; 6) Update
salary of all employees who work for First Bank Corporation by 10%: UPDATE works SET salary = salary * 1.10
WHERE company_name = 'First Bank Corporation'; 7) Employee and their managers: SELECT * FROM manages; 8)
Names, street and cities of employees who work for First Bank Corporation and earn > 10000: SELECT
e.employee_name, [Link], [Link] FROM employee e JOIN works w ON e.employee_name=w.employee_name WHERE
w.company_name='First Bank Corporation' AND [Link] > 10000; 9) Companies whose employees earn higher avg
salary than First Bank Corporation: SELECT w.company_name FROM works w GROUP BY w.company_name HAVING
AVG([Link]) > (SELECT AVG(salary) FROM works WHERE company_name='First Bank Corporation');

Question 3: Hotel/Room/Booking/Guest - schema, sample data and common queries

DDL: CREATE TABLE Hotel (HotelNo INT PRIMARY KEY, Name VARCHAR(100), City VARCHAR(50)); CREATE TABLE Room
(RoomNo INT, HotelNo INT, Type VARCHAR(20), Price DECIMAL(10,2), PRIMARY KEY (HotelNo, RoomNo), FOREIGN KEY
(HotelNo) REFERENCES Hotel(HotelNo)); CREATE TABLE Guest (GuestNo INT PRIMARY KEY, GuestName VARCHAR(100),
GuestAddress VARCHAR(200)); CREATE TABLE Booking (HotelNo INT, GuestNo INT, DateFrom DATE, DateTo DATE, RoomNo
INT, PRIMARY KEY (HotelNo, GuestNo, DateFrom), FOREIGN KEY (HotelNo, RoomNo) REFERENCES Room(HotelNo, RoomNo),
FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo)); Sample INSERTs: INSERT INTO Hotel VALUES (1, 'Grosvenor
Hotel', 'London'); INSERT INTO Hotel VALUES (2, 'Park Inn', 'London'); INSERT INTO Room VALUES
(101,1,'Double',75.00); INSERT INTO Room VALUES (102,1,'Single',50.00); INSERT INTO Room VALUES
(201,2,'Double',65.00); INSERT INTO Guest VALUES (1,'John Smith','10 Downing St, London'); INSERT INTO Booking
VALUES (1,1, DATE '2025-04-10', DATE '2025-04-15', 101); Common Queries & Solutions: 1) List full details of
all hotels: SELECT * FROM Hotel; 2) How many hotels are there? SELECT COUNT(*) FROM Hotel; 3) Price and type
of all rooms at Grosvenor Hotel: SELECT [Link], [Link] FROM Room r JOIN Hotel h ON [Link]=[Link] WHERE
[Link]='Grosvenor Hotel'; 4) Number of rooms in each hotel: SELECT HotelNo, COUNT(*) AS num_rooms FROM Room
GROUP BY HotelNo; 5) Update price of all rooms by 5%: UPDATE Room SET Price = Price * 1.05; 6) Full details of
hotels in London: SELECT * FROM Hotel WHERE City='London'; 7) Average price of a room: SELECT AVG(Price) FROM
Room; 8) List all guests currently staying at Grosvenor (today within DateFrom..DateTo): SELECT g.* FROM Guest
g JOIN Booking b ON [Link]=[Link] JOIN Hotel h ON [Link]=[Link] WHERE [Link]='Grosvenor Hotel' AND
DATE '2025-11-05' BETWEEN [Link] AND [Link]; 9) Number of rooms in each hotel in London: SELECT
[Link], COUNT(*) FROM Room r JOIN Hotel h ON [Link]=[Link] WHERE [Link]='London' GROUP BY [Link];

Question 7: Project Management - Project, Employee, Assigned_To (from reference)

DDL: CREATE TABLE Project (project_id VARCHAR(10) PRIMARY KEY, proj_name VARCHAR(50), chief_arch VARCHAR(50));
CREATE TABLE Employee1 (emp_id VARCHAR(10) PRIMARY KEY, emp_name VARCHAR(50)); CREATE TABLE Assigned_To
(project_id VARCHAR(10), emp_id VARCHAR(10), PRIMARY KEY (project_id, emp_id), FOREIGN KEY (project_id)
REFERENCES Project(project_id), FOREIGN KEY (emp_id) REFERENCES Employee1(emp_id)); Sample INSERTs (matching
reference file): INSERT INTO Project VALUES ('C353', 'Database', 'Alice Johnson'); INSERT INTO Project VALUES
('C354', 'WebApp', 'Bob Smith'); INSERT INTO Project VALUES ('C453', 'MobileApp', 'Carol Lee'); INSERT INTO
Employee1 VALUES ('E101', 'John Doe'); INSERT INTO Employee1 VALUES ('E102', 'Jane Smith'); INSERT INTO
Assigned_To VALUES ('C353', 'E101'); INSERT INTO Assigned_To VALUES ('C353', 'E102'); INSERT INTO Assigned_To
VALUES ('C354', 'E102'); Queries & Solutions: 1) Details of employees working on C353: SELECT e.* FROM
Employee1 e JOIN Assigned_To a ON e.emp_id=a.emp_id WHERE a.project_id='C353'; 2) Employee numbers working on
C353: SELECT emp_id FROM Assigned_To WHERE project_id='C353'; 3) Employees working on Database project: SELECT
e.* FROM Employee1 e JOIN Assigned_To a ON e.emp_id=a.emp_id JOIN Project p ON a.project_id=p.project_id WHERE
p.proj_name='Database'; 4) Employees working on both C353 and C354: SELECT e.* FROM Employee1 e JOIN
Assigned_To a1 ON e.emp_id=a1.emp_id AND a1.project_id='C353' JOIN Assigned_To a2 ON e.emp_id=a2.emp_id AND
a2.project_id='C354'; 5) Employee numbers who do not work on C453: SELECT emp_id FROM Employee1 WHERE emp_id
NOT IN (SELECT emp_id FROM Assigned_To WHERE project_id='C453');

Question 8: Employee-Duty (Employee, Position, Duty_allocation)

DDL: CREATE TABLE Employee (emp_no INT PRIMARY KEY, name VARCHAR(50), skill VARCHAR(50), pay_rate
DECIMAL(10,2)); CREATE TABLE Position (posting_no INT PRIMARY KEY, skill VARCHAR(50)); CREATE TABLE
Duty_allocation (posting_no INT, emp_no INT, day DATE, shift VARCHAR(10), PRIMARY KEY (posting_no, emp_no,
day), FOREIGN KEY (emp_no) REFERENCES Employee(emp_no), FOREIGN KEY (posting_no) REFERENCES
Position(posting_no)); Sample INSERTs (from reference): INSERT INTO Employee VALUES
(123460,'xyz','Chef',500); INSERT INTO Employee VALUES (123461,'John Doe','Chef',600); INSERT INTO Employee
VALUES (123462,'Jane Smith','Waiter',400); INSERT INTO Position VALUES (1,'Chef'); INSERT INTO Position VALUES
(2,'Waiter'); INSERT INTO Duty_allocation VALUES (1,123461,DATE '1986-04-05','Morning'); INSERT INTO
Duty_allocation VALUES (3,123461,DATE '1986-04-15','Evening'); INSERT INTO Duty_allocation VALUES
(1,123460,DATE '1986-04-03','Morning'); Queries & Solutions: 1) Duty allocation for emp_no 123461 in April
1986: SELECT * FROM Duty_allocation WHERE emp_no=123461 AND day BETWEEN DATE '1986-04-01' AND DATE
'1986-04-30'; 2) Shift details for employee 'xyz': SELECT [Link] FROM Duty_allocation d JOIN Employee e ON
d.emp_no=e.emp_no WHERE [Link]='xyz'; 3) Employees with pay_rate >= pay_rate of 'xyz': SELECT * FROM Employee
WHERE pay_rate >= (SELECT pay_rate FROM Employee WHERE name='xyz'); 4) Emp_no < 123460 with pay_rate > at
least one emp_no >=123460: SELECT [Link], e1.pay_rate FROM Employee e1 WHERE e1.emp_no < 123460 AND
e1.pay_rate > (SELECT MIN(e2.pay_rate) FROM Employee e2 WHERE e2.emp_no >= 123460); 5) Employees assigned to
all positions requiring 'Chef' (relational division): SELECT [Link] FROM Employee e WHERE [Link]='Chef' AND
NOT EXISTS (SELECT p.posting_no FROM Position p WHERE [Link]='Chef' AND NOT EXISTS (SELECT 1 FROM
Duty_allocation d WHERE d.emp_no=e.emp_no AND d.posting_no=p.posting_no));

Question 9: Banking schema (Deposit, Branch, Customers, Borrow) - DDL, inserts, queries

DDL: CREATE TABLE Branch (bname VARCHAR(50) PRIMARY KEY, city VARCHAR(50)); CREATE TABLE Customers (cname
VARCHAR(50) PRIMARY KEY, city VARCHAR(50)); CREATE TABLE Deposit (actno INT PRIMARY KEY, cname VARCHAR(50),
bname VARCHAR(50), amount DECIMAL(12,2), adate DATE, FOREIGN KEY (cname) REFERENCES Customers(cname), FOREIGN
KEY (bname) REFERENCES Branch(bname)); CREATE TABLE Borrow (loanno INT PRIMARY KEY, cname VARCHAR(50), bname
VARCHAR(50), amount DECIMAL(12,2), FOREIGN KEY (cname) REFERENCES Customers(cname), FOREIGN KEY (bname)
REFERENCES Branch(bname)); Sample INSERTs: INSERT INTO Branch VALUES ('Perryridge','New York'); INSERT INTO
Branch VALUES ('BombayMain','Bombay'); INSERT INTO Customers VALUES ('Anil','Pune'); INSERT INTO Customers
VALUES ('Sunil','Mumbai'); INSERT INTO Deposit VALUES (1001,'Anil','Perryridge',5000, DATE '1996-12-01');
INSERT INTO Deposit VALUES (1002,'Sunil','BombayMain',3000, DATE '1997-03-15'); INSERT INTO Borrow VALUES
(2001,'Anil','Perryridge',2000); Queries & Solutions (examples from tasks): 1) Depositors with amount > 4000:
SELECT cname FROM Deposit WHERE amount > 4000; 2) Account date of customer Anil: SELECT adate FROM Deposit
WHERE cname='Anil'; 3) Account no and deposit amount opened between '1996-12-01' and '1997-05-01': SELECT
actno, amount FROM Deposit WHERE adate BETWEEN DATE '1996-12-01' AND DATE '1997-05-01'; 4) Avg account balance
at Perryridge: SELECT AVG(amount) FROM Deposit WHERE bname='Perryridge'; 5) Names of branches where avg
account balance > 1200: SELECT bname FROM Deposit GROUP BY bname HAVING AVG(amount) > 1200; 6) Delete
depositors having deposit < 5000: DELETE FROM Deposit WHERE amount < 5000; 7) Create a view on deposit table:
CREATE VIEW vw_deposit AS SELECT * FROM Deposit;

Question 13: Publisher/Book/Author/Author_Book/Review - DDL, inserts, queries

DDL: CREATE TABLE PUBLISHER (PID INT PRIMARY KEY, PNAME VARCHAR(100), ADDRESS VARCHAR(200), STATE VARCHAR(5
PHONE VARCHAR(20), EMAILID VARCHAR(100)); CREATE TABLE BOOK (ISBN VARCHAR(20) PRIMARY KEY, BOOK_TITLE
VARCHAR(200), CATEGORY VARCHAR(50), PRICE DECIMAL(10,2), COPYRIGHT_DATE DATE, YEAR INT, PAGE_COUNT INT, PID
INT, FOREIGN KEY (PID) REFERENCES PUBLISHER(PID)); CREATE TABLE AUTHOR (AID INT PRIMARY KEY, ANAME
VARCHAR(100), STATE VARCHAR(50), CITY VARCHAR(50), ZIP VARCHAR(20), PHONE VARCHAR(20), URL VARCHAR(200));
CREATE TABLE AUTHOR_BOOK (AID INT, ISBN VARCHAR(20), PRIMARY KEY (AID,ISBN), FOREIGN KEY (AID) REFERENCES
AUTHOR(AID), FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN)); CREATE TABLE REVIEW (RID INT PRIMARY KEY, ISBN
VARCHAR(20), RATING INT, FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN)); Sample INSERTs: INSERT INTO PUBLISHER
VALUES (1,'MEHTA','Pune','MH','9999999999','mehta@[Link]'); INSERT INTO AUTHOR VALUES (1,'CHETAN
BHAGAT','MH','Pune','411001','8888888888','[Link] INSERT INTO BOOK VALUES
('ISBN001','Half Girlfriend','Fiction',350.00, DATE '2014-10-01',2014,250,1); INSERT INTO AUTHOR_BOOK VALUES
(1,'ISBN001'); INSERT INTO REVIEW VALUES (1,'ISBN001',4); Queries & Solutions: 1) City, phone, url of author
'CHETAN BHAGAT': SELECT city, phone, url FROM AUTHOR WHERE aname='CHETAN BHAGAT'; 2) Book title, review id and
rating of all books: SELECT b.book_title, [Link], [Link] FROM BOOK b LEFT JOIN REVIEW r ON [Link]=[Link]; 3)
Book title, price, author name and url for publisher 'MEHTA': SELECT b.book_title, [Link], [Link], [Link]
FROM BOOK b JOIN PUBLISHER p ON [Link]=[Link] JOIN AUTHOR_BOOK ab ON [Link]=[Link] JOIN AUTHOR a ON
[Link]=[Link] WHERE [Link]='MEHTA'; 4) Update phone of MEHTA to 123456: UPDATE PUBLISHER SET phone='123456'
WHERE pname='MEHTA'; 5) Avg, max, min price of each publisher: SELECT [Link], AVG([Link]), MAX([Link]),
MIN([Link]) FROM PUBLISHER p JOIN BOOK b ON [Link]=[Link] GROUP BY [Link]; 6) Delete books with page_count <
100: DELETE FROM BOOK WHERE page_count < 100; 7) Authors in city Pune with name starting with specified
character: SELECT * FROM AUTHOR WHERE city='Pune' AND aname LIKE 'K%';

Question 14: PL/SQL blocks - attendance & withdrawal exception examples

(a) Attendance check pseudocode (PL/SQL): DECLARE v_roll NUMBER := &roll; v_att NUMBER; BEGIN SELECT Att
INTO v_att FROM Stud WHERE Roll = v_roll; IF v_att < 75 THEN UPDATE Stud SET Status='D' WHERE
Roll=v_roll; DBMS_OUTPUT.PUT_LINE('Term not granted'); ELSE UPDATE Stud SET Status='ND' WHERE
Roll=v_roll; DBMS_OUTPUT.PUT_LINE('Term granted'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Roll not found'); END; (b) Withdrawal with user-defined exception: DECLARE v_acc
NUMBER := &accno; v_withdraw NUMBER := &amt; v_balance NUMBER; insufficient_balance EXCEPTION; BEGIN
SELECT balance INTO v_balance FROM account_master WHERE accno=v_acc; IF v_withdraw > v_balance THEN
RAISE insufficient_balance; ELSE UPDATE account_master SET balance = balance - v_withdraw WHERE
accno=v_acc; END IF; EXCEPTION WHEN insufficient_balance THEN DBMS_OUTPUT.PUT_LINE('Insufficient funds');
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Account not found'); END;

Question 15: More PL/SQL - business rule and library fine example

(a) Raise user-defined exception when bal_due < 0: DECLARE v_bal NUMBER; bal_error EXCEPTION; BEGIN
SELECT bal_due INTO v_bal FROM client_master WHERE client_id = &id; IF v_bal < 0 THEN RAISE bal_error;
END IF; EXCEPTION WHEN bal_error THEN DBMS_OUTPUT.PUT_LINE('Business rule violated: bal_due < 0'); END; (b)
Library fine block (simplified): DECLARE v_roll NUMBER := &roll; v_name VARCHAR2(100) := '&nameofbook';
v_days NUMBER; v_amt NUMBER; BEGIN SELECT (SYSDATE - DateofIssue) INTO v_days FROM Borrow WHERE Roll_no =
v_roll AND NameofBook = v_name; IF v_days BETWEEN 15 AND 30 THEN v_amt := 5 * v_days; ELSIF v_days > 30
THEN v_amt := 50 * v_days; ELSE v_amt := 5 * v_days; END IF; UPDATE Borrow SET Status='R' WHERE
Roll_no=v_roll AND NameofBook=v_name; IF v_amt > 0 THEN INSERT INTO Fine VALUES (v_roll, SYSDATE, v_amt);
END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); END;

Question 16: Cursors examples (implicit/explicit and salary updates)

16(a) Implicit cursor to activate accounts inactive for 365 days (simplified): BEGIN UPDATE account_master
SET status='ACTIVE' WHERE last_txn_date < SYSDATE - 365 AND status='INACTIVE'; IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' accounts activated'); END IF; END; 16(b) Increase salary by 10% for
employees below avg and log into increment_salary: DECLARE CURSOR c IS SELECT e_no, salary FROM emp WHERE
salary < (SELECT AVG(salary) FROM emp); BEGIN FOR r IN c LOOP UPDATE emp SET salary = salary * 1.10
WHERE e_no = r.e_no; INSERT INTO increment_salary (e_no, old_salary, new_salary, updated_on) VALUES
(r.e_no, [Link], [Link]*1.10, SYSDATE); END LOOP; END;

Question 17: Parameterized cursor examples & dept average insertion

17(b) Merge N_RollCall into O_RollCall skipping duplicates - parameterized cursor pseudocode: DECLARE CURSOR
c_new IS SELECT * FROM N_RollCall; BEGIN FOR r IN c_new LOOP MERGE INTO O_RollCall t USING (SELECT
r.roll_no AS roll_no, [Link] AS data FROM dual) s ON (t.roll_no = s.roll_no) WHEN NOT MATCHED THEN INSERT
(...) VALUES (...); END LOOP; END; 17(c) Dept wise average salary insertion using parameterized cursor:
DECLARE CURSOR c_dept IS SELECT d_no FROM EMP GROUP BY d_no; BEGIN FOR d IN c_dept LOOP INSERT INTO
dept_salary (d_no, avg_salary) SELECT d.d_no, AVG(salary) FROM EMP WHERE d_no = d.d_no GROUP BY d.d_no; END
LOOP; END;

Question 18: Triggers - update/delete audit & salary threshold before insert/update

18(a) Trigger to keep audit of updates/deletes on clientmstr (row-level example): CREATE TABLE audit_trade
(op_type VARCHAR2(10), old_client_data VARCHAR2(4000), op_date DATE); CREATE OR REPLACE TRIGGER
trg_clientmstr_audit AFTER INSERT OR UPDATE OR DELETE ON clientmstr FOR EACH ROW BEGIN IF DELETING THEN
INSERT INTO audit_trade VALUES('DELETE', :OLD.client_info, SYSDATE); ELSIF UPDATING THEN INSERT INTO
audit_trade VALUES('UPDATE', :OLD.client_info, SYSDATE); END IF; END; 18(b) BEFORE INSERT OR UPDATE trigger
to reject salary < 50000 and store attempted entries: CREATE TABLE Tracking (e_no INT, salary DECIMAL(12,2));
CREATE OR REPLACE TRIGGER trg_salary_check BEFORE INSERT OR UPDATE ON Emp FOR EACH ROW BEGIN IF :[Link]
< 50000 THEN INSERT INTO Tracking (e_no, salary) VALUES (:NEW.e_no, :[Link]);
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be less than 50000'); END IF; END;

Question 19: MongoDB: Create DB BSIOTR with Teachers & Students - sample commands and queries

MongoDB shell / Compass style commands (examples): use BSIOTR; [Link]('Teachers');

[Link]('Students'); [Link]([ {Tname:'Raj', dno:1, dname:'COMP', experience:8,
salary:30000, date_of_joining: ISODate('2015-06-01')}, {Tname:'Priya', dno:2, dname:'IT', experience:5,
salary:25000, date_of_joining: ISODate('2018-08-15')} ]); [Link]([{Sname:'Alice', roll_no:2,
class:'A'},{Sname:'Bob', roll_no:3, class:'B'}]); Queries: 1) All teachers: [Link]().pretty(); 2)
Teachers of computer dept: [Link]({dname:'COMP'}); 3) Teachers of COMP,IT,E&TC:
[Link]({dname:{$in:['COMP','IT','E&TC']}}); 4) Those with salary >= 10000 and in those depts:
[Link]({dname:{$in:['COMP','IT','E&TC']}, salary:{$gte:10000}}); 5) Student with roll_no=2 or
Sname='xyz': [Link]({$or:[{roll_no:2},{Sname:'xyz'}]});

Question 26: MapReduce example: [Link] total population per state (MongoDB)

MapReduce (Mongo shell pseudocode): -- After importing [Link] into collection 'zips': var mapFunc =
function() { emit([Link], [Link]); }; var reduceFunc = function(key, values) { return [Link](values);
}; [Link](mapFunc, reduceFunc, { out: 'state_pop' }); -- Query result:
db.state_pop.find().sort({_id:1});

Question 27: Library MapReduce: classify books as Small (<250) or Big (>250) pages

MapReduce approach (MongoDB): [Link]( function() { emit([Link] < 250 ? 'Small' : 'Big', 1);
}, function(key, values) { return [Link](values); }, { out: 'books_size_counts' } ); -- Then:
db.books_size_counts.find();

----
Notes:
- Dates use SQL standard DATE 'YYYY-MM-DD'. For Oracle use TO_DATE('YYYY-MM-DD','YYYY-MM-DD') or appropriate
format.
- Replace sample data with real values as needed.
- This PDF contains concise canonical solutions; if you want full expanded answers for any specific question
(e.g., more sample rows), tell me which question number to expand.