Chapter 9 – Understanding SQL by Selecting Indian Cricket team for South Africa tour using SQL

Posted on Updated on

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:

Image

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 3 States – Tamil Nadu, Bangalore, 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

Image

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

Image

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

Image

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 Final_List*

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

on 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s