SQL Functions Cheat Sheet in Common Concepts

 SQL Functions

SQL provides many built-in functions to perform various operations over data that is stored in tables. Let's look at a few most commonly used functions in the industry using the following database.

Database The IMDb dataset stores the information of movies, actors and cast.

Schema

Date Functions

strftime()

strftime() function is used to extract month, year, etc. from a date/datetime field based on a specified format.

Syntax

1

strftime(format, field_name)

SQL

Example

Get the movie title and release year for every movie in the database

1

2

3

SELECT name, strftime('%Y', release_date)

FROM

movie;

SQL

In the above query, we extract year from

release_date

by writing

strftime('%Y', release_date)

in

SELECT

clause.

Let's understand various formats in date functions with an example. Consider the datetime

2021-02-28 08:30:05

format	description	output format	Example
%d	Day of the month	01 - 31	28
%H	Hour	00 - 24	08
%m	Month	01 - 12	02
%j	Day of the year	001 - 365	59
%M	Minute	00-59	30
...	...	...	...

Example

Get the number of movies released in each month of the year 2010

1

2

3

4

5

6

7

8

9

SELECT

strftime('%m', release_date) as month,

COUNT(*) as total_movies

FROM

movie

WHERE

strftime('%Y', release_date) = '2010'

GROUP BY

strftime('%m', release_date);

SQL

Output

month	total_movies
03	2
05	1
06	3
..	..

As the above example, using strftime(), we can perform weekly, monthly or annual analysis deriving finer insights from the data.

Try it Yourself!

Question 1 Get the number of "Action" movies released in the year 2010.

Expected Output

total_movies
4

Question 2 Get all the movies that are released in summer, i.e., between April and June.

Expected Output

name
The Matrix
Toy Story 3
Shutter Island
...

Question 3 Get the month in which the highest number of movies are released.

Expected Output

month	total_movies
06	6

CAST Function

CAST function is used to typecast a value to a desired data type.

Syntax

1

CAST(value AS data_type);

SQL

Example

Get the number of movies released in each month of the year 2010

1

2

3

4

5

6

7

8

SELECT strftime('%m', release_date) as month,

COUNT(*) as total_movies

FROM

movie

WHERE

CAST(strftime('%Y', release_date) AS INTEGER) = 2010

GROUP BY

strftime('%m', release_date);

SQL

Output

month	total_movies
03	2
05	1
06	3
..	..

Here,

CAST(strftime('%Y', release_date) AS INTEGER)

converts the year in string format to integer format.

Try it Yourself!

Question 1 Get all the leap years in the database. An year can be marked as a leap year if

1 .It is divisible by 4 and not divisible by 100 2. Or if it is divisible by 400

Expected Output

year
1972
2008
2016

Other Common Functions

Arithmetic Functions

SQL Function	Behavior
FLOOR	Rounds a number to the nearest integer below its current value
CEIL	Rounds a number to the nearest integer above its current value
ROUND	Rounds a number to a specified number of decimal places

You can refer the following table to further understand how floor, ceil and round work in general.

	2.3	3.9	4.0	5.5
FLOOR	2	3	4	5
CEIL	3	4	4	6
ROUND	2	4	4	6

Let's understand how these functions can be used.

Examples

1. Fetch the ceil, floor and round (to 1 decimal) values of the collections of all movies.

1

2

3

4

5

6

7

SELECT

name,

ROUND(collection_in_cr, 1) AS RoundedValue,

CEIL(collection_in_cr) AS CeilValue,

FLOOR(collection_in_cr) AS FloorValue

FROM

movie;

SQL

Output

name	RoundedValue	CeilValue	FloorValue
The Matrix	46.4	47	46
Inception	83.7	84	83
The Dark Knight	100.5	101	100
...	...	...	...

String Functions

SQL Function	Behavior
UPPER	Converts a string to upper case
LOWER	Converts a string to lower case

When you are not sure about the case (upper/lower) of the movie name, you can write a query as below to search for all the avengers movies irrespective of the case.

1

2

3

4

5

SELECT

name

FROM

movie

WHERE UPPER(name) LIKE UPPER("%avengers%");

SQL

Output

name
Avengers: Age of Ultron
Avengers: Endgame
Avengers: Infinity War

Note

Usually, UPPER() AND LOWER() functions can help you to perform case-insensitive searches.

Try it Yourself!

Question 1 For each movie, get the ceil, floor and round(to 1 decimal) values of budget.

Expected Output

name	round_value	ceil_value	floor_value
The Matrix	6.3	7	6
Inception	16	16	16
The Dark Knight	18	18	18
...	...	...	...

Question 2 Get all the movie names that are released in 2010 and belong to "Action" genre.

Note:

Try using the sql functions learnt so far

Expected Output

movie_name
Inception
Iron Man 2
Thor
Spider-Man: Homecoming
...