W06 Paper

13 February 2021
BTU-I CIT 225

Data Generation, Conversion, and Manipulation

SQL contains built-in functions which are used to facilitate data generation, conversion, and manipulation.
Some of this functions are included into this paper such as String, Numeric, and Temporal Data

String Data
When working with string data, you will be using one of the following character data types:
CHAR - Holds fixed-length, blank-padded strings. MySQL allows CHAR values up to 255 characters in
length,
VARCHAR - Holds variable-length strings. MySQL permits up to 65,535 characters in a varchar column.
TEXT - Holds very large variable-length strings. MySQL support multiple text types (tiny text, text,
medium text, and long text)

INSERT OPERATOR
The INSERT statement allows you to insert one or more rows into a table. The syntax is as follow:
INSERT INTO table(c1,c2,...)
VALUES (v11,v12,...),(v21,v22,...),...(vnn,vn2,...);

UPDATE OPERATOR
The UPDATE operator is used to update data in a table. It allows you to change the values in one or more
columns of a single row or multiple rows. The syntax is as follow:
UPDATE table_name
SET column_name1 = expr1, column_name2 = expr2, ...
[WHERE condition];

CONCAT() FUNCTION
The CONCAT() function adds two or more expressions together. The syntax is as follow:

CONCAT(expression1, expression2, expression3,...)

DELETE OPERATOR
The DELETE operator is used to delete data from a table. The syntax for DELETE operator is as follow:

DELETE FROM table_name

WHERE condition;
Notice that the WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all
rows in the table.

When we work with strings, we could work with string that returns strings or numbers, such the following:

The LENGTH() function, which returns the number of characters in the string. The syntax is as follow:

LENGTH(string)

The POSITION() function returns the position of a substring within a string. If the substring cannot be found,
the position() function returns 0. The syntax is as follow:

POSITION(substr IN str)

The LOCATE() function, which is similar to the position() function except that it allows an optional third
parameter, which is used to define the search’s start position. The syntax is as follow:

LOCATE(substring, string, start)

The STRCMP() function compares two strings. The syntax is as follow:

STRCMP(string1, string2)

The return values are:

 If string1 = string2, this function returns 0

 If string1 < string2, this function returns -1
 If string1 > string2, this function returns 1

The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. The
syntax is as follow:

REPLACE(string, from_string, new_string)

The SUBSTRING() function extracts a substring from a string (starting at any position). The syntax is as follow:

SUBSTRING(string, start, length)

Numeric Data
MySQL supports all standard SQL numeric data types. These types include the exact numeric data types
(INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL,
and DOUBLE PRECISION). Arithmetic operators can perform arithmetical operations on numeric operands
involved. Arithmetic operators are addition (+), subtraction (-), multiplication (*) and division (/). The syntax is
as follow:

SELECT <Expression>[arithmetic operator]<expression>...

FROM [table_name]
WHERE [expression];
Another Arithmetic operators:

The MOD() function returns the remainder of a number divided by another number. The syntax is as follow:

MOD(x, y)

The POW() function returns the value of a number raised to the power of another number. The syntax is as
follow:

POW(x, y)

The CEIL() and FLOOR() functions are used to round either up or down to the closest integer. The syntax is as
follow:

CEIL(number)

The ROUND() function rounds a number to a specified number of decimal places. The syntax is as follow:

ROUND(number, decimals)

The TRUNCATE() function truncates a number to the specified number of decimal places. The syntax is as
follow:

TRUNCATE(number, decimals)

The SIGN() function returns the sign of a number. This function will return one of the following:
 If number > 0, it returns 1,
 If number = 0, it returns 0,
 If number < 0, it returns -1

The syntax is as follow:

SIGN(number)

Temporal Data
Temporal data is the most involved when it comes to data generation and manipulation. MySQL keeps two
different time zone settings: a global time zone, and a session time zone, you can see both settings via the
following query:
SELECT @@global.time_zone, @@session.time_zone;

you may want to change the time zone setting for your session, which you can do via the following command:
SET time_zone = 'Europe/Zurich';

To look at the time zone data:

SELECT name FROM time_zone_name;

To build a string that the server can interpret as a date, datetime, or time, you need to put the various
components together in the order shown as follow:
If the server is not expecting a datetime value, or if you would like to represent the datetime using a
nondefault format, you will need to tell the server to convert the string to a datetime.

The CAST() function converts a value (of any type) into the specified datatype. The syntax is as follow:
CAST(value AS datatype)

The STR_TO_DATE() function returns a date based on a string and a format. The syntax is as follow:
STR_TO_DATE(string, format)

The DATE_ADD() function adds a time/date interval to a date and then returns the date. The syntax is as
follow:
DATE_ADD(date, INTERVAL value addunit)

The LAST_DAY() function extracts the last day of the month for a given date. The syntax is as follow:
LAST_DAY(date)

The DAYNAME() function returns the weekday name for a given date. The syntax is as follow:
DAYNAME(date)

The EXTRACT() function extracts a part from a given date. The syntax is as follow:
EXTRACT(part FROM ‘date’)

The DATEDIFF() function returns the number of days between two date values. The syntax is as follow:
DATEDIFF(date1, date2)

This is a review of some operators and clauses used to generate, convert and manipulate Data in a database.
There is a lot of additional information to complement each function mentioned in this paper. This additional
information could be viewed in:
 Learning SQL by Alan Beaulieu, 3rd Edition. 2020, ISBN: 9781492057611
 https://www.w3schools.com/sql