How to Get the employee details whose salary is less than the average salary of the department.

dj_dj_dj

 Profile Answers by dj_dj_dj Questions by dj_dj_dj

• May 20th, 2010

select emp_name, avg(salary)from employees group by emp_namehaving avg(salary) < (Select avg(salary)                &...

g.sreemayee

 Profile Answers by g.sreemayee Questions by g.sreemayee

• May 19th, 2010

SELECT * FROM emp e WHERE sal> (SELECT AVG(sal) FROM emp GROUP BY deptno HAVING deptno=e.deptno) ORDER BY deptno;

Answer Question  Select Best Answer

We are UPDATING a field in SQL and ALTER the row also. After giving the COMMIT command the system is crashed. What will happen to the commands given, whether it will UPDATE and ALTER the table or not?

neha.awasthi1

 Profile Answers by neha.awasthi1 Questions by neha.awasthi1

• May 30th, 2011

Since alter command is DDL and DDl are self commit command so the changes will appear.

Raj_P

 Profile Answers by Raj_P Questions by Raj_P

• Sep 1st, 2010

It depends on the sequence of the transactions. 1) DML -----> DDL -----> Crash = You will get the data.2) DDL -----> DML -----> Crash = You will lost the data.

Answer Question  Select Best Answer

SQL query to replace every alternate character in a string to capital.

Input string:- govardhana
I need output as gOvArDhAnA
And vice versa i.e, GOVARDHANA and required output as GoVaRdHaNa

Provide 2 separate queries for above scenarios

Vishnu

• Jun 23rd, 2016

Code
 

Vishnu

• Jun 23rd, 2016

For Instance consider EMP Table.

Code
 

Answer Question  Select Best Answer

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDEINSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

Nicsmart

 Profile Answers by Nicsmart Questions by Nicsmart

• Mar 31st, 2010

SUBSTR------The substr functions allows you to extract a substring from a string.The syntax for the substr function is:substr( string, start_position, [ length ] )string is the source string.  st...

ashish88

 Profile Answers by ashish88 Questions by ashish88

• Jun 10th, 2008

hi Buddy,   Substr only give the sub part of the stringSubstr(String,'start postion','length')Substr('ABCDEFAG',3,7); /*out put is =cdefag */Instr to give only possio...

Answer Question  Select Best Answer

sanjay mehra

• Apr 3rd, 2020

Not possible

prerrak_pandya

• Mar 6th, 2020

Thank_you, its really worked!
its make my day,
something awesome new to learn

Answer Question  Select Best Answer

sanjay pradhan

• Sep 26th, 2014

Alter trigger trigger_name disable

vijaya

• May 11th, 2013

Alter trigger trigger_name disable/enable all triggers.

Answer Question  Select Best Answer

aytasirs

 Profile Answers by aytasirs Questions by aytasirs

• Nov 15th, 2012

Use DML Triggers to apply the same action to another table as well.

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

Write trigger

Answer Question  Select Best Answer

Ekta Raina

• Jul 14th, 2011

Transparent Network Substrate

mselva84

 Profile Answers by mselva84 Questions by mselva84

• Jun 28th, 2010

Oak Ridge Automatic Computer and Logical EngineRegards,Manikandan SelvarajandB Professionals India Pvt LimitedChennai (TN)India.

Answer Question  Select Best Answer

SELECT * FROM empWhere emp_no+' '=12345;i.e you have to concatenate the column name with space within codes in the where condition.SELECT /*+ FULL(a) */ ename, emp_no from empwhere emp_no=1234;i.e using HINTS

jyothi_rayala

 Profile Answers by jyothi_rayala Questions by jyothi_rayala

• Jun 11th, 2009

One way is to diable the index on the column using syntaxAlter index indexname on table_name disable.One way is to apply  /*+no_index */ hint to the query For eg: select ename /*+no_index */from empwhere ename ='smith'

Srini

• Oct 10th, 2007

Its so distressing that no body could provide a better answer than a novice like me.In the where clause change the order of conditions in such a way that:? 5 = value cond1 & value =5or value + 1 = 5

Answer Question  Select Best Answer

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))from emp;the output like,SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))--------- -----------------------------------------------------800 eight hundred1600 one thousand six hundred1250 one thousand two hundred fiftyIf you want to add some text like, Rs. Three Thousand only.SQL> select sal "Salary ",(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))"Sal...

Talal Haider

• Mar 16th, 2013

Can we convert a number in words without using direct function/method?

boobal

• Mar 21st, 2012

This answer is no different to
select to_char(to_date(1290,j),jsp) from dual;

Answer Question  Select Best Answer

saradajasti

 Profile Answers by saradajasti Questions by saradajasti

• Mar 27th, 2008

Here CNT is a column name SELECT CNT FROM table_nameWHERE MOD(CNT,CNT) = 0AND MOD(CNT,2) != 0AND MOD(CNT,3) != 0AND MOD(CNT,5) != 0AND MOD(CNT,7) != 0AND MOD(SQRT(CNT),ROUND(SQRT(CNT))) != 0ORDER BY CNT

