ASSIGNMENT NO 1

1. Consider the Insurance database given below. The primary keys are underlined and
the datatypes are specified:
PERSON (driver-id: string, name: string, address: string)
CAR (Regno:string,model:string,year:int)
ACCIDENT (report-number:int,date:date,location:string)
OWNS (driver-id:string,regno: string)
PARTICIPATED (driver-id:string,regno:string,report-number:int,damage-amount:int)
i) Create the above tables by properly specifying the primary keys and the foreign keys
create table arg_person (
driver_id varchar2(5) not null primary key,
name varchar2(50),
address varchar2(100)
);
Output- Table created create
table arg_car (
regno varchar2(10) not null primary key,
model varchar2(50),
year_ number(4)
);
Output- Table created create
table arg_accident (
report_number number(5) not null primary key,
date_ date,
location varchar2(100)
);
Output- Table created create arg
tablel_owns ( driver_id
varchar2(5) not null, regno
varchar2(10) , primary key
(driver_id),
foreign key (driver_id) references nil_person(driver_id),
foreign key (regno) references nil_car(regno)
);
Output- Table created create table
arg_participated ( driver_id
varchar2(5) not null, regno
varchar2(10) not null,
report_number number(5) not null,
damage_amount number(8,2),
primary key (driver_id, regno, report_number),
foreign key (driver_id) references arg_person(driver_id),
foreign key (regno) references arg_car(regno),
foreign key (report_number) references arg_accident(report_number)
);
Output- Table created
1|Page
 select * from tab where substr(tname,1,2) not in('DB','V$');
TNAME TABTYPE CLUSTERID
ARG_ACCIDENT TABLE -
ARG_CAR TABLE -
ARG_OWNS TABLE -
ARG_PARTICIPATED TABLE -
ARG_PERSON TABLE -
desc arg_person;
Column Null? Type
DRIVER_ID NOT NULL VARCHAR2(5,0)
NAME - VARCHAR2(50)
ADDRESS - VARCHAR2(100)
desc arg_car;
Column Null? Type
REGNO NOT NULL VARCHAR2(10,0)
MODEL - VARCHAR2(50)
YEAR_ - NUMBER(4,0)
desc arg_accident;
Column Null? Type
REPORT_NUMBER NOT NULL NUMBER(5,0)
DATE_ - DATE
LOCATION - VARCHAR2(100)
desc arg_owns;
Column Null? Type
DRIVER_ID NOT NULL VARCHAR2(5,0)
REGNO - VARCHAR2 (10,0)
desc arg_participated;
Column Null? Type
DRIVER_ID NOT NULL VARCHAR2 (5,0)
REGNO NOT NULL VARCHAR2(10,0)
REPORT_NUMBER NOT NULL NUMBER(5,0)
DAMAGE_AMOUNT - NUMBER(8,2)
ii) Enter atleast five tuples for each relation
insert into argl_person(driver_id, name, address) values ('d1', 'ram', 'delhi'); insert
into arg_person(driver_id, name, address) values ('d2', 'sham', 'mumbai'); insert
into arg_person(driver_id, name, address) values ('d3', 'gansha', 'hydrabad');
insert into arg_person(driver_id, name, address) values ('d4', 'charlie', 'kolkata');
insert into arg_person(driver_id, name, address) values ('d5', 'mohan', 'noida');

insert into arg_car (regno, model, year_) values ('r101', 'toyota', 2010);
insert into arg_car(regno, model, year_) values ('r102', 'honda', 2012);

2|Page
 insert into arg_car(regno, model, year_) values ('r103', 'ford', 2015);
insert into arg_car(regno, model, year_) values ('r104', 'mauruti', 2018);
insert into arg_car(regno, model, year_) values ('r105', 'nissan', 2020);
insert into arg_accident(report_number, date_, location) values (31, date
'2021-01-15',
'howrah');
insert into arg_accident(report_number, date_, location) values (32, date'2022-03-22',
'ecocity');
insert into arg_accident(report_number, date_, location) values (33, date '2023-06-30',
'lucknow');
insert into arg_accident(report_number, date_, location) values (34, date '2023-09-12',
'bangalore');
insert into arg_accident(report_number, date_, location) values (35, date '2024-02-18',
'mumbai');

insert into arg_owns(driver_id, regno) values ('d1', 'r101');

