In addition to querying and referencing raw column data with SQL, you can also use expressions to write more complex logic on column values in a query. These expressions can use mathematical and string functions along with basic arithmetic to transform values when the query is executed, as shown in this physics example.

Example query with expressions

SELECT **particle_speed / 2.0** AS half_particle_speed FROM physics_data WHERE **ABS(particle_position) * 10.0 > 500**;

Each database has its own supported set of mathematical, string, and date functions that can be used in a query, which you can find in their own respective docs.

The use of expressions can save time and extra post-processing of the result data, but can also make the query harder to read, so we recommend that when expressions are used in the SELECT part of the query, that they are also given a descriptive alias using the AS keyword.

Select query with expression aliases

SELECT **_col_expression_ AS _expr_description_**, … FROM mytable;

In addition to expressions, regular columns and even tables can also have aliases to make them easier to reference in the output and as a part of simplifying more complex queries.

Example query with both column and table name aliases

SELECT column **AS better_column_name**, … FROM a_long_widgets_table_name **AS mywidgets** INNER JOIN widget_sales ON mywidgets.id = widget_sales.widget_id;

Exercise

You are going to have to use expressions to transform the BoxOffice data into something easier to understand for the tasks below.

1. List all movies and their combined sales in **millions** of dollars
	1. SELECT M.Title, (B.Domestic_sales + B.International_sales) / 1000000 AS Combined_Sales FROM Movies M INNER JOIN Boxoffice B on M.Id = B.Movie_id;
2. List all movies and their ratings **in percent**
	1. SELECT M.Title, Rating * 10 AS Rating_Percent FROM Movies M INNER JOIN Boxoffice B on M.Id = B.Movie_id;
3. List all movies that were released on even number years
	1. SELECT * FROM movies WHERE Year%2 = 0;
	2. SELECT Title FROM movies WHERE Year%2 = 0;
	3. SELECT Title FROM movies WHERE NOT (Year % 2 = 1);
	4. SELECT Title FROM movies WHERE CASE WHEN Year % 2 = 0 THEN 1 ELSE 0 END = 1;

Exercise 9 — Tasks

1. List all movies and their combined sales in **millions** of dollars ✓
2. List all movies and their ratings **in percent** ✓
3. List all movies that were released on even number years ✓

This lesson from SQLBolt introduces the use of expressions in SQL queries to perform more complex logic on column values. Key points include:

• Expressions allow you to use mathematical and string functions, as well as basic arithmetic, to transform data when querying.
• Each database supports its own set of functions (mathematical, string, date) that you can use within queries.
• You can provide aliases to expressions and columns using the AS keyword, making query results clearer and easier to read.
• Tables can also be given aliases for simpler references in complex queries.
• Example queries demonstrate these concepts, such as selecting half the value of a column, or renaming columns and tables for clarity.

The lesson includes an exercise using Movies and Boxoffice tables. It asks you to:

1. List all movies with their combined sales in millions of dollars.
2. Show all movies with their ratings as a percent.
3. List all movies released in even-numbered years.

The lesson emphasizes that using expressions can make queries more powerful, but recommends using aliases for readability and clarity.[1]

1

Yes, you can rewrite your query in several equivalent ways while producing the same output, as long as you follow SQL's syntax and logic. Here are some alternative versions and styles for expressing your original query:

1. Use explicit table names instead of aliases:

SELECT Movies.Title, (Boxoffice.Domestic_sales + Boxoffice.International_sales)/1000000 AS Combined_Sales
FROM Movies
INNER JOIN Boxoffice ON Movies.Id = Boxoffice.Movie_id;

2. Alias the expression only:

SELECT M.Title, ((B.Domestic_sales + B.International_sales) / 1000000) AS Combined_Sales
FROM Movies AS M
JOIN Boxoffice AS B ON M.Id = B.Movie_id;

3. Use LEFT JOIN (if you want all movies, even those with no sales)

SELECT M.Title, (B.Domestic_sales + B.International_sales) / 1000000 AS Combined_Sales
FROM Movies M
LEFT JOIN Boxoffice B ON M.Id = B.Movie_id;

4. Expression in the SELECT with spaces for readability:

SELECT 
  M.Title, 
  (B.Domestic_sales + B.International_sales) / 1000000 AS Combined_Sales
FROM 
  Movies M
  INNER JOIN Boxoffice B ON M.Id = B.Movie_id;

5. Use parenthesis, function or CAST/CONVERT (not needed here, but sometimes seen):

SELECT 
  M.Title, 
  CAST((B.Domestic_sales + B.International_sales) AS FLOAT) / 1000000 AS Combined_Sales
FROM Movies M
INNER JOIN Boxoffice B ON M.Id = B.Movie_id;

All these versions are functionally the same as your original query, just using different stylistic elements or SQL features. If you want to see totally different but equivalent logical approaches, let me know!

1

Yes, here are several alternative ways to write your SQL query to select movie titles released in even years:

1. Using the MOD function (equivalent to % in some SQL dialects):

SELECT Title FROM movies WHERE MOD(Year, 2) = 0;

2. Using CASE with a condition (a bit verbose but functionally equivalent): ✅

SELECT Title FROM movies WHERE CASE WHEN Year % 2 = 0 THEN 1 ELSE 0 END = 1;

3. Using NOT operator: ✅

SELECT Title FROM movies WHERE NOT (Year % 2 = 1);

All of these queries will return movies where the release year is even. The first one is the most common alternative—MOD(Year, 2) = 0 is often used because it is standard SQL and supported in most SQL systems.

1