# Reproduce the example used in
#  https://mariadb.com/kb/en/mariadb-basics/
#  https://mariadb.com/kb/en/getting-data-from-mariadb/

DROP DATABASE bookstore;
CREATE DATABASE bookstore;
USE bookstore;

CREATE TABLE books (
  isbn CHAR(20) PRIMARY KEY, 
  title VARCHAR(50),
  author_id INT,
  publisher_id INT,
  year_pub CHAR(4),
  description TEXT );

CREATE TABLE authors (
  author_id INT AUTO_INCREMENT PRIMARY KEY,
  name_last VARCHAR(50),
  name_first VARCHAR(50),
  country VARCHAR(50) );

INSERT INTO authors
(name_last, name_first, country)
VALUES('Kafka', 'Franz', 'Czech Republic'),
      ('Dostoevsky', 'Fyodor', '');

INSERT INTO books
(isbn, title, author_id)
VALUES
('0553212168', 'Brothers Karamozov', 2),
('0679420290', 'Crime & Punishment', 2),
('0553211757', 'Crime & Punishment', 2),
('0192834118', 'Idiot', 2) ;

########################################
# The example says:
# "This statement will result in only one row displayed for Crime & Punishment 
# and it will be the first one found."
# ...but it actually shows both rows.
########################################
#
SELECT DISTINCT isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title;

#(END)