Create View with Parameter

From the title of this post you guess that oracle give us capability to create view with parameter, but this is wrong, don't think good of oracle to give you this capability as straight forward.

I have workaround to do this capability by the following techniques

1-virtual private database context
2-global package variable
3-Lookup Tables

#1 Virtual Private Database Context

I will use in where clause SYS_CONTEXT function as parameter to filter data of the query of the view.

First step is creating context that will handle session variables

 CREATE OR REPLACE CONTEXT MY_CTX USING CTX_PKG  
 ACCESSED GLOBALLY;  

Second I will create CTX_PKG package that context will use it to handle session parameters.

The package contains three procedures

a-SET_SESSION_ID to set unique id for every session I will use in my context.

b-CLEAR_SESSION to clear session from my context.

c-SET_CONTEXT to set variable value in my context.

Package Specification

 CREATE OR REPLACE PACKAGE CTX_PKG  
 IS  
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2);  
   PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2);  
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2);  
 END CTX_PKG;  

Package Body

 CREATE OR REPLACE PACKAGE BODY CTX_PKG  
 IS  
   GC$SESSION_ID  VARCHAR2 (100);  
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2)  
   IS  
   BEGIN  
    GC$SESSION_ID := IN_SESSION_ID; 

    DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID); 

  END;  
   PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2)  
   IS  
   BEGIN  
    DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);  
    DBMS_SESSION.CLEAR_IDENTIFIER;  
   END;  
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2)  
   IS  
   BEGIN  
    DBMS_SESSION.SET_CONTEXT ('MY_CTX',  
                 IN_NAME,  
                 IN_VALUE,  
                 USER,  
                 GC$SESSION_ID);  
   END;  
 END CTX_PKG;  

Now let's test context and my package

 BEGIN  
   CTX_PKG.SET_SESSION_ID (222);  
   CTX_PKG.SET_CONTEXT ('my_name', 'Mahmoud A. El-Sayed');  
   CTX_PKG.SET_CONTEXT ('my_age', '26 YO');  
 END;  

Now I set two context variable my_name, my_age
to query this variable I will use SYS_CONTEXT function

 SELECT SYS_CONTEXT ('MY_CTX', 'my_name'), SYS_CONTEXT ('MY_CTX', 'my_age')  
  FROM DUAL;  

The output will be

After insuring that my context and package working true let's now create view on table EMP to get employee in department at SCOTT schema

 CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT  
 AS  
   SELECT *  
    FROM EMP  
   WHERE DEPTNO = SYS_CONTEXT ('MY_CTX', 'deptno');  

to filter view by employees in department 20 only you should execute this PLSQL block first

 BEGIN  
   CTX_PKG.SET_SESSION_ID (222);  
   CTX_PKG.SET_CONTEXT ('deptno', '20');  
 END;  

Now lets create select statement against EMP_IN_DEPARTMENT view and see the result

 SELECT * FROM EMP_IN_DEPRATMENT;  

The output result is like below

#2 Global Package Variables

I will use in where clause global package variables as parameter to filter data of the query of the view.

I will create package that hold every global variables which I will use it as parameters in view.

Package Specification

CREATE OR REPLACE PACKAGE GLB_VARIABLES
IS
   GN$DEPTNO   EMP.DEPTNO%TYPE;

   PROCEDURE SET_DEPTNO (
      IN_DEPTNO EMP.DEPTNO%TYPE);

   FUNCTION GET_DEPTNO
      RETURN EMP.DEPTNO%TYPE;
END;

Package Body

 CREATE OR REPLACE PACKAGE BODY GLB_VARIABLES  
 IS  
   PROCEDURE SET_DEPTNO (  
    IN_DEPTNO EMP.DEPTNO%TYPE)  
   IS  
   BEGIN  
    GN$DEPTNO := IN_DEPTNO;  
   END;  
   FUNCTION GET_DEPTNO  
    RETURN EMP.DEPTNO%TYPE  
   IS  
   BEGIN  
    RETURN GN$DEPTNO;  
   END;  
 END;  

Let's now create view filter its data by global variables in GLBL_VARIABLES package

 CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT2  
 AS  
   SELECT *  
    FROM EMP  
   WHERE DEPTNO =GLB_VARIABLES.GET_DEPTNO;  

Now lets create select statement against EMP_IN_DEPARTMENT2 view and see the result

 EXEC GLB_VARIABLES.SET_DEPTNO(20);

 SELECT * FROM EMP_IN_DEPRATMENT2;  

The output result is like below

 

3-Lookup Tables

