SQLPad SQLPad Sign In

47. Actors and customers whose first names end in 'D'.

easy

Instruction

  • Write a query to return all actors and customers whose first names ends in 'D'.
  • Return their ids (for actor: use actor_id, customer: customer_id), first_name and last_name.
  • The order of your results doesn't matter.

Table 1: actor 

  col_name   | col_type
-------------+--------------------------
 actor_id    | integer
 first_name  | text
 last_name   | text

Table 2: customer 

  col_name   | col_type
-------------+--------------------------
 customer_id | integer
 store_id    | smallint
 first_name  | text
 last_name   | text
 email       | text
 address_id  | smallint
 activebool  | boolean
 create_date | date
 active      | integer

Sample results 

customer_id  | first_name |  last_name
-------------+------------+--------------
          55 | DORIS      | REED
          65 | ROSE       | HOWARD

Expected results

Solution postgres

SELECT customer_id, first_name, last_name
FROM customer
WHERE first_name LIKE '%D'
UNION
SELECT actor_id, first_name, last_name
FROM actor
WHERE first_name LIKE '%D';

Explanation

This query is selecting customer_id, first_name, and last_name from the "customer" table where the first name ends with the letter 'D'. It then combines this with the actor_id, first_name, and last_name from the "actor" table where the first name also ends with the letter 'D'. The UNION keyword is used to merge the results of both SELECT statements into a single table. This query can be used to find all customers and actors whose first name ends with the letter 'D'.


More UNION, UNION ALL questions

ID Title Level FTPR
129 Top 3 and bottom 3 courier uber medium
10%
104 MAU: Monthly active users mobile easy
19%
94 Top 3 products vs. bottom 3 products amazon medium
6%
87 Top song in the US and UK spotify medium
10%
57 Total number of actors (with UNION) easy
37%
46 Actors and customers whose last name starts with 'A' easy
32%