create table Student (
regno varchar(20),
name varchar(20),
class varchar(20),
bdate date,
marks1 int,
marks2 int,
marks3 int,
Primary key (regno)
);
insert into Student values('U01', 'Shelton', 'BCA 3','2002/10/10', '90', '95', '97');
insert into Student values('U02', 'Michael', 'BCA 1','2001/10/10', '85', '25', '20');
insert into Student values('U03', 'Varad', 'BCA 2','2002/11/10', '87', '30', '45');
insert into Student values('U04', 'Raghu', 'BCA 4','2003/10/12', '91', '70', '87');
insert into Student values('U05', 'Rishi', 'BCA 3','2002/09/01', '90', '60', '40');
Demonstrate where, order by, having, group by.
select * from Student where class = 'BCA 3';
select * from Student order by marks2 desc;
Select class, sum(marks1) from student group by(class);
select * from student group by class having marks1 >= 90;
Demonstrate sum, avg, count, like, between, max & min.
select class, sum(marks1) from student group by(class);
select class, avg(marks1) from student group by class;
select class, COUNT(marks1) from student GROUP BY(class);
select * from student where name LIKE 'S%';
select name, regno from student where marks1 between 80 AND 90;
select name, class, min (marks1) from student;
select name, class, max(marks1) from student;
1. b) consider the following database that maintain information about employees and departments
Tables and Data
create table department (
deptno int,
dname varchar(10),
manager_id int,
primary key (deptno)
);
insert into department values (1, 'CS', 100);
insert into department values (2, 'Physics', 101);
insert into department values (3, 'Chemistry', 102);
insert into department values (4, 'Maths', 103);
insert into department values (5, 'Biology', 104);
create table employee (
empid int,
ename varchar(10),
age int,
salary int,
deptno int,
primary key (empid),
foreign key (deptno) references department(deptno)
);
insert into employee values (201, 'Shelton', 20, 20000, 1);
insert into employee values (202, 'Michael', 19, 15000, 2);
insert into employee values (203, 'Varad', 19, 25000, 1);
insert into employee values (204, 'Raghu', 18, 20000, 4);
insert into employee values (205, 'Pavan', 21, 40000, 5);
Display emp id and emp name whose salary lies between 10,000 and 50,000.
select empid, ename from employee where salary between 20000 and 50000;
List emp name and salary for all the employee working for CS Dept.
select ename, salary from employee where deptno = 1;
Display emp name and dept name for all the Manager.
select ename, dname from employee e, department d where e.deptno = d.deptno;
Write PL/SQL program to insert a new row
DECLARE
BEGIN
INSERT INTO employee VALUES('14','Mike','28','28000','10');
COMMIT;
Dbms_output.put_line('Values Inserted');
END;
/
Count the Customers with grades above Bangalore’s average.
select grade, count (Distinct customer_id) as sum
from customer
group by
grade having grade > (select avg(grade) from customer where city = 'bangalore');
Find the name and numbers of all salesmen who had more than one customer.
select * from salesman
where 1 < (select count(*) from customer where salesman_id = salesman.salesman_id);
List all salesmen and indicate those who have and don't have customers in their cities.(Use UNION operation.)
SELECT s.salesman_id, s.name,
CASE WHEN c.customer_id IS NULL THEN 'No' ELSE 'Yes' END AS has_customers
FROM salesman s
LEFT JOIN customer c ON s.city = c.city
UNION
SELECT s.salesman_id, s.name, 'No' AS has_customer
FROM salesman s
WHERE s.city NOT IN (SELECT city FROM customer)
select s.salesman_id, s.name, c.cust_name, s.commission
from salesman s, customer c
where s.city = c.city
union
select s.salesman_id, s.name, commission
from salesman s, customer c
where not city = any(select city from customer order by city desc)
Create a view that finds the salesman who has the customer with the highest order of a day.
create view elitsalesman
as select b.ord_date, a.salesman_id, a.name
from salesman a, orders b
where a.salesman_id = b.salesman_id
and
b.purchase_amt =
(select max (purchase_amt) from orders c where c.ord_date = b.ord_date);
Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.
PL/SQL program to demonstrate %ROWCOUNT attribute
DECLARE
total_rows int;
BEGIN
UPDATE salesman SET commission = commission + 500;
IF sql%notfound THEN
dbms_output.put_line('No Salesman selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line(total_rows || ' Salesman Updated');
END IF;
END;
/
3. Insurance database
Tables and data
create table person (
driver_id varchar(10),
name varchar(10),
address varchar(10),
primary key (driver_id)
);
insert into person values ('KA01', 'Shelton', 'Dharwad');
insert into person values ('KA02', 'Michael', 'belgaum');
insert into person values ('KA03', 'Varad', 'bangalore');
insert into person values ('KA04', 'Raghu', 'mumbai');
insert into person values ('KA05', 'victor', 'goa');
create table car (
regno varchar(10),
model varchar(10),
year int,
primary key (regno)
);
insert into car values ('KA101', 'Audi', 2002);
insert into car values ('KA102', 'BMW', 2022);
insert into car values ('KA103', 'Kia', 2001);
insert into car values ('KA104', 'alpha', 2004);
insert into car values ('KA105', 'bugati', 2002);
create table accident (
report_number int,
adate date,
location varchar(10),
primary key (report_number)
);
insert into accident values (01, '01-jan-2002', 'Dharwad');
insert into accident values (02, '02-jan-2003', 'belgaum');
insert into accident values (03, '03-jan-2002', 'bangalore');
insert into accident values (04, '04-jan-2004', 'mumbai');
insert into accident values (05, '05-jan-2005', 'goa');
create table owns (
driver_id varchar(10),
regno varchar(10),
foreign key (driver_id) references person(driver_id),
foreign key (regno) references car(regno)
);
insert into owns values ('KA01', 'KA101');
insert into owns values ('KA02', 'KA102');
insert into owns values ('KA03', 'KA103');
insert into owns values ('KA04', 'KA104');
insert into owns values ('KA05', 'KA105');
create table participated (
driver_id varchar(10),
regno varchar(10),
report_number int,
damage_amount int,
foreign key (driver_id) references person(driver_id),
foreign key (regno) references car(regno),
foreign key (report_number) references accident(report_number)
);
insert into participated values ('KA01', 'KA101', 1, 2500);
insert into participated values ('KA02', 'KA102', 2, 2600);
insert into participated values ('KA03', 'KA103', 3, 1600);
insert into participated values ('KA04', 'KA104', 4, 1000);
insert into participated values ('KA05', 'KA105', 5, 500);
a. Update the damage amount for the car with a specific Regno in the accident with report number 12 to 25000.
update participated set damage_amount = 1000 where report_number = 5;
b. Add a new accident to the database.
insert into accident values (6, '05-jan-2005', 'pune');
insert into participated values ('KA05', 'KA105', 6, 1560);
Find the total number of people who owned cars that were involved in accidents in 2002.
select count(*) as No_of_people
from person pe, participated pa, accident a, owns o
where pe.driver_id = o.driver_id
and pe.driver_id = pa.driver_id
and pa.report_number = a.report_number
and a.adate between '01-jan-2002' and '01-jan-2003';
Find the total number of accidents in which cars belonging to a specific model were involved.
select count(*) from car c, participated pa
where c.model = 'bugati' and c.regno = pa.regno;
Write PL/SQL program to demonstrate exception handling for the above query
declare
temp int;
begin
select name into temp from person;
dbms_output.put_line(temp);
exception
when too_many_rows then
dbms_output.put_line('Error');
dbms_output.put_line('Change data type of temp into varchar(20)');
end;
/
4. book dealer
Tabels and rows
create table author (
author_id int,
name varchar(10),
city varchar(10),
country varchar(10),
primary key (author_id)
);
insert into author values (1101, 'Shelton', 'Dublin', 'USA');
insert into author values (1102, 'Charli', 'Miami', 'USA');
insert into author values (1103, 'Alan', 'LA', 'USA');
insert into author values (1104, 'Jake', 'Fuji', 'Japan');
insert into author values (1105, 'Walden', 'Vegas', 'USA');
create table publishers (
publisher_id int,
name varchar(10),
city varchar(10),
country varchar(10),
primary key (publisher_id)
);
insert into publishers values(1201, 'Mac', 'Fuji', 'Japan');
insert into publishers values(1202, 'Hill', 'LA', 'USA');
insert into publishers values(1203, 'Graw', 'Nord', 'Finland');
insert into publishers values(1204, 'Swan', 'Delhi', 'India');
insert into publishers values(1205, 'Peak', 'SF', 'USA');
create table category (
category_id int,
description varchar(10),
primary key (category_id)
);
insert into category values(1401, 'Psychology');
insert into category values(1402, 'Science');
insert into category values(1403, 'Arts');
insert into category values(1404, 'Literature');
insert into category values(1405, 'Astronomy');
create table catalogs (
book_id int,
title varchar(10),
author_id int,
publisher_id int,
category_id int,
year int,
price int,
primary key (book_id),
foreign key (author_id) references author(author_id),
foreign key (publisher_id) references Publishers(publisher_id),
foreign key (category_id) references category(category_id)
);
insert into catalogs values(1301, 'Gifted', 1101, 1201, 1401, 2002, 40);
insert into catalogs values(1302, 'Child', 1101, 1201, 1401, 2004, 200);
insert into catalogs values(1303, 'Maths', 1102, 1202, 1402, 2003, 20);
insert into catalogs values(1304, 'History', 1103, 1203, 1403, 2002, 10);
insert into catalogs values(1305, 'Physics', 1104, 1205, 1402, 2002, 5);
create table order_details (
order_no int,
book_id int,
quantity int,
primary key (order_no),
foreign key (book_id) references catalogs(book_id)
);
insert into order_details values(1501, 1301, 50);
insert into order_details values(1502, 1302, 100);
insert into order_details values(1503, 1303, 5);
insert into order_details values(1504, 1304, 10);
insert into order_details values(1505, 1305, 5);
Give the details of the authors who have 2 or more books in the catalog and the price of the books is greator than average price of the books in the catalog.
select * from author
where author_id in (
select author_id
from catalogs
where price > (select avg(price) from catalogs)
group by author_id having count(author_id) >= 2);
Find the author of the book, which has maximum sales.
select distinct(a.name)
from author a, order_details o, catalogs c
where a.author_id = c.author_id
and c.book_id = (select book_id from order_details group by book_id having sum(quantity) = (select max(quantity) from order_details));
Demonstrate how you increase the price of books published by a specific publisher by 10%.
update catalogs set price = price * 1.1 where publisher_id = 1201;
Write PL/SQL program illustrates how to create and call a function.
CREATE OR REPLACE FUNCTION totalAuthor
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM author;
RETURN total;
END;
/
DECLARE
c number(2);
BEGIN
c := totalAuthor();
dbms_output.put_line('Total no. of Authors: ' || c);
COMMIT;
END;
/
5. student enrollment
Tables and rows
create table students (
regno varchar(10),
name varchar(10),
major varchar(10),
bdate date,
primary key (regno)
);
insert into students values ('U01', 'Shelton', 'BCA', '10-jan-2002');
insert into students values ('U02', 'Jimmy', 'BSC', '11-oct-2003');
insert into students values ('U03', 'Michael', 'BCOM', '05-jun-2002');
insert into students values ('U04', 'Franklin', 'BTECH', '07-feb-2002');
insert into students values ('U05', 'Trever', 'BED', '01-jan-2003');
create table course (
course int,
cname varchar(10),
major varchar(10),
dept varchar(10),
primary key (course)
);
insert into course values (101, 'Java', 'BCA', 'Computer');
insert into course values (102, 'Physics', 'BSC', 'Science');
insert into course values (103, 'Advertise', 'BCOM', 'Commerce');
insert into course values (104, 'Maths', 'BTECH', 'Science');
insert into course values (105, 'Psyco', 'BED', 'Commerce');
create table enroll (
regno varchar(10),
course int,
sem int,
marks int,
primary key (sem),
foreign key (regno) references students(regno),
foreign key (course) references course(course)
);
insert into enroll values('U01', 101, 3, 9);
insert into enroll values('U02', 102, 1, 8);
insert into enroll values('U03', 103, 5, 7);
insert into enroll values('U04', 101, 6, 6);
insert into enroll values('U05', 103, 2, 5);
create table text (
book_isbn int,
book_title varchar(10),
publisher varchar(10),
author varchar(10),
primary key (book_isbn)
);
insert into text values(201, 'intro Java', 'Mac', 'Shelly');
insert into text values(202, 'Gravity', 'Graw', 'Thomas');
insert into text values(203, 'Marketing', 'Hill', 'Gupta');
insert into text values(204, 'SQL', 'Mac', 'Shelly');
insert into text values(205, 'Vectors', 'Arvind', 'Kejriwal');
create table book_adoption (
course int,
sem int,
book_isbn int,
foreign key (course) references course(course),
foreign key (sem) references enroll(sem),
foreign key (book_isbn) references text(book_isbn)
);
insert into book_adoption values(101, 3, 201);
insert into book_adoption values(102, 1, 202);
insert into book_adoption values(103, 5, 203);
insert into book_adoption values(104, 6, 204);
insert into book_adoption values(105, 2, 205);
Demonstrate how you add a textbook to the database and make this book be adapted by some department.
insert into text values (106, 'Cpp', 'Mount', 'Everest');
insert into book_adoption values (101, 3, 206);
Produce list of textbooks (include Course#, Book-ISBN, Book-title) in the alphabetical order for courses offered by the CS department that use more than two books
select c.course, t.book_isbn, t.book_title
from course c, text t, book_adoption b
where t.book_isbn = b.book_isbn
and b.course = c.course
and c.dept = 'Computer'
order by t.book_title;
List any department that has its adopted books published by a specific publisher.
select c.dept
from course c, book_adoption b, text t
where c.course = b.course
and b.book_isbn = t.book_isbn
and t.publisher = 'Mac';
Write PL/SQL program to demonstrate user defined exception handling.
DECLARE
myex EXCEPTION;
i varchar(10);
BEGIN
FOR i IN (SELECT * FROM students)
LOOP
IF i.regno = 'U03' THEN
RAISE myex;
END IF;
END LOOP;
EXCEPTION
WHEN myex THEN
dbms_output.put_line('Student number already exist in enum table.');
END;
/
DECLARE
myex EXCEPTION;
i number;
BEGIN
FOR i IN (SELECT * FROM students) LOOP
IF i.regno = 3 THEN
RAISE myex;
END IF;
END LOOP;
EXCEPTION
WHEN myex THEN
dbms_output.put_line('Student number already exist in enum table.');
END;
/
6. library management
Tables and rows
create table book (
book_id int,
title varchar(10),
publisher_name varchar(10),
pub_year int,
primary key (book_id)
);
insert into book values ( 101, 'Maths', 'Mac', 2002);
insert into book values ( 102, 'Geography', 'Graw', 2001);
insert into book values ( 103, 'Java', 'Hills', 2003);
insert into book values ( 104, 'Chemistry', 'Mount', 2002);
insert into book values ( 105, 'Physics', 'Land', 2004);
create table book_authors (
book_id int,
author_name varchar(10),
foreign key (book_id) references book(book_id)
);
insert into book_authors values (101, 'Sharma');
insert into book_authors values (102, 'Phil');
insert into book_authors values (103, 'Jake');
insert into book_authors values (104, 'Charlie');
insert into book_authors values (105, 'Mike');
create table publisher (
name varchar(10),
address varchar(10),
phone int
);
insert into publisher values ('Shell', 'Dharwad', 987);
insert into publisher values ('Mac', 'Pune', 987);
insert into publisher values ('Rag', 'Bangalore', 987);
insert into publisher values ('var', 'Mysore', 987);
insert into publisher values ('pav', 'Mangalore', 987);
create table library_branch (
branch_id int,
branch_name varchar(10),
address varchar(10),
primary key (branch_id)
);
insert into library_branch values (201, 'Science', 'Dharwad');
insert into library_branch values (202, 'Science', 'Dharwad');
insert into library_branch values (203, 'Computer', 'Dharwad');
insert into library_branch values (204, 'Science', 'Dharwad');
insert into library_branch values (205, 'Science', 'Dharwad');
create table book_copies (
book_id int,
branch_id int,
no_of_copies int,
foreign key (book_id) references book(book_id),
foreign key (branch_id) references library_branch(branch_id)
);
insert into book_copies values (101, 201, 9);
insert into book_copies values (102, 202, 5);
insert into book_copies values (103, 203, 6);
insert into book_copies values (104, 204, 7);
insert into book_copies values (105, 205, 8);
create table book_lending (
book_id int,
branch_id int,
card_no int,
date_out date,
due_date date,
foreign key(book_id) references book(book_id),
foreign key(branch_id) references library_branch(branch_id)
);
insert into book_lending values (101, 201, 301, '03-jan-2002', '13-jan-2002');
insert into book_lending values (102, 202, 302, '12-feb-2002', '22-feb-2002');
insert into book_lending values (103, 203, 303, '01-jun-2002', '11-jun-2002');
insert into book_lending values (104, 204, 304, '20-nov-2003', '30-nov-2003');
insert into book_lending values (105, 205, 305, '11-jan-2003', '21-jan-2003');
insert into book_lending values (102, 202, 301, '01-jan-2002', '11-jan-2002');
insert into book_lending values (103, 203, 301, '01-jan-2002', '11-jan-2002');
insert into book_lending values (104, 204, 301, '01-jan-2002', '11-jan-2002');
Retrieve details of all books in the library–id, title, name of publisher, authors, number of copies in each branch, etc.
select b.book_id, b.title, b.publisher_name, ba.author_name,lb.branch_name, bc.no_of_copies
from book b, book_authors ba, book_copies bc, library_branch lb
where b.book_id = ba.book_id
and ba.book_id = bc.book_id
and bc.branch_id = lb.branch_id
Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun2017
/*kuch gadabad hai*/
select card_no, count(*)
from book_lending
where date_out between '01-jan-2002' and '01-jun-2002'
group by card_no
having count(card_no) > 3
union
select card_no, count(*)
from book_lending
group by card_no
having count(card_no) > 3;
Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
delete from book_lending where book_id = 105;
delete from book_copies where book_id = 105;
delete from book_authors where book_id = 105;
delete from book where book_id = 105;
Create a view of all books and its number of copies that are recurrently available in the Library.
create view best_books as
select b.book_id, b.title, bc.no_of_copies
from book b, book_copies bc
where b.book_id = bc.book_id
Write PL/SQL program to demonstrate implicit cursor.
DECLARE
total_rows number(2);
BEGIN
UPDATE bookcp SET noof_cp = noof_cp + 10;
IF sql%notfound THEN
dbms_output.put_line('no Books selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' Books selected ');
END IF;
END;
/
7. company database
Tables and rows
create table department (
dno int,
dname varchar(10),
mgrssn int,
mgrstartdate int,
primary key (dno)
);
insert into department values (200, 'Sales', 300, 10);
insert into department values (201, 'Marketing', 301, 10);
insert into department values (202, 'RND', 306, 10);
insert into department values (203, 'Admin', 305, 10);
insert into department values (204, 'Tech', 307, 10);
create table dlocation (
dno int,
dloc varchar(10),
foreign key (dno) references department(dno)
);
insert into dlocation values (200, 'Dharwad');
insert into dlocation values (201, 'Hubli');
insert into dlocation values (202, 'Mysore');
insert into dlocation values (203, 'Goa');
insert into dlocation values (204, 'Dandeli');
create table project (
pno int,
pname varchar(10),
plocation varchar(10),
dno int,
primary key (pno),
foreign key (dno) references department(dno)
);
insert into project values (500, 'intel', 'Dharwad', 200);
insert into project values (501, 'Advertie', 'Hubli', 201);
insert into project values (502, 'Engineer', 'China', 202);
insert into project values (503, 'revenue', 'Dharwad', 203);
insert into project values (504, 'Bugs', 'dandeli', 204);
create table employee (
ssn int,
name varchar(10),
address varchar(10),
sex varchar(10),
salary int,
superssn int,
dno int,
primary key (ssn),
foreign key (dno) references department(dno)
);
insert into employee values (100, 'Jake Scott', 'Malibu', 'M', 20000, 300, 200);
insert into employee values (101, 'Charls wil', 'Malibu', 'M', 25000, 301, 201);
insert into employee values (102, 'Will dick', 'Hawkins', 'M', 30000, 302, 202);
insert into employee values (103, 'Rach Scott', 'New york', 'M', 45000, 303, 203);
insert into employee values (104, 'Shely Cout', 'Texas', 'M', 10000, 304, 204);
insert into employee values (105, 'Win Coutin', 'Texas', 'M', 10000, 305, 203);
insert into employee values (106, 'Shel Scott', 'Texas', 'M', 10000, 306, 202);
insert into employee values (107, 'S.Coutinho', 'Texas', 'M', 10000, 307, 201);
create table works_on (
ssn int,
pno int,
hourse int,
foreign key (ssn) references employee(ssn),
foreign key (pno) references project(pno)
);
insert into works_on values (100, 500, 30);
insert into works_on values (101, 501, 20);
insert into works_on values (102, 502, 25);
insert into works_on values (103, 503, 35);
insert into works_on values (104, 504, 40);
insert into works_on values (105, 503, 40);
insert into works_on values (106, 502, 40);
insert into works_on values (107, 501, 40);
Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’, either as a worker or as a manager of the department that controls the project.
select distinct(w.pno)
from works_on w, employee e, department d
where e.ssn = w.ssn
or e.superssn = d.mgrssn
and e.name like '%Scott';
Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10% raise.
select e.name, e.salary, e.salary * 1.1 as raise
from employee e, works_on w, project p
where e.ssn = w.ssn
and w.pno = p.pno
and p.pname = 'Engineer'
Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department.
select
sum(e.salary),
max(e.salary) as Max_Salary,
min(e.salary) as Min_Salary,
avg(e.salary) as Avg_Salary
from employee e, department d
where e.dno = d.dno
and d.dname = 'Marketing';
Create a view with columns dept name and dept location. Display name of dept located in ‘Dharwad’ on this view.
create view v1 as
select d.dname, dl.dloc
from department d, dlocation dl
where d.dno = dl.dno
and dl.dloc = 'Dharwad'
Write PL/SQL program to demonstrate explicit cursor.
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT ssn, name, adress FROM employee;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