insert into arg_owns(driver_id, regno) values ('d2', 'r102');
insert into arg_owns(driver_id, regno) values ('d3', 'r103');
insert into arg_owns(driver_id, regno) values ('d4', 'r104');
insert into arg_owns(driver_id, regno) values ('d5', 'r105');

insert into arg_participated(driver_id, regno, report_number, damage_amount) values ('d1',

'r101', 31, 5000.00);
insert into arg_participated(driver_id, regno, report_number, damage_amount) values ('d2',
'r102', 32, 10000.00);
insert into arg_participated(driver_id, regno, report_number, damage_amount) values ('d3',
'r103', 33, 15000.00);
insert into arg_participated(driver_id, regno, report_number, damage_amount) values ('d4',
'r104', 34, 20000.00);
insert into arg_participated(driver_id, regno, report_number, damage_amount) values ('d5',
'r105', 35, 25000.00);
Output
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
.
.
select * from arg_person;
DRIVER_ID NAME ADDRESS
d1 ram delhi
d2 sham mumbai
d3 gansha hydrabad
d4 charlie kolkata
d5 mohan noida
select * from argl_car;

3|Page
 REGNO MODEL YEAR_
r101 Toyota 2010
r102 Honda 2012
r103 Ford 2015
r104 Mauruti 2018
r105 Nissan 2020

select * from arg_accident;

REPORT_NUMBER DATE_ LOCATION
31 15-Jan-21 howrah
32 22-Mar-22 ecocity
33 30-Jun-23 lucknow
34 12-Sep-23 bangalore
35 18-Feb-24 mumbai
select * from arg_owns;
DRIVER_ID REGNO
d1 r101
d2 r102
d3 r103
d4 r104
d5 r105
select * from arg_participated;
DRIVER_ID REGNO REPORT_NUMBER DAMAGE_AMOUNT
d1 r101 31 5000
d2 r102 32 10000
d3 r103 33 15000
d4 r104 34 20000
d5 r105 35 25000
iii) Demonstrate how you
a. Update the damage amount for the car with specific regno in accident with report
number 12 to25000
b. Add a new accident to the database update nil_participated set damage_amount =
25000.00 where regno = ‘r101’ and report_number = 31
Output
1 row(s) updated.
insert into arg_accident (report_number, date_, location) values (36, date '2024-07-20',
'chandannagar');
insert into arg_participated (driver_id, regno, report_number, damage_amount) values
('d2','r102',36,2020.00);
Output

4|Page
 1 row(s) inserted.
1 row(s) inserted.
iv) Find the total number of people who owned cars that were involved in accidents
in 2006.
select count(distinct o.driver_id) as total_people from
nil_owns o
join arg_participated p on [Link] = [Link]
join arg_accident a on p.report_number = a.report_number
where extract(year from a.date_) = 2006

