DBMS Lab: SQL Answer Book

1. EMPLOYEE: ALTER, COPY, DELETE, DROP, TRIGGER

Create table and insert sample rows

CREATE TABLE Employee (
 S_No NUMBER PRIMARY KEY,
 Name VARCHAR2(40),
 Designation VARCHAR2(30),
 Branch VARCHAR2(30)
);
INSERT INTO Employee VALUES (1, 'Arun', 'Clerk', 'Chennai');
INSERT INTO Employee VALUES (2, 'Bala', 'Manager', 'Madurai');
INSERT INTO Employee VALUES (3, 'Devi', 'Analyst', 'Chennai');
COMMIT;

a) Add salary column

ALTER TABLE Employee ADD Salary NUMBER(10,2);

b) Copy table as Emp (structure and current rows)

CREATE TABLE Emp AS SELECT * FROM Employee;

c) Delete the second row (using its key, not physical position)

DELETE FROM Employee WHERE S_No = 2;
COMMIT;

d) Drop table

DROP TABLE Employee;

e) Trigger: automatically record updates

CREATE TABLE Employee_Audit (
 S_No NUMBER, Old_Salary NUMBER(10,2),
 New_Salary NUMBER(10,2), Changed_On DATE
);
-- Recreate Employee before running this trigger if it was dropped.
CREATE OR REPLACE TRIGGER trg_emp_salary_audit
AFTER UPDATE OF Salary ON Employee
FOR EACH ROW
BEGIN
 INSERT INTO Employee_Audit
 VALUES (:OLD.S_No, :OLD.Salary, :NEW.Salary, SYSDATE);
END;
/

A table has no guaranteed "second row" unless you define an ordering. Use a key such as S_No = 2.

2. EMPLOYEE: CONSTRAINTS, DDL/DML, SALARY HIKE

CREATE TABLE Employee (
 Emp_ID NUMBER PRIMARY KEY,
 Emp_Name VARCHAR2(40) NOT NULL,
 Email VARCHAR2(80) UNIQUE,
 Age NUMBER CHECK (Age >= 18),
 Department VARCHAR2(30),
 Salary NUMBER(10,2) CHECK (Salary >= 0)
);
INSERT INTO Employee VALUES (101, 'Arun', 'arun@mail.com', 22, 'Production', 25000);
INSERT INTO Employee VALUES (102, 'Bala', 'bala@mail.com', 24, 'Sales', 30000);
INSERT INTO Employee VALUES (103, 'Devi', 'devi@mail.com', 23, 'Production', 28000);
UPDATE Employee SET Salary = 32000 WHERE Emp_ID = 102;
DELETE FROM Employee WHERE Emp_ID = 103;
COMMIT;

PL/SQL: hike Production department salary by 10%

BEGIN
 UPDATE Employee
 SET Salary = Salary * 1.10
 WHERE Department = 'Production';
 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employee(s) updated.');
 COMMIT;
END;
/

10% is an example because the question does not specify the hike percentage.

3. EMPLOYEE: GROUP BY, LOWEST PAID, SORT, RENAME, INSERT TRIGGER

CREATE TABLE Employee (
 Emp_no NUMBER PRIMARY KEY,
 Emp_name VARCHAR2(40),
 Emp_dept VARCHAR2(30),
 Job VARCHAR2(30),
 Mgr NUMBER,
 Sal NUMBER(10,2)
);
INSERT INTO Employee VALUES (1, 'Arun', 'xxx', 'Clerk', 10, 20000);
INSERT INTO Employee VALUES (2, 'Bala', 'Sales', 'Salesman', 10, 25000);
INSERT INTO Employee VALUES (3, 'Charu', 'xxx', 'Analyst', 10, 18000);
COMMIT;

a) Employee names and salary for department xxx using GROUP BY

SELECT Emp_name, Sal
FROM Employee
WHERE Emp_dept = 'xxx'
GROUP BY Emp_name, Sal;

b) Lowest-paid employee details in each department

SELECT e.*
FROM Employee e
WHERE e.Sal = (
 SELECT MIN(e2.Sal)
 FROM Employee e2
 WHERE e2.Emp_dept = e.Emp_dept
);

c) Names in descending order

SELECT Emp_name FROM Employee ORDER BY Emp_name DESC;

d) Rename a column

ALTER TABLE Employee RENAME COLUMN Sal TO Salary;

e) Trigger: log inserted employee rows

CREATE TABLE Employee_Insert_Log (
 Emp_no NUMBER, Emp_name VARCHAR2(40), Inserted_On DATE
);
CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
 INSERT INTO Employee_Insert_Log
 VALUES (:NEW.Emp_no, :NEW.Emp_name, SYSDATE);
END;
/

4. STUDENT DATABASE: FOREIGN KEY, REFERENTIAL INTEGRITY, TRIGGERS

CREATE TABLE Student (
 RegNo NUMBER PRIMARY KEY,
 StudName VARCHAR2(40) NOT NULL,
 Dept VARCHAR2(30)
);
CREATE TABLE Student_Course (
 Course_ID NUMBER PRIMARY KEY,
 Course_Name VARCHAR2(50) NOT NULL
);
CREATE TABLE Student_Enrollment (
 RegNo NUMBER,
 Course_ID NUMBER,
 Enroll_Date DATE DEFAULT SYSDATE,
 CONSTRAINT pk_enrollment PRIMARY KEY (RegNo, Course_ID),
 CONSTRAINT fk_enroll_student FOREIGN KEY (RegNo)
 REFERENCES Student(RegNo),
 CONSTRAINT fk_enroll_course FOREIGN KEY (Course_ID)
 REFERENCES Student_Course(Course_ID)
);
INSERT INTO Student VALUES (1, 'Anu', 'ADS');
INSERT INTO Student_Course VALUES (10, 'DBMS');
INSERT INTO Student_Enrollment (RegNo, Course_ID) VALUES (1, 10);
COMMIT;

Triggers for insertion and deletion audit

CREATE TABLE Student_Audit (
 RegNo NUMBER, Action_Name VARCHAR2(10), Action_Date DATE
);
CREATE OR REPLACE TRIGGER trg_student_insert
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
 INSERT INTO Student_Audit VALUES (:NEW.RegNo, 'INSERT', SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER trg_student_delete
AFTER DELETE ON Student
FOR EACH ROW
BEGIN
 INSERT INTO Student_Audit VALUES (:OLD.RegNo, 'DELETE', SYSDATE);
END;
/

Foreign keys prevent an enrollment from referring to a student/course that does not exist.

5. DEPARTMENTS & EMPLOYEES: GRANT, REVOKE, SAVEPOINT, PROCEDURE

CREATE TABLE Departments (
 Dept_No NUMBER PRIMARY KEY,
 Dept_Name VARCHAR2(40),
 Dept_Location VARCHAR2(40)
);
CREATE TABLE Employees (
 Emp_ID NUMBER PRIMARY KEY,
 Emp_Name VARCHAR2(40),
 Emp_Salary NUMBER(10,2),
 Dept_No NUMBER REFERENCES Departments(Dept_No)
);

a) Grant selected privileges on Employees to user DEPTUSER

GRANT SELECT, INSERT ON Employees TO DEPTUSER;

b) Revoke all object privileges granted on Employees from DEPTUSER

REVOKE ALL ON Employees FROM DEPTUSER;

c) Revoke only INSERT privilege

REVOKE INSERT ON Employees FROM DEPTUSER;

d) SAVEPOINT example

UPDATE Employees SET Emp_Salary = Emp_Salary + 1000 WHERE Dept_No = 10;
SAVEPOINT salary_update;
DELETE FROM Employees WHERE Emp_ID = 105;
ROLLBACK TO salary_update;
COMMIT;

e) User-defined procedure to increase salary in a department

CREATE OR REPLACE PROCEDURE hike_dept_salary (
 p_dept_no IN NUMBER,
 p_percent IN NUMBER
) AS
BEGIN
 UPDATE Employees
 SET Emp_Salary = Emp_Salary + (Emp_Salary * p_percent / 100)
 WHERE Dept_No = p_dept_no;
END;
/
BEGIN
 hike_dept_salary(10, 10);
 COMMIT;
END;
/

Replace DEPTUSER with an existing database username. GRANT/REVOKE require suitable privileges.

6. EVENT, PARTICIPANT, PRIZES, WINNERS

CREATE TABLE Event (
 EventID NUMBER PRIMARY KEY,
 Name VARCHAR2(60) NOT NULL,
 Description VARCHAR2(200),
 City VARCHAR2(40)
);
CREATE TABLE Participant (
 PlayerID VARCHAR2(20) PRIMARY KEY,
 Name VARCHAR2(60),
 EventID NUMBER REFERENCES Event(EventID),
 Gender VARCHAR2(10) CHECK (Gender IN ('Male','Female')),
 Year NUMBER
);
CREATE TABLE Prizes (
 PrizeID NUMBER PRIMARY KEY,
 Prize_Money NUMBER(10,2),
 EventID NUMBER REFERENCES Event(EventID),
 Rank NUMBER CHECK (Rank IN (1,2,3)),
 Year NUMBER,
 CONSTRAINT uq_event_rank_year UNIQUE (EventID, Rank, Year)
);
CREATE TABLE Winners (
 PrizeID NUMBER REFERENCES Prizes(PrizeID),
 PlayerID VARCHAR2(20) REFERENCES Participant(PlayerID),
 PRIMARY KEY (PrizeID, PlayerID)
);

b) PlayerID must contain at least one digit

ALTER TABLE Participant ADD CONSTRAINT chk_playerid_digit
CHECK (REGEXP_LIKE(PlayerID, '[0-9]'));

c) Event names where all prize winners are female

SELECT e.Name
FROM Event e
WHERE EXISTS (
 SELECT 1 FROM Prizes p
 JOIN Winners w ON w.PrizeID = p.PrizeID
 WHERE p.EventID = e.EventID
)
AND NOT EXISTS (
 SELECT 1
 FROM Prizes p
 JOIN Winners w ON w.PrizeID = p.PrizeID
 JOIN Participant pa ON pa.PlayerID = w.PlayerID
 WHERE p.EventID = e.EventID
 AND pa.Gender <> 'Female'
);

d) Non-updatable view: participants who won first prize with event name

CREATE OR REPLACE VIEW First_Prize_Winners AS
SELECT DISTINCT pa.Name AS Participant_Name, e.Name AS Event_Name
FROM Participant pa
JOIN Winners w ON w.PlayerID = pa.PlayerID
JOIN Prizes p ON p.PrizeID = w.PrizeID
JOIN Event e ON e.EventID = p.EventID
WHERE p.Rank = 1
WITH READ ONLY;

e) Trigger: create 3 prize rows for every new event

CREATE SEQUENCE prize_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trg_event_prizes
AFTER INSERT ON Event
FOR EACH ROW
BEGIN
 INSERT INTO Prizes VALUES (prize_seq.NEXTVAL, 1500, :NEW.EventID, 1, EXTRACT(YEAR FROM SYSDATE));
 INSERT INTO Prizes VALUES (prize_seq.NEXTVAL, 1000, :NEW.EventID, 2, EXTRACT(YEAR FROM SYSDATE));
 INSERT INTO Prizes VALUES (prize_seq.NEXTVAL, 500, :NEW.EventID, 3, EXTRACT(YEAR FROM SYSDATE));
END;
/

The trigger uses a sequence for unique PrizeID values and the current year for the prize year.

7. MOVIE DATABASE: JOINS, VIEW, FUNCTION

CREATE TABLE Actor (
 Act_ID NUMBER PRIMARY KEY, Act_Name VARCHAR2(50), Act_Gender VARCHAR2(10)
);
CREATE TABLE Director (
 Dir_ID NUMBER PRIMARY KEY, Dir_Name VARCHAR2(50), Dir_Phone VARCHAR2(20)
);
CREATE TABLE Movies (
 Mov_ID NUMBER PRIMARY KEY, Mov_Title VARCHAR2(100), Mov_Year NUMBER,
 Mov_Lang VARCHAR2(30), Dir_ID NUMBER REFERENCES Director(Dir_ID)
);
CREATE TABLE Movie_Cast (
 Act_ID NUMBER REFERENCES Actor(Act_ID),
 Mov_ID NUMBER REFERENCES Movies(Mov_ID),
 Role VARCHAR2(40),
 PRIMARY KEY (Act_ID, Mov_ID)
);

a) Titles directed by director XXXX

SELECT m.Mov_Title
FROM Movies m JOIN Director d ON d.Dir_ID = m.Dir_ID
WHERE d.Dir_Name = 'XXXX';

b) Movie names in which an actor who acted in 2+ movies appears

SELECT DISTINCT m.Mov_Title
FROM Movies m
JOIN Movie_Cast mc ON mc.Mov_ID = m.Mov_ID
WHERE mc.Act_ID IN (
 SELECT Act_ID FROM Movie_Cast
 GROUP BY Act_ID HAVING COUNT(DISTINCT Mov_ID) >= 2
);

c) Actors who acted before 2010 and after 2015

SELECT DISTINCT a.Act_Name
FROM Actor a
JOIN Movie_Cast mc1 ON mc1.Act_ID = a.Act_ID
JOIN Movies m1 ON m1.Mov_ID = mc1.Mov_ID
JOIN Movie_Cast mc2 ON mc2.Act_ID = a.Act_ID
JOIN Movies m2 ON m2.Mov_ID = mc2.Mov_ID
WHERE m1.Mov_Year < 2010 AND m2.Mov_Year > 2015;

d) View of a particular actor and their movies/directors

CREATE OR REPLACE VIEW Actor_Movie_Director AS
SELECT a.Act_Name, m.Mov_Title, d.Dir_Name
FROM Actor a
JOIN Movie_Cast mc ON mc.Act_ID = a.Act_ID
JOIN Movies m ON m.Mov_ID = mc.Mov_ID
JOIN Director d ON d.Dir_ID = m.Dir_ID;
SELECT * FROM Actor_Movie_Director WHERE Act_Name = 'XXXX';

e) User-defined function: count movies for an actor

CREATE OR REPLACE FUNCTION actor_movie_count(p_act_id NUMBER)
RETURN NUMBER IS
 v_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO v_count
 FROM Movie_Cast WHERE Act_ID = p_act_id;
 RETURN v_count;
END;
/
SELECT actor_movie_count(1) AS Movie_Count FROM DUAL;

8. COLLEGE DATABASE: GENDER COUNTS, FINAL MARK, CATEGORY, VIEW, PROCEDURE

CREATE TABLE Student (
 RegNo NUMBER PRIMARY KEY, StudName VARCHAR2(50),
 Address VARCHAR2(100), Phone VARCHAR2(20), Gender VARCHAR2(10)
);
CREATE TABLE Subject (
 Subcode VARCHAR2(10) PRIMARY KEY, Title VARCHAR2(60),
 Sem NUMBER, Credits NUMBER
);
CREATE TABLE Marks (
 RegNo NUMBER REFERENCES Student(RegNo),
 Subcode VARCHAR2(10) REFERENCES Subject(Subcode),
 Test1 NUMBER, Test2 NUMBER, Test3 NUMBER, Finalmark NUMBER,
 PRIMARY KEY (RegNo, Subcode)
);

a) Count male/female students in each semester and section

The supplied STUDENT schema has no Semester or Section columns. If your lab data stores them, add those columns or join a section/semester table. With semester stored in SUBJECT, count students enrolled in each semester as follows:

SELECT su.Sem, st.Gender, COUNT(DISTINCT st.RegNo) AS Student_Count
FROM Student st
JOIN Marks m ON m.RegNo = st.RegNo
JOIN Subject su ON su.Subcode = m.Subcode
GROUP BY su.Sem, st.Gender
ORDER BY su.Sem, st.Gender;

b) Finalmark = average of the best two test marks

UPDATE Marks
SET Finalmark = (Test1 + Test2 + Test3
 - LEAST(Test1, Test2, Test3)) / 2;
COMMIT;

c) Categorize students

SELECT RegNo, Subcode, Finalmark,
 CASE
 WHEN Finalmark BETWEEN 81 AND 100 THEN 'Outstanding'
 WHEN Finalmark BETWEEN 51 AND 80 THEN 'Average'
 WHEN Finalmark < 51 THEN 'Weak'
 ELSE 'Not Available'
 END AS CAT
FROM Marks;

d) View of Test3 marks for a particular student in all subjects

CREATE OR REPLACE VIEW Student_Test3 AS
SELECT st.RegNo, st.StudName, su.Subcode, su.Title, m.Test3
FROM Student st
JOIN Marks m ON m.RegNo = st.RegNo
JOIN Subject su ON su.Subcode = m.Subcode;
SELECT * FROM Student_Test3 WHERE RegNo = 101;

e) Procedure to display marks for one student

CREATE OR REPLACE PROCEDURE show_student_marks(p_regno NUMBER) AS
BEGIN
 FOR r IN (
 SELECT su.Title, m.Test1, m.Test2, m.Test3, m.Finalmark
 FROM Marks m JOIN Subject su ON su.Subcode = m.Subcode
 WHERE m.RegNo = p_regno
 ) LOOP
 DBMS_OUTPUT.PUT_LINE(r.Title || ': ' || r.Test1 || ', ' ||
 r.Test2 || ', ' || r.Test3 || ', Final=' || r.Finalmark);
 END LOOP;
END;
/
SET SERVEROUTPUT ON;
EXEC show_student_marks(101);

If any test mark can be NULL, use NVL or add a rule that all three test marks must be entered before calculating Finalmark.

9. BANK: WHERE, COMPARISON, UPDATE, BETWEEN, TRIGGER

CREATE TABLE Bank (
 S_No NUMBER PRIMARY KEY, Cust_Name VARCHAR2(50),
 Acc_No NUMBER UNIQUE, Balance NUMBER(12,2), Branch VARCHAR2(40)
);
INSERT INTO Bank VALUES (1, 'Arun', 5001, 5000, 'Chennai');
INSERT INTO Bank VALUES (2, 'Bala', 5002, 2500, 'Madurai');
INSERT INTO Bank VALUES (3, 'Devi', 5003, 800, 'Chennai');
COMMIT;

a) SELECT with WHERE

