Oracle Exercise

Learn how to create a table in Oracle.
How to insert, update and delete rows in Oracle,
How to add constraints like primary key, foreign key etc in Oracle,
How to create indexes in oracle,
How to grant permission to other user in Oracle on a table,

Below is the complete assignment :

Q1. Create all the tables in Oracle iSQL+

create table part
pno number(3) constraint pk_part primary key,
pname varchar2(20) constraint nn_pname not null,
colour varchar2(10),
weight number(2)

create table supplier
sno number(3) constraint pk_supplier primary key,
sname varchar2(20) constraint nn_sname not null,
scity varchar2(20),
manager varchar2(20)

create table project
prno number(3) constraint pk_project primary key,
prname varchar2(20) constraint nn_prname not null,
pr_st_date date,
pr_manager varchar2(20)

create table supply
pno number(3) constraint nn_pno not null,
sno number(3) constraint nn_sno not null,
qty number(3),
sdate date,
constraint fk_pno foreign key(pno) references part(pno) on delete cascade,
constraint fk_sno foreign key(sno) references supplier(sno) on delete cascade,
constraint pk_supply primary key(pno, sno)

Q2. Add the column 'scountry' to the table supplier

alter table supplier
add(scountry varchar2(20))

Q3. Display suppliers who have either an 'n' or a 'r' somewhere in their name

select * from supplier
sname like '%n%'
sname like  '%r%'

Q4. Display details of quantity supplied by suppliers located in china

select * from supply inner join supplier on supply.sno = supplier.sno
upper(scountry) = 'CHINA'

Q5. Display the supplier name, partname, quantity for project mark 42

select sname, pname, qty from supply
inner join supplier on supplier.sno = supply.sno
inner join part on part.pno = supply.pno
inner join project on project.prno = supply.prno
upper(prname) = 'MARK 42'

Q6. display the no. of supplier in each city

select scity, count(sno) from supplier group by scity

Q7. display the no of parts in asc order of color and within color desc order of weight

select * from part order by colour asc, weight desc

Q8. Grant the select, insert and update privileges to user 'cse01' on table 'supply' and make sure that he can further give rights on this table to other users too.

grant select, insert, update on supply to mca202 with grant option

Q9. Create an unique index on the supplier name

create index ix_supplier on supplier(sname)

Q10. display details of projects which starts in the month of january and utilize part no. P2

select * from supply
inner join project on project.prno = supply.prno
where to_char(sdate, 'mon') = 'jan'
pno = 2

Q11. display the total quantity of part "Metallic Saws" supplied to project "ultima"

select sum(qty) from supply
inner join part on part.pno = supply.pno
inner join project on project.prno = supply.prno

upper(pname) = 'BRAIN'
upper(prname) = 'MARK 42'

Q12. Display all those suppliers who are located in the same city address that of "Manager Jayesh" or are supplying part P99

select * from supplier
inner join supply on supply.sno = supplier.sno
inner join part on supply.pno = part.pno
where pname='P2'

Q13. Display all those parts which are still not supplied to any project

select * from part where pno not in (select pno from supply group by pno)

Post a Comment