When I last sat down to update my resume, I Googled a bit and found a lot of good write ups on How to write a good resume, tips on a good resume/profile etc. But I could not find why? Why I should compile a resume and what lies in there which would impress my future Boss. In other wards, what would my future Boss look in for in my resume and get impressed? Looks like a catch 22 situation, well to some extent, it is. But again think of a situation when you want to hire someone, would not you like to pick the best. Same is the case if you want yourself to be chosen.
As you know, a good resume should provide valuable insights about the job applicant which can help the hirer to determine whether she/he should be considered for the given open position. Likewise, when the employer looks at your resume, she/he should be given those ‘valuable insights’ right away, neatly and clearly. When I say clearly, it does not mean big fonts with double spacing, what I mean is “understandably”.
When your future Boss scans through your resume, generally she/he notes down all the striking points which causes her/him to think, “You are really a good fit for the job” but also, sometimes, if the person does not sees that spark in your profile, do not get the impression that your chance is gone, your information might be kept for further analysis. So what is that the Manager looks for, here is a small list of observations.
1. Objective: One of the first points that any good employer would be interested in, is what the Objective of the candidate and how specific it is to the rest of the resume. Whether it reflects that you have certain a goal in mind which this job would provide. If your objectives are not clearly mentioned, it would give an indication that you are not sure of what and why you want this job, and it will also send signal that you just applied to take a shot at it.
2. Over all Organization: When you have a nicely organized write up and it is easy to read, it helps a lot in making out that you are eager to good communication and you value what you write. Avoid spelling or grammatical errors, as you know a resume are the first communication to the employer, if it does not make any sense from reading, you have almost lost your chance.
3. Listing some of your Accomplishments.
4. Showing a consistent growth throughout your career.
5. Demonstrating appropriate professionalism.
6. Worked successfully in a Team.
7. Shown Interest in taking responsibility
If you are new to Teradata SQL Assistant and would like to see your query results appear in multiple answerset windows rather than getting overwritten. Here is a way to do it.
1) Click on Tools.
2) Click on Options.
3) Click on the ‘General’ tab.
4) Off the 4 check box options, select the following:
a. Allow Multiple Queries
b. Close Answerset windows before submitting a new query.
5) You would also see a set of radio button choices with the following label:
a. User Separate Answerset Windows for
i. Each Resultset.
ii. Each Query
iii. Never
6) Choose “Each Resultset”.
7) Click OK.
Submit the following two queries with semicolon present at the end of 1st query.
a. select date;
b. select time
The result set would be displayed in two separate windows.
In this “General” tab, you can also specify the string you want to display when a NULL data appears (by default it is ‘?’). Look for the label “Display this string for Null data fields” and put your favorite word in the text box present next to it.
The Outer Join is an extension of the Inner Join which includes both rows that qualify for a simple Inner Join as well as a specified set of rows that do NOT match the join conditions mentioned in the query. This is the reason sometimes it gets a bit complicated to code the Outer Joins correctly. There are three types of outer joins:
- Left Table (Left Outer Join).
- Right Table (Right Outer Join).
- Both Tables (Full Outer Join).
As mentioned before the sole purpose of an OUTER JOIN is to find and return rows that do NOT match at least one row from another table. It is something like an “exception” reporting, but at the same time, it does the INNER JOIN processing too. Therefore, the matching rows are returned along with all rows without a matching value from another table, which might be due to the existence of a NULL or invalid data values.
1. Left Table (Left Outer Join).
In any Outer Join it is very important to know which table is Outer table; this is the table that decides the how the rows (matching as well as non-matching) would be returned. In a Left Outer Join, the table that is mentioned at the left position to the key word “Left Outer Join” is the outer table. This means the non-matching rows would be returned from this table. For example,
Table A Table B
F1
|
F2
|
F3
|
AA
|
1
|
A1
|
BB
|
2
|
B2
|
CC
|
3
|
C3
|
DD
|
4
|
D4
|
F1
|
F2
|
F3
|
CC
|
3
|
C3
|
DD
|
4
|
D4
|
EE
|
5
|
E5
|
FF
|
6
|
F6
|
SELECT A.f1 OuterCol, B.f1 InnerCol
FROM A LEFT OUTER JOIN B
ON A.f1 = B.f1
The above query would return:
OuterCol InnerCol
DD DD
AA NULL
CC CC
BB NULL
See the NULL values appearing in the inner table, a further addition of a WHERE condition,
SELECT A.f1 OuterCol, B.f1 InnerCol
FROM A LEFT OUTER JOIN B
ON A.f1 = B.f1
WHERE A.F1 = ‘AA’
Would return the following result.
OuterCol InnerCol
AA NULL
Right Table (Right Outer Join). In a Right Outer Join, the table that is mentioned at the right position to the key word “Right Outer Join” is the outer table. This means the non-matching rows would be returned from this table. For example,
SELECT A.f1 OuterCol, B.f1 InnerCol
FROM A RIGHT OUTER JOIN B
ON A.f1 = B.f1
OuterCol InnerCol
NULL FF
DD DD
NULL EE
CC CC
But notice that neither of the following queries would return any rows as the value ‘AA’ is not present in the outer table (B).
SELECT A.f1 OuterCol, B.f1 InnerCol
FROM A RIGHT OUTER JOIN B
ON A.f1 = B.f1
WHERE A.F1 = ‘AA’
SELECT A.f1 OuterCol, B.f1 InnerCol
FROM A right outer JOIN B
ON A.f1 = B.f1
WHERE B.F1 = ‘AA’
Both Tables (Full Outer Join).
In a Full Outer Join, both the tables that are mentioned at both sides of the key word Full Outer Join are the outer tables. This means the non-matching rows would be returned from both the tables. For example,
SELECT A.f1 OuterColA, B.f1 OuterColB
FROM a full outer JOIN b
ON A.f1 = B.f1
OuterColA OuterColB
NULL FF
DD DD
AA NULL
NULL EE
CC CC
BB NULL
Unlike the Right Outer Join, if you run the following query you would see the resulted row which would be similar to that of inner join query. This happens because the table A is also treated as an Outer table.
SELECT A.f1 OuterColA, B.f1 OuterColB
FROM a full outer JOIN b
ON A.f1 = B.f1
WHERE a.F1 = ‘AA’
OuterCol OuterColB
AA NULL
As discussed previously, in Teradata we have the following types of Inner Joins.
- Ordinary Inner Join
- Cross Join
- Self-Join
Let’s discuss how they are different from each other and how to use them.
Ordinary Inner Join: – As you know, JOIN allows you to select common columns and rows from two or more tables/views, similarly in an inner join, common data from two or more tables/views are returned which meets this specific common conditions. Let’s understand this concept with the help of SET rules in Algebra. We have the following two sets:
A = {1, 2, 3, 4, 5}
B = {4, 5, 6, 7, 8}
When you do a JOIN between A and B considering them as tables, only the common element present in them would be returned. In this case it is {4, 5}. This can be derived by the set rule “Intersection”.
So, “A JOIN B” is similar to “A intersection B”. Remember that by default all the Joins are Inner Joins. Following are the ways you can code Joins.
SELECT <table_name(s).column name(s)>
FROM < table_name1 > INNER JOIN < table_name2 >
ON <table_name1.column name> = <table_name2.column name>
SELECT <table_name(s).column name(s)>
FROM < table_name1 > JOIN < table_name2 >
ON <table_name1.column name> = <table_name2.column name>
SELECT <column name(s)>
FROM < table_name1 >, < table_name2 >
WHERE < table_name1.column name1> = < table_name2.column name2>
Cross Join: – Cross Joins are also known as unconstrained joins (where a “WHERE” clause between the joined tables is not specified/needed). The result of an unconstrained join is also referred to as a Cartesian product. Where the collective result set would be a multiplication product of each element of one table with each element of another table. So, for example, if you have two tables with 10 rows each, the CROSS join would return 100 rows as its result set. Imagine tables with 1000’s of rows. This is reason one has to be very careful before performing a Cross Join. One good point to note, Cross Join is not the same as full Outer Join.
Syntax:
SELECT <column name(s)>
FROM < table_name1 > CROSS JOIN < table_name2 >
Also the following is valid, note that the key word CROSS JOIN is not used.
SELECT <column name(s)>
FROM < table_name1 >, < table_name2 >
Self-Join:- As the name suggests, a self-join combines the information from two or more rows of the “same” table into a single row, effectively joining the table with itself. The usage arises when same column value would be used for comparison. For example, compare sales figure of a sales person with that of another sales person with different designation.
Following query can be used.
SELECT a.name, a.dept_no, a.sales_amt,
b.name, b.dept_no, b.sales_amt
FROM sales a, sales b
WHERE a.dept_no = b.dept_no
AND a.desig IN (’Sales Executive’, ’Sales Supervisor’)
AND a.sales_amt <> b.sales_amt;
This particular query would treat the one “sales” tables to two fictitious “sales” table and give the desired result which is done be aliasing the “sales” table twice once as a and again as b. This is also called as correlation names / range variables in ANSI. Remember to put the alias names before each column to be selected.
In a JOIN processing, columns from two or more tables are simulated into a virtual table. In Teradata, we can have as many as 64 table JOINs per query, but in case if you need to JOIN these many tables, you better take good advice from your DBA, to avoid, facing last minute music. Also a JOIN does not have to be with two or more tables, we can also JOIN one single table with itself, which is called “Self Join” where the information from two or more rows of the same table put into a single result row.
There are varieties of JOINS, which are different by the “conditions” specified in the SQL query. We will discuss on the following types:
• Natural or Equijoin
• Inner
• Outer
Natural or Equijoin
Because Teradata SQL does not support the ANSI SQL-2003 NATURAL JOIN syntax, it treats natural and equijoins as one and the same.
The natural join is an equality join (with = sign) made over a common column set with matching column names such as a primary key-foreign key relationship that is expressed in a WHERE clause. For example,
WHERE A.empnum = B.empnum
While both Natural as well as Equijoin are made over an equality condition (with = sign), the column names on which tables are joined need not match in an equijoin (but data should), while they must match in a natural join. For example, in the WHERE condition above, if there is a column present in table B with matching data and the name is emp_no then we can rewrite the query as follows:
WHERE A.empnum = B.emp_no
Inner Joins: The inner join, most of the time referred as just JOIN, combines only the rows that have specific similarity between the joined tables (employee number in the above example). One good thing about using INNER JOINS is that one can avoid unwanted cross joins. This happens by mistake. Following types of INNER JOINS are used in SQL.
- Ordinary Inner Join
- Cross Join
- Self-Join
Outer Joins: The outer join is an extension to inner join that returns not only common rows between the tables, but also it returns rows that do NOT have anything in common. This non-matching row might be due to a NULL value or invalid data. Depending on how you code, outer join can return the inner join rows plus any of the no matching rows from the:
- Left Table (Left Outer Join).
- Right Table (Right Outer Join).
- Both Tables (Full Outer Join).
RANK ():- As the function name implies, RANK returns ranking (ordered) of rows based on the number or expression given in the ORDER BY clause. Note that we do not need to give anything inside the RANK function. Just give it to the ORDER BY clause and things would be taken care. Let’s work out a scenario.
Scenario is to find out the sales figure for each store based on their actual sales.
SELECT store_no, sale_month, actual_sale,
RANK () OVER (PARTITION BY store_no ORDER BY actual_sale) as SaleRank
FROM sales;
Note that there is nothing being fed to the function RANK. Result set below shows the sales figure ranked, for each store.
store_no sale_month actual_sale SaleRank
10 05/01/2007 13,500 1
10 01/01/2007 13,500 1
20 02/01/2007 11,000 1
20 04/01/2007 12,500 2
30 02/01/2007 10,000 1
30 03/01/2007 10,500 2
30 03/01/2007 12,500 3
40 02/01/2007 11,500 1
40 04/01/2007 12,500 2
50 01/01/2007 10,500 1
50 05/01/2007 12,500 2
60 06/01/2007 11,500 1
70 07/01/2007 11,500 1
80 07/01/2007 15,500 1
90 06/01/2007 15,500 1
ROW_NUMBER ():- It returns the sequential number of the row within a group, starting with 1 based on the ORDER BY clause. With the above scenario if we replace the RANK with ROW_NUMBER and run the following query:
SELECT store_no, sale_month, actual_sale,
ROW_NUMBER() OVER (PARTITION BY store_no ORDER BY actual_sale) as SaleRank
FROM sales;
The result set we get is similar, only difference is for first two rows we have 1,1 with RANK but 1,2 with ROW_NUMBER. This is because ROW_NUMBER takes into consideration the sequential occurrence of the row in a group which is store_no here.
store_no sale_month actual_sale SaleRank
10 05/01/2007 13,500 1
10 01/01/2007 13,500 2
20 02/01/2007 11,000 1
20 04/01/2007 12,500 2
30 02/01/2007 10,000 1
30 03/01/2007 10,500 2
30 03/01/2007 12,500 3
40 02/01/2007 11,500 1
40 04/01/2007 12,500 2
50 01/01/2007 10,500 1
50 05/01/2007 12,500 2
60 06/01/2007 11,500 1
70 07/01/2007 11,500 1
80 07/01/2007 15,500 1
90 06/01/2007 15,500 1
SUM ():- When used with partition clause, SUM () function returns the cumulative or moving sum of an expression based on how the aggregation group is specified. This function can also be used in place of CSUM and MSUM which are Teradata-specific functions and are discouraged to a great extent. As per recent recommendations from Teradata, usage of ANSI-compliant window function for any new applications is advised. Lets see why Teradata suggests to go for ANSI-compliant SUM () function.
• If you are using SUM function and want to calculate the cumulative SUM, then just specify ORDER BY clause with ROWS UNBOUNDED PRECEDING which would give the same result as that of CSUM function.
• To compute moving average using SUM window function use ORDER BY clause and specify ROWS number PRECEDING (number of rows preceding the current row).
Let’s work out some examples using the data present in the previous section.
Scenario 1:- Calculate the cumulative actual sales per store ordered by sale month:
SELECT store_no, sale_month, actual_sale,
SUM (actual_sale) OVER (PARTITION BY store_no ORDER BY sale_month ROWS UNBOUNDED PRECEDING) as StoreTotal
FROM sales;ORDER BY store_no, sale_month;
Result Set:-
store_no sale_month actual_sale StoreTotal
10 01/01/2007 13,500 13,500
10 05/01/2007 13,500 27,000
20 02/01/2007 11,000 11,000
20 04/01/2007 12,500 23,500
30 02/01/2007 10,000 10,000
30 03/01/2007 12,500 22,500
30 03/01/2007 10,500 33,000
40 02/01/2007 11,500 11,500
40 04/01/2007 12,500 24,000
50 01/01/2007 10,500 10,500
50 05/01/2007 12,500 23,000
60 06/01/2007 11,500 11,500
70 07/01/2007 11,500 11,500
80 07/01/2007 15,500 15,500
90 06/01/2007 15,500 15,500
Scenario 2:- Calculate the total actual sales of fruits by category per each store:
SELECT store_no, prod_code, actual_sale,
SUM (actual_sale) OVER (PARTITION BY prod_code ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as FruitTotal
FROM sales;
Result would be as follows:
store_no prod_code actual_sale FruitTotal
60 Apple 11,500 25,000
10 Apple 13,500 25,000
40 Apricot 12,500 26,000
10 Apricot 13,500 26,000
20 Banana 11,000 22,500
70 Banana 11,500 22,500
50 Grapes 12,500 28,000
80 Grapes 15,500 28,000
50 Guava 10,500 22,000
40 Guava 11,500 22,000
20 Mango 12,500 25,000
30 Mango 12,500 25,000
90 Orange 15,500 26,000
30 Orange 10,500 26,000
30 Strawberry 10,000 10,000
Scenario 3:- Calculate the moving actual sales per month per store.
SELECT store_no, sale_month, actual_sale,
SUM (actual_sale) OVER (PARTITION BY store_no, sale_month ORDER BY actual_sale ROWS 1 PRECEDING) as SaleTrend
FROM sales;
And the Result would be:
store_no sale_month actual_sale SaleTrend
10 01/01/2007 13,500 13,500
10 05/01/2007 13,500 13,500
20 02/01/2007 11,000 11,000
20 04/01/2007 12,500 12,500
30 02/01/2007 10,000 10,000
30 03/01/2007 10,500 10,500
30 03/01/2007 12,500 23,000
40 02/01/2007 11,500 11,500
40 04/01/2007 12,500 12,500
50 01/01/2007 10,500 10,500
50 05/01/2007 12,500 12,500
60 06/01/2007 11,500 11,500
70 07/01/2007 11,500 11,500
80 07/01/2007 15,500 15,500
90 06/01/2007 15,500 15,500
COUNT ():- As you know when you use this function as aggregate function, it would just return the total number of rows present in the SELECT statement. But the same function when used with OLAP, returns the cumulative or moving count for an expression, let’s see how this is done. Let’s also use the same data set present in the initial article. Scenario is to find out, number of stores selling a particular product.
This is called grouping count and the same can be done in two ways one with taking store_no as count, partitioning by prod_code and the other ways to reverse the logic of taking prod_code as count and partition by store_no.
The following queries would give the desired result.
SELECT store_no, sale_month, actual_sale, prod_code,
COUNT (prod_code) OVER (PARTITION BY store_noROWS BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING)
FROM sales;
SELECT store_no, sale_month, actual_sale, prod_code,
COUNT (store_no) OVER (PARTITION BY prod_codeROWS BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING)
FROM sales;
store_no sale_month actual_sale prod_code Group Count(store_no)
10 01/01/2007 13,500 Apple 2
60 06/01/2007 11,500 Apple 2
10 05/01/2007 13,500 Apricot 2
40 04/01/2007 12,500 Apricot 2
70 07/01/2007 11,500 Banana 2
20 02/01/2007 11,000 Banana 2
50 05/01/2007 12,500 Grapes 2
80 07/01/2007 15,500 Grapes 2
50 01/01/2007 10,500 Guava 2
40 02/01/2007 11,500 Guava 2
30 03/01/2007 12,500 Mango 2
20 04/01/2007 12,500 Mango 2
30 03/01/2007 10,500 Orange 2
90 06/01/2007 15,500 Orange 2
30 02/01/2007 10,000 Strawberry 1
New Terms:-
ROWS BETWEEN: – This clause is to specify the start and end of the aggregation group.The default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING (take out this portion from the above query and run).
UNBOUNDED PROCEEDING: – When this is specified, the entire partition that precedes the current row is taken into the aggregation group.
UNBOUNDED FOLLOWING: – Used to define the entire partition that follows the current row.
Let’s work out some examples of OLAP functions that are being discussed in the previous articles. Before that make sure you have a table and there is data present init. Follow the below instruction to create a temporary table and insert data into it. While inserting data you might encounter one of the following errors:
3520: A constant value in a query is not valid for column sale_month.
3535: A character string failed conversion to a numeric value.
2665: Invalid date.
All of these errors are due to the date value that you are inserting. If you encounter any of these errors, try permutation and combination of the date value and see if you can get rid of the error. If you still can not then find out from your sys admin what date format in allowed while you are inserting dates. Here in this example the format is ‘yyyy-mm-dd’. All of the below statements are tested in SQL assistant. Create a Volatile Table “Sales” as follows:
CREATE SET VOLATILE TABLE <username>.sales, NO FALLBACK,CHECKSUM = DEFAULT,LOG ( “store_no” INTEGER, “sale_month” DATE, “prod_code” CHAR (20) CHARACTER SET LATIN NOT CASESPECIFIC, “projected_sale” INTEGER, “actual_sale” INTEGER )PRIMARY INDEX ( “store_no” )ON COMMIT PRESERVE ROWS;
Insert data as follows:
INSERT INTO sales VALUES(10,’2007-01-01′,’Apple’,15000,13500);
INSERT INTO sales VALUES(20,’2007-02-01′,’Banana’,10000,11000);
INSERT INTO sales VALUES(30,’2007-03-01′,’Orange’,11000,10500);
INSERT INTO sales VALUES(40,’2007-04-01′,’Apricot’,12500,12500);
INSERT INTO sales VALUES(50,’2007-05-01′,’Grapes’,15000,12500);
INSERT INTO sales VALUES(60,’2007-06-01′,’Apple’,12000,11500);
INSERT INTO sales VALUES(70,’2007-07-01′,’Banana’,15500,11500);
INSERT INTO sales VALUES(80,’2007-07-01′,’Grapes’,15000,15500);
INSERT INTO sales VALUES(90,’2007-06-01′,’Orange’,16000,15500);
INSERT INTO sales VALUES(10,’2007-05-01′,’Apricot’,14500,13500);
INSERT INTO sales VALUES(20,’2007-04-01′,’Mango’,15500,12500);
INSERT INTO sales VALUES(30,’2007-03-01′,’Mango’,15000,12500);
INSERT INTO sales VALUES(40,’2007-02-01′,’Guava’,14000,11500);
INSERT INTO sales VALUES(50,’2007-01-01′,’Guava’,12000,10500);
INSERT INTO sales VALUES(30,’2007-02-01′,’Strawberry’,12000,10000);
Now take the example of AVG () function. As you know AVG () computes the cumulative or moving average of a column, we will calculate something similar in the following scenario.
Scenario is to find out the actual sales in each store, averaged over the current month and the preceding month. Here preceding month would be understood as the previous month to the current month, now what is current month, it is the first row returned after the result set is ordered after partitioning. Following is the query.
SELECT store_no, sale_month, actual_sale, projected_sale,AVG(actual_sale) OVER (PARTITION BY store_noORDER BY projected_saleROWS 1 PRECEDING)FROM sales;
And it would return the following result set.
store_no sale_month actual_sale projected_sale Moving Avg(actual_sale)10 5/1/2007 13,500 14,500 13,500.00
10 1/1/2007 13,500 15,000 13,500.00
20 2/1/2007 11,000 10,000 11,000.00
20 4/1/2007 12,500 15,500 11,750.00
30 3/1/2007 10,500 11,000 10,500.00
30 2/1/2007 10,000 12,000 10,250.00
30 3/1/2007 12,500 15,000 11,250.00
40 4/1/2007 12,500 12,500 12,500.00
40 2/1/2007 11,500 14,000 12,000.00
50 1/1/2007 10,500 12,000 10,500.00
50 5/1/2007 12,500 15,000 11,500.00
60 6/1/2007 11,500 12,000 11,500.00
70 7/1/2007 11,500 15,500 11,500.00
80 7/1/2007 15,500 15,000 15,500.00
90 6/1/2007 15,500 16,000 15,500.00
Note that the moving average number is changing every 2nd row in case there is a difference in between the 1st and 2nd row figure within the same store number. Particularly for the store number 30, you can see that there is a slight increase in the moving average in spite of good increase in actual sales. Projected Sales is also added just to have an idea how the actual sale is performing over the project sale.
1. One of the useful features in Ordered Analytical Computation is to produce/define data sets or data subsets dynamically. This is done by the usage of the OVER () phrase (detail later). This OVER () feature can be used with all the aggregate functions too.
2. Introducing the usage of QUALIFY Clause: If there is a need to eliminate or extract rows based on the certain condition after the partition, use this clause.
3. DISTINCT Clause in NOT allowed Analytical Computations.
4. Ordered (OLAP) analytical functions are permitted in the following database objects:• Views• Macros• Derived tables• INSERT INTO-SELECT FROM.
5. Ordered (OLAP) analytical functions are not permitted in sub queries. But note that if you are just using aggregate functions, then it is allowed.
6. Ordered (OLAP) analytical functions are even not permitted in WHERE clauses. Same is the case with aggregate functions.
7. Ordered (OLAP) analytical functions do not support CLOB or BLOB data types.
8. If you want to sort the result set, by a column present in the partition clause, then mind that you have to use the ORDER BY again after the FROM clause. Only using within the partition specification would not sort the result set.
ReadVitamin 