MySQL

We will be using a number of sample databases throughout this module:

• Sakila database, which can be found at http://downloads.mysql.com/docs/sakila-db.zip
• NorthwindDB: http://perso.sinfronteras.ws/index.php/File:NorthwindDB.zip

What is SQL

SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). https://en.wikipedia.org/wiki/SQL

What is a Database management system - DBMS

https://en.wikipedia.org/wiki/Database

A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.

Well-known DBMSs include MySQL, PostgreSQL, MongoDB, Microsoft SQL Server, Oracle, Sybase, SAP HANA, and IBM DB2.

A database is not generally portable across different DBMSs, but different DBMS can interoperate by using standards such as SQL and ODBC or JDBC to allow a single application to work with more than one DBMS. Database management systems are often classified according to the database model that they support; the most popular database systems since the 1980s have all supported the relational model as represented by the SQL language.

The DBMS acronym is sometimes extended to indicate the underlying database model, with RDBMS for the relational, OODBMS for the object (oriented), and ORDBMS for the object-relational model. Other extensions can indicate some other characteristic, such as DDBMS for distributed database management systems.

What is MySQL

MySQL est un système de gestion de bases de données relationnelles (SGBDR). Il fait partie des logiciels de gestion de base de données les plus utilisés au monde. En informatique, un système de gestion de base de données (abr. SGBD) est un logiciel système destiné à stocker et à partager des informations dans une base de données. SQL fait référence au Structured Query Language, le langage de requête utilisé. SQL (sigle de Structured Query Language, en français langage de requête structurée) est un langage informatique normalisé servant à exploiter des bases de données relationnelles.

Installing MySQL

https://www.mysql.com/

https://en.wikipedia.org/wiki/MySQL

Para instalar la última versión disponible en los repositorios de Ubuntu:

sudo apt update
sudo apt-get install mysql-server

Para instalar la última versión (8.0.12): https://www.tecmint.com/install-mysql-8-in-ubuntu/ La instalación la última versión (si ésta es muy reciente) puede generar errores que no han sido aún corregidos y documentados por la comunidad. La última vez, al instalar la versión 8.0.12, no pude ingresar a mysql a través de «mycli» y la instalación de phpMyAdmin generó errores; ninguno de los dos problemas pudieron ser solutionados debido a que no se encontró suficiente documentación en la web. Recomiendo instalar la última versión incluida por defecto en los repositorios de Ubuntu.

Add MySQL Apt Repository:

wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb 

Then install the MySQL repository package using the following dpkg command:

sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb 

Note that in the package installation process, you will be prompted to choose MySQL server version and other components such as cluster, shared client libraries, or the MySQL workbench that you want to configure for installation. MySQL server version mysql-8.0 will be auto-selected, then scroll down to the last option Ok and click [Enter]

sudo apt update
sudo apt-get install mysql-server

Si la versión instalada de la forma anterior no es la deseada, se puede intentar lo siguiente:

sudo apt install mysql-server-5.6
sudo apt install mysql-client-5.6 (He generalmente usado el server. El client realmente no sé para que se usa)

Secure MySQL Server Installation

Run the security script which comes with the binary package by issuing the following command:

sudo mysql_secure_installation

By default, the MySQL installation is unsecure. To secure it, run the security script which comes with the binary package. You will be asked to enter the root password you set during the installation process. Then also choose whether to use the VALIDATE PASSWORD plugin or not. En esta primera pregunta elegí «N» porque creo que este plugin genera problemas al tratar de ingresar a «mysql» con «mycli».

You can also change the root password you set before (as we have done in this example). Then enter yes/y to the following security questions:

• Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
• Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
• Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
• Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

La última vez se generó este error:

... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

La solución se encuentra en este post: https://askubuntu.com/questions/1406395/mysql-root-password-setup-error

Testing and connecting to MySQL

Si se genera este Error: Luego de instalar MySQL, no podía ingresar a través de «mysql -u root -p». Se generaba el error ERROR 1698 (28000): Access denied for user 'root'@'localhost'». La solución se encuentra en https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

Al realizar los pasos sugeridos por el método 1 de la solución indicada en el link de arriba:

• «mysql -u root -p» no funcionó la primera vez.
• Luego intente ajustar de nuevo el password ejecutando «sudo mysql_secure_installation» nuevamente.
• Luego no funcionaba ni siquiera de esta forma: «sudo mysql -u root», lo que estaba funcionando antes.
• Luego intenté con «sudo mysql -u root -p» y funcionó.
• Luego de eso, también funcionó con «mysql -u root -p»

Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status:

systemctl status mysql.service

If MySQL isn't running, you can start it with:

sudo systemctl start MySQL.

For an additional check:

mysqladmin -u root -p version

Connecting to MySQL from the terminal:

mysql -u root -p
mysql -u usuario -p

Uninstall MySQL

https://askubuntu.com/questions/172514/how-do-i-uninstall-mysql

To remove mysql completely from your system Just type in terminal:

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

En el siguiente link se incluyen algunos otros comandos to remove mysql completely from your system: https://linuxscriptshub.com/uninstall-completely-remove-mysql-ubuntu-16-04/

Customizing MySQL prompt

Personalizar (customize) MySQL prompt: http://www.thegeekstuff.com/2010/02/mysql_ps1-6-examples-to-make-your-mysql-prompt-like-angelina-jolie/

Display username, hostname and current database name in the mysql prompt:

export MYSQL_PS1="\u@\h[\d]> "
export MYSQL_PS1="Adelo_Vieira(2017279):\u@\h[\d]> "

O desde el prompt de MySQL:

prompt Adelo_Vieira(2017279):\u@\h[\d]> 
prompt   // Returning to default PROMPT of mysql

Ingresar al prompt MySQL desde la línea de comandos:

mysql -u root -p
O a través de «mycli»:  mycli -u root

Mycli

https://www.mycli.net/

MyCLI is a command line interface for MySQL, MariaDB, and Percona with auto-completion and syntax highlighting.

Installation:

sudo apt-get install mycli

Para ingresar a la línea de comandos de «mycli»:

mycli -u root
mycli -u usuario
mycli -u usuario -p db_name

Some configurations

Display version and other details like socket

mysqladmin -u root -p version

Check what port mysql is running on:

mysqladmin -p -u root variables | egrep port 

or

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';

Configure port and other variables

https://askubuntu.com/questions/407847/how-to-change-mysql-port-number-in-ubuntu

There may be multiple files containing mysql configuration. Their full path may exist in the file:

/etc/mysql/my.cnf 

Lines starting «!includedir» include different paths from where configuration files are taken. For a sample, in my «/etc/mysql/my.cnf» there are:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

The following file contains several configurations, including port:

/etc/mysql/mysql.conf.d/mysqld.cnf

show and create users

mysql> select host, user from mysql.user;

Allow remote connection

https://www.digitalocean.com/community/questions/can-t-connect-to-mysql-server-on-ipaddress-10061

By default MySQL will listen for connections only from the localhost. To enable remote connections like the one used by mysqlworkbench you will need to modify:

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Comment this line:

bind-address = 127.0.0.1

The above line is telling MySQL to only accept local connections (127.0.0.1 / localhost).

If you know the IP that you are trying to remotely connect from, you should enter it here to restrict remote connections to that IP.

If you have to allow all IP addresses to connect, you can comment out the line by adding # before it so it looks like:

After commenting the above line, I got another error saying: Host «MyLaptopIP» Is Not Allowed to Connect to This MySQL Server. The solution is create a new administrator user or modify the root user to allow connections from «MyLaptopIP»:

• https://support.infrasightlabs.com/article/host-is-not-allowed-to-connect-to-this-mysql-server/
• https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server

CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'a*****1';
GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'localhost' WITH GRANT OPTION;
CREATE USER 'usuario'@'%' IDENTIFIED BY 'a*****1';
GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

GUI DB administration tools for MySQL

Visual Studio Code

MySQL Workbench

https://www.mysql.com/products/workbench/

https://en.wikipedia.org/wiki/MySQL_Workbench

MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system.

Para isntalarlo se puede hacer desde los repositorios de Ubuntu:

sudo apt-get install mysql-workbench

También se puede descargar workbench de la página oficial de MySQL and double click the downloaded package; the software center takes over the installation.

La última vez que lo intalé, la versión de los repositorios estaba bastante desactualizada y presentó errores. Recomiendo instalar la última versión descargada de la página oficial.

Other GUI DB administration tools for MySQL

Aparte de PhpMyAdmin, también existen otras aplicaciones de este tipo. Entre las más comunes se encuentran:

• Para Windows: HeidiSQL - MySQL Database Viewer: https://www.heidisql.com/
• Para Ubuntu: Mysql workbench
• Para que estas aplicaciones se conecten a nuestra base de datos MySQL, debemos configurar:
  • Hostanme/IP: 127.0.0.1
  • User: root
  • Port: 3306 (Mysql port)

En HeidiSQL, por ejemplo, para crear una base de datos:

• Unnamed-1 (clic derecho) > Database > Create new > Nombre_database (crear esta base de datos)

• Crear una tabla: Nombre_database (clic d) > create table: Name: nombre_tabla
• Crear un campo en la tabla:
  • Add: nombre_campo (tipo VARCHAR)
  • Clic derecho en nombre_campo > create new index > primary (para hacer que dicho campo sea nuestra primary key)
• Luego podemos ingresar datos a nuestro campo:
  • ...

Some fundamental commands

Listar las bases de datos:

show databases;

Ingresar o cambiar de base de datos:

use nombre_database;

Mostrar la base de datos que se está usando:

select database();

Crear una base de datos:

create database nombre_database;

Borrar una base de datos:

drop database nombre_de_la_base

Listar las tablas en la base de datos:

show tables;

Borrar tablas por separado:

drop table nombre_tabla;
DROP TABLE IF EXISTS authors;

• Use IF EXISTS to prevent an error from occurring for tables that do not exist. Instead of an error, a NOTE is generated for each nonexistent table; these notes can be displayed with SHOW WARNINGS.

Para desplegar los campos de una tabla. Esta orden no muestra los valores de cada campo; sólo los campos y especifica el Tipo, Key y otras descripciones:

describe nombre_tabla; 

Para desplegar todos los campos de una tabla con sus respectivos valores:

select * from nombre_tabla;

Para desplegar algunos campos de una tabla:

select au_fname, au_lname, phone from authors;

Backup and Restore MySQL/MariaDB Databases

Using Command-Line

Backup

The mysqldump command is used to export the contents of a database to a text file.

Ejecutar la siguiente orden en el command line (bash) prompt (NO en la línea de comandos de MySQL):

mysqldump --user=root --password="" nombre_database > nombre_database.sql

OR

mysqldump -u root -p nombre_database > nombre_database.sql

Al parecer, la opción -A permite realizar un backup de todas las bases de datos:

mysqldump -A -u root -p > backup.sql

Restore/Import

The mysql command-line client can be used to import data from a text file into a MySQL/MariaDB database.

1. create a new, empty database to hold your data:

Este paso dependerá del archivo .sql que se quiera importar. A veces la creación de la DB se encuentra incluida en el archivo .sql por lo que no se necesario crear una base de datos antes de importar el archivo.

Ejecutar la siguiente orden en el command line (bash) prompt (NO en la línea de comandos de MySQL):

mysql --user=root --password="" -e "CREATE DATABASE nombre_database"

// OR

mysql -u root -p -e "CREATE DATABASE nombre_database"

2. Import the contents of the backup file into the new database:

mysql --user=root --password="" --database=nombre_database < nombre_database.sql

// OR

mysql -u root -p nombre_database < nombre_database.sql

// Si el archivo .sql incluye la creación de la DB, no es necesario indicar el nombre de la DB:
mysql -u root -p < nombre_database.sql

You can then show the content of the database de esta forma:

mysql --user=root --password="" --database=nombre_database -e "SHOW TABLES"

OR

mysql -u root -p nombre_database -e "SHOW TABLES"

You should now update your application’s configuration and point it to your newly-created database. For example, with the Bitnami WordPress module, you will need to edit the wp-config.php file in the apps\wordpress\htdocs\ subdirectory of your XAMPP installation directory. Within this file, you will need to update the DB_NAME constant to use the new database name.

Example - Importing the Sakila database

http://downloads.mysql.com/docs/sakila-db.zip

Dos archivos son proporcionados para crear esta DB:

sakila-schema.sql
sakila-data.sql

Debemos primero importar el archivos sakila-schema.sql y luego sakila-data.sql:

mysql -u root -p < sakila-schema.sql
mysql -u root -p < sakila-data.sql

Note que en este caso no es necesario crear la DB antes de importar el archivo .sql ni indicar el nombre de la DB en la syntax.

Using phpMyAdmin

Import a CSV file into a MySQL database

https://medium.com/@AviGoom/how-to-import-a-csv-file-into-a-mysql-database-ef8860878a68

Example using File:SP500Companies.csv

DROP TABLE IF EXISTS SP500Companies;
CREATE TABLE SP500Companies
  (
  id          INT          AUTO_INCREMENT,
  Name        VARCHAR(50)  NOT NULL,
  Weight      float,
  Sector      VARCHAR(50),
  Shares_Held float,
  CONSTRAINT pk_SP500Companies PRIMARY KEY (id)
  );

LOAD DATA LOCAL INFILE '/root/SP500Companies.csv'
INTO TABLE SP500Companies
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
IGNORE 1 ROWS (Name, Weight, Sector, Shares_Held);

Es problable: ERROR 1148: The used command is not allowed with this MySQL version. La solución se encuentra explicada aquí: https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version

You must specify that as an additional option when setting up your client connection:

mysql -u myuser -p --local-infile somedatabase

This is because that feature opens a security hole. So you have to enable it in an explicit manner in case you really want to use it.

Both client and server should enable the local-file option. Otherwise it doesn't work.To enable it for files on the server side server add following to the my.cnf configuration file:

loose-local-infile = 1

Reset the MySQL/MariaDB Password

Si ningún password ha sido configurado:

mysqladmin --user=root password "newpassword"

For example, to change the MySQL/MariaDB root password from its default empty value to the password gue55me, you would execute:

mysqladmin --user=root password "gue55me"

If a password has already been previously set and you’d like to change it to a new one, you can use the following syntax:

mysqladmin --user=root --password=oldpassword password "newpassword"

For example, to change the root password from 12345 to gue55me, you would execute:

mysqladmin --user=root --password=12345 password "gue55me"

Databases Authentication and Authorization

Authentication is a mechanism that determines whether a user is, who he or she claims to be.

Create a new user called test and give a password of secret:

CREATE USER 'test'@'localhost' IDENTIFIED BY 'secret';

Las comillas en el user y el host no son obligatorias, por lo menos no en MySQL.

Para listar los usuarios:

SELECT USER FROM mysql.user;

Note: MariaDB /MySQL stores global privileges in the mysql.user system table.

Understand Authorization by granting and revoking of privileges Access control (related to authorization):

Authorization: The granting of a right or privilege, which enables a subject to legitimately have access to a system or a system’s object.

Grant some privileges to your new user. GRANT specifies account privileges:

GRANT SELECT, INSERT, UPDATE ON books.authors TO 'test'@'localhost';

We Grant the privileges to our books database to the authors table.

En este caso, el usuario 'test' es autorizado a SELECT, INSERT and UPDATE sólo en la la tabla 'authors' de la database 'books'. En este caso 'test' ni siquiera podrá ver las otras databases o tables de 'books'. La única database que 'test' puede ver (además de books) es 'information_schema'

Note: INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several readonly tables.

If we want revoke INSERT on 'test' user:

REVOKE INSERT ON books.authors FROM 'test'@'localhost';

Creación de una base de datos de prueba en MySQL

Si queremos crear una base de datos extensa, podemos utilizar la base de datos sakila, la cual se puede descargar de:

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

Para nuestras prácticas, vamos a crear una pequeña base de datos: books.

Esquema de la base de datos books.

Ingresar al prompt MySQL y ejecutar los siguientes comandos:

CREATE DATABASE books;

USE books;

DROP TABLE IF EXISTS authors;
CREATE TABLE authors
  (
  au_id    CHAR(3)     NOT NULL,
  au_fname VARCHAR(15) NOT NULL,
  au_lname VARCHAR(15) NOT NULL,
  phone    VARCHAR(12)         ,
  address  VARCHAR(20)         ,
  city     VARCHAR(15)         ,
  state    CHAR(2)             ,
  zip      CHAR(5)             ,
  CONSTRAINT pk_authors PRIMARY KEY (au_id)
  );
INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223','75 West 205 St','Bronx','NY','10468');
INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020','2922 Baseline Rd','Boulder','CO','80303');
INSERT INTO authors VALUES('A03','Hallie','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A04','Klee','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A05','Christian','Kells','212-771-4680','114 Horatio St','New York','NY','10014');
INSERT INTO authors VALUES('A06','','Kellsey','650-836-7128','390 Serra Mall','Palo Alto','CA','94305');
INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752','1442 Main St','Sarasota','FL','34236');


DROP TABLE IF EXISTS publishers;
CREATE TABLE publishers
  (
  pub_id   CHAR(3)     NOT NULL,
  pub_name VARCHAR(20) NOT NULL,
  city     VARCHAR(15) NOT NULL,
  state    CHAR(2)             ,
  country  VARCHAR(15) NOT NULL,
  CONSTRAINT pk_publishers PRIMARY KEY (pub_id)
  );
