Scribd
Upload
7 views1 page

CASE

The document provides SQL syntax and explanations for various functions including CASE, COALESCE, CAST, and NULLIF, which are used for conditional logic and data type conversion. It also covers how to create, replace, and drop views in a database. These functions and commands are essential for managing and manipulating data effectively in SQL queries.

Uploaded by

Mace Chua
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
7 views1 page

CASE

The document provides SQL syntax and explanations for various functions including CASE, COALESCE, CAST, and NULLIF, which are used for conditional logic and data type conversion. It also covers how to create, replace, and drop views in a database. These functions and commands are essential for managing and manipulating data effectively in SQL queries.

Uploaded by

Mace Chua
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

CASE – Executes the statement only if a certain condition has been met

SELECT column CASE


WHEN condition 1 THEN result 1
WHEN condition 2 THEN result 2
ELSE failed result END
FROM table;

CASE Expression – Executes the statement only if a value is present


SELECT CASE column
WHEN value 1 THEN result 1
WHEN condition 2 THEN result 2
ELSE failed result END
FROM table;

*Use the SUM function to count the number of values in the table
SELECT CASE column
SUM(WHEN value 1 THEN 1
ELSE 0 END)
FROM table;

*The case function can be used repeatedly for a simultaneous result

COALESCE – Prevents the use of “NULL” in an argument/operation function and instead


replaces the NULL with another value
SELECT column, (column – COALESCE(NULL, value, value, value) FROM table;
*Accepts an unlimited number of arguments/values inside and returns the first non-null
value
*Useful when performing operations with a table consisting of NULL values

CAST – Converts a data type into another type but it must be possible to begin with
SELECT CAST(column AS NEW TYPE)

NULLIF – Takes 2 inputs and equates them to each other, if the statement is true then
“NULL” will be returned if not then the first value will be returned.
SELECT NULLIF(value 1, value 2) FROM table

VIEWS – Database of objects that is of a stored query, it does not physically make the table
but it simply stores it
CREATE VIEW view name AS subquery

CREATE OR REPLACE VIEW view name AS new subquery


*To replace or change something in the subquery

DROP VIEW IF EXISTS view name


*To delete a view

You might also like