Ques:-
TEAM RESULT
A WIN
B WIN
A LOSS
A WIN
B DRAW
C WIN
C LOSS
D LOSS
E DRAW
EXPECTED OUTPUT
TEAM WIN LOSS DRAW
A 2 1 0
B 1 0 1
C 1 1 0
D 0 1 0
E 0 0 1
ANS:-
select TEAM,SUM(CASE RESULT when 'WIN' then 1
else 0 end) WIN
,SUM(CASE RESULT when 'LOSS' then 1
else 0 end) LOSS
,SUM(CASE RESULT when 'DRAW' then 1
else 0 end) DRAW
,SUM((CASE RESULT when 'WIN' then 1 else 0 end)+(CASE RESULT when 'LOSS' then 1 else 0 end)+(CASE RESULT when 'DRAW' then 1 else 0 end)) TOTAL
from xxc05_test1
GROUP BY TEAM
order by 1
----------------------------------------------------------------------
Ques:- Display the salary of each and every employee in 1991,1992 and 1993.
(hint: use 'DECODE' function)
Zigzag
NAME YEAR ATM_NO
------------- ----------- -----------------
JOHN 1991 1000
JOHN 1992 2000
JOHN 1993 3000
JACK 1991 1500
JACK 1992 1200
JACK 1993 1340
MARY 1991 1250
MARY 1992 2323
MARY 1993 8700
EXPECTED OUTPUT
YEAR JOHN JACK MARY
------------ ---------- ---------- ----------
1991 1000 1500 1250
1992 2000 1200 2323
1993 3000 1340 8700
Ans:-
SELECT YEAR,SUM(DECODE(NAME,'JOHN',ATM_NO,0)) JOHN
,SUM(DECODE(NAME,'JACK',ATM_NO,0)) JACK
,SUM(DECODE(NAME,'MERRY',ATM_NO,0)) MERRY
FROM XXC05_TEST2
GROUP BY YEAR
ORDER BY 1
----------------------------------------------------------------------
Ques:-
Write a Query to display each letter of the world “Happy” in a separate row.
(Hint: use 'SUBSTRING and CONNECT BY LEVEL')
Answer table should be
Output
result
--
H
A
P
P
Y
Ans:-
select substr('HAPPY',LEVEL,1) FROM DUAL
CONNECT BY LEVEL<=LENGTH('HAPPY');
----------------------------------------------------------------------
Ques:-
My table have some records
NAME ACT_NO
SAJAL 1234567890
AMAL 2345678901
NAVAL 9876542211
--
--
but i want to print Like thet
******7890
******8901
******2211
Ans:-
SELECT LPAD(SUBSTR(ACT_NO,LENGTH(ACT_NO)-3),LENGTH(ACT_NO),'*') FROM DUAL
----------------------------------------------------------------------
Ques:- Convert number to cahr
Ans:-
select to_char(to_date(999999,'j'),'jsp') from dual;
range is 1 to 5373484
----------------------------------------------------------------------
Ques:-
Find Nth Highest Salary
Ans:-
select * from
EMPloyees x
where &no=(
select count(*)
from EMPloyees y
where y.employee_id>=x.employee_id
)
----------------------------------------------------------------------
Ques:- Count the no of Male and Female Candidate in a table.
Ans:-
select SUM(case sex when 'MALE' then 1
end )"MALE"
,SUM(case sex when 'FEMALE' then 1
end)"FEMALE"
from XXC05_TEST3;
----------------------------------------------------------------------
Ques:- how to insert a column in a table which is already existing in other table
First you add column name in second table then
Run that query.
DECLARE
BEGIN
FOR I IN (SELECT ID,DEPARTMENT_NAME FROM XXC05_T1)
LOOP
UPDATE XXC05_T2 SET NAME = I.DEPARTMENT_NAME
WHERE ID = I.ID;
END LOOP;
COMMIT;
END;
----------------------------------------------------------------------
QUES:- HOW TO PRINT MAX,MIN,AVG,SUM OF SALARY AND PRINT ALL COLUMN NAME.
ANS:-
SELECT E.*,MAX(SALARY) OVER() MAX_SALARY ,MIN(SALARY) OVER() MIN_SALARY,ROUND(AVG(SALARY) OVER()) AVG_SALARY,SUM(SALARY) OVER() SUM_SALARY FROM EMPLOYEES E
or
SELECT
e.*,
(SELECT MAX(salary)
FROM Employees) MAXSAL,
(SELECT SUM(salary)
FROM Employees) SUMSAL
FROM Employees e;
----------------------------------------------------------------------
Ques:-
HI FRND HOW TO REPLACE
col
a*b@c#d
o/p
a,b,c,d
ANS:-
select 'a*b@c#d',regexp_replace('a*b@c#d', '[^0-9a-zA-Z]', ',' ) from dual;
or
SELECT translate('a*b@c#d','*@#',',,,') FROM dual
or
select replace (replace (replace ('a*b@c#d','*',','),'@',','),'#',',') N from dual
----------------------------------------------------------------------
Ques:- Without use rowid delete duplicate record.
ANS:-
select *
from (select e.empno, rank() over(order by e.deptno desc) rk
from scott.emp e)
----------------------------------------------------------------------
Ques:- How to find greated, smallest number
Table is
c1 c2 c3
1 4 10
2 5 15
10 20 28
Ouput is 28,1
ANS:-
SELECT MAX(GREATEST(C1,C2,C3)) FROM T1;
Or
SELECT GREATEST(MAX(C1),MAX(C2), MAX(C3)) FROM T1
SELECT LEAST(MIN(C1),MIN(C2),MIN(C3)) FROM T1;
Or
SELECT MIN(LEAST(C1,C2,C3)) FROM T1;
----------------------------------------------------------------------
QUES:- hi...frd....suppose a number is 2642.how we get output 14 (2+6+4+2) by the use of sql.
ANS:- select sum(s) total from(
select substr(num,level,1) s from(select &g num from dual)
connect by level<=length(num))
SELECT SUBSTR(7654,1,1)+SUBSTR(7654,2,1)+SUBSTR(7654,3,1)+SUBSTR(7654,4,1) FROM DUAL
----------------------------------------------------------------------
QUES:- HOW TO CHACK STRING IS PALINDROM OR NOT
DECLARE
LEN NUMBER;
PALSTR VARCHAR2(20) := '&PALSTR';
CHKSTR VARCHAR2(20);
BEGIN
LEN := LENGTH(PALSTR);
FOR I IN REVERSE 1..LEN LOOP
CHKSTR := CHKSTR||SUBSTR(PALSTR,I,1);
END LOOP;
IF CHKSTR = PALSTR THEN
DBMS_OUTPUT.PUT_LINE(PALSTR||' IS A PALINDROME');
ELSE
DBMS_OUTPUT.PUT_LINE(PALSTR||' IS NOT A PALINDROME');
END IF;
END;
----------------------------------------------------------------------
DIFFERENCE BETWEEN DATASET AND DATATABLE
DataSet:- DataSet is the Collection of DataTables
DataSet can Fetch multiple TablesRows at a time
In DataSet DataTable objects can be related to each other like(primary key, forign key, unique key etc)
DataTable:- DataTable is single datbase table
DataTable Fetch data from only one table.
As DataTable is a single database table, so there is no Data relation object in it.
----------------------------------------------------------------------
QUES:- How to find Those record in which and special symbol.
SELECT * FROM T11
WHERE REGEXP_LIKE(NAME,'%');
OR
SELECT * FROM T11
WHERE NAME LIKE '%\%%' ESCAPE '\';
----------------------------------------------------------------------
QUES:- Given String is 11223344556677 or give it at run time
output is:- 11**********77
Ans:-
SELECT RPAD(SUBSTR(&A,1,2),LENGTH(&&A)-2,'*')||SUBSTR(&&A,-2,2) FROM DUAL
----------------------------------------------------------------------
Ques:- In table data is
1 SAJAL MALE
2 AMAL MALE
3 MEENA FEMALE
4 SONAM FEMALE
5 RAHUL MALE
6 SONIA FEMALE
7 NANCY FEMALE
8 NAVAL MALE
My requirement is print record alternative of Gender
1 SAJAL MALE
3 MEENA FEMALE
2 AMAL MALE
4 SONAM FEMALE
5 RAHUL MALE
6 SONIA FEMALE
8 NAVAL MALE
7 NANCY FEMALE
ANS:-
select ROW_NUM,id,name,gender from (
select (rownum*2) ROW_NUM,id,name,gender from XXC05_GENDER
where gender = 'M'
union
select (rownum*2-1) ROW_NUM,id,name,gender from XXC05_GENDER
where gender = 'F'
)
order by ROW_NUM
----------------------------------------------------------------------
QUES:- Print 1 to 100 number in sql
ANS:-
Select Rownum
From dual
Connect By Rownum <= 100
OR
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100;
No comments:
Post a Comment