16:54

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SQL data manipulation language

SELECT statement

CASE statement page 1

Let the list of all the models of PC is required along with their prices. Besides that, if the model is not on sale (not in PC table), in the place of price must be the text "Not available".

The list of all the PC models with their prices we can obtain running the query:

Console

Execute

SELECT DISTINCT Product.model, price 
FROM Product LEFT JOIN 
 PC ON Product.model = PC.model
WHERE product.type = 'pc';

Missing prices will be replaced by NULL values:

Model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	NULL
2112	NULL

The CASE statement helps us to get required text instead of NULLs:

Console

Execute

SELECT DISTINCT product.model, 
 CASE 
 WHEN price IS NULL 
 THEN 'Not available' 
 ELSE CAST(price AS CHAR(20)) 
 END price 
FROM Product LEFT JOIN 
 PC ON Product.model = PC.model
WHERE product.type = 'pc';

Depending on defined conditions, the CASE statement returns one of the possible values. The condition in above example is the checking for NULL. If this condition is satisfied, the text "Not available" will be returned; otherwise (ELSE) it will be the price.

One principal moment is here. As a table is always the result of the SELECT statement, all values from any column must be of the same data type (having regard to implicit type conversions). Then, we cannot combine character constant with a price (numeric type) within a single column. That is the reason why we use the type conversion to the price column to reduce its type to character. As a result, we get

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	Not available
2112	Not available

Pages

CASE

Previous [Data type conversion and CAST function]

[Data modification statements] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

SQL data manipulation language

SELECT statement

CASE statement page 1