Table of contents

SQLite

SQLite is a C-language library that implements a small, fast, self-contained SQL database engine.

In order to use SQLite we will use DB Browser for SQLite that you should already have installed if you followed the instructions that we sent by email before the beginning of the course. If you didn't install it, go to the following address SQLite Download page.

The database

In this lab we will use a simplified version of the Sakila database. The database contains fictitious data on a DVD rental store, featuring entities such as films, actors, film-actor relationships, and a central inventory table that connects films, stores, and rentals. Sakila is composed of 16 tables; download it using this link, and use the Open database command of DB Browser for SQLite to load it.

The schema of Sakila database

SCHEMA CREATION

As first step you're asked to enrich the Sakila schema with new tables. We'll use SQL; a reference guide is available at the following address (you can use this reference if you have any doubt during the definition of your queries): https://www.w3schools.com/sql/default.asp (the specific version of SQLite is described here).

Now focus on the structure of the database by learning the instructions for creating and deleting the elements characterizing a relational database (i.e., tables, columns, ...).

The SQL instruction that deletes a table is the DROP TABLE statement:

DROP TABLE IF EXISTS tableName;

while the instruction that creates a table is the CREATE TABLE statement, which has the following syntax:

CREATE TABLE IF NOT EXISTS tableName (
    column1Name dataType columnConstraint DEFAULT defaultValue,
    column2Name dataType columnConstraint DEFAULT defaultValue,
    …
);

Here's a short description of the components of this statement:

1. CREATE TABLE IF NOT EXISTS generates a new table with the name tableName if and only if it does not exists yet.
2. The list of columns in the table is given between parentheses.

Columns are declared through the following elements:

1. columnName, name of the columns (mandatory).
2. dataType, type of the values in the column (mandatory).
3. columnConstraint, list of integrity constraints on the column values (optional).

The following tables lists the types that you can assign a column.

Both the CHAR and VARCHAR (variable character) types are specified with the maximum number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.

Each column can have additional constraints on it, which limits what values can be inserted into that column. This is not a comprehensive list, but will show a few common constraints that you might find useful in the exercises.

CREATE TABLE film_director (
    director_ID varchar(32),
    film_ID smallint(5),
    PRIMARY KEY (director_ID,film_ID),
    FOREIGN KEY (director_ID) REFERENCES director(director_ID)
    FOREIGN KEY (film_ID) REFERENCES film(film_ID)
);

INSERTING DATA

The INSERT INTO statement is used to add one or more rows to a table. You need to specify the target table and, for each row, the list of columns for which you specify a value and the actual values.

INSERT INTO tableName
(column1Name, column2Name, …)
VALUES (value_or_expr_for_column1, value_or_expr_for_column2, …),
       (value_or_expr_for_column1, value_or_expr_for_column2, …),
        … ;

INSERT INTO director
(Director_ID, First_Name, Last_Name)
VALUES ("1234", "Woody", "Allen"),
       ("1234", "Nicolas", "Winding");

INSERT INTO film_director
(Director_ID, Film_ID)
VALUES ("1234", 5);	

INSERT INTO director
(Director_ID, First_Name, Last_Name)
VALUES ("1234", "Woody", "Allen");

QUERYING DATA

A SELECT statement is used to read data from the database. Here, columnName1, columnName2, ... are the column names of the table that you want to select data from.

SELECT columnName1, columnName2, ...
FROM tableName;

Note that the result of a SELECT statement is a table. If you want to select all columns, use the following syntax:

SELECT *
FROM tableName;

The SELECT DISTINCT statement is used to return only distinct (different) values.

If we want to only read a fixed number of rows in the result, we can use the LIMIT clause. The following only returns the first five rows in table actor.

SELECT *
FROM actor
LIMIT 5;

The WHERE clause is used to get a subset of rows satisfying a given condition.

SELECT columnName1, columnName2, ...
FROM tableName
WHERE condition
LIMIT 5;

The following operators can be used in the WHERE clause:

Try to define and execute the following queries:

Up to now, you have been working with a single table, but as we have seen data is spread across different tables following the normalization criteria. Database normalization is useful because it minimizes unnecessary redundancies.

A JOIN operation is used to combine rows coming from two tables, based on common values of specified columns. Usually, the foreign key is used to join two tables, although this might not be always the case,

SELECT column_name(s)
FROM tableName1
JOIN tableName2
ON tableName1.column_name = tableName2.column_name;

Sometimes it is useful to give tables or columns an alias while writing a query. The AS keyword serves this purpose.

SELECT column_name(s)
FROM tableName1 AS t1
JOIN tableName2 AS t2
ON t1.column_name = t2.column_name;

Note that aliases are only visible within the query: the tables in the database maintain the original name.

If you intend to join two tables on the common values of columns that share the same name in both tables, you can use the following syntax:

SELECT column_name(s)
FROM tableName1 AS t1
JOIN tableName2 AS t2
USING (column_name); 

It is also possible give tables an alias without the keyword AS, we will see it in more complex queries.

SELECT column_name(s)
FROM tableName1  t1
JOIN tableName2  t2
ON t1.column_name = t2.column_name;

SQL also supports the use of aggregate expressions (or functions) to summarize information on a group of rows: COUNT, MAX, MIN, SUM, AVG. In addition to aggregating across all the rows, you can apply the aggregate functions to individual groups of data. The GROUP BY statement is often used with aggregate functions to group the result-set by one or more columns. SQL also provides a way to sort your results by a given column in ascending or descending order using the ORDER BY clause.

SELECT AGG_FUNC(column_or_expression) AS aggregate_alias, …
FROM tableName
WHERE constraint_expression
GROUP BY column;

The following query returns the number of customers of each store:

SELECT COUNT(Customer_ID), store_id
FROM Customer
GROUP BY store_id;

Sometimes you need to use more complex join operations. When joining two tables, you have the following possibilities:

1. You want to return only the rows that have a match in both tables. You use then an INNER JOIN (or simply a JOIN). This is the operation that we've been using so far.
2. You want to return all the rows from the left table, even if they don't match any in the right table. You use then a LEFT JOIN.
3. You want to return all the rows from the right table, even if they don't match any in the left table. You use then a RIGHT JOIN.
4. You want to return all the rows from both the left table and the right table, including those that have no match in either direction. You use then a FULL OUTER JOIN (or FULL JOIN).

INSERT INTO film(film_id, title, language_id) 
   values (10001, "Immaginary dummy film", 1);

INSERT INTO film_actor(actor_id,film_id) values (102,10001);

INSERT into inventory values (4582,10001,2,'2019-03-15 07:09:17');