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
where
sname like '%n%'
or
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
where
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
where
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'
and
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

where
upper(pname) = 'BRAIN'
and
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