Write an SQL statement to create user-defined data types?

jpundalik

 Profile Answers by jpundalik Questions by jpundalik

• Dec 29th, 2009

Below is the syntax can be used for creating user defined data type in Oraclecreate or replace type type_name as object(add fields and types..)Example:create or replace type address as object(addr1 varchar2(100),City varchar2(30),phone varchar2(15));

shivakrishna.ganji

 Profile Answers by shivakrishna.ganji Questions by shivakrishna.ganji

• Dec 20th, 2009

Select column_name as User_defined_name from table_name;

Answer Question  Select Best Answer

What do you mean by LOB segment? Explain LOB segment

smonroy

 Profile Answers by smonroy Questions by smonroy

• Sep 27th, 2011

You are talking about oracle, LOB is simply a pointer. It points to an index.

sandeep.katta

 Profile Answers by sandeep.katta Questions by sandeep.katta

• Feb 7th, 2011

LOB is a pointer which points to  the Index

Answer Question  Select Best Answer

robindas

 Profile Answers by robindas Questions by robindas

• Jan 11th, 2013

Few steps for query tuning. 1) Check for if any sub query or exists or not exists is there if yes then try to change it in the joins 2) Try to check the way joins had been written try to put it in f...

samareshoracle

 Profile Answers by samareshoracle Questions by samareshoracle

• Aug 4th, 2012

Following a Tuning Methodology Objectives:: Determine performance problems Manage performance Describe tuning methodologies Identify goals for tuning Describe automatic SQL tunin...

Answer Question  Select Best Answer

I have table .. like

EMPNO Ename DOB
------------------------------------
1 you 1-jan-2011
2 Me 28-jan-2011

tel me the query for no.of days different from.. two dates of column DOB.?

Thanks in advance.
Sateesh

skcwebworld

 Profile Answers by skcwebworld Questions by skcwebworld

• Aug 2nd, 2015

Code
 

kirathakudu

 Profile Answers by kirathakudu Questions by kirathakudu

• Dec 13th, 2011

Select date1-date2 from tablename;

If the above value is negative it means date 1 is older than date 2

Answer Question  Select Best Answer

What is difference b/w coreleated subquery and nested subquery?

pradeep

• Nov 21st, 2013

A outer query out put is input of inner query

Vikas

• Jul 31st, 2013

Co-related sub query give reference to outer main query but nested sub query will have independent result set to be passed to outer query.

Answer Question  Select Best Answer

Can we make DML operations on base tables using a simple view?Can we insert or update or delete rows to the base tables using a view?

lviswanath

 Profile Answers by lviswanath Questions by lviswanath

• Jul 22nd, 2013

Of course we can do anything i.e. updating/deleting/inserting on a base table underlying a simple view, until unless its covering NOT NULL columns of the table in the query of the simple View.

ppad001

 Profile Answers by ppad001 Questions by ppad001

• Feb 27th, 2013

Yes we can use DML operations on single table view.

Answer Question  Select Best Answer

What is sql query to retrieve the second effective employees from job data for all the employees?

sathis raja

• Sep 11th, 2014

We can get 2nd maximum salary of the employees.

VIBHAV MISHRA

• Jun 30th, 2014

Select max(Sal) from Emp Where Sal not in(Select max(Sal) from Emp)

Answer Question  Select Best Answer

Nilesh Rathod

• Mar 17th, 2015

Simply using group by and Having clause.

Code
 

Nilesh Rathod

• Mar 17th, 2015

Simply Using group by and having clause...

Code
 

Answer Question  Select Best Answer

What is difference between a table and a materialised view, a view and a materialised view?

Pooja

• May 12th, 2016

Materialised views do not have the same rowid as that of the actual table wherease views have the same rowid.
Materialised views are faster because indexing can be applied since the result is stored in a db unlike view which is a logical query executed everytime.

sonali Kumari

• Jan 15th, 2016

Unlike views, however, materialized views also store the results of the query in the database. However the rowids for the table and the view are identical, indicating the view returns the exact same ...

Answer Question  Select Best Answer

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

 Profile Answers by Paurian Questions by 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

Answer Question  Select Best Answer

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

 Profile Answers by DevaNaidu Questions by 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)

Answer Question  Select Best Answer

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 prdct

Divya

• Jun 5th, 2017

You can use Substring function to split the column values to 3 columns

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Jul 12th, 2016

Please use MID() function to extract characters from a field.

Code
 

Answer Question  Select Best Answer

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...

SANTHOSH KUMAR GUJJA

• Nov 26th, 2017

SEL CASE WHEN COL1=1 THEN COL1 ELSE COL1+MIN(COL1) OVER(ORDER BY COL1 ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) END AS
COL1,COL2 FROM TABLE
UNION
SEL COL1,NULL AS COL2 FROM TABLE WHERE COL11

Rakesh

• Mar 2nd, 2017

SELECT COL1, CASE
WHEN MOD(COL1,2) = 0 THEN NULL
ELSE
COL2
END CASE
FROM G2

Answer Question  Select Best Answer

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.

chengiri

 Profile Answers by chengiri Questions by chengiri

• Sep 3rd, 2009

Insted of NVL use SELECT SAL + isnull(COMM,0) FROM EMP which will convert null values to 0 of the field COMM

padma.rkp

 Profile Answers by padma.rkp Questions by padma.rkp

• Apr 28th, 2007

GIVES ADDED VALUE OF SAL & COMM FOR EACH EMPLOYEE

Answer Question  Select Best Answer

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

jpundalik

 Profile Answers by jpundalik Questions by 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.

Answer Question  Select Best Answer

_ for single character substitution and % for multi-character substitution

Bhuvaneswari.V

 Profile Answers by Bhuvaneswari.V Questions by 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

 Profile Answers by pujavipin Questions by 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'.

Answer Question  Select Best Answer

USER_CONSTRAINTS

psingla

 Profile Answers by psingla Questions by psingla

• Mar 5th, 2010

For SQL Server users:SELECT object_name(id) objname,object_name(constid) constraintname,* from sysconstraints where object_name(id)= ''

malapati

 Profile Answers by malapati Questions by 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;

Answer Question  Select Best Answer

I work for oracle

chaitanya_chitrala

 Profile Answers by chaitanya_chitrala Questions by 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.

Answer Question  Select Best Answer

B

Bhoomika sharma

 Profile Answers by Bhoomika sharma Questions by 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

Answer Question  Select Best Answer

JohnCenzer

 Profile Answers by JohnCenzer Questions by 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

Answer Question  Select Best Answer