SQL Database Design & Query Examples

Posted on Aug 30, 2025 in Computing and Communications

Library Database Schema Definition

This section outlines the SQL Data Definition Language (DDL) statements for creating a library database, including tables for publishers, distributors, customers, articles, books, journals, and rental records.

Database Setup

DROP DATABASE IF EXISTS library;
CREATE DATABASE IF NOT EXISTS library;
USE library;

Table Creation Statements

Below are the DDL statements for defining the various tables within the library database, establishing relationships and constraints.

Publisher Table

CREATE TABLE publisher(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phonenumber INT NOT NULL
) ENGINE=INNODB;

Distributor Table

CREATE TABLE distributor(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  phonenumber INT NOT NULL
) ENGINE=INNODB;

Distributor-Publisher Link Table

CREATE TABLE distributor_publisher(
  distributor_id INT,
  publisher_id INT,
  FOREIGN KEY (publisher_id) REFERENCES publisher(id),
  FOREIGN KEY (distributor_id) REFERENCES distributor(id),
  PRIMARY KEY (publisher_id, distributor_id)
) ENGINE=INNODB;

Customer Table

CREATE TABLE customer(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(100) NOT NULL
) ENGINE=INNODB;

Article Table

CREATE TABLE article(
  id INT AUTO_INCREMENT PRIMARY KEY,
  price INT NOT NULL,
  amount INT NOT NULL,
  distributed_by INT NOT NULL,
  bought_by INT NOT NULL,
  published_by INT NOT NULL,
  FOREIGN KEY (published_by) REFERENCES publisher(id),
  FOREIGN KEY (bought_by) REFERENCES customer(id),
  FOREIGN KEY (distributed_by) REFERENCES distributor(id)
) ENGINE=INNODB;

Book Table

CREATE TABLE book(
  id INT PRIMARY KEY,
  ISBN VARCHAR(20) UNIQUE NOT NULL,
  FOREIGN KEY (id) REFERENCES article(id)
) ENGINE=INNODB;

Journal Table

CREATE TABLE journal(
  id INT PRIMARY KEY,
  ISSN VARCHAR(24) UNIQUE NOT NULL,
  issue INT NOT NULL,
  FOREIGN KEY (id) REFERENCES article(id)
) ENGINE=INNODB;

Rent Table

CREATE TABLE rent(
  customer_id INT,
  article_id INT,
  begin TIMESTAMP,
  end TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customer(id),
  FOREIGN KEY (article_id) REFERENCES article(id),
  PRIMARY KEY (customer_id, article_id)
) ENGINE=INNODB;

SQL Query Examples

This section presents various SQL Data Manipulation Language (DML) queries, demonstrating common operations such as data insertion, filtering, ordering, aggregation, and joining tables. These examples are based on a different database context, likely a car dealership scenario.

Basic Data Retrieval

SELECT * FROM moto;

Data Insertion (INSERT Statements)

Examples of inserting new records into the moto table within the part2022 database.

INSERT INTO `part2022`.`moto` (`id`, `matricula`, `marca`, `modelo`, `vehiculo_id`) VALUES ('1', '2342ZZZ', 'Honda', 'CBR600', '5');
INSERT INTO `part2022`.`moto` (`id`, `matricula`, `marca`, `modelo`, `vehiculo_id`) VALUES ('2', '3443XXX', 'Honda', 'CBR125', '6');
INSERT INTO `part2022`.`moto` (`id`, `matricula`, `marca`, `modelo`, `vehiculo_id`) VALUES ('3', '4943XXX', 'Kawasaki', 'Ninja600', '7');

Filtering Data with WHERE Clause

Retrieving specific columns for cars of a particular brand.

SELECT matricula, modelo
FROM coche
WHERE marca = "Seat";

Filtering with BETWEEN and AND Operators

Demonstrates selecting buyers based on a budget range using BETWEEN and multiple AND conditions.

SELECT nombre, localizacion
FROM comprador
WHERE presupuesto BETWEEN 2000 AND 5000;

Note: The following query is logically equivalent to presupuesto > 5000.

SELECT nombre, localizacion
FROM comprador
WHERE presupuesto > 2000 AND presupuesto > 5000;

Ordering and Limiting Results

Selecting the top 2 buyers ordered by their budget in ascending order.

SELECT nombre, presupuesto
FROM comprador
ORDER BY presupuesto ASC
LIMIT 2;

Aggregating Data with GROUP BY

Counting the number of dealerships per city and ordering the results.

SELECT ciudad, COUNT(ciudad)
FROM concesionario
GROUP BY ciudad
ORDER BY COUNT(ciudad) DESC;

Joining Tables (INNER JOIN)

Finding distinct seller names associated with dealerships in Barcelona.

SELECT DISTINCT vendedor.nombre
FROM vendedor INNER JOIN concesionario
ON vendedor_id = concesionario.vendedor_id
WHERE concesionario.ciudad = "Barcelona";

Using Aggregate Functions and Joins

Retrieving the car with the maximum price by joining coche and vehiculo tables.

SELECT MAX(precio) AS precio_maximo, coche.*
FROM coche
INNER JOIN vehiculo
ON coche.id = vehiculo.id;

Subqueries for Conditional Filtering

Selecting buyers whose vehicle price is below the average vehicle price.

SELECT DISTINCT comprador.nombre, comprador.localizacion
FROM vehiculo
INNER JOIN comprador
ON comprador.id = vehiculo.comprador_por
WHERE precio < (SELECT AVG(vehiculo.precio) FROM vehiculo);

Complex Joins and Subqueries for Specific Data

Two different approaches to find the license plate, brand, and model of cars bought by ‘Marina’.

SELECT B.matricula, B.marca, B.modelo
FROM comprador A
INNER JOIN
(SELECT vehiculo.comprador_por, coche.matricula, coche.marca, coche.modelo
FROM vehiculo
INNER JOIN coche
ON vehiculo.id = coche.vehiculo_id) B
ON A.id = B.comprador_por
WHERE A.nombre = "Marina";

SELECT co.matricula, co.marca, co.modelo
FROM vehiculo v
INNER JOIN coche co
ON v.id = co.vehiculo_id
INNER JOIN comprador cm
ON cm.id = v.comprador_por
WHERE cm.nombre = "Marina";

Retrieving Data Through Multiple Joins

Two methods to find the name and location of buyers associated with the intermediary ‘María’.

SELECT B.nombre, B.localizacion
FROM intermediario A
INNER JOIN
(SELECT comprador.nombre, comprador.localizacion, vehiculo.distribuido_por
FROM vehiculo
INNER JOIN comprador
ON vehiculo.comprador_por = comprador.id) B
ON A.id = B.distribuido_por
WHERE A.nombre = "María";

SELECT C.nombre, C.localizacion
FROM intermediario A
INNER JOIN vehiculo V
ON A.id = V.distribuido_por
INNER JOIN comprador C
ON C.id = V.comprador_por
WHERE A.nombre = "María";

Schema Modification (ALTER TABLE)

Adding an index to the precio column of the VEHICULO table for performance optimization.

ALTER TABLE VEHICULO ADD INDEX (precio);