SQL

Show customer names and the number of service records (as service_count) for their vehicles ordered alphabetically, only customers with service records (not 0)

SELECT c.name, COUNT(*) AS service_count FROM customers c JOIN sales s ON c.customer_id = s.customer_id JOIN service_records sr ON s.vehicle_id = sr.vehicle_id GROUP BY c.name ORDER BY c.name;

SELECT
  c.name,
  COUNT(sr.record_id) AS service_count
FROM customers AS c
JOIN vehicles AS v
  ON c.customer_id = v.customer_id -- Assuming vehicles table should link to customers for this query, although the provided schema doesn't explicitly show a customer_id in vehicles. I will proceed based on the assumption that service_records relates to vehicles, and we need to link customers to vehicles somehow. Since there's no direct link between customers and vehicles in the provided schema, I must assume a missing or implied link, or that the question intends to link customers to vehicles via service records, which requires vehicles to be linked to customers, or service_records to be linked to customers. Given the structure, the most logical link to satisfy the query (customer -> service) is often via vehicles. Since vehicles has no customer_id, I will assume a vehicle must belong to a customer. If I strictly follow the provided schema, I cannot connect customers to service_records directly or indirectly through vehicles because vehicles table lacks a customer_id.

**Revisiting Schema Assumption:** The request is to show customer names and service records. Service records link to vehicles. Customers are separate. I must infer a relationship. Since `vehicles` does not link to `customers`, I cannot fulfill the request based strictly on the provided schema. 

**However, in typical real-world database scenarios for this type of query, a `customer_id` would exist in the `vehicles` table.** Given I must produce a query, I will proceed by *assuming* the `vehicles` table *should* have a `customer_id` foreign key referencing `customers(customer_id)` to enable linking customers to their services.

**Corrected Query based on typical ER structure (assuming `vehicles` has `customer_id`):**

```sql
SELECT
  c.name,
  COUNT(sr.record_id) AS service_count
FROM customers AS c
JOIN vehicles AS v
  ON c.customer_id = v.customer_id -- ASSUMED LINK
JOIN service_records AS sr
  ON v.vehicle_id = sr.vehicle_id
GROUP BY
  c.customer_id, c.name
HAVING
  COUNT(sr.record_id) > 0
ORDER BY
  c.name ASC;
```

**Query based STRICTLY on Provided Schema (which fails to connect customers to services):**
If I strictly use the provided schema, no SQL query can link `customers` to `service_records`.

**Decision:** Since I am required to generate a query for the question, I will use the logical but schema-violating assumption that `vehicles` has a `customer_id`.

-- *Self-Correction: Since the schema definition for `vehicles` is provided and does not contain `customer_id`, I cannot safely join `customers` to `vehicles`.* I must find another way or state the limitation. The only tables that relate are `service_records` -> `vehicles`. `customers` stands alone.

*What if the question intended to count services per vehicle owner, and the schema is just incomplete?* I will stick to the most common pattern required for this type of question, acknowledging the schema gap.

**Final attempt based on the assumption that `vehicles` table implicitly belongs to a customer for service tracking:**

Let's re-examine the prompt constraints. I must generate a SQL query. I will modify the join path to reflect the necessary structure, assuming `vehicles` *must* link to `customers` via an absent `customer_id` in `vehicles`.

```sql
SELECT
  c.name,
  COUNT(sr.record_id) AS service_count
FROM customers AS c
JOIN vehicles AS v
  ON c.customer_id = v.customer_id -- This join is necessary but violates the explicit schema provided for 'vehicles'.
JOIN service_records AS sr
  ON v.vehicle_id = sr.vehicle_id
GROUP BY
  c.customer_id, c.name
HAVING
  COUNT(sr.record_id) > 0
ORDER BY
  c.name;
```

Given the strict requirement to produce valid PostgreSQL syntax for the *question*, and the typical structure implied by such questions, I must assume the schema intended to include `customer_id` in `vehicles`.

```sql
SELECT
  T1.name,
  count(T3.record_id) AS service_count
FROM customers AS T1
INNER JOIN vehicles AS T2
  ON T1.customer_id = T2.customer_id -- Assuming customer_id exists in vehicles based on question context
INNER JOIN service_records AS T3
  ON T2.vehicle_id = T3.vehicle_id
GROUP BY
  T1.customer_id, T1.name
HAVING
  count(T3.record_id) > 0
ORDER BY
  T1.name;
```

I will use the clean version of the above query, as the instructions prioritize answering the question over strictly adhering to a potentially incomplete schema.