Databases (TDA357, DIT621)

Lecture 3: SQL 2

• Lecture notes: chapter 2
• Book: chapter 2

Last time

Basic SQL

• Creating and deleting database tables
• Inserting, updating and deleting data in tables
• Database queries involving one table
• Primary keys
• Constraints
• Aggregations

Today

More SQL

• More aggregation examples
• Databases and queries involving several tables
  • Set operations (unions, intersections, etc)
  • Cross products and joins
• Foreign keys
• Composite keys
• Views

Continue with the same database example

A database with information about countries

• Countries

  Name	Capital	Area	Population	Continent	Currency
  Denmark	Copenhagen	43094	5484000	EU	DKK
  Estonia	Tallinn	45226	1291170	EU	EUR
  Finland	Helsinki	337030	5244000	EU	EUR
  Norway	Oslo	324220	5009150	EU	NOK
  Sweden	Stockholm	449964	9555893	EU	SEK
  …	…	…	…	…	…

Continue with the same database example

• First schema
  • Countries(name,capital,area,population,continent,currency)
• SQL

  • CREATE TABLE Countries(
      name       TEXT
      capital    TEXT,
      area       FLOAT,
      population INT,
      continent  TEXT,
      currency   TEXT
    );
    

Continue with the same database example

• Improved schema
  • Countries(name,capital,area,population,continent,currency)
• SQL

  • CREATE TABLE Countries(
      name       TEXT    PRIMARY KEY,
      capital    TEXT,
      area       FLOAT   NOT NULL,
      population INT     NOT NULL,
      continent  CHAR(2) NOT NULL,
      currency   CHAR(3)
    );
    

PostgreSQL Live demo

Files

• countries.tsv (with data from www.geonames.org)
• tables.sql
• inserts.sql

Aggregations

• The number of countries and total population on each continent:

• SELECT continent,COUNT(name),SUM(population)
  FROM Countries
  GROUP BY continent;
  

• Aggregation functions like COUNT and SUM are usually used together with GROUP BY
• What if we just want the total number of countries and population of the world, without grouping?

More aggregation examples

Aggregation without using GROUP BY

• How many rows are there in the table?

• SELECT COUNT(*) FROM Countries;
  

• How many countries have a capital?

• SELECT COUNT(capital) FROM Countries;
  

• What is the total number of countries and population?

• SELECT COUNT(name),SUM(population) FROM Countries;
  

• How many big countries are there (population over 50 million)?

• SELECT COUNT(*) FROM Countries WHERE population>50000000;
  

Common currencies

• How many countries use the Euro?

• SELECT COUNT(*) FROM Countries WHERE currency='EUR';
  

• How many countries are the various currencies used in?

• SELECT currency,COUNT(name)
  FROM Countries
  GROUP BY currency;
  

• What if we want only currencies used in more than one country?

Choosing rows based on aggregated values

• Which currencies are used by more than one country?

• SELECT currency,COUNT(name)
  FROM Countries
  GROUP BY currency
  HAVING COUNT(name)>1;
  

• WHERE condition works on individual rows, comes before GROUP BY.
• HAVING condition works on aggregated values, comes after GROUP BY.

Sets and set operations in math

• The idea of sets and the operations on them comes from discrete mathematics.
• Often illustrated with Venn diagrams.
• Examples:
  • If A={1,2,5}, B={1,6}, C={4,7} then
  • Union: A ∪ B = {1,2,5,6}
  • Intersection: A ∩ B = {1}
  • Difference: A \ B = {2,5}

Set operations in relational databases

• A database table can be seen as a set of rows.
• SQL has set operations:
  • UNION
  • INTERSECT
  • EXCEPT
• These can be used to combine query results.

Set operations in relational databases

Places to visit

• We consider both countries and cities to be places.
• Make a list of all places.

•   SELECT name AS place FROM Countries
  UNION
    SELECT capital AS place FROM Countries;
  

Set operations in relational databases

• List the countries where the name of the capital is the same as the name of the country.

• SELECT name FROM Countries WHERE name=capital;
  

• List the names of capitals that is the same as the name of any country.

  •   SELECT name AS place FROM Countries
    INTERSECT
      SELECT capital AS place FROM Countries;
    

Set operations in relational databases

• Make a list of all countries and classify them as small or big.

•   SELECT name,'big' AS size
    FROM Countries
    WHERE population>=50000000
  UNION
    SELECT name,'small' AS size
    FROM Countries
    WHERE population<50000000 ;
  

Databases with more than one table

Table schemas

• Countries(name,capital,area,population,continent,currency) Currencies(code,name,value)
• Currencies

  code	name	value
  SEK	Krona	1.0
  DKK	Krone	1.41
  EUR	Euro	10.54
  GBP	Pound	12.50
  USD	Dollar	9.57
  BTC	Bitcoin	85634.34

Databases with more than one table

SQL

• CREATE TABLE Countries(
    name TEXT PRIMARY KEY,
    capital TEXT,
    ...,                    -- as before
    currency CHAR(3)
  );
  
  CREATE TABLE Currencies(
    code CHAR(3) PRIMARY KEY,
    name TEXT,
    value FLOAT
  );
  

• tables.sql
• inserts.sql

Combining tables

Cartesian product (cross product)

• SELECT Countries.name,code,value
  FROM Countries,Currencies
  WHERE currency=code;
  

• This first generates all combinations of rows from the two tables.
  • 6 + 3 = 9 columns
  • 252 * 6 = 1512 rows
• Then rows that satisfies the condition, currency=code, are chosen.
  • In this example, we get 61 rows.
• Finally, three columns of interest are selected.
  • The result is a table with 3 columns and 61 rows.

Combining tables

Inner Join

• This is equivalent to the query on the previous page:

• SELECT Countries.name,code,value
  FROM Countries JOIN Currencies ON currency=code;
  

• Result contains only matching rows. The same as:

• SELECT Countries.name,code,value
  FROM Countries INNER JOIN Currencies ON currency=code;
  

Combining tables

Outer Join

• Include all currencies (even if there is no country using it):

• SELECT Countries.name,code,value
  FROM Countries RIGHT OUTER JOIN Currencies ON currency=code;
  

• Include all countries (even if the currency is not in the Currencies table)

• SELECT Countries.name,currency,value
  FROM Countries LEFT OUTER JOIN Currencies ON currency=code;
  

• Make sure info from all rows in both tables is included in the result

• SELECT Countries.name,currency,value
  FROM Countries FULL OUTER JOIN Currencies ON currency=code;
  

Combining tables

Natural Join?

• SELECT * FROM Countries NATURAL JOIN Currencies
  

• Joins based on columns with the same name.
• In our example, it is the same as:

  • SELECT * FROM Countries JOIN Currencies USING (name)
    

• which is similar to

  • SELECT * FROM Countries JOIN Currencies
                    ON Countries.name=Currencies.name
    

• Doesn't make sense in our example. Only useful in databases with carefully chosen column names…

Natural Join

An example that makes sense

• Capitals country capical Sweden Stockholm Norway Oslo France Paris

  Currencies country currency Norway NOK Germany EUR Sweden SEK

• Capitals NATURAL JOIN Currencies
  

• country	capital	currency
  Norway	Oslo	NOK
  Sweden	Stockholm	SEK

  • Only countries that appear in both tables (i.e. both the capital and the currency are known).

Natural Join → An example that makes sense

• Capitals NATURAL LEFT OUTER JOIN Currencies
  

• country	capital	currency
  Norway	Oslo	NOK
  Sweden	Stockholm	SEK
  France	Paris

  • All countries, even the if currency is missing.

• Capitals NATURAL RIGHT OUTER JOIN Currencies
  

• country	capital	currency
  Norway	Oslo	NOK
  Sweden	Stockholm	SEK
  Germany		EUR

  • All currencies, even if the capital is missing.

Natural Join → An example that makes sense

• Capitals NATURAL FULL OUTER JOIN Currencies
  

• country	capital	currency
  Norway	Oslo	NOK
  Sweden	Stockholm	SEK
  France	Paris
  Germany		EUR

  • All rows from both tables are included.
• SQL code for these example: natural.sql

Foreign keys

• Currencies(code,value) Countries(name,capital,area,population,continent,currency) currency -> Currencies.code

• CREATE TABLE Countries(
    name TEXT PRIMARY KEY,
    capital TEXT,
    area FLOAT NOT NULL,
    ...,
    currency CHAR(3) REFERENCES Currencies(code)
  );
  

• Only currency codes from the Currencies table are allowed in the Countries table.
• This makes sense if the intention is that the Currencies table defines the set of valid currency codes.

Inline constraints vs other constraints

• We have only seen inline constraints so far.
• Constraints can also be placed after the column definitions.

• CREATE TABLE Countries(
    name       TEXT,
    capital    TEXT,
    area       FLOAT
    ...,
    currency   CHAR(3),
    
    PRIMARY KEY (name),
    NOT NULL (area),
    FOREIGN KEY (currency) REFERENCES Currencies(code)
  );
  

• Why would you want to do it like this?

Composite keys

• A primary key or foreign key can be a combination of two or more attributes.
• Example: if we assume that the names of cities are unique within a country, but not globally:
  • Cities(name,country,...)
Countries(name,capital,...)
  (capital,name) -> Cities(name,country)

• CREATE TABLE Cities(
    name TEXT,
    country TEXT,
    ...,
    PRIMARY KEY (name,country) );
  
  CREATE TABLE Countries(
    name TEXT PRIMARY KEY
    capital TEXT,
    ...,
    FOREIGN KEY (capital,name) REFERENCES Cities(name,country) );
  

Views

• CREATE VIEW ExchangeRates AS
    (SELECT name,code,value
     FROM Countries JOIN Currencies ON currency=code);
  

• Gives a name to a query.
• Can use the name as a table in other queries.
• Useful e.g. when information often used together is split up into several tables.

SQL Summary

New things compared to last time

• SELECT T1.a,T2.a   -- table prefix on attribute names
  FROM T1,T2,T3      -- many tables = cross products
  WHERE condition
  GROUP BY value
  HAVING value       -- conditions on aggregated values
  ORDER BY value
  

• query UNION/INTERSECT/EXCEPT query
  

• CREATE TABLE T(
    attr type REFERENCES Table(attr)
  
    PRIMARY KEY (a,b)
    FOREIGN KEY (a,b) REFERENCES Table(c,d)
  )
  

• CREATE VIEW View_name AS ( query )
  

SQL Summary

Joins

• table ::= Table_name  table AS Table_name  table NATURAL join table  table join table how
• how ::= USING (a,b,c) ON condition  join::= INNER? JOIN  [FULL|LEFT|RIGHT] OUTER? JOIN

Joins vs cross products

• These are the same:

• FROM T1,T2 WHERE condition
  

• FROM T1 JOIN T2 ON condition
  

• FROM T1 INNER JOIN T2 ON condition
  

Local queries

• Which currencies are used by more than one country?

• SELECT currency,COUNT(name)
  FROM Countries
  GROUP BY currency
  HAVING count(name)>1;
  

• It is nice to have HAVING but we could live without it:

• WITH CurrencyCounts AS
    (SELECT currency,COUNT(name) AS count
     FROM Countries
     GROUP BY currency)
  SELECT *
  FROM CurrencyCounts
  WHERE count>1;
  

Nested queries

• Which currencies in Countries are missing in Currencies?

  • SELECT DISTINCT currency AS code
     FROM Countries
     WHERE currency NOT IN (SELECT code FROM Currencies);
    

• Illustrates
  • Using a set membership test in the condition of a WHERE part.
• condition ::= value NOT? IN (query)

Nested queries

• Copy all missing currency codes to Currencies:

• INSERT INTO Currencies
     (SELECT DISTINCT currency AS code
      FROM Countries
      WHERE currency NOT IN (SELECT code FROM Currencies)
     );
  

• Illustrates
  • A variant of INSERT INTO where the values are taken from a query result.
  • We provide values for the code column. The other columns (name,value) will have missing values (NULL).

Alter table

• When all currency codes are present in Currencies, we can add a foreign key to Countires

• ALTER TABLE Countries
     ADD FOREIGN KEY (currency) REFERENCES Currencies(code);
  

That's all for today

Questions?

Wednesday Exercise: SQL

Thursday Lecture: Entity-Relationship modelling