-
Sql query
What is sql query to retrieve the second effective employees from job data for all the employees?
-
-
Materialised View
What is difference between a table and a materialised view, a view and a materialised view?
-
Findout the Least Null Values column and print out that column records ?
I `ve 1 table with 3 colums A,B,C
A B C
1 3 5
4 null 6
8 2 null
null 1 null
null null 9
5 null 6
3 5 7
null null 3
Paurian
- Feb 17th, 2016
A surprise to me, using CASE statements was slightly faster, and had the exact same execution plan. People would prefer this code over the SUM( ISNULL( ... ) ) answer because the intent is more clearl...
vinay
- Feb 16th, 2016
It should print out null 1 nll
-
Question on Constraints
I am new to Oracle. I have created one table in that one column name salary, while creating the table I have given the datatype for that column is varchar2 and also I have given not null constraint. Now I want to change the datatype varchar2 to number, Is it possible to alter the datatype when having constraint in table?
DevaNaidu
- Mar 7th, 2018
If you want change only varchar2 to number you can change by using ALTER, but you also defined NOT NULL constraints..so if you need to change a datatype along with Not null..you must use MODIFY key word
Abhimanyu eati
- Nov 17th, 2017
Yes we can change datatype but make sure about size if you have created varchar(5) then alter to number(5)
-
Write a query for split one column to multiple columns
I have a source table with one column of 15 characters and I want output like divide the 15 chars column into multiple of 3 columns like every column 5 chars
source empnoorderprdct target empno order prdctDivya
- Jun 5th, 2017
You can use Substring function to split the column values to 3 columns
-
Alternative NULL values
Source:
col1 col2
1 Will
2 John
3 Josh
4 Devin
Target:
col1 col2
1 Will
2 NULL
3 John
4 NULL
5 Josh
6 NULL
7 Devin
8 NULL
I want the SQL query for the... -
What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
-
What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
jpundalik
- Dec 30th, 2009
This is used enforce the constraints to delete the chaild records when corresponding parent records are deleted.This can be explained with below example- Parent table has department details creat...
Thanagdurai
- Jul 25th, 2007
Hi,Deletes all Foreign keys that reference the table to be dropped, then drops the table. RedsTahngam.
-
What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution
Bhuvaneswari.V
- Aug 22nd, 2009
_ for matching single characters% for matching mutiple characters[] for matching either characters[^] for not matching characteregTable having a column name thenName LIKE 'A_' returns the name...
pujavipin
- Jun 4th, 2009
% = for matching one or more characters._ = for matching excatly one character.[abc]= for matching either 'a' or 'b' or 'c'.[^abc]= for matching character other than 'a','b'and 'c'.
-
Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS
psingla
- Mar 5th, 2010
For SQL Server users:SELECT object_name(id) objname,object_name(constid) constraintname,* from sysconstraints where object_name(id)= ''
malapati
- Oct 8th, 2008
Desc user_constraints;to see constraints on a particular table like emp select constraint_name,constraint_type,table_name from user_constraints;
-
Main(int argc,int *argv[]){int i;for(i=1;i<argc; i++)printf("n%s%s",argv[i],(i<argc-1)?"":"");return 0;getch();}
I work for oracle
chaitanya_chitrala
- Jun 25th, 2008
As the value in argc is 4 ,the value till 2 char is printed from the input string therefore the output is :I Work
Amee Thaker
- Sep 5th, 2007
It will print all the parameters passed.
-
What is the output of the programvoid main(){int i,j,k,n=5;clrscr();for(i=5;i>0;i--){j=1<i;k=n&j;k==0?printf("0"):printf("1");}getch();}A. 00011B. 11110C. 11001D. 11100
B
Bhoomika sharma
- Dec 16th, 2008
Although we may think that the code contains errors but its logical approach,We have a for-loop in which i is initialized to 5. The value of j =1 if 1<i i.e. it is valid for i=5,4,3,2 and the truth...
aniljoo
- Feb 12th, 2007
Answer should be 11110
-
How many columns are retrieved from this query:SELECT address1 || ',' ||address2 ||',' ||address2 "Address" FROM = employee;A. 3B. 2C. 1D. 0
JohnCenzer
- Nov 30th, 2006
I think there is some confusion between "retrieve" and "return".The query retreives 2 columns from the table - address1 and address2, and returns 1 column "Address"
Jim Abraham
- May 27th, 2006
All the addressed rows in the select statement are concatenated and renamed as "Addresss". So only 1 column will be reflected in the result.So answer is C
-
Can we call user defined packages in SQL statements?
FALAK
- Apr 25th, 2006
A user defined package can be invoked in sql only if we r using it to call public functions or variables.but packaged procedures can not be called from sql
Mainak Aich
- Mar 17th, 2006
Hi,Everyone forgot to point out 1 thing,which is most essential to use a user defined package which is he/she must have the execute permission to use the package if he/she is not the creator of that package.If it is ok definitely an user defined package can be used.
-
What is the main difference between the IN and EXISTS clause in subqueries??
The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result...
gemini burj
- Oct 23rd, 2007
In case of a big number of list from the select list IN operator should not be used but the exists shall be used with the proper condition this will definetely make the query optimal query.
Mahesh
- Jul 24th, 2007
In operaor scan all the values inside the IN block where as EXIST quit after 1st occurrence.
-
while this query dint work :
"select * from (select rownum,a.* from emp a) where rownum=70;"
2. as i've read that rownum can work only for < and <= but not for = or > or >=, then why the query worked for rnum=70">1. Why is it so that the given query worked :"select * from (select rownum rnum,a.* from emp a) where rnum=70; "while this query dint work :"select * from (select rownum,a.* from emp a) where rownum=70;"2. as i've read that rownum can work only for < and or >=, then why the query worked for rnum=70
krishnaindia2007
- Jun 21st, 2008
ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. If you directly user rownum in the outer query it considers the rownum of outer query not that of inner query.
Lavanya Chowdary
- May 6th, 2007
It will work for = operator But it will work for the first row in the table pls check that
-
How to tune the following sql in order that the performance improve the query is SELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_ITEM_ACTIVITY A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'UNIONSELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_PENDING_ITEM A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'AND A.POSTED_FLAG = 'N'AND ERROR_FLAG = ' '
raji_4u
- Sep 19th, 2008
Remove union and place union allMake sure the where conditions is in order of1. =2. >3. 4. like.
Make sure that the condition that returns least records comes first.zskhan
- Oct 12th, 2006
Remove DISTINCT from both queries.Since UNION is being used, we are going to get distinct rows anyway and queries with distinct are quite expensive.
-
Employee Query question
Suppose im having employee table with fields, eno, ename, dept, address1, address2, address3. In address field employee can fill only address1 or address2 or address3... at a time he can fill three address fields. now i want all employee names who filled only one address field..
Ans