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/