SQL Operators

This document discusses SQL operators used with Oracle Lite. Topics include:

Ø  SQL Operators Overview

Ø  Arithmetic Operators

Ø  Character Operators

Ø  Comparison Operators

Ø  Logical Operators

Ø  Set Operators 

Ø  Other Operators

SQL Operators Overview

An operator manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL. There are two general classes of operators: unary and binary. Oracle Lite SQL also supports set operators

Unary Operators

A unary operator uses only one operand. A unary operator typically appears with its operand in the following format:

                                operator operand

Binary Operators

A binary operator uses two operands. A binary operator appears with its operands in the following format:

                                operand1 operator operand2

Set Operators

Set operators combine sets of rows returned by queries, instead of individual data items. All set operators have equal precedence. Oracle Lite supports the following set operators:

Ø  UNION

Ø  UNION ALL

Ø  INTERSECT

Ø  MINUS

The following lists the levels of precedence among the Oracle Lite SQL operators from high to low. Operators listed on the same line have the same level of precedence:

Table 2-1 Levels of Precedence of the Oracle Lite SQL Operators

Precedence Level	SQL Operator
1	Unary + - arithmetic operators, PRIOR operator
2	* / arithmetic operators
3	Binary + - arithmetic operators, || character operators
4	All comparison operators
5	NOT logical operator
6	AND logical operator
7	OR logical operator

Other Operators

Other operators with special formats accept more than two operands. If an operator receives a null operator, the result is always null. The only operator that does not follow this rule is CONCAT.

Arithmetic Operators

Arithmetic operators manipulate numeric operands. The - operator is also used in date arithmetic.

Table 2-2 Arithmetic Operators

Operator	Description	Example
+ (unary)	Makes operand positive	SELECT +3 FROM DUAL;
- (unary)	Negates operand	SELECT -4 FROM DUAL;
/	Division (numbers and dates)	SELECT SAL / 10 FROM EMP;
*	Multiplication	SELECT SAL * 5 FROM EMP;
+	Addition (numbers and dates)	SELECT SAL + 200 FROM EMP;
-	Subtraction (numbers and dates)	SELECT SAL - 100 FROM EMP;

Character Operators

Character operators are used in expressions to manipulate character strings.

Table Character Operators

Operator	Description	Example
||	Concatenates character strings	SELECT 'The Name of the employee is: ' || ENAME FROM EMP;

Concatenating Character Strings

With Oracle Lite, you can concatenate character strings with the following results:

Ø  Concatenating two character strings results in another character string.

Ø  Oracle Lite preserves trailing blanks in character strings by concatenation, regardless of the strings' datatypes.

Ø  Oracle Lite provides the CONCAT character function as an alternative to the vertical bar operator. For example:

   SELECT CONCAT (CONCAT (ENAME, ' is a '),job) FROM EMP WHERE SAL > 2000;

This returns:

CONCAT(CONCAT(ENAME

-------------------------

KING       is a PRESIDENT

BLAKE      is a MANAGER

CLARK      is a MANAGER

JONES      is a MANAGER

FORD       is a ANALYST

SCOTT      is a ANALYST

6 rows selected.

Ø  Oracle Lite treats zero-length character strings as nulls. When you concatenate a zero-length character string with another operand the result is always the other operand. A null value can only result from the concatenation of two null strings.

Comparison Operators

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN.

Table Comparison Operators

Operator	Description	Example
=	Equality test.	SELECT ENAME "Employee" FROM EMP WHERE SAL = 1500;
!=, ^=, <>	Inequality test.	SELECT ENAME FROM EMP WHERE SAL ^= 5000;
>	Greater than test.	SELECT ENAME "Employee", JOB "Title" FROM EMP WHERE SAL > 3000;
<	Less than test.	SELECT * FROM PRICE WHERE MINPRICE < 30;
>=	Greater than or equal to test.	SELECT * FROM PRICE WHERE MINPRICE >= 20;
<=	Less than or equal to test.	SELECT ENAME FROM EMP WHERE SAL <= 1500;
IN	"Equivalent to any member of" test. Equivalent to "= ANY".	SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD');
ANY/ SOME	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to FALSE if the query returns no rows.	SELECT * FROM DEPT WHERE LOC = SOME ('NEW YORK','DALLAS');
NOT IN	Equivalent to "!= ANY". Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM DEPT WHERE LOC NOT IN ('NEW YORK', 'DALLAS');
ALL	Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to TRUE if the query returns no rows.	SELECT * FROM emp WHERE sal >= ALL (1400, 3000);
[NOT] BETWEEN xand y	[Not] greater than or equal to x and less than or equal to y.	SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000;
EXISTS	TRUE if a sub-query returns at least one row.	SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);
x [NOT] LIKEy [ESCAPE z]	TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character following ESCAPE is interpretted litteraly, useful when y contains a percent (%) or underscore (_).	SELECT * FROM EMP WHERE ENAME LIKE '%E%';
IS [NOT] NULL	Tests for nulls. This is the only operator that should be used to test for nulls.	SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;

Logical Operators

Logical operators manipulate the results of conditions.

Table Logical Operators

Operator	Description	Example
NOT	Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.	SELECT * FROM EMP WHERE NOT (job IS NULL) SELECT * FROM EMP WHERE NOT (sal BETWEEN 1000 AND 2000)
AND	Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.	SELECT * FROM EMP WHERE job='CLERK' AND deptno=10
OR	Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.	SELECT * FROM emp WHERE job='CLERK' OR deptno=10

Set Operators

Set operators combine the results of two queries into a single result.

Table Set Operators

Operator	Description	Example
UNION	Returns all distinct rows selected by either query.	SELECT * FROM (SELECT ENAME FROM EMP WHERE JOB = 'CLERK' UNION SELECT ENAME FROM EMP WHERE JOB = 'ANALYST');
UNION ALL	Returns all rows selected by either query, including all duplicates.	SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = 'CLERK' UNION SELECT SAL FROM EMP WHERE JOB = 'ANALYST');
INTERSECT and INTERSECT ALL	Returns all distinct rows selected by both queries.	SELECT * FROM orders_list1 INTERSECT SELECT * FROM orders_list2
MINUS	Returns all distinct rows selected by the first query but not the second.	SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = 'PRESIDENT' MINUS SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

Note: : The syntax for INTERSECT ALL is supported, but it returns the same results as INTERSECT.

Other Operators

The following lists other operators:

Table Other Operators

Operator	Description	Example
(+)	Indicates that the preceding column is the outer join column in a join.	SELECT ENAME, DNAME FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO (+);
PRIOR	Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between the parent and child rows.	SELECT EMPNO, ENAME, MGR FROM EMP CONNECT BY PRIOR EMPNO = MGR;

           For Oracle online training classes please contact : training@virtualnuggets.com
                                             http://www.virtualnuggets.com/

Introduction to Oracle Datatypes

Each column value and constant in a SQL statement has a data type, which is associated with a specific storage format, constraints, and a valid range of values. When you create a table, you must specify a datatype for each of its columns.

Oracle provides the following categories of built-in datatypes:

• Overview of Character Datatypes
• Overview of Numeric Datatypes
• Overview of DATE Datatype
• Overview of LOB Datatypes
• Overview of RAW and LONG RAW Datatypes
• Overview of ROWID and UROWID Datatypes

The following sections that describe each of the built-in datatypes in more detail.

Overview of Character Datatypes

The character datatypes store character (alphanumeric) data in strings, with byte values corresponding to the character encoding scheme, generally called a character set or code page.

The database's character set is established when you create the database. Examples of character sets are 7-bit ASCII (American Standard Code for Information Interchange), EBCDIC (Extended Binary Coded Decimal Interchange Code), Code Page 500, Japan Extended UNIX, and Unicode UTF-8. Oracle supports both single-byte and multibyte encoding schemes.

This section includes the following topics:

·         CHAR Datatype

·         VARCHAR2 and VARCHAR Datatypes

·         Length Semantics for Character Datatypes

·         NCHAR and NVARCHAR2 Datatypes

·         Use of Unicode Data in Oracle Database

·         LOB Character Datatypes

·         LONG Datatype

Oracle Business Intelligence Applications Overview

The benefit of accessing data from across the enterprise and delivering deep insight directly to business users is faster and more informed decisions that help the organization optimize resources, reduce costs, and improve the effectiveness of front- and back-office activities ranging from sales to human resources (HR) to procurement. Oracle Business Intelligence Applications support eleven different functional areas with best-practice analytics.

"With Oracle’s prebuilt analytic solutions for sales, marketing, and service, we were able to deploy a powerful BI solution in under three months. Verizon Business employees, across the enterprise, are now empowered with relevant, complete information tailored to their role.”

Oracle Financial Analytics

Oracle Financial Analytics provides organizations with better visibility into the factors that drive revenues, costs, and shareholder value. With dashboards that track key performance indicators (KPIs), managers can see how staffing costs and supplier performance correlate with increased revenue and customer satisfaction. Oracle Financial Analytics also offers insight into the general ledger, product or customer profitability, actual performance versus budget, and payables and receivables. As a result, managers are empowered to make the best decisions, close the books faster, and comply with all regulatory laws.

Dashboards and alerts allow financial and business managers to monitor financial performance in real-time. Detailed financial reports generated at a greater frequency and delivered to a broader range of users allow managers to understand how their business is performing while there is still time to make adjustments. Oracle Financial Analytics enables companies to more effectively manage their financial performance and improve business by:

·  Analyzing detailed, transaction-level data to understand the factors driving revenue, cost, and Proftability across business units, geographic locations, sales territories, customers, products, and distribution channels in time to take action

·       Optimizing cash flow through detailed accounts receivable, accounts payable, and inventory analysis

·      Enhancing regulatory reporting to reduce the time it takes to generate periodic financial statements or reports for regulatory compliance to laws such as the Sarbanes-Oxley Act

·  Ensuring budget compliance with effective expense controls that deliver expense line details to departmental managers in time to take corrective action.

·   Improving cash collections and reducing days sales outstanding (DSO) by identifying slow-paying customers or those with billing issues.

Figure 1. Oracle Financial Analytics includes prebuilt dashboards that pull information from enterprise systems and provide timely, complete data to corporate decision makers.

Oracle Procurement and Spend Analytics

Oracle Procurement and Spend Analytics optimizes an organization’s supply-side performance by integrating data from across the enterprise value chain and enabling executives, managers, and frontline employees to make more informed and actionable decisions. Organizations using Oracle Procurement and Spend Analytics benefit from increased visibility into corporate expenditures and a complete view of the procure-to-pay process—including comprehensive analyses of procurement, supplier performance,  supplier payables, and employee expenses.With complete, end-to-end insight into spend patterns and supplier performance, organizations can significantly reduce costs, enhance profitability, increase customer satisfaction, and gain competitive advantage.

The solution allows companies to more effectively manage their expenditures and improve business performance by

·         Providing timely direct and indirect spending data to all departments

·      Reducing data collection time with source-specific adapters that extract and   transform data from disparate enterprise systems—both Oracle and non-Oracle-based—so managers can spend more time on higher value activities such as analysis

·         Analyzing detailed, transaction-level data to understand the factors driving supplier

·         performance and procurement costs

·   Identifying cost savings across business units, geographic locations, products, and procurement organizations

·     Improving performance by identifying suppliers that price inconsistently or do not adhere to price schedules.

Figure 2 Powerful dashboards in Oracle Procurement and Spend Analytics track spend, supplier performance, procurement performance, and employee expenses. 

Oracle Supply Chain and Order Management Analytics

Oracle Supply Chain and Order Management Analytics delivers deep customer insight into the order-to-cash process and supply chain—including inventory management and finished goods—so organizations can make better decisions at each stage of the order lifecycle. Oracle Supply Chain and Order Management Analytics enables organizations to assess inventory levels, predict product fulfillment needs before an order has been booked, identify potential order backlog issues, and stay on top of critical accounts receivable and DSO issues. The insights gained from this analysis lead to actionable steps to address short-term issues and provide strategic input into how to transform the supply chain and order management process.

Oracle Supply Chain and Order Management Analytics enables companies to more effectively manage their customers and improve business performance by

·         Providing timely order, margin, cancellations, discounts, and returns data to operations departments

·      Reducing the time spent compiling, reconciling, and consolidating data from fragmented systems so business users can spend more time analyzing, making proactive decisions, and taking action

·   Improving inventory management for products that consistently get into backlog due to lack of appropriate stock level

·         Enabling effective management of order booking, billing, and backlog.

Figure 3. Oracle Supply Chain and Order Management Analytics includes prebuilt dashboards that pull information from multiple enterprise systems and provide timely, complete data on inventory, orders, and returns to corporate decision makers

Oracle Project Analytics

Oracle Project Analytics delivers insight into the financial performance of projects so all team members can seamlessly track the project lifecycle. Oracle Project Analytics provides hundreds of out-of-the-box, standards-based KPIs and reports for project profitability analysis, funding and budgets, cost, revenue, and billing. Information is personalized, relevant, and actionable to improve project performance and profitability. Oracle Project Analytics also delivers cross-functional analysis—including project-based analysis of accounts receivable and accounts payable, invoice-aging analysis, or status of procurement transactions by project. As a result all employees—given their level of security—can see a personalized, consistent version of the truth and take timely, corrective actions to achieve project objectives.

•  To improve performance of both projects and project portfolios, Oracle Project Analytics allows team members and executives to
•  Monitor projects and control the risks that lead to budget and schedule overruns with out-of-the box, role-based dashboards
• a particular program or project and verify how it is performing for a given time period or inception-to-date metrics
•  See past, present, and future performance—including estimated metrics at project completion
• Drill down to detailed cost information for a specific project such as line items sorted by task, expenditure category, resource, or person 

Figure 4. Oracle Project Analytics monitors project performance so managers can avoid budget and schedule overruns.

Oracle Human Resources Analytics:

Oracle Human Resources Analytics helps organizations manage their talent and analyze workforce performance by integrating critical data from HR, financial, and other enterprise systems. It transforms information silos into comprehensive, timely, and actionable insight into how various factors impact workforce and business performance. Managers and line-of-business managers receive timely information headcount costs and overtime pay—all segmented by geography, job category, division, and pay grade. This relevant information is delivered to executives, HR managers, and business line managers through personalized dashboards, metrics, and alerts. As a result, they can understand how workforce factors affect individual departments and can take appropriate actions.

Oracle Human Resources Analytics enables companies to more effectively manage and improve their workforce by providing tools that allow decision-makers to

·  Understand compensation’s impact on employee performance by correlating salaries with employee performance and turnover

·    Discover the root causes of workforce turnover and analyzing its impact on departmental performance and company costs

·  Optimize staffing levels and compensation to ensure satisfactory delivery of service while maintaining the lowest effective headcount

·     Measure the quality of recruiting efforts, optimize candidate sourcing, analyze the recruitment pipeline, examine the hire-to-retire process efficiency, and monitor vacancies

·  Assess the HR organization’s learning offerings and examine how those programs affect employee performance and tenure.

Figure 5. Oracle Human Resource Analytics allows HR and business managers to understand and adjust the factors driving workplace performance.

Oracle Sales Analytics

Oracle Sales Analytics provides timely, fact-based insight into the entire sales process. This insight is proactively delivered to salespeople in the field via laptop, personal digital assistant, or mobile phones—ensuring they always have the latest information they need to make informed decisions and increase win rates. Sales executives can receive alerts when the pipeline suddenly contracts or territory bookings drop below weekly targets—enabling them to take appropriate corrective action. The benefits are faster and more informed decisions that help the sales organization compete more effectively, lower sales costs, and achieve better results.

Oracle Sales Analytics includes prebuilt data models, more than 200 metrics, and best practices based on Oracle’s experience across thousands of sales force automation implementations. The solution allows companies to increase their revenues and improve business performance by

·    Providing sales professionals with timely insight into sales opportunities, including how long each opportunity has been in the pipeline and the current status of team selling efforts

·         Identifying critical opportunities so executives can assign the appropriate resources to increase the chance of winning

·         Analyzing pipeline opportunities to determine actions required to meet sales targets

·         Highlighting which products and customer segments generate the most revenue

·         Showing which competitors are faced most often and how to win against them

·         Identifying up-sell and cross-sell opportunities within existing accounts

Figure 6. Oracle Sales Analytics provides visibility into the sales pipeline and sales performance.

Oracle Price Analytics

Oracle Price Analytics provides organizations with valuable insight into product demand, customer price sensitivity, and overall pricing effectiveness. The application allows organizations to analyze and understand important information on product velocity, the impact of discounting, price promotion effectiveness, and product profitability across channels. Performance analysis offers fact-based insight into product, customer, and overall business unit profitability. Drill-through capabilities provide access to detailed transactional information. Leader-laggard charts and price waterfall analyses compare customer revenue and product performance against forecasts, commitments, and previous time periods. Oracle Price Analytics takes the guesswork out of setting prices by delivering consistent data to managers who can make insight-driven pricing decisions, measure pricing effectiveness, and adjust or correct prices as needed.

Oracle Price Analytics enables companies to effectively manage prices, improve margins, and enhance business performance by allowing managers to

·    Understand price drivers by considering the bottom-line impact of all discounts, services, incentives, rebates, and marketing programs

·      Identify pricing improvement opportunities by highlighting underperforming segments and critical areas of revenue leakage

·   Monitor and optimize performance by continuously analyzing and refining pricing programs to maximize margins and profits

·         Find patterns in large sets of pricing data with data mining and predictive technology plug-ins

·         Deliver fine-grained prices and price policies to tailored buyers through analytics that determine price segments with price profiles and suggested price floors and corridors

7. Oracle Price Analytics provides rich performance data that drives better pricing decisions and, in turn, improves an organization’s profitability.

Oracle Marketing Analytics

Oracle Marketing Analytics provides marketing professionals with a new level of business insight by unlocking valuable information hidden in systems across the enterprise. With Oracle Marketing Analytics, marketing professionals can manage and track campaign performance, segment customers with data from enterprisewide systems, retain the most valuable customers, generate demand at the lowest costs, and reduce wasted spend. Access to actionable information drives greater returns on marketing spend, reduces marketing costs, and increases revenue-generating opportunities.

Oracle Marketing Analytics improves both marketing and overall business performance by

·         Gathering information on customer behavior from transaction history and correlating it with customer lifetime value, churn risk, or behavioral attributes to gain insight into customer clusters and better inform treatment strategies

·      Monitoring metrics critical to contact center campaigns—including the number of calls made, average days to follow-up, cross-sell and up-sell effectiveness, and total order revenue—so marketers can adapt their marketing approach and remove offers with low response rates

·       Tracking number of emails delivered, open rate, bounce-backs, and offer effectiveness in real time so marketers can measure the effectiveness of email campaigns and fix bottlenecks

·      Providing information on which products customers are likely to buy and insight into which products make effective bundles 

·   Aggregating information from various data sources so marketers can calculate, monitor, and build customer investment strategies based on critical metrics such as customer profitability 

Figure 8. Oracle Marketing Analytics tracks every step of a campaign so marketing professionals can optimize the campaign and ensure that marketing dollars generate measurable returns.

Oracle Loyalty Analytics

 Oracle Loyalty Analytics provides timely, fact-based insight into the entire loyalty program process—including the effectiveness of loyalty promotions and partner relationships. It delivers insight that is personalized, relevant, and actionable. As a result, loyalty marketing managers can analyze member segments, identify which promotions to run and which members to target, and measure promotion effectiveness. Loyalty partner managers can analyze partner contributions to program success and measure joint promotion effectiveness. And executives can analyze loyalty program status, track budgets, evaluate membership trends and details, and summarize rewards and redemption trends. With Oracle Loyalty Analytics, loyalty programs can be optimized to drive member behavior, build value, and reduce costs.

Oracle Loyalty Analytics enables companies to effectively manage loyalty programs by allowing managers to

·         See a complete picture of customer buying patterns, customer value, loyalty promotion effectiveness, liabilities, and customer behavior drivers

·         Analyze the cost and revenue associated with the loyalty program as well as program liability

·         Receive timely information on member accruals and redemptions sliced and diced by dimensions such as tier class/tier, customer geography, segment, or promotion

·         Track member transactions and analyze members’ movements among tiers

·         Evaluate partner performance within the loyalty program by analyzing the partner’s contribution to the overall success of the program

Figure 9. Dashboards in Oracle Loyalty Analytics highlight the revenues and liabilities associated with a customer loyalty program.

Oracle Service Analytics

 Oracle Service Analytics allows companies to rigorously track and analyze key service center metrics—including service request aging, service request resolution, and service activities per employee—and take the appropriate action to maintain or improve performance. Customer service representatives (CSRs) can view the entire customer relationship; discover potential issues; and identify opportunities to cross-sell, up-sell, and improve customer satisfaction. The solution provides best-practice metrics, alerts, and reports that allow employees to make the best possible decisions. By providing powerful insight to analyze all aspects of service center performance, Oracle Service Analytics can help deliver a best-in-class service center with satisfied customers, low operating costs, and high revenue per customer.

The solution enables companies to more effectively manage their service centers and improve business performance by

·       Delivering insight directly to CSRs via their CRM application so they do not have to change screens to receive the latest information

·       Providing a complete, real-time view of the customer account—by drawing data from systems used by field sales and accounting—so CSRs can quickly resolve issues, increase cross- and up-selling, tailor service based on the customer value, and achieve higher levels of customer satisfaction and loyalty

·     Tracking top KPIs for service initiatives—such as service request aging, service request resolution, and service activities per employee—so managers understand the factors driving service requests 

·     Allowing management to evaluate performance at the individual CSR and site level—using metrics that include cost to serve, average resolution time, and contact profitability—and compare results to both internal targets and external benchmarks.

Figure 10. Dashboards provide CSRs with a complete view of the customer account and allow management to track overall service center performance.

Oracle Call Center Telephony Analytics

To provide a complete picture of contact center performance, Oracle Contact Center Telephony Analytics accesses information from Web servers, interactive voice response systems, automatic call distributors, and computer telephony integration systems as well as from CRM, financial, HR, and e-mail applications. When the key reasons behind operating trends are understood, managers and CSRs are able to increase customer satisfaction and retention, monitor channel usage and migration, improve CSR effectiveness, reduce employee turnover, and maximize productivity and resource utilization.

Oracle Contact Center Telephony Analytics allows companies to more effectively manage their contact centers and improve business performance by

·         Tracking top KPIs for service initiatives, including first and final resolution, average speed of answer, average handle time, call abandonment rate, and service levels

·       Allowing CSRs to direct customers to lower-cost service options such as IVR or self-service Web sites to address simple inquiries so they are free to focus on higher-value calls

·      Tracking CSR transfer rates, revenue per CSR, average handle time, and time spent by a CSR on after-call work to help supervisors identify high-performing CSRs as well as those in need of additional training 

·    Integrating workforce management information—generally not available in call center reports—so management can determine how factors such as tenure, education, compensation, and training impact CSR turnover and performance

Figure 11. Oracle Contact Center Telephony Analytics provides a complete view of contact center performance. 

For Oracle online Training online classes please contact : training@virtualnuggets.com
                                      http://www.virtualnuggets.com/