Scribd
Upload
36 views9 pages

1A) Select Decode (Val, Greatest (Val, 0) ,'POSITIVE','NEGATIVE') From Tester1

The document contains 8 sections that provide SQL queries and results. The queries perform operations like selecting, filtering, updating, joining, and transforming data from various tables. Sample tables include customer, product, sales, student, region, and tax. The queries demonstrate SQL functions like DECODE, LEAST, GREATEST, CASE, TRANSLATE, REGEXP_SUBSTR, and more.

Uploaded by

Sivareddy Dodda
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
36 views9 pages

1A) Select Decode (Val, Greatest (Val, 0) ,'POSITIVE','NEGATIVE') From Tester1

The document contains 8 sections that provide SQL queries and results. The queries perform operations like selecting, filtering, updating, joining, and transforming data from various tables. Sample tables include customer, product, sales, student, region, and tax. The queries demonstrate SQL functions like DECODE, LEAST, GREATEST, CASE, TRANSLATE, REGEXP_SUBSTR, and more.

Uploaded by

Sivareddy Dodda
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 9

ASSIGNMENT-2

1A)select
decode(val,greatest(val,0),'POSITIVE','NEGATIVE')from tester1;

DECODE(V
--------
POSITIVE
NEGATIVE
POSITIVE
NEGATIVE
2A) select distinct least(val1,val2)val,greatest(val1,val2)val,val3
from tester2 order by val3;

VV VAL3
- - ----------
AB 10
CD 20
EF 20
AE 25
3A) update tester3 set gender =(case gender when 'M' then 'F'
else 'M' end);
select * from tester3;

EMPID G
---------- -
1F
2M
3M
4F
5M
4A)select customer.cid,product.pid,sum(nvl(quantity,0)) as
totalquantity from
customer cross join product left outer join sales on
(product.pid=sales.pid and sales.cid=customer.cid) group by
customer.cid,product.pid order by cid;

CID PID TOTALQUANTITY


---------- ---------- -------------
1 10 60
1 20 30
1 30 0
2 10 10
2 20 0
2 30 60
3 10 15
3 20 0
3 30 50
4 10 0
4 20 0
CID PID TOTALQUANTITY
---------- ---------- -------------
4 30 0

5A) select translate(input,' 12$3-#89',' ') from


(select '&val' input from dual);
Enter value for val: x12abde$13-#89
old 2: (select '&val' input from dual)
new 2: (select 'x12abde$13-#89' input from dual)

TRANS
-----
xabde
6A) delete from student where rowid not in
(select max(rowid) from student group by id);

select * from student;

ID N
---------- -
1A
4D
2B
3C
7A) select id,name,
case when name='N' then 1 else 0 end n,
case when name='E' then 1 else 0 end e,
case when name='W' then 1 else 0 end w,
case when name='S' then 1 else 0 end s
from region order by id

ID N N E W S
--------- - ---------- ---------- ---------- ----------
1N 1 0 0 0
2E 0 1 0 0
3W 0 0 1 0
4W 0 0 1 0
5S 0 0 0 1
6N 1 0 0 0
8A)
select distinct id,
regexp_substr(name,'[^,]+',1,level) value from tax connect
by level<=regexp_count(name,'[,]')+1 order by id;

ID VALUE
---------- ----------------------------------------
1A
1B
1C
2D
2E
2F
3A
3C
3D

You might also like