✅ SET 1
**Q1. Create the tables shown in the following schema. Add
appropriate constraints.
Emp (Empno, name, designation, salary, dept_no)
Dept (dept_no, dept_name, manager_no)
Insert 2 sample tuples into the above tables.**
Answer:
CREATE TABLE Dept (
dept_no NUMBER PRIMARY KEY,
dept_name VARCHAR2(20) NOT NULL,
manager_no NUMBER
);
CREATE TABLE Emp (
empno NUMBER PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
designation VARCHAR2(20),
salary NUMBER CHECK (salary > 0),
dept_no NUMBER,
CONSTRAINT fk_dept FOREIGN KEY (dept_no) REFERENCES Dept(dept_no)
);
INSERT INTO Dept VALUES (10,'HR',101);
INSERT INTO Dept VALUES (20,'IT',102);
INSERT INTO Emp VALUES (1,'Amit','Manager',8000,10);
INSERT INTO Emp VALUES (2,'Ravi','Clerk',4000,10);
Q2. Write an update statement that violates above included NOT
NULL constraint.
Answer:
UPDATE Emp SET name = NULL WHERE empno = 1;
Q3. Write a query to find the name of the manager and number of
his/her subordinates.
Answer:
SELECT d.manager_no, COUNT([Link]) AS subordinates
FROM Dept d LEFT JOIN Emp e
ON d.dept_no = e.dept_no
GROUP BY d.manager_no;
Q4. Write a query to display the department name which does not
contain any employees.
Answer:
SELECT d.dept_name
FROM Dept d LEFT JOIN Emp e
ON d.dept_no = e.dept_no
WHERE [Link] IS NULL;
Q5. Write a query which will return the short name of the month
(eg. JAN), for any date entered in the format: [Link].
Answer:
SELECT TO_CHAR(TO_DATE('12.03.25','[Link]'),'MON')
FROM dual;
**Q6. Create a table employee with eno, ename, and basic_pay
attributes, insert 3 to 4 records and write a PL/SQL block to
calculate the Gross salary & Net salary for an employee for the
following conditions:
➢ HRA is 15% of basic.
➢ DA is 62% of basic.
➢ PF is 780/- if gross salary exceeds 8000, otherwise 600/-.
➢ Professional tax is 2% of basic.
and then print the employee no, name, hra, da, pf, ptax, gross
salary & net salary for that employee.**
Answer:
CREATE TABLE employee (
eno NUMBER,
ename VARCHAR2(20),
basic_pay NUMBER
);
INSERT INTO employee VALUES (1,'Amit',5000);
INSERT INTO employee VALUES (2,'Ravi',7000);
INSERT INTO employee VALUES (3,'Sita',9000);
SET SERVEROUTPUT ON;
DECLARE
CURSOR c IS SELECT * FROM employee;
hra NUMBER;
da NUMBER;
pf NUMBER;
pt NUMBER;
gross NUMBER;
net NUMBER;
BEGIN
FOR rec IN c LOOP
hra := rec.basic_pay * 0.15;
da := rec.basic_pay * 0.62;
gross := rec.basic_pay + hra + da;
IF gross > 8000 THEN
pf := 780;
ELSE
pf := 600;
END IF;
pt := rec.basic_pay * 0.02;
net := gross - pf - pt;
DBMS_OUTPUT.PUT_LINE([Link]||' '||[Link]||
' HRA:'||hra||' DA:'||da||' PF:'||pf||
' PT:'||pt||' Gross:'||gross||' Net:'||net);
END LOOP;
END;
✅ SET 2
**Q1. Create the tables shown in the following schema. Add
appropriate constraints.
Supplier (Sup_no, name, City)
Part (part_no, part_name, Color)
Supplies(sup_no, part_no, qty)
Alter the supplies table by adding a new attribute
date_of_transaction.**
Answer:
CREATE TABLE Supplier (
Sup_no NUMBER PRIMARY KEY,
name VARCHAR2(20),
City VARCHAR2(20)
);
CREATE TABLE Part (
part_no NUMBER PRIMARY KEY,
part_name VARCHAR2(20),
Color VARCHAR2(10)
);
CREATE TABLE Supplies (
sup_no NUMBER,
part_no NUMBER,
qty NUMBER,
CONSTRAINT fk_sup FOREIGN KEY (sup_no) REFERENCES
Supplier(Sup_no),
CONSTRAINT fk_part FOREIGN KEY (part_no) REFERENCES Part(part_no)
);
ALTER TABLE Supplies ADD date_of_transaction DATE;
Q2. Display the name and job of employees whose name starts
with ‘a’.
Answer:
SELECT name, designation
FROM Emp
WHERE LOWER(name) LIKE 'a%';
Q3. Write a query to display the year and No. of Employees who
joined during that year.
Answer:
SELECT EXTRACT(YEAR FROM hiredate) AS year,
COUNT(*) AS no_of_employees
FROM Emp
GROUP BY EXTRACT(YEAR FROM hiredate);
Q4. Display the employee name and his/her total earnings (sal +
comm)
Answer:
SELECT name,salary + NVL(comm,0) AS total_earnings FROM Emp;
Q5. Write a query which will return the DAY of the week.(ie.
MONDAY), for any date entered in the format: [Link].
Answer:
SELECT TO_CHAR(TO_DATE('12.03.2025','[Link]'),'DAY')
FROM dual;
**Q6. Consider the following relation schemas
Inventory
Product_ID Product_name Quantity
Purchase_Record
Product_ID Status Pdate
Write a PL/SQL block to read the quantity of a product from
inventory and if it is > 0 reduce the quantity by 1 and record the
status of purchase of that product as ‘PURCHASED’. Otherwise
record the status of purchase of that product as ‘OUT OF STOCK’.
While recording the status of a purchase, record the date of
transaction.**
Answer:
SET SERVEROUTPUT ON;
DECLARE
v_qty NUMBER;
BEGIN
SELECT Quantity INTO v_qty
FROM Inventory
WHERE Product_ID = 1;
IF v_qty > 0 THEN
UPDATE Inventory
SET Quantity = Quantity - 1
WHERE Product_ID = 1;
INSERT INTO Purchase_Record
VALUES (1, 'PURCHASED', SYSDATE);
ELSE
INSERT INTO Purchase_Record
VALUES (1, 'OUT OF STOCK', SYSDATE);
END IF;
DBMS_OUTPUT.PUT_LINE('Transaction Done');
END;
✅ SET 3
**Q1. Create the tables shown in the following schema. Add
appropriate constraints.
Part (part_no, part_name, Color)
Project(proj_no, name, location, budget)
Supplies(proj_no, part_no, qty)
Insert 2 sample tuples into the above tables and show how to
delete a single tuple.**
Answer:
CREATE TABLE Part (
part_no NUMBER PRIMARY KEY,
part_name VARCHAR2(20),
Color VARCHAR2(10)
);
CREATE TABLE Project (
proj_no NUMBER PRIMARY KEY,
name VARCHAR2(20),
location VARCHAR2(20),
budget NUMBER
);
CREATE TABLE Supplies (
proj_no NUMBER,
part_no NUMBER,
qty NUMBER,
CONSTRAINT fk_proj FOREIGN KEY (proj_no) REFERENCES
Project(proj_no),
CONSTRAINT fk_part FOREIGN KEY (part_no) REFERENCES Part(part_no)
);
INSERT INTO Part VALUES (1,'Bolt','Red');
INSERT INTO Part VALUES (2,'Nut','Blue');
INSERT INTO Project VALUES (101,'Bridge','Hyd',50000);
INSERT INTO Project VALUES (102,'Road','Vij',30000);
INSERT INTO Supplies VALUES (101,1,50);
INSERT INTO Supplies VALUES (102,2,30);
DELETE FROM Part WHERE part_no = 2;
Q2. Display the name(s) of the employee(s) who does not have a
manager.
Answer:
SELECT name
FROM Emp
WHERE manager_no IS NULL;
Q3. Write a query to find out the year, where most people join in
the company displays the year and No. of Employees.
Answer:
SELECT EXTRACT(YEAR FROM hiredate) AS year,
COUNT(*) AS total
FROM Emp
GROUP BY EXTRACT(YEAR FROM hiredate)
HAVING COUNT(*) = (
SELECT MAX(COUNT(*))
FROM Emp
GROUP BY EXTRACT(YEAR FROM hiredate)
);
Q4. Write a query to find out the manager having Maximum
number of sub-ordinates.
Answer:
SELECT manager_no, COUNT(*) AS total FROM Emp
GROUP BY manager_no
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM Emp
GROUP BY manager_no);
Q5. Write a query which will return the name of the month (eg.
JUNE), for any date entered in the format: [Link].
Answer:
SELECT TO_CHAR(TO_DATE('12.03.25','[Link]'),'MONTH')
FROM dual;
**Q6. Write a PL/SQL block to handle the following built-in
exceptions:
➢ no_data_found
➢ too_many_rows
➢ zero_divide**
Answer:
SET SERVEROUTPUT ON;
DECLARE
v_sal NUMBER;
v_result NUMBER;
BEGIN
SELECT salary INTO v_sal FROM Emp WHERE empno = 999;
v_result := v_sal / 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Too Many Rows');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Divide by Zero');
END;
✅ SET 4
**Q1. Create tables along with constraints for the following
schema:
Inventory (Product_ID, name, Quantity)
Purchase_Record (Product_ID, Status, Date_of_purchase)
Load the purchase_record table with data using the insert
statement.**
Answer:
CREATE TABLE Inventory (
Product_ID NUMBER PRIMARY KEY,
name VARCHAR2(20),
Quantity NUMBER
);
CREATE TABLE Purchase_Record (
Product_ID NUMBER,
Status VARCHAR2(20),
Date_of_purchase DATE
);
INSERT INTO Purchase_Record VALUES (1,'PURCHASED',SYSDATE);
INSERT INTO Purchase_Record VALUES (2,'OUT OF STOCK',SYSDATE);
Q2. Retrieve the details of employees who have joined the
company during the month december.
Answer:
SELECT * FROM Emp WHERE TO_CHAR(hiredate,'MONTH') = 'DECEMBER';
Q3. For each designation, retrieve the name, number of
employees with that designation and their average salary.
Answer:
SELECT designation, COUNT(*), AVG(salary)
FROM Emp GROUP BY designation;
Q4. Write a query to find out the manager having Maximum
number of sub-ordinates.
Answer:
SELECT manager_no, COUNT(*) FROM Emp GROUP BY manager_no
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM Emp
GROUP BY manager_no);
Q5. Write an SQL statement to convert the current date to new
date picture ex: MONDAY 10th June 2005 10:30.00 PM
Answer:
SELECT TO_CHAR(SYSDATE,
'DAY DDth MONTH YYYY HH:MI:SS AM')
FROM dual;
**Q6. Write a PL/SQL block that computes increment of an
employee in employee table by using function which takes
employee number as argument, calculates increment and returns
the same based on the following criteria:
➢ If salary <= 1000 – increment = 40% of salary
➢ If salary > 1000 and <= 3000– increment = 30% of salary
➢ Else increment = 20% of salary.**
Answer:
CREATE OR REPLACE FUNCTION inc_fun(sal NUMBER)
RETURN NUMBER IS
BEGIN
IF sal <= 1000 THEN
RETURN sal * 0.4;
ELSIF sal <= 3000 THEN
RETURN sal * 0.3;
ELSE
RETURN sal * 0.2;
END IF;
END;
SET SERVEROUTPUT ON;
DECLARE
v_sal NUMBER;
v_inc NUMBER;
BEGIN
SELECT salary INTO v_sal FROM Emp WHERE empno = 1;
v_inc := inc_fun(v_sal);
DBMS_OUTPUT.PUT_LINE('Increment = ' || v_inc);
END;
✅ SET 5
**Q1. Create tables along with constraints for the following
schema.
Sailor(Sailor_ID, name, rating, age)
Boat(Boat_id, name, color)
Reserves(Sailor_ID, Boat_id, booking_day)
Modify the contents of boat table.**
Answer:
CREATE TABLE Sailor(
Sailor_ID NUMBER PRIMARY KEY,
name VARCHAR2(20),
rating NUMBER,
age NUMBER
);
CREATE TABLE Boat(
Boat_id NUMBER PRIMARY KEY,
name VARCHAR2(20),
color VARCHAR2(10)
);
CREATE TABLE Reserves(
Sailor_ID NUMBER,
Boat_id NUMBER,
booking_day DATE
);
UPDATE Boat SET color='Red' WHERE Boat_id=1
Q2. Display the department names in the lower case but the
initial must be in uppercase.
Answer:
SELECT INITCAP(LOWER(dept_name)) FROM Dept;
Q3. For each department having at least 2 employees working,
retrieve department name, average salary of the department.
Answer:
SELECT dept_no, AVG(salary) FROM Emp
GROUP BY dept_no HAVING COUNT(*) >= 2;
Q4. Write a query to find out the employees who have joined
before their managers.
Answer:
SELECT [Link] FROM Emp e1, Emp e2 WHERE e1.manager_no =
[Link]
AND [Link] < [Link];
Q5. For each department, Count the number of times ‘E’ occurs in
department names.
Answer:
SELECT dept_name,
LENGTH(dept_name) - LENGTH(REPLACE(dept_name,'E','')) AS count_E
FROM Dept;
Q6. Write a PL/SQL function which will accept two numbers and
return their GCD and LCM. The output should be stored in a table
called DEMO_TAB.
Answer:
CREATE TABLE DEMO_TAB(gcd NUMBER, lcm NUMBER);
CREATE OR REPLACE FUNCTION calc_gcd(a NUMBER,b NUMBER)
RETURN NUMBER IS
BEGIN
WHILE b!=0 LOOP
a := MOD(a,b);
a := a + b;
b := a - b;
a := a - b;
END LOOP;
RETURN a;
END;
DECLARE
g NUMBER;
l NUMBER;
BEGIN
g := calc_gcd(12,18);
l := (12*18)/g;
INSERT INTO DEMO_TAB VALUES (g,l);
END;
✅ SET 6
**Q1. Create tables without constraints for the following schema.
Bank_main(Acct_no, name, Accnt_type, balance)
Bank_trans (Acct_no, trans_type, date, amount)
Drop a table.**
Answer:
CREATE TABLE Bank_main(
Acct_no NUMBER,
name VARCHAR2(20),
Accnt_type VARCHAR2(10),
balance NUMBER
);
CREATE TABLE Bank_trans(
Acct_no NUMBER,
trans_type VARCHAR2(10),
date1 DATE,
amount NUMBER
);
DROP TABLE Bank_trans;
Q2. Select the names of instructors whose names are neither
“Mozart” nor “Einstein”.
Answer:
SELECT name FROM instructor
WHERE name NOT IN ('Mozart','Einstein');
Q3. For the student with ID 12345 (or any other value), show all
course_ids, titles of the courses registered for by the student and
the total number of credits for such courses (taken by that
student). Don't display the tot_creds value from the student
table, you should use SQL aggregation on courses taken by the
student.
Answer:
SELECT t.course_id, [Link], SUM([Link]) AS total_credits
FROM takes t, course c
WHERE t.course_id = c.course_id AND [Link] = 12345
GROUP BY t.course_id, [Link];
Q4. Display the IDs and names of the instructors who have taught
all Comp. Sci. courses.
Answer:
SELECT [Link], [Link] FROM instructor i
WHERE NOT EXISTS (SELECT course_id FROM course WHERE
dept_name='Comp. Sci.'
MINUS
SELECT course_id FROM teaches t WHERE [Link]=[Link]);
Q5. For each student, show tot_credits (Student_ID, year,
num_credits), giving the total number of credits taken by
students in each year.
Answer:
SELECT [Link], [Link], SUM([Link]) FROM takes t, course c
WHERE t.course_id = c.course_id GROUP BY [Link], [Link];
Q6. Write a PL/SQL block to check whether the quantity of any
product in the Inventory table is <0. If so, using an exception
display relevant message and update the quantity to 0.
Answer:
SET SERVEROUTPUT ON;
DECLARE
v_qty NUMBER;
BEGIN
SELECT quantity INTO v_qty FROM Inventory WHERE product_id=1;
IF v_qty < 0 THEN
UPDATE Inventory SET quantity = 0 WHERE product_id=1;
DBMS_OUTPUT.PUT_LINE('Quantity corrected to 0');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No product found');
END;
✅ SET 7
**Q1. Create tables without constraints for the following schema:
product(product_ID, Prod_name, Qty_in_stock, MRP)
order(order_ID, product_ID, Qty_ordered, Unit_Price)
Load the tables with data using the insert statement.**
Answer:
CREATE TABLE product(
product_ID NUMBER,
Prod_name VARCHAR2(20),
Qty_in_stock NUMBER,
MRP NUMBER
);
CREATE TABLE orders(
order_ID NUMBER,
product_ID NUMBER,
Qty_ordered NUMBER,
Unit_Price NUMBER
);
INSERT INTO product VALUES (1,'Pen',100,10);
INSERT INTO product VALUES (2,'Book',50,50);
INSERT INTO orders VALUES (101,1,10,10);
INSERT INTO orders VALUES (102,2,5,50);
Q2. Find all courses taught in the Fall 2009 semester but not in
the Spring 2010 semester.
Answer:
SELECT course_id FROM section
WHERE semester='Fall' AND year=2009
MINUS
SELECT course_id FROM section
WHERE semester='Spring' AND year=2010;
Q3. For each department, display the department name and the
maximum salary of instructors in that department.
Answer:
SELECT dept_name, MAX(salary)
FROM instructor
GROUP BY dept_name;
Q4. Find the average instructors’ salaries of those departments
where the average salary is greater than $42,000. (Write a
Nested Query)
Answer:
SELECT dept_name FROM instructor
GROUP BY dept_name HAVING AVG(salary) > 42000;
Q5. For each department, Count the number of times ‘A’ occurs in
department names.
Answer:
SELECT dept_name, LENGTH(dept_name) -
LENGTH(REPLACE(dept_name,'A','')) AS count_A FROM department;
Q6. Write a stored procedure, raise_salary which accepts an
employee number, increment and modifies salary of that
employee in the employee table. Modified salary = salary
increase amount+ current salary. If the employee number is not
found or if the current salary is null, it should raise an exception.
Otherwise, update the salary.
Answer:
CREATE OR REPLACE PROCEDURE raise_salary(eid NUMBER, inc
NUMBER)IS
v_sal NUMBER;
BEGIN
SELECT salary INTO v_sal FROM Emp WHERE empno=eid;
IF v_sal IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Salary is NULL');
END IF;
UPDATE Emp SET salary = salary + inc WHERE empno=eid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20002,'Employee not found');
END;
✅ SET 8
**Q1. Create the tables shown in the following schema. Add
appropriate constraints while creating the tables.
Emp (Empno, name, designation, salary, dept_no)
project(proj_no, proj_name)
works_on(Empno, proj_no, duration)**
Answer:
CREATE TABLE project(
proj_no NUMBER PRIMARY KEY,
proj_name VARCHAR2(20)
);
CREATE TABLE works_on(
Empno NUMBER,
proj_no NUMBER,
duration NUMBER
);
Q2. List the names of all instructors in the Physics department in
alphabetic order.
Answer:
SELECT name
FROM instructor
WHERE dept_name='Physics'
ORDER BY name;
Q3. Retrieve the names of all instructors, along with their
department names and department building name.
Answer:
SELECT [Link], d.dept_name, [Link]
FROM instructor i, department d
WHERE i.dept_name = d.dept_name;
Q4. Find the department name and average salary of the
department for only those departments where the average salary
of the instructors is less than $42,000.
Answer:
SELECT dept_name, AVG(salary) FROM instructor
GROUP BY dept_name HAVING AVG(salary) < 42000;
Q5. Find the departments with the maximum budget. (Write a
Nested Query)
Answer:
SELECT dept_name FROM department
WHERE budget = (SELECT MAX(budget) FROM department);
Q6. Write a PL/SQL block to delete records of all employees who
belong to a particular department and then record the dno, no of
rows deleted and date on which deletion occurred in the
Del_History table using implicit cursors.
Answer:
CREATE TABLE Del_History(
dno NUMBER,
rows_deleted NUMBER,
date1 DATE
);
DECLARE
v_count NUMBER;
BEGIN
DELETE FROM Emp WHERE dept_no = 10;
v_count := SQL%ROWCOUNT;
INSERT INTO Del_History VALUES (10, v_count, SYSDATE);
END;
✅ SET 9
**Q1. Create the tables shown in the following schema. Add
appropriate constraints while creating the tables.
Instructor(Instructor_ID, name, date_of_birth, Dept_name)
Department (Dept_name, location, budget)
Insert 2 sample tuples into the above tables.**
Answer:
CREATE TABLE Department (
Dept_name VARCHAR2(20) PRIMARY KEY,
location VARCHAR2(20),
budget NUMBER
);
CREATE TABLE Instructor (
Instructor_ID NUMBER PRIMARY KEY,
name VARCHAR2(20),
date_of_birth DATE,
Dept_name VARCHAR2(20),
CONSTRAINT fk_dept FOREIGN KEY (Dept_name) REFERENCES
Department(Dept_name)
);
INSERT INTO Department VALUES ('CSE','Block1',500000);
INSERT INTO Department VALUES ('ECE','Block2',400000);
INSERT INTO Instructor VALUES (1,'Ravi',TO_DATE('10-01-1990','DD-MM-
YYYY'),'CSE');
INSERT INTO Instructor VALUES (2,'Amit',TO_DATE('15-02-1992','DD-MM-
YYYY'),'ECE');
Q2. Find the names of instructors whose name starts with ‘B’ and
salary amounts between $90,000 and $100,000
Answer:
SELECT name
FROM instructor
WHERE name LIKE 'B%'
AND salary BETWEEN 90000 AND 100000;
Q3. Find the names of instructors who have taught at least one
course. (Query should not be nested)
Answer:
SELECT DISTINCT [Link]
FROM instructor i, teaches t
WHERE [Link] = [Link];
Q4. Find the number of instructors in each department who teach
a course in the Spring 2010 semester.
Answer:
SELECT i.dept_name, COUNT(*) FROM instructor i, teaches t
WHERE [Link] = [Link] AND [Link]='Spring'
AND [Link]=2010 GROUP BY i.dept_name;
Q5. For each instructor, Count the number of times ‘J’ occurs in
their names.
Answer:
SELECT name,
LENGTH(name) - LENGTH(REPLACE(name,'J','')) AS count_J
FROM instructor;
Q6. Write a stored procedure that displays the employee name
and their annual income from the Emp.
Answer:
CREATE OR REPLACE PROCEDURE annual_income
IS
BEGIN
FOR rec IN (SELECT name, salary FROM Emp) LOOP
DBMS_OUTPUT.PUT_LINE([Link] || ' ' || ([Link]*12));
END LOOP;
END;
✅ SET 10
**Q1. Create the tables shown in the following schema with
constraints.
Course(course_ID, course_name, prerequisite, Dept_name)
Department (Dept_name, location, budget)
Insert 2 sample tuples into the above tables.**
Answer:
CREATE TABLE Department (
Dept_name VARCHAR2(20) PRIMARY KEY,
location VARCHAR2(20),
budget NUMBER
);
CREATE TABLE Course (
course_ID NUMBER PRIMARY KEY,
course_name VARCHAR2(20),
prerequisite VARCHAR2(20),
Dept_name VARCHAR2(20),
CONSTRAINT fk_dept2 FOREIGN KEY (Dept_name) REFERENCES
Department(Dept_name)
);
INSERT INTO Department VALUES ('CSE','Block1',500000);
INSERT INTO Department VALUES ('ECE','Block2',400000);
INSERT INTO Course VALUES (1,'DBMS','None','CSE');
INSERT INTO Course VALUES (2,'Networks','DBMS','ECE');
Q2. Find the names of the instructors, their present salaries and
the resulting salaries if they were given a 10% raise.
Answer:
SELECT name, salary, salary*1.1 AS new_salary
FROM instructor;
Q3. Find instructor names and course identifiers for instructors in
the Computer Science department. (Query should not be nested)
Answer:
SELECT [Link], t.course_id FROM instructor i, teaches t
WHERE [Link] = [Link] AND i.dept_name='Comp. Sci.';
Q4. Display the name(s) of instructor(s) who have taught
maximum number of courses during 2010.
Answer:
SELECT id FROM teaches WHERE year=2010 GROUP BY id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM teaches
WHERE year=2010 GROUP BY id);
Q5. Write a query which will return the short name of the month
(eg. JAN), for any date entered in the format: [Link]
Answer:
SELECT TO_CHAR(TO_DATE('12.03.25','[Link]'),'MON')
FROM dual;
Q6. Write a PL/SQL function that accepts department number and
returns the average salary of the department.
Answer:
CREATE OR REPLACE FUNCTION avg_sal(dno VARCHAR2)
RETURN NUMBER IS
v_avg NUMBER;
BEGIN
SELECT AVG(salary) INTO v_avg FROM instructor
WHERE dept_name = dno;
RETURN v_avg;
END;
✅ SET 11
**Q1. Create the following table without adding constraints.
Book(Book_number, Title, Author, Price, release_date)
Change the datatype of any attribute in the table.**
Answer:
CREATE TABLE Book(
Book_number NUMBER,
Title VARCHAR2(20),
Author VARCHAR2(20),
Price NUMBER,
release_date DATE
);
ALTER TABLE Book MODIFY Price NUMBER(10,2);
Q2. Find the names of courses in Computer science department
which have 3 credits
Answer:
SELECT course_name
FROM course
WHERE dept_name='Comp. Sci.'
AND credits=3;
Q3. For all instructors in the university who have taught some
course, find their names and the course ID of all courses they
taught. (Query should not be nested)
Answer:
SELECT [Link], t.course_id
FROM instructor i, teaches t
WHERE [Link] = [Link];
Q4. Find the average salary of instructors in the Computer
Science department.
Answer:
SELECT AVG(salary)
FROM instructor
WHERE dept_name='Comp. Sci.';
Q5. Retrieve the department name that has the maximum number
of instructors working and the number of instructors.
Answer:
SELECT dept_name, COUNT(*)
FROM instructor
GROUP BY dept_name
HAVING COUNT(*) = (
SELECT MAX(COUNT(*))
FROM instructor
GROUP BY dept_name
);
Q6. Given the table EMPLOYEE (EmpNo, Name, Salary,
Designation, DeptID) write a cursor to display employees details.
Answer:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c IS SELECT * FROM Employee;
BEGIN
FOR rec IN c LOOP
DBMS_OUTPUT.PUT_LINE([Link] || ' ' || [Link] || ' ' || [Link]);
END LOOP;
END;
✅ SET 12
**Q1. Create the following table by adding applicable constraints.
Item( item_id, item_name, item_price, expiration_date,
quantity_in_hand)
Change the datatype of any attribute in the table.**
Answer:
CREATE TABLE Item(
item_id NUMBER PRIMARY KEY,
item_name VARCHAR2(20),
item_price NUMBER CHECK(item_price>0),
expiration_date DATE,
quantity_in_hand NUMBER
);
ALTER TABLE Item MODIFY item_price NUMBER(10,2);
Q2. Find the names of all instructors who earn more than the
lowest paid instructor in the Biology department. (Query should
not be nested)
Answer:
SELECT [Link]
FROM instructor i, instructor b
WHERE b.dept_name='Biology'
AND [Link] > [Link];
Q3. Find the total number of courses and credits offered by
Biology department.
Answer:
SELECT COUNT(*), SUM(credits)
FROM course WHERE dept_name='Biology';
Q4. Find the departments that have the highest average salary.
(Write a Nested Query)
Answer:
SELECT dept_name FROM instructor
GROUP BY dept_name HAVING AVG(salary) = (SELECT MAX(AVG(salary))
FROM instructor GROUP BY dept_name);
Q5. Write a query which will return the name of the month (eg.
JUNE), for any date entered in the format: [Link].
Answer:
SELECT TO_CHAR(TO_DATE('12.03.25','[Link]'),'MONTH')
FROM dual;
Q6. Create a database trigger that checks whether the new salary
of an employee is less than existing salary. If so, raise an
appropriate exception and avoid that update.
Answer:
CREATE OR REPLACE TRIGGER salary_check
BEFORE UPDATE ON Emp
FOR EACH ROW
BEGIN
IF :[Link] < :[Link] THEN
RAISE_APPLICATION_ERROR(-20001,'Salary cannot decrease');
END IF;
END;