SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions Look at the following “Products” table: P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder 1 Jarlsberg 10.45 16 15 2 Mascarpone 32.56 23 3 Gorgonzola 15.67 9 20 Suppose that the “UnitsOnOrder” column is optional, and…
The SQL CASE Expression The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no…
The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The…
The SQL SELECT INTO Statement The SELECT INTO statement copies data from one table into a new table. SELECT INTO Syntax Copy all columns into a new table: SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition; Copy only…
The SQL ANY and ALL Operators The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values. The SQL ANY Operator The ANY operator: returns a boolean value as…
The SQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records. EXISTS Syntax SELECT column_name(s) FROM table_name WHERE…
The SQL HAVING Clause The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. HAVING Syntax SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(),…
The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns The columns must also have similar data types The…
