Assignment_1.sql

CREATE DATABASE book_store;

CREATE TABLE authors (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	date_of_birth DATETIME,
	name VARCHAR(50),
	avatar_url VARCHAR(200)
);


CREATE TABLE books (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	title VARCHAR(255),
	description VARCHAR(255),
	category VARCHAR(40),
	price DECIMAL(10, 2),
	quantity INT,
	isbn BIGINT,

	author_id INT UNSIGNED NOT NULL,

	FOREIGN KEY (author_id) REFERENCES authors (id),
	UNIQUE INDEX unq_isbn (isbn)
);


CREATE TABLE customers (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	name VARCHAR(50),
	email VARCHAR(60),
	phone_number VARCHAR(15),
	date_of_birth DATETIME,
	
	address_id INT UNSIGNED,

	FOREIGN KEY (address_id) REFERENCES address (id),
	UNIQUE INDEX unq_email (email)
);

CREATE TABLE address (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	street_name VARCHAR(255),
	zip_code VARCHAR(10),
	state VARCHAR(20),
	country VARCHAR(20)
);


CREATE TABLE purchases (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	status ENUM('pending', 'cancelled', 'delivered', 'shipped', 'processing') DEFAULT 'pending',
	purchase_date DATETIME,
	
	customer_id INT UNSIGNED NOT NULL,	

	FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE purchased_books (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	book_id INT UNSIGNED NOT NULL,
	purchase_id INT UNSIGNED NOT NULL,

	FOREIGN KEY (book_id) REFERENCES books (id),
	FOREIGN KEY (purchase_id) REFERENCES purchases (id)
);

-- Selecting top 20 in-stock books

SELECT title 
FROM books
WHERE quantity > 0
ORDER BY created_at
LIMIT 20
;

-- Retrieve a list of all purchases since January 01, 2023. 
SELECT *
FROM purchases
WHERE created_at >= '2023-01-01'
ORDER BY created_at DESC;

-- 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 name = "Mohammad%" OR name = "MD%"
ORDER BY name;