TOTAL_PEOPLE
0
v) Find the number of accidents in which cars belonging to a specific model were
involved.
select count(distinct p.report_number) as num_accidents
from arg_participated p join arg_car c on [Link] =
[Link] where [Link] = 'honda';
Output
NUM_ACCIDENTS
2
2. Consider the following relations for an order processing database application in a
company.
CUSTOMER (Cust #: int, Cname: string, City: string)
ORDER (Order #: int, Odate: date, Cust #: int, Ord-Amt: int)
ORDER-ITEM (Order #: int, Item #: int, qty: int)
ITEM (Item #: int, Unit Price: int)
SHIPMENT (Order #: int, Warehouse #: int, Ship-Date: date)
WAREHOUSE (Warehouse #: int, City: string)
i) Create the above tables by properly specifying the primary keys and the foreign keys.
create table arg_customer (
custno number(10) primary key,
cname varchar2(100),
city varchar2(100)
);
Output- Table created create table
arg_warehouse ( warehouseno
number(10) primary key,
city varchar2(100)
);
Output- Table created
CREATE TABLE arg_ITEM (
ItemNo number(10) PRIMARY KEY,
UnitPrice number(8,2)
);

5|Page
Output- Table created
CREATE TABLE arg_ORDER (
OrderNo number(10) PRIMARY KEY,
Odate DATE,
CustNo number(10),
OrdAmt number(10,2),
FOREIGN KEY (CustNo) REFERENCES arg_CUSTOMER(CustNo)
);
Output- Table created
CREATE TABLE argl_ORDER_ITEM (
OrderNo number(10),
ItemNo number(10), qty
number(8),
PRIMARY KEY (OrderNo, ItemNo),
FOREIGN KEY (OrderNo) REFERENCES arg_ORDER(OrderNo),
FOREIGN KEY (ItemNo) REFERENCES argl_ITEM(ItemNo)
);
Output- Table created
CREATE TABLE arg_SHIPMENT (
OrderNo number(10),
WarehouseNo number(10),
ShipDate DATE,
PRIMARY KEY (OrderNo, WarehouseNo),
FOREIGN KEY (OrderNo) REFERENCES arg_ORDER(OrderNo),
FOREIGN KEY (WarehouseNo) REFERENCES arg_WAREHOUSE(WarehouseNo)
);
Output- Table created select * from tab where substr(tname,1,2)
not in('DB','V$');
TNAME TABTYPE CLUSTERID
ARG_ACCIDENT TABLE -
ARG_CAR TABLE -
ARG_CUSTOMER TABLE -
ARG_ITEM TABLE -
ARG_ORDER TABLE -
ARG_ORDER_ITEM TABLE -
ARG_OWNS TABLE -
ARG_PARTICIPATED TABLE -
ARG_PERSON TABLE -
ARG_SHIPMENT TABLE -
ARG_WAREHOUSE TABLE -
desc arg_CUSTOMER;
Column Null? Type
CUSTNO NOT NULL NUMBER(10,0)
CNAME - VARCHAR2(100)

6|Page
 CITY - VARCHAR2(100)
desc arg_WAREHOUSE;
Column Null? Type
WAREHOUSENO NOT NULL NUMBER(10,0)
CITY - VARCHAR2(100)
desc arg_ITEM;
Column Null? Type
ITEMNO NOT NULL NUMBER(10,0)
UNITPRICE - NUMBER(8,2)
desc arg_ORDER;
Column Null? Type
ORDERNO NOT NULL NUMBER(10,0)
ODATE - DATE
CUSTNO - NUMBER(10,0)
ORDAMT - NUMBER(10,2)

desc arg_ORDER_ITEM;
Column Null? Type
ORDERNO NOT NULL NUMBER(10,0)
ITEMNO NOT NULL NUMBER(10,0)
QTY - NUMBER(8,0)
desc arg_SHIPMENT;
Column Null? Type
ORDERNO NOT NULL NUMBER(10,0)
WAREHOUSENO NOT NULL NUMBER(10,0)
SHIPDATE - DATE
ii) Enter at least five tuples for each relation. insert into argl_customer (custno,
cname, city) values(1, 'ram', 'kolkata'); insert into arg_customer (custno, cname,
city) values(2, 'rohim', 'howrah'); insinto arg_customer (custno, cname, city)
values(3, 'shyam', 'burdwan'); insert into arg_customer (custno, cname, city)
values(4, 'mohan', 'cgandannagar'); insert into arg_customer (custno, cname, city)
values(5, 'jill', 'assansol');

insert into arg_warehouse (warehouseno, city) values(501, 'kolkat');

insert into arg_warehouse (warehouseno, city) values(502, 'mumbai');
insert into arg_warehouse (warehouseno, city) values(503, 'bangalore');
insert into arg_warehouse (warehouseno, city) values(504, 'noida');
insert into arg_warehouse (warehouseno, city) values(505, 'delhi');

insert into arg_item (itemno, unitprice) values(11, 50);

insert into arg_item (itemno, unitprice) values(12, 100);
insert into arg_item (itemno, unitprice) values(13, 150);

7|Page
 insert into arg_item (itemno, unitprice) values(14, 200);
insert into arg_item (itemno, unitprice) values(15, 250);

insert into argl_order (orderno, odate, custno, ordamt) values(101, date'2023-01-15', 1, 500);
insert into arg_order (orderno, odate, custno, ordamt) values(102, date'2023-02-20', 2,
1500); insert into argl_order (orderno, odate, custno, ordamt) values(103, date'2023-03-10',
3, 1000); insert into arg_order (orderno, odate, custno, ordamt) values(104, date'2023-04-
05', 4, 2000); insert into argl_order (orderno, odate, custno, ordamt) values(105, date'2023-
05-25', 5, 750);

insert into arg_order_item (orderno, itemno, qty) values(101, 11, 5);

insert into arg_order_item (orderno, itemno, qty) values(102, 12, 10);
insert into arg_order_item (orderno, itemno, qty) values(103, 13, 7);
insert into arg_order_item (orderno, itemno, qty) values(104, 14, 3);
insert into arg_order_item (orderno, itemno, qty) values(105, 15, 2)

insert into arg_shipment (orderno, warehouseno, shipdate) values(101, 501, date'2023-01-

16');
insert into arg_shipment (orderno, warehouseno, shipdate) values(102, 502, date '2023-02-
21');
insert into arg_shipment (orderno, warehouseno, shipdate) values(103, 503, date'2023-03-
11');
insert into arg_shipment (orderno, warehouseno, shipdate) values(104, 504, date'2023-04-
06');
insert into arg_shipment (orderno, warehouseno, shipdate) values(105, 505, date'2023-
0526');
Output
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
.
.
select * from arg_CUSTOMER;
CUSTNO CNAME CITY
1 ram kolkata
2 rohim howrah
3 shyam burdwan
4 mohan cgandannagar
5 Jill assansol
select * from arg_WAREHOUSE;
WAREHOUSENO CITY
501 kolkata
502 mumbai
503 bangalore
504 noida

8|Page
 505 delhi
select * from arg_ITEM;
ITEMNO UNITPRICE
11 50
12 100
13 150
14 200
15 250
select * from arg_ORDER;
ORDERNO ODATE CUSTNO ORDAMT
101 15-JAN-23 1 500
102 20-FEB-23 2 1500
103 10-MAR-23 3 1000
104 05-APR-23 4 2000
105 25-MAY-23 5 750
select * from arg_ORDER_ITEM;
ORDERNO ITEMNO QTY
101 11 5
102 12 10
103 13 7
104 14 3
105 15 2
select * from arg_shipment;
ORDERNO WAREHOUSENO SHIPDATE
102 502 21-FEB-23
103 503 11-MAR-23
104 504 06-APR-23
105 505 26-MAY-23
101 501 16-JAN-23
iii) Produce a listing: CUSTNAME, NO_OF_ORDERS, AVG_ORDER_AMT,
where the middle column is the total number of orders by the customer and the last
column is the average order amount for that customer.

select [Link], count([Link])

as no_of_orders, avg([Link])
as avg_order_amt from
argl_customer c
join arg_order o on [Link] = [Link]
group by [Link];
CNAME NO_OF_ORDERS AVG_ORDER_AMT
rohim 1 1500
Jill 1 750

9|Page
 shyam 1 1000
ram 1 500
mohan 1 2000

iv) List the Order# for the orders that were shipped from all the warehouses that
the company has in a specific city.
SELECT [Link]
FROM arg_shipment s
JOIN arg_warehouse w ON [Link] = [Link]
WHERE [Link] = 'kolkata'
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = (
SELECT COUNT(*)
FROM arg_warehouse
WHERE city = 'kolkata'
);
ORDERNO
101

3. Create a table Emp(e_no, e_name, e_phone, e_addr,e_salary) to store records of 10

employees:
create table arg_emp (
e_no number(10),
e_name varchar2(100),
e_phone number(10),
e_addr varchar2(255),
e_salary number(12,2)
);
Output: Table created.
desc arg_emp;

Column Null? Type

E_NO - NUMBER(10,0)
E_NAME - VARCHAR2(100)
E_PHONE - NUMBER(10,0)
E_ADDR - VARCHAR2(255)
E_SALARY - NUMBER(12,2)
i) Alter the data type of e_no from number to varchar
alter table argl_emp modify e_no
varchar2(20); Output: Table altered. desc
arg_emp;
Column Null? Type
E_NO - VARCHAR2(20)
E_NAME - VARCHAR2(100)

10 | P a g e
 E_PHONE - NUMBER(10,0)
E_ADDR - VARCHAR2(255)
E_SALARY - NUMBER(12,2)

ii) Alter table by setting e_no as primary key alter table arg_emp
add primary key(e_no); desc argl_emp;
Column Null? Type
E_NO NOT NULL VARCHAR2(20)
E_NAME - VARCHAR2(100)
E_PHONE - NUMBER(10,0)
E_ADDR - VARCHAR2(255)
E_SALARY - NUMBER(12,2)

iii) Alter table by adding a column e_pin alter table arg_emp add
e_pin number(6); desc arg_emp;
Column Null? Type
E_NO NOT NULL VARCHAR2(20)
E_NAME - VARCHAR2(100)
E_PHONE - NUMBER(10,0)
E_ADDR - VARCHAR2(255)
E_SALARY - NUMBER(12,2)
E_PIN - NUMBER(6,0)

iv) Update the phone number of an employee in the table

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e001', 'alice', '8974561230', 'kolkata', 50000, 741258);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e002', 'bob', '9874563210', 'howrah', 55000, 579631);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e003', 'smith', '7854120369', 'siliguri', 60000, 741258);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e004', 'ravi', '9123456789', 'delhi', 65000, 123456);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e005', 'sarada', '9345678901', 'mumbai', 70000, 654321);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e006', 'ram', '8456789012', 'chennai', 72000, 789012);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

11 | P a g e
 VALUES ('e007', 'david', '8567890123', 'bengaluru', 75000, 890123);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e008', 'laura', '8678901234', 'hyderabad', 77000, 901234);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e009', 'james', '8789012345', 'pune', 80000, 123789);

