Hi All,
Hope you have understood concepts what I discussed so far. Thanks for reading my blog !!!
Today I wanted to explain how we can write SQL – When I was new to Data warehouse , I asked Google about SQL. She gave so many links about SQLs. Hope you guys also have done the same. Now I will explain in my way about all the techniques in SQL using our Indian Cricket team.
Anyone wants to know What is Structured Query Language (SQL) then please ask Google, she will explain better than me
I will explain how we can easily understand and use SQL effectively
In SQL you will have 3 Segments
Select Clause –
1.You can mention the columns from the tables
2.You can set Conditional statements , Case When, IF and Else, Counters etc.,
3.You can use SUB STRING, CASTE Functions, TRIM functions etc.,
From Clause -
1. Here you need to mention your Database.Table Name (alias name can be given for the tables)
Where Clause -
1. Here you need to mention what all records you need from your tables , using the relational operators (=,<>,>=,<= ,and, or)
Now you got the result using Select From Where clause – do you want to join this result with you neighbor table then use the Joins (Inner Join, Left Join, Right Join, Cross Joins)
Indian board of directors wants to see the eligible players from different states:
In India we have 28 States and 7 Union Territories,each state has their own Cricket Club and connected to Indian Cricket board.
Each State team details will be kept in their own Tables and Director of Indian Cricket team will join all the tables and pull the final list of the Players.
To make it simple I will use 4 States – Tamil Nadu, Bangalore, Hyderabad, Delhi.
Team Tamil Nadu:
In Tamil Nadu team Ian and Arya are retried so the director should not see them in his final team list
In this team director wants players are
1. Aged below 40
2. Minimum 10 Matches
3.All rounders
4. Only active players
The SQL :
SEL Team,Team_ID,Player_ID,Player_Name,
CASE All_Rounder WHEN ‘Y’ THEN ‘SELECTED’ ELSE ‘NOT SELECTED’,
END AS SELECTION,AGE,No_of_Matches, Hundreds, Fifty’s, Bating, Bowling, All_Rounder,Effective_from,Expired_On,
FROM
TEAM.TABLE_TAMIL_NADU as Tamil_Nadu
WHERE
AGE<=’40′ AND All_Rounder=’Y’ AND and Expired_On=Cast(’31-12-999′ as Date Format ‘YYYYMMDD’)
Just think using the rules and query what all are the records you will get from Tamil Nadu team!!!
Team Bangalore:
In Bangalore team Mickey and Raghav are retried so the director should not see them in his final team list
In this team director wants players are
1. Aged below 30
2. Minimum 20 Matches
3.All Bates man
4. Only active players
The SQL :
SEL Team,Team_ID,Player_ID,Player_Name,
CASE Bating WHEN ‘Y’ THEN ‘SELECTED’ ELSE ‘NOT SELECTED’,All_Rounder ,
END AS SELECTION,AGE,No_of_Matches, Hundreds, Fifty’s, Bowling, All_Rounder,Effective_from,Expired_On,
FROM
TEAM.TABLE_Bangalore as Bangalore
WHERE
AGE<=’30′ AND All_Rounder=’Y’ AND and Expired_On=Cast(’31-12-999′ as Date Format ‘YYYYMMDD’) and Matches <=’20′
Team Delhi:
In Tamil Nadu team Sachin and Agarker are retried so the director should not see them in his final team list
In this team director wants players are
1. Aged below 25
2. Minimum 30 Matches
3.All bowlers
4. Only active players
The SQL :
SEL Team,Team_ID,Player_ID,Player_Name,
CASE Bowling WHEN ‘Y’ THEN ‘SELECTED’ ELSE ‘NOT SELECTED’,All_Rounder ,
END AS SELECTION,AGE,No_of_Matches, Hundreds, Fifty’s, Bating, All_Rounder,Effective_from,Expired_On,
FROM
TEAM.TABLE_Delhi as Delhi
WHERE
AGE<=’25′ AND All_Rounder=’Y’ AND and Expired_On=Cast(’31-12-999′ as Date Format ‘YYYYMMDD’) and Matches <=’30′
Now the director got the results from each of the Tables. Now he wants to join all them and load it into a Selection table.
Its very simple , all alias names used to get the columns from their tables, and the column Team is key for all the three tables and used for joining the tables.
Select Tamil_Nadu* , Bangalore*, Delhi*
from
(
(Copy the Tamil Nadu Query) as team_1
Left join
(Copy the Bangalore Query) as team_2
on team_1.Team=team_2. Team
Left join
(Copy the Delhi Query) as team_3
Left join team_2.Team=team_3. Team
) Final_List
Now the Director got the team he wants !!! India team is now ready for South Africa trip
Hope you guys understood the scenario, use this as base scenarios and play your cases with the help of our friend Miss Google.
Cheers
Asik
Filed under: Data Warehouse Testing - Learners Guide Tagged: Business Intelligence testing, Cricket, Data warehouse testing, ETL, Indian Cricket Team, Joins, Key column, SQL, testing