-
What are the types of SQL statement
csbhaskar
- Mar 25th, 2011
SQL statements can be subdivided into the following six types:1. Data Definition Language (DDL) statements let you to perform these tasks: CREATE,...
majidwgl
- Dec 5th, 2009
Data Definition Language.Data Manipulation Languge.Data Transaction Control Language. Data Manipulation Control Language.Data session control. Data system control. etc these are SQL types statements.
-
Display the records between two range I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query
katgeektalk
- Aug 26th, 2010
If the values in the field comm both NULL and 0 the query should beselect empno, decode(nvl2(comm, to_char(comm), 'N/A'), '0', 'N/A')from scott.empNVL2 to convert NULL value into N/ADecode to convert 0 value into N/A
emraan
- Dec 29th, 2009
1) SELECT e.*, nvl2(comm, 'Not Null', 'Null') FROM emp e 2) SELECT e.*, decode(comm, '', 'Is Null', 'Is not Null') FROM emp e
-
Display the number value in Words
bhaskar1981buie
- Dec 4th, 2007
The number which have precesion can be spelled in words by this way..SELECT DECODE(SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),0,11,DECODE(LENGTH('1256932.1')...
-
If a View on a single base table is manipulated will the changes be reflected on the base table ?
If changes are made to the tables which are base tables of a view will the changes be reference on the view.
-
Default Constraint
What is default constraint? How we you apply it?
Sam Kolta
- Apr 25th, 2010
1. What is default constraint? Constraints are specific values that used to limit the type of data that can go into a table by ensure certain columns have a true condition when data is inserting, modi...
gdurga
- Jun 15th, 2009
A coulumn can be given a default value by using DEFAULT option. This option prevents null values from entering the column if a row is inserted with out a value in to the table.Ex: .......Hire_date DATE DEFAULT SYSDATE,.......
-
What are the more common pseudo-columns
Answer posted by Scott on 2005-05-25 18:43:29: SELECT rownum, rowid FROM sometable
-
What is a join
Read Best Answer
Editorial / Best Answer
Answered by: NK
- Jun 29th, 2005
A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables.
csbhaskar
- Apr 7th, 2011
A join is a query that results in combining rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever names of multiple tables, views or materialized views a...
nirmal1in
- Jan 3rd, 2010
Join is relation between more than one table. No of minimum join conditions required is equal to n-1 (where n is number of tables joined together)
-
What is the sub-query
Read Best Answer
Editorial / Best Answer
Answered by: Sailaja Pasupuleti
- Dec 4th, 2005
A sub-query is a part of query. Sub-queries are useful to find unknown value(s).
Ex: To find details of employees who are getting maximum salary.
In this requirement, the maximum salary not mentioned.
Solution:
To find maximum salary,
select max(sal) from emp;
For that maximum salary the employee details,
select * from emp where sal = (select max(sal) from emp);
So, in the above query, "select max(sal) from emp" is also a query but it returns a value (unknown or not given) and compares with "sal" column. This query is called sub-query.
-
If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE
Gayathri Panchangam
- Sep 4th, 2008
Yes.Becoz sysdate contains date and time.For example: if u create a table like thiscreate table ex(id number,date1 date defualt sysdate unique);u get table created. After that if u insert rows in that...
-
What is CYCLE/NO CYCLE in a Sequence
Answer posted by Scott on 2005-05-25 18:39:48: When you create a sequence with CYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will start over at the MINVALUE. This is not wise if using the sequence for primary key creation. When you create a sequence with NOCYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will NOT start over at the...
-
-
How to grant all permission of one user to another user.
abhi_only12000
- Jan 2nd, 2010
Grant all privileges of user_name1 to user_name2 with grant option.where user_name1 is the user of which we have to pass privileges,and user_name2 is the user to which we are assign the privileges,and grant option is nothing but it allows the user_name2 to pass all privileges to another.
mukesh
- Mar 20th, 2007
SELECT 'GRANT ' ||privilege ||' ON '||table_schema||'.'||table_name ||' TO '||USER ||' ;' FROM ALL_TAB_PRIVSWHERE grantee ='TILITS' -- user name that has already access
-
What is difference between CHAR and VARCHAR2
Read Best Answer
Editorial / Best Answer
Answered by: srinivasan
- Aug 16th, 2005
A varchar2 datatype, when stored in a database table, uses only the space
allocated to it. If you have a varchar2(1999) and put 50 bytes in the table, we
will use 52 bytes (leading length byte).
A char datatype, when stored in a database table, always uses the maximum length
and is blank padded. If you have char(1999) and put 50 bytes into it, it will
consume 2001 bytes (leading length field is present on char's as well).
In the database -- a CHAR is a VARCHAR that is blank padded to its maximum
length.
Nitin Yadav
- Nov 9th, 2017
Char is fixed and varchar 2 is variable.
gopalakrishna
- Jul 28th, 2015
Char is a data type based on the given size memory is used
varchar2 based on the given characters memory is used -
Display Odd/ Even number of records
sharmasl
- Mar 23rd, 2009
These are the querys:- FOR Odd number of records: select * from emp where (rowid ,1) in (select rowid, mod(rownum,2) from emp); FOR Even number of records: select * from emp where (rowid ,0) in (select rowid, mod(rownum, 2) from emp); try.........................
nandk.sharma
- Jun 30th, 2008
We can use the below query to get the odd record of the table --select r from (SELECT rownum r FROM EMP)where mod(r,2) != 0We can use the below query to get the even record of the table --select r from (SELECT rownum r FROM EMP)where mod(r,2) = 0
-
How to drop the column in a table
sharmasl
- Mar 23rd, 2009
use the following syntex for drop the columnalter table emp drop column ename;
NARENDRA
- Dec 12th, 2006
Alter table?TABLENAME drop column?COLUMNNAME Generally rollback cant be followed are implemented along with these DDL statements. The commit statement automatically involves.
-
What is the difference between Inner and equi join?
supratim.kundu
- Mar 4th, 2010
An equi join is used to?retrieve?all the columns from both the tables. An inner join is used to?retrieve selected columns from tables.
patilpravin_1981
- Jan 28th, 2008
Inner jon Or equi join is The query that retrives the rows that exact match using (=) sign in where clause
-
What is ROWID
Read Best Answer
Editorial / Best Answer
Answered by: Zimmy
- Jul 10th, 2005
ROWID is the psedo columns indicate the stored location of the data physically in the database.
Ravi
- Jul 3rd, 2013
ROWID: It is pseudo column,it has physical address location of row in ORACLE database
The format is: OOOOOO.FFF.BBBBBB.RRR
OOOOOO is the object ID
FFF is the file number
BBBBBB is the block number
RRR is the row numbervikas
- Dec 21st, 2012
But according to you it has 16 digits ... and according to my oracle 10g it is showing 18 digits. like AAAC9EAAEAAAABXAAA .. why??
-
How many LONG columns are allowed in a table
Read Best Answer
Editorial / Best Answer
Gayathri Panchangam- Member Since Aug-2008 | Sep 5th, 2008
Only one column can have LONG data type. And that column cannot be used in WHERE clause or in ORDER BY clause. Also we cannot add primary key constraint to that column only not to the whole table, that means we will give primary key to any column in the table except the column having the long data type. If we give, we get the error ORA-02269: key column cannot be of LONG datatype.
Gayathri Panchangam
- Sep 5th, 2008
Only one column can have LONG data type. And that column cannot be used in WHERE clause or in ORDER BY clause. Also we cannot add primary key constraint to that column only not to the whole table, tha...
Nikhil_4_Oracle
- Mar 5th, 2007
Hey all, You can have only one column of long in table , And this table can`t have Primary key Constraint. --Nikhil
-
Can a view be updated/inserted/deleted
sweetytrivedi
- Jun 24th, 2008
If a view is based upon a single table then DML operations can be performed on it, while if a view is based upon more than two tables then it is a complex view and DML operations can be performed using instead of triggers.
Srikanth
- Nov 7th, 2006
A view can be manpulated by using instead of triggers
-
Disable Restricted Session
How do you get back into SQLPlus to disable restricted session if you enabled.
SQL*Plus interview Questions
Ans