INSERT INTO arg_emp (e_no, e_name, e_phone, e_addr, e_salary, e_pin)

VALUES ('e010', 'olivia', '8890123456', 'ahmedabad', 82000, 456123);

SELECT * FROM arg_emp;

E_NO E_NAME E_PHONE E_ADDR E_SALARY E_PIN
E001 Alice 8974561230 kolkata 50000 741258
E002 Bob 9874563210 howrah 55000 579631
E003 Smith 7854120369 siliguri 60000 741258
E004 Ravi 9123456789 Delhi 65000 123456
E005 Sarada 9345678901 Mumbai 70000 654321
E006 Ram 8456789012 Chennai 72000 789012
E007 David 8567890123 Bengaluru 75000 890123
E008 Laura 8678901234 Hyderabad 77000 901234
E009 James 8789012345 Pune 80000 123789
update arg_emp set e_phone = '1234567890' where e_no = 'e001';
select * from arg_emp;

E_NO E_NAME E_PHONE E_ADDR E_SALARY E_PIN

E001 Alice 1234567890 kolkata 50000 741258
E002 Bob 9874563210 howrah 55000 579631
E003 Smith 7854120369 siliguri 60000 741258
E004 Ravi 9123456789 Delhi 65000 123456
E005 Sarada 9345678901 Mumbai 70000 654321
E006 Ram 8456789012 Chennai 72000 789012
E007 David 8567890123 Bengaluru 75000 890123
E008 Laura 8678901234 Hyderabad 77000 901234
E009 James 8789012345 Pune 80000 123789

