-
How to test the complex view using manually testing?
Sudhir Sorout
- Feb 4th, 2008
We can update a view using instead of trigger..... see example on url..http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls365.htm
gemini burj
- Nov 18th, 2007
Complex view means that no DML statement could be executed for this view, to test it you may descrive or select the columns from the views.
-
SQL Time Functions
How to calculate the time variation between two different time sessions?
Sudhir Sorout
- Feb 4th, 2008
Select t1- t2 from dualwhere t1 and t2 are dates then this query will return no of day not hrs.Sudhir Sorout
gbansal4
- Dec 15th, 2007
Select t1 - t2 from dualwhere t1 & t2 are dates. It will give output in hours.
-
How to execute a sql*plus script from pl/sql block.
My script contains sql plus set commands. how to execute that script in a pl/sql block i.e. either anonymous ,procedure or function or trigger
hemant.agarwal03
- Mar 17th, 2008
First you need to save your file in .sql format, suppose you create set.sql and saved it on c: drive then follow below command.SQL> @C:SET.SQL and press ENTERyour script has been executed. :-)
Susil Kumar
- Feb 20th, 2008
Let me reframe the question as follows: How to execute pl/sql block in sql*plus environment?If I am on the right path, I'll be able to give th...
-
What is Partitioning in SQL ?
venkat82.k
- Mar 19th, 2008
Hi guys partitionings nothing but a huge table is divided into some meaningfull parts to do manipulations ,for easy maintainwe divide partitions while creating tables.mainly 3 types of par...
-
Changing column name
How we can change the name of the column for a existing table?
ushalakshmi
- Apr 16th, 2008
The Column Can be changed by using the following commandsp_rename 'Tablename.OldColumnname', 'new columnname', 'COLUMN&#...
ravinder.pathania
- Feb 5th, 2008
The coloumn name can be changed by issuing the following commandalter table rename column to The feature is available after oracle 9i version
-
What is the result of the fallowing code?PieceInsert into table A value(a1):CREATE TABLE B AS SELECT * FROM A;ROLLBACK;A. Table B gets created with the row inserted in the first statement.B. Table B is not createdC. Table B gets created, but no row gets inserted into Table AD. Rollback throws up an exception.
Guest
- Jan 4th, 2007
hian empty table table (B) will be created there is a mistake un the first statementno need to commit a Create instruction, so the rollback will have no effectgood luck
-
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
-
What de-normalization and when do you do it?
sweetytrivedi
- Jun 18th, 2008
Denormalization is the process of attempting to optimize the performance of a DB by adding redundant data or by grouping data. The recommendation is always to use this for teh logical design
hari prasad
- Mar 19th, 2007
When its a large data base and our main constraint is the cost(time) than we concentrates on fast retriving .so we go for denormalization instade of joing(complex relation calculcus) too many table and be in much complexicity with hign normalization.
-
It is also mentioned that column aliases cannot be used in the WHERE clause, then how did this query worked :"select * from (select rownum rnum,a.* from testsal a) where rnum=3;".
Praveen Vaddadi
- Mar 7th, 2008
Lets examing the query as:select * from (select rownum rnum,a.* from testsal a) b where b.rnum=3;".The subquery here is used as another table for the main query and only its structure will b...
-
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
-
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 difficult SQL query you used in last job. Explain the scenario
seemu123
- Jul 1st, 2008
You can use like thisSelect top 10 customer from Databasename..TablenameunionSelect top 10 customer from Databasename..TablenameunionSelect top 10 customer from Databasename..Tablenameex:Select top 10...
kashyap_aseem
- Feb 14th, 2008
One which i faced but till yet couldnt solved was like i am having 50 db's, each db's having 100 customersi wanted top 10 customer for each 50 db's according to their salary in oraclethough it sounds easy but according to my knowledge its not so
-
Which clause you use to exclude the rows before grouping them?A. Group ByB. HavingC. WhereD. Minus
krishnaveni_g
- Jul 17th, 2008
Where clause is the correct answer. Order of execution iswheregroup byhaving
-
Table Level Lock Modes
what are the differences among these table level lock modes - IN SHARE MODE, IN SHARE UPDATE MODE, IN EXCLUSIVE MODE ?
Pintu_unix
- Jul 20th, 2008
SHARE UPDATE EXCLUSIVE This mode protects a table against concurrent schema changes and VACUUM runs. Acquired by VACUUM (without FULL), ANALYZE, and CREATE INDEX CONCURRENTLYEXCLUSIVE This ...
-
How can you create a column to be auto generated when creating a table ? is it possoble ?
mv_sivavardhan
- Aug 6th, 2008
There are two ways to auto generate the values of a column in a table1) Default value: we can set a default value to column definition2) Identity value: we can set an identity value with initial value and increment value to column definition.
-
Pivotal query
What is pivotal query?
mv_sivavardhan
- Aug 6th, 2008
Pivot is a keyword using which the values in vertical rows can be displayed as horizontal columnsEx: If there is a table with all dates of month from 1st to 30th and values against each date...
-
Void main(){int i,j,k;for(i=0;i<3;i++)k=sum(i,i);printf("n%d",k);getch();}sum(s,t){static int m;m+=s+t;return m;}
6
-
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.
-
How do you delete duplicate row from oracle table that does not have any primary key field?
raji_4u
- Sep 19th, 2008
Consider a table has two columns and no primary keyemp---------------------empno ename---------------------1 AA2 BB3 CC1 DD --> not duplicate3 ...
Lavanya Chowdary
- May 6th, 2007
We can use RowID if you are not having any primary key for column.
-
How to find out the database name from SQL*PLUS command prompt?
SQL Interview Questions
Ans