-
SQL System Time
How to get System time in SQL?
kondapallikalyan
- Nov 24th, 2009
We have Functions1. current_timestamp2. systimestampEx: Select current_timestamp from dual;Ex:Select systimestamp from dual;
-
NOCOPY mode
What is the purpose of NOCOPY mode in procedures?
nikhilanshuman
- Mar 16th, 2010
When a parameter is passed as an IN variable, it is passed by reference. Since it will not change, PL/SQL uses the passed variable in the procedure/function. When variables are passed in OUT or INOUT ...
-
Maximum Limit for IN Operator
What is the maximum limit for IN operator on SQL?
-
My problem is i have a two tablestable1customercustid custnametable2custaddrcustaddrid custaddrstr custid datei have different records for each custid in custaddr table. say for example for the first customer we have 6 records in custaddr table. so i want the latest dated record from the custaddr table. ie to find the maximum date. pls somebody can write the query for this, i have tried but not able to find the max of the dates.thanks in advance
rk
- Apr 24th, 2006
select c.*, ca.custaddrstr,ca.custdate from customer c, custaddr cawhere c.CUSTID = ca.CUSTIDand ca.CUSTDATE in (select max(custdate) from custaddrgroup by custid)
-
Difference Between Hash Join & Merge Join
Merge Join :Oracle performs a join between two sets of row data using the mergejoin algorithm. The inputs are two separate sets of row data. Output isthe results of the join. Oracle reads rows from both inputs in analternating fashion and merges together matching rows in order togenerate output. The two inputs are sorted on join column.Hash Join :Oracle performs a join between two sets...
mvg_mca
- Mar 24th, 2010
A merge join basically sorts all relevant rows in the first table by the join key, and also sorts the relevant rows in the second table by the join key, and then merges these sorted rows.A hash join (...
Manoj Seemar
- Nov 2nd, 2007
Merge Joins Sort merge joins can be used to join rows from two independent sources. Hash joins generally perform better than sort merge joins. On the other hand, sort merge joins can perform better th...
-
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.
-
Explain what is mutation and what is mutating table and how this mutation problem is solved in a table
Nicsmart
- Mar 31st, 2010
A mutating table is a table that is currently being modified by an INSERT,UPDATE,DELETE statement or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential in...
Nasim
- Aug 24th, 2007
One solution also exist:create a row level trigger and insert/update the data and take it in collections( varray or nested table or associative array). Then write a after statement level trigger and fetch that data.
-
Where we use nested tables in our real time
Nicsmart
- Mar 31st, 2010
• Use when working with an unbounded list that needs to increase dynamically • Use when you need to store in the database and operate on elements individually
paul
- Sep 2nd, 2006
Hi,Nested Tables are collector objects (only attributes, i.e. no behaviour defined), that can be re-used in the same way as a class in java or c++ (without functions / procedures). In RDBMS...
-
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...
-
What is difference between SUBSTR and INSTR?
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDEINSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
Nicsmart
- Mar 31st, 2010
SUBSTR------The substr functions allows you to extract a substring from a string.The syntax for the substr function is:substr( string, start_position, [ length ] )string is the source string. st...
ashish88
- Jun 10th, 2008
hi Buddy, Substr only give the sub part of the stringSubstr(String,'start postion','length')Substr('ABCDEFAG',3,7); /*out put is =cdefag */Instr to give only possio...
-
What are various privileges that a user can grant to another user?
SELECTCONNECTRESOURCES
ankitagujrati28
- Apr 30th, 2010
A user can give all database privilages to another user, which are also has that user.
-
How to get 1,4,7,10.....rows from a table
Arjunya
- Apr 29th, 2010
SELECT XFROM YWHERE MOD(X,3) = 1;Where X is the column name and Y is the table name.
ganne
- Aug 2nd, 2006
Select rownum,empno from emp group by rownum,empno having mod(rownum,3)=1;
-
What is a OUTER JOIN?
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.
ankitagujrati28
- May 3rd, 2010
OUTER JOIN is the way to collect records in case of lack of true join condition.
patilpravin_1981
- Jan 29th, 2008
outer join is that type of jon which retrives the matach & alson unmatached recores also from the tables
-
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;
-
There is a string 120000 12 0 .125 , how you will find the position of the decimal place?
INSTR('120000 12 0 .125',1,'.')output 13
-
TOAD
What does Toad mean? How it's work?
vinodsahani
- May 18th, 2010
Toad is a freeware, powerful, low-overhead tool used by database designer and application developers for faster and easier and simplifies day-to-day administration of various relational databases.
Waseem Mehmood
- May 7th, 2010
It is just a Tool that uses for writing database statements and PL/SQL.
-
SQL Database Name Length
What could be the maximum size of a database name in MSQL?
vinodsahani
- May 18th, 2010
Max Length in MySql Database 64 Table 64 Column 64 Index 64 Stored Function or Procedure 64 Trigger 64 View 64 Event 64 Tablespace 64 Log File Group 64 Alias 255
-
Intent Shared Lock
Which one of the following is descriptive of the effect of an Intent Shared Lock on other transactions? CHOICE1: A Read Lock that prevents writes CHOICE2: A Read Lock that prevents other reads CHOICE3: A Read Lock that prevents more restrictive Locks CHOICE4: A Read Lock that allows other reads CHOICE5: A Read Lock that allows writes (Dirty Reads)
manimehra905
- May 18th, 2010
Read lock that allows other read as it has been made the shared lock intentionally.
hmounir
- May 3rd, 2010
A Read Lock that allows other reads Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control.No other transactions can modify the data whi...
-
Average Department Salary
How to Get the employee details whose salary is less than the average salary of the department.
dj_dj_dj
- May 20th, 2010
select emp_name, avg(salary)from employees group by emp_namehaving avg(salary) < (Select avg(salary) &...
g.sreemayee
- May 19th, 2010
SELECT * FROM emp e WHERE sal> (SELECT AVG(sal) FROM emp GROUP BY deptno HAVING deptno=e.deptno) ORDER BY deptno;
-
Display Time in SQL
How to display current system time in SQL.
g.sreemayee
- May 19th, 2010
select to_char(sysdate,'HH24:MM:SS') from dual;select current_timestamp from dual;These two will give the current time of the system.1. ll give the time only.2. ll give the time with the date.
Oracle SQL Interview Questions
Ans