Page 104 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

SQL CASE WHEN Operator
The CASE is used to provide if-then-else type of logic in SQL. You can use the CASE
statement within a SQL statement. The CASE statement goes through conditions and return
a value when the first condition is satisfied (like an IF-THEN-ELSE statement). So, once a
condition is true, it will stop reading and return the result. If no conditions are true, it returns
the value in the ELSE clause.
The CASE statement is followed by at least one pair of WHEN and THEN statements.
Every CASE statement must end with the END statement. The ELSE statement is optional, if
there is no ELSE parts and no conditions are true, it returns NULL.
Syntax:
SELECT column1, column2 CASE column_name WHEN value1 THEN result1 WHEN
value2 THEN result2 ELSE result END FROM table_name;

Example: Consider the ‘Persons’ table having the following records:
ID PersonName Age City Gender
1 Christian 22 Tanza M
2 John Mark 21 Trece M
3 Nigel 21 Indang M
4 Jayson 23 Indang M
5 Cielo 21 Indange F

SELECT PersonName, City, Age, CASE Gender WHEN ‘M’ THEN ‘Male’ WHEN ‘F’ THEN
‘Female’ ELSE ‘Not Mentioned’ END AS ‘Gender’ FROM Persons;

The above query would produce the following result:
ID PersonName Age City Gender
1 Christian 22 Tanza Male
2 John Mark 21 Trece Male
3 Nigel 21 Indang Male
4 Jayson 23 Indang Male
5 Cielo 21 Indange Female

SQL EXISTS Operator
The EXISTS operator checks the existence of a result of a subquery. The EXISTS operator
tests whether a subquery fetches at least one record. It returns true if the subquery returns
one or more records and returns false if the subquery returns no records.
The result of EXISTS is a Boolean value True or False. A valid EXISTS subquery must contain
an outer reference and it must be a correlated subquery.
It can be used in a SELECT, INSERT or DELETE statement.
Example: Consider the ‘Customers’ table having the following records:
CustomerID CustomerName Address City Age Gender
1 Christian Langit Punta Tanza 22 M
2 John Mark Aguado Trece 21 M
3 Nigel Andam Kayquit Indang 21 M

Consider the ‘Orders’ table having the following records.
OrderID CustomerID OrderDate
101 1 2019-12-21
102 3 2019-12-13

SELECT CustomerName, Address, City FROM Customers WHERE EXISTS
(SELECT*FROM Orders WHERE Customers.CustomerID = Orders.CustomerID);

Page | 20