View CUSTOMER_LIST

This view provides a list of customers, with first name and last name concatenated together and address information combined into a single view. The view incorporates data from the CUSTOMER, ADDRESS, CITY, and COUNTRY tables.

Query

SQL

SELECT 
  cu.customer_id AS ID,
  cu.first_name||' '||cu.last_name AS name,
  a.address AS address,
  a.postal_code AS "zip code",
  a.phone AS phone,
  city.city AS city,
  country.country AS country,
  case when cu.active=1 then 'active' else '' end AS notes,
  cu.store_id AS SID
FROM 
  customer AS cu 
JOIN  
  address AS a ON cu.address_id = a.address_id 
JOIN 
  city ON a.city_id = city.city_id
JOIN 
  country ON city.country_id = country.country_id

Columns

This view contains 9 columns.

ID: INTEGER
NAME: VARCHAR(91)
ADDRESS: VARCHAR(50)
zip code: VARCHAR(10)
PHONE: VARCHAR(20)
CITY: VARCHAR(50)
COUNTRY: VARCHAR(50)
NOTES: CHAR(6)
SID: INTEGER