I have been learning sql for three weeks now. And i thought why not write a post about some of the advanced stuff i came across. Since @helenanders26 covered a lot of things about sql, here are some sql stuff she might not cover. By the way, this is more like a developer sql post, but if you are a data analyst it is beneficial too. Keep reading
Now obviously, this is not a beginner-friendly post. You have to have some basic knowledge or you will get confused. If you want to start from scratch, then you can check out Helen's series about sql. It is really good. I learned a lot from it.
AND let's get right down to it :)
Table of Contents
- IN
- LIMIT
- Having
- Distinct
- Top
- Wild Cards
- Temporary Tables
IN
The IN operator basically allows you to test if an expression matches any value on a list. It helps to reduce the need to use multiple OR conditions.
Here is an example:
SELECT * FROM customers WHERE name IN ("mohamed", "ahmed", "hassan");
LIMIT
It is used to limit the results based on a certain number. Sometimes you need to limit the results of a query. A great example of this, if you go to my profile, you will see 8 comments with view all 17 comments. This means the query results are limited to 8.
Here is an example:
SELECT * FROM customersLIMIT 3;
Having
It applies conditions on groups of results to filter them, but why not use "WHERE"? Well, "WHERE" places conditions on the selected columns meanwhile "HAVING" places conditions on the groups created by the "GROUP BY" clause.
The "HAVING" clause must follow the "GROUP BY" clause, but precede the "ORDER BY" clause.
In this example, The SQL HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.
SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Distinct
Sometimes you need to get the records without the duplicate ones. Well, that is when distinct comes to show. I would say it is mostly used on columns.
Here is an example:
SELECT DISTINCT name FROM customers;
Top
It is used to limit the data based on a certain number. It gets the records starting from the first row.
Note: Top works only on SQL SERVER and MS ACCESS. It does not work on MYSQL IBM, ..... etc. You can use limit though on these databases. And that is one of the main differences between Limit and Top.
Here is an example:
SELECT TOP (4) name, phone, email FROM customers;
Wild Cards
A wild card is basically a way of searching through strings for characters. Wild cards use the "LIKE" operator. Most known and used wild cards are "%" and "_". If you are a programmer you are probably familiar with regular expressions and these wild cards work like them. Here is briefly how they work.
"%": represents zero or more than one character.
"_": represents only one character.
If you do not know what the heck i am talking about here, relax this is normal. These examples will clear any confusion.
"%"
In the example below, we are telling sql to get the data from the table "customer" where the column "name" records start with the letter "m" and continue with any characters.
SELECT * FROM customers WHERE name LIKE "m%";
here is another example. Here we are basically telling sql to get the records from the column "name" that ends with the letters "ud" and can start with any characters.
SELECT * FROM customers WHERE name LIKE "%ud";
"_"
As i said the "_" will represent one character.
The query below will get the data that starts by any character followed by the pattern "_ohamed".
SELECT * FROM customers WHERE name LIKE "_ohamed";
Another example
So we are telling sql here to get any result from the column name that starts with the pattern "ahme" and ends with any character.
SELECT * FROM customers WHERE name LIKE "ahme_";
And, of course, we can combine the two of them to get more advanced.
This one is a little tricky. We are telling sql to get the data from the column email which starts with any number of characters and ends with only one character after "o". As you may have guessed this will retrieve any ".com" email.
SELECT * FROM customers WHERE email LIKE "%.co_";
Temporary Tables
A lot of RDBMs support temp tables which are nothing but tables to process data like performing insert, update, delete and joins. Sometimes you need to store data in them for some cases. The most important thing to keep in mind about temp tables that as the client finishes the session they are destroyed.
Here is an example:
CREATE TEMPORARY TABLE temp_customers
as
select distinct name, phone, email
from customers
You can also drop a temp table as shown.
DROP TABLE temp_customers;
Conclsuion
Up to this point, i am out of thoughts and words to pitch. If you have something you want to share with me, feel free to comment down below. And thanks for reading :)
Top comments (0)