﻿ SQL - Ratings of all customers with a comment string

# SQL Exercises: Ratings of all customers with a comment string

## SQL UNION : Exercise-8 with Solution

8. From the following table, write a SQL query to create a union of two queries that shows the customer id, cities, and ratings of all customers. Those with a rating of 300 or greater will have the words 'High Rating', while the others will have the words 'Low Rating'.

Sample table: Customer

Sample Solution:

``````SELECT customer_id, city, grade, 'High Rating'
FROM customer
UNION
(SELECT customer_id, city, grade, 'Low Rating'
FROM customer
``````

Sample Output:

```customer_id	city		grade		?column?
3002		New York	100		Low Rating
3003		Moscow		200		Low Rating
3004		Paris		300		High Rating
3008		London		300		High Rating
3005		California	200		Low Rating
3007		New York	200		Low Rating
3009		Berlin		100		Low Rating
```

Code Explanation:

The said query in SQL which selects the customer ID, city, and grade columns from the customer table and adds a new column called 'High Rating' or 'Low Rating' based on the value of the grade column.
The query checks if the grade is greater than or equal to 300, and if so, the value of the column 'Rating' is set to the value 'High Rating' as a result. When not done, it set the value of the 'Rating' column to 'Low Rating'.
Then the results of both queries are then combined using the UNION operator.
The results are then sorted by the customer ID.

Relational Algebra Expression:

Relational Algebra Tree:

## Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Any salesman was matched to the city of any customer.
Next SQL Exercise: Customer with more than one current order.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

```db=# SELECT * FROM xxx;
id | section_id | name
----+------------+------
1 |          1 | A
2 |          1 | B
3 |          1 | C
4 |          1 | D
5 |          2 | E
6 |          2 | F
7 |          3 | G
8 |          2 | H
(8 rows)
```

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

```id | section_id | name
----+------------+------
1 |          1 | A
2 |          1 | B
5 |          2 | E
6 |          2 | F
7 |          3 | G
(5 rows)
```

PostgreSQL v9.3 you can do a lateral join

```select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
select * from t t_inner
where t_inner.section_id = t_outer.section_id
order by t_inner.name
limit 2
) t_top on true
order by t_outer.section_id;
```

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook