SQL - Windows functions

SQL in a nutshell

 

SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator with WHERE clause to search for a specified pattern in a column.

Example:

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

The above example shows - get all the customers whose name starts with 'a'

Wildcard characters:

Symbol	Description
%	Represents zero or more characters
_	Represents a single character
[]	Represents any single character within the brackets *
^	Represents any character not in the brackets *
-	Represents any single character within the specified range *
{}	Represents any escaped character **

Using the % Wildcard

The % wildcard represents any number of characters, even zero characters.

Return all customers that ends with the pattern 'es':

SELECT * FROM Customers
WHERE CustomerName LIKE '%es';

Using the _ Wildcard

The _ wildcard represents a single character.

It can be any character or number, but each _ represents one, and only one, character.

Return all customers with a City starting with any character, followed by "ondon":

SELECT * FROM Customers
WHERE City LIKE '_ondon';

Using the [] Wildcard

The [] wildcard returns a result if any of the characters inside gets a match.

Return all customers starting with either "b", "s", or "p":

SELECT * FROM Customers
WHERE CustomerName LIKE '[bsp]%';

Using the - Wildcard

The - wildcard allows you to specify a range of characters inside the [] wildcard.

Return all customers starting with "a", "b", "c", "d", "e" or "f":

SELECT * FROM Customers
WHERE CustomerName LIKE '[a-f]%';

SQL NULL functions

NVL() function

NVL() function is used to provide a default value when the specified field is null. we can also use the COALESCE() function as alternative.

Syntax:

NVL(column_name, default_value)

For example

In the below table, unitOnOrder is optional and can be null.

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Jarlsberg	10.45	16	15
2	Mascarpone	32.56	23
3	Gorgonzola	15.67	9	20

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL. To avoide such nullibility issue, we use NVL() as below:

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;

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 conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Example:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

The following SQL will order the customers by City. However, if City is NULL, then order by Country:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

SQL Sequence

This is the primary key field that we would like to be created automatically with unique and sequential value every time a new record is inserted.

CREATE SEQUENCE syntax:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. 

It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence)

INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');

SQL Operators

 Using operators

Arithmetic operators: 

• + (Addition)
• - (subtraction)
• * (Multiplication)
• / (Division)
• % (Modulus) - it returns reminder.

Example:

SELECT 30 + 20;

Comparison Operators:

• = (equal to)
• != (Not equal to) or <>
• > (Greater than)
• < (Less than)
• >= (Greater than or equal to)
• <= (Less than or equal to)

Example:

SELECT * FROM Products

WHERE Price <> 18;

SELECT * FROM Products

WHERE Price >= 30;

Logical Operators:

• AND (To check for both conditions to be true)

SELECT * FROM Customers

WHERE City = "London" AND Country = "UK";

• OR (To check for one of the conditions to be true)

SELECT * FROM Customers

WHERE City = "London" OR Country = "UK";

• NOT (To negate the given condition)

SELECT * FROM Customers
WHERE NOT Country = 'Spain';

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';

• IN (To specify multiple possible values for a column

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

• BETWEEN (Between a certain range)

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

• ALL (TRUE if all of the subquery values meet the condition)

SELECT ProductName
FROM Products
WHERE ProductID = ALL
  (SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10);

• LIKE (Search for a pattern)

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

• ANY (TRUE if any of the subquery values meet the condition)

SELECT * FROM Products

WHERE Price > ANY (SELECT Price FROM Products WHERE Price > 50);

• EXIST

SELECT SupplierName

FROM Suppliers

WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

• SOME

SELECT * FROM Products

WHERE Price > SOME (SELECT Price FROM Products WHERE Price > 20);

Bitwise Operators:

• & (Bitwise AND)
• | (Bitwise OR)
• ^ ( Bitwise exclusive or)