EmojiLibrary.sql

DROP DATABASE IF EXISTS 📚;

CREATE DATABASE 📚;

USE 📚;

CREATE TABLE 👤(
	🔑 INTEGER PRIMARY KEY, 
	🗣 varchar(64), -- name
	🗓 DATE -- date of registration
);

CREATE TABLE ✍(
	🔑 INTEGER PRIMARY KEY, 
	🗣 varchar(64)
);

CREATE TABLE 📕(
	🔑 INTEGER PRIMARY KEY, 
	💬 varchar(64), -- title
	🔖 varchar(64), -- genre/tag
	✍ INTEGER REFERENCES ✍(🔑) -- who wrote the book?
);

CREATE TABLE 👤🏠📕(
	👤 INTEGER REFERENCES 👤(🔑), 
	📕 INTEGER REFERENCES 📕(🔑) 
);

INSERT INTO 👤 VALUES 
	(1, 'Jeff', CURRENT_DATE),
	(2, 'Annie', CURRENT_DATE);
	
INSERT INTO ✍ VALUES 
	(1, 'Herman Melville'),
	(2, 'Lewis Carroll');

INSERT INTO 📕 VALUES 
	(1, 'Alice in Wonderland', '🔮', 2), -- genre is fantasy
	(2, 'Moby Dick', '📖', 1), -- genre is novel
	(3, 'Through the Looking-Glass', '🔮', 2); -- genre is fantasy

INSERT INTO 👤🏠📕 VALUES 
	(1, 1), -- Jeff took home 'Alice in Wonderland'
	(1, 2), -- Jeff took home 'Moby Dick'
	(2, 2);	-- Annie took home 'Moby Dick'

-- get the book taken home by each people
SELECT 👤.🗣 AS 👤, 📕.💬 AS 📕 
FROM 👤 JOIN 👤🏠📕 ON 👤.🔑 = 👤🏠📕.👤
	JOIN 📕 ON 📕.🔑 = 👤🏠📕.📕;

-- define an enum for the book length
CREATE TYPE 📄 AS ENUM ('📄', '📄📄', '📄📄📄', '📄📄📄📄');

ALTER TABLE 📕 
	ADD COLUMN 🔢📄 📄 DEFAULT '📄';

INSERT INTO 📕 VALUES 
	(4, 'Example Book', '📖', 1, '📄📄📄');

-- select the most common genre
SELECT MODE() WITHIN GROUP (ORDER BY 🔖) AS MostCommonGenre FROM 📕;

-- select books longer than 2 unit length
SELECT * FROM 📕 WHERE 📕.🔢📄 > '📄📄';


CREATE TYPE ⭐ AS ENUM ('🤢', '☹', '😐', '🙂', '😍');

ALTER TABLE 👤🏠📕 
	ADD COLUMN ⭐ ⭐;

INSERT INTO 👤🏠📕 VALUES 
	(1, 4, '😍'), -- Jeff took home 'Example Book' and rated it 😍
	(2, 3, '🙂'), -- Annie took home 'Through the Looking-Glass' and rated it 🙂
	(2, 4, '😍'), -- Annie took home 'Example Book' and rated it 😍
	(2, 4, '🤢'); -- Annie took home 'Example Book' (yes again) and rated it 🤢
	

-- select the ratings for 'Example Book'
SELECT 👤🏠📕.⭐
FROM 👤🏠📕 JOIN 📕 ON 👤🏠📕.📕 = 📕.🔑
WHERE 📕.💬 = 'Example Book';

-- select the most common rating for each book
SELECT 📕.💬, MODE() WITHIN GROUP (ORDER BY 👤🏠📕.⭐) AS MostCommonRating
FROM 👤🏠📕 JOIN 📕 ON 👤🏠📕.📕 = 📕.🔑
GROUP BY  📕.🔑;