Oracle Deepak

Common Questions in Oracle ----------------------------------------------NOT NECESSARY IMPORTANT 1. Difference between procedure, function & package. Procedure: It is a type of subprogram that performs an action. It can be stored in the database, as a schema object, for repeated execution. Execute as a PL/SQL statement Do not contain RETURN clause in the header Can return none, one, or many values Can contain a RETURN statement Functions: It is a named PL/SQL block that returns a value. It can be stored in the database as a schema object for repeated execution. Invoke as part of an expression Must contain a RETURN clause in the header Must return a single value Must contain at least one Package: It is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages usually have two parts, a specification and a body, but body is optional. The specification is the interface to the applications It declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification. Advantages: Packages offer several advantages: Modularity: Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development. Easier application design: When designing an application, all we need initially is the interface information in the package specifications. We can code and compile a specification without its body. Once the specification has been compiled, stored subprograms that reference the package can be compiled as well. We need not define the package bodies fully until you are ready to complete the application. Information hiding: With packages, you can specify which types, objects, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the definition of the private subprogram so that only the package (not your application) is affected if the definition changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package. Added functionality: Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. Also, they allow you to maintain data across transactions without having to store it in the database. Better performance: When you call a packaged subprogram for the first time, the whole package is loaded into memory. Therefore, subsequent calls to related subprograms in the package require no disk I/O.

In addition, packages stop cascading dependencies and so avoid unnecessary recompiling. For example, if you change the definition of a standalone function, Oracle must recompile all stored subprograms that call the function. However, if you change the definition of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.

2. How 2 migrate a Oracle Db from one version to another? There are several ways: 1) Use the Database Migrations Assistant (DBMA). 2) Export the data from the old system, then import it into the new system. 3) Copy the data across over database links. 4) Copy the data over using the SQL*Plus COPY command. 5) Output all the data to flat files and load it into the new system using SQL*Loader. 3. Maximum no of cols allowed in a table in 9i. 1000 4. How could you restrict the use of a function in a SQL? What is purity level? Functions called from SQL expressions cannot contain DML statements. Functions called from UPDATE/DELETE statements on a table, cannot contain DML on the same table. Functions called from an UPDATE or a DELETE statement on a table cannot query the same table. Functions called from SQL statements cannot contain statements that end the transactions. Calls to subprograms that break the previous restriction are not allowed in the function. The function purity level defines what structures the function reads or modifies. Following are the purity levels WNDS - Writes No Database State i.e. Function does not modify any database tables (No DML) RNDS - Reads No Database State i.e. Function does not read any tables (No select) WNPS - Writes No Package State i.e. Function does not modify any packaged variables (packaged variables are variables declared in a package specification) RNPS - Reads No Package State i.e. Function does not read any packaged variables 5. What is referential integrity? When inserting, where will you insert first- child or parent? Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table, are reflected in the primary table. While inserting, we should insert into the parent table first. 6. What is a Null statement in Oracle? The NULL statement is a no-op: it passes control to the next statement without doing anything. In the body of an IF-THEN clause, a loop, or a procedure, the NULL statement serves as a placeholder.

7. What is the difference between Union & Union All? UNION: Combine the unique rows returned by 2 SELECT statements. The number of columns and the data types of the columns being selected must be identical in all the SELECT statements used in the query. The names of the columns need not be identical. UNION operates over all of the columns being selected. NULL values are not ignored during duplicate checking. The IN operator has a higher precedence than the UNION operator. By default, the output is sorted in ascending order of the first column of the SELECT clause. UNION ALL: Combine the rows returned by 2 SELECT statements (including all duplicates) Unlike UNION, duplicate rows are not eliminated and the output is not sorted by default. The DISTINCT keyword cannot be used. 8. What is explicit and implicit commit? Explicit Commit: Occurs by executing COMMIT; Implicit Commit: Occurs when DDL command is executed or user properly exits system. 9. What is Normalization? Explain with an example the diff stages. Normalization is a design technique that is widely used as a guide in designing relational databases. Normalization is essentially a two-step process that puts data into tabular form by removing repeating groups and then removes duplicated data from the relational tables. Normalization theory is based on the concepts of normal forms. A relational table is said to be a particular normal form if it satisfied a certain set of constraints. First Normal Form: A relational table, by definition, is in first normal form. All values of the columns are atomic. That is, they contain no repeating values.

Although the table FIRST is in 1NF it contains redundant data. For example, information about the