another solution is to create lockup table for storing view parameters on it and build view based on the data stored in lockup table.
This solution is straight forward solution so no need to make demo for this solution to make post shortly as possible

Thanks
Sekhar

Play with NULL Value

NULL is big problem in whole programming language, Today I will write about NULL and  what's the problems we face in code and tips to play with NULL values.

Calling any method or variable of Class has NULL value raise exception in other programming language like C++, Java, C# ,.... etc but in PLSQL it doesn't raise any exception.

If I use NULL in any calculation or logical condition, the output will be NULL.

I wrote before about Three-Valued Logic and the problem  in three-valued logic in PLSQL is NULL value.

Lets see example work with NULL before begin illustration.

CREATE OR REPLACE PROCEDURE MY_NAME (IN_NAME VARCHAR2)
IS
BEGIN
   IF IN_NAME != 'Sekhar'
   THEN

Sekhar');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('My nam
DBMS_OUTPUT.PUT_LINE ('My name is not
 e is Sekhar');
   END IF;
END;

BEGIN
   MY_NAME (NULL);


END;

If I run previous code it will print

 My name is Sekhar  

The printed result is illogical for us because I pass IN_NAME parameter as NULL value, and It printed "MY Name is Sekhar"

So I should state tips about play with NULL

1-Any logical conditional with NULL will return NULL

   For example variable=NULL will return NULL

   NULL = NULL will return NULL

2- Any mathematical operation with NULL will return NULL 

   For example 3+NULL will return NULL 

3- Any concatenation with NULL will return the basic string without NULL 

  For example 'Sekhar' || NULL will return 'Sekhar'

4- You can use logical conditional  "IS NULL" "IS NOT NULL" to check NULL value

  For example IF 'Sekhar' IS NOT NULL will return TRUE Boolean

  For example IF 'Sekhar' IS NULL will return FALSE  Boolean

5-Use DECODE to handle NULL values column in SELECT statement

  You want to get employees from EMP table whose Name is equal to parameter :EMP_NAME otherwise if:EMP_NAME hasn't value return all employees.

You can do this requirement using  OR operand

 SELECT *

  FROM EMP

 WHERE ENAME = :EMP_NAME OR :EMP_NAME IS NULL ;

I should replace previous query by the below query to use DECODE

 SELECT *  

  FROM EMP  

  WHERE DECODE (:EMP_NAME, ENAME, 1, NULL, 1, 0) = 1  ;

6-To avoid logical fallacies always is is better to begin checking with true value then the difference

For example in previous MY_NAME I began logical condition  with

 IF IN_NAME != 'Sekhar'

So I should write it again and begin with true value like

 IF IN_NAME = 'Sekhar'

7-Take attention that empty string is NULL values also

 For example

 DECLARE  

   X  VARCHAR2 (10);  
BEGIN  
   X := '';  

EN  
    DBMS_OUT
IF X IS NULL  
   T
HPUT.PUT_LINE ('x is null');  
   ELSE  

);  
   END IF;  
 END;  

DBMS_OUTPUT.PUT_LINE ('x is not null

'

It will print "x is null" however x is empty string

8-NULL values are excluded from B-tree indexes, so searching for NULL values will cause a full table scan.

9-Bitmap indexes can be used to search for NULL values 

Thanks

Sekhar

Oracle DB 11g New Feature (Read Only Tables)

I posted before about new features about Oracle Database 11g you can read it from below

Oracle DB 11g New Feature ( Virtual Columns ) 

Oracle DB 11g New Feature ( Compound Triggers )

Today I will produce new feature which called Read Only Tables

Read only tables are like normal  tables but it restricts any transaction to perform any DML(Insert, Update, Delete) operation against it.

Before oracle database version 11g READ ONLY was related to DATABASE and TABLE SPACE only but in version 11g you can do READ ONLY to tables too.

Implement READ ONLY in Previous Versions

In version previous 11g we use a workarounds to do READ ONLY against tables by table triggers or check constraints.

1- Table Trigger

I will create table for demo and create trigger on table for restricting DML operations. 

 CREATE TABLE READ_ONLY_TABLE (COL1 NUMBER);  
   

ATE OR REPLACE TRIGGER READ_ONLY_TABLE_TRG  
   
CR
EBEFORE DELETE OR INSERT OR UPDATE  

G NEW AS NEW OLD AS OLD
ON READ_ONLY_TABLE  
   REFERENCI
N  
   FOR EACH ROW  
 DECLARE  
 BEGIN  

RAISE_APPLICATION_ERROR (-20001, 'Table is read only table.');  

END;  


RT INTO READ_ONLY_TABLE  
    
INS

EVALUES (1);  

If I try to run previous script it will raise exception 

ORA -20001, Table is read only table.

2- Check Constraint

I will create table for demo and create check constraint with disabled validation which has condition will be true every time exposed.

 CREATE TABLE READ_ONLY_TABLE2 (COL1 NUMBER);  
   

 ALTER TABLE READ_ONLY_TABLE2 ADD CONSTRAINT READ_ONLY_CONST CHECK(0=0) DISABLE VALIDATE;  

   

 INSERT INTO READ_ONLY_TABLE2  

    VALUES (1);  

If I try to run previous script it will raise exception 

ORA-25128: No insert/update/delete on table with constraint SCOTT.READ_ONLY_CONST) disabled and validated