Create a table Dept(dept_no, dept_name,e_no, dept_loc,dept_hod) to store records of 10

departments:
i) Create the reference between Emp and Dept table with e_no
attribute. CREATE TABLE arg_dept (
ii) dept_no NUMBER(5),
iii) dept_name VARCHAR2(100),
iv) e_no VARCHAR2(20),
12 | P a g e
 v) dept_loc VARCHAR2(100),
vi) dept_hod VARCHAR2(100),
vii) FOREIGN KEY (e_no) REFERENCES arg_emp(e_no)
viii) );Output: Table created.
ix) Assign dept_no as primary key.

alter table arg_Dept add primary key(dept_no);

desc arg_Dept;
Column Null? Type
DEPT_NO NOT NULL NUMBER(5,0)
DEPT_NAME - VARCHAR2(100)
E_NO - VARCHAR2(20)
DEPT_LOC - VARCHAR2(100)
DEPT_HOD - VARCHAR2(100)
x) Update the dept_hod for one department.

INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (1,
'medical', 'e001', 'kolkata', 'alice');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (2, 'finance',
'e002', 'bombay', 'bob');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (3,
'engineering', 'e003', 'madras', 'smith');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (4,
'marketing', 'e004', 'bangalore', 'ravi');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (5, 'sales',
'e005', 'delhi', 'sarada');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (6, 'it
support', 'e006', 'kolkata', 'ram');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (7, 'research
and development', 'e007', 'bombay', 'david');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (8,
'customer service', 'e008', 'madras', 'laura');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (9,
'logistics', 'e009', 'bangalore', 'james');
INSERT INTO arg_dept (dept_no, dept_name, e_no, dept_loc, dept_hod) VALUES (10, 'legal',
'e010', 'delhi', 'olivia');

SELECT * FROM arg_dept;