supplier's location and the location's status have to be repeated for every part supplied. Redundancy causes what are called update anomalies. Update anomalies are problems that arise when information is inserted, deleted, or updated. For example, the following anomalies could occur in FIRST: INSERT: The fact that a certain supplier (s5) is located in a particular city (Athens) cannot be added until they supplied a part. DELETE: If a row is deleted, then not only is the information about quantity and part lost but also information about the supplier. UPDATE: If supplier s1 moved from London to New York, then six rows would have to be updated with this new information. Second Normal Form: The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF. A relational table is in second normal form 2NF if it is in 1NF and every non-key column is fully dependent upon the primary key. That is, every non-key column must be dependent upon the entire primary key. FIRST is in 1NF but not in 2NF because status and city are functionally dependent upon only on the column s# of the composite key (s#, p#). This can be illustrated by listing the functional dependencies in the table: s#—> city, status city—> status (s#,p#)—>qty The process for transforming a 1NF table to 2NF is: Identify any determinants other than the composite key, and the columns they determine. Create and name a new table for each determinant and the unique columns it determines. Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table. Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key. The original table may be renamed to maintain semantic meaning. To transform FIRST into 2NF we move the columns s#, status, and city to a new table called SECOND. The column s# becomes the primary key of this new table. The results are shown below:

Tables in 2NF but not in 3NF still contain modification anomalies. In the example of SECOND, they are: INSERT: The fact that a particular city has a certain status (Rome has a status of 50) cannot be inserted until there is a supplier in the city. DELETE: Deleting any row in SUPPLIER destroys the status information about the city as well as the association between supplier and city. Third Normal Form The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is: A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key. In other words, all nonkey attributes are functionally dependent only upon the primary key. Table PARTS is already in 3NF. The non-key column, qty, is fully dependent upon the primary key (s#, p#). SUPPLIER is in 2NF but not in 3NF because it contains a transitive dependency. A transitive dependency is occurs when a non-key column that is a determinant of the primary key is the determinate of other columns. The concept of a transitive dependency can be illustrated by showing the functional dependencies in SUPPLIER:

SUPPLIER.s# —> SUPPLIER.status SUPPLIER.s# —> SUPPLIER.city SUPPLIER.city —> SUPPLIER.status Note that SUPPLIER.status is determined both by the primary key s# and the non-key column city. The process of transforming a table into 3NF is: Identify any determinants, other the primary key, and the columns they determine. Create and name a new table for each determinant and the unique columns it determines. Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table. Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key. The original table may be renamed to maintain semantic meaning. To transform SUPPLIER into 3NF, we create a new table called CITY_STATUS and move the columns city and status into it. Status is deleted from the original table, city is left behind to serve as a foreign key to CITY_STATUS, and the original table is renamed to SUPPLIER_CITY to reflect its semantic meaning. The results are shown in the Figure below:

The results of putting the original table into 3NF have created three tables. These can be represented in "pseudo-SQL" as: PARTS (#s, p#, qty) Primary Key (s#,#p) Foreign Key (s#) references SUPPLIER_CITY.s# SUPPLIER_CITY(s#, city) Primary Key (s#) Foreign Key (city) references CITY_STATUS.city CITY_STATUS (city, status) Primary Key (city) Advantages of Third Normal Form The advantage of having relational tables in 3NF is that it eliminates redundant data, which in turn saves space and reduces manipulation anomalies. For example, the improvements to our sample database are: INSERT. Facts about the status of a city, Rome have a status of 50, can be added even though there is not supplier in that city. Likewise, facts about new suppliers can be added even though they have not yet supplied parts. DELETE. Information about parts supplied can be deleted without destroying information about a supplier or a city. UPDATE. Changing the location of a supplier or the status of a city requires modifying only one row. 10. What are the different types of triggers? Triggers are nothing but the stored procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. While a procedure is explicitly executed by a user, application, or trigger, one or more triggers are implicitly fired (executed) by Oracle when a triggering INSERT, UPDATE, or DELETE statement is issued, no matter which user is connected or which application is being used. Triggers are commonly used to:  Automatically generate derived column values  Prevent invalid transactions  Enforce complex security authorizations  Enforce referential integrity across nodes in a distributed database  Enforce complex business rules  Provide transparent event logging  Provide sophisticated auditing

 

Maintain synchronous table replicates Gather statistics on table access

Basically Triggers are four types. They are as follows: 

Row Triggers and Statement Triggers: When you define a trigger, you can specify the number of times the trigger action is to be run:  Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows  Once for the triggering statement, no matter how many rows it affects Row Triggers A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run. Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once. Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:  Make a complex security check on the current time or user  Generate a single audit record

 BEFORE and AFTER Triggers: When defining a trigger, you can specify the trigger timing-whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers. BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables. BEFORE TRIGGERS: BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:  When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.  To derive specific column values before completing a triggering INSERT or UPDATE statement. AFTER TRIGGERS: AFTER triggers run the trigger action after the triggering statement is run.

 INSTEAD OF Triggers:

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. These triggers can be only written with VIEWs.

 Triggers on System Events and User Events: You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include: System events  Database startup and shutdown  Server error message events User events  User logon and logoff  DDL statements (CREATE, ALTER, and DROP)  DML statements (INSERT, DELETE, and UPDATE) Triggers on system events can be defined at the database level or schema level. COMMIT, SAVEPOINT and ROLLBACK are not allowed in a trigger. 11. What is a cursor variable. To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, and then declare cursor variables of that type. 12. Difference between FOR & FORALL FOR: The FOR loop executes for a specified number of times, defined in the loop definition. Because the number of loops is specified, the overhead of checking a condition to exit is eliminated. The number of executions is defined in the loop definition as a range from a start value to an end value (inclusive). The integer index in the FOR loop starts at the start value and increments by one (1) for each loop until it reaches the end value. SQL> begin 2 for idx in 2..5 loop 3 dbms_output.put_line (idx); 4 end loop; 5 end; 6 / FORALL: It is an evolution of PL/SQL tables that allows us to manipulate many variables at once, as a unit. The FORALL is used for bulk collection. It can dramatically increase the performance of data manipulation code within PL/SQL. The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:

FORALL index IN lower_bound..upper_bound sql_statement; The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range. 13. What is a Table function? Table functions are a new feature in Oracle9i that allow you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to table would. The added benefit to having a table function is that you can perform transformations to the data in question before it is returned in the result set. This is of great use when performing ETL operations. 14. How to delete duplicate rows from a table. DELETE FROM WHERE rowid NOT IN (SELECT MAX(rowid) FROM group by ); 15. Top-N queries. The task of retrieving the top or bottom N rows from a database table (by salary, sales amount, credit, etc.) is often referred to as a "top-N query." This task is fairly common in application development. The most straightforward, but inefficient, way of accomplishing such a query is by retrieving all rows from the database table(s), sorting them by specified criteria, scanning from the top, and then selecting the top N rows. This is definitely not an elegant solution. So we can use a single SQL statement to perform a top-N query. You can do so either by using the ROWNUM pseudo column available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK() and DENSE_RANK(). 16. What are the pseudo-columns? A pseudo column is an item of data which does not belong in any particular table but which can be treated as if it did. Any SELECT list of columns can include these pseudo columns. Some of the pseudo-columns are:  ROWID  VERSIONS_XID  VERSIONS_OPERATION  VERSIONS_STARTSCN  VERSIONS_ENDSCN  SYSDATE  SYSTIMESTAMP  ROWNUM  ORA_ROWSCN  OBJECT_VALUE 17. How can you issue a commit in a trigger?

Triggers cannot contain COMMIT statements, unless they are marked with the PRAGMA AUTONOMOUS_TRANSACTION. However, only statements within the trigger will be committed, and not the main transaction. 18. What is package initialization? The first time the application makes a reference to a package element, the entire package (in pre-compiled form) is loaded into the SGA of the database instance, making all objects immediately available in memory. We can supplement this automatic instantiation of the package code with the automatic execution of initialization code for the package. This initialization code is contained in the optional initialization section of the package body. The initialization section consists of all statements following the BEGIN statement through the END statement for the entire package body. It is called the initialization section because the statements in this section are executed only once; the first time an object in the package is referenced (a program is called, a cursor is opened, or a variable is used in an assignment, to name a few possibilities). The initialization section initializes the package; it is commonly used to set values for variables declared and referenced in the package. The initialization section is a powerful mechanism: PL/SQL detects automatically when this code should be run. We don’t have to explicitly execute the statements, and you can be sure they are run only once. Drawbacks of Package Initialization:   

It can be dangerous to have the tool perform actions for us that are not explicitly triggered by a user or developer action. It is harder to trace actions triggered automatically by the tool. Executable statements buried in initialization sections are much harder to maintain.

What'll happen if you define a function in package definition but not in package body and viceversa? Will it give a compile time or runtime error? Nothing is going to happen if we define a function in the package definition but not in the package body. It won’t give any compilation error or runtime error. It will compile successfully. The vice versa is not possible. It will give a compilation error if we don’t define the function in the package header but the function is present in the package body. 19. When do we face Snapshot too old error?  

The rollback information itself is overwritten so that Oracle is unable to rollback the (committed) transaction entries to attain a sufficiently old enough version of the block. The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.

20. When a view can/can't be updated? We can’t update a view if it contains any of the following:  

Set Operators (INTERSECT, MINUS, UNION, UNION ALL) DISTINCT

       

Group Aggregate Functions (AVG, COUNT, MAX, MIN, SUM, etc.) GROUP BY Clause ORDER BY Clause CONNECT BY Clause START WITH Clause Collection Expression In A Select List Sub query In A Select List Join Query

21. What is pragma autonomous transaction? A database transaction is a unit of work that must either succeed completely or fail completely. In Oracle, a transaction starts with the first Data Management Language (DML) statement you execute, and ends when you issue a COMMIT statement to save the transaction or a ROLLBACK statement to throw it away. The "all or nothing" nature of transactions can make it difficult to log error messages to a database table because the INSERT statement that is used to write the log entry will be undone when the transaction is rolled back. Oracle provides a convenient way around this dilemma: the autonomous transaction. Autonomous transactions execute in their own context from the current transaction. They can be committed or rolled back separately without affecting the transaction in progress. This makes them ideal for writing to an error log table. Upon detecting an error in the transaction, you can insert a row into the error log table and commit it, and then roll back the main transaction without losing the insert. Because they're separate from the main transaction, autonomous transactions cannot see the current state of rows that have been modified. It is as if they are in a separate session, until the main transaction is committed, they're not available to the autonomous one. The reverse is not true, however: The main transaction can see the results of an autonomous transaction that has already committed. To create autonomous transactions, you must use the PL/SQL statement PRAGMA AUTONOMOUS_TRANSACTION at the top level of an anonymous block, or in the declaration section of a stored procedure, function, package, or trigger. SQL Server statements executed within such a block or procedure are autonomous.

22. Are there any such tables for which they are not available in all_tables? ALL_TABLES view return information about all objects to which we have access, regardless of who owns them. For example, a query to ALL_TABLES returns a list not only of all of the relational tables that you own, but also of all relational tables to which their owners have specifically granted you access (using the GRANT command). So the tables, which are not owned by the current user (who has logged in) and the current user is not having a GRANT access, will not be visible. 23. Where can you find the column information other than a desc operation? We have a view called ALL_TAB_COLUMNS/USER_TAB_COLUMNS, from which we can have the column information.

24. What are the diff types of exceptions? Explain. There are four kinds of exceptions in PL/SQL: NAMED SYSTEM EXCEPTIONS: The exceptions, which are already given names by PL/SQL, are declared in the STANDARD package in PL/SQL. You do not have to declare them in your own programs. CURSOR_ALREADY_OPEN: Trying to OPEN a cursor that was already OPEN. CLOSE a cursor before you try to OPEN or re-OPEN it. DUP_VAL_ON_INDEX: Trying to INSERT or UPDATE to store duplicate values in a column or columns in a row, which is restricted by a unique index. INVALID_CURSOR: Made reference to a cursor that did not exist. This usually happens when we try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed. INVALID_NUMBER: PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. LOGIN_DENIED: Trying to log onto the Oracle RDBMS with an invalid username-password combination. NO_DATA_FOUND: This exception is raised in three different scenarios:  You executed a SELECT INTO statement (implicit cursor) that returned no rows.  You referenced an uninitialized row in a local PL/SQL table.  You read past end of file with UTL_FILE package. NOT_LOGGED_ON: Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS. PROGRAM_ERROR: PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support." STORAGE_ERROR: Program ran out of memory or memory was in some way corrupted. TIMEOUT_ON_RESOURCE: A timeout occurred in the RDBMS while waiting for a resource. TOO_MANY_ROWS: A SELECT INTO statement returned more than one row. If we want to FETCH more than one row then we need to use a CURSOR. TRANSACTION_BACKED_OUT: The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action. VALUE_ERROR: PL/SQL raises the VALUE_ERROR whenever it encounters an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. ZERO_DIVIDE: Your program tried to divide by zero. NAMED PROGRAMMER-DEFINED EXCEPTIONS: Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You

then raise the exceptions explicitly in the program. To handle an exception, we must have a name for that exception. Because PL/SQL cannot name these exceptions for us, we must do so ourselves by declaring an exception in the declaration section of our PL/SQL block. We can declare an exception by listing the name of the exception we want to raise in our program, followed by the keyword EXCEPTION, as follows: exception_name EXCEPTION; UNNAMED SYSTEM EXCEPTIONS: Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax. The pragma EXCEPTION_INIT must appear in the declaration section of a block, after the declaration of the exception name used in the pragma, as shown below: DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, error_code_literal); BEGIN Where exception_name is the name of an exception and error_code_literal is the number of the Oracle error (including the minus sign, if the error code is negative, as is almost always the case). In the following program code, I declare and associate an exception for this error: ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) child record found. This error occurs if I try to delete a parent record while there are child records still in that table. A child record is a record with a foreign key reference to the parent table: PROCEDURE delete_company (company_id_in IN NUMBER) IS /* Declare the exception. */ still_have_employees EXCEPTION; /* Associate the exception name with an error number. */ PRAGMA EXCEPTION_INIT (still_have_employees, -2292); BEGIN /* Try to delete the company. */ DELETE FROM company WHERE company_id = company_id_in; EXCEPTION /* If child records were found, this exception is raised! */ WHEN still_have_employees THEN DBMS_OUTPUT.PUT_LINE (' Please delete employees for company first.'); END;

