My Favorite SQL Functions And Operators

My Favorite SQL Functions and Operators Book Cover My Favorite SQL Functions and Operators
Common Core Programming
Phillip Garriss
IT
Writedge
10/8/2015
3

This covers some of the author's favorite SQL Functions and Operators like Decode, Instr, LTrim, Replace, RTrim, Substr, Over, Partition By, Union, Union All etc.  There are basic concepts and advanced topics.

 

 Post1Pic1

My Favorite SQL Functions And Operators

In this post:

1.       Common SQL Functions

2.       Advanced Functions and Operators

 

Common SQL Functions

Databases contain tables and other objects used for storing, analyzing, and manipulating personal and corporate data.  The following section will provide a quick reference for many of these highly used SQL commands that can be used with a variety of modern day Databases.

 Coalesce:

 This command takes a large group of items and returns the first item in the list.  For instance the following statement will return “a”.

 SELECT COALESCE(‘a’,’b’,’c’) FROM dual;

 

Decode:

 The Decode command locates a string value and changes it as directed by the parameters.  The example below changes the string to ‘BRE’, ‘LUN’, or ‘DIN’ depending on the value of the food type selected.

 SELECT DECODE(food_choice,’eggs’,’BRE’,’sandwitch’,’LUN’,’steak’,

‘DIN’,’Unknown’)

FROM meals;

 

InitCap:

 Use the InitCap function to convert the first string character to upper case.  Here is a selection example:

SELECT InitCap (‘wig’) FROM dual; 

–The output will be Wig.

 

Instr:

 This function will return the start position of a substring within a selected column.  If the value is not there it will return 0.

 

Lower:

Use this function to convert upper case characters in a string to lower case.  Here is a selection example:

 SELECT Lower (‘WIG’) FROM dual;  –The output will be wig.

 

LPAD:

 Use this tool to add a specified number of characters or numbers (i.e. 0) to the left of a value.

 

LTRIM:

 Use this tool to remove spaces from the left hand side of a value.

 

Pivot:

 The pivot function is very useful when dealing with columns that need to be displayed as rows or vice versa.  For instance if an error log is used where the first column is the table name, the second column is the field name, and the third column is the field value etc, the developer will need to use something like the pivot function in Microsoft SQL or Oracle to pivot the values and analyze the data easier.  When incorporating this term in the from clause the values in the columns specified will become the column headers in the result set.

Developers can find good examples of selecting data using the pivot functionality in the following locations:

Microsoft SQL Server:

 http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

 Oracle DB:

 http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

http://www.adp-gmbh.ch/ora/sql/examples/pivot.html

 

 Replace:

 This command replaces values with new values in the selected string.

This example removes all the dashes from a security number.

 SELECT REPLACE(ssn, ‘-‘, ”) FROM employees;

 

RPAD:

 Use the RPAD tool to add a specified number of characters or numbers (ie. 0) to the right of a value.

 

RTRIM:

 Use this tool to remove spaces from the right hand side of a value.

 

 SysDate:

 This is the Current Date from the OS on the Server hosting the DB.  SysDate() with the parenthesis is used with Microsoft products.

 

Substr:

 This function will select a substring using a start and end position.  If the value is less than the end position it will just select that value.  The example below selects up to three characters in a book title.  The Microsoft equivalent of this command is SUBSTRING.  1

 SELECT SUBSTR(title,1,3) FROM book_titles;

 

Upper:

 Use the Upper function to convert lower case characters to upper case.  Here is a selection example:

 SELECT Upper (‘wig’) FROM dual; 

–The output will be WIG.

 

Variance:

 Variance is a function used to see how much a value varies from the other selected values in a query result set.

 

 

Advanced Functions and Operators

As seen in the first section a function is typically used to manipulate data groups and columns.  An operator is typically used to relate data sets together.  The following section will provide a quick reference for some of the more advanced SQL functions and operators.

 

Intersect:

 This command is used to determine the intersection of data between one or more tables.  The select statement columns have to correspond with each other if a developer wants to insert this command between them.  The Intersect statement below shows employees making less than $50000 who also made more than $80000 in company profits.

 SELECT employee FROM employee

WHERE salary < 50000

INTERSECT

SELECT employee FROM

(SELECT employee,

                 SUM(profit)

                FROM sales

                 GROUP BY employee

                 HAVING SUM(profit) > 80000

                );

Additional Tip:  A Vin Diagram like the one below can be useful to display intersecting information.

Post1Pic2

Minus:

 Minus is used to remove a selection of table records from another set of table records.  The select statements have to be the same in order to insert the minus command between them.  See the Intersect example for a similar querying technique and an example.

 

Over:

 Developers use the Group By command to group records with similar data and they use the Having command to filter those record groups.   Partition by will determine an aggregate for a group and return that one value in each record that makes up the record group.  Over will run an aggregate function over every record and return that value for every record unless the partition by statement is used to filter down to a smaller group of records. 

For instance, if a developer calculates the Sum(retail_cost) Over () he or she will get the Overall Sum for the entire result of the records selected and it will then return for every record in the table.

 The following is an example of using OVER with an ORACLE DB:

It will first pull back all of the employee records with a salary greater than 40000.  Then it will search for the first salary value within those record, and return the minimum salary and employee combination for every record.  A similar technique can be used with MS SQL.

 SELECT department,

               firstname,

               lastname,

               salary,

MIN(TO_CHAR(salary)||’ ‘||firstname||’-‘||lastname) OVER (ORDER BY salary) mv

FROM employee_records

WHERE salary > 40000;

 

Other aggregate Oracle functions include:

 First_Value

Last_Value

Min

Max

Row_Number

 

Some aggregate Microsoft functions include:

 Avg

Count

Sum

 1.       http://msdn.microsoft.com/en-us/library/ms189461.aspx

 

Partition By:

 Developers use the Group By command to group records with similar data and they use the Having command to filter those record groups.   Partition by will determine an aggregate for a group and return that one value in each record that makes up the record group.  Over will run an aggregate function over every record and return that value for every record unless the partition by statement is used to filter down to a smaller group of records.

 For instance, if a developer calculates the Sum(retail cost) Over (Partition By Order_ID, Employee_ID) he or she will get a different sum every time the order_id and employee_id changes.

 The following references were used for this section of the article:

1. http://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and- 

2. http://www.midnightdba.com/Jen/2010/10/tip-over-and-partition-by/

 There is a great example of using the partition command at:

1.        https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122801500346829407

 

This Oracle example will first pull all of the Employees with a Salary greater than $40,000.  Then it will look through all the records associated with the current records department and determine the First Salary and employee combination and return that value for each of the related department records.    A similar technique can be used with MS SQL Server.

 SELECT department,

                firstname,

                lastname,

                salary,

                first_value(

     TO_CHAR(salary)||’ ‘||firstname||’-‘||lastname)

OVER (PARTITION BY department ORDER BY department) firstSalary

FROM employee_records

WHERE salary > 40000;

 

This example will use Partition By to find the latest position for a given employee.

  create table employee_positions

(firstname varchar2(100),

 lastname varchar2(100),

 position varchar2(100)

);

 

insert into employee_positions

values

(‘John’,’Allen’,’Developer 1′);

insert into employee_positions

values

(‘John’,’Allen’,’Developer 2′);

insert into employee_positions

values

(‘Jim’,’Jones’,’Developer 1′);

insert into employee_positions

values

(‘Ben’,’Smith’,’Developer 1′);

insert into employee_positions

values

(‘Ben’,’Smith’,’Developer 2′);

insert into employee_positions

values

(‘Ben’,’Smith’,’Developer 3′);

commit;

select * from employee_positions;

 

–Go through the first and last name combinations

–and determine the latest employee position.

SELECT DISTINCT latest_position FROM

(

SELECT firstname ||’ – ‘|| lastname || ‘-‘ || position latest_position, position, max(position)

       OVER (PARTITION BY firstname, lastname) mp

FROM employee_positions

WHERE lastname IN (‘Allen’,’Jones’,’Smith’)

)

WHERE position = mp;

 

–The Group By alternative below is longer, messier, and slower.

SELECT DISTINCT empname ||’ – ‘||

(SELECT DISTINCT position

FROM employee_positions

WHERE position = a.position and empname = a.empname) position FROM

(

SELECT firstname || ‘-‘ || lastname empname,

MAX(position) position

FROM employee_positions

WHERE LOWER(lastname) IN (‘allen’,’jones’,’smith’)

GROUP BY firstname || ‘-‘ || lastname

) a          

 –The results are:

–Ben – Smith – Developer 3

–Jim – Jones – Developer 1

–John – Allen – Developer 2

 

Some OracleDB aggregates functions include:

 First_Value

Last_Value

Min

 Max

Row_Number

 

Some Microsoft aggregate functions include:

Avg

Count

Sum

 http://msdn.microsoft.com/en-us/library/ms189461.aspx

 

Union:     

The Union command will take two selected datasets and return every unique record.  See Union All.

 The following will return each unique department and only one additional unique record without data values.

 (SELECT deptno

        FROM department

        UNION

        SELECT NULL deptno

              FROM department WHERE rownum < 19) ;

 

Union All: 

 Union All will take two selected datasets and return each record from the selection.  See Union.

 The following will return each unique department and eighteen individual records without data.

 (SELECT deptno

        FROM department

        UNION ALL

        SELECT NULL deptno

              FROM department WHERE

rownum < 19) ;

 Thanks for reading!

 

Picture Belongs To Author

Pictures Belong To Author


Share with your friends
Facebooktwittergoogle_plusredditpinterestlinkedinmail
To report this post you need to login first.

Leave a Reply

Your email address will not be published. Required fields are marked *