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 ๐.๐; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment