Excel Crib Sheet

Converting Date to String  in Excel

=TEXT(B2,”m/d/yyyy h:mm AM/PM”)

 

Generating SQL in Excel

Multi-Row Insert –> Generating an INSERT ALL Statement with Excel

 

INSERT ALL INTO xx (NEWPLAYERID) VALUES (264) INTO xx (NEWPLAYERID) VALUES (209) INTO xx (NEWPLAYERID) VALUES (840) INTO xx (NEWPLAYERID) VALUES (285) INTO xx (NEWPLAYERID) VALUES (237) INTO xx (NEWPLAYERID) VALUES (235) INTO xx (NEWPLAYERID) VALUES (222) INTO xx (NEWPLAYERID) VALUES (278) INTO xx (NEWPLAYERID) VALUES (286) SELECT 1 FROM DUAL;

 

Oracle in a Nutshell

A great overview and intro to Oracle

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

 

Oracle MODEL() Clause

http://www.dbasupport.com/oracle/ora10g/model.shtml

B10736-01 Oracle Database Data Warehousing Guide, Chapter 22

http://www.psoug.org/reference/model_clause.html

 

The Set Operators: Union, Intersect & Except

The Set Operators (Union, Intersect & Except) provide some very powerful functionality in SQL and are often the building blocks of complex SQL queries. When you understand what they do and when to use them, it greatly aids your understanding of SQL.

The following below is a summary of a great explanation I found at: http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/sqlrg/sqlrg42.htm

UNION, EXCEPT, INTERSECT
…All operate on multiple result sets to return a single result set:

UNION combines the rows from two or more result sets into a single result set.EXCEPT evaluates two result sets and returns all rows from the first set that are not also contained in the second set.

INTERSECT computes a result set that contains the common rows from two result sets.

UNION, INTERSECT, and EXCEPT operators can be combined in a single UNION expression.

In statements that include multiple operators, the default order of evaluation (precedence) for these operators is left to right; however, the INTERSECT operator is evaluated before UNION and EXCEPT. The order of evaluation can be modified with parentheses.

ALL

If ALL is specified, duplicate rows returned by union_expression are retained. If two query expressions return the same row, two copies of the row are returned in the final result. If ALL is not specified, duplicate rows are eliminated from the result set.In statements with multiple UNION, EXCEPT, and INTERSECT operators in which the ALL keyword is used, the order of evaluation can affect the results. The placement of the ALL keyword in relation to the query evaluation determines the duplicates that are retained and eliminated. If the last operation performed does not contain the ALL keyword, any duplicates retained from previous evaluations are eliminated. The INTERSECT operator has higher precedence than the UNION and EXCEPT operators. Therefore, in the absence of parentheses, INTERSECT operations are always evaluated first.

The following figure illustrates these concepts with Venn diagrams, in which the shaded portion indicates the result set.

Figure 4. Result Set

Venn Diagrams - Set Operators

Examples:

The following example replaces the UNION operator with EXCEPT; consequently, only those California cities that are in the Market table but not the Store table are returned:

select hq_city as ca_cities
from market
where hq_state like 'CA%'
except
select city
from store
where state like 'CA%'

CA_CITIES
Oakland
San Francisco

The following example replaces the UNION operator with INTERSECT; consequently, only those California cities that are in both the Market table and the Store table are returned:

select hq_city as ca_cities
from market
where hq_state like 'CA%'
intersect
select city
from store
where state like 'CA%'

CA_CITIES
Los Angeles
San Jose

select prod_name from product
   natural join sales_canadian
union all
(select prod_name from product
   natural join sales_mexican
union
select prod_name from product
   natural join sales)

(select prod_name from product
   natural join sales_canadian
union
select prod_name from product
   natural join sales_mexican)
intersect
select prod_name from product
   natural join sales
The following query uses multiple INTERSECT operations to return a list of common key values in five different tables:
select prodkey as common_keys from product
   intersect select classkey from class
   intersect select promokey from promotion
   intersect select perkey from period
   intersect select storekey from store

COMMON_KEYS
1
3
4
5
12

 

Detecting duplicates before grouping

SELECT

CUSTOMER_KEY, DATE_KEY,  ACTION_KEY, PRODUCT_KEY, count(*)

FROM CUSTOMER_DAILY_STAGE A

where customer_Key = 96951

GROUP BY

CUSTOMER_KEY, DATE_KEY, ACTION_KEY, PRODUCT_KEY

HAVING COUNT(*)>1

Sometimes I need to find out how many particular combinations of records in a table exist before grouping rows in a table and how many rows will be removed from the source table after the group function has been applied… This post will describe an easy way to show how to do this.

Example 1:

The example below displays a list of customers and how many instances of a action_key and product_key combination exist for a certain date. Essentially, it shows how many times a particular row combination is repeated  in the source table–>

SELECT

CUSTOMER_KEY, DATE_KEY,  ACTION_KEY, PRODUCT_KEY, count(*)

FROM CUSTOMER_DAILY_STAGE A

GROUP BY

CUSTOMER_KEY, DATE_KEY, ACTION_KEY, PRODUCT_KEY

HAVING COUNT(*)>1

Example 2:

This example displays how many duplicate rows will be removed from the original (ungrouped)  table after grouping occurs –>

select sum(GroupCount) from (

– checks how many times a certain action_key, product_key combination is repeated

– on a day for a customer –> i.e. how many times did that customer repeat that exact type of transaction

– in a day?

SELECT CUSTOMER_KEY, DATE_KEY,  ACTION_KEY, PRODUCT_KEY ,  count(*)-1 as GroupCount – I only want to see how many rows will be removed…

FROM CUSTOMER_DAILY_STAGE A

–where customer_Key = 96951

GROUP BY

CUSTOMER_KEY, DATE_KEY, ACTION_KEY, PRODUCT_KEY

HAVING COUNT(*)>1

order by customer_Key, date_key asc

)

 

Formatting/Cleaning data in Oracle

from

http://www.howtodothings.com/computers/how-to-use-formatting-in-oracle-sql

–>

Once you retrieve your data, you should think about how you would like to view it. You can do a lot of formatting in Oracle SQL, which translates into saved time for you. After all who wants dirty data?

Formatting is a presentation issue. It allows you to present the data you have gathered so that it looks good. And as someone once said, “Perception is key.” I use the following 8 formatting tricks. There are many more, but the ones below are essential to know.

1. Round and/or truncate the date field to the nearest month:

SELECT SYSDATE, ROUND(SYSDATE, ‘Wed’),

TRUNC(SYSDATE, ‘Wed’)

FROM u.agg_ia_clickstream

SYSDATE ROUND(SYSDA TRUNC(SYDATE

————– ————————————————

31-Dec-2003 01-Jan-2004 01-Dec-2003

Rounding allows the date to be rounded up to the nearest month. Truncation allows the date of any value you specify to its minimum. The minimum value is 1, so 31-Dec was truncated to 1-Dec.

1. The REPLACE function can be used to do a search and replace:

REPLACE(string, search, replace)

1. Extract data from a string using:

SUBS(STRING, START[, length])

1. Find the length of a string:

LENGTH(string)

1. Add strings together:

String1 ll String2

1. Trim unwanted spaces, numbers, and letters:

Trim(string)

Trim(character FROM string)

To trim only characters to the right:

RTRIM(string)

To trim characters only to the left:

LTRIM(string)

To remove punctuation from both sides of a string:

RTRIM(LTRIM(string,’.;!’),’.,!’)

1. Change capitalization of letters:

To Change letters to upper case letters:

UPPER(string)

To change letters to lower case letters:

LOWER(string)

To use upper case for the first letter and lower case for other letters:

INITCAP(string)

1. Search and Replace:

SELECT date, unique cookies

DECODE (dma, 360, ‘Dayton’, 560, ‘Toledo’, 870, ‘Baton Rouge’, Null, ‘Unknown’)

FROM u.agg_ia_clickstream

In this example, 360 is replaced by Dayton, 560 is replaced by Toledo, etc.

The 8 formatting tips above will help you make your data — and hopefully you, by association — look good. If you know other tips that are helpful, please let me know. Thank you for reading.

 

Cross tab queries – very powerful!

Original link: http://www.dbforums.com/microsoft-sql-server/1212000-query-parse-out-values-one-column-into-different-columns.html

I had trouble recently trying to work out how the hell to find the maximum dates for different conditions and split it out across columns (e.g.  a cross tab) so I looked up on Google how to do it. The only way I could think of was to use derived tables and pull from them, but the query was very very slow. I didn’t know it was possible to do a cross-tab like this but it’s actually very powerful with many applications and simply uses a CASE statement in a different way  I didn’t think was possible. And it’s a cross-platform way of doing it too which is a bonus.

This is the code that was suggested:

“Look up CROSSTAB queries in Books Online.

Code:

select	userid,
	max(case recordtag when 1 then record end) as firstname,
	max(case recordtag when 2 then record end) as middleinitial,
	max(case recordtag when 3 then record end) as suffix
from	[YourTable]
group by userid

This is the code I came up with:

SELECT

CUSTOMER_KEY,

MAX(CASE PRODUCT_KEY  WHEN 2 THEN CD.DATE_KEY ELSE -1 END) AS LAST_POKER_WAGER_DATE,

MAX(CASE PRODUCT_KEY  WHEN 3 THEN CD.DATE_KEY ELSE -1 END) AS LAST_CASINO_WAGER_DATE,

MAX(CASE PRODUCT_KEY  WHEN 4 THEN CD.DATE_KEY ELSE -1 END) AS LAST_SPORTSBOOK_WAGER_DATE

FROM

CRM_DM.CUSTOMER_DAILY CD WHERE ACTION_KEY = 3

GROUP BY  CUSTOMER_KEY

SELECT

CUSTOMER_KEY,

MAX(CASE PRODUCT_KEY  WHEN 2 THEN CD.DATE_KEY ELSE -1 END) AS LAST_POKER_WAGER_DATE,

MAX(CASE PRODUCT_KEY  WHEN 3 THEN CD.DATE_KEY ELSE -1 END) AS LAST_CASINO_WAGER_DATE,

MAX(CASE PRODUCT_KEY  WHEN 4 THEN CD.DATE_KEY ELSE -1 END) AS LAST_SPORTSBOOK_WAGER_DATE

FROM

CRM_DM.CUSTOMER_DAILY CD WHERE ACTION_KEY = 3

GROUP BY  CUSTOMER_KEY

Which produced an output like this (which was exactly what I was after!):

Customer_Key | Last_Poker_Wager_Date | Last_Casino_Wager_Date | Last_Sportsbook_Wager_Date

So, many thanks from me to the guy who posted on the link above!!!

 

Visual BI Meets Pop Culture

http://intelligent-enterprise.informationweek.com/blog/archives/2009/10/visual_bi_meets.html

 

Creating a Data Dictionary

NASA Data Dictionary Guidelines

http://irsa.ipac.caltech.edu/applications/DDGEN/Doc/dd_tbl.html

Syncronex – Data Dictionary Tutorial

http://w2.syronex.com/jmr/edu/db/oracle-data-dictionary/

Data Dictionary Tutorial

http://element4solution.com/javadoc/resources/e4s.db.dict.DataDictionary.pdf

http://element4solution.com/javadoc/resources/e4s.db.dict.DataDictionary.pdf

Using a Data Dictionary

http://database-programmer.blogspot.com/2008/06/using-data-dictionary.html

Guidelines for Developing a Data Dictionary (AHIMA Practice Brief)

http://library.ahima.org/xpedio/groups/public/documents/ahima/bok1_030582.hcsp?dDocName=bok1_030582