SELECT * FROM Bank WHERE Branch = 'Chennai';

b) Comparison operator

SELECT * FROM Bank WHERE Balance >= 1000;

c) Update balance in second row

UPDATE Bank SET Balance = 3000 WHERE S_No = 2;
COMMIT;

d) Balance BETWEEN values

SELECT * FROM Bank WHERE Balance BETWEEN 1000 AND 5000;

e) Trigger when balance falls below 1000

CREATE OR REPLACE TRIGGER trg_low_bank_balance
AFTER INSERT OR UPDATE OF Balance ON Bank
FOR EACH ROW
WHEN (NEW.Balance < 1000)
BEGIN
 DBMS_OUTPUT.PUT_LINE('Warning: account balance is below 1000.');
END;
/

DBMS_OUTPUT from triggers may not be shown in all clients. In a real system, insert a warning into an alert table instead.

10. ACCOUNT: SELECT, ALTER, PROCEDURE

CREATE TABLE Account (
 Account_No NUMBER PRIMARY KEY,
 Cust_Name VARCHAR2(50),
 Branch_Name VARCHAR2(40),
 Account_Balance NUMBER(12,2),
 Account_Type VARCHAR2(20)
);
INSERT INTO Account VALUES (1001, 'Arun', 'Chennai', 15000, 'Savings');
INSERT INTO Account VALUES (1002, 'Bala', 'Madurai', 750, 'Current');
INSERT INTO Account VALUES (1003, 'Devi', 'Chennai', 9000, 'Savings');
COMMIT;

a) Customers from branch XXXXX

SELECT Cust_Name, Account_No FROM Account WHERE Branch_Name = 'XXXXX';

b) Balance greater than 10,000

SELECT Cust_Name, Account_Type FROM Account WHERE Account_Balance > 10000;

c) Add date of birth

ALTER TABLE Account ADD Cust_Date_of_Birth DATE;

d) Balance less than 1,000

SELECT Account_No, Cust_Name, Branch_Name
FROM Account
WHERE Account_Balance < 1000;

e) Procedure to display account details by branch

CREATE OR REPLACE PROCEDURE show_accounts(p_branch VARCHAR2) AS
BEGIN
 FOR r IN (
 SELECT Account_No, Cust_Name, Account_Balance, Account_Type
 FROM Account WHERE Branch_Name = p_branch
 ) LOOP
 DBMS_OUTPUT.PUT_LINE(r.Account_No || ' ' || r.Cust_Name ||
 ' Balance=' || r.Account_Balance || ' Type=' || r.Account_Type);
 END LOOP;
END;
/
SET SERVEROUTPUT ON;
EXEC show_accounts('Chennai');

11. CUSTOMER & ORDERS: JOIN, FILTER, ALTER, FUNCTION

CREATE TABLE Customer (
 C_ID NUMBER PRIMARY KEY, Name VARCHAR2(50),
 Address VARCHAR2(100), City VARCHAR2(40), Mobile_No VARCHAR2(20)
);
CREATE TABLE Orders (
 C_ID NUMBER REFERENCES Customer(C_ID),
 P_ID NUMBER, P_Name VARCHAR2(60), P_Cost NUMBER(10,2),
 PRIMARY KEY (C_ID, P_ID)
);
INSERT INTO Customer VALUES (1, 'Anu', '12 Main Road', 'Delhi', '9876543210');
INSERT INTO Customer VALUES (2, 'Ravi', '4 Lake Road', 'Chennai', '9876501234');
INSERT INTO Orders VALUES (1, 11, 'Keyboard', 1200);
INSERT INTO Orders VALUES (2, 12, 'Mouse', 600);
COMMIT;

a) Customer names and addresses who ordered products costing > 500

SELECT DISTINCT c.Name, c.Address
FROM Customer c JOIN Orders o ON o.C_ID = c.C_ID
WHERE o.P_Cost > 500;

b) Products costing 1,000 or more

SELECT P_Name FROM Orders WHERE P_Cost >= 1000;

c) Products ordered by customers in Delhi

SELECT DISTINCT o.P_Name
FROM Orders o JOIN Customer c ON c.C_ID = o.C_ID
WHERE c.City = 'Delhi';

d) Add Email_id

ALTER TABLE Customer ADD Email_id VARCHAR2(100);

e) Function: return total amount spent by a customer

CREATE OR REPLACE FUNCTION customer_total(p_c_id NUMBER)
RETURN NUMBER IS
 v_total NUMBER;
BEGIN
 SELECT NVL(SUM(P_Cost), 0) INTO v_total
 FROM Orders WHERE C_ID = p_c_id;
 RETURN v_total;
END;
/
SELECT customer_total(1) AS Total_Spent FROM DUAL;

12. SALESMAN, CUSTOMER, ORDERS: GROUP BY, UNION, VIEW, DELETE, TRIGGER

CREATE TABLE Salesman (
 Salesman_ID NUMBER PRIMARY KEY, Name VARCHAR2(50),
 City VARCHAR2(40), Commission NUMBER(5,2)
);
CREATE TABLE Customer (
 Customer_ID NUMBER PRIMARY KEY, Cust_Name VARCHAR2(50),
 City VARCHAR2(40), Grade NUMBER,
 Salesman_ID NUMBER REFERENCES Salesman(Salesman_ID)
);
CREATE TABLE Orders (
 Ord_No NUMBER PRIMARY KEY, Purchase_Amt NUMBER(12,2),
 Ord_Date DATE, Customer_ID NUMBER REFERENCES Customer(Customer_ID),
 Salesman_ID NUMBER REFERENCES Salesman(Salesman_ID)
);

a) Salesmen who have more than one customer

SELECT s.Name, s.Salesman_ID
FROM Salesman s JOIN Customer c ON c.Salesman_ID = s.Salesman_ID
GROUP BY s.Name, s.Salesman_ID
HAVING COUNT(c.Customer_ID) > 1;

b) Salesmen who do and do not have customers in their own cities (UNION)

SELECT s.Salesman_ID, s.Name, 'Has customer in same city' AS Status
FROM Salesman s
WHERE EXISTS (
 SELECT 1 FROM Customer c
 WHERE c.Salesman_ID = s.Salesman_ID AND c.City = s.City
)
UNION
SELECT s.Salesman_ID, s.Name, 'No customer in same city' AS Status
FROM Salesman s
WHERE NOT EXISTS (
 SELECT 1 FROM Customer c
 WHERE c.Salesman_ID = s.Salesman_ID AND c.City = s.City
);

c) View: salesman with the highest order on each day

CREATE OR REPLACE VIEW Daily_Highest_Order_Salesman AS
SELECT s.Salesman_ID, s.Name, o.Ord_Date, o.Purchase_Amt
FROM Salesman s
JOIN Orders o ON o.Salesman_ID = s.Salesman_ID
WHERE o.Purchase_Amt = (
 SELECT MAX(o2.Purchase_Amt) FROM Orders o2
 WHERE TRUNC(o2.Ord_Date) = TRUNC(o.Ord_Date)
);

d) Delete salesman 1000 and all his orders

-- Delete dependent orders first to satisfy foreign keys.
DELETE FROM Orders WHERE Salesman_ID = 1000;
-- If customers assigned to salesman 1000 exist, reassign/delete them first.
DELETE FROM Customer WHERE Salesman_ID = 1000;
DELETE FROM Salesman WHERE Salesman_ID = 1000;
COMMIT;

e) Trigger example: audit new orders

CREATE TABLE Order_Audit (
 Ord_No NUMBER, Action_Name VARCHAR2(10), Action_Date DATE
);
CREATE OR REPLACE TRIGGER trg_order_insert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
 INSERT INTO Order_Audit VALUES (:NEW.Ord_No, 'INSERT', SYSDATE);
END;
/

13. EMPLOYEE TRIGGERS + STUDENT/COURSE JOINS

a) Employee table with insert, delete, update audit triggers

CREATE TABLE Employee (
 Emp_ID NUMBER PRIMARY KEY, Emp_Name VARCHAR2(50), Salary NUMBER(10,2)
);
CREATE TABLE Employee_Audit (
 Emp_ID NUMBER, Action_Name VARCHAR2(10), Action_Date DATE
);
CREATE OR REPLACE TRIGGER trg_employee_insert
AFTER INSERT ON Employee FOR EACH ROW
BEGIN
 INSERT INTO Employee_Audit VALUES (:NEW.Emp_ID, 'INSERT', SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER trg_employee_delete
AFTER DELETE ON Employee FOR EACH ROW
BEGIN
 INSERT INTO Employee_Audit VALUES (:OLD.Emp_ID, 'DELETE', SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER trg_employee_update
AFTER UPDATE ON Employee FOR EACH ROW
BEGIN
 INSERT INTO Employee_Audit VALUES (:NEW.Emp_ID, 'UPDATE', SYSDATE);
END;
/

b) Create student and course tables, insert rows

CREATE TABLE Student (
 Student_ID NUMBER PRIMARY KEY, Student_Name VARCHAR2(50), Course_ID NUMBER
);
CREATE TABLE Course (
 Course_ID NUMBER PRIMARY KEY, Course_Name VARCHAR2(50)
);
ALTER TABLE Student ADD CONSTRAINT fk_student_course
FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID);
INSERT INTO Course VALUES (1, 'Data Science');
INSERT INTO Course VALUES (2, 'Cloud Computing');
INSERT INTO Student VALUES (101, 'Anu', 1);
INSERT INTO Student VALUES (102, 'Ravi', 2);
INSERT INTO Student VALUES (103, 'Devi', NULL);
COMMIT;

INNER JOIN

SELECT s.Student_ID, s.Student_Name, c.Course_Name
FROM Student s INNER JOIN Course c ON c.Course_ID = s.Course_ID;

LEFT JOIN

SELECT s.Student_ID, s.Student_Name, c.Course_Name
FROM Student s LEFT JOIN Course c ON c.Course_ID = s.Course_ID;

RIGHT JOIN

SELECT s.Student_ID, s.Student_Name, c.Course_Name
FROM Student s RIGHT JOIN Course c ON c.Course_ID = s.Course_ID;

FULL OUTER JOIN

SELECT s.Student_ID, s.Student_Name, c.Course_Name
FROM Student s FULL OUTER JOIN Course c ON c.Course_ID = s.Course_ID;

CROSS JOIN

SELECT s.Student_Name, c.Course_Name FROM Student s CROSS JOIN Course c;

14. SUPPLIER: FILTER, ALTER, ORDER, VIEW, PROCEDURE

CREATE TABLE Supplier (
 Sup_No NUMBER PRIMARY KEY, Sup_Name VARCHAR2(50),
 Item_Supplied VARCHAR2(60), Item_Price NUMBER(10,2),
 City VARCHAR2(40)
);
INSERT INTO Supplier VALUES (1, 'Sri Traders', 'Paper', 120, 'Chennai');
INSERT INTO Supplier VALUES (2, 'Metro Supplies', 'Pens', 80, 'Chennai');
INSERT INTO Supplier VALUES (3, 'Sakthi Stores', 'Files', 100, 'Trichy');
COMMIT;

a) Supplier names starting with S

SELECT Sup_No, Sup_Name FROM Supplier WHERE Sup_Name LIKE 'S%';

b) Add CONTACTNO

ALTER TABLE Supplier ADD ContactNo VARCHAR2(20);

c) Suppliers in Chennai, ascending item price

SELECT Sup_No, Sup_Name, Item_Price
FROM Supplier
WHERE City = 'Chennai'
ORDER BY Item_Price ASC;

d) View with supplier number and name only

CREATE OR REPLACE VIEW Supplier_Contacts AS
SELECT Sup_No, Sup_Name FROM Supplier;
SELECT * FROM Supplier_Contacts;

e) Procedure to display suppliers in a city

CREATE OR REPLACE PROCEDURE show_suppliers(p_city VARCHAR2) AS
BEGIN
 FOR r IN (
 SELECT Sup_No, Sup_Name, Item_Supplied, Item_Price
 FROM Supplier WHERE City = p_city
 ) LOOP
 DBMS_OUTPUT.PUT_LINE(r.Sup_No || ' ' || r.Sup_Name ||
 ' ' || r.Item_Supplied || ' ' || r.Item_Price);
 END LOOP;
END;
/
SET SERVEROUTPUT ON;
EXEC show_suppliers('Chennai');

15. GUI-BASED BANKING SYSTEM: DATABASE PART

This question asks for a GUI application, not just SQL. A simple practical design is to create the tables below, then connect them to a GUI using your prescribed language (for example, Java JDBC).

CREATE TABLE Bank_Customer (
 Customer_ID NUMBER PRIMARY KEY, Customer_Name VARCHAR2(50),
 Phone VARCHAR2(20)
);
CREATE TABLE Bank_Account (
 Account_No NUMBER PRIMARY KEY,
 Customer_ID NUMBER REFERENCES Bank_Customer(Customer_ID),
 Account_Type VARCHAR2(20),
 Balance NUMBER(12,2) CHECK (Balance >= 0)
);
CREATE TABLE Bank_Transaction (
 Transaction_ID NUMBER PRIMARY KEY,
 Account_No NUMBER REFERENCES Bank_Account(Account_No),
 Transaction_Type VARCHAR2(10) CHECK (Transaction_Type IN ('DEPOSIT','WITHDRAW')),
 Amount NUMBER(12,2) CHECK (Amount > 0),
 Transaction_Date DATE DEFAULT SYSDATE
);
CREATE SEQUENCE bank_txn_seq START WITH 1 INCREMENT BY 1;

Deposit

UPDATE Bank_Account SET Balance = Balance + 1000 WHERE Account_No = 1001;
INSERT INTO Bank_Transaction
VALUES (bank_txn_seq.NEXTVAL, 1001, 'DEPOSIT', 1000, SYSDATE);

Balance enquiry

SELECT * FROM Bank_Account WHERE Account_No = 1001;

For a real GUI, update the balance and insert the transaction in one database transaction; commit only if both succeed. Withdrawals must check sufficient balance.

16. GUI-BASED EMPLOYEE PAYROLL SYSTEM: DATABASE PART

CREATE TABLE Payroll_Employee (
 Emp_ID NUMBER PRIMARY KEY, Emp_Name VARCHAR2(50),
 Department VARCHAR2(40), Basic_Pay NUMBER(10,2) CHECK (Basic_Pay >= 0)
);
CREATE TABLE Payroll (
 Payroll_ID NUMBER PRIMARY KEY,
 Emp_ID NUMBER REFERENCES Payroll_Employee(Emp_ID),
 Pay_Month VARCHAR2(7),
 HRA NUMBER(10,2), Allowance NUMBER(10,2),
 Deduction NUMBER(10,2), Net_Salary NUMBER(12,2),
 CONSTRAINT uq_emp_month UNIQUE (Emp_ID, Pay_Month)
);

Calculate and store net salary

INSERT INTO Payroll
(Payroll_ID, Emp_ID, Pay_Month, HRA, Allowance, Deduction, Net_Salary)
SELECT 1, Emp_ID, '2026-10', Basic_Pay * 0.20,
 1500, 500, Basic_Pay + Basic_Pay * 0.20 + 1500 - 500
FROM Payroll_Employee WHERE Emp_ID = 101;

Display payroll

SELECT e.Emp_ID, e.Emp_Name, p.Pay_Month, p.Net_Salary
FROM Payroll_Employee e JOIN Payroll p ON p.Emp_ID = e.Emp_ID;

The GUI can provide employee entry, payroll calculation, salary slip display, and report screens. Adjust HRA/allowance/deduction formulas to your lab specification.

17. XML DATABASE FOR ONLINE QUIZ: SAMPLE XML + RESULT QUERY

The paper does not specify an XML-capable database or schema. Oracle can store XML using XMLTYPE. This is a simple XML storage example; actual quiz scoring logic depends on the quiz format.

CREATE TABLE Quiz_XML (
 Quiz_ID NUMBER PRIMARY KEY,
 Quiz_Data XMLTYPE
);
INSERT INTO Quiz_XML VALUES (
 1,
 XMLTYPE('<quiz>
 <question id="1">
 <text>SQL stands for?</text>
 <option>A</option><option>B</option>
 <answer>B</answer>
 </question>
 </quiz>')
);
COMMIT;

Read question text and correct answer

SELECT q.Quiz_ID,
 x.question_text,
 x.correct_answer
FROM Quiz_XML q,
 XMLTABLE('/quiz/question'
 PASSING q.Quiz_Data
 COLUMNS
 question_text VARCHAR2(200) PATH 'text',
 correct_answer VARCHAR2(10) PATH 'answer'
 ) x;

Simple result table for quiz attempts

CREATE TABLE Quiz_Result (
 Attempt_ID NUMBER PRIMARY KEY,
 Student_Name VARCHAR2(50),
 Score NUMBER,
 Total_Marks NUMBER,
 Attempt_Date DATE DEFAULT SYSDATE
);
INSERT INTO Quiz_Result VALUES (1, 'Anu', 8, 10, SYSDATE);
SELECT Student_Name, Score, Total_Marks,
 ROUND(Score * 100 / Total_Marks, 2) AS Percentage
FROM Quiz_Result;

LAST-MINUTE SQL REVISION

-- Basic filtering
SELECT * FROM table_name WHERE condition;
-- Sort
SELECT * FROM table_name ORDER BY column_name ASC;
-- Group and filter groups
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
-- Join
SELECT a.col, b.col
FROM A a JOIN B b ON a.id = b.id;
-- Add a column / rename a column
ALTER TABLE table_name ADD new_column VARCHAR2(30);
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
-- View
CREATE OR REPLACE VIEW view_name AS SELECT ...;
-- Procedure call
EXEC procedure_name(arguments);
-- Function call in SQL
SELECT function_name(arguments) FROM DUAL;
-- Trigger pseudorecords
:NEW.column -- new row values
:OLD.column -- old row values
-- Transaction commands
COMMIT;
ROLLBACK;
SAVEPOINT save_name;
ROLLBACK TO save_name;

Exam tip: First write CREATE TABLE with primary/foreign keys, then write each sub-question query. Check spelling of table/column names. If a trigger/procedure gives an error, compile it and inspect errors before re-running.