Interacting with Database:Basic Queries in SQL Video Lecture | SSC CGL Tier 2 - Study Material, Online Tests, Previous Year

hey check out the fancy logo welcome to
statistical programming dotnet where we
help you learn all the ways that
programming can help you quantify
qualify and generally predict your world
today we're going to be learning basic
commands from sequel which is the
language of relational databases
if you plan to analyze data for a modern
business there's a 95% chance that the
data is housed in a data warehouse and
sooner or later you'll have to retrieve
it yourself
but sequel commands are not quite like
any other kind of programming so if you
are new to it there's nothing like a
good visual representation of what each
command command is doing let's get into
that now this video and a lot of
learning material I plan to create is
based on a visual approach when you
visualize something you're forced to
create that thing using your imagination
and doing so will really tell you where
you have a solid understanding and where
you do not furthermore as you create a
visual model of what you're working with
you gave yourself a clear reference
point to relate to as you build more
complex understanding and that's going
to work very well as we start with
database basics a database is a bunch of
rows stacked on top of each other or
maybe it's a bunch of columns that are
smashed together side by side no one's
quite sure but what we can all agree on
is that rows and columns each make up a
table now tables have some interesting
properties such as their primary key
which defines the granularity of the
table and also allows them to join two
other tables and speaking of the other
tables a collection of them is known as
a database and this is the thing that we
can query with our sequel scripts since
this is a statistical program in net
what we're interested in is the ability
to use the data in some environment
outside of sequel such as a programming
language like Python or even a simple X
parrot a cool feature of sequel commands
is that they're very phonetic
and that they describe a function well
so when I think of select which is our
first command that we're gonna learn I
picture them taking the items off a cart
and place it into my shopping basket so
let's look at how the Select statement
is written the term select goes first
and is always eventually followed by
from as in what table or tables should
we pull from next we need to tell which
columns we want to select in this video
I'm just going to refer to columns by
their color as if that were their name
and the names in our example will be
blue column orange column or green
column these will always follow the word
select and always will be followed by
the word from oh and don't forget to
separate with a comma except the last
one otherwise you will get an error next
we'll put the table name or names after
the word from and we'll finally have
everything we need to complete the most
basic select statement so it'd be very
limiting if we could only grab whole
columns from our tables as many
databases can attain hundreds thousands
even millions of rows the where
statement acts as a way to filter out
rows you don't want to include in your
results for this example I'm using the
dark colored lines within the cells to
represent values notice the two red
values in our bloom column suppose this
column refers to order status for some
ecommerce site and dark red values
represent cancellations of some kind and
we want them excluded from our select
statement to do so we're gonna add where
blue column does not equal dark red
aside from does not equal just as an
aside we could also use a greater than a
less to men value an equal sign in or
not in to filter out a group and or a
few others but we're going to keep
things simple with this does not equal
Satan
so getting back to it it's important to
understand that this has the effect of
filtering the results before they are
accessed in our query so our red records
here are gonna basically be filtered out
before the rest of the data is selected
and this is what this is going to look
like kind of visually the red records
disappear and our select statement our
data is now ready to be used in our
select statement so the where statement
always goes at the bottom of the query
and our final statement will look like
this beautiful but wait I thought sequel
was all about querying databases not
just tables it is that's where joins
come in adding a join clause to your
select statement allows you to combine
tables based on some common key in our
select statement that join clause
appears after the word from but before a
wear statement which we don't have in
this example we select from our first
table and then add the word join
followed by the second table we wish to
pull from like so then to complete the
clause we identify the columns from each
table on which they'll join side note
they don't have to be the exact same
name they just have to contain values
that will match up with each other the
joint works by matching exact values
from one column to the other notice here
that our red and green values are not
physically matched on the same rows the
join will shift each row based on the
value it's joined column matches up to
watch the table on the right closely as
the entire rows move to match the blue
columns together
finally once the join columns are
aligned the two tables are merged
together
and then just like in the where
statement this merge allows the select
process to begin so while the joy we
just learned merges tables side by side
the union matches tables or a single
column top to bottom a basic example is
a union statement sitting between two
select statements the union says that
after the blue and yellow columns are
selected from Table one they should be
placed on top of the blue and yellow
columns of Table two notice we had the
exact same column names the union can
only occur on tables with the exact same
column names and where the columns being
Union have the same data type so in the
past I have emphasized that a filter or
a join happens before the Select
statement in the case of this query the
Select statements go first followed by
the Union which results in the two
tables converging via their columns the
last sequel command that I'm going to go
over is the group by clause the group by
is necessary in the Select statement
when there is a mathematical operation
being performed over one or more of the
columns but there also exists a column
with no operation being done to it the
group I sits at the end of the query
followed by the column or columns that
are not part of the mathematical
operation in this case the blue column
in our example this query performs the
sum function over the green column with
respect to the unique values in the blue
column you'll notice the values in the
green column that correspond to the
duplicate dark red and dark green values
in the blue column will be combined in
total together for our final result
this has been a basic walkthrough of the
commands an analyst is most likely to
use while queried from a database
although each command represents a
simple concept
there are derivation of each that can
become quite complex writing expert
level sequel queries can involve many
combinations of the commands discussed
in can approach the skill level of
actually an art form but it's all based
on the fundamental understanding of the
basic concepts later videos will dive
into each of these commands in more
details and also some more advanced
sequel strategies until then Godspeed
and thanks so much for watching