SQL Reference

Example

DECLARE @num INT = 10;
IF @num > 15
  PRINT 'The number is more than 15'
ELSE
  PRINT 'The number is not more than 15';

Output

The number is not more than 15

Explanation

The given conditional code first checks if @num is greater than 15. Since @num is 10, the condition evaluates to false. Consequently, the control flows to the ELSE part of the statement, and the phrase ‘The number is not more than 15’ is printed as the output.

Example

SELECT name, hire_date,
CASE
    WHEN hire_date BETWEEN '2015-01-01' AND '2020-12-31' THEN 'Recent hire'
    ELSE 'Old hire'
END AS hire_status
FROM employee;

Output

| name     | hire_date  | hire_status  |
|----------|------------|--------------|
| John Doe | 2018-06-20 | Recent hire  |
| Jane Doe | 2012-08-10 | Old hire     |

Explanation

This SQL code checks the hire_date of each record in the employee table. If the hire_date falls between 2015 and 2020, it outputs ‘Recent hire’. For all other dates, it outputs ‘Old hire’. The CASE clause is used within a SQL SELECT statement to specify a condition, the ELSE clause provides the alternative output when the condition is not met.

Example

IF (1 > 2)
  PRINT '1 is greater than 2'
ELSE
  PRINT '1 is not greater than 2'

Output

'1 is not greater than 2'

Explanation

In the provided code, the IF statement tests whether 1 is greater than 2. Since the statement is false, the code in the ELSE block (PRINT '1 is not greater than 2') is executed. Thus, the output ‘1 is not greater than 2’ is displayed.

Example

DECLARE
    n NUMBER := 10;

BEGIN
  IF n > 20 THEN
    DBMS_OUTPUT.PUT_LINE ('The number is greater than 20');
  ELSE
    DBMS_OUTPUT.PUT_LINE ('The number is not greater than 20');
  END IF;
END;

Output

The number is not greater than 20

Explanation

In the given example, we have declared a variable n and assigned a value of 10. The IF-ELSE statement checks if n is greater than 20. If true, it outputs ‘The number is greater than 20’. However, if n is not greater than 20, the ELSE condition triggers and outputs ‘The number is not greater than 20’. Since n is 10, it falls into the ELSE condition, and we see ‘The number is not greater than 20’ in the output.

Example

CREATE TABLE Employees (
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    SALARY REAL NOT NULL
);

INSERT INTO Employees (ID, NAME, SALARY)
VALUES (1, 'John', 50000), (2, 'Jane', 70000), (3, 'Doe', 90000);

SELECT NAME,
CASE
    WHEN SALARY <= 60000 THEN 'Low'
    WHEN SALARY BETWEEN 60000 AND 80000 THEN 'Medium'
    ELSE 'High'
END AS Salary_Bracket
FROM Employees;

Output

NAME | Salary_Bracket
---------------------
John | Low
Jane | Medium
Doe  | High

Explanation

In this example, the CASE statement is used to categorize employee’s salaries into ‘Low’, ‘Medium’, or ‘High’ salary brackets. The ELSE clause assigns ‘High’ to all remaining cases where salary is more than 80000.