SQL Expressions

Sql tutorial · PySpark.in

5. Common SQL Concepts

In SQL, we often need to perform calculations, combine values, compare data, or manipulate existing values inside queries.

These operations are performed using SQL Expressions.

5.1 SQL Expressions

An SQL expression is a combination of:

• Column names
• Values
• Operators
• Functions

that produces a single value.

Expressions can be used in many SQL clauses such as:

• SELECT
• WHERE
• UPDATE
• HAVING
• ORDER BY

Types of Expressions

SQL expressions can work with different types of data:

Example Table — movie

Using Expressions in SELECT Clause

Expressions are commonly used inside the SELECT clause to create calculated columns.

Example: Calculate Profit of Movies

Profit Formula:

profit = collection_in_cr - budget_in_cr

Query

```

sql

CREATE TABLE movie (

id INTEGER PRIMARY KEY,

name TEXT,

genre TEXT,

budget_in_cr REAL,

collection_in_cr REAL,

rating REAL

);

INSERT INTO movie (

id,

name,

genre,

budget_in_cr,

collection_in_cr,

rating

)

VALUES

(1, 'Leo', 'Action', 120, 250, 9.0),

(2, 'Jailer', 'Action', 100, 180, 8.5),

(3, 'Kantara', 'Thriller', 20, 150, 9.2),

(4, 'Master', 'Drama', 80, 110, 8.0);

SELECT id,

name,

(collection_in_cr - budget_in_cr) AS profit

FROM movie;

```

Output

How Expression Works

For movie Leo:

250 - 120 = 130

The calculated value is displayed as a new column named profit.

String Expressions

Expressions can also combine strings.

This operation is called String Concatenation.

SQLite String Concatenation Operator

SQLite uses the || operator to combine strings.

|| → Concatenation Operator

Example: Combine Movie Name and Genre

Required format:

movie_name - genre

Query

```

sql

SELECT name || ' - ' || genre AS movie_genre
FROM movie;

```

Output

Using Expressions in WHERE Clause

Expressions are also used in WHERE clauses for filtering rows.

Example: Movies with Profit Greater Than or Equal to 50 Crores

Query

```

sql

SELECT *
FROM movie
WHERE (collection_in_cr - budget_in_cr) >= 50;

```

How This Works

SQL first calculates:

collection_in_cr - budget_in_cr

for every row and then applies the condition:

profit >= 50

Output

Using Expressions in UPDATE Clause

Expressions can update existing column values.

Example: Convert Rating Scale from 10 to 5

Current ratings are stored out of 10.

We want to convert them to a scale of 5.

Query

```

sql

UPDATE movie
SET rating = rating / 2;

```

Before Update

After Update

Using Expressions in HAVING Clause

Expressions can also be used with aggregation functions inside the HAVING clause.

Example: Genres with Average Profit Greater Than or Equal to 100 Crores

Query

```

sql

SELECT genre,
AVG(collection_in_cr - budget_in_cr) AS avg_profit
FROM movie
GROUP BY genre
HAVING AVG(collection_in_cr - budget_in_cr) >= 100;

```

Step-by-Step Explanation

Step 1 — Calculate Profit of Each Movie

Step 2 — GROUP BY genre

Step 3 — Calculate Average Profit

Step 4 — HAVING Filters Groups

Condition:

HAVING AVG(collection_in_cr - budget_in_cr) >= 100

Final Output:

Common Operators Used in Expressions

Important Notes

Expressions Can Be Used Almost Everywhere

Expressions are supported in:

• SELECT
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• UPDATE

Expressions Make Queries Powerful

Using expressions allows us to:

• Create calculated columns
• Filter based on calculations
• Transform existing values
• Generate formatted output
• Perform analytical queries

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