SQL Basics

• Calculated fields contain values that are not stored directly in the database
• They are created by combining or calculating values that are stored in the database

• Combining text fields is called concatenation
• Examples of combined fields: street + house number, postal code + town

SELECT <column1 en column2>
FROM   <table>

• Values can also be calculated mathematically
• Examples of mathematical calculations: years of service, age, totals and averages

SELECT <column1 gedeeld door column2>
FROM   <table>

• Although these values are not stored ready-made in the database, it is often preferable to calculate them in SQL rather than in the client application

CONCAT is a function. Functions are covered in the next chapter.

SELECT <text1> + <text2>

SELECT <text1> || <text2>

SELECT CONCAT(text1, text2)

Select vend_name and vend_country from Vendors and combine them. Put parentheses around vend_country and sort the result

-- MySQL - MariaDB
SELECT   CONCAT(vend_name,' (',vend_country,')')
FROM     Vendors
ORDER BY vend_name;

-- Oracle - DB2 - PostgreSQL - SQLite	
SELECT   TRIM(vend_name) || ' (' || TRIM(vend_country) || ')'
FROM     Vendors
ORDER BY vend_name;

-- SQL Server	
SELECT   TRIM(vend_name) + ' (' + TRIM(vend_country) + ')'
FROM     Vendors
ORDER BY vend_name;

• Use an alias to name a calculated field (a new column)
• Client applications can refer to an alias
• Create an alias by adding the AS keyword to the SELECT statement

SELECT <value1> + <value2> AS <NewColumnName> 	
FROM   <table>

• Avoid spaces in an alias
• Aliases are often used to provide names without spaces for existing columns

Select vend_name and vend_country from Vendors and combine them into a column named 'VendTitle'. Put parentheses around vend_country and sort the result

-- Oracle
SELECT   TRIM(vend_name) || ' (' || TRIM(vend_country) || ')' AS VendTitle
FROM     Vendors
ORDER BY vend_name;	

-- MySQL
SELECT   CONCAT(vend_name, ' (', vend_country, ')') AS VendTitle
FROM     Vendors
ORDER BY vend_name;

-- SQL Server
SELECT   TRIM(vend_name) + ' (' + TRIM(vend_country) + ')' AS VendTitle
FROM     Vendors
ORDER BY vend_name;	

• The following operators can be used in SQL

Use parentheses ( ) to specify the order of operations in a calculation

-- MySQl / SQL Server	
SELECT 8*2+4-10/2;

-- Oracle (19c & 21C)	
SELECT 8*2+4-10/2
FROM DUAL;

SELECT <column1> * <column2> AS <column3>
FROM   <table>;

Select the product ID, quantity and price from OrderItems. Add a column that multiplies quantity by price and name it 'TotaalPrijs'. Sort by TotaalPrijs.

-- Oracle / MySQL / SQL Server	
SELECT   prod_id,
         quantity,
         item_price,
         quantity * item_price AS TotaalPrijs
FROM     OrderItems
ORDER BY TotaalPrijs;

26. Select the product names and prices, and add fields for VAT (21%) and the price including VAT. Sort by product name (9)

27. Select the customer name and add a column combining the state abbreviation and postal code, separated by a space. Name it 'StateZip'. Sort by customer name. (5)

28. Select the vendor names and add a column containing city and country. Name it 'locatie' and sort by the new column. (6)

29. Select all order numbers, quantities and prices. Add a column containing quantity × price and name it 'totaal'. Display only rows with a total above $224.50 (9)

30. Select the product ID and price. Add a column with a 10% discount on the original price and name it 'ActiePrijs'. Sort by the new column. (9)