Implement READ ONLY in 11g

Oracle support READ ONLY feature directly in version 11g using the below syntax

ALTER TABLE table_name RAED ONLY;

 CREATE TABLE READ_ONLY_TABLE3 (COL1 NUMBER);  
   
 ALTER TABLE READ_ONLY_TABLE3 READ ONLY;  

(1)
INSERT INTO READ_ONLY_TABLE3  
    VALUES

 ;  

If I try to run previous script it will raise exception 

ORA-12081: update operation not allowed on table "SCOTT"."READ_ONLY_TABLE3" 

Now RAED ONLY feature is provide in version 11g so how I can know is table READ ONLY or no?

You can new this from tables data dictionary views ( ALL_TABLES,DBA_TABLES,USER_TABLES,TABS ) fromREAD_ONLY column which has two values ( YES, NO)

 SELECT table_name, READ_ONLY FROM tabs;  

The output of query is

Thanks

Sekhar 

Find Unused Columns in Oracle Database

Sometimes during development of new systems, you may add new columns to tables and then you don't use it and forget dropping it.

So you want to know which these columns to drop.

Usually unused columns have NULL value, So I created a function to return array of column names in my schema have NULL value.

I created GET_NULL_COLUMNS function returns VARRAY of varchar2

It has only one parameter (IN_TABLE_NAME)

If I pass a value for IN_TABLE_NAME then it will return NULL columns in this table only, otherwise it will return NULL columns in entire schema.

 CREATE OR REPLACE FUNCTION GET_NULL_COLUMNS ( 

   IN_TABLE_NAME VARCHAR2 DEFAULT NULL) 

   RETURN SYS.ODCIVARCHAR2LIST 

 IS 

   L_NULL_COLS   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST (); 

   LN$ROW_COUNT  NUMBER; 

   LN$NULL_COUNT  NUMBER; 

   LN$INDEX    NUMBER := 1; 

   CURSOR LCUR$COLS (P_TABLE_NAME VARCHAR2) 

   IS 

    SELECT TABLE_NAME, COLUMN_NAME 

     FROM USER_TAB_COLS 

     WHERE DECODE (P_TABLE_NAME, TABLE_NAME, 1, NULL, 1, 0) = 1;  

 BEGIN 

   FOR LREC$COLS IN LCUR$COLS (IN_TABLE_NAME) 

   LOOP 

    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || LREC$COLS.TABLE_NAME 

      INTO LN$ROW_COUNT; 

    EXECUTE IMMEDIATE  'SELECT COUNT(1) FROM ' 

             || LREC$COLS.TABLE_NAME 

             || ' WHERE ' 

             || LREC$COLS.COLUMN_NAME 

             || ' IS NULL' 

      INTO LN$NULL_COUNT; 

    IF LN$ROW_COUNT = LN$NULL_COUNT 

    THEN 

      L_NULL_COLS.EXTEND; 

      L_NULL_COLS (LN$INDEX) := 

       LREC$COLS.TABLE_NAME || '.' || LREC$COLS.COLUMN_NAME; 

      LN$INDEX := LN$INDEX + 1; 

    END IF; 

   END LOOP; 

   RETURN L_NULL_COLS; 

 END; 

Let's now test this function in SCOTT schema

 SELECT COLUMN_VALUE FROM TABLE (GET_NULL_COLUMNS); 

The output of query is

COLUMN_VALUE

BONUS.ENAME

BONUS.JOB

BONUS.SAL

BONUS.COMM

Performance tuning of SQL Queries

          How to tune SQL Queries for better performance?

Performance tuning of SQL Queries:

Note: One of our visitors, Amit asked this question by posting a comment. Thanks Amit for your contribution. Keep visiting/posting!

