Chapter 10 – The Fundamental SQL Commands That Work on Aster
“The most important thing a father can do for his children is to love their mother.”
-Anonymous
BETWEEN is Inclusive
SELECT *
FROM Student_Table
WHERE Grade_Pt BETWEEN 2.0 AND 4.0 ;
This is a BETWEEN. What this allows you to do is see if a column falls in a range. It is inclusive, meaning that in our example, we will be getting the rows that also have a 2.0 and 4.0 in their column!
BETWEEN Works for Character Data
The BETWEEN isn’t just used with numbers. You can look to see if words falls between certain letters, but remember that ASTER Data is case sensitive. The ending 'z' must be lowercase or this SQL returns nothing.
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
The wildcard percentage sign (%) is a wildcard for any number of characters. We are looking for anyone whose name starts with SM! In this example, the only row that would come back is ‘Smith’.
LIKE command Underscore is Wildcard for one Character
The second wild card is a ‘_’ (underscore). An underscore represents a one character wildcard. Our search finds anyone with an ‘a’ in the second letter of their last name.
GROUP BY Vs. DISTINCT – Good Advice
It is better to use this example
Select First_Name, Last_Name From Employee_Table
Group By 1,2 ;
This example could cause skewing
Select DISTINCT First_Name, Last_Name From
Employee_Table ;
Ad Hoc users should use the GROUP BY example above instead of the DISTINCT example. Both queries will provide the same results, but the GROUP BY could be significantly faster.
The Five Aggregates of Aster Data
How many rows will the above query produce in the result set? The answer is one.
GROUP BY when Aggregates and Normal Columns Mix
A GROUP BY statement is needed when mixing aggregates and non-aggregates.
GROUP BY Delivers one row per Group
The Group BY Dept_No allows for the Aggregates to be calculated per Dept_No.
GROUP BY Dept_No or GROUP BY 1 the same thing
Both queries above produce the same result. The GROUP BY allows you to either name the column or use the number in the SELECT list, just like the ORDER BY.
Limiting Rows and Improving Performance with WHERE
Will Dept_No 300 be calculated? Of course you know it will . . . NOT!
WHERE Clause in Aggregation limits unneeded Calculations
The system eliminates reading any other Dept_No other than 200 and 400. This means that only the Dept_No of 200 and 400 will come off the disk to be calculated.
Keyword HAVING tests Aggregates after they are Totaled
The HAVING Clause only works on Aggregate Totals. The WHERE filters rows to be excluded from calculation, but the HAVING filters the Aggregate totals after the calculations thus eliminating certain Aggregate totals.
Keyword HAVING is like an Extra WHERE Clause for Totals
The HAVING Clause only works on Aggregate Totals after they are totaled. It is a final check after aggregation is complete. Now only the totals with Count (*) > 2 can return.
Getting the Average Values per Column
SELECT 'Product_ID' AS "Column Name"
,COUNT(*) / COUNT(DISTINCT(Product_ID)) AS "Average Rows"
FROM Sales_Table ;
Column Name Average Rows
___________ ____________
Product_ID 7
SELECT 'Sale_Date' AS "Column Name"
,COUNT(*) / COUNT(DISTINCT(Sale_Date)) AS "Average Rows"
FROM Sales_Table ;
Column Name Average Rows
___________ ____________
Sale_Date 3
The first query retrieved the average rows per value for the column Product_ID. The example below did the same for the column Sale_Date.
Getting the Average Values per Column
SELECT 'Product_ID' AS "Column Name"
,COUNT(*) / COUNT(DISTINCT(Product_ID)) AS "Average Rows"
FROM Sales_Table ;
Column Name Average Rows
___________ ____________
Product_ID 7
SELECT 'Sale_Date' AS "Column Name"
,COUNT(*) / COUNT(DISTINCT(Sale_Date)) AS "Average Rows"
FROM Sales_Table ;
Column Name Average Rows
___________ ____________
Sale_Date 3
The first query retrieved the average rows per value for the column Product_ID. The example below did the same for the column Sale_Date.
Average Values per Column for All Columns in a Table
SELECT 'Product_ID' AS "Column Name"
,COUNT(*) / COUNT(DISTINCT(Product_ID)) AS "Average Rows"
,'Sale_Date' AS "Column Name2"
,COUNT(*) / COUNT(DISTINCT(Sale_Date)) AS "Average Rows2"
FROM Sales_Table ;
Column Name Average Rows Column Name2 Average Rows2
____________ ____________ _____________ _____________
Product_ID 7 Sale_Date 3
The query above retrieved the average rows per value for both columns in the table.
A two-table join using Non-ANSI Syntax
SELECT Customer_Table.Customer_Number, Customer_Name,
Order_Number, Order_Total
FROM Customer_Table,
Order_Table
WHERE Customer_Table.Customer_Number = Order_Table.Customer_Number ;
A Join combines columns on the report from more than one table. The example above joins the Customer_Table and the Order_Table together. The most complicated part of any join is the JOIN CONDITION. The JOIN CONDITION means what Column from each table is a match. In this case, Customer_Number is a match that establishes the relationship, so this join will happen on matching Customer_Number columns.
A two-table join using Non-ANSI Syntax with Table Alias
SELECT |
Cust.Customer_Number, Customer_Name, |
FROM |
Customer_Table as Cust, |
WHERE |
Cust.Customer_Number = Ord.Customer_Number ; |
A Join combines columns on the report from more than one table. The example above joins the Customer_Table and the Order_Table together. The most complicated part of any join is the JOIN CONDITION. The JOIN CONDITION means what Column from each table is a match. In this case, Customer_Number is a match that establishes the relationship.
Aliases and Fully Qualifying Columns
Customer_Number is a column in both the Customer and Order Tables. CUST.Customer_Number fully qualifies the column to specifically state we want the Customer_Number from the Customer_Table. That is why we ALIASED the table names, so we could fully qualify any columns in both tables or else we receive an error!
A two-table join using ANSI Syntax
This is the same join as the previous slide except it is using ANSI syntax. Both will return the same rows with the same performance. Rows are joined when the Customer_Number matches on both tables, but non-matches won’t return.
Both Queries have the same Results and Performance
Both of these syntax techniques bring back the same result set and have the same performance. The INNER JOIN is considered ANSI. Which one does Outer Joins?
Quiz – Can You Finish the Join Syntax?
SELECT First_Name, Last_Name,
Department_Name
FROM Employee_Table as E
INNER JOIN
Department_Table as D
ON
Finish this join by placing the missing SQL in the proper place!
Answer to Quiz – Can You Finish the Join Syntax?
This query is ready to run.
Quiz – Can You Find the Error?
SELECT First_Name, Last_Name, Dept_No
Department_Name
FROM Employee_Table as E
INNER JOIN
Department_Table as D
ON E.Dept_No = D.Dept_No ;
This query has an error! Can you find it?
Answer to Quiz – Can You Find the Error?
If a column in the SELECT list is in both tables, you must fully qualify it.
Quiz – Which rows from both tables Won’t Return?
SELECT First_Name, Last_Name,
Department_Name
FROM Employee_Table as E
INNER JOIN
Department_Table as D
ON E.Dept_No = D.Dept_No ;
An Inner Join returns matching rows, but did you know an Outer Join returns both matching rows and non-matching rows? You will understand soon!
Answer to Quiz – Which rows from both tables Won’t Return?
Squiggy Jones has a NULL Dept_No
Richard Smythe has an invalid Dept_No 10
No Employees work in Department 500
The bottom line is that the three rows excluded did not have a matching Dept_No.
LEFT OUTER JOIN
This is a LEFT OUTER JOIN. That means that all rows from the LEFT Table will appear in the report regardless if it finds a match on the right table.
LEFT OUTER JOIN Brings Back All Rows in the Left Table
A LEFT Outer Join Returns all rows from the LEFT Table including all Matches. If a LEFT row can’t find a match, a NULL is placed on right columns not found!
RIGHT OUTER JOIN
This is a RIGHT OUTER JOIN. That means that all rows from the RIGHT Table will appear in the report regardless if it finds a match with the LEFT Table.
RIGHT OUTER JOIN Brings Back All Rows in the RIGHT Table
All rows from the Right Table were returned with matches and Dept_No 500 didn’t have a match, so the system put a NULL Value for Left Column values.
FULL OUTER JOIN
This is a FULL OUTER JOIN. That means that all rows from both the RIGHT and LEFT Table will appear in the report regardless if it finds a match.
FULL OUTER JOIN Brings Back All Rows in All Tables
The FULL Outer Join Returns all rows from both Tables. NULLs show the flaws!
Which Tables are the Left and which are the Right?
SELECT Cla.Claim_Id,
Cla.Claim_Date,
SUB.Last_Name,
SUB.First_Name,
"ADD".Phone,
SER.Service_Pay,
PRO.Provider_Code,
PRO.Provider_Name
FROM CLAIMS Cla
LEFT OUTER JOIN PROVIDERS PRO
ON Cla.Provider_No = PRO.Provider_Code
LEFT OUTER JOIN SERVICES SER
ON Cla.Claim_Service = SER.Service_Code
LEFT OUTER JOIN SUBSCRIBERS SUB
ON Cla.Subscriber_No = SUB.Subscriber_No
AND Cla.Member_No = SUB.Member_No
LEFT OUTER JOIN ADDRESSES "ADD"
ON SUB.Subscriber_No = "ADD".Subscriber_No;
Your mission is to show which tables are Left Tables and which ones are Right Tables.
Answer – Which Tables are the Left and which are the Right?
The first table is always the left table and the rest are the right tables. It is the spool or intermediate results from each join that remain the left table. In this case, all rows will return from the Claims table.
INNER JOIN with Additional AND Clause
The additional AND is performed first in order to eliminate unwanted data, so the join is less intensive than joining everything first and then eliminating.
ANSI INNER JOIN with Additional AND Clause
The additional AND is performed first in order to eliminate unwanted data, so the join is less intensive than joining everything first and then eliminating after.
ANSI INNER JOIN with Additional WHERE Clause
The additional AND is performed first in order to eliminate unwanted data, so the join is less intensive than joining everything first and then eliminating after.
OUTER JOIN with Additional WHERE Clause
The additional WHERE is always performed last on Outer Joins. All rows will be joined first, and then the additional WHERE clause filters after the join takes place.
OUTER JOIN with Additional AND Clause
The additional AND is performed in conjunction with the ON statement on Outer Joins. All rows will be evaluated with the ON clause and the AND combined.
Results from OUTER JOIN with Additional AND Clause
The additional AND is performed in conjunction with the ON statement on Outer Joins. This can surprise you. Only Mandee is in Dept_No 100!
Quiz – Why is this considered an INNER JOIN?
This is considered an INNER JOIN because we are doing a LEFT OUTER JOIN on the Employee_Table, and then filtering with the AND for a column in the right table!
The DREADED Product Join
This query becomes a Product Join because it does not possess any JOIN Conditions (Join Keys). Every row from one table is compared to every row of the other table, and quite often the data is not what you intended to get back.
Result Set of the DREADED Product Join
How can Billy Coffing work in 3 different departments?
A Product Join is often a mistake! Three Department rows had an ‘m’ in their name. These were joined to every employee and the information is worthless.
The Horrifying Cartesian Product Join
This joins every row from one table to every row of another table. Nine rows multiplied by 5 rows = 45 rows of complete nonsense!
A Cartesian Product Join is a big mistake.
The ANSI Cartesian Join will ERROR
This causes an error. ANSI won’t let this run unless a join condition is present.
Quiz – Do these Joins Return the Same Answer Set?
Do these two queries produce the same result?
Answer – Do these Joins Return the Same Answer Set?
Do these two queries produce the same result? No, Query 1 Errors due to ANSI syntax and no ON Clause, but Query 2 Product Joins to bring back junk!
How would you Join these two tables?
Looking at the columns above, which will allow these two tables to join?
How would you Join these two tables? You Can’t Yet!
Get ready for the Associative Table!
An Associative Table is a Bridge that Joins Two Tables
The Associative Table is a bridge between the Course_Table and Student_Table.
Quiz – Can you Write the 3-Table Join?
SELECT ALL Columns from the Course_Table and Student_Table and Join them.
Answer to Quiz – Can you Write the 3-Table Join?
The Associative Table is a bridge between the Course_Table and Student_Table, and its sole purpose is to join these two tables together.
Quiz – Can you Write the 3-Table Join to ANSI Syntax?
Please re-write the above query using ANSI Syntax.
Answer – Can you Write the 3-Table Join to ANSI Syntax?
The above query has been written using ANSI Syntax.
Quiz – Can you Place the ON Clauses at the End?
Select S.*, C.*
From Student_Table as S
INNER JOIN
Student_Course_Table as SC
ON S.Student_ID = SC.Student_ID
INNER JOIN
Course_Table as C
ON C.Course_ID = SC.Course_ID;
Please re-write the above query and place both ON Clauses at the end.
Answer – Can you Place the ON Clauses at the End?
This is tricky. The only way it works is to place the ON clauses backwards. The first ON Clause represents the last INNER JOIN and then moves backwards.
The 5-Table Join – Logical Insurance Model
Above is the logical model for the insurance tables showing the Primary Key and Foreign Key relationships (PK/FK).
Quiz - Write a Five Table Join Using ANSI Syntax
Your mission is to write a five table join selecting all columns using ANSI syntax.
Answer - Write a Five Table Join Using ANSI Syntax
SELECT
cla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROM CLAIMS AS cla1
INNER JOIN
SUBSCRIBERS AS sub1
ON cla1.Subscriber_No = sub1.Subscriber_No
AND cla1.Member_No = sub1.Member_No
INNER JOIN
ADDRESSES AS add1
ON sub1.Subscriber_No = add1.Subscriber_No
INNER JOIN
PROVIDERS AS pro1
ON cla1.Provider_No = pro1.Provider_Code
INNER JOIN
SERVICES AS ser1
ON cla1.Claim_Service = ser1.Service_Code ;
Above is the example writing this five table join using ANSI syntax.
Quiz - Write a Five Table Join Using ANSI Syntax
Your mission is to write a five table join selecting all columns using ANSI syntax.
Answer - Write a Five Table Join Using ANSI Syntax
SELECT
cla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROM CLAIMS AS cla1
INNER JOIN
SUBSCRIBERS AS sub1
ON cla1.Subscriber_No = sub1.Subscriber_No
AND cla1.Member_No = sub1.Member_No
INNER JOIN
ADDRESSES AS add1
ON sub1.Subscriber_No = add1.Subscriber_No
INNER JOIN
PROVIDERS AS pro1
ON cla1.Provider_No = pro1.Provider_Code
INNER JOIN
SERVICES AS ser1
ON cla1.Claim_Service = ser1.Service_Code ;
Above is the example writing this five table join using ANSI syntax.
Quiz - Write a Five Table Join Using Non-ANSI Syntax
Your mission is to write a five table join selecting all columns using Non-ANSI syntax.
Answer - Write a Five Table Join Using Non-ANSI Syntax
SELECT cla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROM CLAIMS AS cla1,
SUBSCRIBERS AS sub1,
ADDRESSES AS add1,
PROVIDERS AS pro1,
SERVICES AS ser1
WHERE cla1.Subscriber_No = sub1.Subscriber_No
AND cla1.Member_No = sub1.Member_No
AND sub1.Subscriber_No = add1.Subscriber_No
AND cla1.Provider_No = pro1.Provider_Code
AND cla1.Claim_Service = ser1.Service_Code ;
Above is an example of writing this five table join using Non-ANSI syntax.
Quiz –Re-Write this putting the ON clauses at the END
SELECT
cla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROM CLAIMS AS cla1
INNER JOIN
SUBSCRIBERS AS sub1
ON cla1.Subscriber_No = sub1.Subscriber_No
AND cla1.Member_No = sub1.Member_No
INNER JOIN
ADDRESSES AS add1
ON sub1.Subscriber_No = add1.Subscriber_No
INNER JOIN
PROVIDERS AS pro1
ON cla1.Provider_No = pro1.Provider_Code
INNER JOIN
SERVICES AS ser1
ON cla1.Claim_Service = ser1.Service_Code ;
Above is an example of writing this five table join using Non-ANSI syntax.
Answer –Re-Write this putting the ON clauses at the END
SELECT cla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROM PROVIDERS AS pro1
INNER JOIN
ADDRESSES AS add1
INNER JOIN
SUBSCRIBERS AS sub1
INNER JOIN
SERVICES AS ser1
INNER JOIN
CLAIMS as cla1
ON cla1.Claim_Service = ser1.Service_Code
ON cla1.Subscriber_No = sub1.Subscriber_No
AND cla1.Member_No = sub1.Member_No
ON sub1.Subscriber_No =add1.Subscriber_No
ON cla1.Provider_No = pro1.Provider_Code ;
Above is an example of writing this five table join using ANSI syntax with the ON clauses at the end. Also to make this happen, we had to move the tables around. Notice that the first ON clause represents the last two tables being joined and then it works backwards.
The Nexus Query Chameleon Writes the SQL for Users.
Let Nexus show users the table relationships and then let Nexus build the SQL. Just load the ERwin logical model inside Nexus, and then all users can point and click.
An IN List is much like a Subquery
This query is very simple and easy to understand. It uses an IN List to find all Employees who are in Dept_No 100 or Dept_No 200.
An IN List Never has Duplicates – Just like a Subquery
What is going on with this IN List? Why in the world are their duplicates in there? Will this query even work? What will the result set look like? Turn the page!
An IN List Ignores Duplicates
Duplicate values are ignored here. We get the same rows back as before, and it is as if the system ignored the duplicate values in the IN List. That is exactly what happened.
The Subquery
The query above is a Subquery, which means there are multiple queries in the same SQL. The bottom query runs first, and its purpose in life is to build a distinct list of values that it passes to the top query. The top query then returns the result set. This query solves the problem: Show all Employees in Valid Departments!
How a Basic Subquery Works
The bottom query runs first and builds a distinct IN list. Then, the top query runs using the list.
The Final Answer Set from the Subquery
Quiz- Answer the Difficult Question
How are Subqueries similar to Joins between two tables?
A great question was asked above. Do you know the key to answering? Turn the page!
Answer to Quiz- Answer the Difficult Question
How are Subqueries similar to Joins between two tables?
A Subquery between two tables or a Join between two tables will each need a common key that represents the relationship. This is called a Primary Key/Foreign Key relationship.
Just like Dept_No and Dept_No!
A Subquery will use a common key linking the two tables together; very similar to a join! When subquerying between two tables, look for the common link between the two tables. They will commonly both have a column with the same name, but not always.
Should you use a Subquery or a Join?
Both queries above return the same data. If you only want to see a report where the final result set has only columns from one table, try a Subquery. Obviously, if you need columns on the report where the final result set has columns from both tables, you have to do a Join. The real answer is that Aster Data prefers the join.
Quiz- Write the Subquery
Write the Subquery
Select all columns in the Customer_Table if the customer has placed an
order!
Here is your opportunity to show how smart you are. Write a Subquery that will bring back everything from the Customer_Table if the customer has placed an order in the Order_Table. Good luck! Advice: Look for the common key among both tables!
Answer to Quiz- Write the Subquery
Write the Subquery
Select all columns in the Customer_Table if the customer has placed an order!
The common key among both tables is Customer_Number. The bottom query runs first and delivers a distinct list of Customer_Numbers which the top query uses in the IN List!
Quiz- Write the More Difficult Subquery
Write the Subquery
Select all columns in the Customer_Table if the customer has placed an order over $10,000.00 Dollars!
Here is your opportunity to show how smart you are. Write a Subquery that will bring back everything from the Customer_Table if the customer has placed an order in the Order_Table that is greater than $10,000.00.
Answer to Quiz- Write the More Difficult Subquery
Write the Subquery
Select all columns in the Customer_Table if the customer has placed an
order over $10,000.00 Dollars!
Here is your answer!
Quiz- Write the Subquery with an Aggregate
Write the Subquery
Select all columns in the Employee_Table if the employee
makes a greater Salary than the AVERAGE Salary.
Another opportunity knocking! Would someone please answer the query door!?
Answer to Quiz- Write the Subquery with an Aggregate
Select all columns in the Employee_Table if the employee
makes a greater Salary than the AVERAGE Salary.
SELECT * FROM Employee_Table
WHERE Salary > (
SELECT AVG(Salary)
FROM Employee_Table) ;
Quiz – Write the Triple Subquery
Write the Subquery
What is the Customer_Name who has the highest dollar order
among all customers? This query will have multiple Subqueries!
Good luck in writing this . . . Remember that this will involve multiple Subqueries.
Answer to Quiz – Write the Triple Subquery
Write the Subquery
What is the Customer_Name who has the highest dollar order
among all customers? This query will have multiple Subqueries!
SELECT Customer_Name
FROM Customer_Table
WHERE Customer_Number IN
(SELECT Customer_Number FROM Order_Table
WHERE Order_Total IN
(SELECT Max(Order_Total) FROM Order_Table)) ;
The query is above. Of course, the answer is XYZ Plumbing.
CHARACTER_LENGTH AND OCTET_LENGTH
Query 1
SELECT First_Name
,CHARACTER_Length (First_Name) AS C_Length
FROM Employee_Table ;
Query 2
SELECT First_Name
,Octet_Length (First_Name) AS C_Length
FROM Employee_Table ;
first_name |
c_length |
Mandee |
6 |
Herbert |
7 |
William |
7 |
Loraine |
7 |
Squiggy |
7 |
Richard |
7 |
Cletus |
6 |
Billy |
5 |
John |
4 |
You can also use the CHARACTER_LENGTH or the OCTET LENGTH command. These queries get the same exact answer sets!
The TRIM Command trims both Leading and Trailing Spaces
Query 1
SELECT Last_Name
,Trim(Last_Name) AS No_Spaces
FROM Employee_Table ;
Query 2
SELECT Last_Name
,Trim(Both from Last_Name) AS No_Spaces
FROM Employee_Table ;
Both queries trim both the leading and trailing spaces from Last_Name.
Trim and Trailing is Case Sensitive
'Billy' and ' Squiggy' does not TRIM the trailing 'y' because it was after a capitol 'Y'
First_Name |
No_Y |
Billy |
Billy |
Cletus |
Cletus |
Herbert |
Herbert |
John |
John |
Loraine |
Loraine |
Mandee |
Mandee |
Richard |
Richard |
Squiggy |
Squiggy |
William |
William |
For LEADING and TRAILNG, it IS case sensitive.
Trim and Trailing works if Case right
First_Name |
No_Y |
Billy |
Bill |
Cletus |
Cletus |
Herbert |
Herbert |
John |
John |
Loraine |
Loraine |
Mandee |
Mandee |
Richard |
Richard |
Squiggy |
Squigg |
William |
William |
For LEADING and TRAILNG, it IS case sensitive.
The SUBSTRING Command
First_Name |
Quiz |
Squiggy |
qui |
John |
ohn |
Richard |
ich |
Herbert |
erb |
Mandee |
and |
Cletus |
let |
William |
ill |
Billy |
ill |
Loraine |
ora |
This is a SUBSTRING. The substring is passed two parameters. They are the starting position of the string and the number of positions to return (from the starting position). The above example will start in position 2 and go for 3 positions!
How SUBSTRING Works with NO ENDING POSITION
First_Name |
GoToEnd |
Squiggy |
quiggy |
John |
ohn |
Richard |
ichard |
Herbert |
erbert |
Mandee |
andee |
Cletus |
letus |
William |
illiam |
Billy |
illy |
Loraine |
oraine |
If you don’t tell the Substring the end position, it will go all the way to the end.
Using SUBSTRING to move Backwards
First_Name |
Before1 |
Squiggy |
Squig |
John |
John |
Richard |
Richa |
Herbert |
Herbe |
Mandee |
Mande |
Cletus |
Cletu |
William |
Willi |
Billy |
Billy |
Loraine |
Lorai |
A starting position of zero moves one space in front of the beginning. Notice that our FOR Length is 6 so ‘Squiggy’ turns into ' Squig'. The point being made here is that both the starting position and ending positions can move backwards, which will come in handy as you see other examples.
How SUBSTRING Works with a Starting Position of -1
First_Name |
Before2 |
Squiggy |
S |
John |
J |
Richard |
R |
Herbert |
H |
Mandee |
M |
Cletus |
C |
William |
W |
Billy |
B |
Loraine |
L |
A starting position of -1 moves two spaces in front of the beginning character. Notice that our FOR Length is 3, so each name delivers only the first initial. The point being made here is that both the starting position and ending positions can move backwards, which will come in handy as you see other examples.
How SUBSTRING Works with an Ending Position of 0
First_Name |
WhatsUp |
Squiggy |
|
John |
|
Richard |
|
Herbert |
|
Mandee |
|
Cletus |
|
William |
|
Billy |
|
Loraine |
|
In our example above, we start in position 3, but we go for zero positions so nothing is delivered in the column. That is what’s up!
An Example using SUBSTRING, TRIM and CHAR Together
Last_Name |
Letters |
Jones |
es |
Smith |
th |
Smythe |
he |
Harrison |
on |
Chambers |
rs |
Strickling |
ng |
Reilly |
ly |
Coffing |
ng |
Larkins |
ns |
The SQL above brings back the last two letters of each Last_Name, even though the last names are of different length. We first trimmed the spaces off of Last_Name. Then, we counted the characters in the Last_Name. Then, we subtracted two from the Last_Name character length, and then passed it to our substring as the starting position. Since we didn’t give an ending position in our substring, it defaulted to the end.
SUBSTRING and SUBSTR are equal, but use different syntax
Query 1
SELECT First_Name,
SUBSTRING(First_Name FROM 2 for 3) AS Quiz
FROM Employee_Table ;
Query 2
SELECT First_Name,
SUBSTR (First_Name , 2 ,3) AS Quiz2
FROM Employee_Table ;
Both queries above are going to yield the same results! SUBSTR is just a different way of doing a substring. Both have two parameters in starting position and number of character length.
The POSITION Command finds a Letters Position
SELECT Last_Name
,Position ('e' in Last_Name) AS Find_The_E
,Position ('f' in Last_Name) AS Find_The_F
FROM Employee_Table ;
This is the position counter. What it will do is tell you what position a letter is on. Why did Jones have a 4 in the result set? The ‘e’ was in the 4th position. Why did Smith get a zero for both columns? There is no ‘e’ in Smith and no ‘f’ in Smith. If there are two ‘f’s’ only the first occurrence is reported.
Concatenation
See those || ? Those represent concatenation. That allows you to combine multiple columns into one column. The || (Pipe Symbol) on your keyboard is just above the ENTER key. Don’t put a space in between, but just put two Pipe Symbols together.
In this example, we have combined the first name, then a single space, and then the last name to get a new column called ‘Full name’ like Squiggy Jones.
The Basics of CAST (Convert and Store)
CAST will convert a column or value’s data type temporarily into another data type.
Below is the syntax:
SELECT CAST(<column-name> AS <data-type>[(<length>)] )
FROM <table-name> ;
Examples using CAST:
CAST ( <smallint-data> AS CHAR(5) ) /* convert smallint to character */
CAST ( <decimal-data> AS INTEGER ) /* truncates decimals */
CAST ( <byteint-data> AS SMALLINT ) /* convert binary to smallint */
CAST ( <char-data> AS BYTE (128) ) /* convert character to binary */
CAST ( <byteint-data> AS VARCHAR(5) ) /* convert byteint to character */
CAST ( <integer-data> AS FLOAT ) /* convert integer to float point */
Data can be converted from one type to another by using the CAST function. As long as the data involved does not break any data rules (i.e. placing alphabetic or special characters into a numeric data type), the conversion works. The name of the CAST function comes from the Convert and Store operation that it performs.
Some Great CAST (Convert and Store) Examples
SELECT CAST('ABCDE' AS CHAR(1) ) AS Trunc
,CAST(128 AS CHAR(3) ) AS OK
,CAST(127 AS INTEGER ) AS Bigger ;
The first CAST truncates the five characters (left to right) to form the single character ‘A’. In the second CAST, the integer 128 is converted to three characters and left justified in the output. The 127 was initially stored in a SMALLINT (5 digits - up to 32767), and then converted to an INTEGER. Hence, it uses 11 character positions for its display, ten numeric digits and a sign (positive assumed) and right justified as numeric.
Some Great CAST (Convert and Store) Examples
SELECT Order_Number as OrdNo
,Customer_Number as CustNo
,Order_Date
,Order_Total
,CAST(Order_Total as integer) as Chopped
,CAST(Order_Total as Decimal(5,0)) as Rounded
FROM Order_Table ;
The Column Chopped takes Order_Total (a Decimal (10,2) and CASTs it as an integer which chops off the decimals. Rounded CASTs Order_Total as a Decimal (5,0), which takes the decimals and rounds up if the decimal is .50 or above.
Combining Searched Case and Valued Case
This Query above uses both a Valued Case and Searched Case. That’s ALLOWED!
A Trick for getting a Horizontal Case
SELECT AVG(CASE Class_Code
WHEN 'FR' THEN Grade_pt
ELSE NULL END) AS Freshman_GPA
,AVG(CASE Class_Code
WHEN 'SO' THEN Grade_pt
ELSE NULL END) AS Sophomore_GPA
,AVG(CASE Class_Code
WHEN 'JR' THEN Grade_pt
ELSE NULL END) AS Junior_GPA
,AVG(CASE Class_Code
WHEN 'SR' THEN Grade_pt
ELSE NULL END) AS Senior_GPA
FROM Student_Table
WHERE Class_Code IS NOT NULL ;
Aggregates ignore Nulls, so knowing this trick has allowed for Horizontal Reporting.
Nested Case
A NESTED Case occurs when you have a Case Statement within another CASE Statement. Notice the Double Pipe symbols (||) that provide Concatenation.
Put a CASE in the ORDER BY
