by 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:
| CLAUSE | Description |
|---|---|
| SELECT | Columns to display |
| FROM | Table(s) to pull from |
| WHERE | Filter rows |
| GROUP BY | Split rows into groups |
| HAVING | Filter grouped rows and aggregated expressions |
| ORDER | Column 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
| CLAUSE | Description |
|---|---|
| FROM | Gathers all of the data |
| WHERE | Filters rows of data |
| GROUP BY | Groups rows together |
| HAVING | Filtered grouped rows and aggregated values |
| SELECT | Specifies columns to display |
| ORDER BY | Sorts 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.
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!
Good day, Could I export your image and utilize it on my own website?
Loving the information on this site, you have done outstanding job on the posts .
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
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.
I gotta bookmark this website it seems very beneficial .
Thanks for sharing, this is a fantastic article.Really looking forward to read more. Keep writing.
Great, thanks for sharing this article post.Much thanks again. Really Great.
I genuinely enjoy looking through on this web site , it holds superb articles .
You can increase your blog visitors by having a fan page on facebook.*’.:*
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…
Yikes this definitely takes me back, i’ve been wondering about this subject for a while.
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.
Some really fantastic info , Sword lily I noticed this.
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!…
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.
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.
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.
Whoah this blog is wonderful i really like reading your articles. Stay up the good work! You know, a lot of individuals are looking round for this info, you can help them greatly.
Muchos Gracias for your blog post.Thanks Again. Really Great.
Wow, great post.Really looking forward to read more. Awesome.
I truly appreciate this blog.Really thank you! Will read on…
whoah this blog is wonderful i love reading your posts. Keep up the great work! You know, many people are searching around for this information, you can help them greatly.
Hi there, its fastidious piece of writing concerning media print, we all be familiar with media is a great source of information.
A big thank you for your blog post.Much thanks again.
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!
Thanks for sharing your thoughts. I really appreciate your efforts and I am waiting for your next post thanks once again.
Really informative blog post.Really thank you!
A big thank you for your blog.Thanks Again. Really Great.
Greetings! Very helpful advice on this article! It is the little changes that make the biggest changes. Thanks a lot for sharing!
Judging by the way you write, you seem like a professional writer.,;~*-
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!
I just added your RSS Feed on my RSS reader, it is so nice to read your blog.;-*;:
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
hello I was very impressed with the setup you used with this website. I use blogs my self so good job. definatly adding to bookmarks.
Thanks for the great post on your blog, it really gives me an insight on this topic.*~“:
I appreciate, cause I found exactly what I was looking for. You’ve ended my four day long hunt! God Bless you man. Have a great day. Bye
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
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
Superb, what a web site it is! This website gives helpful data to us,
keep it up.
Take a look at my web page :: tracfone
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!
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!
You made some good points there. I did a search on the topic and found most people will agree with your blog.
Me and my good friend were arguing about an issue similar to that! Nowadays I know that I was perfect. lol! Thanks for the information you post.
Thanks for having the time to write about this issue. I truly appreciate it. I’ll post a link of this entry in my site.
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.
Cheers, I just stopped by to visit your site and thought I’d say , you have some good info here.
very good post, i definitely adore this site, persist in it
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!
Great intro to SQL.