CPSC340A - Day 2 Notes

Calculated Columns

Can write expressions to calculate values using the values in other columns
Calculations can be arithmetic expressions
```
SELECT num1 + num2 FROM TableName;
```
Or Mathematics functions
```
SELECT SQRT(num1) FROM TableName;
```

Or String operations

SELECT CONCAT(str1, str2) FROM TableName;

Or date and time arithmetic
```
SELECT date1 - date2 FROM TableName;
```

Or date / time function

SELECT date1 - CURDATE() FROM TableName;

When you subtract two dates, you get a different type, an interval (the number of days)
When you subtract two times, you get an interval, time without the colons
See the documentation for all of the available Operators, Numeric Functions, String Functions, and Date / Time Functions functions

Literal Values

Can also use literal values in the SELECT statement

SELECT num1 + 1 FROM TableName;
SELECT CONCAT(str1, ':', str2) FROM TableName;
SELECT date1 - DATE('YYYY-MM-DD') FROM TableName;
SELECT time1 - TIME('00:00:00') FROM TableName;

Note the date and time strings that can be used to insert into the database do not work
Must use the DATE and TIME functions in conjunction with the strings
If you look in the documentation there are many ways to specify a date or time string
Date and Time Literals
The most common are ‘YYYY-MM-DD’ for dates
And ‘D HH:MM:SS’ for times
Date and time intervals have different literals
Note that
```
SELECT date1 + 10 FROM TableName;
```
Will convert date1 to an int and then add, so the result is an int

To add to a date, need an interval literal

SELECT date1 + INTERVAL 10 DAY TableName;
SELECT time1 + INTERVAL 5 MINUTE FROM TableName;

See the documentation for the DATE_ADD function for a list of all the intervals

Naming Columns

Notice that the columns in the retrieved table are named for you based on the calculation used

You can rename the column to make it more readable

SELECT colname as column_name FROM TableName;
SELECT time1 + 'HH:MM:SS' as 'end time' FROM TableName;

Note the use of quotes if you want a space in the name
Aside from readability, this will be useful in the future when we create views

Casting

Can also convert the type of columns

SELECT CAST(decimal1 AS SIGNED) FROM TableName;

Note that MySQL will allow you to concatenate a number with a string
```
SELECT CONCAT('something', num1) FROM TableName;
```
But not all implementations of SQL support this
To write the most portable code be cautious with types and convert them before using them in an expression
```
SELECT CONCAT('something', CAST(num1 as CHAR(10))) FROM TableName;
```
Refer to documentation for list of types when converting
Cast Functions and Operators

Null

All table cells must have a value
NULL can be used to specify an empty cell
What happens when NULL is used in a calculation
```
SELECT num1 + 1 FROM TableName;
```
For most operations, NULL and anything else becomes NULL