-
-
Difference between procedure and function.
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.
usha`
- Aug 19th, 2011
Functions can be used in a select statement where as procedures cannot.
functions cannot return values where as procedures can.
functions can be used as user defines data types in create table but procedures cannot.Prakash Jayabalan
- Oct 29th, 2009
Procedure:Parameters IN, OUT and IN OUT and can return n number of values via sys cursor.Function:Only IN parameter and must return a value by using RETURN.Can be used in where clause of the Query?but performance issue will raise.
-
Birthdate Query
There is a table friends having following detailsName BirthdayJohn 28-jun-1985Peter 27-aug-1987Tom 13-aug-1986Nick 29-apr-1987Natalia 25-apr-1984Kate 28-jun-1984Wilson 11-nov-1987krish 31-jul-1987robert 25-apr-1984
SELECT names of the friends which come on the same day.. condition you don't know which birthday's repeat
SELECT... -
Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?
SELECT customer_no, payments from customer C1WHERE 3<=(SELECT COUNT(*) from customer C2WHERE C1.payment <= C2.payment)
ankitagujrati28
- May 3rd, 2010
Select rownum,cust_no,payments from (select cust_no,payments from customerwhere rownum<4order by payments desc);
thirupathireddy
- Feb 2nd, 2010
SELECT rownum "rank", customer_no, payments FROM (SELECT customer_no, paymentsFROM customer ORDER BY payments DESC)WHERE rownum <4;
-
SQL Syntax for deleting duplicate records in a table
jayshree13
- May 11th, 2011
Delete from emp e where rowid<(select max(rowid) from emp m where e.name=m.ename);
dj_dj_dj
- Mar 18th, 2010
Basically deleting duplicate records while creating table sounds inefficiency of the SQL DBA.To avoid duplication one can provide the column datatype as Unique or Primary key.........Which is the best method upto my knowledge to prevent duplication entry of data.RegardsDharmendra Jaiswal.
-
-
How do i get Greatest salaray of dept group from emp and dept?we have to tables EMP and DEPT. for exp: dept 10 have 10000,dept 20 have 5000 and dept 30 have 15000 salaries, i need dept 30 group salary(means sum of that group is 15000).
vipsforever
- Feb 9th, 2008
Select department_id, m.salary from employees m where m.salary =(select max(salary) from employees where salary in( select max(salary) from employees group by department_id));
bharadh
- Oct 22nd, 2007
Select deptno,sum(sal) from emp where depno in(select deptno from emp where sal)=(select max(sal) from emp group by deptno)
-
What is difference between UNIQUE and PRIMARY KEY constraints?
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.
Read Best Answer
Editorial / Best Answer
Answered by: Vijay Raj Jaiswal
- Sep 20th, 2005
Hi this is modified answer ,pls post this instead of previous which i post
Primay Key Unique key
----------- ------------------
There is only one there may be more than 1
Primary key for Unique Key in table
1 tableIt can not contain It Can contain Null Value
Null valueLakshmanan
- Jun 30th, 2017
Primary key is combination of not null and unique. Primary key does not insert a null value and insert value is unique. Unique is one time accepted null value and insert a data is uniquely
ankitagujrati28
- Apr 30th, 2010
Unique key means we have to enter unique value but we can enter null.But in case of Primary key we should have to enter Unique + Not Null value in that particular column.
-
What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
qptopm
- May 12th, 2016
PL/SQL provides the capability of the programming language and supports the above mentioned list. It also supports running of SQL statements inside the PL/SQL blocks. SQL provides access to the database for retrieving data from the objects. However SQL does not have the programming capabilities.
Das
- Nov 4th, 2015
Simply put, like a command prompt in Windows Operating system, we have SQL PLUS tool in Oracle DBA.
-
When do you use WHERE clause and when do you use HAVING clause?
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
Mushtaque
- Aug 25th, 2013
WHERE clause can contains condition that must be met and should directly follow the from clause. And HAVING clause can precede GROUP BY clause,but it is more logical to declared in after GROUP BY clau...
Santhoshkandula
- May 11th, 2011
?? we have to?apply the condition?Before grouping the records then you have to?use WHERE clause.?? we have to apply the condition after grouping the records then you have to apply HAVING clause.? Thanks & Regards??? K.Santhosh
-
What is CoRelated SubQuery
A subquery within a subquery
rjk1203
- Sep 19th, 2008
A Correlated Subquery?which executes?with value returned by the child Query. Unlike subquery it executes per result from child query.Ex: removing duplicate rows from a table Subquery executes?once for the entire child?query's result
-
SESSION 1DELETE FROM EMP; SESSION 2 SELECT * FROM EMP;What will be the result?
mfasiur
- Jun 7th, 2012
Also, the undo blocks will store all the data until the transaction is either committed or rolled back.So, if someone issues DML statements(insert,update & delete)The before image of the transaction i...
Hany
- Jun 7th, 2012
With No Commit after delete , you will be able to see all record in session 2
-
Hi,Select * from tab; - means we can see all the tables,right!is there any option similar to this..?
VIJAI VEDHA
- Sep 28th, 2022
ITS syn NOT sys,
SuryaRS
- Aug 29th, 2017
TAB will give tables, views whereas CAT will give tables, views, sequences. TAB is having clustered column.
Code -
-
-
There is a % sign in one field of a column. What will be the query to find it?
'' Should be used before '%'.
-
Is there any chance to remove a column of a table
peprah
- Jan 20th, 2010
Sure why not?First of all you need to know and be sure of the table name and column you want to delete. There you are: DELETE FROM [TABLE_NAME]WHERE [ROW_NAME] = ['COLUMN_NAME']; EXA...
-
What is a view ?
A view is stored procedure based on one or more tables, it’s a virtual table.
Suresh Babu Nallamothu
- Nov 8th, 2018
A view is a virtual table,Because it doesn't have its own data and it doesn't occupy the database memory.
ankitagujrati28
- Apr 30th, 2010
A view is a mirror image of one or more table. View provides data access privilages.
-
What is difference between Rename and Alias?
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.
-
Oracle SQL Interview Questions
Read Best Answer
Ans