What is SQL?

SQL (Structured Query Language) is a standardized programming language designed for querying, manipulating, and managing relational databases. Users interact with databases to store, retrieve, update, and delete data. As SQL becomes more widely used in the data world, it is essential that you master these 5 basic SQL functions.

 

 

5 Basic SQL Functions

 

1: Where

The WHERE function is used to filter rows from a table based on a specific condition. You may use the WHERE function to narrow down results in a database. If a row meets the condition(s) it is returned in the output. The basic syntax of the WHERE function is displayed below:
SELECT column1, column2
          FROM table_name
          WHERE condition;
Let’s say we have a table called “NBA_Players” with the columns “first_name”, “surname”, “age”, “position”, and “career_points” and I wanted to find out the names of all of the players under the age of 30 that play the PG position. The query would be:
SELECT name
FROM NBA_Players
WHERE age > 30 AND position = ‘PG’

 

 

2: LIKE

The LIKE function is used alongside the WHERE function to perform pattern matching on text within your database. It allows you to retrieve rows from a table that match a specific pattern specified using wildcards. The basic syntax of the LIKE function is displayed below:
          SELECT column1, column2
          FROM table_name
          WHERE column_name LIKE pattern;

Let’s say I wanted to find out all of the NBA players’ surnames that begin with the Letter “A”. The query would be:
SELECT name
FROM NBA_players
WHERE surname LIKE ‘A%’
The % sign represents any sequence of characters, including zero characters. It can be used before, after, or both before and after the pattern. In the example above, the ‘A%’ would return any surname that begins with A, followed by any number of characters proceeding the letter A.

 

 

3: SUM

The SUM function is an aggregate function that adds the total of numeric values within a specified column. The basic syntax of the SUM() function is displayed below:
SELECT SUM(column_name)
          FROM table_name;
Going back to our previous NBA example, let’s say I wanted to find out the total number of points players have scored within our dataset. The query would be:
SELECT SUM(career_points)
FROM NBA_players
The output would return a single value of all the points scored by all players in the dataset.

 

 

4: GROUP BY

The GROUP BY function allows us to group rows based on the values of one or more columns. It is typically combined with aggregate functions such as SUM() which we discussed in the point above. The basic syntax of the GROUP BY function is displayed below:
SELECT column1, column2 ,aggregate_function(column_name)
          FROM table_name
          GROUP BY column1, column2;
Using our NBA example yet again, let’s say we wanted to find out the total points that each position had scored, the query would be:
SELECT position, sum(career_points)
FROM NBA_players
GROUP BY position
This would return 5 rows (PG, SG, SF, PF, C) with the total number of points that each position had scored in the entire dataset.

 

 

5: TOP

The TOP function allows you to retrieve a specified number of rows from a table. It is usually used to get the highest or lowest values from a table. The basic syntax of TOP is displayed below:
SELECT TOP (N) column1, column2,
          FROM table_name
          ORDER BY column_name DESC; (DESC for descending order, ASC for ascending order)
For our last example, let’s say we wanted to find out the names of the TOP 10 scorers in our NBA dataset and how many points they’ve scored. The query would be:
SELECT TOP(10), first_name, surname, career_points
FROM NBA_players
ORDER BY career_points DESC

 

So, these are 5 vital functions that you need to master for anyone using SQL in 2023. I hope this blog helped!

 

The Data School
Author: The Data School