saradajasti

 Profile Answers by saradajasti Questions by saradajasti

• Mar 26th, 2008

SELECT CNT FROM table_nameWHERE MOD(CNT,CNT) = 0AND MOD(CNT,2) != 0AND MOD(CNT,3) != 0AND MOD(CNT,5) != 0AND MOD(CNT,7) != 0ORDER BY CNT

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Jayakumar M

• Nov 8th, 2005

---

Yes. We can call Functions from SQL statements.

To be callable from SQL statements, a stored function must obey the following

"purity" rules, which are meant to control side effects:

 When called from a SELECT statement or a parallelized INSERT, UPDATE, or

DELETE statement, the function cannot modify any database tables.

 When called from an INSERT, UPDATE, or DELETE statement, the function

cannot query or modify any database tables modified by that statement.

 When called from a SELECT, INSERT, UPDATE, or DELETE statement, the

function cannot execute SQL transaction control statements (such as COMMIT),

session control statements (such as SET ROLE), or system control statements

(such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as

CREATE) because they are followed by an automatic commit.

planet100

 Profile Answers by planet100 Questions by planet100

• Apr 19th, 2011

I have a question... what if the function returns long??i am not able to use in the select query.It says " ORA-00997: illegal use of LONG datatype " .Is there any other way to address this, ...

nis1981

 Profile Answers by nis1981 Questions by nis1981

• Aug 3rd, 2008

Yes, you can call function in sql query, these functions should be in-built or predefined in the DBMS.

Improve Answer

jayshree13

 Profile Answers by jayshree13 Questions by jayshree13

• May 13th, 2011

Select e.ename,e.deptno,d.deptno from emp e,dept d where e.deptno=d.deptno(+);

srividhya_85

 Profile Answers by srividhya_85 Questions by srividhya_85

• Aug 5th, 2008

Select emp_name from emp awhere not exists(select 1                         ...

Answer Question  Select Best Answer

upendra

• Jun 11th, 2015

Sub Query: Inner query is executed first then it gives to outer query and outer query is executed.

Correlated Query: Outer query executed first row by row then it based on inner query is executed.

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Feb 24th, 2010

subquery : returns value to main query. it wont get values from main query. Example: SELECT id "Emp #" First_name "Name" salary "Salary" ROWNUM rank FROM (SELECT id first...

Answer Question  Select Best Answer

aytasirs

 Profile Answers by aytasirs Questions by aytasirs

• Nov 15th, 2012

Explain plan will tell you

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

Use select may be this works

Answer Question  Select Best Answer

10 rows

jasvinderss

 Profile Answers by jasvinderss Questions by jasvinderss

• Jun 30th, 2008

There could be two cases(1) if you supply the query as Select * from A,Byou have not mentioned any join condition in this case 10 rows will be selected.(2)if you supply a join condition with the above queries with a common filed between them, then in that case no rows will be selcted.

nandk.sharma

 Profile Answers by nandk.sharma Questions by nandk.sharma

• Jun 27th, 2008

No rows will be retrieved.The Cartesian product will only yield records when there is atleast one record in both the tables i.e.   A and B.

Answer Question  Select Best Answer

krishsidd

 Profile Answers by krishsidd Questions by krishsidd

• Nov 26th, 2010

CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL...

yagneswarasankar

 Profile Answers by yagneswarasankar Questions by yagneswarasankar

• Nov 24th, 2010

  1  select manager_id,last_name,count(employee_id) over (partition by manager_id ) cnt from employe  2* order by cntSQL> /MANAGER_ID LAST_NAME      &nb...

Answer Question  Select Best Answer

aytasirs

• Apr 29th, 2013

Yes We can. If we use the combination "unique+not null" the referential integrity is not maintained and that is not the case with primary key.

EHSAN REHMANI

• Apr 27th, 2013

There is only one primary key per table but we can use unique and not null constraints more than one column in a table. Primary key having both functionality as it can not be null but unique.

Answer Question  Select Best Answer

We have two type of constraints - Table Level and Column Level. Which is better and why..?

Mohit

• Oct 3rd, 2015

I think Table level contraints are imposed when we have to compare values in multiple columns,suppose delivery_date cannot be less than order_date..

abhilashnanupillai

 Profile Answers by abhilashnanupillai Questions by abhilashnanupillai

• Feb 20th, 2009

Constrains are the key word along with create table command. Different type of table constraints are primary key not null unique etc.

Answer Question  Select Best Answer

srini

 Profile Answers by srini Questions by srini

• Dec 6th, 2013

Using PIVOT we can do this:

http://www.dba-oracle.com/t_pivot_examples.htm

VIJAY SHARMA

• Sep 24th, 2012

SELECT SYS_CONNECT_BY_PATH(DEPTNO, )I ,
SYS_CONNECT_BY_PATH(DNAME, ) II,
SYS_CONNECT_BY_PATH(LOC, ) III
FROM DEPT
WHERE LEVEL = &TOTALDEPARTMENTS
START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO < DEPTNO;

Answer Question  Select Best Answer