October 14, 2024
General Topics / SQL

SQL Simplified: Let’s go back to the basics

Preet Parmarby Preet Parmar
Spread the love

In this article, I am not going to go over what is SQL or why do we use it. Instead, I am going to dive right into the fundamental query clauses and explore what they are and how to use them.

A SQL query is written in a particular order:

CLAUSEDescription
SELECTColumns to display
FROMTable(s) to pull from
WHEREFilter rows
GROUP BYSplit rows into groups
HAVINGFilter grouped rows and aggregated expressions
ORDERColumn to sort

The execution order of a SQL query is different than the syntax. Here’s the order in which all the clauses are executed

CLAUSEDescription
FROMGathers all of the data
WHEREFilters rows of data
GROUP BYGroups rows together
HAVINGFiltered grouped rows and aggregated values
SELECTSpecifies columns to display
ORDER BYSorts the result

SELECT

  • The SELECT clause is used to specify which columns do we want the statement to return.
  • It is usually followed by a list of column names and/or expressions seperated by commas.
  • The selected columns/expressions are returned as columns.

Selecting Columns

  • We can specify the column names seperated by commas directly and fetch it from the a table using the FROM clause.
  • SELECT batsman, team
    FROM cricket_dataset;

Selecting All Columns

  • To select all the columns, we can use * to select all the columns in the SELECT clause
  • SELECT *
    FROM cricket_dataset;

Selecting Expressions

  • In addition to simply selecting the column names, we can also apply complex functions on a column and return that as a column.
  • In our database, we have a column called “total runs” which holds runs scored by individual player broken out by ball. So in order to get a list of all the batsmen and the total number of runs they scored we can write the query as follows:
  • SELECT batsman, SUM(total_runs)
    FROM cricket_dataset;
  • Please note:
    • The column name which appears in our final output is the complete function expression. The column name of our calculated column would be: SUM(total_runs).
    • We will only a single row with the first batsman name and sum of total runs for all the players. We will talk about this in GROUP BY section but wanted to point this out here as this is a general misconception people have about aggregating data.

Aliasing Columns

  • The purpose of aliasing the columns is to give a temporary name to any column or expression listed in the select clause. The alias is then displayed as the column name in the output.
  • Giving an alias isn’t a permanent change, the temporary name exists only within the query.
  • SELECT batsman, SUM(total_runs) AS total_runs
    FROM cricket_dataset;

FROM

  • It is used to specify the source of your data.
  • Similar to selecing columns and expressions, we can give alias to tables as well. This is very useful when we fetch data from multiple tables.

From a single table

  • This is the simplest case of any query, data is fetched from a single table. We have already seen that above, but here is a quick example
  • SELECT batsman
    FROM cricket_database AS data;

From multiple tables

  • We can fetch data from multiple tables as well. We will need to join the different table on some kind of a key (more about joining tables later)
  • SELECT cd.batsman, md.year
    FROM cricket_database AS cd JOIN match_database AS md
    ON cd.match_id = bd.match_id;

WHERE

  • It is used to restrict the query results by filtering the data.
  • It is applied on a row level, not on an aggregated level.
  • It can’t be applied on an aggregated column.
    • In the example shown, we can’t use WHERE to filter SUM(total_runs)
  • One important thing to note is that alias doesn’t work in the WHERE clause, because the output isn’t formally set until you filter the data. So we need to use the actual column name.
    • In the example show, we can’t use “batter” in the WHERE clause, we will need to use the actual name “batsman”.
  • SELECT batsman AS batter, SUM(total_runs) AS runs
    FROM cricket_database
    WHERE batsman = “MS Dhoni”;
  • Here, we will get only 1 row with MS Dhoni as batsman and his total runs in the next column.

GROUP BY

  • The purpose of GROUP BY clause is to collect rows into groups and summarize the rows within the groups.
  • The result is just one row per group.
  • You can use alias names in the GROUP BY clause, as it runs after the data is fetched hence we already have the alias names for the column (unlike the WHERE clause).
  • In the Selecting Expressions section, we saw that even when we aggregate all the runs using SUM we still get one row. We can use GROUP BY to group all the batsman together and get the desired output
  • SELECT batsman as batter, SUM(total_runs)
    FROM cricket_dataset
    GROUP BY batter;

HAVING

  • The HAVING clause is used to filter aggregated columns.
  • It always immediately follows a GROUP BY clause, without GROUP BY there can be no HAVING clause.
  • Never put a condition in the HAVING clause that does not involve an aggregation. Those conditions are evaluated much more efficiently in the WHERE clause.
  • To get a filtered list of batsmen who scored more than 500 total runs, we can use the following query:
  • SELECT batsman, SUM(total_runs) as runs
    FROM cricket_database
    GROUP BY batsman
    HAVING runs > 500;
  • NOTE: the above query works for MySQL and SQLite but we need to use the raw aggregation inside HAVING clause for other databases.

ORDER BY

  • It is used to specify how you want the results of the query to be sorted.
  • By default it sorts in an ascending order, but if you would like to specify one then you add “DESC” or “ASC” at the end.
  • You can sort by the columns and expressions that are not in your SELECT list as well.
  • You can use alias names in your ORDER BY clause.
  • If we need the list of batsmen along with their total runs sorted in a descending order, then we can use the following query:
  • SELECT batsman, SUM(total_runs) as runs
    FROM cricket_database
    GROUP BY batsman
    ORDER BY runs DESC;
  • You can also sort by numeric column position:
    ORDER BY 1 DESC, 2 ASC;

Hopefully, this article gives you a clear explanation of what the different clauses are and how to use them.


Spread the love

Preet Parmar

View all posts by Preet Parmar →

You might also like

SQL Tables: Bread and butter of any SQL query

SQL Exercise: Let’s practice Filtering with Wildcards

SQL Query: Delete Duplicates from a table

51 thoughts on “SQL Simplified: Let’s go back to the basics”

  1. I’m truly enjoying the design and layout of your website. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a developer to create your theme? Fantastic work!

    Reply

  4. It¡¦s really a cool and useful piece of information. I¡¦m satisfied that you shared this useful information with us. Please keep us up to date like this. Thank you for sharing. Pristina Travel

    Reply

  5. Can I simply say what a reduction to find somebody who actually knows what theyre talking about on the internet. You undoubtedly know easy methods to deliver a difficulty to gentle and make it important. More people must learn this and understand this facet of the story. I cant believe youre no more widespread since you undoubtedly have the gift.

    Reply

  11. This is good-looking appealing , i was searching for somewhat but found your site as a substitute through Google . I be in love with networking. Anyways, really wanted in the direction of drop through and say hello . i have subscribed in the direction of your site plus i am hunting onward in the direction of the updates , Gratitude…

    Reply

  13. Nice one for picking the correct to debate this amazing, I think eagerly in it and true love researching a little more about this specific area. Any time probable, when you get to competencies, you imagination adding all of your blog page by working with further more facts? This can be very ideal for everyone.

    Reply

  15. I discovered your blog post internet site on the internet and check many of your early posts. Always maintain the good operate. I just extra encourage Feed to my MSN News Reader. Looking for forward to reading far more on your part later on!…

    Reply

  16. Aw, that was a really good quality post. In theory I’d like to write like this too – taking time and real effort to make a good article… but what can I say… I procrastinate alot and also never seem to get something done.

    Reply

  17. Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you could do with a few pics to drive the message home a little bit, but instead of that, this is excellent blog. A fantastic read. I’ll definitely be back.

    Reply

  18. Aw, this was an incredibly good post. Spending some time and actual effort to make a superb article… but what can I say… I hesitate a whole lot and never seem to get nearly anything done.

    Reply

  26. Very nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed browsing your blog posts. In any case I will be subscribing to your feed and I hope you write again soon!

    Reply

  32. Considerably, the article is in reality the most excellent on this precious topic. I harmonise with your conclusions and can eagerly look forward to your next updates. Simply saying thanks will certainly not simply just be sufficient, for the phenomenal lucidity in your writing. I definitely will directly grab your rss feed to stay informed of any updates. De lightful work and much success in your business dealings!

    Reply

  34. There are a couple of interesting points over time here but I don’t know if I see every one of them center to heart. There’s some validity but Let me take hold opinion until I take a look at it further. Excellent post , thanks and now we want much more! Added onto FeedBurner also

    Reply

  38. Having read this I believed it was extremely informative.

    I appreciate you finding the time and energy to put this article together.
    I once again find myself spending way too much time both
    reading and posting comments. But so what, it was still worth it!

    Here is my website … tracfone

    Reply

  39. An fascinating discussion will be worth comment. I’m sure that you can write on this topic, it will not certainly be a taboo subject but normally individuals are there are not enough to talk on such topics. To another location. Cheers

    Reply

  41. It’s a shame you don’t have a donate button! I’d certainly donate to this brilliant blog! I suppose for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to fresh updates and will share this site with my Facebook group. Chat soon!

    Reply

  42. Youre so cool! I dont suppose Ive read anything like this before. So nice to locate somebody with original applying for grants this subject. realy appreciation for beginning this up. this amazing site is one thing that is needed on the web, an individual if we do originality. helpful work for bringing something totally new towards web!

    Reply

  46. Hmm it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I submitted and say, I’m thoroughly enjoying your blog. I too am an aspiring blog writer but I’m still new to everything. Do you have any tips for rookie blog writers? I’d certainly appreciate it.

    Reply
  49. Pingback: SQL Tables: Bread and butter of any SQL query - Preet Parmar's Blog

  50. you are actually a just right webmaster. The site loading speed is amazing. It kind of feels that you are doing any distinctive trick. Also, The contents are masterpiece. you have done a magnificent process on this matter!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *