Practical List

a.How would you create a new database named EMP?

Ans: CREATE DATABASE EMP;

b. Write the SQL commands to create the EMP table with the following structure:

• empno as a number datatype with up to 4 digits

• ename as a variable character datatype up to 10 characters

• job as a variable character up to 9 characters

• mgr as a number datatype with up to 4 digits

• hiredate as a date

• sal as a number with up to 7 digits, including 2 decimal places

• comm as a number with up to 7 digits, including 2 decimal places

• deptno as a number with up to 2 digits.

Ans:

CREATE TABLE EMP (

empno NUMBER(4), -- Employee number, max 4 digits

ename VARCHAR2(10), -- Employee name, max 10 characters

job VARCHAR2(9), -- Job title, max 9 characters

mgr NUMBER(4), -- Manager ID, max 4 digits

hiredate DATE, -- Hiring date

sal NUMBER(7, 2), -- Salary, max 7 digits with 2 decimal places

comm NUMBER(7, 2), -- Commission, max 7 digits with 2 decimal places

deptno NUMBER(2) -- Department number, max 2 digits

);

c.How would you alter the EMP table to assign the empno column as the primary

key.

Ans: ALTER TABLE EMP ADD PRIMARY KEY (empno);

Practical List

d. Write the SQL commands to create the DEPT table with the following structure:

 deptno as a number with up to 2 digits

 dname as a variable character datatype up to 10 characters
 loc as a variable character up to 20 characters write SQL queries for given question.

Ans:

CREATE TABLE DEPT (

deptno NUMBER(2), -- Department number, max 2 digits

dname VARCHAR2(10), -- Department name, max 10 characters

loc VARCHAR2(20) -- Location, max 20 characters

);
Practical List

Q.2 Write SQL Queries for following questions:

a. Create table for stud using attributes Rollno, Studname, Percentage. Apply primary key for rollno and
check constraint on percentage that the percentage should not be greater than 100.
Ans:

CREATE TABLE stud (

rollno NUMBER PRIMARY KEY, -- Roll number as Primary Key

studname VARCHAR2(50), -- Student name

percentage NUMBER(5, 2) CHECK (percentage <= 100) -- Percentage with max value 100

);

b. Change the stud table structure by adding column City.

Ans: ALTER TABLE stud ADD city VARCHAR2(50); -- Adding City column

c. Increase the size by 10 of studentname column.

Ans: ALTER TABLE stud MODIFY studname VARCHAR2(60); -- Increasing size from 50 to 60

Q.3 Consider the following schema Emp (empno,ename,job,mgr,hiredate,sal,comm,deptno).

a. Insert data into EMP table.

Ans: INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (101, 'SMITH', 'CLERK', 7902,, 800, NULL, 20);

INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (102, 'ADAMS', 'ANALYST', 77,, 1200, 300, 10);

INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (103, 'JONES', 'MANAGER',, 2975, NULL, 30);

b. Delete record of SMITH from the above table.

Ans: DELETE FROM EMP WHERE ename = 'SMITH';

Practical List

c. Change the job of ADAMS to MANAGER.

Ans: UPDATE EMP

SET job = 'MANAGER'

WHERE ename = 'ADAMS';

d. Display contents of empno and sal.

Ans: SELECT empno, sal FROM EMP;

Q.4 Write SQL Queries for following questions:

a. Write TCL command to save all the changes made so far in the EMP.

Ans: Commit;

b. Delete any one record in the EMP table created earlier and undo the deletion operation.

Ans: DELETE FROM EMP WHERE empno = 101;

ROLLBACK;

Q.5 Consider the following schema: Orders(cust_id, order_id, items, amount)

Write queries for the following:

a. Display new column named total_amount which is 200 added to the amount field.

Ans: SELECT cust_id, order_id, items, amount, (amount + 200) AS total_amount FROM Orders;

b. Display new column named offer_price which is 100 subtracted from the amount field.

Ans: SELECT cust_id, order_id, items, amount, (amount - 100) AS offer_price FROM Orders;

c. Display new column named revised_amount which is multiplied by 5 times the amount.

Ans: SELECT cust_id, order_id, items, amount, (amount * 5) AS revised_amount FROM Orders
Practical List

