School of IT

Institute of Management Studies - Noida

MCA(2020-2022)

Database Management System LAB

(KCA-252)
Submitted To: Submitted By:
Ms Shweta Singh Chandrika Rawat
Assistant Professor- SOIT 2000980140011
 INDEX
Sr. No. Particulars Page No. Date Remarks
1. Create table and insert values as follows
Name Age ID RNo
XYZ 25 1 2
PQZ 26 2 3
MNO 25 3 4
PQR 26 4 5

2. Show the description of given table.

3. Display the Name, Age where Rno is greater than 3

4. Delete the row where ID is equal to 1

5. Update given table set name attribute value is equal to

“RAM” for the Name value “PQR”

6. Display all the table values by select statement

7. Display table values from table where Age = 26 and Name =

“PQR”.

8. Drop the given Table

9. Create a Table named ‘Employee’ with the following

specifications
10. Add a new column named Email of the type character with a
maximum width 0f 30 with varying size
11. Add a new column Passport_No of the type character with a
maximum width of 30 with varying size
12. List all the employees who belong to the city names starting
with ‘k’.

13. List all the employees who either belong to city names
starting with ‘K’ or from ‘Ghaziabad’
14. List all the employees who are drawing salary more than
10000 and belong to IT Department or HR Department.
15. List all the employees who are getting Salary in the range
5000 to 15000 and belong to city “Delhi”, “Bombay” or “
Madras”.
16. List the no. of employees and salary drawn by each
department
17. List the Cities and No. of Employees belong to that city.

18. Remove the column Passport_No.

19. List the names, Designation and Dept. name of all the
employees who are getting salary more than 20000 and are
placed at Noida, New Delhi or Ghaziabad.

20. Create a Table named Empl with the following

Specifications:
a. Add Primary Key constraints on Employee relation
on EMP_ID
b. LIST all records of EMP1 table with manager which
is also employee

21. . Create table for following values (Table1 and Table2)

22. Show the UNION of Table 1 and Table2 which is created in

21.

23. Display the INTERSECTION of two table which is created in

21.

24. Display the Difference of two Table(Table1-Table2) which is

created in 21.

25. Create table for following values (T1 and T2).

26. Display Name, Age and Address with the help of given T1
and T2.

27. Create table for following values (Tab1 and Tab2).

28. Display Name1, Age and Fname with the help of given Tab1
and Tab2. (Use Outer Join ) retaining all the values
corresponding name1 attribute.)

29. Create a view view1 from above Tab1 and Tab2 table given
in Q7, with NAME1, Age and Fname attribute
30. Write a PL/SQL code block to calculate the area of a circle
for a value of radius varying from 3 to 7. Store the radius and
the corresponding values of calculated area in a table, Areas

31. Write a PL/SQL block that will display the name, department
and salary of the first 10 employees getting the highest
salary.

32. Write a PL/SQL block to update the salary of each employee

and insert a record in the emp_raise table as well.
Table Name: employee

33.
Write a PL/SQL code block that will accept an account
number from the user and debit an amount of RS. 2000 from
the account if the account has a minimum balance of R. 500
after the amount is debited. The process is to be fired on the
Account table.

Table Name: Account

1. Create table and insert values as follows
Name Age ID RNo
XYZ 25 1 2
PQZ 26 2 3
MNO 25 3 4
PQR 26 4 5

2. Show the description of given table.

3. Display the Name, Age where Rno is greater than 3

4. Delete the row where ID is equal to 1.

5. Update given table set name attribute value is equal to “RAM” for the
Name value “PQR”

6. Display all the table values by select statement.

 7. Display table values from table where Age = 26 and Name = “PQR”.

8. Drop the given Table

Program:
create table STUDENT(
S_Name varchar2(30),
Age integer,
S_ID integer,
RNo integer,
primary key(S_ID))
insert into STUDENT values('XYZ',25,1,2)
insert into STUDENT values('PQZ',26,2,3)
insert into STUDENT values('MNO',25,3,4)
insert into STUDENT values('PQR',26,4,5)
select * from STUDENT WHERE Age = 26 and S_NAME = 'PQR'
select S_Name,Age from STUDENT where RNO>3
delete from STUDENT where S_ID=1
UPDATE Student SET S_NAME = 'RAM' WHERE S_NAME = 'PQR'
drop table STUDENT
9. Create a Table named ‘Employee’ with the following specifications:

a) Emp_No of the type numeric with a maximum width of 4 digits and

no null values and duplicate should be permitted in this column.
b) Ename of the type character with a maximum width of 20 with
varying size.
c) Address of the type character with a maximum width of 30 with
varying size.
d) Dept_Name of the type character with a maximum width of 5 with
fixed size and values can not be other than “IT”, “HR”,
“PROD”,”FIN”,”MKT” should be permitted.
e) Designation of the type character with a maximum width of 10 with
fixed size.
f) Deptno of the type number with a maximum width of 2 digits and
only values permitted are 10,20,30,40,50.
g) City of the type character with a maximum width of 15 with varying
size.
h) Phone of the type numeric with a maximum width of 10 digits.
i) Salary of the type numeric with a maximum width of 8 and 2 digits
are reserved after decimal place, Any value less than 1000 should not
be permitted in this column.
Now write equivalent SQL Statement for the following Queries :
10. Add a new column named Email of the type character with a maximum
width 0f 30 with varying size.

11. Add a new column Passport_No of the type character with a maximum
width of 30 with varying size.

alter table EMPLOYEE

add Passport_No char(30)
12. List all the employees who belong to the city names starting with ‘k’.

13. List all the employees who either belong to city names starting with ‘K’ or
from ‘Ghaziabad’.

select * from EMPLOYEE where City Like 'K%' or City='Ghaziabad'

14. List all the employees who are drawing salary more than 10000 and belong
to IT Department or HR Department.

15. List all the employees who are getting Salary in the range 5000 to 15000
and belong to city “Delhi”, “Bombay” or “ Madras”.
16. List the no. of employees and salary drawn by each department.

17. List the Cities and No. of Employees belong to that city.
18. Remove the column Passport_No.

19. List the names, Designation and Dept. name of all the employees who are
getting salary more than 20000 and are placed at Noida, New Delhi or
Ghaziabad.
Program:
create table EMPLOYEE(
Emp_No number(4,0) NOT NULL,
Ename char(20),
Address char(30),
Dept_Name char(5),
Designation char(10),
Deptno number(2,0),
City char(15),
Phone number(10,0),
Salary number(10,2),
primary key (Emp_No),
check (Salary>1000));

insert into EMPLOYEE values

(1,'Ajay','ABC','IT','President',10,'Ghaziabad',9376,80000.43)
insert into EMPLOYEE values
(2,'Amit','XYZ','HR','Manager',23,'Delhi',8475,75000.54)
insert into EMPLOYEE
values (3,'Atul','PQR','PROD','Salesman',54,'KarolBagh',9874,55000.4)
insert into EMPLOYEE
values (4,'Priya','MNO','FIN','Analyst',42,'Bombay',3054,30000.67)
insert into EMPLOYEE
values (5,'Shivani','DEF','MKT','Clerk',98,'Madras',3534,25000.5)
insert into EMPLOYEE
values (6,'Akshay','GHI','IT','PO',76,'Noida',5440,35000.4)
insert into EMPLOYEE
values (7,'Shreya','STU','HR','President',53,'Newdelhi',5610,90000.57)
insert into EMPLOYEE
values (8,'Nidhi','KLM','PROD','Salesman',60,'Kanpur',5425,75000.69)
insert into EMPLOYEE
values (9,'Raj','RSA','FIN','Economist',21,'Kashmir',8720,45000.56)
insert into EMPLOYEE
values (10,'Rajan','GYT','MKT','Analyst',48,'UP',9630,60000.59)

select * from EMPLOYEE order by Emp_No

alter table EMPLOYEE

add Email char(30)

alter table EMPLOYEE

add Passport_No char(30)
select * from EMPLOYEE where City Like 'K%' or City='Ghaziabad'

select * from EMPLOYEE

where Salary between 5000 and 15000
and City='Delhi' or City='Bombay' or City='Madras'

select Count(Emp_No),Sum(salary),Dept_Name
from EMPLOYEE group by Dept_Name

select Count(Emp_No),City
from EMPLOYEE group by City

select Ename,Dept_Name,Designation
from EMPLOYEE where Salary>20000
and City='Noida' or City='NewDelhi' or City='Ghaziabad'

select * from EMPLOYEE group by City

alter table EMPLOYEE

Drop column Passport_No

drop table EMPLOYEE

20. Create a Table named Empl with the following Specifications:

Name Type

Emp_ID Of the type Numeric with a maximum width of 4 digits

NAME Character with varying size of 25
CITY Character with varying size of 25
PHONE Of the type numeric with a maximum width of 8 digits
DEPT_NO Of the type numeric with a maximum width of 4 digits
No NULL value is permitted in this column
DESIGNATION Of the type character with a fixed size of 10 characters
No NULL value is permitted in this column
SALARY Of the type numeric with a maximum width of 8 digits
PASSPORT_NO Of the type character with a fixed size of 10 character
1. Add Primary Key constraints on Employee relation on EMP_ID

2. LIST all records of EMP1 table with manager which is also employee.
 Program:
create table Empl(
Emp_ID number(4,0),
ENAME char(25),
CITY char(25),
PHONE number(8,0),
DEPT_NO number(4,0) NOT NULL,
DESIGNATION char(10) NOT NULL,
SALARY number(10,2),
PASSPORT_NO char(10))

insert into Empl values

(1,'Ajay','Ghaziabad',986354,1001,'President',80000,'ABC')
insert into Empl values
(2,'Amit','Delhi',7364537,2003,'Manager',75000,'XYZ')
insert into Empl
values (3,'Atul','KarolBagh',736452,5007,'Salesman',55000,'PQR')
insert into Empl
values (4,'Priya','Bombay',305564,2004,'Manager',30000,'MNO')
insert into Empl
values (5,'Shivani','Madras',9534364,3002,'Clerk',25000,'DEF')

ALTER TABLE Empl ADD PRIMARY KEY (EMP_ID)

select * from Empl order by EMP_ID

select * from Empl where DESIGNATION='Manager'

21. Create table for following values (Table1 and Table2)
Table 1 Table 2

Name Age ID Name Age ID

P 25 1 Q 23 2
Q 23 2 R 24 3
R 24 3 S 25 4
S 25 4 T 26 5
22. Show the UNION of Table 1 and Table2 which is created in Q21.
23. Display the INTERSECTION of two table which is created in Q21.

24. Display the Difference of two Table(Table1-Table2) which is created in

Q21
Program :

create table TABLE1(

NAME1 char(10),
AGE integer,
ID1 integer)

insert into TABLE1 values('P',25,1)

insert into TABLE1 values('Q',23,2)
insert into TABLE1 values('R',24,3)
insert into TABLE1 values('S',25,4)

select * from TABLE1 order by ID1

create table TABLE2(

NAME2 char(10),
AGE integer,
ID2 integer)

insert into TABLE2 values('Q',23,2)

insert into TABLE2 values('R',24,3)
insert into TABLE2 values('S',25,4)
insert into TABLE2 values('T',26,5)

select * from TABLE2 order by ID2

select * from TABLE1 UNION select * from TABLE2

select * from TABLE1 INTERSECT select * from TABLE2

select * from TABLE1 MINUS select * from TABLE2

25. Create table for following values (T1 and T2).
T1 T2
Name Age ID1 ID2 Address
PQR 25 1 1 PPPP
QST 26 2 2 VVVV
RAJ 27 3 3 WWWW
ABC 28 4 4 XXXX
26. Display Name, Age and Address with the help of given T1 and T2.
Program :

create table T1(

NAME1 char(10),
AGE integer,
ID1 integer)

insert into T1 values('PQR',25,1)

insert into T1 values('QST',26,2)
insert into T1 values('RAJ',27,3)
insert into T1 values('ABC',28,4)

select * from T1 order by ID1

create table T2(

ID2 integer,
ADDRESS char(10))

insert into T2 values(1,'PPPP')

insert into T2 values(2,'VVVV')
insert into T2 values(3,'WWWW')
insert into T2 values(4,'XXXX')

select * from T2 order by ID2

select T1.NAME1,T1.AGE,T2.ADDRESS
from T1 INNER JOIN T2
on T1.ID1 = T2.ID2

drop table T2
27. Create table for following values (Tab1 and Tab2).
Tab1 Tab2

Name1 Age ID Name2 Fname

P 25 1 P FP
Q 26 2 Q FQ
R 27 3 S FS
M 26 4 T FT
28. Display Name1, Age and Fname with the help of given Tab1 and Tab2.
(Use Outer Join ) retaining all the values corresponding name1 attribute.)
29. Create a view view1 from above Tab1 and Tab2 table given in Q27, with

Program :

create table TAB1(

NAME1 char(10),
AGE integer,
ID1 integer)

insert into TAB1 values('P',25,1)

insert into TAB1 values('Q',26,2)
insert into TAB1 values('R',27,3)
insert into TAB1 values('M',26,4)

create table TAB2(

NAME2 char(10),
FNAME char(4))

insert into TAB2 values('P','FP')

insert into TAB2 values('Q','FQ')
insert into TAB2 values('S','FS')
insert into TAB2 values('T','FT')

select * from TAB2 order by NAME2

select TAB1.NAME1,TAB1.AGE,TAB2.FNAME
from TAB1 FULL OUTER JOIN TAB2
on TAB1.NAME1 = TAB2.NAME2

CREATE VIEW viewtab AS

SELECT TAB1.NAME1,TAB1.AGE,TAB2.FNAME
FROM TAB1,TAB2
30. Write a PL/SQL code block to calculate the area of a circle for a value of
radius varying from 3 to 7. Store the radius and the corresponding values of
calculated area in a table, Areas.
Program :

NAME1, Age and Fname attribute.

create table areas ( r number(2), area number (14,2));

DECLARE
r number(5);
area number(14,2);
pi constant number (4,2):=3.14;
BEGIN
r:=3;
while r<=7
loop
area:=pi*power(r,2);
insert into areas values(r,area );
r:=r+1;
end loop;
END;

select * from areas;

Output :
31. Write a PL/SQL block that will display the name, department and salary
of the first 10 employees getting the highest salary.

CREATE TABLE EMPLOYEE(

E_ID INT NOT NULL,
E_NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
DEPARTMENT CHAR (25),
SALARY DECIMAL (18,2),
PRIMARY KEY (E_ID)
);
INSERT INTO EMPLOYEE VALUES (1,'Ramesh',32,'IT',25000.00)
INSERT INTO EMPLOYEE VALUES (2,'Khilan',25,'Software',15400.00)
INSERT INTO EMPLOYEE VALUES (3,'kaushik',23,'Finance',23000.00)
INSERT INTO EMPLOYEE VALUES (4,'Chaitali',25,'Accounting',63500.00)
INSERT INTO EMPLOYEE VALUES (5,'Hardik',27,'HR',84500.00)
INSERT INTO EMPLOYEE VALUES (6,'Komal',22,'HR',49500.00)
INSERT INTO EMPLOYEE VALUES (7,'Ajay',23,'IT',80000.43)
INSERT INTO EMPLOYEE VALUES (8,'Amit',43,'Marketing',75000.54)
INSERT INTO EMPLOYEE VALUES (9,'Atul',36,'Production',55000.4)
INSERT INTO EMPLOYEE VALUES (10,'Priya',37,'Finance',30000.67)
INSERT INTO EMPLOYEE VALUES (11,'Shivani',27,'Marketing',25000.5)
INSERT INTO EMPLOYEE VALUES (12,'Akshay',46,'IT',35000.4)
INSERT INTO EMPLOYEE VALUES (13,'Shreya',28,'HR',90000.57)
INSERT INTO EMPLOYEE VALUES (14,'Nidhi',31,'Production',75000.69)
INSERT INTO EMPLOYEE VALUES (15,'Raj',42,'Finane',45000.56)
SELECT * FROM EMPLOYEE order by E_ID
DECLARE
cursor c_emp is select
E_NAME,DEPARTMENT,SALARY from EMPLOYEE order by salary desc;
ename EMPLOYEE.E_NAME%type;
dept EMPLOYEE.DEPARTMENT%type;
salary EMPLOYEE.SALARY%type;
BEGIN
open c_emp;
dbms_output.put_line('Name Department Salary');
dbms_output.put_line('---- ---------- ------');
loop
fetch c_emp into ename,dept,salary;
exit when c_emp%rowcount = 11 or c_emp%notfound;
dbms_output.put_line(ename||' '||dept||salary);
end loop;
close c_emp;
END;
OUTPUT :
32. Write a PL/SQL block to update the salary of each employee and insert a
record in the emp_raise table as well.

Table Name: employee

Column Name Data Type Size Attributes
Emp_code Varchar 10 Primary Key, via which we
shall seek data in the table.
Ename Varchar 20 The first name of the
candidate.
Deptno Number 5 The department number.
Job Varchar 20 Employee job details
Salary Number 8.2 The current salary of the
employee.

Table Name: emp_raise

Column Name Data Type Size Attributes
Emp_code Varchar 10 Is the part of a composite
key via which we shall seek
data in the table.
raise_date Date The date on which the raise
was given.
raise_amt Number The raise given to the
employee.
Emp_code and raise_date together form a composite primary key.
Program:
create table Employee1(
Emp_code varchar2(10),
Ename varchar2(20),
Deptno number(5,0),
Job varchar2(20),
Salary number(8,2),
primary key (Emp_code))

INSERT INTO Employee1 VALUES (1,'Ramesh',32,'IT',25000.00)

INSERT INTO Employee1 VALUES (2,'Khilan',25,'Software',15400.00)
INSERT INTO Employee1 VALUES (3,'kaushik',23,'Finance',23000.00)
INSERT INTO Employee1 VALUES (4,'Chaitali',25,'Accounting',63500.00)
INSERT INTO Employee1 VALUES (5,'Hardik',27,'HR',84500.00)

select * from Employee1 order by Emp_code

create table emp_raise(

Emp_code varchar2(20),
raise_date date,
raise_amt number(5,2),
primary key(Emp_code,raise_date))

insert into emp_raise values(1,'10-JUL-21',320.42)

insert into emp_raise values(2,'28-JAN-21',730.21)
insert into emp_raise values(3,'13-JUL-21',440.31)
insert into emp_raise values(4,'17-AUG-20',390.73)
insert into emp_raise values(5,'05-OCT-20',740.62)

select * from emp_raise order by Emp_code

declare
cursor c_emp is select Emp_code,Salary from Employee1;
str_emp_code Employee1.Emp_code%type;
num_salary Employee1.Salary%type;
num_raiseamt emp_raise.raise_amt%type;
begin
open c_emp;
if c_emp%isopen then
loop
fetch c_emp into str_emp_code,num_salary,num_raiseamt;
exit when c_emp%notfound;
update Employee1 set Salary = num_salary + num_raiseamt where Emp_code =
str_emp_code;
insert into emp_raise values(str_emp_code,Salary);
end loop;
close c_emp;
else
dbms_output.put_line('unable to open cursor');
end if;
end;
33. Write a PL/SQL code block that will accept an account number from the
user and debit an amount of RS. 2000 from the account if the account has a
minimum balance of R. 500 after the amount is debited. The process is to be
fired on the Account table.
Table Name: Account
Account Id Name Bal
AC001 Anuj 5000
AC002 Robert 10000
AC003 Mita 5000
AC004 Sunita 15000
AC005 Melba 10000

Program :
create table Accounts3(
Account_id varchar(5),
A_Name varchar(20),
Bal number(7,2));

insert into Accounts3 values ('AC001','Anuj',5000);

insert into Accounts3 values ('AC002','Robert',10000);
insert into Accounts3 values ('AC003','Mita',5000);
insert into Accounts3 values ('AC004','Sunita',15000);
insert into Accounts3 values ('AC005','Melba',10000);

SELECT * FROM Accounts3;

declare
acct_balance number(7,2);
acct_no varchar2(6);
debit_amt number(7,2):=2000.00;
min_bal constant number(7,2):=500.00;
Begin
acct_no:= &Account_id;
select bal into acct_balance
from Accounts3
where account_id=acct_no;
acct_balance:=acct_balance-debit_amt;
if acct_balance>=min_bal then
update accounts3 set bal=bal-debit_amt
where account_id=acct_no;
end if;
end;