INSERT INTO publishers VALUES('P01','Abatis Publishers','New York','NY','USA');
INSERT INTO publishers VALUES('P02','Core Dump Books','San Francisco','CA','USA');
INSERT INTO publishers VALUES('P03','Schadenfreude Press','Hamburg',NULL,'Germany');
INSERT INTO publishers VALUES('P04','Tenterhooks Press','Berkeley','CA','USA');


DROP TABLE IF EXISTS titles;
CREATE TABLE titles
  (
  title_id   CHAR(3)      NOT NULL,
  title_name VARCHAR(40)  NOT NULL,
  type       VARCHAR(10)          ,
  pub_id     CHAR(3)      NOT NULL,
  pages      INTEGER              ,
  price      DECIMAL(5,2)         ,
  sales      INTEGER              ,
  pubdate    DATE                 ,
  contract   SMALLINT     NOT NULL,
  CONSTRAINT pk_titles PRIMARY KEY (title_id)
  );
INSERT INTO titles VALUES('T01','1977!','history','P01',107,21.99,566,'2000-08-01',1);
INSERT INTO titles VALUES('T02','200 Years of German Humor','history','P03',14,19.95,9566,'1998-04-01',1);
INSERT INTO titles VALUES('T03','Ask Your System Administrator','computer','P02',1226,39.95,25667,'2000-09-01',1);
INSERT INTO titles VALUES('T04','But I Did It Unconsciously','psychology','P04',510,12.99,13001,'1999-05-31',1);
INSERT INTO titles VALUES('T05','Exchange of Platitudes','psychology','P04',201,6.95,201440,'2001-01-01',1);
INSERT INTO titles VALUES('T06','How About Never?','biography','P01',473,19.95,11320,'2000-07-31',1);
INSERT INTO titles VALUES('T07','I Blame My Mother','biography','P03',333,23.95,1500200,'1999-10-01',1);
INSERT INTO titles VALUES('T08','Just Wait Until After School','children','P04',86,10.00,4095,'2001-06-01',1);
INSERT INTO titles VALUES('T09','Kiss My Boo-Boo','children','P04',22,13.95,5000,'2002-05-31',1);
INSERT INTO titles VALUES('T10','Not Without My Faberge Egg','biography','P01',NULL,NULL,NULL,NULL,0);
INSERT INTO titles VALUES('T11','Perhaps It''s a Glandular Problem','psychology','P04',826,7.99,94123,'2000-11-30',1);
INSERT INTO titles VALUES('T12','Spontaneous, Not Annoying','biography','P01',507,12.99,100001,'2000-08-31',1);
INSERT INTO titles VALUES('T13','What Are The Civilian Applications?','history','P03',802,29.99,10467,'1999-05-31',1);


DROP TABLE IF EXISTS title_authors;
CREATE TABLE title_authors
  (
  title_id      CHAR(3)      NOT NULL,
  au_id         CHAR(3)      NOT NULL,
  au_order      SMALLINT     NOT NULL,
  royalty_share DECIMAL(5,2) NOT NULL,
  CONSTRAINT pk_title_authors PRIMARY KEY (title_id, au_id)
  );
INSERT INTO title_authors VALUES('T01','A01',1,1.0);
INSERT INTO title_authors VALUES('T02','A01',1,1.0);
INSERT INTO title_authors VALUES('T03','A05',1,1.0);
INSERT INTO title_authors VALUES('T04','A03',1,0.6);
INSERT INTO title_authors VALUES('T04','A04',2,0.4);
INSERT INTO title_authors VALUES('T05','A04',1,1.0);
INSERT INTO title_authors VALUES('T06','A02',1,1.0);
INSERT INTO title_authors VALUES('T07','A02',1,0.5);
INSERT INTO title_authors VALUES('T07','A04',2,0.5);
INSERT INTO title_authors VALUES('T08','A06',1,1.0);
INSERT INTO title_authors VALUES('T09','A06',1,1.0);
INSERT INTO title_authors VALUES('T10','A02',1,1.0);
INSERT INTO title_authors VALUES('T11','A03',2,0.3);
INSERT INTO title_authors VALUES('T11','A04',3,0.3);
INSERT INTO title_authors VALUES('T11','A06',1,0.4);
INSERT INTO title_authors VALUES('T12','A02',1,1.0);
INSERT INTO title_authors VALUES('T13','A01',1,1.0);


DROP TABLE IF EXISTS royalties;
CREATE TABLE royalties
  (
  title_id     CHAR(3)      NOT NULL,
  advance      DECIMAL(9,2)         ,
  royalty_rate DECIMAL(5,2)         ,
  CONSTRAINT pk_royalties PRIMARY KEY (title_id)
  );
INSERT INTO royalties VALUES('T01',10000,0.05);
INSERT INTO royalties VALUES('T02',1000,0.06);
INSERT INTO royalties VALUES('T03',15000,0.07);
INSERT INTO royalties VALUES('T04',20000,0.08);
INSERT INTO royalties VALUES('T05',100000,0.09);
INSERT INTO royalties VALUES('T06',20000,0.08);
INSERT INTO royalties VALUES('T07',1000000,0.11);
INSERT INTO royalties VALUES('T08',0,0.04);
INSERT INTO royalties VALUES('T09',0,0.05);
INSERT INTO royalties VALUES('T10',NULL,NULL);
INSERT INTO royalties VALUES('T11',100000,0.07);
INSERT INTO royalties VALUES('T12',50000,0.09);
INSERT INTO royalties VALUES('T13',20000,0.06);

SQL Practice Lab 1

• WHERE - OR - AND - DISTINCT - IN - LIKE
• AS: Usado para hacer aliases (renombrar una columna): select f_name as 'First name' from ...

All unique book types in the database:

select distinct type from titles;

List the full name, phone number, zip code of authors who live in New York or San Francisco:

select au_fname, au_lname, phone, zip from authors where city="New York" or city="San Francisco";

All the publisher names and IDs, who are based in the USA and in New York city:

select pub_name as 'Publisher name', pub_id as 'Publisher ID' from publishers where country='USA' and city='New York';

// or

select pub_name, pub_id from publishers where country in ('USA') and city in ('New York');

All the title IDs where the advance was between 50000 and 100000:

select title_id, advance from royalties where advance >= 50000 and advance <= 100000;

All the book title IDs, and book names where the type is either history or children (Using a set Operator):

select title_id, title_name from titles where type="history" or type="children";

// or

select title_id, title_name from titles where type in ("history","children");

List all authors who live at an address that starts with the number 14:

select * from authors where address LIKE "14%";

List all the book titles that contain a word that is 4 letters long, and starts with «w» and ends with «t»:

El profesor colocó que la respuesta es:

select title_name from titles where title_name like '%w__t%';

Yo creo que esa respuesta no es completamente válida pues incluirá los títulos que tengan una palabra que contenga 'w__t' sin que necesariamente sea la primera y última letra de la palabra. Es decir una palabra de más de 4 lettres long que contenga el patrón 'w__t'.

La respuesta que creo debe acercarse más a la adecuada es:

select title_name from titles
where title_name like 'w__t %'
or title_name like '% w__t %'
or title_name like '% w__t'
or title_name like 'w__t';

En estos casos MySQL no hace distinción entre mayúsculas o minúsculas.

SQL Practice Lab 2

Fecha y hora

Para crear un campo de fecha y hora que se cree con la fecha y hora actual:

CREATE TABLE fechas(
  id    INT(10)       PRIMARY KEY AUTO_INCREMENT,
  date  TIMESTAMP     NOT NULL   DEFAULT  CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO fechas() VALUES();

Si queremos que el campo date pueda ser nulo debemos usar «DATETIME» en vez de «TIMESTAMP»

NOTE: MySQL retrieves and displays DATE values in 'YYYY-MM-DD'

Lo anterior creará el campo con el siguiente formato:

2017-12-01 00:33:25

Ahora, si queremos seleccionar las fechas con un formato distinto:

select DATE_FORMAT(date,'%d-%m-%y')  AS date from fechas;

select DATE_FORMAT(date,'%d-%m-%Y')  AS date from fechas;

select DATE_FORMAT(date,'%D %M, %Y') AS date from fechas;

The Name of the book published on the 1st August 2000?:

select title_name as 'Titles of books published on the 1st August 2000'
from titles
where pubdate='2000-08-01';

Ordering and pattern Matching

• ORDER: Ordenar alfabética o numéricamente: ASC (por defecto) , DESC

List the type, pages (i.e. page size) and title name of all book titles, alphabetically ordered by type:

select type as 'Type', pages as 'Page size', title_name as 'Title name' 
from titles 
order by type;

The type, page size, price and title name of all book titles, alphabetically ordered by type and increasing price:

select type as 'Type', pages as 'Page size', price as 'Price', title_name as 'Title name' 
from titles 
order by type, price;

Aggregate Functions

• COUNT: Contar filas: select count(*) from titles where ...;
• MAX / MIN / AVG: select avg(pages) from titles;
• SUM(): Sumar

How many book titles:

select count(title_name) as 'Amount of book titles' from titles;

How many book titles have less than or equal to 200 pages:

select count(*) as 'Books less that or equal to 200 pages' from titles where pages <= 200;

The minimum, maximum, and average page size of a book:

select min(pages) as 'Min pages', max(pages) as 'Max pages', avg(pages) as 'Average pages' from titles;

The total price of all books:

select sum(price) as 'Total price of all books (€)' from titles;

Joins

• INNER JOIN

List all book titles that were published in New York:

select title_name as 'Title'
from titles, publishers
where titles.pub_id=publishers.pub_id and city="New York";

or,

select title_name as 'Title' 
from titles t, publishers p 
where t.pub_id=p.pub_id and city="New York";

or, in the NEW ANSI way:

select title_name as 'Title'
from titles t inner join publishers p
on t.pub_id=p.pub_id and city="New York";

List all publishers who have published children books:

select distinct pub_name as 'Publisher' 
from  publishers p inner join titles t 
on p.pub_id=t.pub_id and type='children';

List all book titles that were published in New York, ordered alphabetically desc:

select title_name as 'Title'
from titles t inner join publishers p
on t.pub_id=p.pub_id and city='New York'
order by title_name desc;

The name of the publisher who published the book titled 1977!:

select pub_name as 'Publisher who published the book «1977!»'
from titles t inner join publishers p on t.pub_id=p.pub_id
where title_name='1977!';

Having and grouping

GROUP BY HAVING

For each author_id: The number of books they have wrote:

select au_id as 'Author ID', count(title_id) as 'Number of books written' 
from title_authors 
group by au_id;

For each author_id (who has wrote more than 2 books) the number of books they have wrote:

select au_id as 'Author ID (authors who wrote more than 2 books)', count(title_id) as 'Number of books they wrote'
from title_authors
group by au_id
having count(title_id) > 2;

For each author_id and author firstname (who has wrote more than 2 books) the number of books they have wrote:

select t.au_id as 'Author ID (authors who wrote more than 2 books)', au_fname as 'Author firstname', count(title_id) as 'Number of books they wrote'
from title_authors t inner join authors a on t.au_id=a.au_id
group by t.au_id
having count(title_id) > 2;

SQL Practice Lab 3

Table creation

Create an «instructors» table with 3 columns: ID, name and salary. The ID is the primary key. Choose appropriate data types:

create table instructors(
id        char(5),
name      varchar(50) not null,
salary    numeric(8,2),
primary key(id)
);

Note que «numeric()» también puede ser introducido como «decimal()»

CREATE TABLE Work_on(
    c_id         INT NOT NULL,
    p_id         INT NOT NULL,
    hours_worked INT,
    CONSTRAINT Work_on_PK          PRIMARY KEY (c_id, p_id),
    CONSTRAINT Work_on_Chemist_FK  FOREIGN KEY (c_id) REFERENCES Chemist (c_id),
    CONSTRAINT Work_on_Projects_FK FOREIGN KEY (p_id) REFERENCES Project (p_id)
);

CREATE TABLE fechas(
  id    INT(10)       PRIMARY KEY AUTO_INCREMENT,
  date  TIMESTAMP     NOT NULL   DEFAULT  CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP
);

Auto increment

Si queremos que el «id» sea introducido automáticamente debemos usar «AUTO_INCREMENT». En este caso el id debe ser de tipo «int». De esta forma, si omitimos el id al ingresar un valor en la table, éste será agregado automáticamente con el valor numérico que corresponda (el siguiente número del mayor valor registrado)

create table instructors(
id        int auto_increment,
name      varchar(50) not null,
salary    numeric(8,2),
primary key(id)
);

Insert values

Insert two rows into the instructors table:

insert into instructors values('1','Kike Dominguez',40000.70);

Si queremos dejar algunos campos vacíos (null), hay dos formas:

insert into instructors values('2','Ernesto Vieira',null);

insert into instructors(id,name) values('3','Wilder Raben');

Update/Changes

Update/Changes the value of the name of one of the instructors:

update instructors set name='Enrique Dominguez' where id='1';

Give all instructors a 5% pay increase:

update instructors set salary=salary*1.05;

Delete rows

Delete one of the rows based on the value of the ID:

delete from instructors where id='3';

Add attributes

Add a new attribute/column (address) to the table:

alter table instructors add address varchar(200);

Chage the name of a attribute/column

En MySQL, para cambiar el nombre de una columna debemos utilizar esta sintaxis. Notar que es necesario especificar el tipo de datos aunque no lo estemos cambiando, en este caso (NUMERIC(8,2)). El NOT NULL no es necesario especificarlo a menos que queramos cambiar esta opción:

alter table instructors change column salary salary_a_year numeric(8,2) not null;

What is SQL PSM - Persistent Stored Module

• SQL PSM is an ISO standard defining procedural language extensions to SQL to allow the creation of stored procedures and has been part of the SQL standard since SQL:1999.
• It provides syntatical elements to defne:
  • Control fow;
  • Exception handling;
  • Local variables;
  • Assignment of expressions to variables and parameters; and
  • Procedural use of cursors.
• DB2 and MySQL’s procedure languages are the closest to the SQL/PSM standard, although the language was heavily infuenced by PL/pgSQL.

Stored Procedures

• A stored procedure (SP) is a set of program statements compiled and stored in the RDBMS.
• Given the required permissions, they are accessible by end users and applications.
• Unlike regular SQL, which is a declarative language, they permit imperative programming styles, using control-flow constructs such as IF and FOR.
• SPs can be nested, where one procedure can call another.
• They support a limited degree of recursion, where a procedure can call itself.
• Each RDBMS supports a different range of programming languages for creating SPs:
  • MySQL: SQL/PSM;
  • Oracle: PL/SQL, Java;
  • ...

A complex Query:

Which employee has the highest sales?

SELECT employeeNumber, CONCAT(lastName,’,’,firstName) AS employeeName, SUM(customerTotal) AS totalSales
FROM employees JOIN
     (SELECT salesRepEmployeeNumber, c.customerNumber, SUM(orderTotal) AS customerTotal
      FROM customers c
      JOIN orders o
      ON c.customerNumber = o.customerNumber
      JOIN
            (SELECT orderNumber, SUM(quantityOrdered * priceEach) AS orderTotal
            FROM orderdetails
            GROUP BY orderNumber) AS details
            ON o.orderNumber = details.orderNumber
            GROUP BY customerNumber) AS salesByCustomer
      ON employeeNumber = salesRepEmployeeNumber
      GROUP BY employeeNumber
      ORDER BY totalSales DESC;

• In order to run this query without a stored procedure, the user would need permissions to access a number of different tables.
• Standard RDBMS permissions are not suficiently fine-grained to allow only these queries.
• In most scenarios we would want specific users to be able to execute only particular queries.
• Using stored procedures, access permissions can be limited to only certain queries.

Generic Syntax

NorthwindDB

DELIMITER //
CREATE PROCEDURE getAllProducts()
BEGIN 
	SELECT *
    FROM   Products;
END //
DELIMITER ;

Calling this procedure is simple:

CALL getAllProducts();

Parameters

• A stored procedure can take zero or more parameters and each can be one of three types of parameter:
  • IN parameters. Used to pass values from the caller to the procedure. These need to be passed in with the CALL statement. Any changes made to the parameter as part of the script are not passed back to the caller. This is the default if no type is specifed.
  • OUT parameters. Used to pass values from the procedure to the caller. The caller will need to declare and provide a suitable variable.
  • INOUT parameters. Used to pass values from the caller to the procedure and vice-versa. Any changes made to the parameter during the procedure are available to the caller when the procedure finishes executing.

IN Parameter Example

NorthwindDB

DELIMITER //
CREATE PROCEDURE getEmployeeByID(IN employee_id int)
BEGIN
	SELECT EmployeeID, LastName
    FROM   Employees
    WHERE  EmployeeID = employee_id;
END //
DELIMITER ;

CALL getEmployeeByID(7);

Como siempre, en este tipo de operaciones (EmployeeID = employee_id) el nombre del parameter tiene que ser distinto al nombre del Attribute. Es muy importante notar que SQL no hace distinción entre Mayúsculas y Minúsculas. Por tanto, definir la operación de arriba de la siguiente forma: EmployeeID = employeeid sería incorrecto.

Ejemplo con otra DB

DELIMITER //
CREATE PROCEDURE GetSalesByCountry (IN countryid int)
BEGIN
     SELECT transaction_id,country_id, trans_date_time,product_id,quantity,amount
     FROM sales
     WHERE country_id = countryid;
END//
DELIMITER ;

CALL GetSalesByCountry(353); 10/30

OUT Parameter Example

NorthwindDB

DELIMITER //
CREATE PROCEDURE GetTotalPrice(OUT totalprice decimal(19,4))
BEGIN
     SELECT SUM(UnitPrice) INTO totalprice
     FROM Products;
END //
DELIMITER ;

«decimal(19,4)» is the Type of variable. You can check it with DESCRIBE Products;

CALL GetTotalPrice(@totalprice);
SELECT @totalprice AS Total_price;

NorthwindDB

DELIMITER //
CREATE PROCEDURE GetTotalPriceUntilID(IN product_id int, OUT totalprice decimal(19,4))
BEGIN
     SELECT SUM(UnitPrice) INTO totalprice
     FROM Products
     WHERE ProductID < product_id;
END //
DELIMITER ;

CALL GetTotalPriceUntilID(6, @totalprice);
SELECT @totalprice AS Total_price;

INOUT Parameter Example

No DB involved in this operation

DROP PROCEDURE AddSalesTax;
DELIMITER //
CREATE PROCEDURE AddSalesTax(INOUT sales_amount DECIMAL(8,2), IN tax_percentage double)
BEGIN
     SET sales_amount = sales_amount + (sales_amount * tax_percentage);
END//
DELIMITER ;

SET @salesamount = 100.11;
CALL AddSalesTax(@salesamount, 50.20);
SELECT @salesamount;

Variables

• Variables are used to temporarily hold values for processing during the execution of a stored procedure.
• They are created using the DECLARE keyword and can be assigned a default value using the DEFAULT keyword.
• With the exception of user-defined variables (those starting with a @), variables must be assigned a type, such as VARCHAR(10), INT.
• Values can be assigned to variables either using:
  • SET: for assigning a variable a literal value or the value of another variable (or itself)
  • SELECT . . . INTO: to assign the results of a SELECT statement to the variable.

An example of the use of variables:

DELIMITER //
CREATE PROCEDURE GetTotalProductCount()
BEGIN
     DECLARE totalcount INT DEFAULT 0 ;
     SELECT COUNT (DISTINCT productCode)
     FROM current_products
     INTO totalcount;
     SELECT COUNT (DISTINCT productCode) + totalcount AS totalproductcount
     FROM archived_products;
END //
DELIMITER;

Control Flow

The IF statement

IF condition THEN
     ...statements...
ELSEIF condition THEN
     ...statements...
ELSE
     ...statements...
END IF;

The IF ELSE statement

IF condition THEN
     ...statements...
ELSE
     ...statements...
END IF;

The IF ELSEIF ELSE statement

IF condition THEN
     ...statements...
ELSEIF condition THEN
     ...statements...
ELSE
     ...statements...
END IF;

The CASE statement

• The CASE statement is an alternative to IF statments with multiple ELSEIF clauses.
• Code written with the CASE statement is more readble and also gives a slight increase in performance over the IF. . . ELSEIF alternative.
• The CASE statement comes in two forms, "simple" and "searched"

The Simple CASE statement:

The simple CASE statement checks for a match against one or more non-conditional expressions. The ELSE clause is optional but will raise an error if no match is found

CASE expression
     WHEN expression_a THEN statements;
     WHEN expression_b THEN statements;
     ...
     ELSE statements;
END CASE;

CASE shippingType
     WHEN 'Express' THEN SET shipping_time = 1;
     WHEN 'Normal' THEN SET shipping_time = 3;
     ELSE SET shipping_time = 5;
END CASE;

The Searched CASE statement: The searched CASE statement allows the use of checks for a match against one or more conditional expressions. The ELSE clause is optional but will raise an error if no match is found.

CASE
     WHEN condition_a THEN statements;
     WHEN condition_b THEN statements;
     ...
     ELSE statements;
END CASE;

CASE
     WHEN (num_orders > 100 AND total_value > 100000) THEN SET cust_category = 'Premium';
     WHEN (num_orders > 50 OR total_value > 50000) THEN SET cust_category = 'Advantage';
     ELSE SET customer_category = 'Standard';
END CASE;

The WHILE statement

WHILE condition DO
     ...statements...
END WHILE;
WHILE a < 20 DO
     a = a + 1;
END WHILE;

The REPEAT statement

REPEAT
     ...statements...
UNTIL condition;

REPEAT
     a = a + 1;
UNTIL a = 20;

The LOOP statement

label : LOOP
...statements...
IF condition THEN LEAVE label;
END IF;
...statements...
END LOOP label;

Stored Functions

• A stored function is stored routine that returns a single value.
• It is not called like a stored procedure, but is used as part of an expression inside SQL queries or stored procedures.
• Stored functions take only IN parameters and must specify a return data type (INT, VARCHAR, etc.)
• The function must include at least one RETURN statement
• If the function is deterministic (produces identical output for any given input) then this must be declared.

CREATE FUNCTION function_name(param1,param2,...) RETURNS datatype [NOT] DETERMINISTIC
BEGIN
     statements
END

DELIMITER //
CREATE FUNCTION inverse(value DOUBLE) RETURNS DOUBLE DETERMINISTIC
BEGIN
     RETURN 1/value;
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION customerLevel(creditLimit DOUBLE) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
     DECLARE level VARCHAR(10);
     IF creditLimit > 50000 THEN SET level='PLATINUM';
     ELSEIF (creditLimit <=50000 AND creditLimit >=10000) THEN SET level='GOLD';
     ELSEIF creditLimit <10000 THEN SET level=’SILVER’;
END IF;
RETURN(lvl);
END //
DELIMITER ;

Triggers

• Triggers are a special kind of stored procedure that executes automatically when certain operations are carried out on table.
• Depending on the RDBMS in use they can be defined to run before, after or instead of DML (Data Manipulation Language) operations such as INSERT, UPDATE, and DELETE.
• MySQL does not have an "instead of" option
• They are useful for implementing complex integrity checks as well as auditing.
• Although useful, they add some overhead to the RDBMS

Generic syntax for creating a trigger in MySQL:

CREATE TRIGGER «triggername» {BEFORE|AFTER} {INSERT|UPDATE|DELETE }
ON tablename
FOR EACH ROW {FOLLOWS|PRECEDES} «othertriggername»
BEGIN
END;

• On creation, the trigger becomes associated with the table named tablename, which must refer to a permanent table.
• Triggers can not be created for a TEMPORARY table or a view.
• The optional FOLLOWS or PRECEDES «othertriggername» allows triggers to be fired in a specific sequence.

Creating triggers:

Although not required, it is good practice to follow a naming convention such as:

time_tablename_operation (for example: before_customer_update)
tablename_time_operation (for example: customer_before_update)

• Inside the body of the trigger, the keyword OLD is used to refer to the row (tuple) before it is updated or deleted and NEW is used to refer to the row that will be inserted or an existing row after it is updated.

• Triggers on INSERT operations can only use the NEW values, DELETE only to the OLD values, and UPDATE can use both.

Trigger Example:

DELIMITER \\
CREATE TRIGGER after_payroll_update AFTER INSERT ON payroll FOR EACH ROW
BEGIN
INSERT INTO payroll_audit SET
     operation       = 'INSERT',
     employeeNumber  = OLD.employeeNumber,
     gross_pay       = OLD.gross_pay,
     tax             = OLD.tax,
     prsi            = OLD.prsi,
     net_pay         = OLD.net_pay,
     user            = CURRENT_USER(),
     audit_timestamp = NOW();
END \\
DELIMITER ;

Triggers Exercise:

Using the Sakila database, create a trigger that sets the "Active" column of the customer record to True when they rent a video, if and only if the record is no longer false.

USE sakila;
DELIMITER \\
CREATE TRIGGER SetCustomerActive AFTER INSERT ON rental
FOR EACH ROW 
BEGIN
  UPDATE customer 
  SET active = TRUE 
  WHERE customer_id = NEW.customer_id;
END \\
DELIMITER ;

Events

An event is a special kind of trigger that executes according to a schedule rather than when a particular operation is carried out on a table. As such they can be considered time-based triggers.

Events require that the event scheduler thread is enabled. This can be done using the following code:

SET GLOBAL event_scheduler = ON;

To verify that the event scheduler is running, use the following command:

SHOW PROCESSLIST;

Generic syntax for creating an event in MySQL:

CREATE EVENT eventname
ON SCHEDULE eventschedule DO
eventbody;

CREATE EVENT IF NOT EXISTS test_event
ON SCHEDULE AT CURRENT_TIMESTAMP DO
INSERT INTO messages(message,created at)
VALUES('Test MySQL Event 1',NOW());

The eventschedule clause takes one of two forms, depending whether the event is a 'one-shot' or repeated. See the following slides for details.

Event Schedules

One shot

These examples assume the existence of a table created as follows:

CREATE TABLE IF NOT EXISTS messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL
);

