SQL Part 2

SQL Part 2 – Data Type

Learning Outcomes:

Describe common data types such as integer, real, character, string, Boolean, and date.

Use SQL to maintain a simple relational database, and manipulate its data or retrieve the required information in at most three tables

Skills involved:

Use simple built-in functions such as string functions.

Relevant past paper:

DSE ICT Elec A(SP-2016): PP 4aii. 2012 2a. 2013 3c. 2014 4ai.

AS CA(2000-2013)

Questions are seldom set on data types alone, but they are essential in answering SQL related questions.

Data types

Numeric data type:  e.g. integer, decimal, real

String data type: e.g. character

Boolean: True or False

Date

Numeric data type:

1. INTEGER: integer, maximum 32-bit
2. DECIMAL(precision,scale): e.g. decimal(5,2) can store the decimal number 987.48 where 5 is the maximum number of digit and 2 is the maximum number of decimal digits.
3. REAL: store (extremely close) approximate numeric value. It takes 4-byte storage space which is smaller than that used by decimal. Good to use when the exact value is not as important e.g. scientific calculation.

Built-in functions related to numeric data:

• ABS(<field name>): return the absolute (i.e. positive) value of the selected field (not aggregate function)
• AVG(<field name>): return the average value of the selected field
• MAX(<field name>): return the maximum value of the selected field
• MIN(<field name>): return the minimum value of the selected field
• SUM(<field name>): return the total value of the selected field
• COUNT(<field name>): return the total number of records of the selected field – data type not necessarily of numeric type
• COUNT(*): return the total number of selected records. * means all.
• These are aggregate functions that cannot be used in WHERE clause. 

Character:

1. CHAR(n): character string of length n

Built-in functions related to string data:

• LENGTH(<field name>): return the length of the selected string
• Same as CHAR_LENGTH()
• LOWER(<field name>): return the string in lower case
• TRIM(<field name>): removing the leading and trailing spaces of the string
• SPACE(n): return a string of space with length n.
• SUBSTRING(<field name>, start, length): return the truncated string, starting from the specific position to the specified length
• Same as SUBSTR() and MID()
• UPPER(<field name>): return the string in upper case

BOOLEAN: store TRUE or FALSE value

DATE: store the date. 

Format used in exam is DD/MM/YYYY

To state a condition of between 1/5/2019 and 31/5/2019, we can write: BETWEEN {1/5/2019} AND {31/5/2019}

Date-related functions: 

Suppose the field DOB is created to store the date of birth.

• DAY(DOB): return the day (numeric value)
• MONTH(DOB): return the month (numeric value)
• YEAR(DOB): return the year (numeric value)

Null:

If the field is empty, it is null
To specify this condition, we can use:

WHERE <field name> is NULL;

Put them in use:

Suppose a new table is used to store the student ID, date of birth, whether s/he is a current student and the exam mark.

• CREATE TABLE Example
  (StdID CHAR(4), DOB DATE, Active BOOLEAN, Exam INTEGER);

• INSERT INTO Example
  VALUES (‘0001’, ‘20/12/2005’, TRUE, 90);

Note the presence of ‘ ’ for the character and date data types. 

Character data type is selected for StdID because leading zeros can be retained.

Question:

Suppose you are provided with the date of birth(DOB) of some people. You want to find out those who are younger than 18 years old as at 1/9/2018. How would you state the condition?

DOB > {1/9/2000}