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}

SQL Part 1

SQL Part 1

Learning Outcomes:

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

Skills:

• Modify the structure of the tables
• Add, delete and modify the data in the tables
• Create different views

Relevant past paper:

DSE ICT Elec A(SP-2016): SP 1cd. PP 1ai,iii,ci,iii. 2012 2ei. 2013 3b. 2014 2ab. 2015 2cii, 3di. 2016 2biii, 4ei.

AS CA(2000-2013): 2004 1bc. 2006 6dii. 2007 10ef. 2009 8e. 2010 3, 8c. 2011 10d. 2012 9e. 2013 9d,eii

Table structure related: CREATE, DROP, ALTER

CREATE:

Create a database:

• CREATE DATABASE <database name>;

Create a table:

• CREATE TABLE <table name>
  (<field name 1> <data type 1>, <field name 2> <data type 2>, …);

Create a view AS:

• CREATE VIEW <view name>
  AS SELECT <field name 1>, <field name 2> …
        FROM <table name>
        WHERE <conditions>;

1. Advantage of a view: better access control - database cannot be changed accidentally, data security - hiding confidential information from certain users, convenient to apply information search

Create an index ON:

• CREATE INDEX <index name>
  ON <table name> (<index field>);

1. How it works: works at WHERE clause. If index is created on the fields used in the WHERE clause, it can reduce the searching time. (most commonly used B-tree index, which will reduce the number of records to be examined - instead of examining all the records)
2. What fields to choose: frequently used fields, preferably containing different values
3. Advantage of an index: improves searching speed
4. Disadvantages: overhead on storage(extra storage space), index is rebuilt during insert, delete, update of records - slow down the process

DROP:

Delete a database:

• DROP DATABASE <database name>;

Delete a table:

• DROP TABLE <table name>;

Delete a view:

• DROP VIEW <view name>;

Delete a column:

• ALTER TABLE <table name>
  DROP COLUMN <field name>;

ALTER:

Change the data type of a field:

• ALTER TABLE <table name>
  ALTER COLUMN <field name> <new data type>;

Add a new field:

• ALTER TABLE <table name>
  ADD COLUMN <field name> <data type>;

Delete a field:

• ALTER TABLE <table name>
  DROP COLUMN <field name>;

Table content related: INSERT, DELETE, UPDATE

INSERT INTO: Insert a record

• INSERT INTO <table name>
  (<field name 1>, <field name 2> …)
  VALUES (<value 1>, <value 2> …);

1. Only some fields are filled in.

• INSERT INTO <table name>
  VALUES (<value 1>, <value 2> …);

1. All fields are filled in. The field names are not necessary, but the values should follow the order of the field names.

DELETE FROM: delete records

• DELETE FROM <table name>
  WHERE <conditions>;

UPDATE/SET: update the values of some fields

• UPDATE <table name>
  SET <field name 1> = <new value 1>,
           <field name 2> = <new value 2>,
           …
  WHERE <conditions>;

SUMMARY

Note the difference between changing table structure and table content.

TABLE STRUCTURE vs TABLE CONTENT

CREATE vs INSERT INTO

DROP vs DELETE FROM

ALTER vs UPDATE/SET