-
What is an inline view?
g_sidhu
- Feb 7th, 2008
An inline view is created by placing a subquery in the FROM clause and giving that subquery an alias. The subquery defines a data source that can be referenced in the main query.
rampratap409
- Dec 12th, 2006
When a query is given inplace of tablename in from cluse . The query becomes a inline view.
-
Can a function take OUT parameters. If not why?
krishnaindia2007
- Jun 22nd, 2008
A cusor can tabke out parameter. But never use OUT and IN OUT parameters in functions. The main purpose of function is to take zero or more parameters and return a single value.
BASAVARAJ KOLUR
- Mar 9th, 2007
function can take out parameter and in out parameter in that case it returns values to calling program with return value also accroding to me its not good programing style to u...
-
What is rep cursor?
ravi.8311
- Jun 30th, 2008
Ref cursor is dynamic and no need to declare variables.ref cursor as two types 1.stong cursor(it has return value)2.week cursor (it has no return value)
bharatdwajmishra
- Jun 23rd, 2008
I know about REF cursor..it is one datatype , which by we return data to web pages fetch by java.. but REP cursor is new foe me..
-
How to delete a duplicate records in a table without using rowid
Praveen2384
- Sep 9th, 2008
Delete from table_name where rownum in (select min(rownum) from table_name group by column2,column3);where column2 and column3 are repeating columns.
Karthik
- Mar 14th, 2007
Hi,If you have primary key Constrains in the tableFollowing is query will select the duplicate ----------------------------------------Select * from X a, X bWhere a.1 != b.1 ( Primary Key) And a 2 = b...
-
If i have 6 values in one coloumn that is 10,20 30,40 50,60. in this 10,30,50 are max(val),and other are min values. now i want some of max values is 90. write a query for that?
Imran_Javed
- May 12th, 2008
SELECT SUM(col_name)FROM ( SELECT ROWNUM rn, col_name FROM tab_name)WHERE MOD(rn,2)!=0;
Rohan Deshpande
- Jan 30th, 2007
Select sum(max(sal)) from table_name;
-
How to add positive and negative numbers in a single column and output them to two different columns
-
How to give select privilege on all the objects owned by an user(say user1) to another user say(user2) using a single sql statement?
yagneswarasankar
- Nov 24th, 2010
SQL> conn sample/sample;Connected.SQL> select count(1) from girija.employees;select count(1) from girija.employees &n...
-
Why order by clause maintains column number values instead of column names ?
rahul.katke
- Jun 24th, 2008
The column numbers are used so that you can do the order by without keying in all the column names in the order by clause. For eg.select empno, ename from emp order by empno; -- qry 1could b...
krishnaindia2007
- May 16th, 2008
There is no relation between column_id in user_tab_columns and column number in order by clause.For exampleSelect ename,job, sal from emp order by 1;Here it will arrange data in the order of employee ...
-
View sysdate
How can we view sysdate for n number of times
krishnaindia2007
- Jun 21st, 2008
Select sysdate from user_tables where rownum <=50; Here you can use any table name that contains more than 50 rows.
-
Copy structure of a table
How can I copy the stucture of a table to a text file.
krishnaindia2007
- Jun 21st, 2008
You can copy the definition of objects to text file using dmbs_metadata package.This is the query to copy emp table definition to text file.spool textfilenameselect dbms_metadata.get_ddl('TABLE','EMP','CMSJAN') from dual;spool off;
-
SET Operator
What is a SET operator? What are the types what is the difference between SET operators and JOINS?
Aparna
- Apr 5th, 2012
Union --> All rows/cols from 1st select and 2nd select with no duplicates Union All -->All rows/cols from 1st Select and 2nd Select with duplicates Intersect --> Rows common in both select statements...
dajjaal
- Sep 20th, 2010
The UNION operator is used to combine the result-set of two or more SELECT statements. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
-
Delete contents in two tables at a time
How to delete contents in two tables at a time? When you delete contents in one table then other table contents should also be deleted.
krishnaindia2007
- Jun 20th, 2008
You may also use triggers for this purpose. But specifying on delete cascade while defining foreign key constraint is better option.
-
Column Level Constraint
What are Column Level Constraint and Table Level Constraint? How do they differ from one another?
katgeektalk
- Aug 22nd, 2010
You can create the constarin at either the column or table level. Constrain defined at the column level are included when the column is defined. Table-level constrains are defined at the end of the ta...
hindol_saha
- Jan 20th, 2010
The NOT NULL constraint can be define only at column level,It enforces that a value must be define for this column such tht the column may not be NULL for any row in any circumstance.
-
Null Value
How to handle Null value in WHERE Condition?
taaznyonker
- Mar 31st, 2010
Use "IS NULL" to check whether value is null or "IS NOT NULL" to check the value is not NULL
nikhilanshuman
- Mar 16th, 2010
You should use "IS NULL" or "IS NOT NULL" in the where clause.For example :select DEPT_NAME from DEPT where DEPT_CD IS NOT NULL;The above query returns all those department names from the table "DEPT" where the column "DEPT_CD" is not null.
-
Highest Average Salary
Construct a query which finds the job with the highest average salary
Shubham kumar
- Oct 21st, 2021
Hope, This Will Help
Codetaaznyonker
- Mar 31st, 2010
If you want to get only highest average salary of particular dept, then you can use SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno and if want the dept number too, with the average sal, then query ould...
-
Pluks and Zinks
There is a table with pluks and zinks. Each pluk only resides on a single zink and each zink may contain multiple pluks. How do you find how many pluks are on each and every zink?
-
Get 10 and 11th records in the output using SQL in 5 ways ?
I have to tables A and B. My records in this table are in such a way that
Table A Table B
10 15
11 16
12 10
13 11
Question :- I want only the 10 and 11 records as my output.
Can anyone help me out ( 5 ways... -
What are various joins used while writing SUBQUERIES?
Self join-Its a join foreign key of a table references the same table. Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.
Santhoshkandula
- May 11th, 2011
There are 5 types of joins. Those are 1. Equi Join / Inner Join 2. Non-Equi Join 3. Outer Join ( Left Outer Join, Right Outer Join, Full Outer Join ) 4. Self Join 5. Cross Join
-
Display the records between two range?
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);
LRANI
- Jul 31st, 2007
Hi,minus is used for finding the missing records between 2 tables and not finding the records between the range.It is BETWEEN operator that displays the records between 2 ranges from single table.
-
What a SELECT FOR UPDATE cursor represent.[ANSWER]SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.
Nicsmart
- Mar 31st, 2010
The SELECT....FOR UPDATE [OF column_reference ][NOWAIT] statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when we want to base an upd...
g_sidhu
- Feb 6th, 2008
The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in...
Oracle SQL Interview Questions
Ans