UNNAMED PROGRAMMER-DEFINED EXCEPTIONS: Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application. PROCEDURE RAISE_APPLICATION_ERROR (error_number_in IN NUMBER, error_msg_in IN VARCHAR2); Where error_number_in is the error number you have assigned to this error. The error_msg_in argument is the message that will be sent back with the error code to the client program.

25. What are the diff types of Oracle operators? Operators

Meaning & Example Equals to

= WHERE = ‘VALUE’ Less than < WHERE < ‘VALUE’ Greater than > WHERE > ‘VALUE’ Less than or equal to <= WHERE <= ‘VALUE’ Greater than or equal to >= WHERE >= ‘VALUE’ Not equal to <> WHERE <> ‘VALUE’ Greater than or equal to the low value and less than or equal to the high value BETWEEN WHERE BETWEEN ‘VALUE1’AND ‘VALUE2’ At least one row is present in the subquery EXISTS

SELECT FROM a WHERE EXISTS (SELECT 1 FROM b WHERE a. = b. ) Equals one of the values in the list

IN WHERE IN (‘VALUE1’,’VALUE2’)

Matches the pattern LIKE WHERE LIKE ‘FY9_TEST%’ Less than the low value or greater than the high value NOT BETWEEN

NOT EXISTS

WHERE NOT BETWEEN ‘VALUE1’,’VALUE2’ No rows are present in the subquery SELECT FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE a. = b. ) Does not equal any of the values in the list