Performance of the SQL queries of an application often play a big role in the overall performance of the underlying application. The response time may at times be really irritating for the end users if the application doesn't have fine-tuned SQL queries. There are several ways of tuning SQL statements, few of which are:-

Understanding of the Data, Business, and Application - it's almost impossible to fine-tune the SQL statements without having a proper understanding of the data managed by the application and the business handled by the application. The understanding of the application is of course of utmost importance. By knowing these things better, we may identify several instances where the data retrieval/modification by many SQL queries can simply be avoided as the same data might be available somewhere else, may be in the session of some other integrating application, and we can simply use that data in such cases. The better understanding will help you identify the queries which could be written better either by changing the tables involved or by establishing relationships among available tables.

Using realistic test data - if the application is not being tested in the development/testing environments with the volume and type of data, which the application will eventually face in the production environment, then we can't be very sure about how the SQL queries of the application will really perform in actual business scenarios. Therefore, it's important to have the realistic data for development/testing purposes as well.

Using Bind Variables, Stored Procs, and Packages - Using identical SQL statements (of course wherever applicable) will greatly improve the performance as the parsing step will get eliminated in such cases. So, we should use bind variables, stored procedures, and packages wherever possible to re-use the same parsed SQL statements.

Using the indexes carefully - Having indexes on columns is the most common method of enhancing performance, but having too many of them may degrade the performance as well. So, it's very critical to decide wisely about which all columns of a table we should create indexes on. Few common guidelines are:- creating indexes on the columns which are frequently used either in WHERE clause or to join tables, avoid creating indexes on columns which are used only by functions or operators, avoid creating indexes on the columns which are required to changed quite frequently, etc.

Making available the access path - the optimizer will not use an access path that uses an index only because we have created that index. We need to explicitly make that access path available to the optimizer. We may use SQL hints to do that.

Using EXPLAIN PLAN and TKPROF - these tools can be used to fine tune SQL queries to a great extent. EXPLAIN PLAN explains the complete access path which will be used by the particular SQL statement during execution and the second tool TKPROF displays the actual performance statistics. Both these tools in combination can be really useful to see, change, and in turn fine-tune the SQL statements.

Optimizing the WHERE clause - there are many cases where index access path of a column of the WHERE clause is not used even if the index on that column has already been created. Avoid such cases to make best use of the indexes, which will ultimately improve the performance. Some of these cases are: COLUMN_NAME IS NOT NULL (ROWID for a null is not stored by an index), COLUMN_NAME NOT IN (value1, value2, value3, ...), COLUMN_NAME != expression, COLUMN_NAME LIKE'%pattern' (whereas COLUMN_NAME LIKE 'pattern%' uses the index access path), etc. Usage of expressions or functions on indexed columns will prevent the index access path to be used. So, use them wisely!

Using WHERE instead of HAVING - usage of WHERE clause may take advantage of the index defined on the column(s) used in the WHERE clause.

Using the leading index columns in WHERE clause - the WHERE clause may use the complex index access path in case we specify the leading index column(s) of a complex index otherwise the WHERE clause won't use the indexed access path.

Indexed Scan vs Full Table Scan - Indexed scan is faster only if we are selcting only a few rows of a table otherwise full table scan should be preferred. It's estimated that an indexed scan is slower than a full table scan if the SQL statement is selecting more than 15% of the rows of the table. So, in all such cases use the SQL hints to force full table scan and suppress the use of pre-defined indexes. Okay... any guesses why full table scan is faster when a large percentage of rows are accessed? Because an indexed scan causes multiple reads per row accessed whereas a full table scan can read all rows contained in a block in a single logical read operation.

Using ORDER BY for an indexed scan - the optimizer uses the indexed scan if the column specified in the ORDER BY clause has an index defined on it. It'll use indexed scan even if the WHERE doesn't contain that column (or even if the WHERE clause itself is missing). So, analyze if you really want an indexed scan or a full table scan and if the latter is preferred in a particular scenario then use 'FULL' SQL hint to force the full table scan.

Minimizing table passes - it normally results in a better performance for obvious reasons.

Joining tables in the proper order - the order in which tables are joined normally affects the number of rows processed by that JOIN operation and hence proper ordering of tables in a JOIN operation may result in the processing of fewer rows, which will in turn improve the performance. The key to decide the proper order is to have the most restrictive filtering condition in the early phases of a multiple table JOIN. For example, in case we are using a master table and a details table then it's better to connect to the master table first to connecting to the details table first may result in more number of rows getting joined.

Simple is usually faster - yeah... instead of writing a very complex SQL statement, if we break it into multiple simple SQL statements then the chances are quite high that the performance will improve. Make use of the EXPLAIN PLAN and TKPROF tools to analyze both the conditions and stick to the complex SQL only if you're very sure about its performance.