d. Display new column named half_amount which is divided by 2 to the amount field.

Ans: SELECT cust_id, order_id, items, amount, (amount * 5) AS revised_amount FROM Orders;

Q.6 Consider the following schema Emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)

Write queries for the following:

a. Display employees whose city is ‘Mumbai’ and earns more than 50000.

Ans: SELECT * FROM Emp WHERE city = 'Mumbai' AND sal > 50000;

b. Display employees who job is Clerk or commission is 500.

Ans: SELECT * FROM Emp WHERE job = 'Clerk' OR comm = 500;

c. Display details of employees whose salary is between 20000 and 50000.

Ans: SELECT * FROM Emp WHERE sal BETWEEN 20000 AND 50000;

d. Display details of employees who stays at Mumbai, Pune, Nashik or Nagpur.

Ans: SELECT * FROM Emp WHERE city IN ('Mumbai', 'Pune', 'Nashik', 'Nagpur');

Q.7 Consider following Schema :

emp1(empno,ename,deptno)

emp2(empno,ename,deptno)

Write SQL commands for the following statements.

a. Display the names of employees including duplicate employee names.

Ans: SELECT ename FROM emp1 UNION ALL SELECT ename FROM emp2;

b. Display the names of employees excluding duplicate employee names.

Ans: SELECT ename FROM emp1 UNION SELECT ename FROM emp2;

c. Display the common employee names from both the tables.

Ans: SELECT ename FROM emp1 INTERSECT SELECT ename FROM emp2;
Practical List

d. List employees who are not assigned to any department?

Ans: SELECT ename FROM emp1 WHERE deptno IS NULL UNION SELECT ename FROM emp2

WHERE deptno IS NULL;

Q.8 Consider the following schema :Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

a. Display the minimum, maximum, sum and average salary of all employees. Label the columns
Maximum, Minimum, Sum and Average respectively.

Ans : SELECT MIN(sal) AS Minimum, MAX(sal) AS Maximum, SUM(sal) AS Sum, AVG(sal) AS Average

FROM Emp;

c. Determine the number of managers without listing them. Label the column number of managers.

Ans: SELECT COUNT(DISTINCT mgr) AS "Number of Managers" FROM Emp WHERE mgr IS NOT NULL;

d. Write a query that will display the difference between the highest and lowest salaries. Label the
column DIFFERENCE.

Ans: SELECT MAX(sal) - MIN(sal) AS DIFFERENCE FROM Emp;

f. Display the number of employees in department 10 who earns a commission.

Ans: SELECT COUNT(*) AS "Number of Employees with Commission FROM Emp

WHERE deptno = 10 AND comm IS NOT NULL;

Q.9 Write output of the following queries.

a. Display minimum salary of employee from every department.

Ans: SELECT deptno, MIN(sal) AS Min_Salary FROM Emp GROUP BY deptno;

b. Display total salary of every department.

Ans: SELECT deptno, SUM(sal) AS Total_Salary FROM Emp GROUP BY deptno;

c. Display the department having total employees more than 5.

Ans: SELECT deptno FROM Emp GROUP BY deptno HAVING COUNT(empno) > 5;

d. Display details of employees with employee name in ascending order.

Ans: SELECT * FROM Emp ORDER BY ename ASC;

e. Display emp_no, dept_no from dept Group By deptname.

Practical List

Ans: SELECT empno, deptno FROM Emp GROUP BY deptno, empno;

Q.10 Write output of the following queries.

a. Display employee Nikhil’s employee number, name, department number, and department location.

Ans: SELECT e.empno, e.ename, e.deptno, d.location FROM Emp e JOIN Dept d ON e.deptno =
d.deptno WHERE e.ename = 'Nikhil';

b. Display the list of employees who work in the sales department.

Ans:
SELECT e.empno, e.ename, e.deptno
FROM Emp e
JOIN Dept d ON e.deptno = d.deptno
WHERE d.deptname = 'Sales';

c. Display the list of employees who do not work in the sales department.
Ans: SELECT e.empno, e.ename, e.deptno
FROM Emp e
JOIN Dept d ON e.deptno = d.deptno
WHERE d.deptname != 'Sales';

