Database For Software Developers

Assignment 1- Mohibbulla Munshi - 01757364843

Simple database system for an online bookstore.

CREATE TABLE books (
ISBN INT NOT NULL UNIQUE,
book_title VARCHAR(255),
description VARCHAR(255),
image VARCHAR(255),
stock INT,
price FLOAT,
book_category VARCHAR(100) NULL,
author_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (ISBN),
FOREIGN KEY (author_id) REFERENCES authors(author_id) ON UPDATE CASCADE
);

-- Insert 5 demo records into the books table
INSERT INTO books (ISBN, book_title, description, image, stock, price, book_category, author_id)
VALUES
(1234567, 'Demo Book 1', 'Description for Demo Book 1', 'demo_image_1.jpg', 10, 29.99, 'Demo Category', 1),
(23456784, 'Demo Book 2', 'Description for Demo Book 2', 'demo_image_2.jpg', 5, 19.99, 'Demo Category', 2),
(34567345, 'Demo Book 3', 'Description for Demo Book 3', 'demo_image_3.jpg', 15, 39.99, 'Demo Category', 3),
(4123456, 'Demo Book 4', 'Description for Demo Book 4', 'demo_image_4.jpg', 8, 24.99, 'Demo Category', 4),
(5634567, 'Demo Book 5', 'Description for Demo Book 5', 'demo_image_5.jpg', 12, 34.99, 'Demo Category', 5);

CREATE TABLE authors (
author_id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
author_details VARCHAR(255),
author_image VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (author_id)
);

-- Insert 5 demo records into the authors table
INSERT INTO authors (first_name, last_name, author_details, author_image)
VALUES
('John', 'Doe', 'Author of various genres', 'john_doe.jpg'),
('Jane', 'Smith', 'Best-selling novelist', 'jane_smith.jpg'),
('Bob', 'Johnson', 'Sci-fi enthusiast', 'bob_johnson.jpg'),
('Alice', 'Williams', 'Mystery writer', 'alice_williams.jpg'),
('Charlie', 'Brown', 'Poet and essayist', 'charlie_brown.jpg');

CREATE TABLE customers (
customers_id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone_number VARCHAR(13) UNIQUE,
country VARCHAR(50),
city VARCHAR(50),
district VARCHAR(50),
post_code INT,
email VARCHAR(50) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (customers_id)
);

-- Insert 3 demo records into the customers table
INSERT INTO customers (first_name, last_name, phone_number, country, city, district, post_code, email)
VALUES
('John', 'Doe', '+1234567890', 'USA', 'New York', 'Manhattan', 10001, 'john.doe@email.com'),
('Jane', 'Smith', '+9876543210', 'Canada', 'Toronto', 'Downtown', 12345, 'jane.smith@email.com'),
('Bob', 'Johnson', '+1122334455', 'UK', 'London', 'Westminster', 54321, 'bob.johnson@email.com');

CREATE TABLE purchases (
transaction_id VARCHAR(255) NOT NULL UNIQUE,
purchase_date DATETIME,
quantity INT,
total_price FLOAT,
customer_id INT,
ISBN int, -- Assuming ISBN is a string, adjust size accordingly
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (transaction_id),
FOREIGN KEY (customer_id) REFERENCES customers(customers_id),
FOREIGN KEY (ISBN) REFERENCES books(ISBN) ON UPDATE CASCADE
);

-- Insert 5 demo records into the purchases table
INSERT INTO purchases (transaction_id, purchase_date, quantity, total_price, customer_id, ISBN)
VALUES
('TXN123', '2023-01-01 10:00:00', 2, 59.98, 1, 1234567),
('TXN456', '2023-01-02 11:30:00', 1, 19.99, 2, 23456784),
('TXN789', '2023-01-03 14:45:00', 3, 119.97, 3, 34567345);

Display a list of 20 latest published in-stock book titles of the store

SELECT book_title
FROM books
WHERE stock > 0
ORDER BY created_at desc
LIMIT 20;

Retrieve a list of all purchases since January 01, 2023.

SELECT *
FROM purchases
WHERE purchase_date >= '2023-01-01';

List down all the authors in the database who have “Mohammad” or “MD” at the beginning of their name, sorted by the names (alphabetically)

SELECT *
FROM authors
WHERE first_name LIKE 'Mohammad%' OR first_name LIKE 'MD%'
ORDER BY first_name;