The AT timestamp syntax is used for a one-time event. In this case the event will execute once at the date and time given by timestamp:

CREATE EVENT IF NOT EXISTS test_event2 ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
INSERT INTO messages(message,created at)
VALUES(’Test MySQL Event 2’,NOW());

Repeated

To have an event repeat at a regular interval, the EVERY interval syntax is used. The generic form is:

EVERY interval
STARTS timestamp [+ interval] ENDS timestamp [+ interval]

Example:

CREATE EVENT IF NOT EXISTS test_event ON SCHEDULE
EVERY 1 HOUR STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + 1 DAY DO
INSERT INTO messages(message,created at)
VALUES(’Test MySQL Event 3’,NOW());

Events Exercise:

• Using the Sakila database, create an event that runs every night at midnight and returns a list of email addresses for customers who are overdue.

Cursors

A database cursor is a movable pointer to an individual row within a query result. This allows rows to be operated on individually, using the imperative programming style, instead of as a set, using the declarative programming style of standard SQL.

A cursor can be used inside any stored program, such as a stored procedure. Any given stored program may use more than one cursor provided that each is given a unique name.

Cursors in MySQL have the following properties:

• They are read only, in other words, the result set is not updatable
• Cursor read the entire results of their select statement into memory, so should be used with caution
• They are non-scrollable. Cursors can only be traversed in one direction and rows can not be skipped.

