SQL part 6 – Interpretation

SQL part 6 – Interpretation
 

• In some questions, you are required to explain the purpose of a SQL statement. These should be answered in the context of the scenario.

Relevant past paper:

DSE ICT Elect A(SP-2016): SP 1e. 2012 1cii,2c,4d. 2013 1ei. 2014 2e. 2015 1e. 2016 2bi.

AS CA(2000-2013): 2004 1d. 2009 8f. 2010 8e. 2011 7a. 2012 1c. 2013 9eiii.

• Some questions require you to make changes to the table. For examples, adding fields, decomposing tables, etc. Need to specify the data type if new fields are created.

Relevant past paper:

DSE ICT Elect A(SP-2016): SP 2e. 2012 3bii. 2014 1c. 2016 1f.

AS CA(2000-2013): 2002 4bii. 2005 6b. 2005 8g.

• You may be required to write the output of a SQL statement.

Relevant past paper:

AS CA(2000-2013): 2010 5ai.

AL CS(2003-2006): 2004 2. 2005 8a.

SQL Part 5

SQL Part 5 – Query involving multiple tables

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:

• Perform queries on multiple tables, including the use of equi-join, natural join and outer join

Relevant past paper:

DSE ICT Elec A(SP-2016): SP 3ac. PP 3cde. 2012 1biii,2b. 2013 1bcdeii,iii. 2014 1aiv. 2015 1a,2ci. 2016 1d.

AS CA(2000-2013): 2000 10b. 2001 10bii,c. 2002 7b. 2003 8ce. 2004 7,10ac. 2005 8d. 2006 9cdef. 2007 10c. 2008 8cde. 2009 8c. 2010 8df. 2011 10ef. 2012 9cde.

Equi-join:

• SELECT *
  FROM <first table>
  INNER JOIN <second table>
  ON <first table>.<field name> = <second table>.<field name>;

    tableone                                                                       tabletwo

A	B	C
001
002
002

D	E	A
1		001
2		001
3		004

• SELECT *
  FROM tableone
  INNER JOIN tabletwo
  ON tableone.A = tabletwo.A;

A	B	C	D	E	A
001			1		001
001			2		001

Natural join:

A type of equi-join

Automatically look for same column-name to join the tables

The column-name appears once only

• SELECT *
  FROM <first table>
  NATURAL JOIN <second table>;

A	B	C	D	E
001			1
001			2

Left outer join:

Result contains all records from the left table

Matching records from right table

• SELECT *
  FROM <first table>
  LEFT OUTER JOIN <second table>
  ON <first table>.<field name> = <second table>.<field name>;

     tableone                                                                     tabletwo

A	B	C
001
002

D	E	A
1		001
2		001
3		003

A	B	C	D	E	A
001			1		001
001			2		001
002			NULL	NULL	NULL

Right outer join:

Result contains all records from the right table

Matching records from left table

• SELECT *
  FROM <first table>
  RIGHT OUTER JOIN <second table>
  ON <first table>.<field name> = <second table>.<field name>;

If you reverse the order of the first and second table, it works like left outer join.

Full outer join:

Result contains all records from both tables

• SELECT *
  FROM <first table>
  FULL OUTER JOIN <second table>
  ON <first table>.<field name> = <second table>.<field name>;

    tableone                                                                     tabletwo

A	B	C
001
002
003

D	E	A
1		001
2		001
3		004

A	B	C	D	E	A
001			1		001
001			2		001
002			NULL	NULL	NULL
003			NULL	NULL	NULL
NULL	NULL	NULL	3		004

You can attempt these past paper using outer join (although it is possible to answer the questions without using outer join):
DSE Elec A: SP 3b. 2016 1cd.
AS CA: 2004 10b. 2005 8e. 2006 9f. 2007 10d. 2009 8d.

Merging query results using UNION, INTERSECT, MINUS

Union:

Combine 2 query results, with duplicated results removed

Suppose the output of 2 individual queries are:

SQL1: A B C

SQL2: A D E

• SQL1
  UNION
  SQL2;

Output: A B C D E

Intersect:

Matching results displayed, without duplication

Suppose the output of 2 individual queries are:

SQL1: A B C

SQL2: A B D

• SQL1
  INTERSECT
  SQL2;

Output: A B

Minus:

All results from first table displayed, except those appeared in second table

Suppose the output of 2 individual queries are:

SQL1: A B C

SQL2: A D E

• SQL1
  MINUS
  SQL2;

Output: B C

*** Correlated subquery:

Example: Find all employees who earn more than average salary in the department

• SELECT name, salary, deptname
  FROM employees E
  WHERE salary > (SELECT AVG(salary)
                                 FROM employees
                                 WHERE deptname = E.deptname);

Inner query:

• SELECT AVG(salary)
  FROM employees
  WHERE deptname = E.deptname

This part finds out the average salary of each department

deptname = E.deptname links up the outer and inner queries

Outer query:

• SELECT name, salary, deptname
  FROM employees E
  WHERE salary > (subquery)

This part finds out the employee who earns more than average salary.
deptname = E.deptname allows the average salary be calculated for each employee from different departments.

E is an alias.

Related past paper: DSE Elec A 2016 1d

Reference:

Correlated subquery: https://www.geeksforgeeks.org/sql-correlated-subqueries/

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.