Data Extraction

Overview

In this lab, you will work on creating tables (relations) and adding/deleting records to/from the tables. You will learn also how to modify a database by altering the tables (adding/deleting attributes) or even deleting the whole table. When writing the SQL queries, it is good to keep a copy of the queries in a plain text document so you can use them later (if needed).

Moreover, you will work on querying databases using relational algebra and SQL. The exercises in this lab are similar to the examples that you studied during the lecture. The purpose of the lab sessions is to get the hands-on experience that is required for future career.

Note: we will use table and relation exchangeable unless something else is specified.

The exercises in this lab are based on the following schema and the data is available as .csv files, which can be downloaded using this LINK. The database schema is:

Table Manipulation

Creating Tables

Write the SQL query that creates the three tables in the Figure. For now, you don’t need to specify the primary/foreign keys or any additional constraints. Just the data type of each attribute. Insert some records to the tables or import the data in the csv files into the tables.

Modifying Tables

  2. You might have noticed that when importing the csv, some of the values in the tables table are missing or incorrect. Update those records by adding the values from the csv file.

  3. If you have inserted a few records in the goal table before, display the records of the table and check the values in the assist_provider attribute.

  4. Before performing the next tasks, make sure that you have the query for creating the tables that you will work on and you have the queries for inserting the records again.

    1. Delete all the records from the table. Make sure that the table itself is not deleted.

    2. Delete the table and its content. Comment on the differences between the two queries.

Relational Algebra

Write the following queries in relational algebra:

  1. Find the names of the stadiums where the Netherlands national team played a game.

  2. Find the names of the players who scored a goal in all the games when Poland was playing.

  3. Find the name of the top scorer.

Data Extraction with SQL

For SQL, we will have two types of exercises. Type1, you need to interpret and understand the outcomes of an SQL Query. Type2, You will need to write your SQL query to extract specific piece of data.

Running SQL Queries

To run these queries, you need to have the data inserted to the three tables from the csv files.

You can also write more SQL queries on the university database univdb-sqlite.db, which can be downloaded from here. Try to run the queries that we discussed during the lecture.

Q1. SELECT-FROM-WHERE

SELECT teamname, coach FROM teams
WHERE id = 'FRA';

Q2. Using DISTINCT

SELECT DISTINCT player FROM goal;

Q3. Using ALL

SELECT ALL player FROM goal;

Q4. Using named literal attribute

SELECT 'ABC' AS 'V1';

Q5. Using named literal attribute with FROM

SELECT 'ABC' FROM teams;

Q6. Join

SELECT player, stadium FROM goal, game
ON matchid = id;

Q7. Self-join

SELECT g1.matchid, g1.player, g1.teamid, g2.player, g2.teamid FROM goal AS g1, goal AS g2
ON g1.matchid = g2.matchid AND g1.player <> g2.player;

Q7. Left-outer join

SELECT * FROM goal 
LEFT OUTER JOIN game 
ON goal.matchid = game.id;
Writing SQL Queries

In this part, you will write your own SQL queries to perform the following tasks.

  1. Write a query that returns the information of the teams that played at least one game.

  2. Older versions of SQLite support LEFT OUTER JOIN only. How do we rewrite the following query to be able to run on those versions?

    3. SELECT * FROM goal 
RIGHT OUTER JOIN game 
ON goal.matchid = game.id;

  3. Older versions of SQLite support LEFT OUTER JOIN only. How do we rewrite the following query to be able to run on those versions?

    4. SELECT * FROM goal 
FULL OUTER JOIN game 
ON goal.matchid = game.id;

  4. Write a query that finds the names of all players that contain the substring “an” in their names.

  5. Write a query that finds the names of all players that contain the substring “an” in their names. Show the name of the player only once.

  6. Write a query that returns the names of the players from the Netherlands, Spain and Poland who scored at least a goal. Show the name of the player only once.