Declaring and using cursors

Cursors are declared for a SELECT statement using the syntax below:

DECLARE cursor_name CURSOR
FOR SELECT ...;

In order to use the cursor, it must be opened. This is when the SELECT statement is executed:

OPEN cursor_name;

Rows in the cursor are accessed using the FETCH keyword:

FETCH cursor_name INTO variables;

The FETCH statement will raise a NOT FOUND condition when no more rows are available. In order to handle this, a NOT FOUND handler must be declared as part of the stored program. This usually sets a boolean variable that is used as part of a WHILE loop to a state where the loop terminates:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;

When they are no longer needed, cursors need to be closed to release any associated memory used on the server:

CLOSE cursor_name;

Cursor Example

DELIMITER //
CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
    DECLARE v_done INTEGER DEFAULT 0;
    DECLARE v_email varchar(100) DEFAULT "";
    DECLARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; -- declare NOT FOUND handler
    OPEN email_cursor; -- open the cursor
    FETCH email_cursor INTO v_email;
    WHILE NOT v_done DO -- loop over result set
        SET email_list = CONCAT(v_email,";",email_list);
        FETCH email_cursor INTO v_email;
    END WHILE;
END//
DELIMITER ;

Cursors Exercise

Using the Sakila database, create a stored procedure that uses a cursor to iterate through a query on the the rentals table giving the last rental for each customer and sets the active column in the customer table to false if this date is more than a year ago.

You will need to back up the database before doing this and restore it afterwards, as the procedure changes the data. The query to return the most recent rental for each customer is shown below. Use this as the select statement for your cursor.

SELECT r1.
FROM rental r1
WHERE r1.rental_date = (SELECT MAX(r2.rental_date)
                        FROM rental r2
                        WHERE r2.customer_id = r1.customer_id)

Transactions

A transaction is a collection of one or more database operations that must all either complete successfully or fail entirely.

These typically constitute a single real-world transaction, such as the transfer of money between bank accounts, where if one account is debited, the other must be simultaneously credited.

A transaction is an all or nothing proposition. There are only to possible outcomes for a transaction. Either it is committed and all the requested changes are made to the database or it aborts and no changes are made. This is what is referred to as atomicity.

ACID Properties

In a relational database, all transactions have four fundamental properties:

• Atomicity, all parts of the transaction must either succeed or fail.
• Consistency, the database must be transformed from one consistent state to another.
• Isolation, the effects of incomplete transactions should not be visible to other transactions.
• Durability, the effects of a committed transaction are permanent and should not be lost due to later failure.

Transactions in MySQL

There are four statements in MySQL that provide transaction functionality. The SET TRANSACTION statement is used to specify the properties of transactions.

SET [GLOBAL | SESSION] TRANSACTION
   [REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE]
   [READ WRITE | READ ONLY]

When the GLOBAL option is specified, the statement applies globally for all subsequent sessions. Existing sessions are unaffected. If the SESSION keyword is used, the statement applies to all subsequent transactions performed within the current session.

If neither is specified, the statement applies to the next transaction performed in the current session. Any subsequent transactions will use the SESSION isolation level.

Transaction isolation:

The SQL standard specifies four transaction isolation levels, the first two of which are described below:

• Read Uncommitted - Any transaction may read changes made by other transaction that have not yet been committed, allowing "dirty reads" to take place. Transactions with this level are not isolated from each other.
• Read Committed - A transaction may only read is changes that have been committed by other transactions, disallowing "dirty reads." In order to achieve this, a transaction will place a read or write lock on the row it is currently updating, preventing other transactions from reading, updating or deleting it.
• Repeatable Read - A transaction will place read locks on all rows it references and write locks on all rows it will modify. Other transactions cannot read, update or delete these rows, ensuring all reads are repeatable.
• Serializable - This is the most restrictive level and guarantees that a transaction will retrieve exactly the same data when it repeats a read operation. However, the locking strategy associated with this level can cause problems for other users in multi-user systems.

All four are supported by MySQL when the InnoDB engine is used. The default isolation level is REPEATABLE READ.

Starting a transaction:

Transactions are initiated with the START TRANSACTION command:

START TRANSACTION [WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY]

The WITH CONSISTENT SNAPSHOT option applies only to InnoDB databases and is effectively the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table.

The READ WRITE and READ ONLY options are used to allow or disallow changes to the tables used in the transaction. The READ ONLY option prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions.

Committing and rolling back a transaction:

Assuming a transaction has been started with START TRANSACTION:

• The COMMIT statement will commit all the changes made by the transaction, making its changes permanent.
• The ROLLBACK statement will “roll back”" all of the transaction, cancelling its changes.

Rollbacks can happen for a variety of reasons, either under the control of the database programmer or the system:

• Account balance is not sufficient to complete transfer
• Order quantity is less than the quantity in stock
• Timeout of a transaction or recovery from a system crash

Exception Handling

When an error occurs in a stored procedure, stored function or trigger, it is important that it is handled appropriately, rather than allowing the database engine to return a (cryptic) error message.

MySQL provides straightforward functionality to define handlers that deal with a range of error conditions from the more general, such as warnings or exceptions, to more specific error codes.

Declaring Exception Handlers

Handlers must be declared after variables and cursors, and use the following syntax:

DECLARE <handler action> HANDLER FOR <condition value>
<statement>

Where handler action can be one of:

• CONTINUE - Execution of the current program continues.
• EXIT - Execution terminates for the BEGIN . . . END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.

A further value "UNDO" is currently not supported in MySQL but is intended to execute a rollback.

Where condition value can be one of:

• An integer literal indicating a MySQL error code;
• SQLSTATE value other than those that begin with ‘00’, as these indicate success rather than an error condition;
• SQLWARNING condition - shorthand for any SQLSTATE value that begins with ‘01’; or
• SQLEXCEPTION condition - shorthand any SQLSTATE value that do not begin with '00', '01', or '02'.
• NOT FOUND condition - shorthand for SQLSTATE values that begin with ‘02’; or
• Named condition associated with a MySQL error code or SQLSTATE value.

Examples

If SQLSTATE 23000 (duplicate key value) is encountered, issue an error message for the calling program (using the SELECT statement) and then continue execution:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
     SELECT 'Duplicate key in index';

If MySQL error value is 1216 (FK constraint violated) issue an error message to the calling program and exit.

BEGIN
     DECLARE CONTINUE HANDLER FOR 1216 SELECT 'Foreign key constraint violated';
     INSERT INTO employees(first_name,surname, date_of_birth, department_id)
          VALUES ('Jane','Smith','1997-03-12',12);
END;

Example using Sakila Film table

If the title of a film being inserted into the film table are identical to those that already exist, then return a message to calling program.

DELIMITER $$
CREATE PROCEDURE insert_film(IN title VARCHAR(255) ... <other columns>)
BEGIN
     -- declare a continue handler
     DECLARE CONTINUE HANDLER FOR 1062
     SELECT CONCAT('Warning: Film with title ',title,' already found') AS msg;
     -- insert a new record into film
     INSERT INTO film(title, <other columns>) VALUES(title, <other values>);
END

Exercise:

• Using the Sakila database, add a unique index for film title and year, as shown below. We will revisit indexing in the coming weeks.

ALTER TABLE film ADD CONSTRAINT uk_film_year
UNIQUE KEY (title,release_year);