DEPT_NO DEPT_NAME E_NO DEPT_LOC DEPT_HOD
1 Medical E001 Kolkata Alice
2 Finance E002 Bombay Bob
3 Engineering E003 Madras Smith
4 Marketing E004 Bangalore Ravi
5 Sales E005 Delhi Sarada

13 | P a g e
 6 IT Support E006 Kolkata Ram
7 Research and E007 Bombay David
Development
8 Customer Service E008 Madras Laura
9 Logistics E009 Bangalore James
10 Legal E010 Delhi Olivia
update arj_dept set dept_hod = 'arghyadip' where dept_no = 1;
select * from arg_dept;

DEPT_NO DEPT_NAME E_NO DEPT_LOC DEPT_HOD

1 Medical E001 Kolkata Nilanjan
2 Finance E002 Bombay Bob
3 Engineering E003 Madras Smith
4 Marketing E004 Bangalore Ravi
5 Sales E005 Delhi Sarada
6 IT Support E006 Kolkata Ram
7 Research and E007 Bombay David
Development
8 Customer Service E008 Madras Laura
9 Logistics E009 Bangalore James
10 Legal E010 Delhi Olivia

iv) Delete one department.

delete from arg_dept where dept_no = 10;

select * from arg_dept;

DEPT_NO DEPT_NAME E_NO DEPT_LOC DEPT_HOD

1 Medical E001 Kolkata Nilanjan
2 Finance E002 Bombay Bob
3 Engineering E003 Madras Smith
4 Marketing E004 Bangalore Ravi
5 Sales E005 Delhi Sarada
6 IT Support E006 Kolkata Ram
7 Research and E007 Bombay David
Development
8 Customer Service E008 Madras Laura
9 Logistics E009 Bangalore James

5. Solve the following queries

14 | P a g e
 i) Write a query to find the employee name and dept_hod whose dept_hod is
John. select e.e_name, d.dept_hod from arg_dept d
Join arg_emp e on d.e_no = e.e_no
where d.dept_hod = 'john';
Output: No data found
And if the dept_hod named in the question is changed to Nilanjan
select e.e_name, d.dept_hod from argl_dept d
join arg_emp e on d.e_no = e.e_no
where d.dept_hod = 'arghyadip';

E_NAME DEPT_HOD
Alice Arghyadip
ii) Write a query to find the average salary of the employee of CSE department.
INSERT INTO arg_dept (dept_no,
dept_name, e_no, dept_loc,
dept_hod) VALUES (11, 'cse',
'e010', 'delhi', 'olivia');
INSERT INTO arg_dept (dept_no,
dept_name, e_no, dept_loc,
dept_hod) VALUES (12, 'cse',
'e009', 'bombay', 'bivia');

SELECT AVG(e.e_salary) AS
avg_salary
FROM arg_dept d
JOIN arg_emp e ON d.e_no =
e.e_no
WHERE d.dept_name = 'cse';
AVG_SALARY
81000

6.i) Show the records employee whose name starts with ‘a’.
select * from arg_emp where e_name like 'A%';

E_NO E_NAME E_PHONE E_ADDR E_SALARY E_PIN

E001 Alice 1234567890 kolkata 50000 741258

ii) Write a query to show the save point and rollback

operation.
begin
savepoint my_savepoint;
insert into arg_emp (e_no, e_name, e_phone, e_addr, e_salary) values ('e0015',
'charlie', '5556667777', '789 pine st',
60000);

15 | P a g e
 update arg_emp set e_salary = 58000 where e_no =
'e001'; rollback to my_savepoint; commit;
end;

select * from argl_emp;

E_NO E_NAME E_PHONE E_ADDR E_SALARY E_PIN

E001 Alice 1234567890 kolkata 50000 741258
E002 Bob 9874563210 howrah 55000 579631
E003 Smith 7854120369 siliguri 60000 741258
E004 Ravi 9123456789 Delhi 65000 123456
E005 Sarada 9345678901 Mumbai 70000 654321
E006 Ram 8456789012 Chennai 72000 789012
E007 mid 8567890123 Bengaluru 75000 890123
E008 Laura 8678901234 Hyderabad 77000 901234
E009 James 8789012345 Pune 80000 123789
E010 Olivia 8890123456 Ahmedabad 82000 456123

iii) Calculate 45 select power(4, 5) as result from dual;

RESULT
1024
v) Show system date.
select sysdate as current_date from dual;
CURRENT_DATE
27-JUL-24

16 | P a g e