SQL TUTORIALS

How to Create Conditional Column in SQL

Summary

• The CASE statement is used to create a column whose values are derived from the value of one or more other columns. To create a CASE statement, use the following syntax:
  SELECT [column_name], [column_name],
   CASE
   WHEN [condition1] THEN [value]
   WHEN [condition2] THEN [value]
   ELSE [value]
   END AS [new_column_name]
FROM [table_name];
• This technique can be used with any data type, though the resulting column must be the same data type across conditions.
• NOTE: As mentioned in the video, CASE statements work in a top-down approach, meaning that once a condition is met, the lower conditions will be ignored.
• NOTE: The CASE statement can be used in other clauses, such as the WHERE, GROUP BY, and ORDER BY clauses. We will cover these in a later tutorial.

How to Use CASE Statements in Aggregate Functions

Summary

• When used in Aggregate Functions, the CASE statement is used to as both a filter and an IF/THEN clause. To create a CASE statement within the COUNT Function, use the following syntax:
  SELECT [column_name], [column_name],
   COUNT(CASE
   WHEN [condition1] THEN [integer]
   WHEN [condition2] THEN [integer]
   ELSE [integer] END) AS [new_column_name]
FROM [table_name]
GROUP BY [column_name], [column_name];
• When including a CASE statement in the SUM or AVG Function, use the following syntax:
  SELECT [column_name], [column_name],
   SUM(CASE
   WHEN [condition1] THEN [column_name]
   WHEN [condition2] THEN [column_name]
   ELSE [integer] END) AS [new_column_name]
FROM [table_name]
GROUP BY [column_name], [column_name];
• NOTE:As mentioned in the video, CASE statements work in a top-down approach, meaning that once a condition is met, the lower conditions will be ignored.

Exercise

Congratulations! You just completed the CASE Statements Tutorial! To help test your knowledge, let's use the "lego" database to run some queries with CASE statements. This exercise is based on the exercise of the "Joins" tutorial.

**It's highly recommended that you complete the exercise outlined in the previous tutorial before beginning this exercise.**

Instructions:

1. Open your RDBMS Client (e.g., MySQL, PostgreSQL, MS SQL Server).
2. If you haven't created the "lego" database yet, please view the instructions in the "Joins" tutorial.
3. "lego" database ERD:



The Database used in this exercise includes data on hundreds of Lego sets and their associated parts, colors, and themes.

4. With the "lego" database, write the following queries:
1. Write a query that includes a conditional column that states whether or not a part is in inventory. if it's not in inventory, mark it with "Not Available". Only include the part_num, name fields (and the conditional column). Make sure to give the conditional column a descriptive name.
2. Write a query that includes a conditional column that states the category of theme of a given set in inventory. Only include the set_num and the set name (and the conditional column). Make sure to give your conditional column a descriptive name. The categories are listed below:
   
   • Kids: 4 Juniors, Animals, Cars, Dino, Dino 2010, Dino Attack
   • Young Teens: Adventurers, Agents, Avengers, Ben 10, Coast Guard
   • Older Teens: Harry Potter, Indiana Jones, Lamborghini
3. Write a query that shows the popularity status of all sets in inventory. If the quantity is greater than 20, mark the set as 'Popular', if the quantity if between 10 and 20, then mark the set as 'Average', if the quantity is less than 10, mark the set as 'Not Popular'.
4. Write a query that recommends the number of each set in inventory for the Holiday Season with the following conditions: if the current quantity is greater than 20, the recommended quantity should be double the current quantity, if the current quantity is between 10 and 20, the recommended quantity should be 1.75 times the current quantity, else the quantity should be the same. Name the conditonal column 'Recommended Quantity'. Include the set's name and the current quantity.
5. Exercise Completed! Click here to view the answers.

Have any issues with the above exercise? Post your question on Discord!