SQL Functions

Sql tutorial · PySpark.in

5.2 SQL Functions

SQL provides many built-in functions that help us perform operations on data stored in tables.

Functions make SQL powerful because they allow us to:

• Manipulate strings
• Perform calculations
• Work with dates and time
• Convert data types
• Format output data

Categories of SQL Functions

SQL functions are commonly divided into the following categories:

1. Date Functions
2. CAST Functions
3. Arithmetic Functions
4. String Functions

1. Date Functions

Date functions are used to work with:

• Dates
• Time
• Datetime values

These functions help us extract:

• Year
• Month
• Day
• Hour
• Minute

from a date or datetime field.

SQLite strftime() Function

In SQLite, the strftime() function is one of the most important date functions.

It extracts parts of a date based on a format.

Syntax

```

hide

strftime(format, field_name)

```

Common Format Codes

Example Table — movie

Example: Extract Year from Release Date

Query

```

sql

CREATE TABLE movie (

id INTEGER PRIMARY KEY,

name TEXT,

genre TEXT,

release_date TEXT,

rating REAL

);

INSERT INTO movie (

id,

name,

genre,

release_date,

rating

)

VALUES

(1, 'Leo', 'Action', '2023-10-19', 9.0),

(2, 'Jailer', 'Action', '2023-08-10', 8.5),

(3, 'Avengers Endgame', 'Superhero', '2019-04-26', 9.3),

(4, 'Sherlock Holmes', 'Mystery', '2010-01-15', 8.1);

SELECT name,

strftime('%Y', release_date) AS release_year

FROM movie;

```

Output

How strftime() Works

For the date:

2023-10-19

Examples

2. CAST Function

Different columns can store different data types such as:

• INTEGER
• TEXT
• REAL
• DATE

Sometimes we need to convert one data type into another.

The CAST() function is used for this purpose.

Syntax

```

hide

CAST(value AS data_type)

```

Common Data Types Used in CAST

Example: Movies Released in Each Month of Year 2010

Query

```

sql

SELECT strftime('%m', release_date) AS month,
COUNT(*) AS total_movies
FROM movie
WHERE CAST(strftime('%Y', release_date) AS INTEGER) = 2010
GROUP BY month;

```

Step-by-Step Explanation

Step 1 — Extract Year

```

hide

strftime('%Y', release_date)

```

Output:

Notice that strftime() returns a string.

Step 2 — Convert String to Integer

```

hide

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

```

Now:

"2010" → 2010

Step 3 — Filter Movies Released in 2010

```

hide

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

```

Step 4 — Group Movies by Month

```

hide

GROUP BY month

```

Example Output

3. Arithmetic Functions

Arithmetic functions perform mathematical operations on numeric values.

Common Arithmetic Functions

FLOOR Function

FLOOR() returns the nearest integer less than or equal to the number.

Syntax

```

hide

FLOOR(number)

```

Example

```

sql

SELECT FLOOR(2.3);

```

Output

More Examples

CEIL Function

CEIL() returns the nearest integer greater than or equal to the number.

Syntax

```

hide

CEIL(number)

```

Example

```

sql

SELECT CEIL(-2.7);

```

Output

More Examples

ROUND Function

ROUND() rounds a number to a specified number of decimal places.

Syntax

```

hide

ROUND(number, decimal_places)

```

Examples

Round to 2 Decimal Places

```

sql

SELECT ROUND(2.345, 2);

```

Output

Round to 1 Decimal Place

```

sql

SELECT ROUND(2.345, 1);

```

Output

4. String Functions

String functions are used to manipulate text data.

These functions help us:

• Convert text to uppercase/lowercase
• Search text
• Format text
• Combine strings

Common String Functions

UPPER Function

Converts all characters into uppercase.

Example

```

sql

SELECT UPPER('avengers');

```

Output

LOWER Function

Converts all characters into lowercase.

Example

```

sql

SELECT LOWER('IRON MAN');

```

Output

Case-Insensitive Search Using UPPER()

Example

```

sql

SELECT name
FROM movie
WHERE UPPER(name) LIKE UPPER('%avengers%');

```

How This Works

Both values are converted to uppercase before comparison.

Example:

Avengers
→ AVENGERS

This helps perform case-insensitive searches.

SQL Set Operations

Set operations are used to combine results from multiple SQL queries.

Common Set Operators

SQLite uses EXCEPT instead of MINUS.

Syntax of Set Operations

```

hide

SELECT column1, column2
FROM table1

SET_OPERATOR

SELECT column1, column2
FROM table2;

```

Rules for Set Operations

When combining queries:

1. Number of columns must match
2. Data types should be compatible
3. Column order should be same

Example: Actors Who Acted in Both Movies

Get actor IDs who acted in:

• Sherlock Holmes (movie_id = 6)
• Avengers Endgame (movie_id = 15)

Query

```

sql

CREATE TABLE movie_cast (

movie_id INTEGER,

actor_id INTEGER

);

INSERT INTO movie_cast (

movie_id,

actor_id

)

VALUES

(6, 101),

(6, 102),

(6, 103),

(6, 104),

(15, 103),

(15, 104),

(15, 105),

(15, 106);

SELECT actor_id

FROM movie_cast

WHERE movie_id = 6

INTERSECT

SELECT actor_id

FROM movie_cast

WHERE movie_id = 15;

```

Example Output

UNION Example

```

sql

SELECT actor_id
FROM movie_cast
WHERE movie_id = 6

UNION

SELECT actor_id
FROM movie_cast
WHERE movie_id = 15;

```

Difference Between UNION and UNION ALL

ORDER BY with Set Operations

ORDER BY can appear only once at the end.

Example

```

sql

SELECT actor_id
FROM movie_cast
WHERE movie_id = 6

UNION

SELECT actor_id
FROM movie_cast
WHERE movie_id = 15

ORDER BY actor_id;

```

LIMIT and OFFSET with Set Operations

Similar to ORDER BY, LIMIT and OFFSET are written at the end.

Example

```

sql

SELECT actor_id
FROM movie_cast
WHERE movie_id = 6

UNION

SELECT actor_id
FROM movie_cast
WHERE movie_id = 15

LIMIT 5 OFFSET 2;

```

More Sql tutorials

• Introduction to Databases
• Introduction To DBMS
• Introduction To SQL
• Natural Join
• INNER JOIN
• LEFT JOIN

All tutorials · Try the free PySpark compiler · Practice challenges