• Create a stored procedure to add a film to the film table, taking IN parameters for all columns except film_id and last_update.
• In the stored procedure, output a message if the combination of the title and year are not unique.

Assessment

Part I - Procedural SQL - Individual Assessment

Submit your code for this assessment as a single SQL file, naming it with your student number xxxxxxxx.sql, where xxxxxxxx is your student number. This should be submitted to the Individual Assessment link on Moodle.

Due date: 18th November 2018, 23:55 (at the very latest).

Question 1 - Load the NorthWind database using the supplied SQL format backup file

http://perso.sinfronteras.ws/index.php/File:NorthwindDB.zip

mysql -u root -p < /home/adelo/1-system/desktop/it_cct/3-Advanced_Databases/0-1-databases_usadas_en_este_curso/NorthwindDB/NorthwindDB.sql

Question 2 - Create a stored procedure

Using the NorthWind database, create a stored procedure named sp_LowStock to return the following columns (in order) for all products where the stock level is below the reorder level:

• Supplier Name;
• Product Name;
• Stock Deficit (units in stock minus reorder level); and
• Reorder Level

Create SQL statements to execute this procedure and display the results.

NorthwindDB

DELIMITER //
DROP PROCEDURE IF EXISTS sp_LowStock()//
CREATE PROCEDURE sp_LowStock()
BEGIN
	SELECT CompanyName  as "Supplier Name", 
		   ProductName  as "Product Name",
		   UnitsInStock - ReorderLevel as "Stock Deficit",
		   ReorderLevel as "Reorder Level"
	FROM   Products p INNER JOIN Suppliers s
	ON     p.ProductID = s.SupplierID and UnitsInStock < ReorderLevel;
END //
DELIMITER ;

CALL sp_LowStock();

Question 3 - Create a stored procedure

Create a stored procedure called sp_EmployeeTerritories that takes one IN parameter (the Employee ID) and returns the following columns using two OUT parameters:

• Employee Name (joining Title, FirstName and LastName as one column); and
• Territories (a comma-separated list of territories assigned to the employee).

Create SQL statements to execute this procedure for an employee id of your choosing and display the results.

NorthwindDB

DELIMITER //
DROP PROCEDURE IF EXISTS sp_EmployeeTerritories//
CREATE PROCEDURE sp_EmployeeTerritories(
     IN  employee_id      int,
     OUT employee_details char(200),
     OUT terr_assigned    char(200)
)
BEGIN
     SELECT DISTINCT
            CONCAT(Title,", ",FirstName," ", LastName)        AS "Employee",
            GROUP_CONCAT(TerritoryDescription SEPARATOR ", ") AS "Territories assigned"
     INTO   employee_details, terr_assigned
     FROM   Employees e INNER JOIN EmployeeTerritories et INNER JOIN Territories t
     ON     e.EmployeeID  = et.EmployeeID  AND
            t.TerritoryID = et.TerritoryID AND
            e.EmployeeID  = employee_id;
END //
DELIMITER ;

CALL sp_EmployeeTerritories(4, @employee_details, @terr_assigned);
SELECT @employee_details AS "Employee", @terr_assigned AS "Territories assigned";

Question 4 - Create a stored procedure

Create a stored procedure called sp_AddOrderItem that takes four IN parameters:

• The order id;
• The product id;
• The quantity; and
• A discount amount;

and returns the quantity added to the order.

This procedure will need to:

• check the quantity of the product in stock and add the requested quantity if sufficient stock is available, otherwise add the available quantity;
• fetch the current price from the products table and use that as the unit price;
• deduct the quantity ordered from the units in stock and adjust the units in stock accordingly; and
• Contain an exception hander to handle duplicated order items. You will need to decide if this should be a CONTINUE or EXIT handler.

DELIMITER //
DROP PROCEDURE IF EXISTS sp_AddOrderItem//
CREATE PROCEDURE sp_AddOrderItem(
    IN  in_order_id        int,
    IN  in_product_id      int,
    IN  in_quantity_asked  int,
    IN  in_disc_amount     float,
    OUT out_quantity_added int
)

BEGIN
    DECLARE v_units_in_stock int;
    DECLARE v_unit_price     decimal(19,4);
    DECLARE v_in_order_id    int;
    DECLARE v_in_product_id  int;
    DECLARE v_quantity_order_details int;

	/*
    DECLARE EXIT HANDLER FOR NOT FOUND
		BEGIN
			CASE
				WHEN v_in_order_id    IS NULL THEN SELECT CONCAT('OrderID: ', in_order_id,   ' Not found')                AS WARNING;
				WHEN v_in_product_id  IS NULL THEN SELECT CONCAT('ProductID: ', in_product_id, ' Not found')                AS WARNING;
				WHEN v_units_in_stock IS NULL THEN SELECT CONCAT('UnitsInStock Not found for ProductID: ', in_product_id) AS WARNING;
				WHEN v_unit_price     IS NULL THEN SELECT CONCAT('UnitPrice not found for ProductID: ', in_product_id)    AS WARNING;
			END CASE; 
		END;
	*/
    
    SELECT  OrderID INTO v_in_order_id FROM Orders WHERE OrderID = in_order_id;

    SELECT  ProductID,       UnitPrice,    UnitsInStock
    INTO    v_in_product_id, v_unit_price, v_units_in_stock
    FROM    Products
    WHERE   ProductID = in_product_id;
    
    SELECT Quantity
    INTO   v_quantity_order_details
    FROM   Order_Details
    WHERE  OrderID = in_order_id AND ProductID = in_product_id;
	
    
	CASE
		WHEN v_in_order_id IS NULL THEN
		SELECT CONCAT('OrderID: ', in_order_id, ' Not found') AS WARNING;
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'OrderID Not found';
		
		WHEN v_in_product_id IS NULL THEN
		SELECT CONCAT('ProductID: ', in_product_id, ' Not found') AS WARNING;
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ProductID Not found';
		
		WHEN v_units_in_stock IS NULL THEN
		SELECT CONCAT('UnitsInStock Not found for ProductID: ', in_product_id) AS WARNING;
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UnitsInStock Not found (NULL) for ProductID entered';
		
		WHEN v_unit_price IS NULL THEN
		SELECT CONCAT('UnitPrice not found for ProductID: ', in_product_id) AS WARNING;
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UnitPrice not found (NULL) for ProductID entered';
        
		ELSE BEGIN END;
	END CASE;
    
    
    IF  v_quantity_order_details IS NOT NULL THEN
		SET v_units_in_stock = v_units_in_stock + v_quantity_order_details;
    END IF;
    
    
	IF  (v_units_in_stock = 0) THEN
		SELECT CONCAT("No units in stock for ProductID: ", v_in_product_id) AS WARNING;
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'No units in stock for selected product';
	END IF;
	
    
    IF  (v_units_in_stock  >= in_quantity_asked) THEN
		SET out_quantity_added = in_quantity_asked;
	ELSE
		SET out_quantity_added = v_units_in_stock;
        SELECT CONCAT("Units in stock (",v_units_in_stock,") less than units asked (",in_quantity_asked,"). Only ", v_units_in_stock, " units were added to to order") AS WARNING;
	END IF;


    START TRANSACTION;
               
        UPDATE  Products
        SET     UnitsInStock = v_units_in_stock - out_quantity_added
        WHERE   ProductID = in_product_id;
        
        
        IF v_quantity_order_details IS NOT NULL THEN
			UPDATE  Order_Details
			SET     UnitPrice = v_unit_price,
					Quantity  = out_quantity_added,
                    Discount  = in_disc_amount
			WHERE   OrderID   = in_order_id  AND  ProductID = in_product_id;
		ELSE
			INSERT  INTO  Order_Details  values(in_order_id,
												in_product_id,
											    v_unit_price,
											    out_quantity_added,
											    in_disc_amount);
		END IF;
        
    COMMIT;

END //
DELIMITER ;

CALL sp_AddOrderItem(10248,9,9,0,@out_quantity_added);
SELECT @out_quantity_added AS "Quantity added to the order";

Question 5 - Create a stored function

Create a stored function called OrderFulfilmentCycleTime to return the average difference between the date the order was placed and the date it was shipped.

DELIMITER //
DROP FUNCTION IF EXISTS OrderFulfilmentCycleTime//
CREATE FUNCTION OrderFulfilmentCycleTime() RETURNS decimal(19,1) DETERMINISTIC
BEGIN
    RETURN(SELECT AVG(DATEDIFF(ShippedDate, OrderDate)) FROM Orders);
END //
DELIMITER ;

select OrderFulfilmentCycleTime();


-- -- Pruebas:
-- SELECT AVG(DATEDIFF(ShippedDate, OrderDate))
-- FROM Orders;

-- SELECT OrderID, ShippedDate, OrderDate, (DATEDIFF(ShippedDate, OrderDate))
-- FROM Orders;

-- --

-- SELECT AVG(DATE(ShippedDate)-DATE(OrderDate))
-- FROM Orders;
 
-- SELECT OrderID, ShippedDate, OrderDate, (DATE(ShippedDate)-DATE(OrderDate))
-- FROM Orders;

Question 6 - Create a trigger

Create a trigger for the orders table to prevent more than one order being deleted at a given time and returning an error message to the client. You will need to determine when this trigger should be fired.

DELIMITER //
DROP TRIGGER IF EXISTS before_Orders_delete//
CREATE TRIGGER before_Orders_delete BEFORE DELETE ON Orders FOR EACH ROW
BEGIN
-- DECLARE contar1 int;
-- DECLARE contar2 int;
-- SELECT COUNT(*) INTO contar1 FROM Orders where Orders.OrderID=old.OrderID;
-- SELECT COUNT(*) INTO contar2 FROM Orders;
-- set @a = ROW_COUNT();

    -- IF ROW_COUNT() > 0 THEN
    -- IF contar2 < (contar1 - 1) THEN
    
    IF @contar IS NULL THEN
        SET @contar = 1;
    ELSE
        SET @contar = NULL;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You are trying to delete more than one row at the same time and that is not allowed. No column has been deleted.';
    END IF;
END //
DELIMITER ;

DELETE FROM Orders WHERE OrderID=10469 OR OrderID=10473;
-- DELETE FROM Orders WHERE OrderID > 10374;
SELECT ROW_COUNT();

Question 7 - Create a Trigger

Create a table SupplierOrders with the following structure.

• ProductID int(11)
• SupplierID int(11)
• Date datetime
• Quantity smallint(6)

You will need to choose an appropriate primary key for the table.

Once you have created the table, then create a trigger that automatically inserts an order in this table when the UnitsInStock value for the product goes below ReorderLevel. You will need to determine when this trigger will be fired.

DROP TABLE IF EXISTS SupplierOrders;

CREATE TABLE SupplierOrders(
    ProductID  INT(11)      NOT NULL,
    SupplierID INT(11)      NOT NULL,
    Date       DATETIME     NOT NULL,
    Quantity   SMALLINT(6),
    CONSTRAINT SupplierOrders_PK           PRIMARY KEY (ProductID, SupplierID, Date),
    CONSTRAINT SupplierOrders_Products_FK  FOREIGN KEY (ProductID)  REFERENCES Products  (ProductID),
    CONSTRAINT SupplierOrders_Suppliers_FK FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
);

DELIMITER //
DROP TRIGGER IF EXISTS after_Products_update//
CREATE TRIGGER after_Products_update AFTER UPDATE ON Products FOR EACH ROW
BEGIN
    IF  (NEW.UnitsInStock < NEW.ReorderLevel) THEN
        INSERT INTO SupplierOrders values(NEW.ProductID,
                                          NEW.SupplierID,
                                          CURRENT_TIMESTAMP,
                                          100 + NEW.ReorderLevel);
    END IF;
END //
DELIMITER ;

UPDATE  Products
SET     UnitsInStock = 24
WHERE   ProductID    = 22;

Question 8 - Create a Event

Add a column to the Products table to indicate whether or not the product has not been sold during the last 12 months. Call this column LegacyProduct giving it a default value of FALSE. Create an event called LegacyProducts that checks if the product has been ordered within the last twelve months and sets the value of the LegacyProduct to TRUE if it has not. This event should fire once per week, at 2330 on Sundays.

ALTER TABLE Products DROP LegacyProduct;

ALTER TABLE Products ADD LegacyProduct boolean NOT NULL DEFAULT FALSE;

SET GLOBAL event_scheduler = ON;

DELIMITER //
CREATE EVENT IF NOT EXISTS LegacyProducts ON SCHEDULE
-- EVERY 2 MINUTE STARTS "2018-11-25 01:54:00" DO
EVERY 1 WEEK STARTS "2018-11-25 23:30:00" DO
BEGIN
-- SELECT * FROM Products WHERE ProductID IN (
UPDATE Products SET LegacyProduct = TRUE WHERE ProductID NOT IN (
    SELECT ProductID
    FROM   Orders o INNER JOIN Order_Details od
    ON     o.OrderID = od.OrderID
    WHERE  OrderDate > DATE_SUB(NOW(), INTERVAL 1 YEAR)
);
END //
DELIMITER ;

Part II - Group Assessment

Part II a - Query Optimisation

Question 1 - Propose an example query to demonstrate something you have studied in the recommended reading or found online

Propose an example query to demonstrate something you have studied in the recommended reading or found online. Use either the Employees Database or the Northwind database for this task and reference the pages of the recommended reading or provide the URL of the web site where you got the idea to test this type of SQL query.

Note: A summary of your proposal should be posted to Moodle in the All Questions Forum under the topic Assessment Part IIa. The query you intend to test cannot be already posted by another group.

Question 2 - State what you expect to happen for the chosen database

State what you expect to happen for the chosen database. Give a detailed explanation, including calculations of rows you expect to be accessed etc., clearly outlining what you think should happen.

Question 3 - Write imaginary usage statistics for your chosen query

Write imaginary usage statistics for your chosen query: i.e. "we will suppose that this query runs 60 times per hour on average & 150 times per hour at peak". Provide a transaction analysis as per the Connolly and Begg book, figure 18.4 in 5th and 6th Editions, figure 17.4 in 4th Edition.

Question 4 - Give a detailed account of what you did

Give a detailed account of what you did, including:

a) a full SQL audit trail;

b) an explanation of each step you carried out;

c) the state of the database before the query execution;

d) the output of the EXPLAIN command;

e) the state of the database after the query execution; and

f) the time taken for query execution

Question 5 - Comment on the actual outcome versus the expected outcome

Comment on the actual outcome versus the expected outcome. State whether or not there is a difference between what you expected and what you got – give an explanation.

Question 6 - Propose an optimisation of for an update, delete or insert operation

Propose an optimisation of for an update, delete or insert operation. Outline the effect your optimisation can be expected to have.

Part II b - Database Design

A university's central library wishes to create a database to store details of its titles and borrowers. Details include the following:

• Each book has a unique ISBN number, a title, an edition and one or more authors.

• Each serial has a unique ISSN number, a title, an issue date, an editor and one or more authors

• The library often stocks several copies of any book or serial publication, each of which is given a unique accession number. The price that was paid for each copy and its location is also recorded.

• The central library has one or more branch libraries in individual faculty buildings.

• Each borrower has a name and student number. A borrower can have more than one book or serial publication on loan, but each physical copy can only be on loan to one borrower.

• Some books are designated "reference only" and can not be borrowed, but only read in the library.

• A borrower may borrow the same book on several separate occasions.

• Using an appropriate tool, create an Extended-Entity-Relationship model to comply with the above specification.

Extended Entity-Relationship model

We are going to use the Chen's ERD style.

Regular entities and attributes

Book	Serial	Publication	Borrower
accessionNumber	accessionNumber	accessionNumber	studentNumber
ISBNnumber	ISBNnumber	ISBNnumber	name
title	title	title
authors	authors	authors
location	location	location
pricePaid	pricePaid	pricePaid
edition	editor
referenceOnly	issueDate

• Each book has a unique ISBN number. The library often stocks several copies of any book or serial publication, each of which is given a:
  • Unique accession number: So, this would be the primary key because is unique for each copy
  • The price that was paid for each copy and
  • Its location is also recorded

• Some books are designated "reference only" and can not be borrowed, but only read in the library.

• authors: Multi-value attribute. A publication can have more than one author.
  • In this case, a second table named «Authors» is needed. The primary key will includes the ISBNnumber referencing the primary key in the Publication table. There will be an identifying relationship between «Publication» and «Author».

Relationship between entities

• Cardinality ratios:
  • A borrower can have more than one book or serial publication on loan, but each physical copy can only be on loan to one borrower:
    • One-to-many (1:M) relationship.

• Partial/Total participation:
  • Logical Assumptions:
    • An item (Book or Serial) can not be on loan at a given time: Partial participation
    • A Borrower can have no books on loan at a given time: Partial participation

• Generalization:
  • In this case «Books» and «Serials» can be generalized into «Publications»
  • This generalization must be disjointed. A Publication can either be a Book or a Serial, but not both.
  • No me queda completamente clara la diferencia entre Disjoint/Overlap con Union

• «Borrower»Partial 1<borrows>N «Publication»Partial

• A borrower may borrow the same book on several separate occasions.

Part III - Individual Report on Group Work

• What parts did you participate in for Parts IIa and IIb above?
• What did you learn from working within a team?
• What would you do differently if you had to build it again?
• What did you find most difficult to implement or understand.