31. what is co-related SUBQUERY?
A correlated subquery is one that has correlation name as a table or view designated in the from clause of the outer query and the same correlation name as a qualifier of search condition in the where clause of sub query.
Ex:select fileld1 from table1 X
Where field2>(select avg(field2) from table1 Y
(The sub query in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
32.what are various joins used while writing subqueries?
Self join: it’s a join foreign key of a table references the same table
Outer Join:it’s 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:it’s 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.
33.what are various constraints used in SQL?
- Not Null
34.what are different oracle database objects?
- Tablespaces etc
35. what is difference between rename and alias?
Rename is permanent name given to table or column where as alias is a temporary name given to a table or column which do not exist once the sql statement is executed.
36.What are the various priveliges that a user can grant to another user?
37.Can primary key contain more than one columns?
38.what is difference between SQL and SQL*PLUS?
SQL*PLUS is command line tool where as SQL and PL/SQL language interface and reporting tool.it’s a command line tool that allows users 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.
39.which datatype is used for storing graphics and images?
LONG RAW datatype is used for storing BLOB’s (binary large objects).
40.how will you delete duplicate rows from a table?
DELETE FROM table_name a
Where rowid>(select min(rowid) from table_name b where a.table_no=b.table_no);
41.what is difference between SUBSTR and INSTR?
SUBSTR returns specific portion of the string
INSTR :provides characterposition in which a pattern is found in a string.
Ex:INSTR(‘ABC-DC-F’,’-‘,2) output 7(2nd occurrence of ‘-‘)
42.There is a ‘%’ sign in one field of a column .what will be the query to find it?
‘\’ should be used before ‘%’
43.When you use where clause and when you use having clause?
HAVING clause is used when you want to specify a condition for a group function and it’s written after group by clause
The where clause is used when you want to specify a condition for columns ,single row function except group functions and it’s written before GROUP BY clause if it’s used.
44.which is more faster –IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value where as IN returns a value.
45.How will you avoid your query from using indexes
SELECT * from emp where emp_no+’’=12345;
i.e you have to concentrate the column name with space with in codes in the where condition
SELECT /*+full(a)*/ ename,emp_no from emp
i.e using HINTS
46.Can you use commit statement with in a database trigger?
47.what is commit,rollback and savepoint?
Commit:update the changes once transaction commits
Rollback:rollback the transaction will not effect any data changes make sure we need to rollback before commit
Savepoint:SAVEPOINT are used to subdivide a transaction into smaller parts it enables rolling back part of transaction maximum of five savepoints are allowed.
48.What is difference between UNION and UNIONALL?
Union:returns all rows from either queries no duplicate rows.
Union All:Returns all rows from either queries including duplicate rows.
49.Which system table contains information on constraints on all the tablescreated?
50.How to find out the database name from SQL*PLUS command prompt?
Select * from global_name
This will give the database name which you are currently connected to……
51.What is difference between co-related sub query and nested sub query?
|Corelated subquery||Nested subquery|
|Runs once for each row selected by outer query.It contains a reference to value from the row selected by outer query||Runs only once for entire nesting(outer) query.it does not contain any reference to the outer query|
|EX:Select e1.empname,e1.basicsal,e1.deptno from emp e1 where e1.basicsal=(select max(basicsal) from emp e2 where e2.deptno=e1.deptno);||EX:Select empname,basicsal,deptno from emp where (deptno,basicsal) in (select deptno,max(basicsal) from emp group by deptno)|
52.How can I hide a particular table name of our schema?
You can hide the table name by creating synonyms.
Ex:you can create a synonym Y for table X
Create Synonym Y for X;
53.How can we see current user name in SQL?
SQL> Show user;
54.To view installed oracle version information?
Select banner from V$version;
55.Display the number value in words?
SQL> select sal,(to_char(to_date(sal,’j’),’jsp’)) from emp;
56.what is the maximum number of triggers apply on a single table?
57.what are the advantage of views?
- Provide an additional level of table security,by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity
- Simplify command for the user
- Present the data from a different perspective from that of the base table.
- Store complex queries.
58.How many long columns are allowed in a table?Is it possible to use long columns in where clause or order by?
Only one long column is allowed.It’s not possible to use long column in where or order by clause.
59.which date function returns number value?
60.what is the default return value of a function?
The default return value of a function is int.in other words,unless explicitly specified the default return value of compiler would be integer value from function.