Using ROWID and ROWNUM wherever possible - these special columns can be used to improve the performance of many SQL queries. The ROWID search is the fastest for Oracle database and this luxury must be enjoyed wherever possible. ROWNUM comes really handy in the cases where we want to limit the number of rows returned.

Usage of explicit cursors is better - explicit cursors perform better as the implicit cursors result in an extra fetch operation. Implicit cursosrs are opened the Oracle Server for INSERT, UPDATE, DELETE, and SELECT statements whereas the explicit cursors are opened by the writers of the query by explicitly using DECLARE, OPEN, FETCH, and CLOSE statements.

Reducing network traffic - Arrays and PL/SQL blocks can be used effectively to reduce the network traffic especially in the scenarios where a huge amount of data requires processing. For example, a single INSERT statement can insert thousands of rows if arrays are used. This will obviously result into fewer DB passes and it'll in turn improve performance by reducing the network traffic. Similarly, if we can club multiple SQL statements in a single PL/SQL block then the entire block can be sent to Oracle Server involving a single network communication only, which will eventually improve performance by reducing the network traffic.

Using Oracle parallel query option - Since Oracle 8, even the queries based on indexed range scans can use this parallel query option if the index is partitioned. This feature can result in an improved performance in certain scenarios.

sql interview questions nd answers

Question :
What are the advantages and disadvantages of primary key and foreign key in SQL?

Answers: Primary key

Advantages

1) It is a unique key on which all the other candidate keys are functionally dependent

Disadvantage

1) There can be more than one keys on which all the other attributes are dependent on.

Foreign Key

Advantage

1)It allows refrencing another table using the primary key for the other table

First do remember that a table can have only 1 primary key, though we can have many columns with a single primary key, called composite primary key.
Advantages
1. It prevents to enter null data.
2. It prevents to enter duplicate data.
3. It helps to force integrety constraints.

Disadvantage :
On primary key index will be created so during updation of table index need to be adjusted accordingly. this process makes the updation slower

Question :
Difference between decode and case.
in which case we are using case and in which case we are using decode?
with an example.

Answers:First I will give one example using 'decode'
sql>SELECT ENAME,SAL,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','OTHERS') "DEPARTMENTS" FROM EMP;
I have used the decode function on 'deptno' column. It will give the user-friendly output. instead of using 'accounting', 'research'.. We can use anything we want to get the friendly outputs.
I have to check-out the 'case' function after that I will give an example

Using CASE expression we can use all comparative operators (<,>,==, etc), where as using DECODE we should always use = condition.

Question :
What is the difference between TRUNCATE and DELETE commands?

Answer:TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.<br>WHERE clause can be used with DELETE and not with TRUNCATE.

Truncate resets the identity and delete does not

Question :
What is the use of CASCADE CONSTRAINTS?

Answers:When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

For identifying the continuous constraints

Question :
How can we delete records in a table using update table?

Answers:update table employee
set emp_name = null
where emp_id=10

Question :
What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );

Answers: NO.
Explanation :
The query checks whether a given string is a numerical digit.

I have query regarding this. I guess the Translate part will give the result as 1111111111, and its a numerical digit.So answer should be Yes.
plz correct me , if I am wrong.

Question :
How to programmatically find out when the SQL Server service started?

Answer:-There is one simple but awkward method is there to find out when the SQL server service is started.
The following query will give you the time when the SQL server service is started;

select crdate from [master].dbo.sysdatabases
where name=?tempdb?
This is because, the tempdb will be created whenever SQL server service is started or restarted.

Question :
How can i hide a particular table name of our schema.

 Answers: you can hide the table name by creating synonyms.

e.g) you can create a synonym y for table x

create synonym y for x;

you can hide the table name by creating synonyms.
e.g) you can create a synonym y for table x
create synonym y for x;Question :
How we get second highest salary in
database. Please show me this coding.Answers:

select top 1 from (select top 2 from (select salary from employee order by salary) order by salary dsc)

Question :
What is the difference between cross product & Cartesian product?

Answers: There is no difference between Cartesian product and cross join.
although there syntax are different but they work as a same.

Cartesian or cross product selects all rows from both the table.
example
tableA 6 rows
tableB 6 rows
Cartesian or cross product
36 rows.Question :
What is the use of DESC in SQL?

Answers: DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order. Question :
given a table of student(rollno,name,dno,dateofadmission,city) and department(dno,dname)...How u will found the rollno,class,deptname of students in ascending order of their departments? Answers:

Select s.rollno,d.dname from student s,department d where s.dno=d.dno order by s.name ASC, d.name ASC;

Question :
What is a default TCP/IP socket assigned for SQL Server?

Answers:

1433 is default tcp/ip socket sql server.

Question :
What is the difference between varchar& varchar2?

Answers: varchar2 and varchar both are support strings,but varchar2 support alphanumeric string.and another one differece the total byte of the datatype are differ.

Question :
Explain UNION, MINUS, UNION ALL and INTERSECT?

Answers:INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.

INTERSECT - return all distinct rows but comman records exist in both queries.

Union- Retrun all distinct rows from both queries.

Union All - Returns all rows of both queries.

MINUS - In sql server 2005 -both queries work sepratelly

Question :
What is the difference between "select * from table name" and "select column_name from table name" The above quiries are simple select quiries without having any "where" clause. Secondly which query will be performed faster and why??

Answers: column name will perform quickly.

Question :
what is the difference between replace and translate functions?

Answers: I Think here we r discussing abtSQL.Replace function is used to replace a string as well as character to another string or character,Translate function exists in ORACLE for translating single character to some other character........

Question :
What is SQLPlus?

Answers: SQLPlus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.

Question :
Explain about SQL related to RDBMS?

Answers: SQL is known as structured query language. It is especially designed to retrieve and store information of data in relational database management systems. It creates, modifies and makes the data base object user access the control system. It is primarily specialized software for RDBMS.

Question :
How to find out the database name from SQL*PLUS command prompt?

Answers:Select * from global_name;
This will give the datbase name which u r currently connected to.....

select name from v$database

will give u the database name from which u r currently Connected

Question :
Difference between Stored Procedure and Trigger?

Answers:Information related to Stored procedure you can see in USER_SOURCE,USER_OBJECTS(current user) tables.

Information related to triggers stored in USER_SOURCE,USER_TRIGGERS (current user) Tables.

Stored procedure can't be inactive but trigger can be Inactive.

we can call stored procedure explicitly.

but trigger is automatically invoked when the action defined in trigger is done.
ex:
create trigger <trig_Name> after Insert on <table>


this trigger invoked after we insert something on that table.

Question :
What is the default format of date in Oracle? H ow can I change my default date format?

Answers: Oracle's default format for DATE is "DD-MON-YY"

Question :
You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?

Answers: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.

Question :
Which of the following is not a feature of a cursor FOR loop?

Answers: 1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.

Question :
Which system table contains information on constraints on all the tables created?

Answers: yes,

USER_CONSTRAINTS,

system table contains information on constraints on all the tables created

user_cons_columns table consists the information of the table name,column name ,constraint name
user_constraints contains information about table name, constraint type,constraint name e.t.c

Question :
What is reference cursor ?

Answers: Refereence cursor is dynamic cursor used with SQL statement like For select* from emp;

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time.
declare
type r_cursor is REF CURSOR;
c_empr_cursor;
en emp.ename%type;
begin
open c_emp for select ename from emp;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;

Question :
Which system tables contain information onprivileges granted and privileges obtained?

Answers: USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD.

user_tab_privs_made,user_tab_privs_recd.

Question :
Can we connect two different relational tables from different data sources and get the output as one flat file? Answers: Flat files are data files that contain records with no Structured Relationship

Question :
Explain about XML related specifications?

Answers: XML is a very powerful DOM language. Often this language is used with many database applications as a front end, SQL acts as a backend to support the Database queries. This specification has several extensions which defines routines, functions, data type mappings, storage, etc.

Question :
What is a join? Explain the different types of joins?

Answers:Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

Question :
When we give SELECT * FROM EMP; How does oracle respond:

Answers: When u give SELECT * FROM EMP;

the server check all the data in the EMP file and it displays the data of the EMP file

when we give select * from emp it will shows the records of table emp if the table contain records. If it hasn't any records then it will shows no rows selected.

Question :
How to generate OIDS ?

Answers: In MS SQL Server, we can use function NEWID() to generate OIDs(Object Identifiers) or GUIDsQuestion :
Cursor Syntax brief history?

Answers: To retrieve data with SQL one row at a time you need to use cursor processing.Not all relational databases support this, but many do.Here I show this in Oracle with PL/SQL, which is Procedural Language SQL.Cursor processing is done in several steps:1. Define the rows you want to retrieve. This is called declaring the cursor.2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.3. Fetch the data into variables.4. Close the cursor.

Question :
What are the wildcards used for patternmatching.

Answers: _ for single character substitution and % for multi-character substitution.

% for multi character and ?for single character .

Question :
What is output of following query
Select 2 from employee;

Answers: It depends upon number of rows in table. This query will print 2 as many times as rows in table.

Question :
What is outer join?Explain with examples.

Answers:The Left Outer Join returns all the rows from the Left Table with the values that match with the Key from the second table.

The Right Outer Join returns all the rows from the Right Table with the values that match with the key from the first table.

Question :
What are the two components of ODBC ?

Answers: 1. An ODBC manager/administrator and
2. ODBC driver.

Question :
Write a SQL statement to draw the second highest salary in Employee Table (Using Sub query)
Table structure is:
Employe ID
Name
Salary

Answers:For MS SQL database following query should work..

select min(salary) from emp_salary where salary IN(select top 2 salary from emp_salary order by salary desc)

Question :
Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure?

Answers: Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.

Question :
Write a Query to pull out the maximum unit price for each in the database. (Using Northwind database)

Answers: select empname,max(salary) from employee group by empname

Question :
What the difference between UNION and UNIONALL?

Answers: Union will remove the duplicate rows from the result set while Union all does'nt.

unionwont allow the duplicates,where asunionall allows the duplicates.
while we use union the result will be sorted in the asc order of the first col of the select statement by default.where as no sorting in unionall by default.

Question :
What is deadlock?

Answers: Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.

Question :
What are the advantages of client/server model ?

Answers: Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.

Question :
I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated.

Answers: SELECT COL1 FROM TAB1
WHERE COL1 IN
(SELECT MAX(COL1) FROM TAB1
GROUP BY COL1
HAVING COUNT(COL1) > 1 )

Question :
Identify error in following SQL:
SELECT EMPNO, SUM(SAL)
FROM EMP
GROUP BY EMPNO
HAVING ENAME= ?KENT?
a) Syntax Error
b) SUM() is not an aggregate function
c) HAVING can not be used with GROUP BY
d) The Having condition has to be based on some column that appears in the select list.

Answers: d)

We can use this condition: "ENAME= ?KENT?" in the where clause. Then it will run fine.

Question :
What command is used to create a ta ble by copying the structure of another table?

Answers: CREATE TABLE .. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

create table <table> as
Select * from <existing Table>
where 1=2;

Question :
What is join in sql?

Answers: JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.

Question :
Explain the concept of joins and list out the different type of joins?

Answers: Joins are used to combine tables. There are 3 type of joins. selfjoines, outer joines and inner joins

Question :
Why do I get "Invalid Cursor State" errors when I insert/update/delete data with executeQuery()?

Answers: IF u get this error, then there is a problem with the Java program, U may open the cursor with the Readonlymode.Change the Resultset type then you will get it.This is not problem with the Oracle

Question :
i have a table

empidempnamedept
1 abc d1
2 bcd d2
3 adf d3
4 gtf d2
5 tgh d1

i need the output d1=2,d2=2,d3=1 .
please write the sql query

Answers: CREATE TABLE emp_detail (emp_idNUMBER,emp_name VARCHAR(10),dept_name VARCHAR2(10))

insert into emp_detail values(1,'abc','d1')
insert into emp_detail values(2,'bcd','d2')
insert into emp_detail values(3,'adf','d3')
insert into emp_detail values(4,'gtf','d2')
insert into emp_detail values(5,'tgh','d1')

select *
from emp_detail

SELECT dept_name,
(CASE
WHEN dept_name = 'd1' THEN 2
WHEN dept_name = 'd2' THEN 2
WHEN dept_name = 'd3' THEN 1
ELSE 0
END) dept_no
FROM emp_detail

Question :
Explain normalization with examples.

Answers: Normalization is a process of eleminating the redundancy and increasing the integrity.

Normalisation means refining the redundancy and maintainstablisation.
there are four types of normalisation are
first normal forms, second normal forms, third normal forms and fourth Normal forms.

Question :
What is normalization and different kinds of normalization?

Answers: It is set of rules that have been established to aid in the design of tables that are meant to
be connected through relationships. This set of rules is known as Normalization.
Benefits of normalizing your database will include:
√ Avoiding repetitive entries
√ Reducing required storage space
√ Preventing the need to restructure existing tables to accommodate new data.
√ Increased speed and flexibility of queries, sorts, and summaries.

Question :
Explain the ORDER BY clause?

Answers: Orderby clause identifies the columns which are used to sort the data and the order in which they should be sorted out. SQL query needs to specify orderby clause of they want the data to be returned in a defined manner of rows and columns.

Question :
How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?

Answers: Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

Question : What description of a data source is required for ODBC ?

Answers: The name of the DBMS, the location of the source and the database dependent information.

Question :
What is difference between DBMS and RDBMS?

Answers: 1.RDBMS=DBMS+Refrential Integrity

2. An RDBMS ia one that follows 12 rules of CODD.

RDBMS we can make it as a server, it connect more systems but DBMS only woke in host system,we can't make dbms as global..

Question :
How do I list all of my tables?
How do I list all the indexed created for a particulartable(say EMP)?
How do I SELECT FROM DIFFERENT USERS TABLE?

Answers: select table_name from information_schema.tables

sp_helpindex '<tablename>'

Question :
How to display duplicate rows in a table?

Answers: select * from emp

group by (empid)

having count(empid)>1

select * from emp where EmpID in(select EmpID
from emp
group by EmpID having count(EmpID)>1)

Question :
How to testdata loading in database testing?

 Answers: No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.

Question :
What is diffrence between Co-related sub query and nested sub query??

Answers: Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Question :
What is denormalization and difference between normalization and denormalization?

Answers: Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data.
Normalization is the process of de-composing a relation with anomalies into a well structured relation.

Question :
what is the query for to select multiple columns to generate single row in the sql and how come we can bring grid views in sql for getting those datas are demolished

Answers: i need sql& asp db2 asp.net all other interview questuions

Question :
What is the parameter substitution symbol used with INSERT INTO command?

Answers: &

& is substitution variable.
eg. insert into table_name values (&column1, &column2);
The statement prompts for values of column1 and column2.

Question :
on index
why u need indexing ? where that is stroed
and what u mean by schema object?
for what purpose we are using view

Answers: We cant create an Index on Index.. Index is stoed in user_indextable.Every object that has been created on Schema is Schema Object like Table,Viewetc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.

Indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

Question :
How can variables be passed to a SQL routine?

Answers:By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.

Question :
What is the use of the DROP option in the ALTER TABLE command?

Answers: It is used to drop constraints specified on the table.

Question :
Which command executes the contents of a specified file?

Answers: START <filename> or @<filename>.

Question :
What is the main difference between the IN and EXISTS clause in subqueries??

Answers: The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.

IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.

EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.

Question :
What is the main difference between the IN and EXISTS clause in subqueries??

Answers: The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.

IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.

EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.

Question :
What is DML,DDL?

Answers: Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

Question :
what is the difference between oracle,sql and sql server?

Answers: Oracle is based on RDBMS.

SQL is Structured Query Language.

SQL Server is another tool for RDBMS provided by MicroSoft.

SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center.

Oracle is Relational Database product.

SQL server is a Relational Data Base Management System (RDBMS) from Sybase Corporationfor client/server use and is accessed by applications using SQL.

Question :
What is the fastest way of accessing a row in a table?

Answers:The fastest way of accessing a row in a table using ROWID CONSTRAINTS.

Question :
What is the difference between Single row sub-Query and Scalar sub-Query?

Answers: SINGLE ROW SUBQUERY RETURNS A VALUE WHICH IS USED BY WHERE CLAUSE , WHEREAS SCALAR SUBQUERY IS A SELECT STATEMENT USED IN COLUMN LIST CAN BE THOUGHT OF AS AN INLINE FUNCTION IN SELECT COLUMN LIST

A single row sub-query can retrive more than 1 column, but 1 row. Where-as a Scaler sub-qry retrieves only 1 row 1 colums.

Question :                
Why are the integrity constraints preferred to database triggers ?

Answers: Because it is easier to define an integrity constraint than a database trigger.

because integrity constraint checks the data inserted before , and the new inserted data and it doesn't created unless we correct the whole table..
but when using database trigger , it checks only the new inserted data and doesn't interfere with the old data ...Question :
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?

Answers:The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

Question :
What is spool command?

Answers:Spool command creates a print file of the report.

Question :
State true or false. EXISTS, SOME, ANY are operators in SQL.

Answers:True.

Question :
What is a event handler ?

Answers:An event handler is a routine that is written to respond to a particular event.

Question :
1. How to create a new table?
2. How to insert another column into the table?

Answers:create table employee(name varchar2(10),id number(10),mobileno number(10));

Question :
There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all themployees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?

Answers: What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected coloumns of the right table. eg in this query which uses the key-word LEFT OUTER JOIN. syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id

SQL Server/Sybase :
Employee_table.Dept_id(+) = Dept_table.Dept_id

Select e.fname,e.....,d.name,d....,from employee e LEFT JOIN department d
where e.deptid=d.id