SQL Part 4

SQL Part 4 – Query involving single table only

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 involved include:

• View, sort, and select contents by filtering, and create different view
• Use appropriate operators and expressions such as arithmetic operators and expressions, comparison operators, logical operators and the in, between and like operators. to perform specific operations
• Perform sub-queries (for one sub-level only)

Relevant past paper:

DSE ICT Elec A(SP-2016): SP 3bde. PP 1cii, 3ab. 2012 1bi,ii,ciii,3bi. 2013 1a, 4dii,e. 2014 1a, 2c. 2015 1bcd. 2016 1abc, 2a,bii.

AS CA(2000-2013): 2000 10ai,iii. 2001 10ai. 2002 4a, 7a. 2003 8abd. 2004 10bde. 2005 8abcef. 2006 9ab. 2007 10abd. 2008 8ab. 2009 8abd. 2010 5aii,b, 8ab. 2011 7b, 10bc. 2012 9bf. 2013 9abc.

Basic format of a query:

• SELECT <field name 1>, <field name 2>…
  FROM <table name>
  WHERE <conditions>; 

1. SELECT * à all the fields would be selected, * means all
2. SELECT <field name 1> AS <display name 1> à the output would show your display name(which would be more meaningful, we call this alias 別名) instead of the field name
3. WHERE clause is optional. Do not use aggregate function in WHERE clause

WHERE <conditions>

                Arithmetic operators: +, -, *, /

                Comparison operators: >, <, =, >=, <=, <>

                Logical operators: AND, NOT, OR

• NOT has highest precedence, followed by AND, and then OR
• e.g. A OR B AND C à work the same as A OR (B AND C). So, if your intention is (A OR B) AND C, the parentheses () must be present

                

                IN, BETWEEN, LIKE operators:

• IN:
  WHERE <field name> IN (“A”, “B”, “C”);
  If there is “A”, “B” or “C” in the field, that particular record would be selected. This operator is commonly used in subquery which is in following section.
• BETWEEN:
  WHERE <field name> BETWEEN “A01” AND “A10”;
  If the value of the field is between “A01” and “A10”, inclusively, that particular record would be selected.
• LIKE:
  WHERE <field name> LIKE “_BCD%”;
  This one look for the pattern “BCD” in the field.
  _ represents any single character.
  % represents string of any length (including absent of any string).

                Built-in functions related to string data: See SQL part 2

                Built-in functions related to numeric data: See SQL part 2

Order by:

• SELECT <field name 1>, <field name 2>…
  FROM <table name>
  WHERE <conditions>
  ORDER BY <field name 1> ASC, <field name 2> DESC;

The query result will be displayed based on the lexicographical order(the character order in the encoding table) of the selected field. ASC means ascending order. DESC means descending order.

 Group by:

• SELECT <field name 1>, <field name 2>
  FROM <table name>
  WHERE <conditions>
  GROUP BY <field name 1>;

Usually this is used with aggregate function: see SQL part 2.

E.g. You are interested in the total number of the entities in a group.

• SELECT <field name 1>, COUNT(*)
  FROM <table name>
  WHERE <conditions>
  GROUP BY <field name 1>;

 The result would be grouped by field name 1:

<field name 1>	COUNT(*)
A	5
B	10

* Group by…Having:

• SELECT <field name 1>, <field name 2>
  FROM <table name>
  WHERE <condition 1>
  GROUP BY <field name 1>
  HAVING <condition 2>;

Condition 1 provides information on which records should be selected. Aggregate function should not be used here.

Condition 2 provides information on which records should be included in the grouped analysis. This is based on some aggregate functions (see SQL part 2).

e.g. You want to find out the students who have borrowed more than 100 books, and their corresponding number of books borrowed in 2019.

Each record represents 1 borrowed item. You would not know which student has borrowed more than 100 books in year 2019 unless you really count the number for each student. By using the “group by” function, you can count the number of books borrowed by each student. Yet, you are only interested in those who have borrowed more than 100 books. So you can select those by using the “having clause” which uses aggregate function.

• SELECT StuID, COUNT(*)
  FROM Borrow
  WHERE YEAR(Dateofborrow) = 2019
  GROUP BY StuID
  HAVING COUNT(*) > 100;

Related past paper: DSE Elec A: SP 3c. AS CA: 2005 8fi. 2011 10c.

* Sub-query:

This is essentially a query nested in another query. Both queries can involve the same table or different tables.

• SELECT <field name 1>
  FROM <table name>
  WHERE <field name 2> IN (SELECT…FROM…WHERE…);

Suppose the subquery return the result A, B, C. Those records would be selected when <field name 2> =A, =B or =C.

Related past paper: DSE Elec A: SP 3c. 2013 4e. AS CA: 2003 8bd. 2004 10d. 2005 8fii. 2012 7b. 2013 9c.

• SELECT <field name 1>
  FROM <table name>
  WHERE <field name 2> NOT IN (SELECT…FROM…WHERE…);

Suppose the subquery return the result A, B, C. Those records would NOT be selected when <field name 2> =A, =B or =C. I.e. All records would be selected except those with <field name 2> =A, =B or =C. 
* Tips: For those questions, the negative words “not” or “never” usually appear in the exam question.

Related past paper: DSE Elec A: SP 3be. 20121ciii. 2015 1d. 2016 1c. AS CA: 2004 10be. 2005 8e. 2007 10d. 2009 8d. 2012 9f.

SQL Part 3

SQL Part 3 – Integrity Constraints

Learning Outcomes:

Describe the basic concepts of relational databases.

Key (such as primary key, foreign key and candidate key), and Integrity (such as entity integrity, referential integrity and domain integrity).

Relevant past paper:

DSE ICT Elec A(SP-2016): SP 1abf. PP 1aii,b,2e,4aiii. 2012 1a,ci. 2013 2a,bi,d. 2014 1b, 2d, 4aiii,c. 2015 3b. 2016 3c, di.

AS CA(2000-2013): 2001 2. 2003 2a. 2004 1a. 2005 6a,cii. 2006 6a,b,c,di. 2007 10g. 2008 5a. 2009 2a. 2011 3ac. 2012 1ab, 9ai. 2013 5b, 9ei.

AL CS 2005 P1 8b.

Key

• A key is: one or more selected attributes used to identify a record.
• A candidate key is: one or more attributes used collectively that can be used to uniquely identify a record in a table. A table can have multiple candidate keys.
• A composite key is: a candidate key. At the same time, it contains 2 or more attributes used collectively.
• A primary key is: the candidate key that is chosen (by database designer) to uniquely identify a record in a table. A table can only have 1 primary key.
• A foreign key is: not a candidate key of a table but is a primary key of another table. It can be a null value.

Commonly, you are required to identify the primary keys and foreign keys of different tables. 

Tips: Very often in 1 of the tables, it contains 2 foreign keys which is used collectively as primary key.

Integrity constraints: Entity integrity, Referential integrity, Domain integrity

Entity integrity – primary key

It ensures the values entered into primary key is unique and not null.

In SQL, it can be defined while creating table.

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

For composite key:

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

Referential integrity – foreign key

It ensures data consistency between coupled table. Having foreign key improves referential integrity so that non-existent key cannot be entered. It helps to uniquely identify a record in another table.

Suppose I have a 2 tables:

ABC:

Tableone (primary key)
003

XYZ:

Tabletwo (primary key)		Tableone
A01		003

1. Correct description: Tableone in table XYZ is a foreign key to table ABC.
2. To delete the record 003 in ABC, the record A01 in XYZ must be deleted first.
   It is because if the record 003 in ABC is to be deleted first, the record A01 in XYZ with Tableone=’003’ cannot refer to the corresponding record in ABC as Tableone in XYZ is a foreign key to table ABC. Record 003 in ABC could not be deleted first as it violates referential integrity.

In SQL, it can be defined while creating table or changing table structure subsequently.

• CREATE TABLE XYZ
  (Tabletwo CHAR(3) PRIMARY KEY,
  …,
  Tableone CHAR(3),
  FOREIGN KEY (Tableone) REFERENCES ABC(Tableone));

• ALTER TABLE XYZ
  ADD FOREIGN KEY (Tableone) REFERENCES ABC(Tableone);

  

Domain integrity – data type, data size

It ensures the new data entered is within the data values allowed. i.e. only certain values are allowed.