d. Display the employee names and salary of all employees who report to Sumit Patil.

Ans: SELECT e.ename, e.sal

FROM Emp e WHERE e.mgr = (SELECT empno FROM Emp WHERE ename = 'Sumit Patil');

Q.11 Write output of the following queries.

a. Create view emp_view as select emp_no, enema, salary from emp;

Ans:
CREATE VIEW emp_view AS SELECT emp_no, ename, salary FROM emp;

b. Update emp_view set e_name=’Jay’ where emp_no=101;

Ans: UPDATE emp_view SET ename = 'Jay' WHERE emp_no = 101;

Practical List

c. Delete from emp_view where emp_no= 105;

Ans: DELETE FROM emp_view WHERE emp_no = 105;

d. Drop view emp_view;

Ans: DROP VIEW emp_view;

e. Modify location of dept_no of dept_view;

Ans: UPDATE dept_view SET location = 'New Location' WHERE dept_no = 10;

Q.12 Write output of following queries.

a. Create simple index dept_simple-index on dept table.

Ans: CREATE INDEX dept_simple_index ON dept(dept_no);

b. Create composite index dept_composite_index on dept table.

Ans: CREATE INDEX dept_composite_index ON dept(dept_no, location);

c. Drop index dept_simple_index and dept_composite_index.

Ans: DROP INDEX dept_simple_index;

DROP INDEX dept_composite_index;

d. Create index raj on emp (empno, ename).

Ans: CREATE INDEX raj ON emp(empno, ename);

Q.13 Write a PL/SQL program that asks the user for percentage and then assigns grades based on the
following conditions:

Distinction (>=75%)

First Class (>=60 and <75)

Second Class (>=45 and <60)

Pass Class (>=40 and <45)

Fail (<40).
Practical List

Ans:

DECLARE

percentage NUMBER;

grade VARCHAR2(20);

BEGIN

DBMS_OUTPUT.PUT_LINE('Enter your percentage:');

percentage NUMBER;

IF percentage >= 75 THEN

grade := 'Distinction';

ELSIF percentage >= 60 AND percentage < 75 THEN

grade := 'First Class';

ELSIF percentage >= 45 AND percentage < 60 THEN

grade := 'Second Class';

ELSIF percentage >= 40 AND percentage < 45 THEN

grade := 'Pass Class';

ELSE

grade := 'Fail';

END IF;

DBMS_OUTPUT.PUT_LINE('Your grade is: ' || grade);

END;
Practical List

Q.14 Write a PL/SQL program that asks the user for their age and then prints “You can vote" if they

are over 18, and "You cannot vote" otherwise.

Ans:

DECLARE

age NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Enter your age:');

age NUMBER;

IF age >= 18 THEN

DBMS_OUTPUT.PUT_LINE('You can vote');

ELSE

DBMS_OUTPUT.PUT_LINE('You cannot vote');

Q.15 Write a PL/SQL program to display multiplication table of 5 using FOR loop.

Ans:

BEGIN

FOR i IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE('5 * ' || i || ' = ' || (5 * i));

A END LOOP;

END;
Practical List

Q.16 Write a PL/SQL program to calculate factorial of 10 by using PL/SQL WHILE LOOP statement.

Ans:
DECLARE
num NUMBER := 10;
factorial NUMBER := 1;
i NUMBER := 1;
BEGIN
WHILE i <= num LOOP
factorial := factorial * i;
i := i + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is: ' || factorial);
END;

Q.17 Write a PL/SQL program that asks the user to input a number (1 for Monday, 2 for Tuesday, ...,
7 for Sunday) and prints the corresponding day of the week.

Ans: DECLARE

day_number NUMBER;

day_name VARCHAR2(20);

BEGIN

DBMS_OUTPUT.PUT_LINE('Enter a number (1 for Monday, 2 for Tuesday, ..., 7 for Sunday):');

day_number NUMBER;

CASE day_number

WHEN 1 THEN

day_name := 'Monday';

WHEN 2 THEN

day_name := 'Tuesday';

WHEN 3 THEN
Practical List

day_name := 'Wednesday';

WHEN 4 THEN

day_name := 'Thursday';

WHEN 5 THEN

day_name := 'Friday';

WHEN 6 THEN

day_name := 'Saturday';

WHEN 7 THEN

day_name := 'Sunday';

ELSE

day_name := 'Invalid input';

END CASE;

DBMS_OUTPUT.PUT_LINE('The day of the week is: ' || day_name);

END;
Practical List

Q.18 Write a PL/SQL program for displaying details of students studying in computer department

using cursors.

Ans:

DECLARE

CURSOR student_cursor IS

SELECT student_id, student_name, department

FROM students

WHERE department = 'Computer';

student_record student_cursor%ROWTYPE;

BEGIN

OPEN student_cursor;

LOOP

FETCH student_cursor INTO student_record;

EXIT WHEN student_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Student ID: ' || student_record.student_id ||

', Student Name: ' || student_record.student_name ||

', Department: ' || student_record.department);

END LOOP;

CLOSE student_cursor;

END;
Practical List

Q.19 Write a PL/SQL program that asks the user to input two numbers and divide the first number by
the second. Handle the predefined exception for division by zero and display an appropriate message
if it occurs.
Ans:
DECLARE
num1 NUMBER;

num2 NUMBER;

result NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Enter the first number:');

num1 NUMBER:=:num1;

DBMS_OUTPUT.PUT_LINE('Enter the second number:');

num2 NUMBER:=:num2;

BEGIN

result := num1 / num2;

DBMS_OUTPUT.PUT_LINE;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(' ');

END;
Practical List

Q.20 Write a PL/SQL program that retrieves the salary of an employee based on their employee ID
(emp_id). If the employee ID does not exist in the database, handle the NO_DATA_FOUND exception
and print a message saying, "Employee ID not found.
Ans:

DECLARE

emp_id NUMBER;

emp_salary NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Enter the employee ID:');

emp_id NUMBER;

BEGIN

SELECT salary INTO emp_salary

FROM employees

WHERE employee_id = emp_id;

DBMS_OUTPUT.PUT_LINE('The salary of employee ' || emp_id || ' is: ' || emp_salary);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Employee ID not found.');

END;

END;
Practical List

Q.21 Write a PL/SQL program that asks for customer Id, when user enters invalid Id, the exception

Invalid-Id is raised.

Ans:

DECLARE

customer_id NUMBER;

-- Declare a custom exception

Invalid_Id EXCEPTION;

BEGIN

DBMS_OUTPUT.PUT_LINE('Enter customer ID:');

customer_id NUMBER:=: customer_id;

IF customer_id <= 0 THEN

RAISE Invalid_Id; -- Raise the custom exception if the ID is invalid

ELSE

DBMS_OUTPUT.PUT_LINE('Valid customer ID: ' || customer_id);

END IF;

EXCEPTION

WHEN Invalid_Id THEN

DBMS_OUTPUT.PUT_LINE('Error: Invalid customer ID entered.');

END;
Practical List

Q.22 Create a stored procedure to accept name and greet user with name.

Ans:Ans:

CREATE OR REPLACE PROCEDURE greet_user (user_name IN VARCHAR2) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello, ' || user_name || '! Welcome!');

END greet_user;

Q.23 Write PL/SQL function which will compute and return the maximum of two values.

Ans:

CREATE OR REPLACE FUNCTION get_max (val1 IN NUMBER, val2 IN NUMBER)

RETURN NUMBER IS

BEGIN

IF val1 > val2 THEN

RETURN val1;

ELSE

RETURN val2;

END IF;

END get_max;

DECLARE

num1 NUMBER := 10;

num2 NUMBER := 20;

max_value NUMBER;

BEGIN

max_value := get_max(num1, num2);

DBMS_OUTPUT.PUT_LINE('The maximum value is: ' || max_value);

END;
Practical List

Q.24 Write PL/SQL function to calculate the factorial of given no.

Ans:

CREATE OR REPLACE FUNCTION calculate_factorial (n IN NUMBER) RETURN NUMBER IS

fact NUMBER := 1;

BEGIN

IF n < 0 THEN

RETURN -1;

ELSE

FOR i IN 1..n LOOP

fact := fact * i;

END LOOP;

END IF;

RETURN fact;

END calculate_factorial;