Now in continuation to the part 1, we will see one more example of ORDER BY

SELECT column_name1, column_name2 FROM table_name 
ORDER BY column_name1 DESC, column_name2 ASC;

Here we can order rows based on the entries in column_name1 in descending order and then by the entries in column_name2 in ascending order.

The LIMIT command

It limits the number of rows returned to you. For example you have a list of movies that you want to watch, which has 3 categories: Romance, Horror and Action. You want to order the films by the year they were released and instead of going through the whole list of hundreds of movies, you only want to choose from the top 5 recently released films. To do the above, do as follows:

SELECT * FROM movie_table 
ORDER BY release_date DESC
LIMIT 5;

Key notes to remember: LIMIT will be the last command to be written, after the ORDER BY.

The BETWEEN command

This is a very useful command when you want to get information in brackets. What I mean is, you have a list containing all your loved one’s birthdates and you want to know which birthdates lies between April and June. Then use the BETWEEN operator as follows:

SELECT birthdates FROM table_name
WHERE birthdates BETWEEN april AND june;

Now suppose you need to know all birthdays except for the month of November, then do as follows:

SELECT birthdates FROM table_name
WHERE birthdates NOT BETWEEN november AND december;

Key notes to remember: Dates work in a funny way. When you start from November, it starts from the very first date of that month and when you end with December, it wont count the first day of December (not inclusive).

The IN function

This is a very useful function if you want to know whether a certain value is present in a table or not. For example, you have a table with data of different breeds of dogs available for adoption. you want to know whether there is a brown Doberman Pinscher available for adoption, then you use IN as follows:

SELECT doberman_pinscher FROM dog_table
WHERE color IN(brown);

If in case, for whatever nonsensical reason, you don’t like a brown Doberman pinscher, and you would rather adopt a Dobbie of any other color, then you can do as follows:

SELECT doberman_pinscher FROM dog_table
WHERE color NOT IN(brown);

The LIKE operator

This operator is used to match patterns in a string. For example, you want to know which dog breeds has the term shepherd at the end.

SELECT * FROM dog_table
WHERE breed_name LIKE '%shepherd';

The % sign is used to fill in any number of characters before shepherd and if you want only single characters to be filled then use _

For example, you want to know which John Wick movies are there in your movie list, you only need a single number to be filled after the title of the film, then do as follows:

SELECT movies FROM table_name
WHERE title LIKE 'John Wick_';

Key notes to remember: Remember that LIKE is case sensitive, which means that if you write john wick instead of John Wick, it will not return you anything. If you want something thats case insensitive, you ILIKE instead, the same way you would use LIKE

Another example, you have a list of all possible relations a human being can have, like mother, brother, father etc. and you want only those relationships that has the term ther in it.

select relationship_table from table_name
WHERE relationship_table LIKE '%ther%';

Now, Lets learn about the most common aggregate functions:

  • AVG(): Returns the average
  • COUNT(): Returns the total number of rows
  • MIN(): Returns the minimum value
  • MAX(): Returns the maximum value
  • SUM(): Returns the sum of all values

Examples to cover the above aggregate functions are as follows:

To get the average cost of feeding a puppy for a year.

SELECT AVG(puppy_expense) FROM dog_table;

To get the minimum cost you will incur from a packet of dog treats

SELECT MIN(puppy_treats) FROM dog_table;

To get the maximum cost you might incur from taking your pup to a dog spa

SELECT MAX(puppy_spa) FROM dog_table;

To get the sum of all the costs (No such thing as too much) for having a puppy at home

SELECT SUM(puppy_expense) FROM dog_table;

If you want to round of the AVG of all the expenses up to a certain decimal point, use ROUND() function as follows:

SELECT ROUND(AVG(puppy_expense), 3) FROM dog_table;

The number 3 is the second argument passed into the round function, which lets you decided how many digits after the decimal do you want. I wanted 3.

Follow up to the blog for continuation on SQL.