KCC Institute Of Technology and Management
Department of Computer Science and Engineering
Database Management System
Lab File BCS –501
Session - 2024-25
Submitted to: Submitted by:
Ms. Saranya Raj Roll No: 2204921540142
(Assistant Professor)
Name: Sana Khurshid
Branch: CSE(DS)
INDEX
Sr. EXPERIMENT NAME Date of Date of Faculty
No. Completion Submission Signature
Lab Assignment 1
18-10-24
01
Lab Assignment 2
22-10-24
02
Lab Assignment 3
03
28-10-24
Lab Assignment 4
1-11-24
04
Lab Assignment 5
05 4-11-24
Lab Assignment 6
06 8-11-24
Lab Assignment 7
11-12-24
07
INDEX
Sr. EXPERIMENT NAME Date of Date of Faculty
No. Completion Submission Signature
08 Lab Assignment 8 14-11-24
09 Lab Assignment 9 18-11-24
23-11-24
10 Lab Assignment 10
11 Lab Assignment 11 25-11-24
12 Lab Assignment 12 2-12-24
LAB ASSIGNMENT 1
LAB ASSIGNMENT 2
LAB ASSIGNMENT 3
LAB ASSIGNMENT 4
LAB ASSIGNMENT 5
LAB ASSIGNMENT 6
LAB ASSIGNMENT 7
Assignment on ‘Where’ Clause
Table Name : Persons
i) Display Order Id’s whose quantity is greater than 2
ii) List name of all employees those first name is started with letter ‘A’
iii) Display names those have Order Id is 10250 ?
iv) Display names of order whose unit price lies in the range of 10$ to 40$
v) Display names of ORDER where discount is empty
vi) Display names of all ships ends with alphabet ‘a’
LAB ASSIGNMENT 8
Assignments on GROUPBY, HAVING AND ORDERBY
i) Display total salary spent for each job category
ii) Display lowest paid employee details under each manager
iii) Display number of employees working in each department and their
department name
Customer table
i) Display details of customers whose age count would be more than or
equal to twenty two.
ii) Display details of customers & sort the result in ascending order by
NAME and SALARY
LAB ASSIGNMENT 9
Assignments on View
i) Create a view on salesman_master table for the sales department.
ii) Create a view on client_master table
iii) Perform insert , operation on view created in Q2.
iv) Perform modify operation on view created in Q2.
v) Perform delete operation on view created in Q2.
LAB ASSIGNMENT 10
Assignments on Date Functions
Que-1) Create the following table and perform the following queries
i) List the employee details who joined on a particular date.
ii) Write a query to list the employees with Hire date in the format like
February22, 1991.
iii) Write a query to list the employees who joined before 2018
iv) Write a query to list the employees who joined in the month January.
LAB ASSIGNMENT 11
Assignments on Sub Query
Table Name: Employee
i) Write a query to display the name,salary,department id for
those employeeswho earn such amount of salary which is the
smallest salary of any of the departments.
ii) Write a query to display the employee details who are having
the date of birth after the date of birth of employee having ID
102
iii) Write a query to display the employee details who are having
the same date of birth as of employee having id 106.
iv) Write a query to display the name for those employees who gets
more salary than the employee whose id is 104
LAB ASSIGNMENT 12
1. Hello World Program in PL/SQL
2. PL/SQL Program To Add Two Numbers
3. PL/SQL Program for Prime Number
declare
num number
i number:=1;
c number:=0;
begin
num:=#
for i in 1..num
loop
if((mod(num,i))=0)
then
c:=c+1;
end if;
end loop;
if(c>2)
then
dbms_output.put_line(num||' not a prime');
else
dbms_output.put_line(num||' is prime');
end if;
end;
/
Output:
4. PL/SQL Program to Find Factorial of a Number
Declare
num number:= #
fact number:= 1;
temp number;
begin
temp := num;
while (num > 0)
loop
fact := fact * num;
num := num - 1;
end loop;
Dbms_Output.Put_line('factorial of ' || temp || ' is ' || fact);
end;
/
Output:
5. PL/SQL Program for Reverse of a Number
declare
num1 number(5);
num2 number(5);
rev number(5);
begin
num1:=&num1;
rev:=0;
while num1>0
loop
num2:=num1 mod 10;
rev:=num2+(rev*10);
num1:=floor(num1/10);
end loop;
dbms_output.put_line('Reverse number is: '||rev);
end;
/
Output:
6. PL/SQL Program for Fibonacci Series
declare
a number(3):=1;
b number(3):=1;
c number(3);
n number(3):=&n;
begin
Dbms_output.put_line('the fibinocci series is:');
while a<=n
loop
dbms_output.put_line(a);
c:=a+b;
a:=b;
b:=c;
end loop;
end;
/
Output:
7. PL/SQL Program to Check Number is Odd or Even
declare
num:=:num
if(num mod 2=0) then
dbms_output.put_line(num|| 'is even');
else
dbms_output.put_line(num||' is odd');
end if;
end;
Output:
8. Pl/SQL Program for Palindrome Number
DECLARE
str varchar2(50):='&string';
counter int:=length(str);
BEGIN dbms_output.put_line(counter);
LOOP exit WHEN counter=0;
exit WHEN not(substr(str,counter,1)=substr(str,((length(str)+1)-counter),1));
counter:=counter-1;
END LOOP;
IF counter=0 THEN dbms_output.put_line(str||'is palindrom');
ELSE dbms_output.put_line(str||'is not palindrom');
END IF;
END;
/
Output:
10. PL/SQL Program to Find Greatest of Three Numbers
declare
a number(10);
b number(10);
c number(10);
begin
a:=&a;
b:=&b;
c:=&c;
if a>b and a>c then -- checking if a is the largest.
dbms_output.put_line(chr(10)||a||' is largest');
else if b>c then -- checking if b is the largest.
dbms_output.put_line(chr(10)||b||' is largest');
else -- c is largest.
dbms_output.put_line(chr(10)||c||' is largest');
end if;
end if;
end;
/
Output:
