NAVYA YADAV
BCA 4TH SEM
60
DATA BASE MANAGEMENT SYSTEM LAB
BCA-407P
MS. MEENU VERMA
QUESTION 1
CREATE A TABLE CALLED EMPLOYEE WITH THE
FOLLOWING STRUCTURE.
EMPNO NUMBER ,ENAME VARCHAR2(10) ,JOB VARCHAR2(10),
MGR NUMBER ,SAL NUMBER
create table Employee(
EmpNo int,
Ename varchar2(10),
Job varchar2(10),
Mgr int,
Sal int);
A. Add a column commission with domain to the employee table.
alter table Employee
add commission int;
B. Insert any five records into the table .
insert into Employee(EmpNo,Ename,Job,mgr,Sal,Commission)
values(1,'Tony','Engineer',200,98865,10);
insert into Employee(EmpNo,Ename,Job,mgr,Sal,Commission)
values(2,'Bruce','Mechanic',100,325423,100);
insert into Employee(EmpNo,Ename,Job,mgr,Sal,Commission)
values(3,'Peter','Hacker',500,36723,1000);
insert into Employee(EmpNo,Ename,Job,mgr,Sal,Commission)
values(4,'Saitama','Hero',800,1344,7000);
insert into Employee(EmpNo,Ename,Job,mgr,Sal,Commission)
values(5,'Genos','Sidekick',8200,131834,99000);
C. UPDATE THE COLUMN DETAILS OF JOB
update Employee
set Job= 'Hacker'
where EmpNo= 1;
update Employee
set Job= 'Analyst'
where EmpNo= 2;
update Employee
set Job= 'Engineer'
where EmpNo= 3;
update Employee
set Job= 'Mechanic'
where EmpNo= 4;
update Employee
set Job= 'Hero'
where EmpNo= 5;
D. RENAME THE COLUMN OF EMPLOY TABLE USING ALTER
COMMAND
alter table Employee
rename column Job to JobTitle;
delete from Employee
where EmpNo in (1,5);
QUESTION 2
CREATE DEPARTMENT TABLE WITH THE FOLLOWING
STRUCTURE. DEPTNO NUMBER ,DEPTNAME VARCHAR2(10),
LOCATION VARCHAR2(10)
create table Department(
DeptNo int,
DeptName varchar2(10),
Location varchar2(10));
A. ADD COLUMN DESIGNATION TO THE DEPARTMENT
TABLE.
alter table Department
add Designation varchar2(15);
B. INSERT VALUES INTO THE TABLE.
insert into Department(DeptNo, DeptName, Location, Designation)
values(1,'Admin', 'China', 'Manager');
insert into Department(DeptNo, DeptName, Location, Designation)
values(2,'Finance', 'Russia', 'Officer');
insert into Department(DeptNo, DeptName, Location, Designation)
values(3,'Marketing', 'India', 'Seller');
C. LIST THE RECORDS OF DEPT TABLE GROUPED BY
DEPTNO.
SELECT deptno
FROM department;
D. UPDATE THE RECORD WHERE DEPTNO IS 2 .
update department
set DeptName='Industry', Location = 'Home', designation='Gamer'
where DeptNo =2;
E. DELETE ANY COLUMN DATA FROM THE TABLE.
update department
set Deptname= NULL
where DeptNo =1;
QUESTION 3
CREATE A TABLE EMPLOYEE WITH FOLLOWING SCHEMA:
(EMP_NO, E_NAME, E_ADDRESS, E_PH_NO, DEPT_NO,
DEPT_NAME,JOB_ID , SALARY)
create table Employee(
Emp_no int,
E_name varchar2(20),
E_address varchar2(15),
E_ph_no int,
Dept_no int,
Dept_name varchar2(15),
Job_id int,
Salary int);
1. INSERT ALEAST 5 ROWS IN THE TABLE.
insert into Employee(Emp_no, E_name, E_address, E_ph_no, Dept_no, dept_name,
job_id, salary)
values(1,'Tony','India',123,10,'Web Security',50,900000);
insert into Employee(Emp_no, E_name, E_address, E_ph_no, Dept_no, dept_name,
job_id, salary)
values(2,'Mark','America',456,11,'Web Design',51,900);
insert into Employee(Emp_no, E_name, E_address, E_ph_no, Dept_no, dept_name,
job_id, salary)
values(3,'Goku','Japan',897,12,'Hero',52,9003);
insert into Employee(Emp_no, E_name, E_address, E_ph_no, Dept_no, dept_name,
job_id, salary)
values(4,'Tanjiro','Japan',8242,13,'Hashira',53,9242);
insert into Employee(Emp_no, E_name, E_address, E_ph_no, Dept_no, dept_name,
job_id, salary)
values(5,'Saitama','tokyo',321,14,'Boss',43,2631);
2. DISPLAY ALL THE INFORMATION OF EMP TABLE
select * from Employee;
3. DISPLAY THE RECORD OF EACH EMPLOYEE WHO WORKS
IN DEPARTMENT D10.
select * from Employee
where dept_no=10;
4. UPDATE THE CITY OF EMP_NO-update Employee
set E_address='Nagpur'
update Employee
set E_address='Nagpur'
where emp_no=2;
5. DISPLAY THE DETAILS OF EMPLOYEE WHO WORKS IN
DEPARTMENT HERO.
select * from employee
where dept_name = 'Hero';
6. DELETE THE E_PH_NO OF EMPLOYEE TONY
update employee
set e_ph_no= NULL
where E_name='Tony';
7. DISPLAY THE COMPLETE RECORD OF EMPLOYEES
WORKING IN BOSS DEPARTMENT.
select * from employee
where dept_name='Boss';
Question 4
CREATE THE FOLLOWING SCHEMA, ENTER AT LEAST 5
RECORDS IN EACH TABLE AND ANSWER THE QUERIES
GIVEN BELOW. LIBRARYBOOKS (ACCESSION NUMBER,
TITLE, AUTHOR, DEPARTMENT, PURCHASEDATE, PRICE)
ISSUEDBOOKS (ACCESSION NUMBER, BORROWER)
create table LibraryBooks(
AccessionNumber int PRIMARY KEY,
Title varchar2(20),
Author varchar2(25),
Department varchar2(20),
PurchaseDate date,
price int);
create table IssuedBooks(
AccessionNumber int,
Borrower varchar2(20),
FOREIGN KEY (AccessionNumber) REFERENCES
LibraryBooks(AccessionNumber)
);
A) INSERT AT LEAST 5 RECORDS IN EACH TABLE.
insert into LibraryBooks(AccessionNumber, Title, Author, Department,
PurchaseDate, Price)
values(1,'Mathematics','RD Sharma','dept1','7-MAY-2023',1000);
insert into LibraryBooks(AccessionNumber, Title, Author, Department,
PurchaseDate, Price)
values(2,'Chemistry','Author2','dept2','12-MAY-2023',5000);
insert into LibraryBooks(AccessionNumber, Title, Author, Department,
PurchaseDate, Price)
values(3,'BlackHatPython','Justin Seitz','dept3','15-MAY-2023',10000);
insert into LibraryBooks(AccessionNumber, Title, Author, Department,
PurchaseDate, Price)
values(4,'Book4','Author4','dept4','16-MAY-2023',12321);
insert into LibraryBooks(AccessionNumber, Title, Author, Department,
PurchaseDate, Price)
values(5,'Book5','Author5','dept5','18-MAY-2023',98745);
insert into IssuedBooks(AccessionNumber, Borrower)
values(1,'Barry');
insert into IssuedBooks(AccessionNumber, Borrower)
values(2,'Wayne');
insert into IssuedBooks(AccessionNumber, Borrower)
values(3,'Alfred');
insert into IssuedBooks(AccessionNumber, Borrower)
values(4,'Kroc');
insert into IssuedBooks(AccessionNumber, Borrower)
values(5,'Wader');
B) DELETE THE RECORD OF BOOK TITLED “DB SYSTEM
CONCEPTS”
delete from IssuedBooks
where AccessionNumber in(
select AccessionNumber
from LibraryBooks
where Title= 'DB System Concepts');
delete from LibraryBooks
where Title= 'DB System Concepts';
C) CHANGE THE DEPARTMENT OF THE BOOK TITLED
“MATHEMATICS” TO “CSE”.
update LibraryBooks
set Title ='CSE'
where Title='Mathematics';
D) LIST ALL BOOKS THAT BELONG TO “CSE” DEPARTMENT.
select * from LibraryBooks
where Department= 'CSE';
E) LIST ALL BOOKS THAT BELONG TO “CSE” DEPARTMENT
AND ARE WRITTEN BY AUTHOR “JUSTIN SEITZ”
select * from LibraryBooks
where Department= 'CSE' and Author= 'Justin Seitz';
QUESTION 5
CREATE THE FOLLOWING SCHEMA, ENTER AT LEAST 5
RECORDS IN EACH TABLE AND ANSWER THE QUERIES
GIVEN BELOW. STUDENT (COLLEGE ROLL NUMBER, NAME
OF STUDENT, DATE OF BIRTH, ADDRESS, MARKS (ROUNDED
OFF TO WHOLE NUMBER) IN PERCENTAGE AT 10 + 2, PHONE
NUMBER)
PAPER DETAILS (PAPER CODE, NAME OF THE PAPER)
ACADEMIC DETAILS (COLLEGE ROLL NUMBER, PAPER
CODE, ATTENDANCE, MARKS IN HOME EXAMINATION)
CREATE TABLE Student (
CollegeRollNumber NUMBER,
StudentName VARCHAR2(50),
DateOfBirth DATE,
Address VARCHAR2(100),
MarksPercentage NUMBER,
PhoneNumber VARCHAR2(20)
);
CREATE TABLE PaperDetails (
PaperCode VARCHAR2(10),
PaperName VARCHAR2(50)
);
CREATE TABLE AcademicDetails (
CollegeRollNumber NUMBER,
PaperCode VARCHAR2(10),
Attendance NUMBER,
HomeExamMarks NUMBER
);
insert into student values (1, 'john doe', to_date('1995-01-15', 'yyyy-mm-dd'), '123
main street', 85, '1234567890');
insert into student values (2, 'jane smith', to_date('1996-03-10', 'yyyy-mm-dd'), '456
elm street', 78, '9876543210');
insert into student values (3, 'david johnson', to_date('1994-07-21', 'yyyy-mm-dd'),
'789 oak avenue', 92, '5678901234');
insert into student values (4, 'emily brown', to_date('1997-11-05', 'yyyy-mm-dd'), '321
pine road', 81, '9012345678');
insert into student values (5, 'michael wilson', to_date('1993-09-28', 'yyyy-mm-dd'),
'654 cedar lane', 88, '3456789012');
insert into paperdetails values ('p1', 'mathematics');
insert into paperdetails values ('p2', 'english');
insert into paperdetails values ('p3', 'science');
insert into paperdetails values ('p4', 'history');
insert into paperdetails values ('p5', 'computer science');
INSERT INTO AcademicDetails VALUES (1, 'P1', 90, 85);
INSERT INTO AcademicDetails VALUES (1, 'P2', 95, 80);
INSERT INTO AcademicDetails VALUES (2, 'P3', 88, 90);
INSERT INTO AcademicDetails VALUES (3, 'P1', 92, 75);
INSERT INTO AcademicDetails VALUES (4, 'P4', 85, 92);
B) DESIGN A QUERY THAT WILL RETURN THE RECORDS
(FROM THE SECOND TABLE) ALONG WITH THE NAME OF
STUDENT FROM THE FIRST TABLE, RELATED TO STUDENTS
WHO HAVE MORE THAN 75% ATTENDANCE AND MORE
THAN 60% MARKS IN PAPER 2.
select sd.studentname, ad.*
from academicdetails ad
join student sd on ad.collegerollnumber = sd.collegerollnumber
where ad.attendance > 75 and ad.papercode = 'p2' and ad.homeexammarks > 60;
C) QUERY TO LIST STUDENTS WHO LIVE IN "LUCKNOW" AND
HAVE MARKS GREATER THAN 60 IN PAPER 1:
SELECT *
FROM Student
WHERE Address = 'Lucknow' AND MarksPercentage > 60 AND
CollegeRollNumber IN (
SELECT CollegeRollNumber
FROM AcademicDetails
WHERE PaperCode = 'P1'
);
D) FIND THE TOTAL ATTENDANCE AND TOTAL MARKS
OBTAINED BY EACH STUDENT
SELECT sd.StudentName, SUM(ad.Attendance) AS TotalAttendance,
SUM(ad.HomeExamMarks) AS TotalMarks
FROM AcademicDetails ad
JOIN Student sd ON ad.CollegeRollNumber = sd.CollegeRollNumber
GROUP BY sd.StudentName;
E) LIST THE NAME OF STUDENT WHO HAS GOT THE
HIGHEST MARKS IN PAPER 2
SELECT sd.StudentName
FROM AcademicDetails ad
JOIN Student sd ON ad.CollegeRollNumber = sd.CollegeRollNumber
WHERE ad.PaperCode = 'P’
