SQL Basics

• Functions are predefined routines that transform values through conversion or calculation
• These functions operate on the values displayed in the result, rather than changing the stored data
• There are hundreds of functions; you will typically use a few dozen
• Functions depend heavily on the DBMS. Only a few work identically across all systems
  • Oracle,
  • MySQL,
  • SQL Server,
  • PostgreSQL,
  • DB2,
  • SQLite,
  • MariaDB

SELECT	FUNCTIENAAM(column1)
FROM	<table>;

• Parameters appear in parentheses after the function name and specify the values on which it operates
• A function returns a value of a particular data type
• Some functions take no parameters, for example when simply retrieving a system value
• Parentheses normally follow the function name. Oracle system expressions such as CURRENT_DATE are an exception

• Functions fall into several groups. Most DBMSs support the following groups:

• Some commonly used text functions

Select the customer name and contact name from Customers for contacts whose names sound like "Michael Green."

-- Oracle / MySQL / SQL Server
SELECT cust_name,
       cust_contact
FROM   Customers
WHERE  SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

Select the vendor names from Vendors and display them in uppercase.

-- Oracle / MySQL / SQL Server
SELECT   UPPER(vend_name) AS Leveranciers
FROM     Vendors
ORDER BY vend_name;

• Some commonly used numeric functions

Select item_price multiplied by quantity from OrderItems. Name the column 'Totaalprijs', round it down and sort by it in descending order.

-- Oracle / MySQL / SQL Server
SELECT   FLOOR(item_price * quantity) AS Totaalprijs
FROM     OrderItems
ORDER BY Totaalprijs DESC;

• Dates and times are stored in database-specific formats
• Functions are often needed when working with dates
• Date and time functions are among the most important SQL functions

Select all orders placed in May

-- Oracle / MySQL / PostgreSQL
SELECT *
FROM   Orders
WHERE  EXTRACT(month FROM order_date) = 5;

-- MySQL / SQL Server
SELECT *
FROM   Orders
WHERE  MONTH(order_date) = 5;	

-- SQL Server
SELECT *
FROM   Orders
WHERE  DATEPART(mm, order_date) = 5;	

-- SQLite
SELECT * 
FROM   Orders
WHERE  SUBSTR(order_date, 6,2) = '05';	

• Other functions can be divided into several categories.
• Examples include conversion functions, NULL-handling functions and system functions

Select the product name and price including 21% VAT, rounded to two decimal places

-- Oracle / SQL Server	
SELECT   prod_name,
         CAST(prod_price * 1.21 AS DECIMAL(8,2)) AS PrijsIncl
FROM     Products
ORDER BY PrijsIncl;

-- MySQL / Oracle / Azure SQL Server / PostgreSQL
SELECT   prod_name,
         ROUND(prod_price * 1.21,2) AS PrijsIncl
FROM     Products
ORDER BY PrijsIncl;	

Select all customer email addresses and replace NULL values with 'Onbekend'

-- Oracle 
SELECT	 NVL(cust_email,'Onbekend') EmailAdressen
FROM     Customers;

-- MySQL 
SELECT	 IFNULL(cust_email,'Onbekend') AS EmailAdressen
FROM     Customers;

-- SQL Server 
SELECT	 ISNULL(cust_email,'Onbekend') AS EmailAdressen
FROM     Customers;

-- Oracle / MySQL / SQL Server / PostgreSQL 
SELECT	 COALESCE(cust_email,'Onbekend') AS EmailAdressen 
FROM     Customers;

Select the current date

-- Oracle
SELECT   SYSDATE HuidigeDatum
FROM     dual;	

-- MySQL
SELECT   CURDATE() AS HuidigeDatum;

-- SQL Server
SELECT   CAST(GETDATE() AS Date) AS HuidigeDatum;	

-- PostgreSQL
SELECT   CURRENT_DATE AS HuidigeDatum;	

31. Select the customer name and add a column containing the length of the name (5)

32. Select all orders placed in January 2012 (2)

33. Select the product name and price from Products. Round the price down (9)

34. Select prod_id, prod_name and prod_price from Products. Add columns for VAT and the price including VAT. Format the last two columns to two decimal places (9)

35. Select the name of the current database (1)