EasySQL challenge #4SELECTWHERECONCAT

Concatenate First and Last Name for Premium Users

Show each premium user as a single full_name column, with email and city.

The challenge runs on the SQLabHub practice database (SQLite) and uses the users table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

List all premium users (is_premium = 1) showing their full name, email and city.

A correct answer returns 69 rows with 3 columns: full_name, email, city.

Tables used in this challenge

`users` 150 rows

id INTEGER (PK) · first_name TEXT · last_name TEXT · email TEXT · phone TEXT · city TEXT · country TEXT · created_at TEXT · status TEXT · is_premium INTEGER

First 3 rows of the users table
id	first_name	last_name	email	phone	city	country	created_at	status
1	Jennifer	Torres	jennifer.torres57@icloud.com	+1-263-376-2371	Houston	Canada	2024-04-13	active
2	Donna	Adams	donna.adams52@gmail.com	+1-753-780-6670	Fort Worth	Canada	2020-09-15	inactive
3	Kenneth	Young	kenneth.young94@proton.me	+1-440-743-9503	Austin	USA	2023-09-05	active

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
full_name	email	city
Linda Allen	linda.allen39@yahoo.com	San Antonio
Brian Anderson	brian.anderson56@gmail.com	San Francisco
Thomas Baker	thomas.baker10@gmail.com	Phoenix
Joseph Brown	joseph.brown39@proton.me	Fort Worth
Carol Brown	carol.brown16@outlook.com	Denver

First 5 of 69 rows. This is the output of the model solution on the practice database.

What you practise

Filtering rows with a WHERE condition
Building and cutting text values
Sorting the result with ORDER BY

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

Use WHERE is_premium = 1

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

Read from the users table.
Filter in WHERE: keep the rows where is_premium equals 1.
Build full_name by joining text values with the || operator.
Sort the result by last_name.

Model solution

Checking your session…

What to watch for

SQLite joins strings with the || operator; MySQL uses CONCAT() and SQL Server uses +. Remember the space literal between the two names, or they run together.

String functions. In SQLite and PostgreSQL, joining text with || returns NULL as soon as one of the parts is NULL.
ORDER BY. Without ORDER BY the row order is not guaranteed, even if it looks stable. Rows that tie on the sort columns can come back in any order.
WHERE. WHERE is evaluated before the SELECT list, so it cannot use a column alias defined there or the result of an aggregate.