NOT IN WHERE NOT IN (‘VALUE1’,’VALUE2’) Does not match the pattern NOT LIKE WHERE NOT LIKE ‘%STAPLES%’ Used only with NULL as the value IS No columns in HDW views are null. Used only with NULL as the value IS NOT No columns in HDW views are null. Equal to every value in the list

= ALL

SELECT FROM a WHERE = ALL (SELECT FROM b WHERE a. = b. ) Does not equal even one of the values in the list

!= ALL This is identical to the NOT IN operator. Less than all of the values in the list

< ALL

SELECT FROM a WHERE < ALL (SELECT FROM b WHERE a. = b. ) Greater than all of the values in the list

> ALL

<= ALL

SELECT FROM WHERE > ALL (SELECT FROM WHERE > ‘VALUE’) Less than or equal to all of the values in the list

SELECT FROM WHERE <= ALL (SELECT FROM WHERE > ‘VALUE’) Greater than or equal to all of the values in the list >= ALL

SELECT FROM WHERE >= ALL (SELECT FROM WHERE > ‘VALUE’) Equal to at least one value in the list

= ANY This is identical to the IN operator Less than at least one value in the list < ANY

> ANY

<= ANY

>= ANY

!= ANY

SELECT FROM WHERE < ANY (SELECT FROM b WHERE a. = b. ) Greater than at least one value in the list SELECT FROM WHERE > ANY (SELECT FROM b WHERE a. = b. ) Less than or equal to at least one value in the list SELECT FROM WHERE <= ANY (SELECT FROM b WHERE a. = b. ) Greater than or equal to at least one value in the list SELECT FROM WHERE >= ANY (SELECT FROM b WHERE a. = b. ) Does not equal every one of the values in the list SELECT FROM WHERE != ANY (SELECT FROM b WHERE a. = b. )