SQL Exercises: Ratings of all customers with a comment string

Last update on December 20 2024 12:05:11 (UTC/GMT +8 hours)

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:

-- Selecting specific columns (customer_id, city, grade, 'High Rating') from the 'customer' table
SELECT customer_id, city, grade, 'High Rating'

-- Filtering rows in the 'customer' table where the 'grade' is greater than or equal to 300
FROM customer
WHERE grade >= 300

-- Performing a UNION operation with the result set of a subquery that selects specific columns (customer_id, city, grade, 'Low Rating') from the 'customer' table
UNION

-- Selecting specific columns (customer_id, city, grade, 'Low Rating') from the 'customer' table
SELECT customer_id, city, grade, 'Low Rating'

-- Filtering rows in the 'customer' table where the 'grade' is less than 300
FROM customer
WHERE grade < 300

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: