Q.
1 Consider the following schema
Orders (cust_id, order_id, items, amount)
Write queries for the following:
• Display new column named total_amount which is 200 added to the amount field.
• Display new column named offer_price which is 100 subtracted from the amount field.
• Display new column named revised_amount which is multiplied by 5 times the amount field.
• Display new column named half_amount which is divided by 2 to the amount field.
Q.2 Consider the following schema
Employee (empno,ename,job,mgr,hiredate,sal,comm,deptno,city)
Write queries for the following:
o Display employees whose city is ‘Mumbai’ and earns more than 50000
o Display employees who job is Clerk or commission is 500
o Display details of employees whose salary is between 20000 and 50000.
o Display details of employees who stays at Mumbai, Pune, Nashik or Nagpur.
Q.3 Write a PL/SQL program to calculate factorial of 10 by using PL/SQL WHILE LOOP statement.
Q.4 Consider the following schema
Student (stu_name, course_id, Roll_no, percentage)
Write queries for the following:
o Select stu_name, course_id, from Student WHERE percentage is >=60 and <=100;
o Select details of students whose Roll numbers are above 15;
o Select stu_id, Roll_no from Student WHERE course_id! =121;
Q.5 Write a PL/SQL program to calculate the prime numbers between 1 to 50.
Q.6 Consider following Schema :
o emp1(empno,ename,deptno)
o emp2(empno,ename,deptno)
Write SQL commands for the following statements.
o Display the names of employees including duplicate employee names.
o Display the names of employees excluding duplicate employee names.
o Display the common employee names from both the tables.
o List employees who are not assigned to any department?
Q.7 Print Fibonacci Sequence Up to N Using FOR Loop.
Q.8 a) Write a query to Calculate Weeks Between Two Dates.
b) Write a query to extract Domain from an Email Address.
Q.9 a) Write a query to apply a discount and add tax to an amount and calculate
final price as per following values:
▪ Original price: $500
▪ Discount: 20%
▪ Tax rate: 10%
b) Write a query to Find the Day of the Week for a Specific Date.
Q.10 Write a PL/SQL program that calculates the sum of all even numbers between 1 and 10, skipping
odd numbers
Q.11 Consider the following schema
Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
o Display the minimum, maximum, sum and average salary of all employees. Label the
columns Maximum, Minimum, Sum and Average respectively.
o Determine the number of managers without listing them. Label the column number of
managers
o Write a query that will display the difference between the highest and lowest salaries. Label
the column DIFFERENCE.
o Display the number of employees in department 10 who earns a commission
Q.12 Consider the following schema
Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
o Display minimum salary of employee from every department;
o Display total salary of every department.
o Display the department having total employees more than 5.
o Display details of employees with employee name in ascending order.
Q.13 Write a procedure emp_count () to count number of employees in department, use
deptno as input parameter
Q.14 Consider EMP and DEPT table and write queries for following using Joins
• Display employee SCOTT’s employee number, name, department number, and department location.
• Display the list of employees who work in the sales department.
• Display the list of employees who do not work in the sales department.
• Display the employee names and salary of all employees who report to BLAKE.
• Find Employees in a Specific Location: Show employees who work in departments located in NEW
YORK.
Q.15 Create a stored procedure to accept name and greet user with name.
Q.16 Create a table EMPLOYEE with following schema:
Emp (EMP_no as primary key, E_name, Dept_no, Dept_name, Job_id, salary)
Q.17 Write a PL/SQL program that checks if a given number is positive, and if it is, prints "Number is
positive".
Q.18 Create tables EMPLOYEE and DEPARTMENT with following schema by applying
• Primary and Foreign key:
• Emp(empno as primary key, empname, salary, phoneno)
• Dept(deptno primary key, empno foreign key, deptname, location).
Q.19 Write a PL/SQL program that asks the user for percentage and then assigns grades based on the
following conditions:
o Distinction (>=75%)
o First Class (>=60 and <75)
o Second Class (>=45 and <60)
o Pass Class (>=40 and <45)
o Fail (<40)
Q.20 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.
• Change the stud table structure by adding column City.
• Increase the size by 10 of studentname column.
Q.21 Write a PL/SQL program for displaying details of students studying in computer department using
cursors.
Q.22 Write a PL/SQL procedure to insert any three records in EMP table.
Q.23 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.
Q.24 Write a PL/SQL program that prints numbers from 1 to 10, but skips printing the number 5.
Q.25 Using Cursors to Retrieve Employee Details from a Table
Q.26 Write PL/SQL function which will compute and return the maximum of two values.
Q.27 Create a trigger which invokes on updation of record in Department table
Q.28 Write a PL/SQL program by using the user defined exception.
Q.29 Write a PL/SQL procedure to update an employee’s salary in the emp table based on their employee
ID.
Q.30 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."
Q.31 Write a PL/SQL function to calculate the area of a rectangle given its length and width.
Q.32 Write PL/SQL function to calculate the factorial of given no.
Q.33 Create a trigger on EMP table which is invoked when salary is below 5000.
Q.34 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.
Q.35 Write a procedure to update the salary of all employees in a specific department.
Q. 36 Write a PL/SQL program to display multiplication table of 5 using FOR loop.